动态SQL
使用动态SQL完成多条件查询
动态SQl是MyBatis的一个强大的特性,在使用JDBC操作数据时,如果查询条件过多,将字符串联成SQL语句是比较麻烦的一件事,且容易出错,有了动态SQL我们就可以使用动态SQL，动态SQL基于OGNL表达式,可使我们方便地在SQL语句中实现某些逻辑。好了废话不多说,接下来我们看下示例
在这里我们还是使用上一篇文章使用的示例,只是修改一下,大家可以先看一下上一篇文章
用于实现动态SQL的元素如下
- if：利用if实现简单的条件选择
- choose(when,otherwise)：相当于Java中的switch语句，通常与when和otherwise搭配
- where：简化SQL语句中where的条件判断。
- set：解决动态更新语句 trim：可以灵活地去除多余的关键字
- foreach：迭代一个集合，通常用于in条件
使用if做多条件查询
修改UserMapper.xml文件
<select id="getUserList" resultMap="userList">
SELECT u.*,r.roleName FROM USER u,Role r WHERE u.userRole=r.id
<if test="userRole!=null">
AND u.userRole=#{userRole}
</if>
<if test="userName!=null and userName!=''">
AND u.userName LIKE concat('%',#{userName},'%')
</if>
</select>
这段代码的意思就是,如果有角色id就以角色id查询,有用户名字就以用户名字查询,若都没有就全部查询,这和我们之前JDBC中where条件加1==1的意思是一样的
修改接口中的方法
public List<User> getUserList(@Param("userRole")Integer roleId,@Param("userName")String userName);
添加测试
public void testQuery(){
SqlSession sqlSession=null;
try{
sqlSession=MyBatisUtil.createSqlSession();
//这里我们就写两个参数,看看会出现什么结果
List<User> userList=sqlSession.getMapper(UserMapper.class).getUserList(2,null);
for (User user: userList) {
System.out.println(user.getUserName());
}
}catch (Exception ex){
ex.printStackTrace();
}finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
}
使用where
where元素主要用来简化SQL语句中的where条件判断,并能智能地处理and和or,如果有多余的and或者or where会智能的去除,我们可以把他和if联合起来一块使用
修改UserMapper.xml
<!--注意开启自动映射-->
<select id="getUserList" resultType="User">
SELECT * FROM USER
<where>
<if test="userName!=null and userName!=''">
AND userName LIKE concat('%',#{userName},'%')
</if>
<if test="userRole!=null">
AND userRole=#{userRole}
</if>
</where>
</select>
使用if+trim实现多条件查询
trim元素也会自动识别其标签是否有返回值,若有返回值,会在自己包含前后加上某些前缀(比如我们的修改语句加set),也可在其后加上某些后缀(就像我们的修改语句最后一般会有个修改的条件) 后续我们会详细的讲前缀后缀的使用方法
修改UserMapper.xml
<select id="getUserList" resultType="User">
SELECT * FROM USER
<trim prefix="where" prefixOverrides="and | or">
<if test="userName!=null and userName!=''">
AND userName LIKE concat('%',#{userName},'%')
</if>
<if test="userRole!=null">
AND userRole=#{userRole}
</if>
</trim>
</select>
prefix：前缀，作用是通过自动识别是否有返回值后，在trim包含的内容上加上前缀，如此处的 where
suffix：后缀，作用是在trim包含的内容上加上后缀
prefixOverrides：对于trim包含内容的首部进行指定内容（如此处的"and | or"）的忽略
suffixOverrides：对用trim包含的内容的首尾进行指定内容的忽略
使用动态SQL实现更新操作
使用if+set改造更新操作
set元素主要用于更新操作,它的主要功能和where元素差不多,主要是在包含的语句前面输出一个set,若包含的语句逗号结尾，自动忽略逗号
在映射文件中编写修改语句
<update id="modify" parameterType="User">
UPDATE USER
<set>
<if test="userCode!=null">userCode=#{userCode},</if>
<if test="userName!=null">userName=#{userName},</if>
<if test="userPassword!=null">userPassword=#{userPassword},</if>
<if test="gender!=null">gender=#{gender},</if>
<if test="phone!=null">phone=#{phone},</if>
<if test="address!=null">address=#{address},</if>
<if test="userRole!=null">userRole=#{userRole},</if>
<if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
<if test="modifyDate!=null">modifyDate=#{modifyDate},</if>
<if test="birthday!=null">birthday=#{birthday},</if>
</set>
WHERE id=#{id}
</update>
下面我们用if+trim修改SQl语句
<update id="modify" parameterType="User">
UPDATE USER
<trim prefix="set" suffixOverrides="," suffix="where id=#{id}">
<if test="userCode!=null">userCode=#{userCode},</if>
<if test="userName!=null">userName=#{userName},</if>
<if test="userPassword!=null">userPassword=#{userPassword},</if>
<if test="gender!=null">gender=#{gender},</if>
<if test="phone!=null">phone=#{phone},</if>
<if test="address!=null">address=#{address},</if>
<if test="userRole!=null">userRole=#{userRole},</if>
<if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
<if test="modifyDate!=null">modifyDate=#{modifyDate},</if>
<if test="birthday!=null">birthday=#{birthday},</if>
</trim>
</update>
属性的意思上面也都说过了,大家可以翻到上面看一下
使用foreach完成复杂查询
上面我们已经讲过动态SQL的if,where,trim,set元素来处理一些简单查询操作,那么对于一些SQL语句中含有in条件,我们就需要使用foreach标签
foreach主要用在构建in条件中，在sql语句中迭代一个集合。它的主要属性有，item、index、
collection、separator、close、open。下面我们通过示例给大家进行详细介绍
1.MyBatis入参为数组类型的foreach类型
编写接口
public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);
修改UserMapper.xml
<resultMap id="userMapByRole" type="User">
<id property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
</resultMap>
<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
SELECT * FROM USER WHERE userRole IN
<foreach collection="array" item="roleIds" open="(" separator="," close=")">
#{roleIds}
</foreach>
</select>
编写测试
public void testGetUserByRoleId_foreach_array(){
SqlSession sqlSession=null;
List<User> userList=new ArrayList<User>();
Integer[] roleIds={2,3};
try{
sqlSession=MyBatisUtil.createSqlSession();
userList=sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleIds);
}catch (Exception ex){
ex.printStackTrace();
}finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user: userList) {
System.out.println(user.getUserName()+"\t"+user.getAddress());
}
}
各个属性的意思:
item：表示集合中每一个元素进行迭代时的名称
index：指定一个名称，用于表示在迭代过程中，每次迭代到的位置
open：表示该语句以什么开始（in语句以"（"开始）
separator：表示在每次迭代之间以什么符号做分割符
close：表示该语句以什么结束
collection：必须指定，入参为单参类型是List时，collection属性值为list;入参为单参是数组时，为 array；若为多参，需封装Map
parameterType可以不配置，MyBatis会自动封装为Map传入
2.MyBatis入参为List类型的foreach迭代
在接口中添加方法
public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);
修改UserMapper.xml
<resultMap id="userMapByRole" type="User">
<id property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
</resultMap>
<select id="getUserByRoleId_foreach_list" resultMap="userMapByRole">
SELECT * FROM USER WHERE userRole IN
<foreach collection="list" item="roleIds" open="(" separator="," close=")">
#{roleIds}
</foreach>
</select>
编写测试
public void testGetUserByRoleId_foreach_array(){
SqlSession sqlSession=null;
List<User> userList=new ArrayList<User>();
List<Integer> nums=new ArrayList<Integer>();
nums.add(1);
nums.add(2);
try{
sqlSession=MyBatisUtil.createSqlSession();
userList=sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_list(nums);
}catch (Exception ex){
ex.printStackTrace();
}finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user: userList) {
System.out.println(user.getUserName()+"\t"+user.getAddress());
}
}
3.MyBatis入参为Map类型的foreach迭代
在上面两个示例中,我们都是以一个参数入参的,如果我们查询管理员并且是男性该怎么写那,这种多参数入参我们就可以使用Map入参的方式
编写接口中的方法
public List<User> getUserByConditionMap_foreach_map(Map<String,Object> conditionMap);
修改UserMapper.xml
<select id="getUserByConditionMap_foreach_map" resultMap="userMapByRole">
SELECT * FROM USER WHERE gender=#{gender} AND userRole IN
<foreach collection="roleIds" item="roleMap" open="(" separator="," close=")">
#{roleMap}
</foreach>
</select>
编写测试
public void testGetUserByRoleId_foreach_array(){
SqlSession sqlSession = null;
List<User> userList = new ArrayList<User>();
Map<String, Object> param = new HashMap<String, Object>();
List<Integer> roleList = new ArrayList<Integer>();
roleList.add(1);
roleList.add(2);
param.put("gender",2);
param.put("roleIds",roleList);
try {
sqlSession = MyBatisUtil.createSqlSession();
userList = sqlSession.getMapper(UserMapper.class).getUserByConditionMap_foreach_map(param);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
MyBatisUtil.closeSqlSession(sqlSession);
}
for (User user : userList) {
System.out.println(user.getUserName() + "\t" + user.getAddress());
}
}
总结：
1.MyBatis接受的参数类型：基本类型、对象、List、数组、Map
2.无论MyBatis的入参是哪种数据类型，MyBatis都会将参数放在一个Map中
对于单参数入参的情况：
若入参为基本类型：变量名作为key，变量值为value，此时生成的Map只有一个元素若入参为对象：对象的属性名做key，属性值为value
若入参为List：默认“list”作为key，该List即为value
若入参为数组：默认“array”作为key，该数组即为value
若入参为Map：键值不变
choose（when,otherwise）
choose可以选择其中一种情况下的查询结果，流程和switch相同，通常都是搭配when,otherwise使用
编写接口方法
public List<User> getUserList_choose(@Param("userName")String userName,@Param("userRole")Integer roleId,
@Param("userCode")String userCode,@Param("creationDate")Date creationDate);
修改UserMapper.xml
<select id="getUserList_choose" resultType="User">
SELECT * from USER WHERE 1=1
<choose>
<when test="userName!=null and userName!=''">
AND userName=#{userName}
</when>
<when test="userCode!=null and userCode!=''">
AND userCode LIKE concat('%',#{userCode},'%')
</when>
<when test="userRole!=null and userRole!=''">
AND userRole=#{userRole}
</when>
<otherwise>
AND YEAR(creationDate)=YEAR(#{creationDate})
</otherwise>
</choose>
</select>
各个属性的意思:
when元素:当其test属性中条件满足的时候,就会执行,一旦有一个条件满足,则跳出choose
otherwise元素:当when中所有条件都不满足的时候,就会执行otherwise中的内容
多敲几遍你就会明白其中的奥义,可能我写的文章还有许多地方不足,有什么问题大家可以留言讨论
by安心
