where语句用使用if
需求:
需求:实现一个用户的高级查询功能,根据输入的条件去检索用户信息。说明:
当只输入用户名时,需要根据用户名模糊查询;
当只输入邮箱时,根据邮箱进行完全匹配;
当同时输入用户名和邮箱时,用这两个条件同时查询。
sql语句如下:
<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
SELECT id,
user_name userName,
user_password userPassword,
user_email userEmai,
user_info userInfo,
head_Img headImg,
create_time createTime
FROM sys_user
WHERE user_name like CONCAT('%',#{userName},'%')
AND user_email = #{userEmail}
</select>
这样的sql有问题:当userName和userEmail都有内容时,能够查询出正确结果,若userName有值,userEmail没有值,则userEmail=null作为查询条件,查询不到结果。使用if解决
sql更改为如下代码:
<select id="selectByUser" resultType="tk.mybatis.simple.model.SysUser">
SELECT id,
user_name userName,
user_password userPassword,
user_email userEmai,
user_info userInfo,
head_Img headImg,
create_time createTime
FROM sys_user
WHERE
1=1
<if test="userName != null and userName != ''">
AND user_name like CONCAT('%',#{userName},'%')
</if>
<if test="userEmail != null and userEmail != ''">
AND user_email = #{userEmail}
</if>
</select>
Note:userName != null 和 userName == null 适用于任意类型
userName != ‘’ 和 userName == ‘’ 适用于字符串
UserMapper.java中的代码如下:
/**
* 根据动态条件查询用户
* @param user
* @return
*/
SysUser selectByUser(SysUser user);
测试代码
@Test
public void testSelectByUser(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser user = new SysUser();
user.setUserName("dm");
user.setUserEmail("admin@mybatis.tk");
SysUser sysUser = userMapper.selectByUser(user);
System.out.println(sysUser.getId());
}finally {
sqlSession.close();
}
}
注意:where后面有1=1??为什么?
如果两个条件都不满足,则sql语句以where结束。
注意:条件中and或者or需要自己手动添加
update更新列中用使用if
需求:
只更新有变化的字段。需要注意,更新的时候不能将原来有值但没有发生变化的字段更新为空或者null。通过if标签可以实现这种动态列更新
UserMapper.java中添加如下代码:
/**
* 根据主键更新
* @param user
* @return
*/
int updateByIdSelective(SysUser user);
xml配置代码如下:
<update id="updateByIdSelective">
UPDATE sys_user
set
<if test="userName!=null and userName!=''">
user_name = #{userName},
</if>
<if test="userPassword!=null and userPassword!=''">
user_password = #{userPassword},
</if>
<if test="userEmail!=null and userEmail!=''">
user_email = #{userEmail},
</if>
<if test="userInfo!=null and userInfo!''">
user_info = #{userInfo},
</if>
<if test="headImg != null">
head_img = #{headImg,jdbcType=BLOB},
</if>
<if test="createTime != null">
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
id = #{id}
WHERE id=#{id}
</update>
测试 代码如下:
@Test
public void testUpdateByIdSelective(){
SqlSession sqlSession = getSqlSession();
try {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
SysUser user = new SysUser();
user.setId(1L);
user.setUserName("dm");
user.setUserEmail("admin@mybis.tk");
int i = userMapper.updateByIdSelective(user);
System.out.println(i);
}finally {
sqlSession.rollback();
sqlSession.close();
}
}
测试结果:
[DEBUG] 2018-04-27 18:53:38,494 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Preparing: UPDATE sys_user set user_name = ?, user_email = ?, id = ? WHERE id=?
[DEBUG] 2018-04-27 18:53:38,531 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Parameters: dm(String), admin@mybis.tk(String), 1(Long), 1(Long)
[DEBUG] 2018-04-27 18:53:38,582 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<== Updates: 1
1
insert语句中用使用if
修改insert2的xml配置文件如下:
<insert id="insert2" useGeneratedKeys="true" keyColumn="id">
INSERT INTO sys_user
(user_name,user_password,
<if test="userEmail != null and userEmail != ''">
user_email,
</if>
user_info,head_img,create_time)
VALUES
(#{userName},#{userPassword},
<if test="userEmail != null and userEmail != ''">
#{userEmail},
</if>
#{userInfo},#{headImg,jdbcType=BLOB},
#{createTime,jdbcType=TIMESTAMP})
</insert>
测试代码忽略…