Database Connection Pool Code Example with Apache DBCP and Oracle 10g

1) Download the Apache Commons DBCP and Commons Pool jars from the link here and add them to the classpath / build path of the Java Application / Project.

2) Add the jboss-j2ee.jar file to the class path/build path of the Java Application/Project. The jboss-j2ee.jar file is present in the $JBOSS_HOME/server/default/lib folder of the JBoss Installation folder.

3) Complete the JDBC example at the link here

4) Create a java class - ConnectionPoolExample.java - in the src folder of the Java Application / Project

package info.icontraining.jdbc;

import java.sql.*;
import javax.sql.*;
import org.apache.commons.pool.*;
import org.apache.commons.pool.impl.*;
import org.apache.commons.dbcp.*;

public class ConnectionPoolExample {
 
   private static GenericObjectPool genericPool = new GenericObjectPool();

   public static void main(String[] args) throws Exception {

      try {
         Class.forName("oracle.jdbc.OracleDriver");
      } catch (ClassNotFoundException e) {
         e.printStackTrace();
      }

      DataSource dataSource = setupDataSource("jdbc:oracle:thin:system/system@localhost:1521:xe");

      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;

      try {
         conn = dataSource.getConnection();
         stmt = conn.createStatement();
         rs = stmt.executeQuery("SELECT * FROM DUAL");
            
         while(rs.next()) {
            for(int i=1; i<=rs.getMetaData().getColumnCount(); i++) {
               System.out.println(rs.getString(i));
            }
         }
            
         System.out.println("Active Connections: " + genericPool.getNumActive() + ", Idle Connections: " + genericPool.getNumIdle());
            
      } catch(SQLException e) {
         e.printStackTrace();
      } finally {
         try { if (rs != null) rs.close(); } catch(Exception e) { }
         try { if (stmt != null) stmt.close(); } catch(Exception e) { }
         try { if (conn != null) conn.close(); } catch(Exception e) { }
      }
        
      System.out.println("Active Connections: " + genericPool.getNumActive() + ", Idle Connections: " + genericPool.getNumIdle());
   }

   public static DataSource setupDataSource(String connectURI) throws Exception {
      ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,null);
      KeyedObjectPoolFactory kopf =new GenericKeyedObjectPoolFactory(null);

      PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory( connectionFactory, genericPool, kopf, null, false, true);

      for(int i = 0; i < 5; i++) {
         genericPool.addObject();
      }

      System.out.println("Active Connections: " + genericPool.getNumActive() + ", Idle Connections: " + genericPool.getNumIdle());
        
      PoolingDataSource dataSource = new PoolingDataSource(genericPool);
      return dataSource;
   }
}

5) Run the code as a standalone Java Application

JDBC Code to read BLOB data type from database table

0) Complete the example to store an image (BLOB data) to a table at the link here

1) Create a Java class - JdbcBlobReader.java - in the src folder of the Java Project / application

package info.icontraining.jdbc;

import java.io.*;
import java.sql.*;

public class JdbcBlobReader {
 
   public static void main(String[] args) throws Exception, IOException, SQLException {
   
      Class.forName("oracle.jdbc.OracleDriver");
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");

      PreparedStatement pstmt = conn.prepareStatement("Select name, image from MyImages");
      ResultSet rs = pstmt.executeQuery();
      
      while (rs.next()) {
         String name = rs.getString(1);
         String description = rs.getString(2);
         File image = new File("src/icon-training-downloaded.jpg");
         FileOutputStream fos = new FileOutputStream(image);

         byte[] buffer = new byte[1];
         InputStream is = rs.getBinaryStream(2);

         while (is.read(buffer) > 0) {
            fos.write(buffer);
         }

         fos.close();
      }

      conn.close();    
   }
}

2) Run the code as a standalone Java Application

JDBC Code to insert an image in a BLOB column

0) Complete the first 5 steps in the JDBC code example at the link here

1) Create a new table in the database using the following CREATE TABLE command

create table MyImages (
   name VARCHAR(1000),
   image BLOB
);

2) Add the image file to the src folder of the Java Project/Application

3) Create a Java class - JdbcBlobExample.java - to the src folder of the Java Project/Application

package info.icontraining.jdbc;

import java.io.*;
import java.sql.*;

public class JdbcBlobExample {
 
   public static void main(String[] args) throws Exception, IOException, SQLException {
   
      Class.forName("oracle.jdbc.OracleDriver");
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");

      FileInputStream fis = null;
      PreparedStatement pstmt = null;
    
      try {
         conn.setAutoCommit(false);
      
         File file = new File("src/icon-training.jpg");
         fis = new FileInputStream(file);
      
         pstmt = conn.prepareStatement("insert into MyImages(name, image) values (?, ?)");
      
         pstmt.setString(1, "Icon Training Logo");
         pstmt.setBinaryStream(2, fis, (int) file.length());
      
         pstmt.executeUpdate();
         conn.commit();
      
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         pstmt.close();
         fis.close();
      }
   }
}

4) Run the code as a standalone Java Application

JDBC Transaction Management Code Example

0) Complete the first 6 steps in the JDBC Code example at the link here

1) Create a Java class - JdbcTransactionMgmt.java - in the src folder of the Java Project /Application

package info.icontraining.jdbc;

import java.sql.*;

public class JdbcTransactionMgmt {

