Real'sHowTo AddThis Feed Button
Custom Search

Connect to an Oracle database with JDBCTag(s): JDBC


The JDBC driver for Oracle is available at their Web site. All you need is to include the required jar in the classpath.
import java.sql.*;

public class TestDBOracle {

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

        String url = "jdbc:oracle:thin:@//server.local:1521/prod";
    //               jdbc:oracle:thin:@//host:port/service
    // or
    // String url = "jdbc:oracle:thin:@server.local:1521:prodsid";
    //               jdbc:oracle:thin:@host:port:SID
    //
    //  SID  - System ID of the Oracle server database instance.
	//         By default, Oracle Database 10g Express Edition
	//         creates one database instance called XE.
	//         ex : String url = "jdbc:oracle:thin:@myhost:1521:xe";



    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.");
  }
}
You can find the SID in the tnsnames.ora file, ex :
XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )
While it is not to difficult to connect using the example above, it would be nice to connect without having to specify a server and a port number. Since release 10.2.0.1.0, it's possible to give only a TNSNAMES entry and the driver extract the required infos (server and port) for the defined TNSNAMES.ORA file. In order for this to work you must have configured the file TNSNAMES.ORA correctly and set a the java property oracle.net.tns_admin.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleDriver;

public class TestOra {
    public TestOra() {
        System.setProperty("oracle.net.tns_admin","\\\\myserver\\TNSNAMES_DIR");
        // or
        //  java.exe -Doracle.net.tns_admin=\\myserver\TNSNAMES_DIR TestOra ...
        //
    }

    public void doit () throws SQLException {
        String usr = "scott";
        String pwd = "tiger";
        String url = "jdbc:oracle:thin:@MYORCL";

        DriverManager.registerDriver(new OracleDriver());
        Connection conn = DriverManager.getConnection(url,usr,pwd);

        String sql = "select {fn now()} from dual" ;
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next())
            System.out.println("results: " + rs.getString(1));
        conn.close();
    }

    public static void main(String[] args){
        TestOra test = new TestOra();
        try {
            test.doit();
            System.out.println("Done..");
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

/*

  \\myserver\TNSNAMES_DIR\tnsnames.ora


  MYORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcltest.local)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = orcl)(INSTANCE_ROLE=ANY))
  )

 */

See also this HowTo to connect using the Oracle Connection Pool.
blog comments powered by Disqus


If you find this article useful, consider making a small donation
to show your support for this Web site and its content.

Written and compiled by Réal Gagnon ©1998-2014
[ home ]