MyBatis中动态sql的实现方法示例

select * from user where user.sex = #{user.sex} and user.username like \'%${user.username}%\'

<select id=\"findUserList\" parameterType=\"mybatis.po.UserQueryVo\" resultType=\"mybatis.po.User\">
 select * from user <!-- where可以自动去掉条件中的第一个and -->
 <where>
 <if test=\"user!=null\">
 <if test=\"user.sex!=null and user.sex!=\'\'\">
 and user.sex = #{user.sex} </if>
 <if test=\"user.username!=null and user.username!=\'\'\">
 and user.username like \'%${user.username}%\' </if>
 </if>
 </where>
</select>

<sql id=\"query_user_where\">
 <if test=\"user!=null\">
 <if test=\"user.sex!=null and user.sex!=\'\'\">
 and user.sex = #{user.sex} </if>
 <if test=\"user.username!=null and user.username!=\'\'\">
 and user.username like \'%${user.username}%\' </if>
 </if>
</sql>

<select id=\"findUserList\" parameterType=\"mybatis.po.UserQueryVo\" resultType=\"mybatis.po.User\">
 select * from user <where>
 <!-- 引用sql片段的id，如果refid指定的id不在本mapper文件中，需要在前面加上namespace -->
 <include refid=\"query_user_where\"></include>
 <!-- 还可以引用其他sql片段 -->
 </where>
</select>

SELECT * FROM USER WHERE id=1 OR id=12 OR id=17SELECT * FROM USER WHERE id IN(1,12,17)

//传入多个id
private List<Integer> ids;

<sql id=\"query_user_where\">
 <if test=\"user!=null\">
 <if test=\"user.sex!=null and user.sex!=\'\'\">
 and user.sex = #{user.sex} </if>
 <if test=\"user.username!=null and user.username!=\'\'\">
 and user.username like \'%${user.username}%\' </if>
 </if>
 <if test=\"ids!=null\">
 <!-- 使用右边的sql拼接：AND (id=1 OR id=12 OR id=17) -->
 <foreach collection=\"ids\" item=\"user_id\" open=\"AND (\" close=\")\" separator=\"OR\">
 id=#{user_id} 
  </foreach>
 </if>
</sql>

@Testpublic void testFindUserList() throws Exception {
 
 SqlSession sqlSession = sqlSessionFactory.openSession();
 //创建UserMapper对象，mybatis自动生成mapper代理对象
 UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
 
 //创建包装对象，设置查询条件
 UserQueryVo userQueryVo = new UserQueryVo();
 User user = new User();
 //由于这里使用动态sql，如果不设置某个值，条件不会拼接在sql中
 user.setSex(\"男\");
 user.setUsername(\"倪升武\");
 
 //传入多个id
 List<Integer> ids = new ArrayList<Integer>();
 ids.add(1);
 ids.add(12);
 ids.add(17);
 userQueryVo.setIds(ids);
 
 userQueryVo.setUser(user); 
 //调用userMapper的方法
 List<User> list = userMapper.findUserList(userQueryVo);
 System.out.println(list);}

select * from user WHERE user.sex = ? and user.username like \'%倪升武%\' AND ( id=? OR id=? OR id=? ) 

<!-- 使用右边的sql拼接：AND id IN(1,12,17) -->
<foreach collection=\"ids\" item=\"user_id\" open=\"AND id IN(\" close=\")\" separator=\",\">
 #{user_id}</foreach>