c3p0 jdbc dbcp tomcatjdbc 乐观锁 高并发测试 4000 以上

/* @author su jian ping * test sql * create table LOCK_TEST ( ID NUMBER not null, DES VARCHAR2(250), VERSION NUMBER not null ); alter table LOCK_TEST add constraint PK_LOCK_TEST primary key (ID); */ package com; import java.beans.PropertyVetoException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSource; import org.apache.tomcat.jdbc.pool.PoolProperties; import com.mchange.v2.c3p0.ComboPooledDataSource; public class OptimisticLock { //public static ComboPooledDataSource pool = c3p0(); //public static DataSource pool = dbcp(); //public static DataSource pool = tomcatJdbc(); public final static Object POOL = defaultPool(); public static Object defaultPool(){ try { return getPool(CONN_ENUM.C3P0); } catch (PropertyVetoException e) { e.printStackTrace(); } return null; } public static Object getPool(CONN_ENUM CONN) throws PropertyVetoException{ Object pool = null; switch (CONN){ case TOMCATJDBC : pool = tomcatJdbc(); break; case DBCP : pool = dbcp(); break; default : pool = c3p0(); } return pool; } public static enum CONN_ENUM {JDBC,TOMCATJDBC,C3P0,DBCP}; public static Connection jdbc() throws ClassNotFoundException, SQLException{ String url = "jdbc:oracle:thin:@192.168.0.168:1521:orcl"; String user = "cisadm"; String password = "cisadm"; String className = "oracle.jdbc.driver.OracleDriver"; Class.forName(className); Connection conn = null; conn = DriverManager.getConnection(url, user, password); return conn; } public static ComboPooledDataSource c3p0() throws PropertyVetoException{ ComboPooledDataSource cpds = new ComboPooledDataSource(); cpds.setDriverClass( "oracle.jdbc.driver.OracleDriver" ); //loads the jdbc driver cpds.setJdbcUrl( "jdbc:oracle:thin:@192.168.0.168:1521:orcl" ); cpds.setUser("cisadm"); cpds.setPassword("cisadm"); cpds.setMinPoolSize(5); cpds.setAcquireIncrement(5); cpds.setMaxPoolSize(100); return cpds; } public static DataSource dbcp() { BasicDataSource ds = new BasicDataSource(); ds.setDriverClassName("oracle.jdbc.driver.OracleDriver"); ds.setUsername("cisadm"); ds.setPassword("cisadm"); ds.setUrl("jdbc:oracle:thin:@192.168.0.168:1521:orcl"); return ds; } public static DataSource tomcatJdbc() { PoolProperties p = new PoolProperties(); p.setUrl("jdbc:oracle:thin:@192.168.0.168:1521:orcl"); p.setDriverClassName("oracle.jdbc.driver.OracleDriver"); p.setUsername("cisadm"); p.setPassword("cisadm"); p.setJmxEnabled(true); p.setTestWhileIdle(false); p.setTestOnBorrow(true); p.setValidationQuery("SELECT 1"); p.setTestOnReturn(false); p.setValidationInterval(30000); p.setTimeBetweenEvictionRunsMillis(30000); p.setMaxActive(100); p.setInitialSize(10); p.setMaxWait(10000); p.setRemoveAbandonedTimeout(60); p.setMinEvictableIdleTimeMillis(30000); p.setMinIdle(10); p.setLogAbandoned(true); p.setRemoveAbandoned(true); p.setJdbcInterceptors( "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+ "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"); org.apache.tomcat.jdbc.pool.DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource(); ds.setPoolProperties(p); return ds; } public static Connection getConnection(Object _pool) throws SQLException{ Connection conn = null; if(_pool instanceof ComboPooledDataSource){ ComboPooledDataSource pool = (ComboPooledDataSource)_pool; conn = pool.getConnection(); }else{ DataSource pool = (DataSource)_pool; conn = pool.getConnection(); } return conn; } public static void prepareDatafortest() throws SQLException{ Connection conn = null; try { conn = getConnection(POOL); int id = 111; //clear the data if exist DaoTask.deleteLockTest(id, conn); DaoTask.insertLockTest(id , "test" , 1,conn ); } finally{ if(conn != null) conn.close(); } } public static class Lock_test{ private int id; private String desc; private int version; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getDesc() { return desc; } public void setDesc(String desc) { this.desc = desc; } public int getVersion() { return version; } public void setVersion(int version) { this.version = version; } } public static class DaoTask implements Runnable { private int num; public DaoTask(int num){ this.num = num; } public void run() { Connection conn = null; try { conn = getConnection(POOL); conn.setAutoCommit(false); Lock_test locktest = getLockTestByid(111 ,conn); if(locktest !=null){ // we expect the commit order is in order, if the same version commit in the same time // one can be success full. if(updateLockTest(111 , "test"+num , locktest.getVersion() ,conn)>0){ conn.commit(); System.out.println("commit time " +System.currentTimeMillis()+ " task "+num + " commit action" +" current version "+locktest.getVersion()+" expect version "+(locktest.getVersion()+1)); }else{ conn.rollback(); System.out.println("rollback time " +System.currentTimeMillis()+ " task "+num + " rollback action" +" current version "+locktest.getVersion()+" expect version "+(locktest.getVersion()+1) ); } } } catch (SQLException e) { e.printStackTrace(); }finally{ if(conn != null){ try { conn.rollback(); conn.close(); } catch (SQLException e1) { e1.printStackTrace(); } } } } public static int updateLockTest(int id , String desc , int oldversion , Connection conn) throws SQLException { // update the version+1 flag and the where clause version = ? to detect the concurrency. String sql = "update lock_test set des =?, version = ? where id =? and version = ?"; PreparedStatement stmt = null; try{ stmt = conn.prepareStatement(sql); stmt.setString(1, desc); stmt.setInt(2, oldversion+1); stmt.setInt(3, id); stmt.setInt(4, oldversion); return stmt.executeUpdate(); }finally{ if(stmt !=null) { stmt.close(); } } } public static Lock_test getLockTestByid(int id , Connection conn) throws SQLException{ String sql = "select * from lock_test a where a.id = ?"; PreparedStatement stmt = conn.prepareStatement(sql); stmt.setInt(1, id); ResultSet rs = stmt.executeQuery(); try{ if(rs.next()){ Lock_test lockTest = new Lock_test(); lockTest.setId(rs.getInt(1)); lockTest.setDesc(rs.getString(2)); lockTest.setVersion(rs.getInt(3)); return lockTest; } }finally{ if(rs !=null) { rs.close(); } if(stmt !=null) { stmt.close(); } } return null; } public static int deleteLockTest(int id,Connection conn) throws SQLException { String sql = "delete from lock_test where id =? "; PreparedStatement stmt = null; try{ stmt = conn.prepareStatement(sql); stmt.setInt(1, id); return stmt.executeUpdate(); }finally{ if(stmt !=null) { stmt.close(); } } } public static int insertLockTest(int id , String desc , int version ,Connection conn) throws SQLExceptio