什么!MyBatis的xml还可以这么写
一、介绍
MyBatis
的XML
动态SQL
相信大家不陌生了,本文主要讲解一些比较容易出错、忘记,比较冷门的写法。
二、内容
1)bind标签
这是一个冷门的标签,它的作用是可以使用OGNL
表达式创建一个变量设置到上下文中。
比如说要进行一次模糊查询,使用bind
标签来进行拼接百分号
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace ="com.banmoon.test.mapper.UserMapper" > <select id ="getListByUsername" resultMap ="BaseResultMap" > select * from sys_user <where > <if test ="username != null and username != ''" > <bind name ="searchUsername" value ="'%' + username + '%'" /> username like #{searchUsername} </if > </where > </select > </mapper >
bind
标签还可以调用java
的方法,如下这样使用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 package com.banmoon.test.utils;import cn.hutool.core.lang.Assert;import cn.hutool.core.util.StrUtil;import lombok.AccessLevel;import lombok.NoArgsConstructor;@NoArgsConstructor(access = AccessLevel.PRIVATE) public class MybatisUtil { public static String likeConcat (String value) { Assert.notBlank(value); return StrUtil.format("%{}%" , value); } }
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.banmoon.test.mapper.UserMapper" > <select id="getListByUsername" resultMap="BaseResultMap" > select * from sys_user <where> <if test="username != null and username != ''" > <bind name="searchUsername" value="@com.banmoon.test.utils.MybatisUtil@likeConcat(username)" /> username like #{searchUsername} </if > </where> </select> </mapper>
通过@符号进行调用Java类方法,一些复杂的参数可以直接这样调用获取了。
2)if判断0的时候
对于if
标签大家都不陌生了,但这里面有使用上的坑需要注意
在如何判断0
这个坑上,我遇到了很多次,我的同事也遇到很多次
先来看看下面这段
1 2 3 4 5 6 7 8 9 10 11 12 13 14 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace ="com.banmoon.test.mapper.UserMapper" > <select id ="getListByStatus" resultMap ="BaseResultMap" > select * from sys_user <where > <if test ="status != null and status != ''" > and `status` = #{status} </if > </where > </select > </mapper >
如果传入的status
是0
,那么这段个判断,就会判断为false
,条件就不再会进入。
原因就是statsu != ''
在源码中会被当做status != 0
来进行判断,故此不会进入判断
我们只需要将status != ''
删除掉就可以进入判断了
3)一对一、一对多映射
mybatis
可以将结果集封装成java
对象,单个对象的映射大家都会,这里主要讲一下一对一和一对多的映射使用
先讲一对一,在一个对象中有另一个对象,我们只需要如此就好
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace ="com.banmoon.test.mapper.UserMapper" > <resultMap id ="UserDTOResultMap" type ="com.banmoon.test.dto.UserDTO" > <id column ="id" property ="id" /> <result column ="username" property ="username" /> <result column ="status" property ="status" /> <association property ="userInfo" javaType ="com.banmoon.test.dto.UserInfo" > <id column ="info_id" property ="id" /> <result column ="real_name" property ="realName" /> <result column ="nick_name" property ="nickName" /> <result column ="sex" property ="sex" /> <result column ="age" property ="age" /> </association > </resultMap > <select id ="getInfoList" resultMap ="UserDTOResultMap" > select t0.id, t0.username, t0.`status`, t1.id as info_id, t1.real_name, t1.nick_name, t1.sex, t1.age from sys_user t0 left join sys_user_info t1 on t0.id = t1.user_id where t0.`status` = 0 </select > </mapper >
对应的Java
类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 package com.banmoon.test.dto;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data @NoArgsConstructor @AllArgsConstructor public class UserDTO { private Integer id; private String username; private Integer status; private UserInfo userInfo; }
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 package com.banmoon.test.dto;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data @NoArgsConstructor @AllArgsConstructor public class UserInfo { private Integer id; private String realName; private String nickName; private Integer sex; private Integer age; }
当执行后,分别看看数据库中查询,和mybatis
映射后的结果集
sql查询
结果集
如果是一对多,需要只需要这样做,修改一下resultMap
映射
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace ="com.banmoon.test.mapper.UserMapper" > <resultMap id ="UserPermissionDTOResultMap" type ="com.banmoon.test.dto.UserPermissionDTO" > <id column ="id" property ="id" /> <result column ="username" property ="username" /> <result column ="status" property ="status" /> <collection property ="roleList" ofType ="com.banmoon.test.dto.RoleDTO" > <id column ="role_id" property ="id" /> <result column ="role_name" property ="name" /> </collection > <collection property ="permissionList" ofType ="java.lang.String" > <constructor > <arg column ="permission_name" /> </constructor > </collection > </resultMap > <select id ="getPermissionUserList" resultMap ="UserPermissionDTOResultMap" > select t0.id, t0.username, t0.`status`, t2.id as role_id, t2.`name` as role_name, t4.`name` as permission_name from sys_user t0 left join sys_user_role t1 on t0.id = t1.user_id left join sys_role t2 on t1.role_id = t2.id left join sys_role_permission t3 on t2.id = t3.role_id left join sys_permission t4 on t3.permission_id = t4.id where t0.`status` = 1 order by id </select > </mapper >
看看对应的Java
类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 package com.banmoon.test.dto;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.util.List;@Data @NoArgsConstructor @AllArgsConstructor public class UserPermissionDTO { private Integer id; private String username; private Integer status; private List<String> permissionList; private List<RoleDTO> roleList; }
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 package com.banmoon.test.dto;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data @NoArgsConstructor @AllArgsConstructor public class RoleDTO { private Integer id; private String name; }
当执行后,分别看看数据库中查询,和mybatis
映射后的结果集
sql查询
结果集
需要注意的是,使用了这种一对多的映射后,就不能使用分页了
4)在xml中调用java方法
上面已经用bind
标签,调用过java
中的方法了
使用的OGNL
表达式,如下
1 $ {@prefix@methodName(入参...)}
出了bind
标签,我们还可以在其他地方使用此表达式进行调用java
方法
场景
使用
赋值时
if
标签判断
三、最后
我是半月,你我一同共勉!!!