1、JDBC执行查询语句

再回忆一下最初写JDBC的语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public static void select() {
    String sql = "select * from people where name = ? order by id";
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs;
    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, "李四");
        rs = pstmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println("id:" + id + ",name:" + name);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        close(pstmt);
        close(conn);
    }
}

例子在本系列文章的开篇中简单的介绍了，这里改进了一些，简单总结一下步骤：

  1. 获取Connection
  2. 获取PreparedStatement
  3. 设置PreparedStatement参数
  4. 执行SQL查询executeQuery
  5. 获取查询结果ResultSet
  6. 关闭Connection和PreparedStatement资源

再来看MyBatis是怎么执行的查询语句。

2、MyBatis执行查询语句

经过前几篇文章的分析，已经获取到了Mapper的代理实例，调用Mapper中的方法的时候，会执行MapperProxy的invoke方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@Override
 public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
   try {
 //如果是Object中的方法，比如toString(),hashCode()，则直接执行
     if (Object.class.equals(method.getDeclaringClass())) {
       return method.invoke(this, args);
     } else if (isDefaultMethod(method)) {
  //如果是接口中的默认方法，走这里
       return invokeDefaultMethod(proxy, method, args);
     }
   } catch (Throwable t) {
     throw ExceptionUtil.unwrapThrowable(t);
   }
//首先从缓存中获取MapperMethod，若缓存未命中，则创建MapperMethod
   final MapperMethod mapperMethod = cachedMapperMethod(method);
//调用MapperMethod的execte方法
   return mapperMethod.execute(sqlSession, args);
 }

简单的看一看MapperMethod的结构：

1
2
3
4
5
6
private final SqlCommand command;
private final MethodSignature method;
public MapperMethod(Class<?> mapperInterface, Method method, Configuration config) {
    this.command = new SqlCommand(config, mapperInterface, method);
    this.method = new MethodSignature(config, mapperInterface, method);
}

也就是说执行mapper代理实例的一个方法的时候，都会有对应一个MapperMethod。再来看一下其中包含的两个属性是什么：

2.1、SqlCommand

SQL相关信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public SqlCommand(Configuration configuration, Class<?> mapperInterface, Method method) {
     //获取方法名称
     final String methodName = method.getName();
     //类，这里为接口的类，比如PeopleMapper
     final Class<?> declaringClass = method.getDeclaringClass();
     //获取MappedStatement，tatementId：接口的全量名+"."+方法名
     MappedStatement ms = resolveMappedStatement(mapperInterface, methodName, declaringClass,
         configuration);
     if (ms == null) {
       if (method.getAnnotation(Flush.class) != null) {
         name = null;
         type = SqlCommandType.FLUSH;
       } else {
         throw new BindingException("Invalid bound statement (not found): "
             + mapperInterface.getName() + "." + methodName);
       }
     } else {
       name = ms.getId();
       type = ms.getSqlCommandType();
       if (type == SqlCommandType.UNKNOWN) {
         throw new BindingException("Unknown execution method for: " + name);
       }
     }
   }

MappedStatement表示的是XML中的一个SQL:

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
public final class MappedStatement {
  
  private String resource;
  private Configuration configuration;
  //sql的ID
  private String id;
  //尝试影响驱动程序每次批量返回的结果行数和这个设置值相等
  private Integer fetchSize;
  //SQL超时时间
  private Integer timeout;
  //Statement的类型，STATEMENT/PREPARE/CALLABLE
  private StatementType statementType;
  //结果集类型，FORWARD_ONLY/SCROLL_SENSITIVE/SCROLL_INSENSITIVE 
  private ResultSetType resultSetType;
  //表示解析出来的SQL
  private SqlSource sqlSource;
  //缓存
  private Cache cache;
  //已废弃
  private ParameterMap parameterMap;
  //对应的ResultMap
  private List<ResultMap> resultMaps;
  private boolean flushCacheRequired;
  private boolean useCache;
  private boolean resultOrdered;
  //SQL类型，INSERT/SELECT/DELETE
  private SqlCommandType sqlCommandType;
  //和SELECTKEY标签有关
  private KeyGenerator keyGenerator;
  private String[] keyProperties;
  private String[] keyColumns;
  private boolean hasNestedResultMaps;
  //数据库ID，用来区分不同环境
  private String databaseId;
  private Log statementLog;
  private LanguageDriver lang;
  //多结果集时
  private String[] resultSets;

