MyBatis execute Dynamic SQL statement _java

We all know how to use MyBatis to execute arbitrary SQL, and the method is simple, for example, in a xxmapper.xml:

<select id= "ExecuteSQL" resulttype= "map" >
${_parameter}
</select>

You can call the following:

Sqlsession.selectlist ("ExecuteSQL", "select * from Sysuser where enabled = 1");

Alternatively, you can define the following methods in the Xxmapper.java interface:

list<map> ExecuteSQL (String sql);

Then use the interface to invoke the method:

Xxmapper.executesql ("select * from Sysuser where enabled = 1");

The above content may be, the following is a little more complicated.

If you like the above SQL enabled = 1 I would like to use parameter method to pass the value, that is, write enabled = #{enabled}, if you have not encountered such a demand, may not understand why to write, for example, to implement a dynamic query, you can configure SQL in the foreground, Provide some query conditions to implement a query function (for security, these configurations must be developed or implemented, it is impossible for users to directly manipulate the database).

For this feature, it is fairly easy to implement using MyBatis. Configuring SQL is sure to execute, in this way it is certainly possible to execute SQL, how to provide parameters? The parameter is the #{enabled} part of the Enabled = #{enabled}. If you have more than one condition, a configured SQL is as follows:

SELECT * from Sysuser 
where is enabled = #{enabled} 
and userName like concat ('% ', #{username}, '% ')

In this case, how to use the MyBatis to achieve it?

First, the XML is modified as follows:

<select id= "ExecuteSQL" resulttype= "map" >
${sql}
</select>

The method in the interface is modified to:

List<map> ExecuteSQL (map map);

Then call the method:

Map map = new HashMap ();
Here the SQL corresponds to the ${sql} map.put in XML
("SQL", "SELECT * from Sysuser"
+ "where enabled = #{enabled}"
+ "and US Ername like concat ('% ', #{username}, '% ') ");
#{enabled}
map.put ("Enabled", 1);
#{username}
map.put ("UserName", "admin");
Interface method calls
list<map> List = Xxmapper.executesql (MAP);
Sqlsession method calls
sqlsession.selectlist ("ExecuteSQL", map);

With this SQL, enabled and UserName can be provided to the user as a condition. These two conditions are clearly required. If it's optional, how do you write it?

Maybe someone thought about whether you can use the dynamic SQL in MyBatis, using <if> tags, and so on?

Before we answer this question, let's look at the code in the 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 processes dynamic SQL, all dynamic SQL labels are processed as Sqlnode (here Rootsqlnode) objects, and ${} is also processed as a Textsqlnode object, the first two lines of the above method, which is mybatis handling dynamic SQL Place.

So if the contents of our ${sql} contain nested ${} and <if>,<where> tags, when they mybatis parsing the XML as Sqlnode object, the XML <select> element contains only the ${ SQL}, only ${sql} will be parsed, ${} and <if>,<where> tags that may be included in this parameter string at run time, but this occurs after MyBatis parsing, so when the content appears as part of the string, They will not be handled specifically, they are only part of the SQL, but the output is not processed because the database is not known to be an error, so it is not possible to write dynamic SQL by MyBatis this way.

Tips

In the code above:

Sqlsourceparser.parse (Context.getsql (), ParameterType, Context.getbindings ());

This piece of code is handling dynamic parameters (#{}) after dynamic SQL processing, so you can use this type of argument in SQL.

Since you can't use mybatis dynamic SQL, how do you implement dynamic SQL?

Here is a simple idea to use the Template Markup language in SQL to implement dynamic SQL (for example, Freemarker), to use a template to process SQL before handing it over to MyBatis to generate the resulting SQL (you need to avoid processing #{} parameters), and give this SQL to MyBatis execution.

As an example of a freemarker template, it is still based on the above SQL:

SELECT * from Sysuser 
where 1 = 1
< #if enabled?? >
enabled = #{enabled} 
</#if >
< #if userName?? && userName!= ' > and
userName Like concat ('% ', #{username}, '% ')
</#if >

Note that the < #if > is the freemarker element. 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 also handles ${username} and replaces the parameters here with actual values.

In the code that was called earlier, this is modified as follows:

#{enabled}
map.put ("Enabled", 1);
#{username}
map.put ("UserName", "admin");
Here the SQL corresponds to the ${sql}
String sql in XML = "One of the above two complex SQL";
Use Freemarker to process SQL
sql = processsqlbyfreemarker (sql, map);
Place the processed SQL in the map
map.put ("SQL", "SELECT * from Sysuser"
+ "where enabled = #{enabled}"
+ "and UserName l Ike concat ('% ', #{username}, '% ') ");
Execution method
list<map> List = Xxmapper.executesql (MAP);

Note: The Processsqlbyfreemarker method is to process the SQL string based on the data in the map, and the implementation can be searched by itself.

Here, a dynamic SQL function that is not very complex is implemented.

Do not know whether there is more greedy person, you will not think, the above return value is list<map> type, can return a I specified entity class?

For example, in the map:

Map.put ("Class", "Tk.mybatis.model.SysUser");

Can you make the return value the Sysuser type in this way? Since this article has taken too long, here's a solution, not deep.

You can use the interceptor implementation, get the mappedstatement, copy one, and then modify the Resultmaps Resultmap type attribute for you specified class type can be achieved, easier said, the actual operation can be pagehelper Paging plug-ins around 1/10 of the workload.

Because this is a daughter-in-law request to write, so if the daughter-in-law has the last of this demand, I will help daughter-in-law realize this plug-in, and then shared out.

Note: If it is a dynamic update,insert,delete statement, you can change the above <select> to update (do not need to use <delete> and <insert>), return the value with int, than The case for select is much easier.

The above is a small set to introduce the MyBatis implementation of dynamic SQL statement, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!