Mybatis [13] - the use of mybatis dynamic SQL tag

Letter 16 2021-01-21 00:35:30
mybatis use mybatis dynamic sql


mybatis There's a powerful feature , Other frames are splicing sql Be very careful when you do , Like where you need spaces , Also notice to remove the comma from the last column name of the list ,mybtis The dynamics of the sql It can help us escape from this painful struggle , That's it dynamic SQL. It can also deal with a situation , When you are not sure whether your parameter is empty or not , We don't need to judge in business logic , Directly in sql In dealing with , The code is extremely concise . Main news sql The label is as follows :

  • <if></if>
  • <where></where>(trim,set)
  • <choose></choose>(when, otherwise)
  • <foreach></foreach>

matters needing attention :
stay mapper If there is a greater than sign in (>), Less than no. (), Greater than or equal to (), Less than or equal to sign () etc. , It's better to convert to entity symbols , This is because mapper yes XML file ,xml The file itself contains more <> Angle brackets like this , So there may be errors in parsing .

Original symbol < <= > >= & ' "
Replace symbols &lt; &lt;= &gt; &gt;= &amp; &apos; &quot;

<if>

We often need to be based on where The following conditions filter out the data you need , When multiple conditions are combined , We usually use <if></if>, If if The conditions in it hold , Then the tag statement will be used , But we can know where The first label of the sentence is no and Of , And the latter conditions all need and, So one way to do this is to be the first to use where 1 = 1, This condition is tenable , Add... To all subsequent statements and, If you add judgment , So we just need to add <if> A label will do .

 <!-- dynamic sql if label -->
<!-- & have access to and Instead of , Be careful != It needs to be written together -->
<select id="selectStudentByDynamicSQL" resultType="Student">
<!-- Most commonly used ( Dynamic parameters ) select id,name,age,score from student where name like '%' #{name} '%' -->
<!-- Here's string splicing , Can only write value, Understanding can , Easy to sql Inject , Inefficient execution , Not recommended -->
select id,name,age,score
from student
where 1=1
<if test="name != null and name != ''">
and name like '%' #{name} '%'
</if>
<if test="age > 0">
and age > #{age}
</if>
</select>

When there are two query conditions ,sql The sentence is :select * from student where 1=1 and name like '%' ? '%' and age > ?

When there is a query condition :sql The statement becomes :select * from student where 1=1 and name like '%' ? '%'

When there are no query conditions ,sql The sentence is :
select * from student where 1=1

<if></if> Labels need to be manually placed in where Add later 1=1 sentence , This is because if <if> The latter conditions are the same false When ,where If not in the back 1=1 sentence ,sql There's only one empty one left where,sql You're going to report a mistake . So in where You need to add Yongzhen sentence after it 1=1, But there is a problem , When the amount of data is large , Can have a serious impact sql The query efficiency of .

<where></where>,<trim></trim>,<set></set>

Use <where></where> label , When there are query statements , Make up automatically where Clause , When there are no query conditions , Not with where Clause , This also solves the problems we mentioned above , The rest is <if> Labeled and Clause , first ,<if> The clip may not contain and, It can also include , The system will automatically remove and, But the others <if> In the clip and, It must be written , Otherwise it will go wrong . In the following way , If name by null, the second if In the tag if It will be removed too , No mistake. .

 <select id="selectStudentByDynamicSQLWhere" resultType="Student">
<!-- Most commonly used ( Dynamic parameters ) select id,name,age,score from student where name like '%' #{name} '%' -->
<!-- Here's string splicing , Can only write value, Understanding can , Easy to sql Inject , Inefficient execution , Not recommended -->
select id,name,age,score
from student
<where>
<if test="name != null and name != ''">
and name like '%' #{name} '%'
</if>
<if test="age > 0">
and age > #{age}
</if>
</where>
</select>

