Java for oracle client

Java connect to Oracle database via JDBC

This JDBC tutorial helps you write Java code to establish database connection with an Oracle database server – the first step to have Java applications working with one of the most popular database systems. Suppose you already had a version of Oracle database installed, such as Oracle Database Express Edition.

1. Download JDBC driver library for Oracle database

To make a Java program talks with Oracle database, we need to have the Oracle JDBC driver (OJDBC) present in the classpath. Click here to visit Oracle’s JDBC driver download page. Then select the JDBC driver version that matches Oracle database sever and JDK installed on your computer. Currently there are two main versions of OJDBC:

— OJDBC 8: certified with JDK 8, for Oracle database 11g and 12c.

— OJDBC 10: certified with JDK 10, for Oracle database 18c and 19c.

NOTE: Oracle requires users to have an Oracle account for downloading, so you may have to register an account if you don’t have one.

Extract the downloaded archive, and place the ojdbc10.jar file under your project’s classpath as usual as using any jar file.

If you use Maven, add the following dependency to the pom.xml file:

 com.oracle ojdbc8 1.0 system d:/Path/To/Oracle/JDBC/ojdbc8.jar 

2. JDBC database URL for Oracle database

• drivertype can be thin, oci or kprb .
• database can be in the form of hostname:port:SID or a TNSNAMES entry listed in the file tnsnames.ora reside on the client computer. The default port is 1521.

Driver type

Thin Driver

For client-side use without an Oracle installation

For client-side use with an Oracle installation

Server-Side Thin Driver

Same as Thin Driver, but runs inside an Oracle server to access a remote server

Server-Side Internal Driver

Runs inside the target server

According to Oracle, if your JDBC client and Oracle database server are running on the same machine, you should use the OCI Driver because it is much faster than the Thin Driver (The OCI Driver can use Inter Process Communication – IPC, whereas the Thin Driver can use only network connection).

For example, if you want to connect user tiger with password scott to an Oracle database with SID productDB through default port on host dbHost using the Thin Driver, you can construct the URL as follows:

String url = “jdbc:oracle:thin:tiger/scott@dbHost:1521:productDB”

String url = “jdbc:oracle:oci:tiger/scott@localhost:1521:productDB” String url = “jdbc:oracle:oci:tiger/scott@dbHost:1521:productDB”

If you have a TNSNAMES entry productionDB in the tnsnames.ora file, you can construct the URL as follows:

String url = “jdbc:oracle:oci:@productionDB”

For the Server-Side Internal Driver, use the following URLs:

String url = "jdbc:oracle:kprb:" String url = "jdbc:default:connection:"

Because in that environment, the driver actually runs within a default session, and the client is always connected so the connection should never be closed.

3. Register Oracle JDBC driver

The Oracle JDBC driver class name is oracle.jdbc.OracleDriver . You can register this driver as follows:

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Class.forName("oracle.jdbc.OracleDriver");

NOTE: Since Java 6 (JDBC 4.0), registering the driver explicitly as above becomes optional. As long as we put the ojdbc10.jar file in the classpath, JDBC driver manager can detect and load the driver automatically.

4. Establish connection to Oracle database

With JDBC, we can establish a database connection by calling the method getConnection() of the DriverManager class. There are three versions of this method:

So we can have three ways for making a connection as follows:

Using only database URL for everything

In this method, we specify all connection properties in a single URL string, for example:

String dbURL = «jdbc:oracle:thin:tiger/scott@localhost:1521:productDB»; Connection conn = DriverManager.getConnection(dbURL); if (conn != null)

That uses the Thin Driver to connect the user tiger with password scott to the database SID productDB running on the same machine through the default port 1521.

Using database URL, username and password

In this method, we pass the username and password as additional arguments to the method getConnetion() , for example:

String dbURL = "jdbc:oracle:thin:@localhost:1521:productDB"; String username = "tiger"; String password = "scott"; Connection conn = DriverManager.getConnection(dbURL, username, password);

Using database URL and Properties object

In this method, we use a java.util.Properties object to hold username, password and other additional properties. For example:

String dbURL = "jdbc:oracle:oci:@ProductDB"; Properties properties = new Properties(); properties.put("user", "scott"); properties.put("password", "tiger"); properties.put("defaultRowPrefetch", "20"); Connection conn = DriverManager.getConnection(dbURL, properties);

In this example, we are using the OCI Driver with a TNSNAMES entry ProductDB , and specifying an additional property defaultRowPrefetch which is the number of rows to prefetch from the server.

5. Java Connect to Oracle Database Example program

To demonstrate, we create a small example program below that establishes three different connections in 3 ways mentioned above, and finally close all the connections:

