MyBatis常用工具类三-使用SqlRunner操作数据库

MyBatis中提供了一个非常实用的、用于操作数据库的SqlRunner工具类，该类对JDBC做了很好的封装，结合SQL工具类，能够很方便地通过Java代码执行SQL语句并检索SQL执行结果。

SqlRunner类提供了几个操作数据库的方法，分别说明如下。

SqlRunner#closeConnection()：用于关闭Connection对象。

SqlRunner#selectOne(String sql, Object… args)：执行SELECT语句，SQL语句中可以使用占位符，如果SQL中包含占位符，则可变参数用于为参数占位符赋值，该方法只返回一条记录。若查询结果行数不等于一，则会抛出SQLException异常。

SqlRunner#selectAll(String sql, Object… args)：该方法和selectOne()方法的作用相同，只不过该方法可以返回多条记录，方法返回值是一个List对象，List中包含多个Map对象，每个Map对象对应数据库中的一行记录。

SqlRunner#insert(String sql, Object… args)：执行一条INSERT语句，插入一条记录。

SqlRunner#update(String sql, Object… args)：更新若干条记录。

SqlRunner#delete(String sql, Object… args)：删除若干条记录。

SqlRunner#run(String sql)：执行任意一条SQL语句，最好为DDL语句。

接下来我们来看一下SqlRunner工具类的使用案例，代码如下：

public class SqlRunnerExample {

    Connection connection = null;

    @Before
    public void initTable() throws SQLException, IOException {
        connection = DriverManager.getConnection("jdbc:hsqldb:mem:mybatis",
                "sa", "");
        ScriptRunner scriptRunner = new ScriptRunner(connection);
        scriptRunner.setLogWriter(null);
        scriptRunner.runScript(Resources.getResourceAsReader("create-table.sql"));
        scriptRunner.runScript(Resources.getResourceAsReader("init-data.sql"));
    }

    @Test
    public void testSelectOne() throws SQLException {
        SqlRunner sqlRunner = new SqlRunner(connection);
        String qryUserSql = new SQL() {{
            SELECT("*");
            FROM("user");
            WHERE("id = ?");
        }}.toString();
        Map<String, Object> resultMap = sqlRunner.selectOne(qryUserSql, Integer.valueOf(1));
        System.out.println(JSON.toJSONString(resultMap));
    }

    @Test
    public void testDelete() throws SQLException {
        SqlRunner sqlRunner = new SqlRunner(connection);
        String deleteUserSql = new SQL(){{
            DELETE_FROM("user");
            WHERE("id = ?");
        }}.toString();
        sqlRunner.delete(deleteUserSql, Integer.valueOf(1));
    }

    @Test
    public void testUpdate() throws SQLException {
        SqlRunner sqlRunner = new SqlRunner(connection);
        String updateUserSql = new SQL(){{
            UPDATE("user");
            SET("nick_name = ?");
            WHERE("id = ?");
        }}.toString();
        sqlRunner.update(updateUserSql, "Jane", Integer.valueOf(1));
    }

    @Test
    public void testInsert() throws SQLException {
        SqlRunner sqlRunner = new SqlRunner(connection);
        String insertUserSql = new SQL(){{
            INSERT_INTO("user");
            INTO_COLUMNS("create_time,name,password,phone,nick_name");
            INTO_VALUES("?,?,?,?,?");
        }}.toString();
        String createTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        sqlRunner.insert(insertUserSql,createTime,"Jane","test","18700000000","J");
    }

    @After
    public void closeConnection() {
        IOUtils.closeQuietly(connection);
    }


}

除了查询外，我们还可以使用SqlRunner完成数据库的增删改操作。

熟悉了SqlRunner工具类的使用后，接下来我们了解一下SqlRunner工具类的实现。以selectAll()方法为例，该方法代码如下：

/*
   * Executes a SELECT statement that returns multiple rows.
   *
   * @param sql  The SQL
   * @param args The arguments to be set on the statement.
   * @return The list of rows expected.
   * @throws SQLException If statement preparation or execution fails
   */
  public List<Map<String, Object>> selectAll(String sql, Object... args) throws SQLException {
    PreparedStatement ps = connection.prepareStatement(sql);
    try {
      setParameters(ps, args);
      ResultSet rs = ps.executeQuery();
      return getResults(rs);
    } finally {
      try {
        ps.close();
      } catch (SQLException e) {
        //ignore
      }
    }
  }

