blogger templates blogger widgets
This is part of a list of blog posts.
To browse the contents go to

JDBC connections using DataSouce (framework implementation)

We could use commons-dbcp as our library of choice.

Approach I - using BasicDataSource

package com.prodapps;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.dbcp.BasicDataSource;

public class PoolingDataSourceExample {

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

  Connection conn = null;
  try {
   BasicDataSource ds = new BasicDataSource();
   ds.setDriverClassName("oracle.jdbc.OracleDriver");
   ds.setUsername("SYSTEM");
   ds.setPassword("password");
   ds.setUrl("jdbc:oracle:thin:@localhost:1521:XE");
   ds.setInitialSize(1);
   ds.setMaxActive(50);
   ds.setDefaultAutoCommit(false);
   
   conn = ds.getConnection();
   Statement st = conn.createStatement();
   ResultSet rs = st.executeQuery("select 777 from dual");

   while (rs.next()) {
    System.out.println(rs.getString(1));
    ;
   }
   rs.close();
   st.close();
  } finally {
   if (conn != null)
    try {
     conn.close();
    } catch (Exception ignore) {
    }
  }
 }
}

Trying it out as a Web application is left to the reader.
Note that connection pooling capability is inherently added to the BasicDataSource implementation.

Approach II - using PoolingDataSource

package com.prodapps;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

//
// Here are the dbcp-specific classes.
// Note that they are only used in the setupDataSource
// method. In normal use, your classes interact
// only with the standard JDBC API
//
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;

public class ManualPoolingDataSourceExample {

 public static void main(String[] s) {
  
  //
  // First we load the underlying JDBC driver.
  // You need this if you don't use the jdbc.drivers
  // system property.
  //
  System.out.println("Loading underlying JDBC driver.");
  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
  System.out.println("Done.");

  //
  // Then, we set up the PoolingDataSource.
  // Normally this would be handled auto-magically by
  // an external configuration, but in this example we'll
  // do it manually.
  //
  System.out.println("Setting up data source.");
  DataSource dataSource = setupDataSource("jdbc:oracle:thin:SYSTEM/password@localhost:1521:XE");
  System.out.println("Done.");

  //
  // Now, we can use JDBC DataSource as we normally would.
  //
  Connection conn = null;
  Statement stmt = null;
  ResultSet rset = null;

  try {
   System.out.println("Creating connection.");
   conn = dataSource.getConnection();
   System.out.println("Creating statement.");
   stmt = conn.createStatement();
   System.out.println("Executing statement.");
   rset = stmt.executeQuery("select 777 from dual");
   System.out.println("Results:");
   int numcols = rset.getMetaData().getColumnCount();
   while (rset.next()) {
    for (int i = 1; i <= numcols; i++) {
     System.out.print("\t" + rset.getString(i));
    }
    System.out.println("");
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   try {
    rset.close();
   } catch (Exception e) {
   }
   try {
    stmt.close();
   } catch (Exception e) {
   }
   try {
    conn.close();
   } catch (Exception e) {
   }
  }
 }

 public static DataSource setupDataSource(String connectURI) {
  //
  // First, we'll need a ObjectPool that serves as the
  // actual pool of connections.
  //
  // We'll use a GenericObjectPool instance, although
  // any ObjectPool implementation will suffice.
  //
  ObjectPool connectionPool = new GenericObjectPool(null);

  //
  // Next, we'll create a ConnectionFactory that the
  // pool will use to create Connections.
  // We'll use the DriverManagerConnectionFactory,
  // using the connect string passed in the command line
  // arguments.
  //
  ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
    connectURI, null);

  //
  // Now we'll create the PoolableConnectionFactory, which wraps
  // the "real" Connections created by the ConnectionFactory with
  // the classes that implement the pooling functionality.
  //
  PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
    connectionFactory, connectionPool, null, null, false, true);

  //
  // Finally, we create the PoolingDriver itself,
  // passing in the object pool we created.
  //
  PoolingDataSource dataSource = new PoolingDataSource(connectionPool);

  return dataSource;
 }
}
The web application version is available for download here

Continue reading: JDBC connections using Datasource (container implementation)

No comments:

Post a Comment