Everyone basically knows how to use MyBatis to execute arbitrary SQL. The usage method is very simple, for example, in an XXMapper.xml:
<select id="executeSql" resultType="map">${_parameter}</select>You can call it as follows:
sqlSession.selectList("executeSql", "select * from sysuser where enabled = 1");Or you can define the following method in the XXMapper.java interface:
List<Map> executeSql(String sql);
Then use the interface to call the method:
xxMapper.executeSql("select * from sysuser where enabled = 1");All the above contents may be, and the following is a little more complicated based on this.
If you want to pass the value using parameter method in SQL above, that is, write it as enabled = #{enabled}, if you have not encountered such needs, you may not understand why you should write it like this. For example, to implement a dynamic query, you can configure SQL in the foreground and provide some query conditions to realize the function of a query (for safety, these configurations must be developed or implemented, and it is impossible for users to operate the database directly).
For this feature, it is quite easy to implement with MyBatis. Configuring SQL must be executed. SQL can be executed in the method mentioned above. How to provide parameters? The parameter is the #{enabled} part in enabled = #{enabled}. If there are more conditions, a configured SQL is as follows:
select * from sysuser where enabled = #{enabled} and userName like concat('%',#{userName},'%')In this case, how can I use MyBatis to implement it?
First, the XML is modified as follows:
<select id="executeSql" resultType="map">${sql}</select>The methods in the interface are modified to:
List<Map> executeSql(Map map);
Then call the method:
Map map = new HashMap();//The sql here corresponds to ${sql}map.put("sql", "select * from sysuser "+ " where enabled = #{enabled} "+ " and userName like concat('%',#{userName},'%')");//#{enabled}map.put("enabled", 1);//#{userName}map.put("userName", "admin");//Insert call List<Map> list = xxMapper.executeSql(map);//The sqlSession method calls sqlSession.selectList("executeSql", map);With this SQL, you can provide enabled and userName as conditions to the user. These two conditions are obviously required. If it is optional, how should I write it?
Maybe someone has thought about whether it is possible to use dynamic SQL in MyBatis, use <if> tags, etc.?
Before answering this question, let's look at the code in DynamicSqlSource that handles dynamic SQL:
@Override public BoundSql getBoundSql(Object parameterObject) {DynamicContext context = new DynamicContext(configuration, parameterObject);rootSqlNode.apply(context);SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);Class < ?>parameterType = parameterObject == null ? Object.class: parameterObject.getClass();SqlSource sqlSource = sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings());BoundSql boundSql = sqlSource.getBoundSql(parameterObject); for (Map.Entry < String, Object > entry: context.getBindings().entrySet()) {boundSql.setAdditionalParameter(entry.getKey(), entry.getValue());}return boundSql;}When MyBatis handles dynamic SQL, all dynamic SQL tags will be processed as SqlNode (rootSqlNode here) objects, and those containing ${} will also be processed as TextSqlNode objects. In the first two lines of the above method, is where MyBatis handles dynamic SQL.
Therefore, if our content in ${sql} contains nested tags such as ${} and <if>, <where>, and other tags, when MyBatis parses XML to SqlNode objects, the XML <select> element contains only ${sql}, and only ${sql} will be parsed. At runtime, this parameter string may contain tags such as ${} and <if>, <where>, etc., but this happens after MyBatis parsing. Therefore, when these contents appear as part of the string, they will not be specially processed. They are only part of SQL, and they are just output as it is (because the database does not recognize it, it will report an error) and cannot be processed. Therefore, it cannot write dynamic SQL through the method that comes with MyBatis.
hint
In the above code:
sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings());
This piece of code handles dynamic parameters (#{}) after dynamic SQL processing, so this type of parameters can be used in SQL.
Since you cannot use the MyBatis dynamic SQL method, how to implement dynamic SQL?
Here is a simple idea. Use template markup language in SQL to implement dynamic SQL (such as freemarker). Before SQL is handed over to MyBatis for execution, use templates to process SQL to generate the final executed SQL (need to avoid processing #{} parameters), and hand this SQL to MyBatis for execution.
Take an example of a Freemarker template, and still the above SQL is based on:
select * from sysuser where 1 = 1<#if enabled??>enabled = #{enabled} </#if><#if userName?? && userName != ''>and userName like concat('%',#{userName},'%')</#if>Note that the <#if> here is the element of Freemarker. Without considering SQL injection, the above SQL can also be written as:
select * from sysuser where 1 = 1<#if enabled??>enabled = #{enabled} </#if><#if userName?? && userName != ''>and userName like '%${userName}%'</#if>The difference is '%${userName}%', because Freemarker will also process ${userName} and will also replace the parameters here with the actual value.
In the code called earlier, the modification is as follows:
//#{enabled}map.put("enabled", 1);//#{userName}map.put("userName", "admin");//The sql here corresponds to ${sql}String in XML sql = "One of the two complex SQLs above";//Use Freemarker to process sqlsql = processSqlByFreemarker(sql, map);//Put the processed sql in the map map.put("sql", "select * from sysuser "+ " where enabled = #{enabled} "+ " and userName like concat('%',#{userName},'%')");//Execute method List<Map> list = xxMapper.executeSql(map);Note: The processSqlByFreemarker method is to process SQL strings based on the data in the map, and the implementation method can be searched by yourself.
At this point, a dynamic SQL function that is not very complicated is implemented.
I wonder if there are more greedy people. Do you think that the returned values above are all List<Map> type. Can you return an entity class I specified?
For example in map:
map.put("class", "tk.mybatis.model.SysUser");Can I make the return value become SysUser type in this way? Since this article has taken too long, I will provide a solution here, not in-depth.
You can use an interceptor to implement it. After obtaining the MappedStatement, copy a copy, and then modifying the type attribute of the resultMap in resultMaps to specify the class type for you. It is easy to say, but in actual operation, you can have about 1/10 of the PageHelper paging plugin.
Because this article was written at the request of my wife, if my wife has this last need, I will help my wife implement this plug-in and then share it out.
Note: If it is a dynamic update,insert, delete statement, you can change the above <select> to update (no need to use <delete> and <insert>), and use int for return value, which is much easier than select.
The above is a detailed explanation of MyBatis execution dynamic SQL statement introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!