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语句,参数和参数类型,以及返回的值。