Hakurei
文章
11
分类
4
评论
0

个人常用开发工具-mybatis

| 后端 •
字数总计 11917 | 阅读时长 29分钟 | 阅读量 24

Mybatis动态SQL打印工具

在Java软件开发中，使用MyBatis等ORM框架可以大大简化数据库操作。然而，在非Spring环境中，直接调用Mapper接口方法并打印完整的动态SQL可能会有些困难。为了解决这个问题，本人开发了一个小工具，可以在非Spring环境下实现这一功能。

该小工具的核心思想是通过动态代理技术，对Mapper接口进行代理，并在代理方法中捕获SQL语句的生成过程。接下来，让我们一步步了解如何实现这个小工具。

创建一个工具类，命名为"MybatisMapperPrinter"。该类需要实现Java的InvocationHandler接口，用于处理动态代理方法的调用。XmlDriver这个类封装了从Xml中获取动态SQL和对应的解析逻辑。

public class MybatisMapperPrinter<T> implements InvocationHandler {

    public static <T> T proxy(Class<T> targetClass) {
        return new MybatisMapperPrinter<>((T) null).getProxy(targetClass);
    }

    private final T target;

    public MybatisMapperPrinter(T target) {
        this.target = target;
    }

    public T getProxy(Class<T> targetClass) {
        return (T) Proxy.newProxyInstance(targetClass.getClassLoader(), new Class[] {targetClass}, this);
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        
        new XmlDriver( method, args).parse();

        return null;
    }
}

使用Mybatis框架提供的ParamNameResolver可以方便的获取到方法入参，再根据入参使用LanguageDriver来获取完整动态SQL语句。此时获取到的SQL语句是诸如SELECT * FROM table_name WHERE id = ? 这种形式的预分配形式，所以还需要把所有的占位符替换为真正的查询条件，这里通过一个自定义的PreparedStatement来实现。

public class XmlDriver {

    public static final String MAPPER_PATH = ResourceUtils.CLASSPATH_URL_PREFIX + "mapper";

    private final String xmlName;
    private final String methodName;
    private final Object parameterObject;

    public XmlDriver(String xmlName, Method method, Object[] args) {
        this.xmlName = MAPPER_PATH + "/" + method.getDeclaringClass().getSimpleName() + ".xml";
        this.methodName = method.getName();
        ParamNameResolver resolver = new ParamNameResolver(new Configuration(), method);
        this.parameterObject = resolver.getNamedParams(args);
    }

    public void parse() throws IOException, SQLException {
        Configuration configuration = new Configuration();
        LanguageDriver langDriver = new XMLLanguageDriver();
        URL url = ResourceUtils.getURL(xmlName);
        InputStream inputStream = url.openStream();
        MapperBuilderAssistant builderAssistant = new MapperBuilderAssistant(configuration, url.getPath());
        builderAssistant.setCurrentNamespace("");
        XPathParser parser = new XPathParser(inputStream, true, configuration.getVariables(), new XMLMapperEntityResolver());
        XNode context = parser.evalNode("/mapper");
        List<XNode> list = context.evalNodes("/mapper/sql");
        for (XNode node : list) {
            String id = node.getStringAttribute("id");
            id = builderAssistant.applyCurrentNamespace(id, false);
            if (!configuration.getSqlFragments().containsKey(id)) {
                configuration.getSqlFragments().put(id, node);
            }
        }
        XMLIncludeTransformer includeParser = new XMLIncludeTransformer(configuration, builderAssistant);
        includeParser.applyIncludes(context.getNode());
        List<XNode> nodes = context.evalNodes("select|insert|update|delete");
        for (XNode node : nodes) {
            if (!node.getStringAttribute("id").equals(methodName)) {
                continue;
            }
            Class<Object> parameterTypeClass = configuration.getTypeAliasRegistry().resolveAlias(node.getStringAttribute("parameterType"));
            SqlSource sqlSource = langDriver.createSqlSource(configuration, node, parameterTypeClass);
            BoundSql boundSql = sqlSource.getBoundSql(parameterObject);
            PreparedStatement ps = new SqlBuildPreparedStatement(boundSql.getSql());
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() == ParameterMode.OUT) {
                    continue;
                }
                String property = parameterMapping.getProperty();
                Object value;
                if (boundSql.hasAdditionalParameter(property)) {
                    value = boundSql.getAdditionalParameter(property);
                } else if (parameterObject == null) {
                    value = null;
                } else if (configuration.getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass())) {
                    value = parameterObject;
                } else {
                    MetaObject metaObject = configuration.newMetaObject(parameterObject);
                    value = metaObject.getValue(property);
                }
                JdbcType jdbcType = parameterMapping.getJdbcType();
                if (value == null && jdbcType == null) {
                    jdbcType = configuration.getJdbcTypeForNull();
                }
                TypeHandler typeHandler = parameterMapping.getTypeHandler();
                typeHandler.setParameter(ps, i + 1, value, jdbcType);
            }
            ps.addBatch();
            ps.execute();
        }
    }
}

自定义的PreparedStatement中，会进行SQL的拼接，execute方法会在控制台打印拼接完成的SQL。

public class SqlBuildPreparedStatement implements PreparedStatement {

        private final String sql;
        
        private int index;

        private final StringBuilder sqlBuilder;

