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

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:

...
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:154)

 
Here, an utility class of database connections:

package com.java.lu.db.extract;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Utility class of database connections
 */
public class DBUtils  {
	private final static Log logger = LogFactory.getLog(DBUtils.class);

	/**
	 * Make the connection to the database
	 * @throws Exception
	 */
	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 {
		
		Driver driver = (Driver)Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
		DriverManager.registerDriver(driver);

		// URL
		String connectionURL = null;
		if(StringUtils.isNotBlank(DB_Sid)){ // With SID
			connectionURL = "jdbc:oracle:thin:@" + DB_Server+ ":" + DB_Port + ":" + DB_Sid;
		}else{ // With ServiceName
			connectionURL = "jdbc:oracle:thin:@" + DB_Server+ ":" + DB_Port + "/" + DB_ServiceName;
		}

		// ADDITIONAL PARAMS
		java.util.Properties additionalParamsProps = new java.util.Properties();
		{
			additionalParamsProps.put("user", DB_Login);
			additionalParamsProps.put("password", DB_Password);
			//
			String additionalParams = DB_AdditionalParams;
			if(StringUtils.isNotBlank(additionalParams)){
				additionalParams = additionalParams.replaceAll("&", "\n");
				additionalParamsProps.load(new java.io.ByteArrayInputStream(additionalParams.getBytes()));
			}//end-if
		}
		Connection con = java.sql.DriverManager.getConnection(connectionURL, additionalParamsProps);
		//con = DriverManager.getConnection(connectionURL, config.getString(DB_DCTM_Login), config.getString(DB_DCTM_Password));
		return con;
	}
	
	
	/**
	 * Close the connection to the database
	 * @throws Exception
	 */
	public static void closeConnection(Connection con) throws Exception {
		try {
			if(con !=null){
				logger.info("closeConnection : "+ con);
				con.close();
			}
		} catch (Exception e) {
			logger.error(ExceptionUtils.getFullStackTrace(e));
		}
	}
	
	
	public static void main(String[] args) {
		try {
			// DEV with SID
			Connection con = makeConnection("MY_USER_DB1", "MY_PWD_DB1", "1521", "", "myserver1.dev.java.lu", "MY_DB1_SID", "", "");
			closeConnection(con);
			
			// DEV with ServiceName
			con = makeConnection("MY_USER_DB2", "MY_PWD_DB2", "1521", "", "myserver2.dev.java.lu", "", "MY_DB2_SERVICENAME", "");
			closeConnection(con);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

 
 
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):

MYDBDEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = MYSERVER_DB_DEV)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = GEDdev)
    )
  )

MYDBTEST=
	(DESCRIPTION =
		(FAILOVER=ON)
		(ADDRESS_LIST =
			(ADDRESS=(PROTOCOL=TCP)(HOST=MYSERVER_DB_TST1)(PORT=1521))
			(ADDRESS=(PROTOCOL=TCP)(HOST=MYSERVER_DB_TST2)(PORT=1521)))
		(CONNECT_DATA=(SERVICE_NAME=GEDtest))
		(FAILOVER_MODE=
			(TYPE=select)
			(METHOD=basic)))

Best regards,

Huseyin OZVEREN