mybatis执行传入的任意sql语句

发布于 2019-09-06  403 次阅读


首先,新建一个SqlMapper.xml,注意修改namespace

其实因为需要,我只用到了select,用LinkedHashMap是为了保证查询的字段值有序(存入与取出顺序一致)。

<?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="loneking.admin.mapper.SqlMapper">
    <select id="select" parameterType="java.lang.String" resultType="java.util.LinkedHashMap">
    ${value}
  </select>

    <insert id="insert" parameterType="java.lang.String">
    ${value}
  </insert>

    <update id="update" parameterType="java.lang.String">
    ${value}
  </update>

    <delete id="delete" parameterType="java.lang.String">
    ${value}
  </delete>
</mapper>

然后新建接口SqlMapper

public interface SysMapper{
    List<LinkedHashMap<String, Object>> select(String sql);
    int insert(String sql);
    int update(String sql);
    int delete(String sql);
}

再新建个接口SqlService

public interface SqlService {

    String getSingle(String sql);

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

    LinkedHashMap<String, Object> getOne(String sql);

    boolean execute(String sql);

    boolean executeBatch(List<String> sql);
}

最后是实现这个SqlService接口

@Service("SqlService")
public class SqlServiceImpl implements SqlService {
    @Autowired
    SqlMapper sqlMapper;

    @Override
    public String getSingle(String sql) {
        var selectResult = sqlMapper.select(sql);
        if (selectResult.isEmpty())
            return "";
        else {
            return selectResult.get(0).values().iterator().next().toString();
        }
    }

    @Override
    public LinkedHashMap<String, Object> getOne(String sql) {
        return sqlMapper.select(sql).get(0);
    }

    @Override
    public List<LinkedHashMap<String, Object>> query(String sql) {
        return sqlMapper.select(sql);
    }

    @Override
    public boolean execute(String sql) {
        try {
            sqlMapper.select(sql);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public boolean executeBatch(List<String> sql) {
        return this.execute(StringUtils.join(sql, ';'));
    }
}

LoneKing