基于Mybatis实现动态SQL功能并执行

基于Mybatis实现动态SQL功能并执行

虽然现在基于mybatis+spring的写法基本满足需求了，但如果现在有个需求，需要动态生成SQL且进行SQL执行，不能写死，怎么办？下面介绍的方法非最优解。

Pom依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.11</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.11.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

简单封装

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.ibatis.builder.MapperBuilderAssistant;
import org.apache.ibatis.builder.xml.XMLMapperEntityResolver;
import org.apache.ibatis.builder.xml.XMLStatementBuilder;
import org.apache.ibatis.datasource.pooled.PooledDataSource;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.logging.stdout.StdOutImpl;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.parsing.XPathParser;
import org.apache.ibatis.session.*;
import org.apache.ibatis.transaction.Transaction;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * Mapper
 **/
@Slf4j
@Component
public class MapperService {

    /**
     * 执行sql
     *
     * @param sql      sql语句
     * @param params   参数
     * @param driver   驱动包名称，eg: com.mysql.cj.jdbc.Driver
     * @param url      url
     * @param username 用户名
     * @param password 密码
     * @return {@link Object} 执行结果
     */
    public List<Map<String, Object>> execute(String sql, Map<String, Object> params, String driver, String url, String username, String password) {
        SqlSession sqlSession = sqlSession(driver, url, username, password);

        List<Map<String, Object>> result = null;
        Executor executor = null;
        try {
            Configuration configuration = sqlSession.getConfiguration();

            String excutorId; // mybatis执行ID
            if (sql.toLowerCase().startsWith("select")) {
                excutorId = "null.select";
            } else if (sql.toLowerCase().startsWith("insert")) {
                excutorId = "null.insert";
            } else if (sql.toLowerCase().startsWith("update")) {
                excutorId = "null.update";
            } else {
                excutorId = "null.delete";
            }

            Field mappedStatementsField = Configuration.class.getDeclaredField("mappedStatements");
            mappedStatementsField.setAccessible(true);
            Map<String, MappedStatement> mappedStatements = (Map<String, MappedStatement>) mappedStatementsField.get(configuration);
            mappedStatements.remove(excutorId);

            InputStream inputStream = IOUtils.toInputStream(getMapper(sql), StandardCharsets.UTF_8);
            XPathParser parser = new XPathParser(inputStream, true, configuration.getVariables(), new XMLMapperEntityResolver());
            XNode node = null;
            if (sql.toLowerCase().startsWith("select")) {
                node = parser.evalNode("/mapper").evalNodes("select").get(0);
            } else if (sql.toLowerCase().startsWith("insert")) {
                node = parser.evalNode("/mapper").evalNodes("insert").get(0);
            } else if (sql.toLowerCase().startsWith("update")) {
                node = parser.evalNode("/mapper").evalNodes("update").get(0);
            } else {
                node = parser.evalNode("/mapper").evalNodes("delete").get(0);
            }

            XMLStatementBuilder xmlStatementBuilder = new XMLStatementBuilder(configuration, new MapperBuilderAssistant(configuration, inputStream.toString()), node, null);
            xmlStatementBuilder.parseStatementNode();
            executor = newExecutor(configuration);
            MappedStatement mappedStatement = configuration.getMappedStatement(excutorId);

            if (sql.toLowerCase().startsWith("select")) {
                result = executor.query(mappedStatement, params, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER);
            } else {
                executor.update(mappedStatement, params); // 此方法用来执行insert/update/delete，目前示例没有返回该方法的操作
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage());
        } finally {
            if (executor != null) {
                executor.close(false);
            }
        }

        return result;
    }

    /**
     * 动态生成mapper xml文件内容
     *
     * @param sql sql语句
     * @return mapper
     */
    public String getMapper(String sql) {
        StringBuilder mapepr = new StringBuilder();
        mapepr.append("<?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\" >\n")
                .append("<mapper>\n");
        if (sql.toLowerCase().startsWith("select")) {
            mapepr.append("<select id=\"select\" resultType=\"java.util.Map\">\n")
                    .append(sql)
                    .append("</select>\n");
        } else if (sql.toLowerCase().startsWith("insert")) {
            mapepr.append("<insert id=\"insert\">\n")
                    .append(sql)
                    .append("</insert>\n");
        } else if (sql.toLowerCase().startsWith("update")) {
            mapepr.append("<update id=\"update\">\n")
                    .append(sql)
                    .append("</update>\n");
        } else {
            mapepr.append("<delete id=\"delete\">\n")
                    .append(sql)
                    .append("</delete>\n");
        }

        mapepr.append("</mapper>");

        return mapepr.toString();
    }

    /**
     * 获取sql session
     *
     * @param driver   驱动，名称
     * @param url      URL
     * @param username 用户名
     * @param password 密码
     * @return {@link SqlSession}
     */
    public SqlSession sqlSession(String driver, String url, String username, String password) {
        Properties properties = new Properties();
        properties.setProperty("driver", driver);
        properties.setProperty("url", url);
        properties.setProperty("username", username);
        properties.setProperty("password", password);

        /*UnpooledDataSourceFactory unpooledDataSourceFactory = new UnpooledDataSourceFactory();
        unpooledDataSourceFactory.setProperties(properties);
        DataSource dataSource = unpooledDataSourceFactory.getDataSource();*/

        DataSource dataSource = new PooledDataSource(driver, url, username, password);
        Environment environment = new Environment("dev", new JdbcTransactionFactory(), dataSource);
        Configuration configuration = new Configuration(environment);
        // configuration.addMapper(UserMapper.class);
        configuration.setLogImpl(StdOutImpl.class);
        configuration.setMapUnderscoreToCamelCase(true);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
        return sqlSessionFactory.openSession();
    }

    /**
     * 获取执行器
     *
     * @param configuration mybatis配置
     * @return {@link Executor}
     */
    private Executor newExecutor(Configuration configuration) {
        Environment environment = configuration.getEnvironment();
        if (environment == null) {
            throw new ExecutorException("Environment was not configured.");
        }
        DataSource dataSource = environment.getDataSource();
        if (dataSource == null) {
            throw new ExecutorException("DataSource was not configured.");
        }
        TransactionFactory transactionFactory = environment.getTransactionFactory();
        Transaction transaction = transactionFactory.newTransaction(dataSource, null, false);
        return configuration.newExecutor(transaction, ExecutorType.SIMPLE); // 简单模式执行
    }
}

调用示例

import cn.iwiteks.app.demo.dto.MybatisParams;
import cn.iwiteks.demo.service.MapperService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.apache.ibatis.builder.MapperBuilderAssistant;
import org.apache.ibatis.builder.xml.XMLMapperEntityResolver;
import org.apache.ibatis.builder.xml.XMLStatementBuilder;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.parsing.XNode;
import org.apache.ibatis.parsing.XPathParser;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.transaction.Transaction;
import org.apache.ibatis.transaction.TransactionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;

@Slf4j
@RestController
@RequestMapping(value = "/mybatis")
public class MybatisSqlExecutorController {
    @Autowired
    private MapperService mapperService;

    /**
     * 执行sql, 支持mybatis mapper.xml语法
     */
    @PostMapping(value = "/test")
    public Object test(@RequestBody MybatisParams params) {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "admin123456";

        return mapperService.execute(params.getSql(), params.getParam(), driver, url, username, password);
    }

MybatisParams.java源码：

import lombok.Data;

import java.util.Map;

@Data
public class MybatisParams {
    private String sql;
    private Map<String, Object> param;
}

调用结果示例