Java access database jdbc

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(); >> >

Источник

Lesson: JDBC Introduction

The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a relational database.

JDBC helps you to write Java applications that manage these three programming activities:

Connect to a data source, like a database
Send queries and update statements to the database
Retrieve and process the results received from the database in answer to your query

The following simple code fragment gives a simple example of these three steps:

public void connectToAndQueryDatabase(String username, String password) < Connection con = DriverManager.getConnection( "jdbc:myDriver:myDatabase", username, password); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1"); while (rs.next()) < int x = rs.getInt("a"); String s = rs.getString("b"); float f = rs.getFloat("c"); >>

This short code fragment instantiates a DriverManager object to connect to a database driver and log into the database, instantiates a Statement object that carries your SQL language query to the database; instantiates a ResultSet object that retrieves the results of your query, and executes a simple while loop, which retrieves and displays those results. It’s that simple.

JDBC Product Components

JDBC includes four components:

The JDBC API — The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment. The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE ) and the Java™ Enterprise Edition (Java™ EE). The JDBC 4.0 API is divided into two packages: java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.
JDBC Driver Manager — The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple. The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.
JDBC Test Suite — The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.
JDBC-ODBC Bridge — The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.

This Trail uses the first two of these four JDBC components to connect to a database and then build a java program that uses SQL commands to communicate with a test relational database. The last two components are used in specialized environments to test web applications, or to communicate with ODBC-aware DBMSs.

Источник

Lesson: JDBC Introduction

The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a relational database.

JDBC helps you to write Java applications that manage these three programming activities:

Connect to a data source, like a database
Send queries and update statements to the database
Retrieve and process the results received from the database in answer to your query

The following simple code fragment gives a simple example of these three steps:

public void connectToAndQueryDatabase(String username, String password) < Connection con = DriverManager.getConnection( "jdbc:myDriver:myDatabase", username, password); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1"); while (rs.next()) < int x = rs.getInt("a"); String s = rs.getString("b"); float f = rs.getFloat("c"); >>

JDBC Product Components

JDBC includes four components:

The JDBC API — The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment. The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE ) and the Java™ Enterprise Edition (Java™ EE). The JDBC 4.0 API is divided into two packages: java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.
JDBC Driver Manager — The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple. The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.
JDBC Test Suite — The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.
JDBC-ODBC Bridge — The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.

Источник