In-depth explanation of Mybatis series (9)---powerful dynamic SQL
The previous article "Mybatis series in simple terms (8)---mapper mapping file configuration select, resultMap" briefly introduced the query of mybatis. So far, CRUD has been explained. This article will introduce the powerful dynamic SQL of mybatis.
So, here comes the question: What is dynamic SQL? What is the role of dynamic SQL?
With the traditional method of using JDBC, I believe that when you combine complex SQL statements, you need to splice them together. If you don't pay attention, even missing a space will lead to errors. The dynamic SQL function of Mybatis is designed to solve this problem. It can be combined into very flexible SQL statements through if, choose, when, otherwise, trim, where, set, and foreach tags, thus improving the efficiency of developers. Let’s feel the charm of Mybatis dynamic SQL:
1. if: You can judge, and so can I!
As a programmer, who doesn’t understand if! You can also use if in mybatis:
<select id="findUserById" resultType="user">
select * from user where
<if test="id != null">
id=#{id} </if>
and deleteFlag=0;</select>##2. where, with me, the SQL statement The splicing conditions are all in the clouds! Let’s transform the above example through where:
<select id="findUserById" resultType="user">
select * from user
<where>
<if test="id != null">
id=#{id} </if>
and deleteFlag=0; </where>
</select>Indeed, on the surface, It's just an extra where tag, but in essence, mybatis processes it. When it encounters AND or OR, it knows how to deal with it. In fact, we can customize this processing rule through the trim tag.
3. trim : My territory, I decide! The where tag above can actually be represented by trim as follows:
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
It means: When WHERE is followed by AND or then When ORing, remove AND or OR. In addition to WHERE, there is actually a more classic implementation, which is SET.
4. set: Trust me, you won’t make any mistakes! <update id="updateUser" parameterType="com.dy.entity.User">
update user set
<if test="name != null">
name = #{name}, </if>
<if test="password != null">
password = #{password}, </if>
<if test="age != null">
age = #{age} </if>
<where>
<if test="id != null">
id = #{id} </if>
and deleteFlag = 0; </where></update>
Yes, at this time, we can use the set tag provided by mybatis. The following is modified through the set tag:
<update id="updateUser" parameterType="com.dy.entity.User">
update user <set>
<if test="name != null">name = #{name},</if>
<if test="password != null">password = #{password},</if>
<if test="age != null">age = #{age},</if>
</set>
<where>
<if test="id != null">
id = #{id} </if>
and deleteFlag = 0; </where></update>这个用trim 可表示为：
<trim prefix="SET" suffixOverrides=","> ...</trim>
WHERE是使用的 prefixOverrides（前缀）， SET是使用的 suffixOverrides （后缀）， 看明白了吧！
5. foreach: 你有for, 我有foreach, 不要以为就你才屌！
java中有for, 可通过for循环， 同样， mybatis中有foreach, 可通过它实现循环，循环的对象当然主要是java容器和数组。
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in <foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item} </foreach></select>将一个 List 实例或者数组作为参数对象传给 MyBatis，当这么做的时候，MyBatis 会自动将它包装在一个 Map 中并以名称为键。List 实例将会以“list”作为键，而数组实例的键将是“array”。同样， 当循环的对象为map的时候，index其实就是map的key。
6. choose: 我选择了你，你选择了我！
Java中有switch, mybatis有choose。
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <choose>
<when test="title != null">
AND title like #{title} </when>
<when test="author != null and author.name != null">
AND author_name like #{author.name} </when>
<otherwise>
AND featured = 1 </otherwise>
</choose></select>以上例子中： 当title和author都不为null的时候， 那么选择二选一（前者优先）， 如果都为null, 那么就选择 otherwise中的， 如果tilte和author只有一个不为null, 那么就选择不为null的那个。
纵观mybatis的动态SQL， 强大而简单， 相信大家简单看一下就能使用了。
好啦，本次就写到这！下篇文章将结合mybatis的源码分析一次sql语句执行的整个过程。
以上就是深入浅出Mybatis系列（九）---强大的动态SQL的内容，更多相关内容请关注PHP中文网（www.php.cn）！
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
iBatis vs. MyBatis: Which one is better for you?
Feb 19, 2024 pm 04:38 PM
iBatis vs. MyBatis: Which should you choose? Introduction: With the rapid development of the Java language, many persistence frameworks have emerged. iBatis and MyBatis are two popular persistence frameworks, both of which provide a simple and efficient data access solution. This article will introduce the features and advantages of iBatis and MyBatis, and give some specific code examples to help you choose the appropriate framework. Introduction to iBatis: iBatis is an open source persistence framework
Detailed explanation of the Set tag function in MyBatis dynamic SQL tags
Feb 26, 2024 pm 07:48 PM
Interpretation of MyBatis dynamic SQL tags: Detailed explanation of Set tag usage MyBatis is an excellent persistence layer framework. It provides a wealth of dynamic SQL tags and can flexibly construct database operation statements. Among them, the Set tag is used to generate the SET clause in the UPDATE statement, which is very commonly used in update operations. This article will explain in detail the usage of the Set tag in MyBatis and demonstrate its functionality through specific code examples. What is Set tag Set tag is used in MyBati
Comparative analysis of the functions and performance of JPA and MyBatis
Feb 19, 2024 pm 05:43 PM
JPA and MyBatis: Function and Performance Comparative Analysis Introduction: In Java development, the persistence framework plays a very important role. Common persistence frameworks include JPA (JavaPersistenceAPI) and MyBatis. This article will conduct a comparative analysis of the functions and performance of the two frameworks and provide specific code examples. 1. Function comparison: JPA: JPA is part of JavaEE and provides an object-oriented data persistence solution. It is passed annotation or X
Various ways to implement batch deletion operations in MyBatis
Feb 19, 2024 pm 07:31 PM
Several ways to implement batch deletion statements in MyBatis require specific code examples. In recent years, due to the increasing amount of data, batch operations have become an important part of database operations. In actual development, we often need to delete records in the database in batches. This article will focus on several ways to implement batch delete statements in MyBatis and provide corresponding code examples. Use the foreach tag to implement batch deletion. MyBatis provides the foreach tag, which can easily traverse a set.
Detailed explanation of how to use MyBatis batch delete statements
Feb 20, 2024 am 08:31 AM
Detailed explanation of how to use MyBatis batch delete statements requires specific code examples. Introduction: MyBatis is an excellent persistence layer framework that provides rich SQL operation functions. In actual project development, we often encounter situations where data needs to be deleted in batches. This article will introduce in detail how to use MyBatis batch delete statements, and attach specific code examples. Usage scenario: When deleting a large amount of data in the database, it is inefficient to execute the delete statements one by one. At this point, you can use the batch deletion function of MyBatis
Detailed explanation of MyBatis cache mechanism: understand the cache storage principle in one article
Feb 23, 2024 pm 04:09 PM
Detailed explanation of MyBatis caching mechanism: One article to understand the principle of cache storage Introduction When using MyBatis for database access, caching is a very important mechanism, which can effectively reduce access to the database and improve system performance. This article will introduce the caching mechanism of MyBatis in detail, including cache classification, storage principles and specific code examples. 1. Cache classification MyBatis cache is mainly divided into two types: first-level cache and second-level cache. The first-level cache is a SqlSession-level cache. When
Detailed explanation of MyBatis first-level cache: How to improve data access efficiency?
Feb 23, 2024 pm 08:13 PM
Detailed explanation of MyBatis first-level cache: How to improve data access efficiency? During the development process, efficient data access has always been one of the focuses of programmers. For persistence layer frameworks like MyBatis, caching is one of the key methods to improve data access efficiency. MyBatis provides two caching mechanisms: first-level cache and second-level cache. The first-level cache is enabled by default. This article will introduce the mechanism of MyBatis first-level cache in detail and provide specific code examples to help readers better understand
In-depth understanding of the batch Insert implementation principle in MyBatis
Feb 21, 2024 pm 04:42 PM
MyBatis is a popular Java persistence layer framework that is widely used in various Java projects. Among them, batch insertion is a common operation that can effectively improve the performance of database operations. This article will deeply explore the implementation principle of batch Insert in MyBatis, and analyze it in detail with specific code examples. Batch Insert in MyBatis In MyBatis, batch Insert operations are usually implemented using dynamic SQL. By constructing a line S containing multiple inserted values
