一、if
1.动态sql编写
<select id="findByIf" resultType="user" parameterType="map" >
select * from user
<where>
<if test="username !=null">
and username=#{username}
</if>
<if test="password !=null">
and password = #{password}
</if>
</where>
</select>
2.测试查询
@Test
public void fun(){
SqlSession sqlsession = MybatisUtil.getSqlsession();
UserMapper mapper = sqlsession.getMapper(UserMapper.class);
Map map = new HashMap();
//map.put("username","张三");
map.put("password","123");
List<User> userList = mapper.findByIf(map);
for (User user : userList) {
System.out.println(user);
}
}
3.结果输出
==> Preparing: select * from user WHERE password = ?
==> Parameters: 123(String)
<== Columns: id, username, password
<== Row: 1, 张三, 123
<== Total: 1
User{id=1, username='张三', password='123'}
二、choose-when-otherwise
1.编写动态sql
<select id="findByChoose" parameterType="map" resultType="user">
select * from user
<where>
<choose>
<when test="username !=null">
username =#{username}
</when>
<when test="password !=null">
password = #{password}
</when>
<otherwise>
1 = 1
</otherwise>
</choose>
</where>
</select>
2.测试查询
@Test
public void fun2(){
SqlSession sqlsession = MybatisUtil.getSqlsession();
UserMapper mapper = sqlsession.getMapper(UserMapper.class);
Map map = new HashMap();
//map.put("username","张三");
//map.put("password","123");
List<User> userList = mapper.findByChoose(map);
for (User user : userList) {
System.out.println(user);
}
}
3.结果输出
==> Preparing: select * from user WHERE 1 = 1
==> Parameters:
<== Columns: id, username, password
<== Row: 1, 张三, 123
<== Row: 2, 李四, 444
<== Row: 3, 王五, 111
<== Row: 4, 赵六, 222
<== Total: 4
User{id=1, username='张三', password='123'}
User{id=2, username='李四', password='444'}
User{id=3, username='王五', password='111'}
User{id=4, username='赵六', password='222'}
三、set
1.编写动态sql
<update id="updataBySet" parameterType="map">
update user
<set>
<if test="username !=null">
username = #{username},
</if>
<if test="password !=null">
password = #{password},
</if>
</set>
<where>
id = #{id}
</where>
</update>
2.测试查询
@Test
public void fun3(){
SqlSession sqlsession = MybatisUtil.getSqlsession();
UserMapper mapper = sqlsession.getMapper(UserMapper.class);
Map map = new HashMap();
//map.put("username","张三2");
map.put("password","1212");
map.put("id",1);
Integer integer = mapper.updataBySet(map);
sqlsession.commit();
sqlsession.close();
}
3.结果输出
==> Preparing: update user SET password = ? WHERE id = ? ==> Parameters: 1212(String), 1(Integer)<== Updates: 1true
四、foreach
1.编写动态sql
<select id="findByForEach" parameterType="map" resultType="user"> select * from user where id in <foreach collection="ids" item="id" open="(" separator="," close=")"> #{id} </foreach></select>
2.测试查询
@Testpublic void fun4(){ SqlSession sqlsession = MybatisUtil.getSqlsession(); UserMapper mapper = sqlsession.getMapper(UserMapper.class); Map map = new HashMap(); List<Integer> list = new ArrayList<Integer>(); list.add(1); list.add(2); list.add(4); map.put("ids",list); List<User> userList = mapper.findByForEach(map); for (User user : userList) { System.out.println(user); } sqlsession.commit(); sqlsession.close();}
3.结果输出
==> Preparing: select * from user where id in ( ? , ? , ? ) ==> Parameters: 1(Integer), 2(Integer), 4(Integer)<== Columns: id, username, password<== Row: 1, 张三, 1212<== Row: 2, 李四, 444<== Row: 4, 赵六, 222<== Total: 3User{id=1, username='张三', password='1212'}User{id=2, username='李四', password='444'}User{id=4, username='赵六', password='222'}