一 概念
MyBatis是一个Dao层映射框架,底层还是用的JDBC来访问数据库的,JDBC的执行过程如下:
1 获得连接
2 预编译sql
3 执行sql
4 读取结果
二 JDBC使用回顾
Maven工程经典JDBC demo 代码执行逻辑如下:
引入单元测试Maven依赖:
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
public class JdbcTest {
public static final String URL = "jdbc:mysql://127.0.0.1:3306/jdbc";
public static final String USERNAME = "geekAntony";
public static final String PASSWORD = "123456";
private Connection connection;
@Before
public void init() throws SQLException {
//获得连接
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
@After
public void over() throws SQLException {
//关闭连接
connection.close();
}
@Test
public void jdbcTest() throws SQLException {
// 预编译SQL
String sql = "SELECT * FROM users WHERE `name`=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置SQL参数
preparedStatement.setString(1, "geekAntony");
//执行SQL
preparedStatement.execute();
//获得结果集
ResultSet resultSet = preparedStatement.getResultSet();
//遍历结果集
while (resultSet.next()) {
System.out.println(resultSet.getString(1));
}
resultSet.close();
preparedStatement.close();
over();
}
}
二 JDBC的三种执行器
众所周知JDBC一共有3种执行器的功能特点作用如下:
简单执行器Statement | 预处理执行器PreparedStatement | 存储过程执行器CallableStatement |
---|---|---|
只能执行静态SQL(不带参数的SQL) | 支持可变参数的SQL(即一次SQL编译可以多次执行) | 支持调用存储过程 |
---- | 防止SQL注入攻击(本质是在数据库端将参数转义) | 设置出参,读取出参 |
SQL注入测试:
测试方法1:使用普通statement
/**
* sql注入测试
* @param name
* @return
* @throws SQLException
*/
public int selectByName(String name) throws SQLException {
String sql = "SELECT * FROM express_memberinfo WHERE `name`='" + name + "'";
System.out.println(sql);
Statement statement = connection.createStatement();
statement.executeQuery(sql);
ResultSet resultSet = statement.getResultSet();
int count=0;
while (resultSet.next()){
count++;
}
statement.close();
return count;
}
测试方法2:使用PreparedStatement
/**
* PreparedStatement防止 sql注入测试
* @param name
* @return
* @throws SQLException
*/
public int selectByName2(String name) throws SQLException {
String sql = "SELECT * FROM express_memberinfo WHERE `name`=?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1,name);
System.out.println(statement);
statement.executeQuery();
ResultSet resultSet = statement.getResultSet();
int count=0;
while (resultSet.next()){
count++;
}
statement.close();
return count;
}
Test:
@Test
public void injectTest() throws SQLException {
System.out.println(selectByName("徐小超"));
System.out.println(selectByName("徐小超' or '1'='1"));
System.out.println(selectByName2("徐小超' or '1'='1"));
}
console:
Connected to the target VM, address: '127.0.0.1:56423', transport: 'socket'
SELECT * FROM express_memberinfo WHERE `name`='徐小超'
1
SELECT * FROM express_memberinfo WHERE `name`='徐小超' or '1'='1'
36
com.mysql.cj.jdbc.ClientPreparedStatement: SELECT * FROM express_memberinfo WHERE `name`='徐小超\' or \'1\'=\'1'
0
Disconnected from the target VM, address: '127.0.0.1:56423', transport: 'socket'
Process finished with exit code 0
总结:
由此可见,若是使用普通Statement,正常使用是没有任何问题的,但是可能会有别有用心之人使用SQL注入偷取数据库数据,恶意修改数据库,并且Mybatis中默认使用的JDBC执行器都是PreparedStatementHandler,PreparedStatementHandler常用的Statement就是PreparedStatement。
三 以上三种JDBC执行器的共有功能
预置知识:
statement除了常规的CRUD的方法外还有addBatch,setFetchSize方法
addBatch 测试:
单行SQL连续执行100次测试:
Test:
/**
* 单条SQL执行,执行100次 执行时间大约3176ms
* @throws SQLException
*/
@Test
public void prepareBatchTest1() throws SQLException {
String sql = "INSERT INTO `users` (`name`,age) VALUES (?,18);";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setFetchSize(100);
long l = System.currentTimeMillis();
for (int i = 0; i < 100; i++) {
preparedStatement.setString(1, UUID.randomUUID().toString());
preparedStatement.execute(); //单条执行
}
System.out.println(System.currentTimeMillis() - l);
preparedStatement.close();
}
console:
Connected to the target VM, address: '127.0.0.1:57434', transport: 'socket'
3176
Disconnected from the target VM, address: '127.0.0.1:57434', transport: 'socket'
Process finished with exit code 0
addBatch方法测试,100条SQL一次性执行:
Test:
/**
* 批处理执 行时间1932ms
* @throws SQLException
*/
@Test
public void prepareBatchTest2() throws SQLException {
String sql = "INSERT INTO `users` (`name`,age) VALUES (?,18);";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setFetchSize(100);
long l = System.currentTimeMillis();
for (int i = 0; i < 100; i++) {
preparedStatement.setString(1, UUID.randomUUID().toString());
preparedStatement.addBatch(); // 添加批处理参数
}
preparedStatement.executeBatch(); // 批处理,一次性执行
System.out.println(System.currentTimeMillis() - l);
preparedStatement.close();
}
console:
Connected to the target VM, address: '127.0.0.1:57560', transport: 'socket'
1932
Disconnected from the target VM, address: '127.0.0.1:57560', transport: 'socket'
使用addBatch,但是不听过PreparedStatement预编译SQL,而是直接填写参数:
Test:
/**
* 批处理执 行时间1314ms
*/
@Test
public void prepareBatchTest3() throws SQLException {
//没有使用SQL预处理的方式
String sql = "INSERT INTO `users` (`name`,age) VALUES ('geekAntony',18);";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//设置每次从数据库中读取100行,防止一次性读取过多数据,导致内存溢出(MySql不支持此功能)
preparedStatement.setFetchSize(100);
long l = System.currentTimeMillis();
for (int i = 0; i < 100; i++) {
//准备炮弹(没有使用SQL预处理的方式)
preparedStatement.addBatch(sql);
}
preparedStatement.executeBatch(); // 批处理,一次性执行
System.out.println(System.currentTimeMillis() - l);
preparedStatement.close();
}
console:
Connected to the target VM, address: '127.0.0.1:57681', transport: 'socket'
1314
Disconnected from the target VM, address: '127.0.0.1:57681', transport: 'socket'
Process finished with exit code 0
总结:
1addBatch批处理,在某些情况下,SQL执行效率很快,但是不一定提升性能,因为此刻没有处理并发请求,当前只有此一条连接在执行
只有满足以下两个条件执行速度才快:
---服务端处理的没有其他线程发起SQL请求
---MySql服务器没有处理其他的SQL请求
2 有些时候静态SQL执行比预编译快,但是不够安全,容易遭受SQL注入攻击
setFetchSize
Mysql暂不支持此功能,Oracle支持
四 实战:
如何在Mybatis框架中决定使用哪一种JDBC执行器,在Mapper接口中设置option注解
@Options(statementType = StatementType.PREPARED)
List<User> selectByName(User user);
package org.apache.ibatis.mapping;
/**
* @author Clinton Begin
*/
public enum StatementType {
STATEMENT, PREPARED, CALLABLE
}