Mybatis动态SQL

范例：根据用户名称和角色id进行查询
1.接口方法

//根据用户名称和角色id进行查询
  	public List<User> getUserListByUserNameAndRoleId(@Param("userName")String userName,@Param("roleId")Integer roleId);

2.映射文件

<!-- 根据用户名称和角色id进行查询 -->
  <select id="getUserListByUserNameAndRoleId" resultType="User">
     select u.*,r.id,r.roleName from smbms_user u,smbms_role r where u.userName like concat('%',#{userName},'%') and u.userRole=#{roleId} and u.userRole=r.id
  </select>

3.测试

@Test
	public void test18(){  
		SqlSession sqlSession = SqlSessionUtil.creatSqlSession();
		List<User> user=sqlSession.getMapper(UserMapper.class).getUserListByUserNameAndRoleId("赵", 2);
		for(User u:user) {
			System.out.println(u.getUserName());
		}
		
		// 关闭sqlSession
				SqlSessionUtil.closSqleSession(sqlSession);
	}

一般而言，用户在查询时会输入查询内容，如果此时用户在搜索栏啥也没输入，应该查出所有的信息，如果按照以上写法，查出来为空。
解决改进：使用动态SQL

动态SQL

• <if>

 select u.*,r.id as r_id,r.roleName from smbms_user u,smbms_role r where 
    <if test="roleId!=null">u.userRole=#{roleId} and</if> 
    <if test="userName!=null">and u.userName like concat('%',#{userName},'%') </if>
     u.userRole=r.id 

测试

@Test
	public void test18(){  
		SqlSession sqlSession = SqlSessionUtil.creatSqlSession();
		List<User> user=sqlSession.getMapper(UserMapper.class).getUserListByUserNameAndRoleId(null, 2);
		for(User u:user) {
			System.out.println(u.getUserName());
		}
		
		// 关闭sqlSession
				SqlSessionUtil.closSqleSession(sqlSession);
	}

• <where>+<if>

 select * from smbms_user
    <where>
    <if test="userName!=null and userName!=''">and userName like concat('%',#{userName},'%') </if>
    <if test="roleId!=null">and userRole=#{roleId}</if> 
    </where>

• <trim>

<!-- trim使用 -->
	select * from smbms_user
	<trim prefix="where" prefixOverrides="and|or">
		<if test="userName!=null and userName!=''">
			and userName like concat('%',#{userName},'%')
		</if>
		<if test="roleId!=null">and userRole=#{roleId}</if> 
	</trim>

update smbms_user
  <trim prefix="set" suffix="where id=#{id}" suffixOverrides=",">
  <if test="userCode!=null">userCode=#{userCode},</if>
  <if test="userName!=null">userName=#{userName},</if>
  <if test="userPassword!=null">userPassword=#{userPassword},</if>
  <if test="phone!=null">phone=#{phone}</if>
  
  </trim>

范例：更新用户的信息
1.接口文件

//更新用户的信息
  	public void updtaUser();

2.映射文件

 <!-- 更新用户信息 -->
  <update id="updtaUser" parameterType="User">
  update smbms_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="phone!=null">phone=#{phone}</if>
  </set>
  where id=#{id}
  </update>

测试

@Test
	public void test19(){  
		SqlSession sqlSession = SqlSessionUtil.creatSqlSession();
		User user=new User();
		user.setId(14);
		user.setUserName("帅哥美女");
		user.setUserCode("123456");
		user.setPhone("null");
		sqlSession.getMapper(UserMapper.class).updtaUser(user);
		sqlSession.commit();
		
		// 关闭sqlSession
				SqlSessionUtil.closSqleSession(sqlSession);
	}

• <set>

 <!-- 更新用户信息 -->
  <update id="updtaUser" parameterType="User">
  update smbms_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="phone!=null">phone=#{phone}</if>
  </set>
  where id=#{id}
  </update>

范例：数组入参
1.接口方法

//数组入参
  	public List<User> getUserListByArray(Integer [] roleIds);

2.映射文件

<!-- 数组入参 -->
  <select id="getUserListByArray" resultMap="userResult">
  select * from smbms_user where userRole in 
  <foreach collection="array" item="roleIds" open="(" separator="," close=")">
   #{roleIds}
  </foreach>
     
  </select>
  <resultMap type="User" id="userResult">
  <id property="id" column="id"></id>
  <result property="userName" column="userName" />
  
  </resultMap>

测试

@Test
	public void test20(){  
		SqlSession sqlSession = SqlSessionUtil.creatSqlSession();
		//要查询的角色数组集合
		Integer roleIds[]= {1,2};
		List<User> list=sqlSession.getMapper(UserMapper.class).getUserListByArray(roleIds);
		for(User u:list) {
			System.out.println(u.getUserName()+"\t"+u.getUserPassword());
		}
		// 关闭sqlSession
				SqlSessionUtil.closSqleSession(sqlSession);
	}

• <foreach >

<!-- 数组入参 -->
  <select id="getUserListByArray" resultMap="userResult">
  select * from smbms_user where userRole in 
  <foreach collection="array" item="roleIds" open="(" separator="," close=")">
   #{roleIds}
  </foreach>
     
  </select>
  <resultMap type="User" id="userResult">
  <id property="id" column="id"></id>
  <result property="userName" column="userName" />
  
  </resultMap>