In some business scenarios, multiple tables are needed to perform statistical operations jointly. In such scenarios, the logic of sql is often complex, and it is difficult to write it out easily by using mybatis. At the same time, various business variables are very large, so it is a little weak to deal with it based on mybatis. Sometimes I have to write some pure sql and then pass it to mybatis. The problem of writing pure sql and passing it to mybatis is that it is difficult to avoid the criticized sql splicing. In order to solve the conflicts in this scenario, the following methods can be used.

<!--SqlMapper.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.chaojilaji.shengqian.SqlMapper">
    <select id="sqltest" parameterType="java.util.Map" resultType="HashMap">
        ${sql}
    </select>
</mapper>

The parameter type passed in is Map

// SqlMapper.java
@Mapper
@Component(value = "sqlMapper")
public interface SqlMapper {
    List<Map<String,Object>> sqltest(Map<String,Object> map);
}

call

@Controller
public class TestController {
   
    @Autowired
    SqlMapper sqlMapper

    @RequestMapping(value = "/testsqlmapper", method = RequestMethod.GET)
    @ResponseBody
    public String testSqlMapper() {
        try {
            Map<String,Object> params = new HashMap<>();
            params.put("sql","select count(*) from user where code like #{code}");
            params.put("code","1%");
            List<Map<String, Object>> ans = sqlMapper.sqltest(params);
            if (Objects.nonNull(ans) && ans.size() > 0){
                System.out.println(ans.get(0));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "1";
    }
}

Explain, that is, put everything including sql into the Map, write variables in sql according to the writing method in xml, and then put these variables into the Map at the same time, so that the binding within parameters can be realized. It needs to be said here that the difference between ා and $, the former can automatically bind variables according to parameter types, including adding quotation marks, etc., and the latter is just filled in directly, if there are quotation marks, you need to add them yourself.

To see the effect:

It has the same effect as binding parameters in mybatis.