PreparedStatement的原理
PreparedStatement的作用
- 预编译，提高效率
- 防止Sql注入
MySql数据库执行sql语句的步骤是检查SQL语法，编译SQL，执行SQL.
使用PreparedStatement预编译，检查和编译仅执行一次，以后MySql数据库可以直接执行SQL。
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://pig-mysql:3306/kayak?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true&useServerPrepStmts=true";
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,username,password);
String sql = "select * from sys_user where username = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"admin");
ResultSet resultSet = null;
for(int i=1;i<10;i++)
resultSet= preparedStatement.executeQuery();
while (resultSet.next()){
//字符串类型
String sysUserName = resultSet.getString("password");
System.out.println(sysUserName);
}
配置查看是否使用预编译：
log-output=file
general-log=1
general_log_file="D:\mysql_general.log"
slow-query-log=1
slow_query_log_file="D:\mysql_slow.log"
long_query_time=2
没有使用预编译功能：
2022-05-24T10:29:30.717567Z 12 Connect root@localhost on kayak using TCP/IP
2022-05-24T10:29:30.734050Z 12 Query /* mysql-connector-java-8.0.27 (Revision:
2022-05-24T10:29:30.806255Z 12 Query SET character_set_results = NULL
2022-05-24T10:29:30.808560Z 12 Query SET autocommit=1
2022-05-24T10:29:30.893164Z 12 Query select * from sys_user where username = 'admin'
2022-05-24T10:29:30.936417Z 12 Query select * from sys_user where username = 'admin'
2022-05-24T10:29:30.945949Z 12 Query select * from sys_user where username = 'admin'
2022-05-24T10:29:30.947771Z 12 Query select * from sys_user where username = 'admin'
2022-05-24T10:29:30.949567Z 12 Query select * from sys_user where username = 'admin'
2022-05-24T10:29:30.951537Z 12 Query select * from sys_user where username = 'admin'
2022-05-24T10:29:30.953515Z 12 Query select * from sys_user where username = 'admin'
2022-05-24T10:29:30.955403Z 12 Query select * from sys_user where username = 'admin'
2022-05-24T10:29:30.957416Z 12 Query select * from sys_user where username = 'admin'
使用预编译功能，在url后面拼接：useServerPrepStmts=true开启预编译
2022-05-24T10:32:04.893572Z 13 Connect root@localhost on kayak using TCP/IP
2022-05-24T10:32:04.910893Z 13 Query /* mysql-connector-java-8.0.27 (Revision:
2022-05-24T10:32:05.001940Z 13 Query SET character_set_results = NULL
2022-05-24T10:32:05.004136Z 13 Query SET autocommit=1
2022-05-24T10:32:05.108287Z 13 Prepare select * from sys_user where username = ?
2022-05-24T10:32:05.126023Z 13 Execute select * from sys_user where username = 'admin'
2022-05-24T10:32:05.168144Z 13 Execute select * from sys_user where username = 'admin'
2022-05-24T10:32:05.176318Z 13 Execute select * from sys_user where username = 'admin'
2022-05-24T10:32:05.178389Z 13 Execute select * from sys_user where username = 'admin'
2022-05-24T10:32:05.180031Z 13 Execute select * from sys_user where username = 'admin'
2022-05-24T10:32:05.181703Z 13 Execute select * from sys_user where username = 'admin'
预编译原理：
- 在获取PreparedStatement对象时，将Sql语句发送MySql服务器进行检查、编译（这些步骤耗时）
- 如果Sql模板一样，只需要一次检查、编译。
防止SQL注入的原理
SQL注入演示
Connection connection = DriverManager.getConnection(url,dbUsername,dbPassword);
String username = "admin";
String password = "123' or '1' = '1";
String sql = "select * from sys_user where username = '" + username + "' and password = '" + password +"'";
System.out.println(sql);
//select * from sys_user where username = 'admin' and password = '123' or '1' = '1'
Statement statement = connection.createStatement();
statement.executeQuery(sql);
ResultSet resultSet = null;
resultSet= statement.executeQuery(sql);
SQL注入问题就是字符串拼接导致的
使用PreparedStatement解决SQL注入问题
Connection connection = DriverManager.getConnection(url,dbUsername,dbPassword);
String username = "admin";
String password = "123' or '1' = '1";
String sql = "select * from sys_user where username = ? and password = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1,username);
statement.setString(2,password);
ResultSet resultSet = null;
resultSet= statement.executeQuery();
PreparedStatement会对字符串加转译字符
Prepare select * from sys_user where username = ? and password = ?
Execute select * from sys_user where username = 'admin' and password = '123' or '1' = '1'
主要方法
void setObject(int parameterIndex, Object x)
setObject(int parameterIndex, Object x, SQLType targetSqlType)
void setDate(int parameterIndex, java.sql.Date x)
void setTime(int parameterIndex, java.sql.Time x)
void setTimestamp(int parameterIndex, java.sql.Timestamp x)
ResultSet executeQuery(String sql) #查询
void addBatch();
int[] executeBatch();
