Tutorial on dynamic SQL query of database in mybatis framework of Java

In fact, one of the powerful features of mybatis is usually its dynamic SQL capability. If you have experience in using JDBC or other similar frameworks, you will understand that it is very tangled to concatenate SQL strings dynamically. Make sure you can't forget the space or omit the comma at the end of the list. Dynamic SQL in mybatis can completely deal with this pain. For dynamic SQL, the most popular and simple method is to give judgment to various dynamic behaviors during hard coding. In mybatis, a powerful dynamic SQL language is used to improve this situation. This language can be used in SQL statements with arbitrary mapping. Dynamic SQL elements are similar to using JSTL or other similar XML based text processors. Mybatis uses powerful ognl based expressions to eliminate other elements. The common node elements are if, choose (when, otherwise), trim (where, if) and foreach. When I really use it, I feel a bit like the use of XSLT (I will mention ~) later in the article. (1) the use of if

In the paging configuration of viisitmapper, if PageIndex > - 1 and PageSize > - 1, the corresponding paging SQL will be added, otherwise it will not be added (all by default), as follows:

Because our parameters PageIndex and PageSize are int values, we can judge directly in this way. If it is an object instance, we can use null judgment to control some dynamic logic. The specific actual development depends on the business requirements. Here, I think it should be noted that it is not very easy to write and as &, which will not be recognized in the configuration ~.

(2) Use of choose (when, otherwise)

Choose when is mainly used in application scenarios where only one of the conditions is met under multiple conditions. For example, a query condition is built here to pass ID, name and createtime respectively. Suppose that when we query the visitor table, if the visitor ID has a value, we use the ID query. If the visitor name has a value, we use the visitname query, as shown below, or in David mybatis. demo. Add the list < visitor > getlistchoosewhendemo (basicqueryargs args) method to the ivisitoroperation interface class. Add the corresponding select node configuration in visitormapper:

(3) Usage of where if (trim)

The advantage of where keyword is that if there are corresponding filter conditions, it knows to insert the where keyword at the appropriate time. And it also knows when to remove the corresponding and and or connectors. It mainly deals with the following situations

Not because all conditions are not met

Or because the first condition is not satisfied, only the latter condition becomes

Therefore, for this example, we can create the choose when condition. Similarly, we can add the corresponding method public list < visitor > getlistwheredemo (basicqueryargs args) to the ivistoroperation interface class, and add the corresponding configuration in the visitormapper configuration file as follows:

(4) Foreach usage

In the commonly used dynamic SQL, we have a business scenario that requires where id in a large string of IDS. In this case, we can use foreach without having to work hard to splice ID strings. The same step is to add the corresponding method public list < visitor > getlistforeachdemo (list < integer > IDS) to the ivistoroperation interface class, and then configure the corresponding node element information in the corresponding mapper file, as follows:

Finally, you only need to establish the corresponding test method in demorun, and the dynamic SQL in mybatis will be completed. The demorun method used for the test below