Using TNSName - JDBC Thin client

The general practice in industry is to use TNSNames while fetching a database connection. Lets see a example on how to use TNSName while fetching the database connection.

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestJDBCDriver {

private final boolean useTnsName;

public TestJDBCDriver(boolean useTnsName) {
this.useTnsName = useTnsName;
}

public void retrieveEmployeeSalary() throws SQLException {

Connection con = getConnection(useTnsName);

PreparedStatement ps = con.prepareStatement("SELECT SALARY FROM EMPLOYEE WHERE EMPLOYEE_ID = 395");
ResultSet rs = ps.executeQuery();
double salary = readResultSet(rs);

System.out.println(" Salaray :" + salary + "When useTnsName is set to :"+useTnsName );
}


private double readResultSet(ResultSet rs) throws SQLException {
double result = -2;

if (!rs.next ()) {
result = -1;
}
result = rs.getDouble(1);
return result;
}


private Connection getConnection(boolean useTnsName) throws SQLException {

String url = useTnsName?getDBUrl():getFullDBUrl();

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(url, getDBUsername(), getDBPassword());

return conn;
}

private String getDBUsername() {
return "USER";
}

private String getDBPassword() {
return "PASSWORD";
}

private String getFullDBUrl() {
return "jdbc:oracle:thin:@HOST:1521:DBINSTANCE";
}

private String getDBUrl() {
return "jdbc:oracle:thin:@DBINSTANCE";
}

public static void main(String[] args) throws SQLException {
String useTnsnameStr = System.getProperty("useTnsName");
TestJDBCDriver testJDBCDriver = new TestJDBCDriver(Boolean.parseBoolean(useTnsnameStr));
testJDBCDriver.retrieveEmployeeSalary();
}
}

Now the difference is in using the database URL, when TNSName is used, the corresponding database details exists in tnsnames.ora file and the details of this file need to be provided as JVM argument while invoking the main class. JVM argument is -Doracle.net.tns_admin

Now lets invoke the above code in both ways:

When there is no TNSName:

java com.testTestJDBCDriver 

When using TNSName:

java -DuseTnsName=true -Doracle.net.tns_admin=O:/Ora/network/admin/ com.testTestJDBCDriver 

I hope the above was useful