  MappedStatement() {
    // constructor disabled
  }
}

2.2、MethodSignature

保存了一些和目标方法相关的信息，比如目标方法的返回类型，目标方法的参数列表信息等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public MethodSignature(Configuration configuration, Class<?> mapperInterface, Method method) {
     //获取返回类型
     Type resolvedReturnType = TypeParameterResolver.resolveReturnType(method, mapperInterface);
     if (resolvedReturnType instanceof Class<?>) {
       this.returnType = (Class<?>) resolvedReturnType;
     } else if (resolvedReturnType instanceof ParameterizedType) {
       this.returnType = (Class<?>) ((ParameterizedType) resolvedReturnType).getRawType();
     } else {
       this.returnType = method.getReturnType();
     }
     //void
     this.returnsVoid = void.class.equals(this.returnType);
     //集合或者数组
     this.returnsMany = configuration.getObjectFactory().isCollection(this.returnType) || this.returnType.isArray();
     //游标
     this.returnsCursor = Cursor.class.equals(this.returnType);
     //MapKey注解
     this.mapKey = getMapKey(method);
     this.returnsMap = this.mapKey != null;
     this.rowBoundsIndex = getUniqueParamIndex(method, RowBounds.class);
     this.resultHandlerIndex = getUniqueParamIndex(method, ResultHandler.class);
     //参数列表解析
     this.paramNameResolver = new ParamNameResolver(configuration, method);
   }

如果方法上加了@Param的参数：
People getPeople(@Param(value=”id”)String id,@Param(value=”name”)String name);，参数会被解析成{0=id, 1=name}。

3、执行MapperMethod的execute方法

这一步到了执行sql的入口：

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
public Object execute(SqlSession sqlSession, Object[] args) {
       Object result;
       //根据sql类型执行操作
       switch (command.getType()) {
           case INSERT: {
               //参数转换
               Object param = method.convertArgsToSqlCommandParam(args);
               //插入操作
               result = rowCountResult(sqlSession.insert(command.getName(), param));
               break;
           }
           case UPDATE: {
               //参数转换
               Object param = method.convertArgsToSqlCommandParam(args);
               //更新操作
               result = rowCountResult(sqlSession.update(command.getName(), param));
               break;
           }
           case DELETE: {
               //参数转换
               Object param = method.convertArgsToSqlCommandParam(args);
               //删除操作
               result = rowCountResult(sqlSession.delete(command.getName(), param));
               break;
           }
           //查询
           case SELECT:
               //根据返回值的类型操作
               if (method.returnsVoid() && method.hasResultHandler()) {
                   //返回值为void,有自定义的结果处理器
                   executeWithResultHandler(sqlSession, args);
                   result = null;
               } else if (method.returnsMany()) {
                   //返回多个
                   result = executeForMany(sqlSession, args);
               } else if (method.returnsMap()) {
                   //返回map
                   result = executeForMap(sqlSession, args);
               } else if (method.returnsCursor()) {
                   //返回游标
                   result = executeForCursor(sqlSession, args);
               } else {
                   //参数转换
                   Object param = method.convertArgsToSqlCommandParam(args);
                   //查询一条
                   result = sqlSession.selectOne(command.getName(), param);
               }
               break;
           case FLUSH:
               //刷新
               result = sqlSession.flushStatements();
               break;
           default:
               throw new BindingException("Unknown execution method for: " + command.getName());
       }
       //最终异常判断
       if (result == null && method.getReturnType().isPrimitive() && !method.returnsVoid()) {
           throw new BindingException("Mapper method '" + command.getName()
                   + " attempted to return null from a method with a primitive return type (" + method.getReturnType() + ").");
       }
       return result;
   }

