Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use dynamic SQL tags in MyBatis. It is very detailed and has a certain reference value. Friends who are interested must read it!

1.MyBatis dynamic SQL

One of the powerful features of MyBatis is its dynamic SQL, that is, concatenation of SQL strings. If you have experience with JDBC or other similar frameworks, you can see how painful it is to concatenate SQL statements according to different conditions. When stitching, make sure you don't forget the necessary spaces and omit the comma at the end of the list of column names. You can get rid of this pain completely by using the feature of dynamic SQL.

Usually using dynamic SQL cannot be an independent part, and MyBatis certainly uses a powerful dynamic SQL language to improve this situation, which can be used in any SQL mapping statement.

Dynamic SQL elements are similar to using JSTL or other similar XML-based text processors. In previous versions of MyBatis, there were a lot of elements to understand. MyBatis 3 has improved them so much that it now takes less than half of the original elements. MyBatis uses powerful OGNL-based expressions to eliminate other elements.

two。 Dynamic SQL tags: if,choose (when, otherwise), trim (where, set), foreach

2.1 if tag: directly add the code SELECT * FROM BLOG WHERE 1: 1 AND id=# {id} and title=# {title}

Note: if tags are generally used for non-empty verification, such as the above example, if id is empty, the code in the if tag will not be executed, otherwise, it will be executed.

2.2 choose (when,otherwise) tag: directly add the code SELECT * FROM BLOG WHERE 1room1 AND title like # {title} AND id= 1

Note: the choose (when,otherwise) tag is equivalent to switch (case,default). As in the above example, if title is empty, the code in the when tag will not be executed, and the code in the otherwise tag will be executed by default.

2.3 trim (where,set) tag: code select * from user and username=# {username} and password=# {password} directly

Note: assuming that the username,password passed in in the above example is not empty, the code can run successfully! But friends may be wondering, what is the sql statement actually executed? In fact, sql is: select * from user

Where username=? And password=? Did friends find that the where tag replaces the where keyword in sql, but the and in if is missing? In fact, the where tag can automatically remove the "AND" or "OR" keywords from the sql that starts with "AND" or "OR".

If the where element does not follow the normal pattern, we can still customize the sql by customizing the trim element to achieve the effect of the where tag. The code is as follows:

Select * from user and username=# {username} and password=# {password}

Set tag, the code is as follows:

Update user username=# {username} id=# {id}

Note: the function of the set tag is similar to that of the where tag. The set tag replaces the set keyword in sql. The set tag can automatically remove the redundant "," in the sql.

Similarly, trim tags can also implement the functions of set tags.

Update user username=# {username} id=# {id} 2.4foreach tags: foreach tags are deleted in batches, directly with the code delete from user where id in # {id}

Note: the foreach tag can iterate over any object (such as list, collection, etc.) and any dictionary or array object passed to foreach as collection parameters, when using iteratable objects or arrays, index is the number of current iterations, and the value of item is the element obtained in this iteration. When using dictionaries (or collections of Map.Entry objects), index is the key and item is the value. The collection tag can be filled in ('list','array','map').

The main attribute of the foreach element is item,index,collection,open,separator,close.

Item represents the alias of each element in the collection when iterating

Index specifies a name that indicates the location of each iteration during the iteration

Open indicates what the statement begins with

Separator indicates what symbol is used as a separator between each iteration

Close indicates what to end with.

3.bind

The bind element can create a variable from the OGNL expression and bind it to the context. For example:

SELECT * FROM BLOG WHERE title LIKE # {pattern} 4.Multi-db vendor support

A databaseIdProvider configured with the "_ databaseId" variable is available for dynamic code so that specific statements can be built according to different database vendors. For example, the following example:

Select seq_users.nextval from dual select nextval for seq_users from sysibm.sysdummy1 "insert into users values (# {id}, # {name})

Pluggable scripting language in dynamic SQL

MyBatis has supported pluggable scripting languages since 3. 2, so you can write dynamic SQL queries based on a language after inserting a language driver (language driver).

You can insert a language by implementing the following interface:

Public interface LanguageDriver {ParameterHandler createParameterHandler (MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql); SqlSource createSqlSource (Configuration configuration, XNode script, Class parameterType); SqlSource createSqlSource (Configuration configuration, String script, Class parameterType);}

Once you have a custom language driver, you can set it as the default language in the mybatis-config.xml file:

In addition to setting the default language, you can also specify a specific language for a particular statement, which can be done through the following lang attribute:

SELECT * FROM BLOG

Or add @ Lang to the mapping you are using to do this:

Public interface Mapper {@ Lang (MyLanguageDriver.class) @ Select ("SELECT * FROM BLOG") List selectBlog (); that's all about the article "how to use dynamic SQL tags in MyBatis". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.