c3p0数据库连接池问题
由于以前做的项目没用mysql是用自己的连接池. 新启一个工程用到mysql,mysql对于连接超过8小时,就会断开连接. 在网上查了下,说c3p0可以解决该问题. 找了点资料,配置后写代码测试,发觉c3p0也并没有提高效率,难道是我代码写错了. 因为c3p0每次都得释放连接,感觉用不用作用不太大.
import java.beans.PropertyVetoException;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;import javax.sql.rowset.CachedRowSet;import com.mchange.v2.c3p0.ComboPooledDataSource;import com.sun.rowset.CachedRowSetImpl;import com.zhongwei.connectionpool.ConnectionPool;public class ConnectionManager{ private static ConnectionManager instance; private static ComboPooledDataSource dataSource; private ConnectionManager() throws IOException, SQLException, PropertyVetoException { Properties p = new Properties(); p.load(this.getClass().getResourceAsStream("c3p0.properties")); dataSource = new ComboPooledDataSource(); dataSource.setProperties(p); System.out.println(p.getProperty("password")); dataSource.setUser(p.getProperty("user")); dataSource.setPassword(p.getProperty("password")); dataSource.setJdbcUrl(p.getProperty("jdbcUrl")); dataSource.setDriverClass(p.getProperty("driverClass")); dataSource.setInitialPoolSize(Integer.valueOf(p.getProperty("initialPoolSize"))); dataSource.setMinPoolSize(Integer.valueOf(p.getProperty("minPoolSize"))); dataSource.setMaxPoolSize(Integer.valueOf(p.getProperty("maxPoolSize"))); dataSource.setMaxStatements(Integer.valueOf(p.getProperty("maxStatements"))); dataSource.setMaxIdleTime(Integer.valueOf(p.getProperty("maxIdleTime"))); dataSource.setPreferredTestQuery(p.getProperty("preferredTestQuery")); dataSource.setIdleConnectionTestPeriod(Integer.valueOf(p.getProperty("idleConnectionTestPeriod"))); dataSource.setTestConnectionOnCheckout(Boolean.valueOf(p.getProperty("testConnectionOnCheckout"))); dataSource.setTestConnectionOnCheckin(Boolean.valueOf(p.getProperty("testConnectionOnCheckin"))); dataSource.setAcquireIncrement(Integer.valueOf(p.getProperty("acquireIncrement"))); dataSource.setAcquireRetryAttempts(Integer.valueOf(p.getProperty("acquireRetryAttempts"))); dataSource.setAcquireRetryDelay(Integer.valueOf(p.getProperty("acquireRetryDelay"))); dataSource.setAutomaticTestTable(p.getProperty("automaticTestTable")); dataSource.setCheckoutTimeout(Integer.valueOf(p.getProperty("checkoutTimeout"))); dataSource.setAutoCommitOnClose(Boolean.valueOf(p.getProperty("autoCommitOnClose"))); } public static final ConnectionManager getInstance() { if (instance == null) { try { instance = new ConnectionManager(); } catch (Exception e) { e.printStackTrace(); } } return instance; } public synchronized final Connection getConnection() { Connection connection = null; try { connection = dataSource.getConnection(); } catch (Exception e) { e.printStackTrace(); } return connection; } public static void main(String[] args) throws Exception { System.out.println("开始使用连接池..."); PreparedStatement pst = null; for(int i=0;i<10;i++) { long beginTime = System.currentTimeMillis(); ConnectionManager inManager = ConnectionManager.getInstance(); Connection conn = inManager.getConnection(); try { pst = conn.prepareStatement("select * from tb_smsqueue"); ResultSet rs = pst.executeQuery(); rs.close(); } catch (Exception e) { e.printStackTrace(); } finally { try { //这里每次要释放连接,用不用连接池有什么作用? if(pst != null) { pst.close(); conn.close(); } } catch (Exception e) { e.printStackTrace(); } } long endTime = System.currentTimeMillis(); System.out.println("第" + (i + 1) + "次执行花费时间为:" + (endTime - beginTime)); }}