代码很清晰。

3.1、查询操作

这里以PeopleMapper.query为例子分析，因为我的例子是返回PeopleEntity。

1
2
3
public interface PeopleMapper {
    PeopleEntity query(@Param("id") int id, @Param("name") String name);
}

会走到selectOne分支：

1
2
3
4
//参数转换
Object param = method.convertArgsToSqlCommandParam(args);
//查询一条
result = sqlSession.selectOne(command.getName(), param);

这里的param：

1
2
3
4
"name"->"李四"
"id"->"1"
"param1"->"1"
"param2"->"李四"

进入selectOne方法：

1
2
3
4
@Override
 public <T> T selectOne(String statement, Object parameter) {
   return this.sqlSessionProxy.<T> selectOne(statement, parameter);
 }

这里面的statement为：com.demo.mybatis.mapper.PeopleMapper.query,参数还是上一步的param。sqlSessionProxy是org.apache.ibatis.session.defaults.DefaultSqlSession，是什么时候设置的呢？
简单分析一下，在生成MapperFactoryBean的时候会按类型注入SqlSessionFactory：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
   if (!this.externalSqlSession) {
     this.sqlSession = new SqlSessionTemplate(sqlSessionFactory);
   }
 }
public SqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType,
     PersistenceExceptionTranslator exceptionTranslator) {

   notNull(sqlSessionFactory, "Property 'sqlSessionFactory' is required");
   notNull(executorType, "Property 'executorType' is required");

//这里为DefaultSqlSessionFactory
   this.sqlSessionFactory = sqlSessionFactory;
//SIMPLE
   this.executorType = executorType;
   this.exceptionTranslator = exceptionTranslator;
   this.sqlSessionProxy = (SqlSession) newProxyInstance(
       SqlSessionFactory.class.getClassLoader(),
       new Class[] { SqlSession.class },
       new SqlSessionInterceptor());
 }

看到JDK动态代理了，sqlSessionProxy为DefaultSqlSession,当执行DefaultSqlSession的方法时，会被SqlSessionInterceptor拦截。
再回头看sqlSessionProxy的selectOne:

1
2
3
4
@Override
 public <T> T selectOne(String statement, Object parameter) {
   return this.sqlSessionProxy.<T> selectOne(statement, parameter);
 }

在执行这个方法selectOne方法的时候会执行SqlSessionTemplate中的SqlSessionInterceptor的invoke:

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
@Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
      SqlSession sqlSession = getSqlSession(
          SqlSessionTemplate.this.sqlSessionFactory,
          SqlSessionTemplate.this.executorType,
          SqlSessionTemplate.this.exceptionTranslator);
      try {
        Object result = method.invoke(sqlSession, args);
        if (!isSqlSessionTransactional(sqlSession, SqlSessionTemplate.this.sqlSessionFactory)) {
          // force commit even on non-dirty sessions because some databases require
          // a commit/rollback before calling close()
          sqlSession.commit(true);
        }
        return result;
      } catch (Throwable t) {
        Throwable unwrapped = unwrapThrowable(t);
        if (SqlSessionTemplate.this.exceptionTranslator != null && unwrapped instanceof PersistenceException) {
          // release the connection to avoid a deadlock if the translator is no loaded. See issue #22
          closeSqlSession(sqlSession, SqlSessionTemplate.this.sqlSessionFactory);
          sqlSession = null;
          Throwable translated = SqlSessionTemplate.this.exceptionTranslator.translateExceptionIfPossible((PersistenceException) unwrapped);
          if (translated != null) {
            unwrapped = translated;
          }
        }
        throw unwrapped;
      } finally {
        if (sqlSession != null) {
          closeSqlSession(sqlSession, SqlSessionTemplate.this.sqlSessionFactory);
        }
      }
    }

首先获取SqlSession，之后执行DefaultSqlSession中的selectOne，再执行sqlSession.commit(true)，最后关闭sqlSession；
再来看DefaultSqlSession中的selectOne:

