MySQL Server 4.1 前不支持预编译

Connector/J <= 5.0.5 useServerPrepStmts 默认 true

Connector/J 5.0.5 开始 useServerPrepStmts 默认 false

即使开启预编译缓存后,不同 connection 间预编译结果也是独立无法共享的

一个 connection 无法得到另外一个 connection 的预编译缓存结果

useServerPrepStmts=true 时也一定要同时使用 cachePrepStmts=true

但二者都开启,性能也并没有显著的提高

public static void main(String []a) throws Exception{  
       String sql = "select * from users where name = ?";  
       Class.forName("com.mysql.jdbc.Driver");  
       Connection conn = null;  
       try{  
           conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=true");  
           PreparedStatement stmt = conn.prepareStatement(sql);  
           stmt.setString(1, "aaa");  
           ResultSet rs1 = stmt.executeQuery();// 第一次执行  
           rs1.close();  
           stmt.close();  
           stmt = conn.prepareStatement(sql);// 重新获取一个 statement  
           stmt.setString(1, "ddd");  
           ResultSet rs2 = stmt.executeQuery();// 第二次执行  
           rs2.close();  
           stmt.close();  
       }catch(Exception e){  
           e.printStackTrace();  
       }finally{  
           conn.close();  
       }  
    }  

只开useServerPrepStmts,不开cachePrepStmts,PreparedStatement关闭后

Prepare          select * from users where name = ?
Execute          select * from users where name = 'aaa'
Close stmt   
Prepare          select * from users where name = ?
Execute          select * from users where name = 'ddd'