Mybatis 动态执行SQL语句

悠悠 2022-09-02 00:52 274阅读 0赞

有很多的接口都只是执行个SQL查询之后就直接返回给前端，那么我们能不能把这些SQL保存在数据库中，调用一个固定的接口就能根据传参查询出想要的数据呢？或者当为了加减个字段就得修改代码重启服务的痛苦能不能减少点呢？下面就是方案。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0cmVhbV9uYW1l_size_16_color_FFFFFF_t_70][]

调用直接传入SQL语句（可以选择存数据库）和参数，SQL语句写法和在XML内的写法保持一致即可，包括Mybatis标签等等，参数选择使用通用的Map，可以从接口接收任何参数，方法的返回值是List<Map>。

import lombok.extern.slf4j.Slf4j;
    import org.apache.ibatis.builder.xml.XMLMapperBuilder;
    import org.apache.ibatis.executor.ErrorContext;
    import org.apache.ibatis.session.Configuration;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.defaults.DefaultSqlSessionFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;
     
    import java.io.ByteArrayInputStream;
    import java.io.InputStream;
    import java.lang.reflect.Method;
    import java.util.Map;
     
    //from fhadmin.cn
    @Component
    @Slf4j
    public class SqlExecutor {
     
        @Autowired
        private SqlSessionFactory sqlSessionFactory;
     
        public void parserString(String originSql,Map<String,Object> param) {
            StringBuilder builder = new StringBuilder();
            builder.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n");
            builder.append("<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\r\n");
            builder.append("<mapper namespace=\"cn.video.asm.TestMapper\">\r\n");
            builder.append("	 <select id=\"queryById\"  resultType=\"java.util.Map\">\r\n");
            builder.append(originSql);
            builder.append("	 </select>\r\n");
            builder.append("</mapper>");
            InputStream inputStream = new ByteArrayInputStream(builder.toString().getBytes());
            Configuration baseConfig = sqlSessionFactory.getConfiguration();
            // 不能使用原有的config对象加载，否则下次就不会重复加载导致传入的SQL语句不能切换
            // 也可以在这里指定数据源，从对应的数据源做查询动作
            Configuration configuration = new Configuration(baseConfig.getEnvironment());
            String resource = "resource";
            ErrorContext.instance().resource(resource);
            XMLMapperBuilder mapperParser = new XMLMapperBuilder(inputStream, configuration,resource ,configuration.getSqlFragments());
            mapperParser.parse();
            SqlSession sqlSessionXML = new DefaultSqlSessionFactory(configuration).openSession();
            Object result = null;
            try {
                // 使用自定义的ClassLoader
                MyClassLoader loader = new MyClassLoader();
                // 生成二进制字节码
                byte[] bytes = MyClassLoader.dump();
                // 加载我们生成的 Mapper类
                Class<?> clazz = loader.defineClass("cn.video.asm.TestMapper", bytes);
                // 将生成的类对象加载到configuration中
                configuration.addMapper(clazz);
                Method query = clazz.getMethod("queryById", Map.class);
                // 这里就是通过类对象从configuration中获取对应的Mapper
                Object testMapper = sqlSessionXML.getMapper(clazz);
                result = query.invoke(testMapper, param);
            } catch (Exception e) {
                log.error("",e);
            }
            System.out.println("dyn : " + result);
        }
    }

package cn.video.common;
     
    import jdk.internal.org.objectweb.asm.ClassWriter;
    import jdk.internal.org.objectweb.asm.MethodVisitor;
     
    import static jdk.internal.org.objectweb.asm.Opcodes.*;
     
    //from fhadmin.cn
    public class MyClassLoader extends ClassLoader {
     
        public static byte[] dump() {
            ClassWriter cw = new ClassWriter(0);
            cw.visit(52, ACC_PUBLIC + ACC_ABSTRACT + ACC_INTERFACE, "cn/video/asm/TestMapper", null, "java/lang/Object", null);
            cw.visitSource("TestMapper.java", null);
            {
                MethodVisitor mv = cw.visitMethod(ACC_PUBLIC + ACC_ABSTRACT, "queryById", "(Ljava/util/Map;)Ljava/util/List;", "(Ljava/util/Map;)Ljava/util/List<Ljava/lang/Object;>;", null);
                mv.visitEnd();
            }
            cw.visitEnd();
            return cw.toByteArray();
        }
     
        public Class<?> defineClass(String name, byte[] b) {
            // ClassLoader是个抽象类，而ClassLoader.defineClass 方法是protected的
            // 所以我们需要定义一个子类将这个方法暴露出来
            Class<?> clazz = super.findLoadedClass(name);
            if (clazz != null) {
                return clazz;
            }
            return super.defineClass(name, b, 0, b.length);
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0cmVhbV9uYW1l_size_16_color_FFFFFF_t_70]: /images/20220829/ce10fe584b944f65b14a0675207f8878.png