Mybatis04---动态SQL

动态SQL

IF

根据性别和名字查询

接口

 public List<User> findif(User user);

映射文件 

 <select id="findif" parameterType="User" resultType="user">
        select * from User where
        <if test="sex != null and sex != '' ">
           sex =  #{sex}
        </if>
        <if test="username != null and username != '' ">
         and  username =  #{username}
        </if>
    </select>

测试类

 String resource = "SqlMapConfig.xml";
            InputStream in = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
            SqlSession sqlSession = sqlSessionFactory.openSession(true);

            usermapper mapper = sqlSession.getMapper(usermapper.class);

            User user = new User();
            user.setUsername("张小明");
            user.setSex("1");
            List<User> users = mapper.findif(user);

                System.out.println(users);

 如果username的属性不对，对代码毫无影响，但是sex如果不对，sql语句会发生拼接错误

所以再where后面加上1=1，是sql成立

where

去掉IF标签中的前and，是sql语句拼接成功

映射文件

     <select id="findif" parameterType="User" resultType="user">
        select * from User
        <where>
        <if test="sex != null and sex != '' ">
           sex =  #{sex}
        </if>
        <if test="username != null and username != '' ">
         and  username =  #{username}
        </if>
        </where>
    </select>

SQL片段

  <sql id="tet" >
        select * from USER
    </sql>

<select id="findif" parameterType="User" resultType="user">
        <include refid="tet"/>
        <where>
        <if test="sex != null and sex != '' ">
           sex =  #{sex}
        </if>
        <if test="username != null and username != '' ">
         and  username =  #{username}
        </if>
        </where>
    </select>

Foreach

是将id的参数，接收过来进行遍历

select * from user where id in (1,2,3)

mapper接口：三种方式

public List<User> findlist1(Integer[] args);
    public List<User> findlist2(List<Integer> idslist);
    public List<User> findlist3(QueryVo vo);

mapper映射文件：collection的值，参数是数组则是array，参数是list集合则是list，参数是包装类则是注入包装类的属性名

item="值是什么无所谓"，但是要与#{值}要一致

       <select id="findlist1" parameterType="QueryVo" resultType="User">
        SELECT * FROM USER WHERE id IN
        <foreach collection="array" item="id"   open="(" close=")" separator="," >
            #{id}
        </foreach>

测试类

        包装类
         List<Integer> idslist = new ArrayList<Integer>();
            idslist.add(1);
            idslist.add(22);
            idslist.add(10);
            QueryVo vo = new QueryVo(idslist);

            List<User> users = mapper.findlist3(vo);
            System.out.println(users);

       数组
         Integer[] args = new Integer[3];
            args[0] = 1;
            args[1] = 10;
            args[2] = 16;

            List<User> users = mapper.findlist1(args);
            System.out.println(users);

       集合
      List<Integer> idslist  =  new ArrayList<Integer>();
            idslist.add(1);
            idslist.add(10);
            idslist.add(16);
            List<User> users = mapper.findlist2(idslist);
            System.out.println(users);