10.搭建Sql执行器

最新推荐文章于 2024-06-05 16:57:40 发布

白日905

最新推荐文章于 2024-06-05 16:57:40 发布

阅读量608

点赞数 27

分类专栏：手写SSM 文章标签： sql java 数据库 mybatis

本文链接：https://blog.csdn.net/qq_63612259/article/details/137466905

版权

手写SSM 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

title: 10.搭建Sql执行器
tag: 笔记手写SSM ORM

在搭建了基础框架之后，我们就可以使用执行器Excutor根据XML的信息来执行Sql并拿到值了。

分析

在上一步中我们完成了Mybatis框架的基础搭建，大概有了Mybatis大概的执行流程：

读取到XML配置文件中的信息
解析信息构建SqlSession
根据SqlSession的信息为接口创建动态代理对象的执行逻辑

目前已经可以获取到一个Mapper映射中每一条语句中的入参，返回值，Sql语句等信息，而在实际使用中我们需要的不是这些信息，而是Sql语句执行后的结果，因此本节我们需要完成的是：

为Sql预注入参数注入值
将Sql语句执行
处理执行后的结果集

数据源

要想将Sql语句到Mybatis中执行，我们需要配置JDBC的环境，我们需要拿到JDBC的数据源DataSource：

这里我们使用工厂模式来创建数据源：

public interface DataSourceFactory {

    void setProperties(Properties props);

    DataSource getDataSource();

}

public class DruidDataSourceFactory implements DataSourceFactory {

    private Properties props;

    @Override
    public void setProperties(Properties props) {
        this.props = props;
    }
    @Override
    public DataSource getDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(props.getProperty("driver"));
        dataSource.setUrl(props.getProperty("url"));
        dataSource.setUsername(props.getProperty("username"));
        dataSource.setPassword(props.getProperty("password"));
        return dataSource;
    }

}

我们通过传入DataSource必要的配置项来创建了一个德鲁伊的数据源，并通过getDataSource方法来拿到创建好的数据源。

事务管理

获取了数据源之后我们还需要对事务进行管理，我们同样使用一个工厂模式来创建事务：

事务工厂接口：

public interface TransactionFactory {

    /**
     * 根据 Connection 创建 Transaction
     *
     * @param conn Existing database connection
     * @return Transaction
     */
    Transaction newTransaction(Connection conn);

    /**
     * 根据数据源和事务隔离级别创建 Transaction
     *
     * @param dataSource DataSource to take the connection from
     * @param level      Desired isolation level
     * @param autoCommit Desired autocommit
     * @return Transaction
     */
    Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit);

}

事务接口：

public interface Transaction {

    Connection getConnection() throws SQLException;

    void commit() throws SQLException;

    void rollback() throws SQLException;

    void close() throws SQLException;

}

事务工厂实现类：

public class JdbcTransactionFactory implements TransactionFactory {

    @Override
    public Transaction newTransaction(Connection conn) {
        return new JdbcTransaction(conn);
    }

    @Override
    public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
        return new JdbcTransaction(dataSource, level, autoCommit);
    }

}

事务实现类：

public class JdbcTransaction implements Transaction {

    protected Connection connection;
    protected DataSource dataSource;
    protected TransactionIsolationLevel level = TransactionIsolationLevel.NONE;
    protected boolean autoCommit;

    public JdbcTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
        this.dataSource = dataSource;
        this.level = level;
        this.autoCommit = autoCommit;
    }

    public JdbcTransaction(Connection connection) {
        this.connection = connection;
    }

    @Override
    public Connection getConnection() throws SQLException {
        connection = dataSource.getConnection();
        connection.setTransactionIsolation(level.getLevel());
        connection.setAutoCommit(autoCommit);
        return connection;
    }

    @Override
    public void commit() throws SQLException {
        if (connection != null && !connection.getAutoCommit()) {
            connection.commit();
        }
    }

    @Override
    public void rollback() throws SQLException {
        if (connection != null && !connection.getAutoCommit()) {
            connection.rollback();
        }
    }

    @Override
    public void close() throws SQLException {
        if (connection != null && !connection.getAutoCommit()) {
            connection.close();
        }
    }
}

