myBatis动态sql查询

mybatis提供的各种标签方法可以实现动态拼接sql。

使用<if/>标签对字符串类型的数据做不等于空字符串的校验。
使用<where/>标签能够省去第一个AND之前的数据。
sql代码段可以把常用的sql代码提取出来。

使用Mapper开发方式需注意

1. statement的id是sql的id
2. paramenterType表明输入的类型paramenterType表明输入的类型
3. resultType是返回的类型，填写pojo类的全类名resultType是返回的类型，填写pojo类的全类名

例如：

根据性别和姓名查询用户

UserMapper.java

 public List<User> selectUserBySexAndUsername(User user);

UserMapper.xml

<!-- 根据条件查询用户 -->
<select id="selectUserBySexAndUsername" parameterType="user" resultType="user">
	SELECT * FROM `user`
	WHERE 1=1
	<if test="sex != null and sex != ''">
		AND sex = #{sex}
	</if>
	<if test="username != null and username != ''">
		AND username LIKE
		'%${username}%'
	</if>
</select>

如上所述，利用if标签对字符串做非空判断，这里如果没有Where 1==1，sex一旦为空则会报错。

添加<where/>标签,使用sql代码段提取出常用代码。

<mapper namespace="main.java.maping.UserMapper">
    <!--sql片段-->
   <sql id="selector">
       select *
       from user
   </sql>
  <select id="selectUserBySexAndUsername" parameterType="user" resultType="user">
        <include refid="selector"/>
        <where>
            <if test="sex != null and sex != ''">
                sex = #{sex}
            </if>
            <if test="username != null and username != ''">
                and username = #{username}
            </if>
        </where>
    </select>
</mapper>

根据多个id查询用户信息

这里列出3种查询方式，看似类似，实则有坑。

UserMapper.java

   public List<User> selectUserByIds1(QueryVo ids);
   public List<User> selectUserByIds2(Integer[] ids);  
   public List<User> selectUserByIds3(List<Integer> idsList);   
 

包装类

public class QueryVo implements Serializable {

    private User user;
    private List<Integer> idsList;
    private Integer[] ids;
    ｝

1.包装类型查找

测试类

public class MapperTest {

    private SqlSessionFactory sqlSessionFactory;
    @Before
    public void before() {
        String resource = "main/resources/mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    }
    @Test
    public void selectUserByIdsTest1(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        QueryVo queryVo = new QueryVo();
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(10);
        ids.add(16);
        ids.add(22);
        queryVo.setIdsList(ids);
        List<User> list = userMapper.selectUserByIds1(queryVo);
        for (User u : list){
            System.out.println(u);
        }
    }
}

UserMapper.xml
对包装类型中的列表数据循环调用。

<mapper namespace="main.java.maping.UserMapper">
    <!--sql片段-->
   <sql id="selector">
       select *
       from user
   </sql>
   <select id="selectUserByIds" parameterType="QueryVo" resultType="User">
        <include refid="selector"/>
            <where>
                  id in
                <foreach collection="idsList" item="id" separator="," open="(" close=")">
                <!--使用foreach标签循环-->
                    #{id}
                </foreach>
            </where>
    </select>
</mapper>

数组方式

测试类
传入一个数组

    @Test
    public void selectUserByIdsTest2(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Integer[] id = new Integer[]{10,16,22};
        List<User> list2 = userMapper.selectUserByIds2(id);
        for (User u : list2){
            System.out.println(u);
        }
    }

UserMapper.xml

注意foreach里调用的不是ids
直接传递数组用array

 <select id="selectUserByIds" parameterType="QueryVo" resultType="User">
        <include refid="selector"/>
            <where>
                  id in
                <foreach collection="array" item="id" separator="," open="(" close=")">
                    #{id}
                </foreach>
            </where>
    </select>

列表方式

这里是list

  <foreach collection="list" item="id" separator="," open="(" close=")">