C3P0 connection pool configuration

<bean id="oracle.dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"></property> <property name="jdbcUrl" value="jdbc:oracle:thin:testdb"/> <property name="user" value="sa"/> <property name="password" value=""/> <property name="initialPoolSize" value="5"></property> <property name="minPoolSize" value="5"></property> <property name="maxPoolSize" value="15"/> <property name="checkoutTimeout" value="1000"/> <property name="preferredTestQuery" value="select 1 from dual"/> <property name="maxStatements" value="50"></property> <property name="testConnectionOnCheckin" value="true"></property> <property name="idleConnectionTestPeriod" value="60"></property> </bean>

Spring JDBC Template Sample

<?xml version="1.0" encoding="UTF-8"?> <!--<beans xmlns="http://www.springframework.org/schema/beans"--> <!--xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"--> <!--xmlns:jdbc="http://www.springframework.org/schema/jdbc"--> <!--xmlns:context="http://www.springframework.org/schema/context"--> <!--xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd--> <!--http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd--> <!--http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">--> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation=" http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <!--<context:property-placeholder location="classpath:/eventjob-postgresql.properties"/>--> <!--<bean id="postgresql.dataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">--> <!--<property name="driverClassName" value="${jdbc.driverClassName}"/>--> <!--<property name="url" value="${datasource.url}"/>--> <!--<property name="username" value="${datasource.username}"/>--> <!--<property name="password" value="${datasource.password}"/>--> <!--<property name="suppressClose" value="true"/>--> <!--</bean>--> <bean id="postgresql.dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="org.postgresql.Driver"/> <property name="url" value="jdbc:postgresql://localhost:5432/ProductReviews"/> <property name="username" value="postgres"/> <property name="password" value="99a59csWS"/> <property name="initialSize" value="10"/> <property name="maxActive" value="20"/> <property name="maxIdle" value="2"/> </bean> <!--<bean id="postgresql.dataSource" class="org.apache.commons.dbcp.BasicDataSource"--> <!--destroy-method="close">--> <!--<property name="driverClassName" value="${jdbc.driverClassName}"/>--> <!--<property name="url" value="${datasource.url}"/>--> <!--<property name="username" value="${datasource.username}"/>--> <!--<property name="password" value="${datasource.password}"/>--> <!--<property name="initialSize" value="10"/>--> <!--<property name="maxActive" value="20"/>--> <!--<property name="maxIdle" value="2"/>--> <!--</bean>--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg ref="postgresql.dataSource"/> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="postgresql.dataSource"/> </bean> <bean id="dao" class="dao.KohlsOrderStatusEventDAOImpl"> <property name="jdbcTemplate" ref="jdbcTemplate"/> </bean> <bean id="cosee" class="CompleteOrderStatusEventExecutor"> <property name="dao" ref="dao"/> </bean> <bean id="Job" class="Job"> <property name="executor"> <list> <ref bean="cosee"/> </list> </property> </bean> </beans>

JDBC Example

Class.forName("org.postgresql.Driver"); // load database interface // connect to the database conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/ProductReviews", "postgres", "abcd"); stmt = conn.createStatement(); //st=conn.createStatement(); Date dat = new Date(System.currentTimeMillis()); ResultSet res = stmt.executeQuery( // send the query "select obj_id from object where obj_status_cd='C' and obj_modify_dt >= '18-DEC-10' and obj_modify_dt < '19-DEC-10' order by obj_id"); PreparedStatement pstmt = conn.prepareStatement("insert into event values(?,'C',?)"); if (res != null) while (res.next()) { String id = res.getString(1); //st.executeUpdate("insert into event values("+id+",'C','19-DEC-10')"); //Double d = new Double("2.9"); System.out.println("ID is: "+id); BigDecimal d = new BigDecimal(id); System.out.println("ID in double is: "+d); pstmt.setBigDecimal(1, d); java.sql.Date sqlDate = new java.sql.Date(new java.util.Date(System.currentTimeMillis()).getTime()); pstmt.setDate(2, sqlDate); pstmt.executeUpdate(); System.out.println(id); } res.close(); stmt.close(); pstmt.close(); conn.close();