background

I don't know if there is such a puzzle in your company. Many departments often ask your department to provide interfaces to query some data. The interface basically has no business logic. An SQL is enough, but it takes time and effort to develop an interface for this SQL. Many people also want to solve it. For example, they often write an SQL with many fields, Then, different SQL (if > in mybatis) are spliced by different input parameters. This method is simple and crude, and only fixed tables can be queried. If the data of another table is changed, it still needs to be rewritten, and a large number of useless fields are returned

thinking

How to solve it? Let's talk about my idea and my partner D's idea: Looking back on the demand scenario, we provide an interface without business logic, which only returns the sql query results. That is to say, if there is such an interface that can execute the sql I write every time, then the problem will be solved. So our goal is to write the sql to a place (DB), and then the interface obtains the sql and executes the interface to return the execution results

Realization

D and I started to think it's not difficult. We saved the sql to the DB, read it, and execute it with mybatis. But in this step, we got stuck. If it's a simple sql, such as

select * from user where name = ? and age = ?

It can be implemented, for example, by using the @ SelectProvider annotation provided by mybatis, splicing parameters in the method selectUserSql, and then executing

@SelectProvider(value = UserService.class, method = "selectUserSql")
List<User> selectDyn(SQL sql, Map<String, Object> parameterMap);

But if it's a little more complicated, for example, name is not required, then it's a big deal to think about (I still want to implement a set of parsing tools myself at first) Discuss with D. since mybatis already has a complete set of sql parsing tools, we can use them directly, which not only saves the workload of our own development, but also is reliable (look down on me, eh ~)

Overview of mybatis loading and parsing process

Just do what you say. Starting from the source code of mybatis, we found some ways. Generally, the mybatis code is as follows

// Configuration files are loaded into memory as streams
InputStream inputStreamXML = Resources.getResourceAsStream("mybatis-config.xml");
// Construction plant
SqlSessionFactory sqlSessionFactoryXML = new SqlSessionFactoryBuilder().build(inputStreamXML);
// sqlSession
SqlSession sqlSessionXML = sqlSessionFactoryXML.openSession();
// Get Mapper
UserMapper userMapper = sqlSessionXML.getMapper(UserMapper.class);
// implement
System.out.println("xml : " + userMapper.queryById(1));

Looking at the code, we start from loading the configuration file. First, we test the configuration information of the code as follows

<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/xxx"/>
                <property name="username" value="xxx"/>
                <property name="password" value="xxxxxx"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

The process is like this. There are too many configuration parameters. Create a factory class through the builder of the factory. First construct a tool to parse the configuration file, then parse it a little bit, put the parsing result into the configuration object, and then use the object to construct the factory object

Since our goal is to load sql dynamically, we will focus on Mapper parsing There are two types of parsing: package tag and Mapper tag. There are three kinds of resources, URL and class under Mapper tag. Next, the file information specified by Mapper tag, namely UserMapper.xml, will be loaded. The content is as follows:

<?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.togo.repository.UserMapper">
    <resultMap type="com.togo.entity.User" id="UserMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="xx" column="xx" jdbcType="VARCHAR"/>
        <result property="appid" column="appid" jdbcType="VARCHAR"/>
        <result property="nickname" column="nickname" jdbcType="VARCHAR"/>
        <result property="passtest" column="passtest" jdbcType="INTEGER"/>
    </resultMap>

    <select id="queryById" resultMap="UserMap">
        select
          id, xx, appid, nickname, passtest
        from wx.user
        <where>
            <if test="id != null">
               and id = #{id}
            </if>
        </where>
    </select>
</mapper>

It is consistent with the formula of parsing configuration files, and it is also a label by label parsing. Because we intend to directly use mybatis's parsing tool at first, we don't care much about how it is implemented. We just need to know how to load Mapper, and the key code appears here

org.apache.ibatis.builder.xml.XMLConfigBuilder#mapperElement
if (resource != null && url == null && mapperClass == null) {
    ErrorContext.instance().resource(resource);
    InputStream inputStream = Resources.getResourceAsStream(resource);
    XMLMapperBuilder mapperParser = new XMLMapperBuilder(inputStream, configuration, resource,                 configuration.getSqlFragments());
    mapperParser.parse();
}

Here we can take it out and load our mapper,

// mapper is a string in xml
InputStream inputStream = new ByteArrayInputStream(mapper.getBytes());
Configuration configuration = sqlSessionFactoryXML.getConfiguration();

ErrorContext.instance().resource("resource");
XMLMapperBuilder mapperParser = new XMLMapperBuilder(inputStream, configuration, "resource",                configuration.getSqlFragments());
mapperParser.parse();

Found in debug that it has been loaded into the configuration object~

implement

After loading, it will be executed. Let's see the normal execution code

SqlSession sqlSessionXML = sqlSessionFactoryXML.openSession();
UserMapper userMapper = sqlSessionXML.getMapper(UserMapper.class);
System.out.println("xml : " + userMapper.queryById(1));

Well, how to get this UserMapper? We just loaded a string, of course, there is no Mapper class that can execute methods. Does that mean that as long as we have such a class, we can generate one dynamically~ We use asm here, which is easy to use with the idea plug-in

dependency>
    <groupId>org.ow2.asm</groupId>
    <artifactId>asm</artifactId>
    <version>7.0</version>
</dependency>

Classes to build

public interface TestMapper {

    Map<String, Object> queryById(Integer id);
}

Generate code

public class MyClassLoader extends ClassLoader {

    public static byte[] dump() throws Exception {

        ClassWriter cw = new ClassWriter(0);
        FieldVisitor fv;
        MethodVisitor mv;
        AnnotationVisitor av0;

        cw.visit(52, ACC_PUBLIC + ACC_ABSTRACT + ACC_INTERFACE, "com/togo/asm/TestMapper", null, "java/lang/Object", null);

        cw.visitSource("TestMapper.java", null);

        {
            mv = cw.visitMethod(ACC_PUBLIC + ACC_ABSTRACT, "queryById", "(Ljava/lang/Integer;)Ljava/util/Map;", "(Ljava/lang/Integer;)Ljava/util/Map<Ljava/lang/String;Ljava/lang/Object;>;", null);
            mv.visitEnd();
        }
        cw.visitEnd();

        return cw.toByteArray();
    }

    public Class<?> defineClass(String name, byte[] b) {
        // ClassLoader is an abstract class, while the ClassLoader.defineClass method is protected
        // So we need to define a subclass to expose this method
        return super.defineClass(name, b, 0, b.length);
    }
}

Execute!!!

// Generate binary bytecode
byte[] bytes = MyClassLoader.dump();

// Use custom ClassLoader
MyClassLoader cl = new MyClassLoader();
// Load the HelloWorld class we generated
Class<?> clazz = cl.defineClass("com.togo.asm.TestMapper", bytes);
// Load the generated class object into configuration
configuration.addMapper(clazz);

Method query = clazz.getMethod("queryById", Integer.class);
// This is to get the corresponding Mapper from the configuration through the class object
Object testMapper = sqlSessionXML.getMapper(clazz);
Object result = query.invoke(testMapper, 1);

System.out.println("dyn : " + result);

summary

This article realizes the function of dynamically loading and executing external sql through mybatis. Here is just a way to realize it. There are many details to be studied before it is applied to the project~ demo address