对事务的提交，回滚，关闭做了一层封装
getConnection方法获取数据库连接，并可以设置事务隔离级别和事务是否自动提交。

执行器Excutor

Excutor接口定义：

public interface Executor {

    <E> List<E> query(MappedStatement ms, Object[] parameters);
    
    int update(MappedStatement ms, Object[] parameters);

    Transaction getTransaction();

    void commit(boolean required) throws SQLException;

    void rollback(boolean required) throws SQLException;

    void close(boolean forceRollback);

}

执行query，update(包括delete和insert)等操作
事务管理

实现Excutor接口的抽象类AbstractExecutor，这里使用了模板方法：

public abstract class AbstractExecutor implements Executor{
    private org.slf4j.Logger logger = LoggerFactory.getLogger(AbstractExecutor.class);
    Configuration configuration;
    Transaction transaction;
    private boolean closed;

    public AbstractExecutor(Configuration configuration, Transaction transaction) {
        this.configuration = configuration;
        this.transaction = transaction;
    }

    @Override
    public <E> List<E> query(MappedStatement ms, Object[] parameters) {
        if (closed) {
            throw new RuntimeException("Executor was closed.");
        }
        return doQuery(ms, parameters);
    }
    @Override
    public int update(MappedStatement ms, Object[] parameters) {
        if (closed) {
            throw new RuntimeException("Executor was closed.");
        }
        return doUpdate(ms, parameters);
    }

    protected abstract int doUpdate(MappedStatement ms, Object[] parameters);
    
    protected abstract <E> List<E> doQuery(MappedStatement ms, Object[] parameters);

    @Override
    public Transaction getTransaction() {
        return transaction;
    }

    @Override
    public void commit(boolean required) throws SQLException {
        if(closed){
            throw new RuntimeException("Executor was closed.");
        }
        if(required){
            transaction.commit();
        }
    }
   // rallback,close等实现...，
}

该抽象类定义了执行器需要的属性configuration和transaction。
使用模板方法定义了两个抽象方法doQuery和doUpdate来具体实现查询和更新操作的具体逻辑。

默认的执行器实现SimpleExecutor：

public class SimpleExecutor extends AbstractExecutor {
    public SimpleExecutor(Configuration configuration, Transaction transaction) {
        super(configuration, transaction);
    }

    @Override
    protected <E> List<E> doQuery(MappedStatement ms, Object[] parameters) {
       //查询逻辑...
    }

    @Override
    protected int doUpdate(MappedStatement ms, Object[] parameters) {
       //增删改逻辑
        
}

该类继承了Excutor的抽象类，并在该类中实现了查询和更新的具体逻辑，这些具体逻辑会在后面解释，它主要包括了参数处理和结果集处理两个步骤。

最后我们来看执行器Excutor的是在哪里使用：

@Override
public SqlSession openSession() {
    Transaction tx = null;
    try {
        final Environment environment = configuration.getEnvironment();
        TransactionFactory transactionFactory = environment.getTransactionFactory();
        tx = transactionFactory.newTransaction(configuration.getEnvironment().getDataSource(), TransactionIsolationLevel.READ_COMMITTED, false);
        // 创建执行器
        final Executor executor = configuration.newExecutor(tx);
        // 创建DefaultSqlSession
        return new DefaultsSqlSession(configuration, executor);
    } catch (Exception e) {
        try {
            assert tx != null;
            tx.close();
        } catch (SQLException ignore) {
        }
        throw new RuntimeException("Error opening session.  Cause: " + e);
    }
}

我们在获取SqlSession的openSession中获取SqlSession时就会创建一个执行器并传入到DefaultsSqlSession中，因此SqlSession执行sql语句的作用实际上是委托给Excutor执行器的。

Sql执行

Sql执行分为三部：

准备Sql语句
将Sql语句执行
处理执行后的结果集

为了实现上面的需求，我们需要：

一个StatementHandler来通过MapperStatement中的信息来执行Sql得到ResultSet
一个ResultSetHandler处理Sql执行后的ResultSet

然后由Excutor实现doQeury和doUpdate时使用，返回给用户封装好的结果。

StatementHandler

StatementHandler接口：

public interface StatementHandler {