package net.codejava.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; /** * This program demonstrates how to make database connection with Oracle * database server. * @author www.codejava.net * */ public class JdbcOracleConnection < public static void main(String[] args) < Connection conn1 = null; Connection conn2 = null; Connection conn3 = null; try < // registers Oracle JDBC driver - though this is no longer required // since JDBC 4.0, but added here for backward compatibility Class.forName("oracle.jdbc.OracleDriver"); // METHOD #1 String dbURL1 = "jdbc:oracle:thin:tiger/scott@localhost:1521:productDB"; conn1 = DriverManager.getConnection(dbURL1); if (conn1 != null) < System.out.println("Connected with connection #1"); >// METHOD #2 String dbURL2 = "jdbc:oracle:thin:@localhost:1521:productDB"; String username = "tiger"; String password = "scott"; conn2 = DriverManager.getConnection(dbURL2, username, password); if (conn2 != null) < System.out.println("Connected with connection #2"); >// METHOD #3 String dbURL3 = "jdbc:oracle:oci:@ProductDB"; Properties properties = new Properties(); properties.put("user", "tiger"); properties.put("password", "scott"); properties.put("defaultRowPrefetch", "20"); conn3 = DriverManager.getConnection(dbURL3, properties); if (conn3 != null) < System.out.println("Connected with connection #3"); >> catch (ClassNotFoundException ex) < ex.printStackTrace(); >catch (SQLException ex) < ex.printStackTrace(); >finally < try < if (conn1 != null && !conn1.isClosed()) < conn1.close(); >if (conn2 != null && !conn2.isClosed()) < conn2.close(); >if (conn3 != null && !conn3.isClosed()) < conn3.close(); >> catch (SQLException ex) < ex.printStackTrace(); >> > >

That’s Java code example for making connection to Oracle database server. For visual howtos, watch the following video:

JDBC API References:

Related JDBC Tutorials:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Источник

Java for oracle client

Oracle provides enterprise application developers an end-to-end Java solution for creating, deploying, and managing Java applications. The total solution consists of client-side and server-side programmatic interfaces, tools to support Java development, and a JVM integrated with Oracle Database. All these products are fully compatible with Java standards. This section covers the following topics:

1.6.1 Java in Database Application Development

The most important features of Java in database application development are:

• Designing data-bound procedures and functions using Java SE APIs and JDBC.
• Extending the reach and capabilities of the database with standard and third-party Java libraries. For example, accessing third-party databases using their drivers in the database and accessing Hadoop/HDFS.
• Providing flexible partitioning of Java2 Platform, Standard Edition (J2SE) applications for symmetric data access at the JDBC level.
• Bridging SQL and the Java2 Platform, Enterprise Edition (J2EE) world by:
  • Calling out Web components, such as JSP and servlet
  • Bridging SQL and Web Services using Web Service Callouts

  1.6.2 Java Programming Environment Usage

  In addition to the Oracle JVM, the Java programming environment provides:

  • Java stored procedures as the Java equivalent and companion for PL/SQL. Java stored procedures are tightly integrated with PL/SQL. You can call Java stored procedures from PL/SQL packages and PL/SQL procedures from Java stored procedures.
  • The JDBC and SQLJ programming interfaces for accessing SQL data.
  • Tools and scripts that assist in developing, loading, and managing classes.

  The following table helps you decide when to use which Java API:

  To have a Java procedure called from SQL, such as a trigger.

  To call a static, simple SQL statement from a known table with known column names from a Java object.

  To call dynamic, complex SQL statements from a Java object.

  1.6.3 Java Stored Procedures

  Java stored procedures are Java programs written and deployed on a server and run from the server, exactly like a PL/SQL stored procedure. You invoke it directly with products like SQL*Plus, or indirectly with a trigger. You can access it from any Oracle Net client, such as OCI and PRO*, or JDBC or SQLJ.

  In addition, you can use Java to develop powerful, server-side programs, which can be independent of PL/SQL. Oracle Database provides a complete implementation of the standard Java programming language and a fully compliant JVM.

  Related Topics

  1.6.4 PL/SQL Integration and Oracle RDBMS Functionality

  You can call existing PL/SQL programs from Java and Java programs from PL/SQL. This solution protects and leverages your PL/SQL and Java code and opens up the advantages and opportunities of Java-based Internet computing.

  Oracle Database offers two different Java APIs for accessing SQL data, JDBC and SQLJ. Both these APIs are available on the client, and the JDBC API is also available on the server. As a result, you can deploy your applications on the client and server.

  The following topics introduce the Java APIs provided by Oracle Database:

  1.6.4.1 JDBC Drivers

  JDBC is a database access protocol that enables you to connect to a database and run SQL statements and queries to the database. The core Java class libraries provide the following JDBC APIs: java.sql and javax.sql . However, JDBC is designed to enable vendors to supply drivers that offer the necessary specialization for a particular database. Oracle provides the following distinct JDBC drivers:

  You can use the JDBC Thin driver to write pure Java applications and applets that access Oracle SQL data. The JDBC Thin driver is especially well-suited for Web-based applications and applets, because you can dynamically download it from a Web page, similar to any other Java applet.

  The JDBC OCI driver accesses Oracle-specific native code, that is, non-Java code, and libraries on the client or middle tier, providing performance boost compared to the JDBC Thin driver, at the cost of significantly larger size and client-side installation.

  JDBC server-side internal driver

  Oracle Database uses the server-side internal driver when the Java code runs on the server. It allows Java applications running in the Oracle JVM on the server to access locally defined data, that is, data on the same system and in the same process, with JDBC. It provides a performance boost, because of its ability to use the underlying Oracle RDBMS libraries directly, without the overhead of an intervening network connection between the Java code and SQL data. By supporting the same Java-SQL interface on the server, Oracle Database does not require you to rework code when deploying it.

  Related Topics

  Источник