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.jarojdbc6.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 !!!