    /**
     * 准备语句
     */
    Statement prepare(Connection connection) throws SQLException;

    /**
     * 参数化
     */
    void parameterize(Statement statement) throws SQLException, IllegalAccessException;

    /**
     * 执行查询
     */
    <E> List<E> query(Statement statement) throws SQLException;

    /**
     *执行更新操作
     */
    int update(Statement statement) throws SQLException;

}

StatementHandler接口定义了Sql执行需要的过程

我们同样为这个接口提供了一个抽象类AbstractStatementHandler：

public abstract class AbstractStatementHandler implements StatementHandler{
    public final Logger logger = LoggerFactory.getLogger(getClass());

    protected final Configuration configuration;

    protected final Executor executor;
    protected final MappedStatement mappedStatement;
    protected final Object[] parameters;
    protected final ResultSetHandler resultSetHandler;

    protected BoundSql boundSql;

    public AbstractStatementHandler(Executor executor, MappedStatement mappedStatement, Object[] parameters) {
        this.configuration = mappedStatement.getConfiguration();
        this.executor = executor;
        this.mappedStatement = mappedStatement;
        this.boundSql = mappedStatement.getBoundSql();
        this.parameters = parameters;
        this.resultSetHandler = configuration.newResultSetHandler(mappedStatement);
    }
    @Override
    public Statement prepare(Connection connection) throws SQLException {
        Statement statement = null;
        try {
            statement = instantiateStatement(connection);
            statement.setQueryTimeout(350);
            statement.setFetchSize(10000);
            return statement;
        }catch (Exception e){
            throw new RuntimeException("Error preparing statement.  Cause: " + e, e);
        }
    }
    protected abstract Statement instantiateStatement(Connection connection) throws SQLException;
}

在这个抽象类中，定义了StatementHandler所需要的属性
实现了接口的prepare方法准备一个Statement，并且将主要逻辑委托了抽象方法instantiateStatement供子类实现，这里是为了程序的扩展性，子类可以通过重写instantiateStatement来对准备Statement的过程进行自定义，我们可以准备一个普通的Statement返回，也可以准备一个预注入的PreparedStatement返回或者其它。

因为在实际使用中我们基本都是使用PreparedStatement进行参数的注入而不是在sql中拼接参数，因此我们仅实现一个PreparedStatementHandler：

public class PreparedStatementHandler extends AbstractStatementHandler {
    public PreparedStatementHandler(Executor executor, MappedStatement mappedStatement, Object[] parameters) {
        super(executor, mappedStatement, parameters);
    }

    @Override
    protected Statement instantiateStatement(@NotNull Connection connection) throws SQLException {
        return connection.prepareStatement(boundSql.getSql());

    }

    @Override
    public void parameterize(Statement statement) throws SQLException, IllegalAccessException {
        System.out.println("==>  Preparing:" + mappedStatement.getBoundSql().getSql().replace("\n","").replace("       ",""));
        Map<String, Object> parameterMap = parseParameters(parameters);
        PreparedStatement ps = (PreparedStatement) statement;
        StringBuilder sb = new StringBuilder();
        for (ParameterMapping parameterMapping : boundSql.getParameterMappings()) {
            Object setValue = getValue(parameterMapping.getProperty(), parameterMap);
            if(parameterMapping.getTypeHandler() == null){
                parameterMapping.setTypeHandler(
                        configuration.getTypeHandlerRegistry().getHandler(setValue.getClass()));
            }
            sb.append(setValue).append("(").append(setValue.getClass().getSimpleName()).append("), ");
            setParam(parameterMapping, ps, setValue);
        }
        System.out.println("==> Parameters: " + sb);

    }