If where It's not standard inside , Then we can go through From definition where The function of elements , Tags have the following properties :

  • prefix: Prefix the included content with , Not 100 percent , It will automatically add
  • suffix: Add a suffix after the included content , Not 100 percent , It will automatically add
  • prefixOverrides: You can ignore the first part of the content ( You can't increase by yourself ), You don't have to ignore , Automatically ignore as needed
  • suffixOverrides: You can also ignore some content at the end of the content ( You can't increase by yourself ), ditto

The following is FALSE , When incoming name Not empty , and age Greater than 0 When

 <select id="selectStudentByDynamicSQLWhere" resultType="Student">
select id,name,age,score
from student
<trim prefix="where" prefixOverrides="and">
<if test="name != null and name != ''">
name like '%' #{name} '%'
</if>
<if test="age > 0">
age > #{age}
</if>
</trim>
</select>

It won't increase by itself and In the second age front :



The following is true , We are in two <if> The front of the label has been added and, the second and Will automatically remove :

 <select id="selectStudentByDynamicSQLWhere" resultType="Student">
select id,name,age,score
from student
<trim prefix="where" prefixOverrides="and">
<if test="name != null and name != ''">
and name like '%' #{name} '%'
</if>
<if test="age > 0">
and age > #{age}
</if>
</trim>
</select>

Here's the suffix pattern , prefix="set" Prefixes the entire statement set, suffixoverride="," Denotes the suffix at the end of each statement "," Can be ignored , If it's necessary .suffix=" where id = #{id} Indicates to add after the entire statement where id = #{id},:

update user
<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">
<if test="name != null and name.length()>0"> name=#{name} , </if>
<if test="age != null "> age=#{age} , </if>
</trim>

Of course , We also have a dynamic solution to the above statement , That's it label :

 <update id="updateStudent">
update student
<set>
<!-- first if The comma of the label must have , The comma of the last tag may not be -->
<if test="name != null"> name=#{name},</if>
<if test="age != null">age=#{age},</if>
<if test="score != null"> score=#{score},</if>
</set>
where id=#{id}
</update>

<choose>, <when>, <otherwise>

occasionally , We just want to match the first condition , Or when the first condition does not match, the second condition will be matched , Unlike <where></where> In the label <if></if> We will also judge whether all the sub statements can match , Instead, if you meet a match, you will jump out <choose></choose>

 <!-- selectStudentByDynamicSQLChoose Be similar to switch, If you are satisfied, you will not judge the following -->
<!-- If the name is not empty , So search by name , If the name is empty , Just by age , If there are no query conditions , There are no query conditions -->
<select id="selectStudentByDynamicSQLChoose" resultType="Student">
<!-- Most commonly used ( Dynamic parameters ) select id,name,age,score from student where name like '%' #{name} '%' -->
select id,name,age,score
from student
<where>
<choose>
<when test="name != null and name != ''">
and name like '%' #{name} '%'
</when>
<when test="age > 0">
and age > #{age}
</when>
<otherwise>
and 1 != 1
</otherwise>
</choose>
</where>
</select>

<choose> Labels are like switch sentence , every last <when> It's all like case, I'll follow you later break sentence , As long as one is satisfied, the following sub statements will not be judged , When all the front <when></when> When it's not implemented , Will execute <otherwise></otherwise> Content of the label , This content is also like switch In the sentence default.

foreach

dynamic SQL A more common operation is to traverse a collection , Usually in the build IN When a conditional statement . Something to watch out for :

  • collection Represents the type of collection to traverse ,array Represents the array to traverse
  • open,close,separator It's about traversing the content SQL Splicing
  • foreach Elements are very powerful , It allows you to specify a set , Declare the collection items that can be used inside the element (item) And index (index) Variable . It also allows you to specify the start and end strings and place separators between iteration results .
  • You can put any iteratable object ( Such as List、Set etc. )、Map Object or array object passed to foreach As a set parameter . When using iteratable objects or arrays ,index Is the number of current iterations ,item The value of is the element obtained in this iteration . When using Map object ( perhaps Map.Entry A collection of objects ) when ,index It's a key ,item Is the value .

1. For example, we need to find the students id by 1,2,3 Student information , We don't want to check one at a time separately , Instead, I want the array id Pass it in at once , Find out a collection of students .

sql Interfaces can be written like this , Pass in an array of objects :

public List<Student>selectStudentByDynamicSQLForeachArray(Object[]studentIds);

sql The statement is as follows , Traverse array When you have an array , Specifies that the left symbol is the left bracket , On the right is the right bracket , Elements are separated by commas :

 <!-- select * from student where id in (1,3) -->
<select id="selectStudentByDynamicSQLForeachArray" resultType="Student">
select id,name,age,score
from student
<if test="array !=null and array.length > 0 ">
where id in
<foreach collection="array" open="(" close=")" item="myid" separator=",">
#{myid}
</foreach>
</if>
</select>

2. When traversing a type of int Of list When listing :

public List<Student>selectStudentByDynamicSQLForeachList(List<Integer>studentIds);

sql The statement is as follows ,colleaction Designated as list:

 <select id="selectStudentByDynamicSQLForeachList" resultType="Student">
select id,name,age,score
from student
<if test="list !=null and list.size > 0 ">
where id in
<foreach collection="list" open="(" close=")" item="myid" separator=",">
#{myid}
</foreach>
</if>
</select>

3. When traversing an object of type list:

public List<Student>selectStudentByDynamicSQLForeachListStudent(List<Student>students);

sql The sentence is similar to the above , It's just different when using attributes :

<select id="selectStudentByDynamicSQLForeachListStudent" resultType="Student">
select id,name,age,score
from student
<if test="list !=null and list.size > 0 ">
where id in
<foreach collection="list" open="(" close=")" item="stu" separator=",">
#{stu.id}
</foreach>
</if>
</select>

<sql></sql>

Used for definition sql fragment , Convenient in other SQL Inside the tag , When reusing in other places, you need to use <include></include> Child tags ,<sql> Can define sql Any part of , therefore <include> Tags can be placed in dynamic SQL Anywhere in the world .

 <sql id="selectHead">
select id,name,age,score
from student
</sql>
<!-- Poor readability -->
<select id="selectStudentByDynamicSQLfragment" resultType="Student">
<include refid="selectHead"></include>
<if test="list !=null and list.size > 0 ">
where id in
<foreach collection="list" open="(" close=")" item="stu" separator=",">
#{stu.id}
</foreach>
</if>
</select>

dynamic sql Give Way SQL It's simpler to write , Reduced a lot of duplicate code , dynamic sql They can be spliced with each other , As long as meet sql Statement specification .

【 Author's brief introduction 】
Qin Huai , official account 【 Qin Huai grocery store 】 author , The road to technology is not in the moment , one's nobility lasts forever , Even slowly , Go on and on . The world wants everything to be quick , faster , But I hope I can take every step well , Write every article well , Looking forward to communicating with you .

版权声明
本文为[Letter 16]所创,转载请带上原文链接,感谢
https://javamana.com/2021/01/20210121003506743z.html

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云