Development of Oracle Database-intensive practical applications

We all know that when we re-develop Oracle database-intensive practical applications, we will benefit from using the relevant connection pool. The reason is that we can reuse the connection, instead of re-creating a new connection every time we request a connection. The connection pool saves the resources required to create a new database connection and improves the performance of applications, because creating a new connection is always a performance-intensive operation.

Oracle Universal Connection Pool for JDBC indicates a full-featured implementation for caching JDBC connections. UCP is a very useful feature that allows you to reuse the connection object, which can speed up the connection process and save the resources required to open a new database connection.

Assume that you want to create a ucp jdbc connection pool to reuse the established connection in the HR/HR Oracle Database sample mode. The following is a simple example of the ucp jdbc connection pool running. It will show you how to complete this operation. You will first create a data source instance that supports the pool, and then set the connection and pool attributes. After that, you will borrow a connection from the pool and then use the connection to interact with the database. Finally, you will close the connection and return it to the pool.

 
 

  
  
import java.sql.*; import oracle.ucp.jdbc.PoolDataSourceFactory; 
import oracle.ucp.jdbc.PoolDataSource; public class UcpConnection 
{ public static void main(String args[]) throws SQLException 
{ try { //Creating a pool-enabled data source PoolDataSource pds 
= PoolDataSourceFactory.getPoolDataSource(); 
//Setting connection properties of the data source pds.
setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource"); 
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE"); pds.setUser("hr"); 
pds.setPassword("hr"); //Setting pool properties pds.setInitialPoolSize(5); 
pds.setMinPoolSize(5); pds.setMaxPoolSize(10); 
//Borrowing a connection fro th oo Connection con = pds.getConnection();  
  
  
ount(); System.out.println("\nAvailable connections: 
" + avlConnCount); int brwConnCount = pds.getBorrowedConnectionsCount(); 
System.out.println("\nBorrowed connections: " + brwConnCount); 
//Working with the connection Statement stmt = conn.createStatement(); 
ResultSet rs = stmt.executeQuery("select user from dual"); 
while(rs.next()) System.out.println("\nConnected as: "+rs.getString(1)); 
rs.close(); //Returning the connection to the pool conn.close(); conn=null; 
System.out.println("\nConnection returned to the pool"); 
//Checking the number of available and borrowed connections again avlConnCount = 
pds.getAvailableConnectionsCount(); 
System.out.println("\nAvailable connections: " + avlConnCount); 
brwConnCount = pds.getBorrowedConnectionsCount(); 
System.out.println("\nBorrowed connections: " + brwConnCount); } 
catch(SQLException e) 
{ System.out.println("\nAn SQL exception occurred : " + e.getMessage()); } } } 
 
 

It is worth noting the changes when the connection is closed. The output of the above program illustrates that disabling a connection borrowed from the ucp jdbc connection pool will return the connection to the pool for the next connection request.

The output of the application should be as follows:

 
 

  
  
Connection borrowed from the poolAvailable connections: 
4Borrowed connections: 1Connected as: HRConnection returned 
to the poolAvailable connections: 5Borrowed connections: 0 
 
 

The above content describes how to use the UCP cache JDBC connection to develop Oracle database-intensive applications. I hope it will help you in this regard.