Mybatis Path to God – Review the JDBC implementation process

A concept

MyBatis is a Dao layer mapping framework. At the bottom,JDBC is used to access the database. The execution process of JDBC is as follows: 1

Review of JDBC usage

Maven project classic JDBC demo code execution logic is as follows:

<dependency> <groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency>Copy the code

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 {/ / get connected connection = DriverManager. GetConnection (URL, USERNAME, PASSWORD); } @after public void over() throws SQLException {connection.close(); } @test public void jdbcTest() throws SQLException {// Precompiled SQL String SQL ="SELECT * FROM users WHERE `name`=?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); / / set the SQL preparedStatement parameters. SetString (1,"geekAntony"); / / execute SQL preparedStatement. The execute (); / / get the result set the ResultSet ResultSet = preparedStatement. GetResultSet (); // Iterate over the result setwhile(resultSet.next()) { System.out.println(resultSet.getString(1)); } resultSet.close(); preparedStatement.close(); over(); }}Copy the code

Two, three kinds of JDBC actuators

It is well known that there are three TYPES of JDBC actuators:

SQL injection test method 1: Use a common statement

/** * SQL injection test * @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;
    }
Copy the code

Test method 2: Use a PreparedStatement

/** * PreparedStatement prevents SQL injection tests * @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;
    }
Copy the code

The Test:

   @Test
   public void injectTest() throws SQLException {
        System.out.println(selectByName("Xu Xiaochao"));
        System.out.println(selectByName('or '1'='1'));
        System.out.println(selectByName2('or '1'='1'));
    }
Copy the code

The console:

Connected to the target VM, address: '127.0.0.1:56423', transport: 'socket'
SELECT * FROM express_memberinfo WHERE `name`='Xu Xiaochao'
1
SELECT * FROM express_memberinfo WHERE `name`='Xu Xiaochao' or '1'='1'
36
com.mysql.cj.jdbc.ClientPreparedStatement: SELECT * FROM express_memberinfo WHERE `name`='Xu Xiaochao \' or \'1 \'= \'1'
0
Disconnected from the target VM, address: '127.0.0.1:56423', transport: 'socket'

Process finished with exit code 0
Copy the code

Conclusion: Thus, if use normal Statement, normal use is no problem, but there may be people with ulterior motives use SQL injection to steal the database data, malicious change the database, and default in Mybatis JDBC actuators are PreparedStatementHandler, Pr A PreparedStatement is commonly used in eparedStatementHandler.

Common functions of three or more JDBC actuators

Preset knowledge: Statement has addBatch and setFetchSize methods in addition to regular CRUD methods

AddBatch: can perform batch processing, merge multiple SQL files together, and then execute executorBatch calls, sending them together to the database setFetchSize: Set the unit of data read from the database each time to prevent memory overflow caused by loading too much data from the database at a time

AddBatch test:

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(); Println (system.currentTimemillis () -l);} system.out.println (system.currentTimemillis () -l); preparedStatement.close(); }Copy the code

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
Copy the code

AddBatch method Test,100 SQL at a time: Test:

/** * Batch execution time 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(); / / add batch parameters} preparedStatement. ExecuteBatch (); Println (system.currentTimemillis () -l); preparedStatement.close(); }Copy the code

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'

Copy the code

Use addBatch, but not heard PreparedStatement precompiled SQL, but directly fill in the parameter: the Test:

/** * batch execution time 1314ms */ @test public void prepareBatchTest3() throws SQLException {// No SQL preprocessing method is used. String SQL ="INSERT INTO `users` (`name`,age) VALUES ('geekAntony',18);"; PreparedStatement preparedStatement = connection.prepareStatement(sql); / / set every time I read from the database of 100 lines, prevent the one-time read too much data, lead to memory overflow (MySql does not support this function) preparedStatement. SetFetchSize (100); long l = System.currentTimeMillis();for(int i = 0; i < 100; I++) {/ / prepare shells (without using SQL pretreatment way) preparedStatement. AddBatch (SQL); } preparedStatement.executeBatch(); Println (system.currentTimemillis () -l); preparedStatement.close(); }Copy the code

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
Copy the code

Summary: 1addBatch, in some cases, the SQL execution efficiency is very fast, but it does not necessarily improve the performance, because no concurrent requests are being processed at this moment, only one connection is being executed, and the execution speed is fast only when the following two conditions are met: MySql server processes no other SQL requests. 2 Static SQL execution is sometimes faster than precompilation, but it is not secure enough to be vulnerable to SQL injection attacks

setFetchSize

Mysql does not support this function,Oracle does

Four practical:

How to determine which JDBC executor to use in Mybatis framework and set option annotation in Mapper interface

@Options(statementType = StatementType.PREPARED)
List<User> selectByName(User user);
Copy the code

package org.apache.ibatis.mapping;

/**
 * @author Clinton Begin
 */
public enum StatementType {
  STATEMENT, PREPARED, CALLABLE
}
Copy the code