mybatis实践篇（一）

日志（logImpl）

StdOutImpl

  <setting name="logImpl" value="org.apache.ibatis.logging.stdout.StdOutImpl"/>

Slf4jImpl

<setting name="logImpl" value="org.apache.ibatis.logging.slf4j.Slf4jImpl"/>

引入maven文件

<dependency>
  <groupId>org.slf4j</groupId>
  <artifactId>slf4j-simple</artifactId>
  <version>2.0.12</version>
</dependency>

编写配置文件

org.slf4j.simpleLogger.defaultLogLevel=debug
org.slf4j.simpleLogger.showDateTime=true
org.slf4j.simpleLogger.dateTimeFormat=yyyy-MM-dd HH:mm:ss
org.slf4j.simpleLogger.levelInBrackets=true
org.slf4j.simpleLogger.logFile=System.out

执行器

默认执行器（SimpleExecutor）

try(SqlSession sqlSession = sqlSessionFactory.openSession()) {
    // 使用SqlSession获取映射器实例
    FullCityMapper mapper = sqlSession.getMapper(FullCityMapper.class);
    // 使用映射器执行操作
    FullCity fullCity = mapper.selectByName("广东省");
    System.out.println("城市的名称："+fullCity.getName());
}

重用执行器（ReuseExecutor）

try(SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.REUSE)) {
    // 使用SqlSession获取映射器实例
    FullCityMapper mapper = sqlSession.getMapper(FullCityMapper.class);
    // 使用映射器执行操作
    FullCity fullCity = mapper.selectByName("广东省");
    System.out.println("城市的名称："+fullCity.getName());
}

批量执行器（BatchExecutor）

try(SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            // 使用SqlSession获取映射器实例
            FullCityMapper mapper = sqlSession.getMapper(FullCityMapper.class);
            // 使用映射器执行操作
            FullCity fullCity = mapper.selectByName("广东省");
            System.out.println("城市的名称："+fullCity.getName());
        }

起别名

配置文件

    <typeAliases>
<!--        <typeAlias type="com.wyl.mybatis.entity.FullCity"/>-->
        <package name="com.wyl.mybatis.entity"/>
    </typeAliases>

mapper文件

<select id="selectByName" resultType="FullCity">
        select * from d_full_city where name = #{name}
    </select>

插件

注解

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Intercepts {
  /**
   * Returns method signatures to intercept.
   *
   * @return method signatures
   */
  Signature[] value();
}

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({})
public @interface Signature {
  // Executor.class
  Class<?> type();

  // update
  String method();

  // MappedStatement.class, Object.class
  Class<?>[] args();
}

主要用到了上面两个注解：@Intercepts和@Signature 方法名和参数的定义如下：

时间插件

作用：打印SQL语句执行的时间，分析慢查询原因（一般针对查询query来说）

package com.wyl.mybatis.intercept;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONUtil;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description 时间插件
 * @Author WuYiLong
 * @Date 2024/2/29 9:51
 */
@Intercepts({@Signature(
        type = Executor.class,
        method = "query",
        args = {
                MappedStatement.class,
                Object.class,
                RowBounds.class,
                ResultHandler.class
        })})
public class TimeIntercept implements Interceptor {

    private final  static Logger log = LoggerFactory.getLogger(TimeIntercept.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement)args[0];
        MapperMethod.ParamMap pm = ( MapperMethod.ParamMap) args[1];

        Map<String, Object> paramsMap = new HashMap<>();
        pm.forEach((k,v)->{
            String key = String.valueOf(k);
            if(!key.contains("param")) {
                paramsMap.put(String.valueOf(k),v);
            }
        });

        BoundSql boundSql = ms.getBoundSql(ms.getParameterMap());
        // sql执行之前
        long startTime = System.currentTimeMillis();
        Object proceed = invocation.proceed();
        JSONArray jsonArray = JSONUtil.parseArray(proceed);
        long endTime = System.currentTimeMillis();
        log.info("----sql执行语句: {}",boundSql.getSql());
        log.info("----sql输入参数: {}", JSONUtil.toJsonStr(paramsMap));
        log.info("----sql输出结果数: {}", jsonArray.size());
        log.info("----sql执行花费时间: {}", (endTime-startTime)/1000);
        // sql执行之后
        return proceed;
    }
}

  <plugins>
        <plugin interceptor="com.wyl.mybatis.intercept.TimeIntercept"></plugin>
    </plugins>

#

分页插件

FullCityMapper mapper = sqlSession.getMapper(FullCityMapper.class);
for (int i = 1; i <= 3 ; i++) {
    Page<FullCity> page = new Page<>(i, 10);
    List<FullCity> fullCities = mapper.selectFullCityPage(page);
    log.info("当前页：{}",page.getCurrentPage());
    log.info("页数：{}",page.getPageSize());
    log.info("总数：{}",page.getTotal());
    log.info("列表：{}", JSONUtil.toJsonStr(fullCities));
}

作用：mysql数据库物理分页,简化分页流程

package com.wyl.mybatis.page;

import java.util.List;

/**
 * @Description
 * @Author WuYiLong
 * @Date 2024/3/13 13:48
 */
public class Page<T>  {

    private Integer currentPage = 1;
    private Integer pageSize = 10;
    private Integer total;
    private List<T> records;