    @Override
    public <E> List<E> query(Statement statement) throws SQLException {
        PreparedStatement ps = (PreparedStatement) statement;
        ps.execute();
        return resultSetHandler.handleResultSets(ps);
    }

    @Override
    public int update(Statement statement) throws SQLException {
        PreparedStatement ps = (PreparedStatement) statement;
        ps.execute();
        int updateCount = ps.getUpdateCount();
        System.out.println("<==    Updates: " +  updateCount);
        return updateCount;
    }

parameterize参数化过程较为复杂，主要是将用户传入的参数和用户在sql语句中编写的${param}对应起来。
注入了参数之后我们就可以执行程序或者更新操作了，其中执行查询后返回的ResultSet的处理交由ResultSetHandler来处理。

ResultSetHandler

ResultSetHandler接口：

public interface ResultSetHandler {

    <E> List<E> handleResultSets(Statement stmt) throws SQLException;

}

public class DefaultResultSetHandler implements ResultSetHandler{
    BoundSql boundSql;
    private final Configuration configuration;
    private final MappedStatement mappedStatement;

    private final TypeHandlerRegistry typeHandlerRegistry;
    public DefaultResultSetHandler(MappedStatement mappedStatement){
        this.configuration = mappedStatement.getConfiguration();
        this.mappedStatement = mappedStatement;
        this.boundSql = mappedStatement.getBoundSql();
        this.typeHandlerRegistry = configuration.getTypeHandlerRegistry();
    }
    @Override
    public <T> List<T> handleResultSets(Statement stmt) throws SQLException {
        ResultSet resultSet = stmt.getResultSet();
        return (List<T>) resultSet2Obj(resultSet, boundSql.getResultType());
    }

