Java access to oracle
Oracle provides various ways to integrate Oracle object features with Java.
These interfaces enable you both to access SQL data from Java and to provide persistent database storage for Java objects.
4.7.1 JDBC Access to Oracle Object Data
JDBC (Java Database Connectivity) is a set of Java interfaces to the Oracle server.
Oracle provides tight integration between objects and JDBC. You can map SQL types to Java classes with considerable flexibility.
- Allows access to objects and collection types (defined in the database) in Java programs through dynamic SQL.
- Translates types defined in the database into Java classes through default or customizable mappings.
Version 2.0 of the JDBC specification supports object-relational constructs such as user-defined (object) types. JDBC materializes Oracle objects as instances of particular Java classes. Using JDBC to access Oracle objects involves creating the Java classes for the Oracle objects and populating these classes. You can either:
- Let JDBC materialize the object as a STRUCT . In this case, JDBC creates the classes for the attributes and populates them for you.
- Manually specify the mappings between Oracle objects and Java classes; that is, customize your Java classes for object data. The driver then populates the customized Java classes that you specify, which imposes a set of constraints on the Java classes. To satisfy these constraints, you can choose to define your classes according to either the SQLData interface or the ORAData interface.
4.7.2 Data Mapping Strategies
Oracle SQLJ supports either strongly typed or weakly typed Java representations of object types, reference types ( REF s), and collection types (varrays and nested tables) to be used in iterators or host expressions.
Strongly typed representations use a custom Java class that corresponds to a particular object type, REF type, or collection type and must implement the interface oracle . sql . ORAData . The Oracle JVM Web services Call-Out Utility can automatically generate such custom Java classes.
Weakly typed representations use the class oracle . sql . STRUCT (for objects), oracle . sql . REF (for references), or oracle . sql . ARRAY (for collections).
4.7.3 Java Object Storage
Oracle JVM Web services Call-Out Utility enables you to construct Java classes that map to existing SQL types. You can then access the SQL types from a Java application using JDBC.
You can also go in the other direction. That is, you can create SQL types that map to existing Java classes. This capability enables you to provide persistent storage for Java objects. Such SQL types are called SQL types of Language Java, or SQLJ object types. They can be used as the type of an object, an attribute, a column, or a row in an object table. You can navigationally access objects of such types—Java objects—through either object references or foreign keys, and you can query and manipulate such objects from SQL.
You create SQLJ types with a CREATE TYPE statement as you do other user-defined SQL types. For SQLJ types, two special elements are added to the CREATE TYPE statement:
- An EXTERNAL NAME phrase, used to identify the Java counterpart for each SQLJ attribute and method and the Java class corresponding to the SQLJ type itself
- A USING clause, to specify how the SQLJ type is to be represented to the server. The USING clause specifies the interface used to retrieve a SQLJ type and the kind of storage.
Example 4-2 Mapping SQL Types to Java Classes
-- Mapping SQL Types to Java Classes example, not sample schema CREATE TYPE full_address AS OBJECT (a NUMBER); / CREATE OR REPLACE TYPE person_t AS OBJECT EXTERNAL NAME 'Person' LANGUAGE JAVA USING SQLData ( ss_no NUMBER (9) EXTERNAL NAME 'socialSecurityNo', name varchar(100) EXTERNAL NAME 'name', address full_address EXTERNAL NAME 'addrs', birth_date date EXTERNAL NAME 'birthDate', MEMBER FUNCTION age RETURN NUMBER EXTERNAL NAME 'age () return int', MEMBER FUNCTION addressf RETURN full_address EXTERNAL NAME 'get_address () return long_address', STATIC function createf RETURN person_t EXTERNAL NAME 'create () return Person', STATIC function createf (name VARCHAR2, addrs full_address, bDate DATE) RETURN person_t EXTERNAL NAME 'create (java.lang.String, Long_address, oracle.sql.date) return Person', ORDER member FUNCTION compare (in_person person_t) RETURN NUMBER EXTERNAL NAME 'isSame (Person) return int') /
SQLJ types use the corresponding Java class as the body of the type; you do not specify a type body in SQL to contain implementations of the type’s methods as you do with ordinary object types.
Java access to oracle
Oracle Database is a relational database that you can use to store, modify and use data.
The Java Database Connectivity (JDBC) standard is used by Java applications to access and manipulate data in relational databases.
JDBC is an industry-standard application programming interface (API) that lets you access a RDBMS using SQL from Java. JDBC complies with the Entry Level of the JDBC escape standard. Each vendor implements the JDBC Specification with its own extensions.
Universal Connection Pool (UCP) is a connection pool used to cache the database connection objects to reuse the connections, thus improving the performance.
Java in the Database (OJVM) helps group SQL operations with Java data logic and load them into the database for in-place processing.
This chapter introduces you to the JDBC driver, Universal Connection Pool (UCP) and Java in the Database (OJVM) with Oracle Database 12 c Release 2 (12.2)
- Java Database Connectivity Driver (JDBC)
- Universal Connection Pool (UCP)
- Java in the Database (OJVM)
Java Database Connectivity Driver (JDBC)
JDBC is a database access protocol that enables you to connect to a database and run SQL statement and queries on the database. JDBC drivers implement and comply with the latest JDBC specifications. Java application need to have ojdbc8.jar compatible with JDK8 in their classpath.
The core Java class libraries provide the JDBC APIs, java.sql and javax.sql
The following sections describe Oracle support for the JDBC standard:
Oracle recommends using the JDBC Thin Driver for most requirements. The JDBC Thin Driver will work on any system with a suitable Java Virtual Machine. (JVM). Some other client drivers that Oracle provides are JDBC thin driver, Oracle Call Interface (OCI) driver, Server side thin driver, and server side internal driver.
The JDBC Thin Driver is a pure Java, Type IV driver. The JDBC driver version )ojdbc8.jar) inludes support for JDK 8.
JDBC Thin Driver communicates with the server using SQL*Net to access the database.
Oracle Database JDBC Developer’s Guide
Action Item 1: Change the DB_URL to point to your database. If you need help, refer to the DataSourceSample.java on Github.
import java.sql.Connection; import java.sql.SQLException; import java.sql.DatabaseMetaData; import oracle.jdbc.pool.OracleDataSource; import oracle.jdbc.OracleConnection; public class DataSourceSample < // The recommended format of a connection URL is the long format with the // connection descriptor. // final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))"; final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=slc07qwu.us.oracle.com)(PORT=5521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=jvma.regress.rdbms.dev.us.oracle.com)))"; final static String DB_USER = "hr"; final static String DB_PASSWORD = "hr"; public static void main (String args[]) throws SQLException < OracleDataSource ods = new OracleDataSource(); ods.setURL(DB_URL); ods.setUser(DB_USER); ods.setPassword(DB_PASSWORD); // With AutoCloseable, the connection is closed automatically. try (OracleConnection connection = (OracleConnection) ods.getConnection()) < // Get the JDBC driver name and version DatabaseMetaData dbmd = connection.getMetaData(); System.out.println("Driver Name: " + dbmd.getDriverName()); System.out.println("Driver Version: " + dbmd.getDriverVersion()); System.out.println("Database Username is: " + connection.getUserName()); >> > Universal Connection Pool
Connection pools help improve performance by reusing connection objects and reducing the number of times that connection objects are created.
Oracle Universal Connection Pool (UCP) is a feature rich Java connection pool that provides connection pool functionalities, along with high availability, scalability and load balancing with the help of tighter integration with Oracle Database configurations.
A Java application or container must have ucp.jar in their classpath, along with the ojdbc8.jar (JDK8), to be able to use UCP.
Oracle Universal Connection Pool Developer’s Guide
Action Item 2: Change the DB_URL to point to your database. You can also refer to the UCPSample on Github for more information.
Import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import oracle.ucp.jdbc.PoolDataSourceFactory; import oracle.ucp.jdbc.PoolDataSource; public class UCPSample < // final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myorcldbservicename)))"; final static String DB_USER = "hr"; final static String DB_PASSWORD = "hr"; final static String CONN_FACTORY_CLASS_NAME = "oracle.jdbc.pool.OracleDataSource"; /* * The sample demonstrates UCP as client side connection pool. */ public static void main(String args[]) throws Exception < // Get the PoolDataSource for UCP PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource(); // Set the connection factory first before all other properties pds.setConnectionFactoryClassName(CONN_FACTORY_CLASS_NAME); pds.setURL(DB_URL); pds.setUser(DB_USER); pds.setPassword(DB_PASSWORD); pds.setConnectionPoolName("JDBC_UCP_POOL"); // Default is 0. Set the initial number of connections to be // created when UCP is started. pds.setInitialPoolSize(5); // Default is 0. Set the minimum number of connections // that is maintained by UCP at runtime. pds.setMinPoolSize(5); // Default is Integer.MAX_VALUE (2147483647). Set the maximum // number of connections allowed on the connection pool. pds.setMaxPoolSize(20); // Default is 30secs. Set the frequency in seconds to enforce // the timeout properties. Applies to // inactiveConnectionTimeout(int secs), // AbandonedConnectionTimeout(secs)& //TimeToLiveConnectionTimeout(int secs). // Range of valid values is 0 to Integer.MAX_VALUE. pds.setTimeoutCheckInterval(5); // Default is 0. Set the maximum time, in seconds, that a // connection remains available in the connection pool. pds.setInactiveConnectionTimeout(10); System.out.println("Available connections before checkout: " + pds.getAvailableConnectionsCount()); System.out.println("Borrowed connections before checkout: " + pds.getBorrowedConnectionsCount()); // Get the database connection from UCP. try (Connection conn = pds.getConnection()) < System.out.println("Available connections after checkout: " + pds.getAvailableConnectionsCount()); System.out.println("Borrowed connections after checkout: " + pds.getBorrowedConnectionsCount()); // Perform a database operation printEmployees(conn); >catch (SQLException e) < System.out.println("UCPSample - " + "SQLException occurred : " + e.getMessage()); >System.out.println("Available connections after checkin: " + pds.getAvailableConnectionsCount()); System.out.println("Borrowed connections after checkin: " + pds.getBorrowedConnectionsCount()); > /* * Displays first_name and last_name from the employees table. */ public static void printEmployees(Connection connection) throws SQLException < // Statement and ResultSet are AutoCloseable and closed // automatically. try (Statement statement = connection.createStatement()) < try (ResultSet resultSet = statement .executeQuery("select first_name, last_name from employees")) < System.out.println("FIRST_NAME" + " " + "LAST_NAME"); System.out.println("---------------------"); while (resultSet.next()) System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + " "); >> > > Java in the Database (OJVM)
Oracle Database has a Java Virtual Machine (JVM) that resides in the server. It helps Java applications running in the Oracle JVM on the server to access data present on the same system and same process.
Java in the Database is recommended for applications that are data-intensive. JVM has the ability to use the underlying Oracle RDBMS libraries directly, without the use of a network connection between the Java code and SQL data. This helps improve performance and execution. For data access, Oracle Database uses server-side internal driver when Java code runs on the server.
Action Item 3: Connect to the database through SQLPlus and run ServersideConnect.sql before invoking ServersideConnect.java. For more information, refer to the samples of Github.
Rem NAME Rem ServersideConnect.sql Rem Rem DESCRIPTION Rem SQL for invoking the method which gets a server side connection to rem Reads the content of the Java source from ServersideConnect.java rem then compiles it connect hr/hr CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED ServersideConnect_src AS @ ServersideConnect.java / show error rem A wrapper (a.k.a. Call Spec), to invoke Java rem function in the database from SQL, PL/SQL, and client applications CREATE OR REPLACE PROCEDURE ServersideConnect_proc AS LANGUAGE JAVA NAME 'ServersideConnect.jrun ()'; / rem running the sample connect hr/hr SET SERVEROUTPUT ON SIZE 10000 CALL dbms_java.set_output (10000); execute ServersideConnect_proc; InternalT2Server.java import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import oracle.jdbc.driver.OracleDriver; import oracle.jdbc.pool.OracleDataSource; public class ServersideConnect < static public void jrun() throws SQLException < // For testing ServersideConnect // test("jdbc:oracle:kprb:@"); method1("jdbc:default:connection"); method2(); >/* * Shows using the server side Type 2 driver a.k.a KPRB driver */ static public void method1 (String url) throws SQLException < Connection connection = null; try < // Method 1: Using OracleDataSource OracleDataSource ods = new OracleDataSource(); ods.setURL(url); connection = ods.getConnection(); System.out.println("Method 1: Getting Default Connection " + "using OracleDataSource"); // Perform database operation printEmployees(connection); >> static public void method2 () throws SQLException < Connection connection = null; try < OracleDriver ora = new OracleDriver(); connection = ora.defaultConnection(); System.out.println("Method 2: Getting Default Connection " + "using OracleDriver"); // Perform database operation printEmployees(connection); >> /* * Displays employee_id and first_name from the employees table. */ static public void printEmployees(Connection connection) throws SQLException < ResultSet resultSet = null; Statement statement = null; try < statement = connection.createStatement(); resultSet = statement.executeQuery("SELECT employee_id, first_name" + " FROM employees order by employee_id"); while (resultSet.next()) < System.out.println("Emp no: " + resultSet.getInt(1) + " Emp name: " + resultSet.getString(2)); >> catch (SQLException ea) < System.out.println("Error during execution: " + ea); ea.printStackTrace(); >finally < if (resultSet != null) resultSet.close(); if (statement != null) statement.close(); >> >