Mybatis 直接传入sql执行, 并且传入参数List＜Object＞ params

使用mybatis 直接执行sql , 并传入参数作为占位符号

找了很久没有找到, mybatis直接执行sql, 其中sql 可以携带? 作为占位符的这种形式 , 然后可以带参数的形式 .

那就自己实现一下.

原理: 就是自己把参数拼接进去… 笨办法

Service层代码

import com.example.sqldemo.mapper.SqlMapper;
import com.example.sqldemo.pojo.Pair;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

@Service
public class SqlService {


    @Resource
    private SqlMapper sqlMapper;

    public void getData(String sql){

        Pair pair1 = new Pair("int", 1);
        Pair pair2 = new Pair("string", "北京");
        ArrayList<Pair> params = new ArrayList<>();
        params.add(pair1);
        params.add(pair2);
        String finalSql = getSql(sql,params); 
        System.out.println(finalSql);
        List<LinkedHashMap<String, Object>> result = sqlMapper.select(finalSql);
        System.out.println(result);
    }

    private String getSql(String sql, ArrayList<Pair> params) {
        for (int i = 0; i < params.size(); i++) {
            String data = getValueData(params.get(i));
            sql = sql.replaceFirst("\\?", data);
        }
        return sql;
    }

    private String getValueData(Pair param) {
        String value = null;
        switch (param.getKey()){
            case "string":
                value = "'" + param.getValue()+"'";
                break;
            case "int":
                value = ""+param.getValue();
                break;
            case "double":
                value = ""+param.getValue();
                break;
            case "date":
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String date = format.format(param.getValue());
                value = "'"+date+"'";
                break;
            case "boolean":
                value = Boolean.TRUE.equals((boolean)param.getValue())? "1":"0";
                break;

        }
        return value;
    }


    public void createTable(String sql){
        sqlMapper.createTable(sql);
    }
}

Mapper层代码

@Mapper
public interface SqlMapper {


    List<LinkedHashMap<String, Object>> select(String sql);

    List<LinkedHashMap<String, Object>> selectByParams(String sql,List<Object> params);

    void createTable(String sql);
}

Mapper 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.example.sqldemo.mapper.SqlMapper">


    <select id="select" parameterType="java.lang.String" resultType="java.util.HashMap">
      ${sql}
    </select>

    <select id="selectByParams" parameterType="map" resultType="java.util.HashMap">
        ${sql}
    </select>


    <insert id="createTable">
        ${sql}
    </insert>
    
</mapper>

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Pair {

    private String key;

    private Object value;
}

返回的结果中, 字段值为null的直接被mybatis忽略了, 如果不想忽略, 配置文件中, 添加

mybatis.configuration.callSettersOnNulls=true