Mybatis成神之路-回顾JDBC执行过程

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种执行器的功能特点作用如下:

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
复制代码

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
}
复制代码