MyBatis framework dynamic Sql
Dynamic SQL: judge the conditions through various tags provided by MyBatis to realize dynamic splicing of SQL statements. The expression used for conditional judgment here is OGNL expression.
The commonly used dynamic SQL tags are,,, and so on.
The dynamic SQL statement of MyBatis is very similar to the statement in JSTL.
Dynamic SQL is mainly used to solve the situation of uncertain query conditions: query according to the query conditions submitted by users during program operation. The submitted query conditions are different, and the executed SQL statements are different. If each possible situation is listed one by one and all conditions are arranged and combined, a large number of SQL statements will appear. Dynamic SQL can be used to solve this problem.
Dynamic sql: the same dao method can represent different sql statements according to different conditions, mainly the changes in the where part. It uses the tags provided by mybatis to realize the ability of dynamic sql.
When using dynamic sql, the formal parameters of dao methods use java objects.
In mapper's dynamic SQL, if there are symbols such as greater than (>), less than (<), greater than or equal to (> =), less than or equal to (< =), it is best to convert them into entity symbols. Otherwise, XML parsing errors may occur. Especially for the less than sign (<), it must not appear in XML. Otherwise, an error will occur when parsing the mapper file.
Entity symbol table:
| Symbol | explain | Entity symbol |
|---|---|---|
| < | less than | < ; |
| > | greater than | > ; |
| <= | Less than or equal to | < ;= |
| >= | Greater than or equal to | > ;= |
Dynamic SQL if
For the execution of this tag, when the value of test is true, the SQL fragments contained in it will be spliced into the SQL statement where it is located.
Syntax:<if test="boolean Judgment result"> sql Part of a statement </if>
In the mapper file:
<select id="selectStudentIf" resultType="com.lln.vo.Student"> select id,name,email,age from student <if test="condition"> sql sentence </if> <if test="condition"> sql sentence </if> </select>
Method interface:
//if
List<Student> selectIf(Student student);
mapper file:
<!--
if
test: Use the property value of the object as a condition
-->
<select id="selectIf" resultType="com.lln.vo.Student">
select * from student
where
<if test="name != null and name != '' ">
name = #{name}
</if>
<if test="age > 0 ">
or age = #{age}
</if>
</select>
Test class 1:
@Test
public void testSelectIf(){
//1. Get SqlSession
SqlSession session = MyBatisUtil.getSqlSession();
//2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
Student student = new Student();
student.setName("Li Si");
student.setAge(26);
List<Student> students = dao.selectIf(student);
students.forEach(stu -> System.out.println(stu));
//3. Close the SqlSession object
session.close();
}
Operation result 1:
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 428910174.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1990a65e]
==> Preparing: select * from student where name = ? or age = ?
==> Parameters: Li Si(String), 26(Integer)
<== Columns: id, name, email, age
<== Row: 2, Li Si, 456@163.com, 26
<== Row: 7, Li Yu, liyu@163.com, 26
<== Total: 2
Student Entity:{id=2, name='Li Si', email='456@163.com', age=26}
Student Entity:{id=7, name='Li Yu', email='liyu@163.com', age=26}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1990a65e]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1990a65e]
Returned connection 428910174 to pool.
Test class 2:
@Test
public void testSelectIf(){
//1. Get SqlSession
SqlSession session = MyBatisUtil.getSqlSession();
//2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
Student student = new Student();
student.setName(null);
student.setAge(26);
List<Student> students = dao.selectIf(student);
students.forEach(stu -> System.out.println(stu));
//3. Close the SqlSession object
session.close();
}
Operation result 2:
An error is reported. There is one more or, which does not comply with the syntax specification.
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. PooledDataSource forcefully closed/removed all connections. Opening JDBC Connection Created connection 1075738627. Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803] ==> Preparing: select * from student where or age = ? ==> Parameters: 26(Integer)
Dynamic SQL where
There is a troublesome place in the label: you need to manually add a 1 = 1 clause after where. Because if all conditions after where are false and there is no 1 = 1 clause in where, there will be only an empty where in SQL and SQL error. Therefore, after where, you need to add the always true Clause 1 = 1 to prevent this from happening. However, when the amount of data is large, it will seriously affect the query efficiency.
Using tags, you can automatically add a where clause when there are query conditions; When there are no query criteria, the where clause is not added. Note that the SQL fragment in the first tag may not contain and. However, it's also good to write and. The system will remove the extra and. However, the and of other SQL fragments must be written. Otherwise, the SQL statement will splice errors.
Syntax: <where> <if test="condition"> sql Part of a statement </if> <if test="condition"> sql Part of a statement </if> </where>
Interface method:
List<Student> selectWhere(Student student);
mapper file:
<select id="selectWhere" resultType="com.lln.vo.Student">
select * from student
<where>
<if test="name != null and name != '' ">
or name = #{name}
</if>
<if test="age > 0 ">
or age = #{age}
</if>
</where>
</select>
Test class 1:
@Test
public void testSelectWhere(){
//1. Get SqlSession
SqlSession session = MyBatisUtil.getSqlSession();
//2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
Student student = new Student();
student.setName("Li Si");
student.setAge(26);
List<Student> students = dao.selectWhere(student);
students.forEach(stu -> System.out.println(stu));
//3. Close the SqlSession object
session.close();
}
Operation result 1:
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 775931202.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2e3fc542]
==> Preparing: select * from student WHERE name = ? or age = ?
==> Parameters: Li Si(String), 26(Integer)
<== Columns: id, name, email, age
<== Row: 2, Li Si, 456@163.com, 26
<== Row: 7, Li Yu, liyu@163.com, 26
<== Total: 2
Student Entity:{id=2, name='Li Si', email='456@163.com', age=26}
Student Entity:{id=7, name='Li Yu', email='liyu@163.com', age=26}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2e3fc542]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2e3fc542]
Returned connection 775931202 to pool.
Test class 2:
@Test
public void testSelectWhere(){
//1. Get SqlSession
SqlSession session = MyBatisUtil.getSqlSession();
//2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
Student student = new Student();
student.setName(null);
student.setAge(26);
List<Student> students = dao.selectWhere(student);
students.forEach(stu -> System.out.println(stu));
//3. Close the SqlSession object
session.close();
}
Operation result 2:
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1075738627.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
==> Preparing: select * from student WHERE age = ?
==> Parameters: 26(Integer)
<== Columns: id, name, email, age
<== Row: 2, Li Si, 456@163.com, 26
<== Row: 7, Li Yu, liyu@163.com, 26
<== Total: 2
Student Entity:{id=2, name='Li Si', email='456@163.com', age=26}
Student Entity:{id=7, name='Li Yu', email='liyu@163.com', age=26}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
Returned connection 1075738627 to pool.
Test class 3:
@Test
public void testSelectWhere(){
//1. Get SqlSession
SqlSession session = MyBatisUtil.getSqlSession();
//2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
Student student = new Student();
student.setName(null);
student.setAge(null);
List<Student> students = dao.selectWhere(student);
students.forEach(stu -> System.out.println(stu));
//3. Close the SqlSession object
session.close();
}
Operation result 3:
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1075738627.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
==> Preparing: select * from student
==> Parameters:
<== Columns: id, name, email, age
<== Row: 1, Zhang San, 123@163.com, 18
<== Row: 2, Li Si, 456@163.com, 26
<== Row: 3, Wang Wu, 789@163.com, 30
<== Row: 4, Liu Bei, liubei@163.com, 55
<== Row: 5, Guan Yu, guanyu@163.com, 50
<== Row: 6, Fei Zhang, zhangfei@163.com, 55
<== Row: 7, Li Yu, liyu@163.com, 26
<== Total: 7
Student Entity:{id=1, name='Zhang San', email='123@163.com', age=18}
Student Entity:{id=2, name='Li Si', email='456@163.com', age=26}
Student Entity:{id=3, name='Wang Wu', email='789@163.com', age=30}
Student Entity:{id=4, name='Liu Bei', email='liubei@163.com', age=55}
Student Entity:{id=5, name='Guan Yu', email='guanyu@163.com', age=50}
Student Entity:{id=6, name='Fei Zhang', email='zhangfei@163.com', age=55}
Student Entity:{id=7, name='Li Yu', email='liyu@163.com', age=26}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
Returned connection 1075738627 to pool.
Dynamic SQL foreach
Using foreach, you can loop arrays and list sets, which are generally used in in statements.
Tags are used to traverse arrays and collections.
For its use, attention should be paid to:
Collection indicates the collection type to be traversed, such as list, array, etc.
open, close and separator are SQL splicing of traversal content.
Syntax:
<foreach collection="Collection type" open="Start character" close="Ending character" item="Members in the collection" separator="Separator between collection members">
#{value of item}
</foreach>
Label properties:
collection: Indicates whether the object of the loop is an array or an array list Set.
If dao The formal parameters of interface methods are arrays, collection="array"；
If dao The formal parameters of the interface method are list Gather, collection="list"；
open: The character at the beginning of the loop.
close: The character at the end of the loop.
item: Collection members, custom variables. Integer item = idlist.get(i);
separator: Separator between collection members.
#{value of item}: get the value of the collection member.
Simple type
Interface method:
//Collection containing basic types
List<Student> selectForeachOne(List<Integer> idList);
mapper file:
<select id="selectForeachOne" resultType="com.lln.vo.Student">
select * from student
<if test="list!=null and list.size>0">
where id in
<foreach collection="list" open="(" close=")" separator="," item="stuId">
#{stuId}
</foreach>
</if>
</select>
Test class:
@Test
public void testSelectForeachOne(){
//1. Get SqlSession
SqlSession session = MyBatisUtil.getSqlSession();
//2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
List<Integer> idList = new ArrayList<>();
idList.add(1);
idList.add(2);
idList.add(3);
List<Student> students = dao.selectForeachOne(idList);
students.forEach(stu -> System.out.println(stu));
//3. Close the SqlSession object
session.close();
}
Operation results:
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1858609436.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c]
==> Preparing: select * from student where id in ( ? , ? , ? )
==> Parameters: 1(Integer), 2(Integer), 3(Integer)
<== Columns: id, name, email, age
<== Row: 1, Zhang San, 123@163.com, 18
<== Row: 2, Li Si, 456@163.com, 26
<== Row: 3, Wang Wu, 789@163.com, 30
<== Total: 3
Student Entity:{id=1, name='Zhang San', email='123@163.com', age=18}
Student Entity:{id=2, name='Li Si', email='456@163.com', age=26}
Student Entity:{id=3, name='Wang Wu', email='789@163.com', age=30}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c]
Returned connection 1858609436 to pool.
object type
Interface method:
List<Student> selectForeachTwo(List<Student> studentList);
mapper file:
<select id="selectForeachTwo" resultType="com.lln.vo.Student">
select * from student
<if test="list!=null and list.size >0">
where id in
<foreach collection="list" open="(" close=")" separator="," item="stu">
#{stu.id}
</foreach>
</if>
</select>
Test class:
@Test
public void testSelectForeachTwo(){
//1. Get SqlSession
SqlSession session = MyBatisUtil.getSqlSession();
//2. Get the agent of dao
StudentDao dao = session.getMapper(StudentDao.class);
List<Student> list = new ArrayList<>();
Student s1 = new Student();
s1.setId(1);
Student s2 = new Student();
s2.setId(2);
Student s3 = new Student();
s3.setId(3);
list.add(s1);
list.add(s2);
list.add(s3);
List<Student> students = dao.selectForeachTwo(list);
students.forEach(stu -> System.out.println(stu));
//3. Close the SqlSession object
session.close();
}
Operation results:
Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1543974463.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c072e3f]
==> Preparing: select * from student where id in ( ? , ? , ? )
==> Parameters: 1(Integer), 2(Integer), 3(Integer)
<== Columns: id, name, email, age
<== Row: 1, Zhang San, 123@163.com, 18
<== Row: 2, Li Si, 456@163.com, 26
<== Row: 3, Wang Wu, 789@163.com, 30
<== Total: 3
Student Entity:{id=1, name='Zhang San', email='123@163.com', age=18}
Student Entity:{id=2, name='Li Si', email='456@163.com', age=26}
Student Entity:{id=3, name='Wang Wu', email='789@163.com', age=30}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c072e3f]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c072e3f]
Returned connection 1543974463 to pool.
Dynamic SQL code snippet
<sql/>Labels are used to define SQL Fragment for other SQL Label reuse. Other labels use this SQL Fragment, need to use<include/>Child labels. Should<sql/>Labels can be defined SQL Any part of a statement, so<include/>Sub tags can be placed dynamically SQL Anywhere.
Steps:
1. Define sql code snippets in mapper file;
2. In other places, use the include tag to refer to a code fragment.