20 Query /* mysql-connector-java-5.1.13 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment 20 Query SHOW COLLATION 20 Query SET NAMES utf8mb4 20 Query SET character_set_results = NULL 20 Query SET autocommit=1 20 Query select * from user where username = '王五' 20 Query select * from user where username = '张三' 20 Quit
可以看到,在日志中并没有看到”prepare”命令来预编译select * from user where username = ?这个sql模板。所以我们一般用的PreparedStatement并没有用到预编译功能的,只是用到了防止sql注入攻击的功能。防止sql注入攻击的实现是在PreparedStatement中实现的,和服务器无关。在源码中,PreparedStatement对敏感字符已经转义过了。
21 Query SHOW WARNINGS 21 Query /* mysql-connector-java-5.1.13 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment 21 Query SHOW COLLATION 21 Query SET NAMES utf8mb4 21 Query SET character_set_results = NULL 21 Query SET autocommit=1 21 Prepare select * from user where username = ? 21 Execute select * from user where username = '王五' 21 Execute select * from user where username = '张三' 21 Close stmt 21 Quit
很明显已经进行了预编译,Prepare select * from user where username = ?,这一句就是对sql语句模板进行预编译的日志。好的非常Nice。
preparedStatement=connection.prepareStatement("select * from user where username like ?"); preparedStatement.setString(1, "%小明%"); resultSet = preparedStatement.executeQuery(); //遍历查询结果集 while(resultSet.next()){ System.out.println(resultSet.getString("id")+" "+resultSet.getString("username")); } //注意这里必须要关闭当前PreparedStatement对象流,否则下次再次创建PreparedStatement对象的时候还是会再次预编译sql模板,使用PreparedStatement对象后不关闭当前PreparedStatement对象流是不会缓存预编译后的函数key的 resultSet.close(); preparedStatement.close();
preparedStatement=connection.prepareStatement("select * from user where username like ?"); preparedStatement.setString(1, "%三%"); resultSet = preparedStatement.executeQuery(); //遍历查询结果集 while(resultSet.next()){ System.out.println(resultSet.getString("id")+" "+resultSet.getString("username")); }
24 Query SHOW WARNINGS 24 Query /* mysql-connector-java-5.1.13 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment 24 Query SHOW COLLATION 24 Query SET NAMES utf8mb4 24 Query SET character_set_results = NULL 24 Query SET autocommit=1 24 Prepare select * from user where username like ? 24 Execute select * from user where username like '%小明%' 24 Execute select * from user where username like '%三%' 24 Quit
resultSet = statement.executeQuery("select * from user where username='小天'"); //遍历查询结果集 while(resultSet.next()){ System.out.println(resultSet.getString("id")+" "+resultSet.getString("username")); }
resultSet.close(); statement.close();
statement=connection.createStatement();
resultSet = statement.executeQuery("select * from user where username='小天'"); //遍历查询结果集 while(resultSet.next()){ System.out.println(resultSet.getString("id")+" "+resultSet.getString("username")); }
26 Query SHOW WARNINGS 26 Query /* mysql-connector-java-5.1.13 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment 26 Query SHOW COLLATION 26 Query SET NAMES utf8mb4 26 Query SET character_set_results = NULL 26 Query SET autocommit=1 26 Query select * from user where username='小天' 26 Query select * from user where username='小天' 26 Quit