   public static void main(String[] args) throws SQLException, ClassNotFoundException {

      Class.forName("oracle.jdbc.OracleDriver");
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
      PreparedStatement pstmt = null;
  
      conn.setAutoCommit(false); // start of transaction
  
      try {
         pstmt = conn.prepareStatement("insert into jdbcdemo values (?, ?, ?, ?)");

         pstmt.setString(1, "Ramesh");
         pstmt.setString(2, "PC");
         pstmt.setInt(3, 32);

         Date d = new Date(0);
         pstmt.setDate(4, d.valueOf("1978-9-28"));

         int rowsAffected = pstmt.executeUpdate();
         System.out.println(rowsAffected + " rows affected.");

         // deliberately cause an exception to be thrown
         int i = 3/0;    // comment this line for commit to happen 

         pstmt.setString(1, "Gunjan");
         pstmt.setString(2, "C");
         pstmt.setInt(3, 26);
         pstmt.setDate(4, d.valueOf("1984-10-30"));

         rowsAffected = pstmt.executeUpdate();
         System.out.println(rowsAffected + " rows affected.");

         conn.commit();

         pstmt.close();

      } catch (Exception e) {
         conn.rollback();
         System.err.println("Caught Exception & transaction rollbacked");
      }

      conn.close(); 
   }
}

2) Run the code as a standalone Java application.
Since the code throws an exception, which is caught, and the transaction is rolled-back, no changes reflect in the database table, even though one row was, apparently, successfully inserted.

3) Comment the line that deliberately throws an exception and run the code as a standalone Java application.
Now, both rows are inserted because the transaction gets committed successfully.

JDBC Prepared Statement Code Example

0) Complete the first 6 steps in the JDBC Code Example at the link here

1) Create a java class - JdbcPSExample.java - to the src folder of the Java Project / Application

package info.icontraining.jdbc;

import java.sql.*;

public class JdbcPSExample {

   public static void main(String[] args) throws SQLException, ClassNotFoundException {
  
      Class.forName("oracle.jdbc.OracleDriver");
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
      PreparedStatement pstmt = conn.prepareStatement("insert into jdbcdemo values (?, ?, ?, ?)");
  
      pstmt.setString(1, "Dinesh");
      pstmt.setString(2, "PC");
      pstmt.setInt(3, 32);
  
      Date d = new Date(0);
      pstmt.setDate(4, d.valueOf("1978-9-28"));

      int rowsAffected = pstmt.executeUpdate();
      System.out.println(rowsAffected + " rows affected.");

      pstmt.setString(1, "Tanvi");
      pstmt.setString(2, "C");
      pstmt.setInt(3, 26);
      pstmt.setDate(4, d.valueOf("1984-10-30"));
  
      rowsAffected = pstmt.executeUpdate();
      System.out.println(rowsAffected + " rows affected.");

      pstmt.close();
      conn.close(); 
   }
}

3) Run the Example as a standalone Java Application

JDBC Code Example

1) Download and install Oracle 10g Express edition from the following link (note down the admin password chosen during installation).
Verify that the installation was successful - in the Start > Programs > 'Oracle Database 10g Express Edition' should show up.

2) Start the Oracle 10g Services by following the steps,

- Go to Start > Run

- Type services.msc and click OK

- In the Services window find the 5 Oracle services and start all of them, starting with the OracleServiceXE service

3) Change Oracle's Web Manager Application port number:
- Open the SQL*Plus Command Prompt window
- Type the command: connect
- Log in as user 'system' and type the password chosen during installation
- Execute the following command at the SQL prompt, (where 8090 is the new port number),

SQL> EXEC DBMS_XDB.SETHTTPPORT(8090);

4) Add the ojdbc14.jar file from the following path of the installed Database folder:
C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib into the build path of the Java Application

5) The Oracle JDBC driver class is oracle.jdbc.OracleDriver and the JDBC connection string to be used in the DriverManager.getConnection() method is jdbc:oracle:thin:@localhost:1521:xe

Note: This information is procured from vendor documentation, in this case, Oracle 10g documentation.

6) At the SQL command prompt, paste the following SQL statement to create a new table,

create table jdbcdemo ( 
    firstname varchar2(25),
    lastname varchar2(25),
    age number(2),
    dob date
);

Next, at the SQL prompt, execute the following command, 

commit;

7) Create a java class, JDBCdemo.java and add the following code,

package info.icontraining.jdbc;

import java.sql.*;

public class JDBCdemo {

   public static void main(String[] args) throws SQLException, ClassNotFoundException {

      Class.forName("oracle.jdbc.OracleDriver");
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "system");
      Statement stmt = conn.createStatement();
  
      stmt.executeUpdate("insert into jdbcdemo values ('Dinesh', 'PC', 32, '1-JAN-1978')");
      stmt.executeUpdate("insert into jdbcdemo values ('Tanvi', 'DC', 27, '1-JAN-1984')");
      stmt.executeUpdate("insert into jdbcdemo values ('Laksh', 'DC', 2, '1-MAY-2009')");
  
      ResultSet rs = stmt.executeQuery("select * from jdbcdemo");
  
      while (rs.next()) {  
         System.out.println("Name: " + rs.getString(1) + " " + rs.getString(2));
         System.out.println("Age: " + rs.getInt("age"));
         System.out.println("DOB: " + rs.getDate(4).toString());
         System.out.println();
      }
  
      rs.close();
      stmt.close();
      conn.close();  
   }
}

8) Run the code as a standalone Java application