Connect to Oracle from Java

To connect to Oracle from Java, you need to have the correct Oracle JDBC drivers on your classpath. The best approach is to use the most recent 11gr2 drivers, which are currently ojdbc6.jar (for Java 6) or ojdbc5.jar for Java 5. These drivers are certified to work with Oracle 9 to 11gr2, so there is little point in using the older ones. For older versions of the Oracle JDBC drivers, ojdbc14.jar will work with Java 4, 5 and 6.

set CLASSPATH=C:\app\sodonnel\product\11.2.0\dbhome_1\jdbc\lib\ojdbc5.jar;.

Then the following code is about the simplest possible to connect to a database:

import java.sql.*;
import oracle.jdbc.*;

public class TestDBOracle {

    public static void main(String[] args)
        throws ClassNotFoundException, SQLException
    {
        DriverManager.registerDriver
            (new oracle.jdbc.driver.OracleDriver());

        String url = "jdbc:oracle:thin:@//localhost:1521/local11gr2.world";
        //            jdbc:oracle:thin:@//host:port/service


        Connection conn =
            DriverManager.getConnection(url,"scott","tiger");

        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement();
        ResultSet rset =
            stmt.executeQuery("select BANNER from SYS.V_$VERSION");
        while (rset.next()) {
            System.out.println (rset.getString(1));
        }
        stmt.close();
        System.out.println ("Ok.");
    }
}

Save the code in a file called TestDBOracle.java, and then complile with javac:

javac TestDBOracle.java

Then run it with the java command:

java TestDBOracle

Remember to leave out the '.class' extension when running it.

Connect With a tns_names.ora file

To connect using a TNS entry, you need to tell Java which directory the tns_names.ora file resides in. This can be done by setting the System property in the Java code:

System.setProperty("oracle.net.tns_admin","C:/app/sodonnel/product/11.2.0/dbhome_1/NETWORK/ADMIN");

The JDBC URL then becomes:

String url = "jdbc:oracle:thin:@local11gr2";

Where "local11gr2" is replaced with the TNS name you want to connect to.