    private <T> List<T> resultSet2Obj(ResultSet resultSet, Class<T> clazz) {
        List<T> list = new ArrayList<>();
        try {
            // 每次遍历行值
            while (resultSet.next()) {
                T obj = getRowValue(resultSet, clazz);
                list.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("<==      Total: " + list.size());
        return list;
    }

结果集的处理也较为复杂，主要是将ResultSet中的值封装为用户定义的返回值类型去返回。

doQuery和doUpdate

有了StatementHandler和ResultSetHandler之后，我们就可以在查询或者更新的逻辑里面使用它们真正完成数据的增删查改了：

@Override
protected <E> List<E> doQuery(MappedStatement ms, Object[] parameters) {
    Statement stmt = null;
    try {
        StatementHandler handler = configuration.newStatementHandler(this, ms, parameters);
        Connection connection = transaction.getConnection();
        stmt = handler.prepare(connection);
        handler.parameterize(stmt);
        return handler.query(stmt);
    } catch (SQLException | IllegalAccessException e) {
        return null;
    }finally {
        closeStatement(stmt);
    }
}

@Override
protected int doUpdate(MappedStatement ms, Object[] parameters) {
    Statement stmt = null;
    try {
        StatementHandler handler = configuration.newStatementHandler(this, ms, parameters);
        Connection connection = transaction.getConnection();
        stmt = handler.prepare(connection);
        handler.parameterize(stmt);
        return handler.update(stmt);
    } catch (SQLException | IllegalAccessException e) {
        return 0;
    }finally {
        closeStatement(stmt);
    }
}

测试

目前我们已经基本能够实现mybatis的主要内容了，需要用户提供以下条件即可使用：

配置文件
映射文件
接口

mybatis配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="DRUID">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true"/>
                <property name="username" value="root"/>
                <property name="password" value="asdqwe123"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/User_Mapper.xml"/>
    </mappers>
    <settings>
        <setting name="ignorePrefix" value="tb_"/>
        <setting name="mapUnderscoreToCamelCase" value="false"/>
    </settings>
</configuration>

我们配置了数据源，还有Mapper文件位置和数据库字段和pojo属性的映射，支持前缀后缀和驼峰映射。

映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test_04.duan.mapper.UserMapper">

    <select id="queryUserInfoById" parameterType="long" resultMap="activityMap">
        SELECT *
        FROM users
        where tb_user_id = #{userId}
    </select>
    <select id="queryUsersInfoById" parameterType="test_03.po.User" resultMap="activityMap">
        SELECT *
        FROM users
        where tb_user_age = #{userAge}
    </select>
    <select id="countAge"  resultType="long">
        SELECT count(*)
        FROM users
        where tb_user_age between #{param1} and #{param2}
    </select>
    <resultMap id="activityMap" type="test_03.po.User">
        <id column="tb_user_id" property="userId"/>
        <result column="tb_user_name" property="userName"/>
        <result column="tb_user_age" property="userAge"/>
    </resultMap>

</mapper>

我们的映射文件还支持resultMap的结果集映射来解决数据库字段和pojo属性名不匹配的情况。

接口

public interface UserMapper {
    User queryUserInfoById(@Param("userId") Long userId);
    List<User> queryUsersInfoById(User user);
    Long countAge(Long minAge,Long maxAge);

}

我们也同样支持@Param注解来与sql语句中的${}来匹配

调用

@Test
public void test_SqlSessionFactory() throws IOException {
    // 1. 从SqlSessionFactory中获取SqlSession
    Reader reader = Resources.getResourceAsReader("mybatis-config-datasource.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    // 2. 获取映射器对象
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    // 3. 测试验证
    User res = userMapper.queryUserInfoById(1L);
    User user2 = new User();
    user2.setUserAge(20);
    List<User> user1List = userMapper.queryUsersInfoById(user2);
    Long nums = userMapper.countAge(18L, 20L);
    logger.info("测试结果：{},{},{}", res, user1List,nums);
}

输出结果：

==>  Preparing: SELECT * FROM users where tb_user_id = ?    
==> Parameters: 1(Long), 
<==      Total: 1
==>  Preparing: SELECT * FROM users where tb_user_age = ?    
==> Parameters: 20(Integer), 
<==      Total: 2
==>  Preparing: SELECT count(*) FROM users where tb_user_age between ? and ?    
==> Parameters: 18(Long), 20(Long), 
<==      Total: 1
22:58:30.827 [main] INFO test_03.ApiTest -- 测试结果：User1{userId=1, userName='张三', userAge=20},[User1{userId=1, userName='张三', userAge=20}, User1{userId=4, userName='王五', userAge=20}],3

我们还仿照了mybatis的日志输出了sql语句，参数和参数类型，以及返回的值。
18L, 20L);
logger.info(“测试结果：{},{},{}”, res, user1List,nums);
}


输出结果：

```java
==>  Preparing: SELECT * FROM users where tb_user_id = ?    
==> Parameters: 1(Long), 
<==      Total: 1
==>  Preparing: SELECT * FROM users where tb_user_age = ?    
==> Parameters: 20(Integer), 
<==      Total: 2
==>  Preparing: SELECT count(*) FROM users where tb_user_age between ? and ?    
==> Parameters: 18(Long), 20(Long), 
<==      Total: 1
22:58:30.827 [main] INFO test_03.ApiTest -- 测试结果：User1{userId=1, userName='张三', userAge=20},[User1{userId=1, userName='张三', userAge=20}, User1{userId=4, userName='王五', userAge=20}],3

我们还仿照了mybatis的日志输出了sql语句，参数和参数类型，以及返回的值。

白日905

关注

27
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
10.搭建Sql执行器

logger.info(“测试结果：{},{},{}”, res, user1List,nums);在搭建了基础框架之后，我们就可以使用执行器Excutor根据XML的信息来执行Sql并拿到值了。我们还仿照了mybatis的日志输出了sql语句，参数和参数类型，以及返回的值。我们还仿照了mybatis的日志输出了sql语句，参数和参数类型，以及返回的值。我们通过传入DataSource必要的配置项来创建了一个德鲁伊的数据源，并通过。进行参数的注入而不是在sql中拼接参数，因此我们仅实现一个。
复制链接

扫一扫