mybatis中动态sql

动态SQL

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {

    private int id;
    private String name;
    private int age;
}

Mapper接口

public interface StudentMapper {
    /**
     * if 标签
     * @param student
     * @return
     */
    List<Student> findByIf(Student student);

    /**
     * where 标签
     * @param student
     * @return
     */
    List<Student> findByWhereAndIf(Student student);
    /**
     * choose标签的使用
     */
    List<Student> findByChoose( Student student);
    /**
     * set 和if 标签使用动态更新数据库
     */
    void updateStudent(Student student);
    /**
     * 批量插入
     * foreach 标签的使用
     */
    void insertManyByForeach(@Param("students") List<Student> students);
    /**
     * 批量删除
     */
    void deleteManyByForeach(@Param("list")List<Integer> list);
}

1. if 元素

    <select id="findByIf" resultType="com.wcc.pojo.Student" >
      SELECT * FROM student WHERE
      <if test="name != null and name != ''">
           name LIKE concat ('%',#{name},'%') AND
      </if>
    </select>

2 . where标签

    <select id="findByWhereAndIf" resultType="com.wcc.pojo.Student">
      SELECT * FROM student
      <where>
          <if test="id != null">
              id > #{id}
          </if>
          <if test="name != null and name != ''">
           AND  name LIKE concat('%',#{name},'%')
          </if>
          <if test="age != null">
            AND  age > #{age}
          </if>
      </where>

3. choose ,when otherwise

    <select id="findByChoose" resultType="com.wcc.pojo.Student">
      SELECT * FROM student
        <where>
      <choose>
          <!--如果id不为空，则按照这个条件执行sql语句-->
          <when test="id != null and id != 0">
              id > #{id}
          </when>
          <!--如果id为空，name不为空，则按照这个条件执行sql语句-->
          <when test="name != null and name != ''">
              name LIKE concat('%',#{name},'%')
          </when>
          <!--以上条件都不满足，则按照如下条件，执行sql语句-->
          <otherwise>
              age IS NOT NULL
          </otherwise>
      </choose>
        </where>
    </select>

4. set和if标签动态更新数据库

    <update id="updateStudent">
    UPDATE student
    <set>
        <if test="name != null and name != ''">
            name = #{name}
        </if>
        <if test="age != null and age!= 0">
            age = #{age}
        </if>
    </set>
        WHERE id = #{id}
    </update>

5. foreach 标签

    <insert id="insertManyByForeach">
      INSERT INTO student(name,age) VALUES
        <!--collection:集合的名称 separator，下面的循环体以什么符号隔开
         item： 循环的元素  open="(" close=")" ：循环体以什么包裹起来
         -->
      <foreach collection="students" separator="," item="student" >
          (#{student.name},#{student.age})
      </foreach>
    </insert>
	
    <delete id="deleteManyByForeach">
    DELETE FROM student WHERE id IN
    <foreach collection="list" item="id" separator="," close=")" open="(">
        #{id}
    </foreach>
    </delete>

测试类

public class StudentTest {
    SqlSession sqlSession;
    @Before
    public void test() throws IOException {
        String s = "mybatis.xml";
        InputStream inputStream = Resources.getResourceAsStream(s);
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(inputStream);
         sqlSession = build.openSession();
    }
    /**
     * if 标签的测试
     */
    @Test
    public void ifTest() throws IOException {
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = new Student();
        student.setName("王");
        List<Student> list1 = mapper.findByIf(student);
        for (Student s:list1){
            System.out.println(s);
        }
    }

    /**
     * where和if标签一起使用的测试
     */

    @Test
    public void whereTest(){
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = new Student();
        student.setId(1);
        student.setAge(15);
        List<Student> list = mapper.findByWhereAndIf(student);
        for (Student s:list){
            System.out.println(s);
        }
    }

    /**
     * choose标签的测试
     */
    @Test
    public void chooseTest(){
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
       Student student = new Student();
       student.setId(2);
       student.setName("王");
        List<Student> list = mapper.findByChoose(student);
        for (Student s1:list){
            System.out.println(s1);
        }
        sqlSession.commit();
    }

    /**
     * set 和if 标签的使用
     */
    @Test
    public void setAbdIfTest(){

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student studnet = new Student();
        studnet.setId(1);
        studnet.setName("小王");
        mapper.updateStudent(studnet);
        sqlSession.commit();
    }

    /**
     * foreach元素的测试
     */
    @Test
    public void foreachTest(){
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> list = new ArrayList<>();
        for (int i = 1;i <10; i++){
            Student student = new Student();
            student.setName("nnn"+i);
            student.setAge(10+i);
            list.add(student);
        }
        mapper.insertManyByForeach(list);
        sqlSession.commit();
    }
    /**
     * 批量删除的测试
     */
    @Test
    public void deleteManyTest(){
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Integer> list = new ArrayList<>();
        for (int i = 10; i < 19 ; i++) {
            list.add(i);
        }
        mapper.deleteManyByForeach(list);
        sqlSession.commit();
    }
}