1
2
3
4
5
6
7
8
9
10
11
12
@Override
public <T> T selectOne(String statement, Object parameter) {
  // Popular vote was to return null on 0 results and throw exception on too many.
  List<T> list = this.<T>selectList(statement, parameter);
  if (list.size() == 1) {
    return list.get(0);
  } else if (list.size() > 1) {
    throw new TooManyResultsException("Expected one result (or null) to be returned by selectOne(), but found: " + list.size());
  } else {
    return null;
  }
}

这块的异常很熟悉啊，发现内部调用了selectList。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Override
 public <E> List<E> selectList(String statement, Object parameter) {
   return this.selectList(statement, parameter, RowBounds.DEFAULT);
 }
@Override
 public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {
   try {
     MappedStatement ms = configuration.getMappedStatement(statement);
     return executor.query(ms, wrapCollection(parameter), rowBounds, Executor.NO_RESULT_HANDLER);
   } catch (Exception e) {
     throw ExceptionFactory.wrapException("Error querying database.  Cause: " + e, e);
   } finally {
     ErrorContext.instance().reset();
   }
 }

首先获取MappedStatement,之后调用executor的query,这里的executor为CachingExecutor。

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
@Override
  public <E> List<E> query(MappedStatement ms, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException {
    //获取BoundSql,这里包含了xml中的sql（未知参数值用？代替）,还有具体的参数值。
    BoundSql boundSql = ms.getBoundSql(parameterObject);
	//创建缓存key
    CacheKey key = createCacheKey(ms, parameterObject, rowBounds, boundSql);
	//查询
    return query(ms, parameterObject, rowBounds, resultHandler, key, boundSql);
  }

 @Override
  public <E> List<E> query(MappedStatement ms, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, CacheKey key, BoundSql boundSql)
      throws SQLException {
    Cache cache = ms.getCache();
    if (cache != null) {
      flushCacheIfRequired(ms);
      if (ms.isUseCache() && resultHandler == null) {
        ensureNoOutParams(ms, boundSql);
        @SuppressWarnings("unchecked")
        List<E> list = (List<E>) tcm.getObject(cache, key);
        if (list == null) {
          list = delegate.<E> query(ms, parameterObject, rowBounds, resultHandler, key, boundSql);
          tcm.putObject(cache, key, list); // issue #578 and #116
        }
        return list;
      }
    }
    return delegate.<E> query(ms, parameterObject, rowBounds, resultHandler, key, boundSql);
  }

首先从缓存里获取，获取不到就调用被装饰类的query方法(这里为SimpleExecutor的父类BaseExecutor中实现)：

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
@Override
 public <E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey key, BoundSql boundSql) throws SQLException {
   ErrorContext.instance().resource(ms.getResource()).activity("executing a query").object(ms.getId());
   if (closed) {
     throw new ExecutorException("Executor was closed.");
   }
   if (queryStack == 0 && ms.isFlushCacheRequired()) {
     clearLocalCache();
   }
   List<E> list;
   try {
     queryStack++;
     list = resultHandler == null ? (List<E>) localCache.getObject(key) : null;
     if (list != null) {
       handleLocallyCachedOutputParameters(ms, key, parameter, boundSql);
     } else {
       list = queryFromDatabase(ms, parameter, rowBounds, resultHandler, key, boundSql);
     }
   } finally {
     queryStack--;
   }
   if (queryStack == 0) {
     for (DeferredLoad deferredLoad : deferredLoads) {
       deferredLoad.load();
     }
     // issue #601
     deferredLoads.clear();
     if (configuration.getLocalCacheScope() == LocalCacheScope.STATEMENT) {
       // issue #482
       clearLocalCache();
     }
   }
   return list;
 }

从一级缓存获取，获取不到再查找数据库。这里分析从数据库查找。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
private <E> List<E> queryFromDatabase(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey key, BoundSql boundSql) throws SQLException {
    List<E> list;
    localCache.putObject(key, EXECUTION_PLACEHOLDER);
    try {
      list = doQuery(ms, parameter, rowBounds, resultHandler, boundSql);
    } finally {
      localCache.removeObject(key);
    }
    localCache.putObject(key, list);
    if (ms.getStatementType() == StatementType.CALLABLE) {
      localOutputParameterCache.putObject(key, parameter);
    }
    return list;
  }

