Configure Oracle DataSource Using Commons DBCP

Creating Oracle Database connection pool is very simple using Commons DBCP.Simply include these jars in your class path :

commons-dbcp-1.4.jar
commons-pool-1.5.4.jar
ojdbc6.jar

ojdbc6.jar file can be found at ${Your oracle installation Dir}\product\11.2.0\dbhome_1\jdbc\lib . Here is the java file to get oracle Database connections :

package com.datamigration.db;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

/**
 * 
 * 
 * @author Abhishek Somani
 * 
 */
public class DataBase {
 
 private String driverClassName="oracle.jdbc.driver.OracleDriver" ;
 private String userName="usr";
 
 private String password="pass";
 
 private static int MAX_ACTIVE= 10;
 
 private String url="jdbc:oracle:thin:@localhost:1521:orcl";
 
 
 private BasicDataSource ds = null;
 
 public void init() throws SQLException
 {
  ds = new BasicDataSource();
  ds.setDriverClassName(driverClassName);
  ds.setPassword(password);
  ds.setUsername(userName);
  ds.setUrl(url);
  ds.setMaxActive(MAX_ACTIVE);
  //check connections
  ds.getConnection();
  
 }
 
 
 public Connection getConnection() throws SQLException
 {
  return ds.getConnection();
 }


 public String getDriverClassName() {
  return driverClassName;
 }


 public void setDriverClassName(String driverClassName) {
  this.driverClassName = driverClassName;
 }


 public String getUserName() {
  return userName;
 }


 public void setUserName(String userName) {
  this.userName = userName;
 }


 public String getPassword() {
  return password;
 }


 public void setPassword(String password) {
  this.password = password;
 }


 public String getUrl() {
  return url;
 }


 public void setUrl(String url) {
  this.url = url;
 }
 
 

}

One Common Error :

java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
 at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
 at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)

This error occurs when you try to use oracle reserved keyword in your query . For example query like

Select * from Usr where NUMBER=?

Here Number is keyword in oracle , so you have to use escape sequence. Escape sequence in oracle is double quotes , so just write the query like this

Select * from Usr where "NUMBER"=?

Post Comments and Suggestions !!!