Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to master MyBatis dynamic SQL". In daily operation, I believe many people have doubts about how to master MyBatis dynamic SQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to master MyBatis dynamic SQL"! Next, please follow the editor to study!

One of the favorite features of MyBatis is dynamic SQL. In the process of using JDBC, it is troublesome and error-prone to assemble SQL according to conditions. The emergence of MyBatis dynamic SQL solves this problem.

MyBatis uses OGNL for dynamic SQL. Currently, dynamic SQL supports the following tags:

1. Data preparation

For the following demonstration, a Maven project mybatis-dynamic is created, and the corresponding databases and tables are created

DROP TABLE IF EXISTS `student` CREATE TABLE `student` (`student_ id` int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'number', `name` varchar (20) DEFAULT NULL COMMENT 'name', `phone`varchar (20) DEFAULT NULL COMMENT 'phone', `email`varchar (50) DEFAULT NULL COMMENT 'mailbox', `sex`tinyint (4) DEFAULT NULL COMMENT 'gender', `locked`tinyint (4) DEFAULT NULL COMMENT 'status (0: normal, 1: locked)' `time gmt_ created` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'saved in database', `time modified 'datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT', `delete`int (11) DEFAULT NULL, PRIMARY KEY (`student_ id`) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT=' student table'

Corresponding project structure

2. If tag

The if tag is the one we use most often. It is likely to be used when querying, deleting, or updating. Must be used in conjunction with the test attribute.

2.1 use if tags in WHERE conditions

This is a common phenomenon, and there may be a variety of situations when we query by condition.

2.1.1 query conditions

Conditional retrieval according to the entered student information

Use the user name for fuzzy retrieval when only the user name is entered

Use gender for exact matching when only gender is entered

When both user name and gender exist, these two conditions are used for query matching query.

2.1.2 dynamic SQL

application program interface

/ * conditional retrieval based on the entered student information * 1. When only the user name is entered, the user name is used for fuzzy retrieval; * 2. When entering only the mailbox, use gender for exact matching * 3. When both user name and gender exist, the query matching using these two conditions uses * @ param student * @ return * / List selectByStudentSelective (Student student)

Corresponding dynamic SQL

Select from student where 1131 and name like concat ('%', # {name},'%') and sex=# {sex}

In this SQL statement, where 1 is a trick for multi-conditional stitching, and and can be used for all subsequent conditional queries.

At the same time, we add if tags to handle dynamic SQL

And name like concat ('%', # {name},'%') and sex=# {sex}

The value of the test attribute of this if tag is an expression that conforms to OGNL, which can be true or false. If the expression returns a numeric value, 0 is false and non-0 is true

2.1.3 Test

@ Test public void selectByStudent () {SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession (); StudentMapper studentMapper = sqlSession.getMapper (StudentMapper.class); Student search = new Student (); search.setName ("Ming"); System.out.println ("query with only names"); List studentsByName = studentMapper.selectByStudentSelective (search); for (int I = 0; I

< studentsByName.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE)); } search.setName(null); search.setSex((byte) 1); System.out.println("只有性别时的查询"); List studentsBySex = studentMapper.selectByStudentSelective(search); for (int i = 0; i < studentsBySex.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE)); } System.out.println("姓名和性别同时存在的查询"); search.setName("明"); List studentsByNameAndSex = studentMapper.selectByStudentSelective(search); for (int i = 0; i < studentsByNameAndSex.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE)); } sqlSession.commit(); sqlSession.close(); } 只有名字时的查询， 发送的语句和结果 查询的条件只发送了 where 11=1 and name like concat('%', ?, '%') 只有性别时的查询， 发送的语句和结果 查询的条件只发送了 where 11=1 and sex=? 姓名和性别同时存在的查询， 发送的语句和结果 查询条件 where 11=1 and name like concat('%', ?, '%') and sex=? 2.2 在 UPDATE 更新列中使用 if 标签 有时候我们不希望更新所有的字段， 只更新有变化的字段。 2.2.1 更新条件 只更新有变化的字段， 空值不更新。 2.2.2动态 SQL 接口方法 /** * 更新非空属性 */ int updateByPrimaryKeySelective(Student record); 对应的 SQL update student `name` = #{name,jdbcType=VARCHAR}, phone = #{phone,jdbcType=VARCHAR}, email = #{email,jdbcType=VARCHAR}, sex = #{sex,jdbcType=TINYINT}, locked = #{locked,jdbcType=TINYINT}, gmt_created = #{gmtCreated,jdbcType=TIMESTAMP}, gmt_modified = #{gmtModified,jdbcType=TIMESTAMP}, where student_id = #{studentId,jdbcType=INTEGER} 2.2.3 测试 @Test public void updateByStudentSelective() { SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student student = new Student(); student.setStudentId(1); student.setName("明明"); student.setPhone("13838438888"); System.out.println(studentMapper.updateByPrimaryKeySelective(student)); sqlSession.commit(); sqlSession.close(); } 结果如下 2.3 在 INSERT 动态插入中使用 if 标签 我们插入数据库中的一条记录， 不是每一个字段都有值的， 而是动态变化的。在这时候使用 if 标签， 可帮我们解决这个问题。 2.3.1 插入条件 只有非空属性才插入。 2.3.2 动态SQL 接口方法 /** * 非空字段才进行插入 */ int insertSelective(Student record); 对应的SQL insert into student student_id, `name`, phone, email, sex, locked, gmt_created, gmt_modified, #{studentId,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{phone,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{sex,jdbcType=TINYINT}, #{locked,jdbcType=TINYINT}, #{gmtCreated,jdbcType=TIMESTAMP}, #{gmtModified,jdbcType=TIMESTAMP}, 这个 SQL 大家应该很熟悉， 毕竟是自动生成的。 2.3.3 测试 @Test public void insertByStudentSelective() { SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student student = new Student(); student.setName("小飞机"); student.setPhone("13838438899"); student.setEmail("xiaofeiji@qq.com"); student.setLocked((byte) 0); System.out.println(studentMapper.insertSelective(student)); sqlSession.commit(); sqlSession.close(); } 对应的结果 SQL 中， 只有非空的字段才进行了插入。 3、choose 标签 choose when otherwise 标签可以帮我们实现 if else 的逻辑。一个 choose 标签至少有一个 when, 最多一个otherwise。 下面是一个查询的例子。 3.1 查询条件 假设 name 具有唯一性， 查询一个学生 当 studen_id 有值时， 使用 studen_id 进行查询； 当 studen_id 没有值时， 使用 name 进行查询； 否则返回空 3.2 动态SQL 接口方法 /** * - 当 studen_id 有值时， 使用 studen_id 进行查询； * - 当 studen_id 没有值时， 使用 name 进行查询； * - 否则返回空 */ Student selectByIdOrName(Student record); 对应的SQL select from student where 11=1 and student_id=#{studentId} and name=#{name} and 1=2 3.3 测试 @Test public void selectByIdOrName() { SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student student = new Student(); student.setName("小飞机"); student.setStudentId(1); Student studentById = studentMapper.selectByIdOrName(student); System.out.println("有 ID 则根据 ID 获取"); System.out.println(ToStringBuilder.reflectionToString(studentById, ToStringStyle.MULTI_LINE_STYLE)); student.setStudentId(null); Student studentByName = studentMapper.selectByIdOrName(student); System.out.println("没有 ID 则根据 name 获取"); System.out.println(ToStringBuilder.reflectionToString(studentByName, ToStringStyle.MULTI_LINE_STYLE)); student.setName(null); Student studentNull = studentMapper.selectByIdOrName(student); System.out.println("没有 ID 和 name, 返回 null"); Assert.assertNull(studentNull); sqlSession.commit(); sqlSession.close(); } 有 ID 则根据 ID 获取， 结果 没有 ID 则根据 name 获取 没有 ID 和 name, 返回 null

4. Trim (set, where)

These three actually solve similar problems. For example, when we write the previous [use if tag in WHERE condition] SQL, we do not want the condition where 1 to exist.

4.1 where

4.1.1 query conditions

Carry on the conditional retrieval according to the input student information.

Use the user name for fuzzy retrieval when only the user name is entered

Use gender for exact matching when only gender is entered

When both user name and gender exist, these two conditions are used for query matching query.

Do not use where 1 # 1.

4.1.2 dynamic SQL

Obviously, we have to solve these problems.

When the conditions are not met: there should be no where in the SQL at this time, otherwise it will cause an error.

When the if conditions are met: where is required in the SQL, and and under the first established if tag is required. | or, etc., need to be removed.

At this point, we can use the where tag.

Interface method

/ * conditional retrieval based on the entered student information * 1. When only the user name is entered, the user name is used for fuzzy retrieval; * 2. When entering only the mailbox, use gender for exact matching * 3. When both user name and gender exist, the query matching with these two conditions uses * / List selectByStudentSelectiveWhereTag (Student student)

Corresponding SQL

Select from student and name like concat ('%', # {name},'%') and sex=# {sex}

4.1.3 Test

@ Test public void selectByStudentWhereTag () {SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession (); StudentMapper studentMapper = sqlSession.getMapper (StudentMapper.class); Student search = new Student (); search.setName ("Ming"); System.out.println ("query with only names"); List studentsByName = studentMapper.selectByStudentSelectiveWhereTag (search); for (int I = 0; I

< studentsByName.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE)); } search.setSex((byte) 1); System.out.println("姓名和性别同时存在的查询"); List studentsBySex = studentMapper.selectByStudentSelectiveWhereTag(search); for (int i = 0; i < studentsBySex.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE)); } System.out.println("姓名和性别都不存在时查询"); search.setName(null); search.setSex(null); List studentsByNameAndSex = studentMapper.selectByStudentSelectiveWhereTag(search); for (int i = 0; i < studentsByNameAndSex.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE)); } sqlSession.commit(); sqlSession.close(); } 只有名字时的查询, 有 where 姓名和性别同时存在的查询， 有 where 姓名和性别都不存在时查询, 此时， where 不会再出现了。 4.2 set set 标签也类似， 在 [2.2 在 UPDATE 更新列中使用 if 标签] 中， 如果我们的方法 updateByPrimaryKeySelective 没有使用 4.3 trim set 和 where 其实都是 trim 标签的一种类型， 该两种功能都可以使用 trim 标签进行实现。 4.3.1 trim 来表示 where 如以上的 where 标签， 我们也可以写成 表示当 trim 中含有内容时， 添加 where， 且第一个为 and 或 or 时， 会将其去掉。而如果没有内容， 则不添加 where。 4.3.2 trim 来表示 set 相应的， set 标签可以如下表示 表示当 trim 中含有内容时， 添加 set， 且最后的内容为 , 时， 会将其去掉。而没有内容， 不添加 set 4.3.3 trim 的几个属性 prefix: 当 trim 元素包含有内容时， 增加 prefix 所指定的前缀 prefixOverrides: 当 trim 元素包含有内容时， 去除 prefixOverrides 指定的 前缀 suffix: 当 trim 元素包含有内容时， 增加 suffix 所指定的后缀 suffixOverrides：当 trim 元素包含有内容时， 去除 suffixOverrides 指定的后缀 5、foreach 标签 foreach 标签可以对数组， Map 或实现 Iterable 接口。 foreach 中有以下几个属性： collection: 必填， 集合/数组/Map的名称. item: 变量名。即从迭代的对象中取出的每一个值 index: 索引的属性名。当迭代的对象为 Map 时， 该值为 Map 中的 Key. open: 循环开头的字符串 close: 循环结束的字符串 separator: 每次循环的分隔符 其他的比较好理解， collection 中的值应该怎么设定呢？ 跟接口方法中的参数相关。 1. 只有一个数组参数或集合参数 默认情况：集合collection=list， 数组是collection=array 推荐：使用 @Param 来指定参数的名称， 如我们在参数前@Param("ids")， 则就填写 collection=ids 2. 多参数 多参数请使用 @Param 来指定， 否则SQL中会很不方便 3. 参数是Map 指定为 Map 中的对应的 Key 即可。其实上面的 @Param 最后也是转化为 Map 的。 4. 参数是对象 使用属性.属性即可。 5.1 在 where 中使用 foreach 在 where条件中使用， 如按id集合查询， 按id集合删除等。 5.1.1 查询条件 我们希望查询用户 id 集合中的所有用户信息。 5.1.2 动态 SQL 函数接口 /** * 获取 id 集合中的用户信息 * @param ids * @return */ List selectByStudentIdList(List ids); 对应 SQL select from student where student_id in #{id} 5.1.3 测试 @Test public void selectByStudentIdList() { SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); List ids = new LinkedList(); ids.add(1); ids.add(3); List students = studentMapper.selectByStudentIdList(ids); for (int i = 0; i < students.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(students.get(i), ToStringStyle.MULTI_LINE_STYLE)); } sqlSession.commit(); sqlSession.close(); } 结果 5.2 foreach 实现批量插入 可以通过foreach来实现批量插入。 5.2.1 动态SQL 接口方法 /** * 批量插入学生 */ int insertList(List students); 对应的SQL insert into student(name, phone, email, sex, locked) values ( #{student.name}, #{student.phone},#{student.email}, #{student.sex},#{student.locked} ) 5.2.2 测试 @Test public void insertList() { SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); List students = new LinkedList(); Student stu1 = new Student(); stu1.setName("批量01"); stu1.setPhone("13888888881"); stu1.setLocked((byte) 0); stu1.setEmail("13888888881@138.com"); stu1.setSex((byte) 1); students.add(stu1); Student stu2 = new Student(); stu2.setName("批量02"); stu2.setPhone("13888888882"); stu2.setLocked((byte) 0); stu2.setEmail("13888888882@138.com"); stu2.setSex((byte) 0); students.add(stu2); System.out.println(studentMapper.insertList(students)); sqlSession.commit(); sqlSession.close(); } 结果

6. Bind tag

The bind tag is a variable that defines a context through an OGNL expression, which is convenient for us to use.

As in the selectByStudentSelective method, there are the following

And name like concat ('%', # {name},'%')

In MySQL, this function supports multiple parameters, but only two parameters are supported in Oracle. Then we can use bind to make the SQL support two databases.

And name like # {nameLike}

The result of the changed query is as follows

At this point, the study of "how to master MyBatis dynamic SQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.