如上面的代码所示，SqlRunner工具类的selectAll()逻辑如下：

（1）调用Connection对象的prepareStatement()方法获取PreparedStatement对象后，调用setParameters()方法为SQL中的参数占位符赋值。

（2）调用PreparedStatement的executeQuery()方法执行查询操作。

（3）调用getResults()方法，将ResultSet对象转换为List对象，其中List对象中的每个Map对象对应数据库中的一条记录。

接下来我们来看一下setParameters()方法的实现，代码如下：

 private void setParameters(PreparedStatement ps, Object... args) throws SQLException {
    for (int i = 0, n = args.length; i < n; i++) {
      if (args[i] == null) {
        throw new SQLException("SqlRunner requires an instance of Null to represent typed null values for JDBC compatibility");
      } else if (args[i] instanceof Null) {
        ((Null) args[i]).getTypeHandler().setParameter(ps, i + 1, null, ((Null) args[i]).getJdbcType());
      } else {
        TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(args[i].getClass());
        if (typeHandler == null) {
          throw new SQLException("SqlRunner could not find a TypeHandler instance for " + args[i].getClass());
        } else {
          typeHandler.setParameter(ps, i + 1, args[i], null);
        }
      }
    }
  }

对setParameters()方法的可变参数进行遍历，根据参数类型获取对应的TypeHandler对象，然后调用MyBatis中TypeHandler对象的setParameter()方法为参数占位符赋值。MyBatis中TypeHandler的作用及实现原理将在后面的章节中详细介绍。

最后，我们再来看一下getResultSet()方法处理结果集的过程，代码如下：

private List<Map<String, Object>> getResults(ResultSet rs) throws SQLException {
    try {
      List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
      List<String> columns = new ArrayList<String>();
      List<TypeHandler<?>> typeHandlers = new ArrayList<TypeHandler<?>>();
      // 1.获取ResultSetMetaData对象。学习JDBC规范时，我们了解到ResultSetMetaData对象中封装了结果集的元数据信息，
      // 包括所有的字段名称及列的数量等信息
      ResultSetMetaData rsmd = rs.getMetaData();
      for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) {
        columns.add(rsmd.getColumnLabel(i + 1));
        try {
          // 获取所有列的java类型。
          // 根据类型获取对应的TypeHandler对象，将TypeHandler对象注册到变量名为typeHandlers的ArrayList对象中
          Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1));
          TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type);
          if (typeHandler == null) {
            typeHandler = typeHandlerRegistry.getTypeHandler(Object.class);
          }
          typeHandlers.add(typeHandler);
        } catch (Exception e) {
          typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class));
        }
      }

      // 遍历ResultSet对象。调用TypeHandler对象的getSesult()方法，
      // 将JDBC类型转换为Java类型，然后将ResultSet对象中的记录行转换为Map对象
      while (rs.next()) {
        Map<String, Object> row = new HashMap<String, Object>();
        for (int i = 0, n = columns.size(); i < n; i++) {
          String name = columns.get(i);
          TypeHandler<?> handler = typeHandlers.get(i);
          row.put(name.toUpperCase(Locale.ENGLISH), handler.getResult(rs, name));
        }
        list.add(row);
      }
      return list;
    } finally {
      if (rs != null) {
        try {
            rs.close();
        } catch (Exception e) {
          // ignore
        }
      }
    }
  }

如上面的代码所示，getResultSet()方法处理结果集的过程如下：

（1）获取ResultSetMetaData对象。学习JDBC规范时，我们了解到ResultSetMetaData对象中封装了结果集的元数据信息，包括所有的字段名称及列的数量等信息。getResultSet()方法中通过ResultSetMetaData对象获取所有列的名称。

（2）获取所有列的JDBC类型。根据类型获取对应的TypeHandler对象，将TypeHandler对象注册到变量名为typeHandlers的ArrayList对象中。

（3）遍历ResultSet对象。调用TypeHandler对象的getSesult()方法，将JDBC类型转换为Java类型，然后将ResultSet对象中的记录行转换为Map对象。