    public Page(Integer currentPage,Integer pageSize) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
    }
    public Page(){};

    public Integer getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(Integer currentPage) {
        this.currentPage = currentPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotal() {
        return total;
    }

    public void setTotal(Integer total) {
        this.total = total;
    }

    public List<T> getRecords() {
        return records;
    }

    public void setRecords(List<T> records) {
        this.records = records;
    }
}

 /**
     * 分页
     * @param page
     * @return
     */
    @Select("select * from d_full_city")
    List<FullCity> selectFullCityPage(@Param("page") Page page);

package com.wyl.mybatis.intercept;

import cn.hutool.db.sql.SqlBuilder;
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.wyl.mybatis.config.SqlSessionFactoryConfig;
import com.wyl.mybatis.page.Page;
import com.wyl.mybatis.util.SqlParamUtil;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * @Description
 * @Author WuYiLong
 * @Date 2024/3/13 12:06
 */
@Intercepts({
        @Signature(
                type = Executor.class,
                method = "query",
                args = {
                        MappedStatement.class,
                        Object.class,
                        RowBounds.class,
                        ResultHandler.class
                }),
        @Signature(
                type = Executor.class,
                method = "query",
                args = {MappedStatement.class,
                        Object.class,
                        RowBounds.class,
                        ResultHandler.class,
                        CacheKey.class,
                        BoundSql.class})})
public class PageIntercept implements Interceptor {

    private final static Logger log = LoggerFactory.getLogger(PageIntercept.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        Executor executor = (Executor) invocation.getTarget();
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object params = args[1];
        RowBounds rowBounds = (RowBounds) args[2];
        BoundSql boundSql = ms.getBoundSql(params);
        String sql = boundSql.getSql();
        ResultHandler resultHandler = (ResultHandler) args[3];

        Page page = null;
        Map<String, Object> paramMap = SqlParamUtil.filter(params);
        for (Map.Entry<String, Object> mapEntry : paramMap.entrySet()) {
            Object v = mapEntry.getValue();
            if (v instanceof Page) {
                page = (Page) v;

            }
        }

        if(page != null) {
            String countSql = countSql(sql);
            int count = 0;
            SqlSessionFactory sqlSessionFactory = SqlSessionFactoryConfig.buildSqlSessionFactory();
            try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
                Connection connection = sqlSession.getConnection();
                PreparedStatement preparedStatement = connection.prepareStatement(countSql);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    count = resultSet.getInt("count");
                }
            }

            page.setTotal(count);
            rowBounds = new RowBounds(page.getCurrentPage()-1,page.getPageSize());
        }

        CacheKey cacheKey;
        if (args.length == 4) {
            cacheKey = executor.createCacheKey(ms, params, rowBounds, boundSql);
        } else {
            cacheKey = (CacheKey) args[4];
            boundSql = (BoundSql) args[5];
        }

        List<Object> query = executor.query(ms, params, rowBounds, resultHandler, cacheKey, boundSql);
        return query;
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof Executor) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    private String countSql(String sql) {
        SqlBuilder sqlBuilder = new SqlBuilder();
        sqlBuilder.select("count(*) count");
        List<SQLStatement> sqlStatements = SQLUtils.parseStatements(sql, DbType.mysql);
        SQLSelectStatement sqlStatement = (SQLSelectStatement) sqlStatements.get(0);
        MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) sqlStatement.getSelect().getQueryBlock();
        sqlBuilder.from(queryBlock.getFrom().toString());
        if (queryBlock.getWhere() != null) {
            sqlBuilder.where(queryBlock.getWhere().toString());
        }
        return sqlBuilder.build();
    }
}

 <plugins>
        <plugin interceptor="com.wyl.mybatis.intercept.PageIntercept"></plugin>
    </plugins>

这里需要注意的是CacheKey缓存key,因为sql的变化直接影响了查询的输出，从上面可以看出分页参数，是不需要用户输入的，通过内置分页插件即可完成分页，所以说sql实质上是没有发生变化的，从而导致重新生成的缓存key都是一样的，如源码所示：

 @Override
  public CacheKey createCacheKey(MappedStatement ms, Object parameterObject, RowBounds rowBounds, BoundSql boundSql) {
    if (closed) {
      throw new ExecutorException("Executor was closed.");
    }
    CacheKey cacheKey = new CacheKey();
    cacheKey.update(ms.getId()); 
    cacheKey.update(rowBounds.getOffset());
    cacheKey.update(rowBounds.getLimit());
    cacheKey.update(boundSql.getSql());
    List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
    TypeHandlerRegistry typeHandlerRegistry = ms.getConfiguration().getTypeHandlerRegistry();
    // mimic DefaultParameterHandler logic
    for (ParameterMapping parameterMapping : parameterMappings) {
      if (parameterMapping.getMode() != ParameterMode.OUT) {
        Object value;
        String propertyName = parameterMapping.getProperty();
        if (boundSql.hasAdditionalParameter(propertyName)) {
          value = boundSql.getAdditionalParameter(propertyName);
        } else if (parameterObject == null) {
          value = null;
        } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
          value = parameterObject;
        } else {
          MetaObject metaObject = configuration.newMetaObject(parameterObject);
          value = metaObject.getValue(propertyName);
        }
        cacheKey.update(value);
      }
    }
    if (configuration.getEnvironment() != null) {
      // issue #176
      cacheKey.update(configuration.getEnvironment().getId());
    }
    return cacheKey;
  }

看到了cacheKey的update方法，所以我们只需要每次请求改变其中之一即可，很明显，我们改变下RowBounds对象的参数就好，这个对象也是控制行数的，从它的名字就可以直接看出来。

项目地址

demo地址