人人日常平凡都运用过JDBC中的PreparedStatement接口,它有预编译功用。什么是预编译功用呢?它有什么优点呢?
当客户发送一条SQL语句给服务器后,服务器老是须要校验SQL语句的语法花样是不是准确,然后把SQL语句编译成可实行的函数,末了才是实行SQL语句。个中校验语法,和编译所花的时候能够比实行SQL语句花的时候还要多。
假如我们须要实行屡次insert语句,但只是每次插进去的值差别,MySQL服务器也是须要每次都去校验SQL语句的语法花样,以及编译,这就浪费了太多的时候。假如运用预编译功用,那末只对SQL语句举行一次语法校验和编译,所以效力要高。
2、MySQL实行预编译
MySQL实行预编译分为如三步:
实行预编译语句,比方:prepare myfun from 'select * from t_book where bid=?'
设置变量,比方:set @str='b1'
实行语句,比方:execute myfun using @str
假如须要再次实行myfun,那末就不再须要第一步,即不须要再编译语句了:
设置变量,比方:set @str='b2'
实行语句,比方:execute myfun using @str
经由历程检察MySQL日记能够看到实行的历程:
3、运用Statement实行预编译
运用Statement实行预编译就是把上面的SQL语句实行一次。
Connection con = JdbcUtils.getConnection(); Statement stmt = con.createStatement(); stmt.executeUpdate("prepare myfun from 'select * from t_book where bid=?'"); stmt.executeUpdate("set @str='b1'"); ResultSet rs = stmt.executeQuery("execute myfun using @str");while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4)); } stmt.executeUpdate("set @str='b2'"); rs = stmt.executeQuery("execute myfun using @str");while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4)); } rs.close(); stmt.close(); con.close();
4、useServerPrepStmts参数
默许运用PreparedStatement是不能实行预编译的,这须要在url中给出useServerPrepStmts=true参数(MySQL Server 4.1之前的版本是不支持预编译的,而Connector/J在5.0.5今后的版本,默许是没有开启预编译功用的)。
比方:jdbc:mysql://localhost:3306/test?useServerPrepStmts=true
如许才保证mysql驱动会先把SQL语句发送给服务器举行预编译,然后在实行executeQuery()时只是把参数发送给服务器。
Connection con = JdbcUtils.getConnection(); String sql = "select * from t_book where bid=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, "b1"); ResultSet rs = pstmt.executeQuery();while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4)); } pstmt.setString(1, "b2"); rs = pstmt.executeQuery();while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4)); } rs.close(); pstmt.close(); con.close();
5、cachePrepStmts参数
当运用差别的PreparedStatement对象来实行雷同的SQL语句时,照样会涌现编译两次的征象,这是由于驱动没有缓存编译后的函数key,致使二次编译。假如愿望缓存编译后函数的key,那末就要设置cachePrepStmts参数为true。比方:
jdbc:mysql://localhost:3306/test?useServerPrepStmts=true&cachePrepStmts=true
Connection con = JdbcUtils.getConnection(); String sql = "select * from t_book where bid=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, "b1"); ResultSet rs = pstmt.executeQuery();while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4)); } pstmt = con.prepareStatement(sql); pstmt.setString(1, "b2"); rs = pstmt.executeQuery();while(rs.next()) { System.out.print(rs.getString(1) + ", "); System.out.print(rs.getString(2) + ", "); System.out.print(rs.getString(3) + ", "); System.out.println(rs.getString(4)); } rs.close(); pstmt.close(); con.close();
6、翻开批处理
MySQL的批处理也须要经由历程参数来翻开:rewriteBatchedStatements=true
以上就是MySQL的预编译功用的细致内容,更多请关注ki4网别的相干文章!