手撸Mybatis（五）——连接数据库进行insert，update和delete

引言

在上一章中，我们成功实现了数据库的连接，以及单个字段的查询、resultType映射查询、resultMap映射查询。在本章，我们将讲解关于增加、修改和删除操作。

insert操作

首先，我们修改IUserMapper类，添加insertUser接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
package com.yang.mybatis.test;

public interface IUserMapper {
    String queryUserName(Integer id);

    Integer queryUserAge(Integer id);

    User queryUserById(Integer id);

    IdUserNameVO queryIdUserNameVOById(Integer id);

    void insertUser(User user);
}

修改UserMapper.xml，加上insertUser相关的xml块

1
2
3
4
<insert id="insertUser" parameterType="com.yang.mybatis.test.User">
       insert into user( user_name, password, age, create_time)
       values( #{userName}, #{password}, #{age}, #{createTime})
   </insert>

我们注意到，对于每一个接口方法，其对应的xml块，类型有select，insert，update和delete这几种类型，此外，还有一个parameterType参数，因此，我们需要再次修改MybatisSqlStatement类，加上operateType和parameterType参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public class MybatisSqlStatement implements Serializable {
    private String namespace;

    private String id;

    private String sql;

    private String resultType;

    private String resultMap;

    private String operateType;

    private String parameterType;
  
    ...省略getter 和setter
}

接着修改XmlMybatisMapperParser的parseStatement方法，在解析mapper.xml的时候，设置对应的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
private void parseStatement(MybatisMapperXmlConfiguration mybatisMapperXmlConfiguration, List<Element> elements) {
       if (elements == null || elements.isEmpty()) {
           return;
       }
       String namespace = mybatisMapperXmlConfiguration.getMapperName();
       for (Element element : elements) {
           String id = element.attributeValue("id");
           String resultType = element.attributeValue("resultType");
           String resultMap = element.attributeValue("resultMap");
           String parameterType = element.attributeValue("parameterType");
           String sql = element.getText().trim();

           MybatisSqlStatement mybatisSqlStatement = new MybatisSqlStatement();
           mybatisSqlStatement.setNamespace(namespace);
           mybatisSqlStatement.setId(id);
           mybatisSqlStatement.setSql(sql);
           mybatisSqlStatement.setResultType(resultType);
           mybatisSqlStatement.setResultMap(resultMap);
           mybatisSqlStatement.setParameterType(parameterType);
           mybatisSqlStatement.setOperateType(element.getName().toLowerCase());

           mybatisMapperXmlConfiguration.addMybatisSqlStatement(mybatisSqlStatement);
       }
   }

定义IMybatisPreparedStatementBuilder接口，用于接收请求参数并设置参数值到preparedStatement

1
2
3
4
5
6
7
8
9
10
11
12
13
package com.yang.mybatis.execute;



import com.yang.mybatis.execute.request.MybatisPreparedStatementBuilderRequest;

import java.sql.PreparedStatement;
import java.sql.SQLException;

public interface IMybatisPreparedStatementBuilder {
    PreparedStatement buildPreparedStatement(MybatisPreparedStatementBuilderRequest mybatisPreparedStatementBuilderRequest) throws SQLException;
}

其中，MybatisPreparedStatementBuilderRequest的定义如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
package com.yang.mybatis.execute.request;

import com.yang.mybatis.config.MybatisSqlStatement;

import java.io.Serializable;
import java.sql.Connection;

public class MybatisPreparedStatementBuilderRequest implements Serializable {
    private Connection connection;
    private Object[] parameters;
    private MybatisSqlStatement mybatisSqlStatement;
    private String operateType;

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public Object[] getParameters() {
        return parameters;
    }

    public void setParameters(Object[] parameters) {
        this.parameters = parameters;
    }

    public MybatisSqlStatement getMybatisSqlStatement() {
        return mybatisSqlStatement;
    }

    public void setMybatisSqlStatement(MybatisSqlStatement mybatisSqlStatement) {
        this.mybatisSqlStatement = mybatisSqlStatement;
    }

    public String getOperateType() {
        return operateType;
    }

    public void setOperateType(String operateType) {
        this.operateType = operateType;
    }
}

该接口的默认实现类为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
package com.yang.mybatis.execute;

import com.yang.mybatis.config.MybatisSqlStatement;
import com.yang.mybatis.execute.request.MybatisPreparedStatementBuilderRequest;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DefaultMybatisPreparedStatementBuilder implements IMybatisPreparedStatementBuilder {
    @Override
    public PreparedStatement buildPreparedStatement(MybatisPreparedStatementBuilderRequest mybatisPreparedStatementBuilderRequest) throws SQLException {
        Connection connection = mybatisPreparedStatementBuilderRequest.getConnection();
        Object[] parameters = mybatisPreparedStatementBuilderRequest.getParameters();

        MybatisSqlStatement mybatisSqlStatement = mybatisPreparedStatementBuilderRequest.getMybatisSqlStatement();
        String rawSql = mybatisSqlStatement.getSql();

        List<String> parameterNameList = new ArrayList<>();
        String sql = extractRawSql(rawSql, parameterNameList);
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        String parameterType = mybatisSqlStatement.getParameterType();
        if (StringUtils.isEmpty(parameterType)) {
            if (parameterNameList.size() != parameters.length) {
                throw new RuntimeException("SQL语句参数个数不匹配====");
            }
            int index = 1;
            for (Object o : parameters) {
                preparedStatement.setObject(index ++, o);
            }
            return preparedStatement;
        }

        try {
            Object parameter = parameters[0];
            Class<?> aClass = Class.forName(parameterType);
            Field[] fields = aClass.getDeclaredFields();
            Map<String, Field> filedName2FieldMap = new HashMap<>();
            for (Field field : fields) {
                filedName2FieldMap.put(field.getName(), field);
            }

            List<Object> parameterValueList = new ArrayList<>();
            for (String parameterName : parameterNameList) {
                Field field = filedName2FieldMap.get(parameterName);
                if (field == null) {
                    throw new RuntimeException("SQL参数不存在");
                }
                field.setAccessible(true);
                Object value = field.get(parameter);
                parameterValueList.add(value);
            }

            int index = 1;
            for (Object o : parameterValueList) {
                preparedStatement.setObject(index++, o);
            }
            return preparedStatement;
        } catch (ClassNotFoundException | IllegalAccessException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    private String extractRawSql(String rawSql, List<String> parameterNameList) {
        StringBuilder sqlBuilder = new StringBuilder();
        int start = 0;
        int end = -1;
        while ((end = rawSql.indexOf("#", start)) != -1) {
            sqlBuilder.append(rawSql.substring(start, end - 1))
                    .append(" ? ");
            int parameterStart = end + 2;
            int parameterEnd = rawSql.indexOf("}", parameterStart);
            parameterNameList.add(rawSql.substring(parameterStart, parameterEnd));
            start = parameterEnd + 1;
        }
        sqlBuilder.append(rawSql.substring(start));

        return sqlBuilder.toString();
    }
}

然后，我们修改DefaultMybatisSqlSession类，在execute方法中我们先使用IMybatisPreparedStatementBuilder来构建PreparedStatement，然后执行相关操作，最后再通过IMybatisResultParser来对结果进行解析。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
package com.yang.mybatis.session;

import com.yang.mybatis.config.MybatisConfiguration;
import com.yang.mybatis.config.MybatisEnvironment;
import com.yang.mybatis.config.MybatisMapperXmlConfiguration;
import com.yang.mybatis.config.MybatisSqlStatement;
import com.yang.mybatis.execute.DefaultMybatisPreparedStatementBuilder;
import com.yang.mybatis.execute.DefaultMybatisResultParser;
import com.yang.mybatis.execute.IMybatisPreparedStatementBuilder;
import com.yang.mybatis.execute.IMybatisResultParser;
import com.yang.mybatis.execute.request.MybatisPreparedStatementBuilderRequest;
import com.yang.mybatis.execute.request.MybatisResultParserRequest;
import com.yang.mybatis.proxy.MapperProxyFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;


public class DefaultMybatisSqlSession implements IMybatisSqlSession {
    private MapperProxyFactory mapperProxyFactory;
    private MybatisConfiguration mybatisConfiguration;

    private IMybatisResultParser iMybatisResultParser;

    private IMybatisPreparedStatementBuilder iMybatisPreparedStatementBuilder;

    public DefaultMybatisSqlSession(MapperProxyFactory mapperProxyFactory, IMybatisPreparedStatementBuilder iMybatisPreparedStatementBuilder,
                                    IMybatisResultParser iMybatisResultParser) {
        this.mapperProxyFactory = mapperProxyFactory;
        this.mybatisConfiguration = mapperProxyFactory.getMybatisConfiguration();
        this.iMybatisPreparedStatementBuilder = iMybatisPreparedStatementBuilder;
        this.iMybatisResultParser = iMybatisResultParser;
    }

    @Override
    public <T> T execute(String method, Object parameter) {
        Map<String, MybatisSqlStatement> mapperMethod2SqlStatementsMap = mapperProxyFactory.getMybatisConfiguration().getMapperMethod2SqlStatementsMap();
        MybatisSqlStatement mybatisSqlStatement = mapperMethod2SqlStatementsMap.get(method);

        MybatisEnvironment defaultMybatisEnvironment = this.mybatisConfiguration.getDefaultMybatisEnvironment();

        return new TransactionInvoke<T>() {
            @Override
            public T execute(Connection connection) throws SQLException {
                Object[] parameters = (Object[]) parameter;
                String operateType = mybatisSqlStatement.getOperateType();

                MybatisPreparedStatementBuilderRequest mybatisPreparedStatementBuilderRequest = new MybatisPreparedStatementBuilderRequest();
                mybatisPreparedStatementBuilderRequest.setConnection(connection);
                mybatisPreparedStatementBuilderRequest.setMybatisSqlStatement(mybatisSqlStatement);
                mybatisPreparedStatementBuilderRequest.setParameters(parameters);
                mybatisPreparedStatementBuilderRequest.setOperateType(operateType);
                PreparedStatement preparedStatement = iMybatisPreparedStatementBuilder.buildPreparedStatement(mybatisPreparedStatementBuilderRequest);

                ResultSet resultSet = null;
                if ("select".equals(operateType)) {
                    resultSet = preparedStatement.executeQuery();
                } else {
                    preparedStatement.execute();
                }

                String mapperName = mybatisSqlStatement.getNamespace();
                MybatisMapperXmlConfiguration mybatisMapperXmlConfiguration = mybatisConfiguration.getMybatisMapperXmlConfiguration(mapperName);

                MybatisResultParserRequest mybatisResultParserRequest = new MybatisResultParserRequest();
                mybatisResultParserRequest.setResultSet(resultSet);
                mybatisResultParserRequest.setMybatisSqlStatement(mybatisSqlStatement);
                mybatisResultParserRequest.setMybatisMapperXmlConfiguration(mybatisMapperXmlConfiguration);
                mybatisResultParserRequest.setOperateType(operateType);
                T result = iMybatisResultParser.parseResult(mybatisResultParserRequest);
                if (resultSet != null) {
                    resultSet.close();
                }
                return result;
            }
        }.invoke(defaultMybatisEnvironment.getMybatisDataSource());
    }

    @Override
    public <T> T getMapper(Class<T> type) {
        return (T) mapperProxyFactory.newInstance(type, this);
    }
}

这里还需要修改DefaultMybatisSqlSessionFactory类，将IMybatisResultParser和IMybatisPreparedStatementBuilder的构建职责迁移于此，从而避免每一个SqlSession都需要重复创建这些类。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package com.yang.mybatis.session;

import com.yang.mybatis.execute.DefaultMybatisPreparedStatementBuilder;
import com.yang.mybatis.execute.DefaultMybatisResultParser;
import com.yang.mybatis.execute.IMybatisPreparedStatementBuilder;
import com.yang.mybatis.execute.IMybatisResultParser;
import com.yang.mybatis.proxy.MapperProxyFactory;

public class DefaultMybatisSqlSessionFactory implements IMybatisSqlSessionFactory {
    private MapperProxyFactory mapperProxyFactory;
    private IMybatisPreparedStatementBuilder iMybatisPreparedStatementBuilder = new DefaultMybatisPreparedStatementBuilder();
    private IMybatisResultParser iMybatisResultParser = new DefaultMybatisResultParser();

    public DefaultMybatisSqlSessionFactory(MapperProxyFactory mapperProxyFactory) {
        this.mapperProxyFactory = mapperProxyFactory;
    }

    @Override
    public IMybatisSqlSession openSession() {
        return new DefaultMybatisSqlSession(mapperProxyFactory, iMybatisPreparedStatementBuilder, iMybatisResultParser);
    }
}

添加测试代码，进行测试：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com.yang.mybatis.test;

import com.yang.mybatis.config.parser.XmlMybatisConfigurationParser;
import com.yang.mybatis.config.parser.XmlMybatisMapperParser;
import com.yang.mybatis.session.IMybatisSqlSession;
import com.yang.mybatis.session.IMybatisSqlSessionFactory;
import com.yang.mybatis.session.MybatisSqlSessionFactoryBuilder;

import java.time.LocalDateTime;


public class Main {
    public static void main(String[] args) {
        String configPath = "mybatis-config.xml";
        IMybatisSqlSessionFactory mybatisSqlSessionFactory = new MybatisSqlSessionFactoryBuilder()
                .setMybatisMapperParser(new XmlMybatisMapperParser())
                .setMybatisConfigurationParser(new XmlMybatisConfigurationParser())
                .setConfigPath(configPath)
                .buildSqlSessionFactory();
        IMybatisSqlSession mybatisSqlSession = mybatisSqlSessionFactory.openSession();
        IUserMapper userMapper = mybatisSqlSession.getMapper(IUserMapper.class);

        User user = new User();
        user.setUserName("test");
        user.setPassword("test");
        user.setAge(4);
        user.setCreateTime(LocalDateTime.now());
        userMapper.insertUser(user);
    }

}

运行上述方法，再次查看数据库，结果如下：

update操作

首先，我们在IUserMapper接口上，添加一个updateUserById方法

1
2
void updateUserById(User user);

然后修改UserMapper.xml，添加上updateUserById相关的xml块

1
2
3
4
5
6
7
<update id="updateUserById" parameterType="com.yang.mybatis.test.User">
       update user
       set user_name = #{userName},
           password = #{password},
           age = #{age}
       where id = #{id}
   </update>

添加测试方法，进行测试

1
2
3
4
5
6
7
8
9
10
11
12
13
String configPath = "mybatis-config.xml";
     MybatisSqlSessionFactory mybatisSqlSessionFactory = new MybatisSqlSessionFactoryBuilder()
             .setMybatisMapperParser(new XmlMybatisMapperParser())
             .setMybatisConfigurationParser(new XmlMybatisConfigurationParser())
             .setConfigPath(configPath)
             .buildSqlSessionFactory();
     IMybatisSqlSession mybatisSqlSession = mybatisSqlSessionFactory.openSession();
     IUserMapper userMapper = mybatisSqlSession.getMapper(IUserMapper.class);
     User user = userMapper.queryUserById(4);
     user.setUserName("cxy1");
     user.setPassword("1234");
     user.setAge(10);
     userMapper.updateUserById(user);

运行方法后，查看数据库，结果如下：

delete操作

首先，修改IUserMapper，添加deleteUserById方法

1
2
void deleteUserById(Integer id);

然后在UserMapper.xml中添加上对应的xml块

1
2
3
4
<delete id="deleteUserById" parameterType="int">
        delete from user
        where id = #{id}
    </delete>

添加测试方法，进行测试：

1
2
3
4
5
6
7
8
9
10
11
public static void main(String[] args) {
       String configPath = "mybatis-config.xml";
       IMybatisSqlSessionFactory mybatisSqlSessionFactory = new MybatisSqlSessionFactoryBuilder()
               .setMybatisMapperParser(new XmlMybatisMapperParser())
               .setMybatisConfigurationParser(new XmlMybatisConfigurationParser())
               .setConfigPath(configPath)
               .buildSqlSessionFactory();
       IMybatisSqlSession mybatisSqlSession = mybatisSqlSessionFactory.openSession();
       IUserMapper userMapper = mybatisSqlSession.getMapper(IUserMapper.class);
       userMapper.deleteUserById(5);
   }

结果如下:

可以看出，这里因为我们使用的是parameterType是int而不是java.lang.Integer，所以会报错，但是一般情况下，像int，float，long等小写，一般我们也会使用的，而且用的频率比java.lang.Integer的频率更大，那么，我们其实可以添加一个假名，将int这些小写的方式和对应的包装类关联起来。我们修改DefaultMybatisPreparedStatementBuilder，修改内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
package com.yang.mybatis.execute;

import com.yang.mybatis.config.MybatisSqlStatement;
import com.yang.mybatis.execute.request.MybatisPreparedStatementBuilderRequest;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DefaultMybatisPreparedStatementBuilder implements IMybatisPreparedStatementBuilder {
    private Map<String, String> baseAliasMap = new HashMap<>();

    {
        baseAliasMap.put("int", Integer.class.getName());
        baseAliasMap.put("float", Float.class.getName());
        baseAliasMap.put("double", Double.class.getName());
        baseAliasMap.put("long", Long.class.getName());
        baseAliasMap.put("byte", Byte.class.getName());
        baseAliasMap.put("short", Short.class.getName());
        baseAliasMap.put("string", String.class.getName());
        baseAliasMap.put("String", String.class.getName());
        baseAliasMap.put("char", Character.class.getName());
    }
    @Override
    public PreparedStatement buildPreparedStatement(MybatisPreparedStatementBuilderRequest mybatisPreparedStatementBuilderRequest) throws SQLException {
        Connection connection = mybatisPreparedStatementBuilderRequest.getConnection();
        Object[] parameters = mybatisPreparedStatementBuilderRequest.getParameters();

        MybatisSqlStatement mybatisSqlStatement = mybatisPreparedStatementBuilderRequest.getMybatisSqlStatement();
        String rawSql = mybatisSqlStatement.getSql();

        List<String> parameterNameList = new ArrayList<>();
        String sql = extractRawSql(rawSql, parameterNameList);
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        String parameterType = mybatisSqlStatement.getParameterType();
        if (StringUtils.isEmpty(parameterType)) {
            if (parameterNameList.size() != parameters.length) {
                throw new RuntimeException("SQL语句参数个数不匹配====");
            }
            int index = 1;
            for (Object o : parameters) {
                preparedStatement.setObject(index ++, o);
            }
            return preparedStatement;
        }

        try {
            Object parameter = parameters[0];
            if (baseAliasMap.containsKey(parameterType)) {
                if (parameters.length != 1) {
                    throw new RuntimeException("SQL语句有误, 只能有一个parameterType参数");
                }
                preparedStatement.setObject(1, parameter);
                return preparedStatement;
            }
            Class<?> aClass = Class.forName(parameterType);
            Field[] fields = aClass.getDeclaredFields();
            Map<String, Field> filedName2FieldMap = new HashMap<>();
            for (Field field : fields) {
                filedName2FieldMap.put(field.getName(), field);
            }

            List<Object> parameterValueList = new ArrayList<>();
            for (String parameterName : parameterNameList) {
                Field field = filedName2FieldMap.get(parameterName);
                if (field == null) {
                    throw new RuntimeException("SQL参数不存在");
                }
                field.setAccessible(true);
                Object value = field.get(parameter);
                parameterValueList.add(value);
            }

            int index = 1;
            for (Object o : parameterValueList) {
                preparedStatement.setObject(index++, o);
            }
            return preparedStatement;
        } catch (ClassNotFoundException | IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    private String extractRawSql(String rawSql, List<String> parameterNameList) {
        StringBuilder sqlBuilder = new StringBuilder();
        int start = 0;
        int end = -1;
        while ((end = rawSql.indexOf("#", start)) != -1) {
            sqlBuilder.append(rawSql.substring(start, end - 1))
                    .append(" ? ");
            int parameterStart = end + 2;
            int parameterEnd = rawSql.indexOf("}", parameterStart);
            parameterNameList.add(rawSql.substring(parameterStart, parameterEnd));
            start = parameterEnd + 1;
        }
        sqlBuilder.append(rawSql.substring(start));

        return sqlBuilder.toString();
    }
}

再次运行测试方法，这次没有报错了，然后我们查看数据库，结果如下，说明删除成功了。

#-Mybatis
手撸Mybatis（五）——连接数据库进行insert，update和delete
https://cxydhi.github.io/2024/05/05/手撸Mybatis（五）——连接数据库进行insert，update和delete/
作者
沉河不浮
发布于
2024年5月5日
许可协议