        public SqlBuildPreparedStatement(String sql) {
            this.sql = sql;
            this.sqlBuilder = new StringBuilder();
        }

        public void nextToken() {
            while (index < sql.length()) {
                char c = sql.charAt(index++);
                if (c == '?') {
                    break;
                }
                sqlBuilder.append(c);
            }
        }

        @Override
        public void setNull(int parameterIndex, int sqlType) {
            nextToken();
            sqlBuilder.append("null");
        }

        @Override
        public void setBoolean(int parameterIndex, boolean x) {
            nextToken();
            sqlBuilder.append(x);
        }

        @Override
        public void setInt(int parameterIndex, int x) {
            nextToken();
            sqlBuilder.append(x);
        }

        @Override
        public void setString(int parameterIndex, String x) {
            nextToken();
            sqlBuilder.append("'").append(x).append("'");
        }

        @Override
        public void setObject(int parameterIndex, Object x) {
            nextToken();
            sqlBuilder.append("'").append(x).append("'");
        }

        @Override
        public boolean execute() {
            System.out.println(sqlBuilder);
            return true;
        }

        @Override
        public void addBatch() {
            sqlBuilder.append(sql, index, sql.length());
        }
}

通过以上步骤，成功地开发了一个小工具，使非Spring环境下能够直接调用Mapper接口方法并打印完整的动态SQL。这对于调试和优化数据库操作非常有帮助。接下来我们来测试一下：

假设我们有一张person表：

CREATE TABLE `person` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL,
  `mobile` bigint NOT NULL,
  `is_deleted` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB;

首先准备对应的实体类、Mapper接口和XML文件：

@TableName("person")
public class Person {

    @TableId(value = "id", type = IdType.ASSIGN_ID)
    private Long id;
    private String name;
    private Integer age;
    private String mobile;
    @TableLogic(value = "0", delval = "id")
    private Long isDeleted;

}

Mapper接口

@Mapper
public interface PersonMapper extends BaseMapper<Person> {
    List<Person> selectByCondition(@Param("params") Person person);
}

XML文件

<?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="com.masterliu.zero.mybatis.mapper.PersonMapper">

    <select id="selectByCondition" resultType="com.masterliu.zero.mybatis.entity.Person">
        SELECT id, `name`, age, mobile, is_deleted
        FROM person
        <where>
            <if test="params.id != null">
                AND id = #{params.id}
            </if>
            <if test="params.name != null">
                AND `name` = #{params.name}
            </if>
            <if test="params.age != null">
                AND age = #{params.age}
            </if>
            <if test="params.mobile != null">
                AND mobile = #{params.mobile}
            </if>
            <if test="params.isDeleted != null">
                AND is_deleted = #{params.isDeleted}
            </if>
        </where>
    </select>

</mapper>

然后让我们写一个测试类

public class MybatisMapperPrinterTest {

    @Test
    public void testMapperPrinter() {
        PersonMapper mapper = MybatisMapperPrinter.proxy(PersonMapper.class);
        Person person = new Person();
        person.setId(1L);
        person.setName("张三");
        mapper.selectByCondition(person);
    }

}

执行测试方法后，控制台输出如下内容：

SELECT id, `name`, age, mobile, is_deleted
        FROM person
         WHERE  id = 1
            
            
                AND `name` = '张三'

可以看到，成功实现了根据入参打印完整动态SQL的功能！

Mybatis直接执行Mapper接口方法

以上的小工具只能在控制台打印动态SQL，但在开发测试过程中我们经常还需要直接执行Mapper的接口方法。所以我们还需要一个小工具，来实现在非Spring环境下进行数据库操作。

创建一个工具类，命名为"MybatisTookit"，该类用于创建SqlSessionFactory和执行数据库操作。

public class MybatisTookit {

    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8";
    private String username = "root";
    private String password = "123456";

    public MybatisTookit() {}

    public MybatisTookit(String driver, String url, String username, String password) {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
    }

    public <T> void execute(Class<T> clz, Consumer<T> consumer) {
        DataSource dataSource = new PooledDataSource(driver, url, username, password);
        TransactionFactory transactionFactory = new JdbcTransactionFactory();
        Environment environment = new Environment("test", transactionFactory, dataSource);
        Configuration configuration = new MybatisConfiguration(environment);
        configuration.addMapper(clz);
        SqlSessionFactory sqlSessionFactory = new MybatisSqlSessionFactoryBuilder().build(configuration);
        try (SqlSession session = sqlSessionFactory.openSession()) {
            consumer.accept(session.getMapper(clz));
        }
    }

}

有了上面的小工具后，我们就可以直接执行Mapper的接口方法了。

首先我们先往person表插入一条数据：

INSERT INTO `test`.`person` (`id`, `name`, `age`, `mobile`, `is_deleted`) VALUES (1, '张三', 28, 18111112222, 0);

然后写一个测试类：

public class MybatisTookitTest {

    @Test
    public void test() {
        MybatisTookit tookit = new MybatisTookit();
        tookit.execute(PersonMapper.class, mapper -> {
            Person person = mapper.selectById(1L);
            System.out.println(person);
        });
    }

}

执行测试方法后，控制台输出如下内容：

Person{id=1, name='张三', age=28, mobile='18111112222', isDeleted=0}

现在我们就可以成功执行Mapper的接口方法了！

文章作者： Hakurei
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Zero！
后端 Java
喜欢就支持一下吧