这里有一些缓存处理，在就是调用doQuery，这里由子类实现。

1
2
3
4
5
6
7
8
9
10
11
12
@Override
  public <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
    Statement stmt = null;
    try {
      Configuration configuration = ms.getConfiguration();
      StatementHandler handler = configuration.newStatementHandler(wrapper, ms, parameter, rowBounds, resultHandler, boundSql);
      stmt = prepareStatement(handler, ms.getStatementLog());
      return handler.<E>query(stmt, resultHandler);
    } finally {
      closeStatement(stmt);
    }
  }

首先获取StatementHandler，之后创建Statement.在这一步会将查询参数设置完成，最后执行query。
这里看PreparedStatementHandler中的query:

1
2
3
4
5
6
@Override
public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
  PreparedStatement ps = (PreparedStatement) statement;
  ps.execute();
  return resultSetHandler.<E> handleResultSets(ps);
}

这里就很熟悉了，执行ps.execute()，获取到结果之后，处理结果集，
这里的resultSetHandler为DefaultResultSetHandler。

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
@Override
 public List<Object> handleResultSets(Statement stmt) throws SQLException {
   ErrorContext.instance().activity("handling results").object(mappedStatement.getId());

   final List<Object> multipleResults = new ArrayList<Object>();

   int resultSetCount = 0;
   ResultSetWrapper rsw = getFirstResultSet(stmt);

   List<ResultMap> resultMaps = mappedStatement.getResultMaps();
   int resultMapCount = resultMaps.size();
   validateResultMapsCount(rsw, resultMapCount);
   while (rsw != null && resultMapCount > resultSetCount) {
     ResultMap resultMap = resultMaps.get(resultSetCount);
     handleResultSet(rsw, resultMap, multipleResults, null);
     rsw = getNextResultSet(stmt);
     cleanUpAfterHandlingResultSet();
     resultSetCount++;
   }

   String[] resultSets = mappedStatement.getResultSets();
   if (resultSets != null) {
     while (rsw != null && resultSetCount < resultSets.length) {
       ResultMapping parentMapping = nextResultMaps.get(resultSets[resultSetCount]);
       if (parentMapping != null) {
         String nestedResultMapId = parentMapping.getNestedResultMapId();
         ResultMap resultMap = configuration.getResultMap(nestedResultMapId);
         handleResultSet(rsw, resultMap, null, parentMapping);
       }
       rsw = getNextResultSet(stmt);
       cleanUpAfterHandlingResultSet();
       resultSetCount++;
     }
   }

   return collapseSingleResultList(multipleResults);
 }

其中的getFirstResultSet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
private ResultSetWrapper getFirstResultSet(Statement stmt) throws SQLException {
   ResultSet rs = stmt.getResultSet();
   while (rs == null) {
     // move forward to get the first resultset in case the driver
     // doesn't return the resultset as the first result (HSQLDB 2.1)
     if (stmt.getMoreResults()) {
       rs = stmt.getResultSet();
     } else {
       if (stmt.getUpdateCount() == -1) {
         // no more results. Must be no resultset
         break;
       }
     }
   }
   return rs != null ? new ResultSetWrapper(rs, configuration) : null;
 }

以上整个获取结果集的步骤就是对JDBC的封装，具体细节太多了，看不下去了。

至此获取了数据库中的结果。

4、简单总结

  1. 获取sqlSession
  2. 获取BoundSql随想，根据传入的参数，获取最终的sql
  3. 从数据库连接池中获取Connection对象
  4. 从Connection中获取PreparedStatement对象，并设置参数
  5. 执行查询
  6. 从结果集中获取结果，并转java对象
  7. sqlSession提交
  8. 释放sqlSession资源

发现这个过程和JDBC操作mysql的主流程是一样的，这也更加的证实MyBatis就是对JDBC的封装以便更好地使用。

tencent.jpg