使用动态 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 包含的内容的首尾进行指定内容的忽略
- <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>
- <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>
- <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());
- }
- }
- <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());
- }
- }
- <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());
- }
- }
- public List<User> getUserList_choose(@Param("userName")String userName,@Param("userRole")Integer roleId,
- @Param("userCode")String userCode,@Param("creationDate")Date creationDate);
- <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>
来源: https://www.cnblogs.com/anxin0/p/9926500.html