JavaBlog.fr / Java.lu Database,DEVELOPMENT,Java Java : Access Database via JDBC, Use of SID or ServiceName

Java : Access Database via JDBC, Use of SID or ServiceName

March 6, 2017March 6, 2017 | Huseyin OZVERENHuseyin OZVEREN | 0 Comment | 11:58 PM

Hi,

Often, the accesses to databases are different in JDBC according to the installation’s type of databases:

  • via SID for the simple/single instance of databases with a connection string like jdbc :oracle :thin :@//hostname :port_number:SID
  • via Service Name for the simple/single instance of databases OR the databases installed on cluster system with a connection string like jdbc :oracle :thin :@//hostname :port_number/service_name

 
A bad use of JBDC access for example via SID with the databases installed on cluster system could produce the following error:

view source
print?
01...
02at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
03at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
04at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
05at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
06at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
07at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
08at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
09at java.sql.DriverManager.getConnection(DriverManager.java:582)
10at java.sql.DriverManager.getConnection(DriverManager.java:154)

 
Here, an utility class of database connections:

view source
print?
01package com.java.lu.db.extract;
02 
03import java.sql.Connection;
04import java.sql.Driver;
05import java.sql.DriverManager;
06 
07import org.apache.commons.lang.StringUtils;
08import org.apache.commons.lang.exception.ExceptionUtils;
09import org.apache.commons.logging.Log;
10import org.apache.commons.logging.LogFactory;
11 
12/**
13 * Utility class of database connections
14 */
15public class DBUtils  {
16    private final static Log logger = LogFactory.getLog(DBUtils.class);
17 
18    /**
19     * Make the connection to the database
20     * @throws Exception
21     */
22    public static Connection makeConnection(String DB_Login, String DB_Password, String DB_Port, String DB_Schema, String DB_Server, String DB_Sid, String DB_ServiceName, String DB_AdditionalParams) throws Exception {
23         
24        Driver driver = (Driver)Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
25        DriverManager.registerDriver(driver);
26 
27        // URL
28        String connectionURL = null;
29        if(StringUtils.isNotBlank(DB_Sid)){ // With SID
30            connectionURL = "jdbc:oracle:thin:@" + DB_Server+ ":" + DB_Port + ":" + DB_Sid;
31        }else{ // With ServiceName
32            connectionURL = "jdbc:oracle:thin:@" + DB_Server+ ":" + DB_Port + "/" + DB_ServiceName;
33        }
34 
35        // ADDITIONAL PARAMS
36        java.util.Properties additionalParamsProps = new java.util.Properties();
37        {
38            additionalParamsProps.put("user", DB_Login);
39            additionalParamsProps.put("password", DB_Password);
40            //
41            String additionalParams = DB_AdditionalParams;
42            if(StringUtils.isNotBlank(additionalParams)){
43                additionalParams = additionalParams.replaceAll("&", "\n");
44                additionalParamsProps.load(new java.io.ByteArrayInputStream(additionalParams.getBytes()));
45            }//end-if
46        }
47        Connection con = java.sql.DriverManager.getConnection(connectionURL, additionalParamsProps);
48        //con = DriverManager.getConnection(connectionURL, config.getString(DB_DCTM_Login), config.getString(DB_DCTM_Password));
49        return con;
50    }
51     
52     
53    /**
54     * Close the connection to the database
55     * @throws Exception
56     */
57    public static void closeConnection(Connection con) throws Exception {
58        try {
59            if(con !=null){
60                logger.info("closeConnection : "+ con);
61                con.close();
62            }
63        } catch (Exception e) {
64            logger.error(ExceptionUtils.getFullStackTrace(e));
65        }
66    }
67     
68     
69    public static void main(String[] args) {
70        try {
71            // DEV with SID
72            Connection con = makeConnection("MY_USER_DB1", "MY_PWD_DB1", "1521", "", "myserver1.dev.java.lu", "MY_DB1_SID", "", "");
73            closeConnection(con);
74             
75            // DEV with ServiceName
76            con = makeConnection("MY_USER_DB2", "MY_PWD_DB2", "1521", "", "myserver2.dev.java.lu", "", "MY_DB2_SERVICENAME", "");
77            closeConnection(con);
78        } catch (Exception e) {
79            e.printStackTrace();
80        }
81    }
82 
83}

 
 
More, just some words concerning the configuration file tnsnames.ora of ORACLE which contains the informations used in the configuration of database connection in tools like SQLdeveloper via Network Alias. The tools like Oracle SQL Developer looks sequentially for the tnsnames.ora file in the following locations:

  1. $HOME/.tnsnames.ora
  2. $TNS_ADMIN/tnsnames.ora
  3. /etc/tnsnames.ora (non-Windows systems)
  4. $ORACLE_HOME/network/admin/tnsnames.ora
  5. Registry key

So, if the environment variable TNS_ADMIN = C:\MYTNS, the C:\MYTNS\tnsnames.ora file must contain the database configuration (protocol, host, port, connection data SERVICE_NAME):

view source
print?
01MYDBDEV =
02  (DESCRIPTION =
03    (ADDRESS_LIST =
04      (ADDRESS = (PROTOCOL = TCP)(HOST = MYSERVER_DB_DEV)(PORT = 1521))
05    )
06    (CONNECT_DATA = (SERVICE_NAME = GEDdev)
07    )
08  )
09 
10MYDBTEST=
11    (DESCRIPTION =
12        (FAILOVER=ON)
13        (ADDRESS_LIST =
14            (ADDRESS=(PROTOCOL=TCP)(HOST=MYSERVER_DB_TST1)(PORT=1521))
15            (ADDRESS=(PROTOCOL=TCP)(HOST=MYSERVER_DB_TST2)(PORT=1521)))
16        (CONNECT_DATA=(SERVICE_NAME=GEDtest))
17        (FAILOVER_MODE=
18            (TYPE=select)
19            (METHOD=basic)))

Best regards,

Huseyin OZVEREN

Tags: , , , ,

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.

Related Post