我启动了十个线程去测试一个业务方法,但有时候会报出连接已经关闭,无法继续操作的异常。直接上代码了:
首先是测试代码:
public class Test { public static void main(String[] args){ // ProductService productService = new ProductServiceImpl(); // productService.updateProductPrice(1, 3000); for(int i=0;i<10;i++){ ProductService productService = new ProductServiceImpl(); MyThread myThread = new MyThread(productService); myThread.start(); } } } class MyThread extends Thread{ private ProductService productService; public MyThread(ProductService productService){ this.productService = productService; } @Override public void run() { System.out.println(Thread.currentThread().getName()); productService.updateProductPrice(1,3000); } }
业务代码:
public class ProductServiceImpl implements ProductService{ private static final String UPDATE_PRODUCT_SQL = "update product set price = ? where id = ?"; private static final String INSERT_LOG_SQL = "insert into log (created, description) values (?, ?)"; public void updateProductPrice(long productId, int price) { Connection conn = null; try { // 获取连接 conn = DBUtil.getConnection(); System.out.println(Thread.currentThread().getName()+"---->"+conn.toString()); conn.setAutoCommit(false); // 关闭自动提交事务(开启事务) // 执行操作 updateProduct(conn, UPDATE_PRODUCT_SQL, productId, price); // 更新产品 insertLog(conn, INSERT_LOG_SQL, "Create product."); // 插入日志 // 提交事务 conn.commit(); } catch (Exception e) { e.printStackTrace(); } finally { // 关闭连接 DBUtil.closeConnection(conn); } } private void updateProduct(Connection conn, String updateProductSQL, long productId, int productPrice) throws Exception { PreparedStatement pstmt = conn.prepareStatement(updateProductSQL); pstmt.setInt(1, productPrice); pstmt.setLong(2, productId); int rows = pstmt.executeUpdate(); if (rows != 0) { System.out.println("Update product success!"); } } private void insertLog(Connection conn, String insertLogSQL, String logDescription) throws Exception { PreparedStatement pstmt = conn.prepareStatement(insertLogSQL); pstmt.setString(1, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss SSS").format(new Date())); pstmt.setString(2, logDescription); int rows = pstmt.executeUpdate(); if (rows != 0) { System.out.println("Insert log success!"); } } }
数据库操作类:
public class DBUtil { private static final String driver = "com.mysql.jdbc.Driver"; private static final String url = "jdbc:mysql://localhost:3306/demo"; private static final String username = "root"; private static final String password = "root"; // 定义一个数据库连接 private static Connection conn = null; // 获取连接 public static Connection getConnection() { try { Class.forName(driver); conn = DriverManager.getConnection(url, username, password); } catch (Exception e) { e.printStackTrace(); } return conn; } // 关闭连接 public static void closeConnection(Connection conn) { try { if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } }
其中一次得到的运行结果:
要用Pool,初始化放多个,每个连接就要分配给一个。
public class ConnectionPool { private Vector<Connection> pool; private int poolSize = 100; private static ConnectionPool instance = null; Connection conn = null; private ConnectionPool() { pool = new Vector<Connection>(poolSize); for (int i = 0; i < poolSize; i++) { try { Class.forName(driverClassName); conn = DriverManager.getConnection(url, username, password); pool.add(conn); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } } /* 返回连接到连接池 */ public synchronized void release() { pool.add(conn); } /* 返回连接池中的一个数据库连接 */ public synchronized Connection getConnection() { if (pool.size() > 0) { Connection conn = pool.get(0); pool.remove(conn); return conn; } else { return null; } } }
应该说不保证每次都返回不一样的connection
毕竟你写成成员变量了
问题在DBUtil的这一行代码:
private static Connection conn = null;
conn这个变量是全局静态变量,是所有线程共享的。
一个极端的情况:
线程A执行到conn = DriverManager.getConnection(...) 的时候conn是数据库a连接;
线程B执行到conn = DriverManager.getConnection(...) 的时候conn重新被赋值为数据库b连接;
此时,由于conn是所有线程共享的,对于线程A和线程B来说conn的值是数据库b连接。这就导致了所有的线程共用一个数据库b连接,当一个线程关闭了这个连接,其他的线程也就会抛出异常。
此外,由于最后共享一个数据库b连接,数据库a连接就无法关闭导致内存泄露。