MyBatis动态sql实现

定义接口

public interface StudentMapper {

    public List<Student> getByAge(int age);
    //if测试
    public List<Student> getStudent_Where(Student student);
    //where测试
    public List<Student> getStudent_Where1(Student student);
    //choose when otherwise测试
    public List<Student> getStudent_choosewhenotherwise(Map<String,Object> map);
    //in foreach
    public List<Student> getStudentForeach(Map<String,Object> map);
    //trim查询
    public List<Student> getStudentByTrim(Student student);
    //trim增加
    public void insertByTrim(Map<String,Object> map);
    //set修改
    public void updateStudent(Student student);
}

xml绑定sql

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.yhy.mapper.StudentMapper">

    <!--if-->
    <select id="getStudent_Where" resultType="Student" parameterType="Student">
        select * from tbl_student where 1=1
        <if test="age>20">
            and age=#{age}
        </if>
        <if test="name!=null">
            and name=#{name}
        </if>
    </select>
    <!--where-->
    <select id="getStudent_Where1" resultType="Student" parameterType="Student">
        select * from tbl_student
        <where>
            <if test="name!=null &amp;&amp;name!=''">
                name = #{name}
            </if>
            <if test="score!=null &amp;&amp; score!=''">
                score=#{score}
            </if>
        </where>
    </select>
    <!--choose-->
    <select id="getStudent_choosewhenotherwise" resultType="Student" parameterType="map">
        select * from tbl_student
        <choose>
            <when test="id!=null &amp;&amp;id!=''">
                where id=#{id}
            </when>
            <when test="name!=null &amp;&amp;name!=''">
                where name=#{name}
            </when>
            <otherwise>
                where 1=1
            </otherwise>
        </choose>
    </select>
    <!--foreach-->
    <select id="getStudentForeach" resultType="Student" parameterType="map">
        select * from tbl_student
        <if test="ages!=null">
            <where>
                age IN
                <foreach collection="ages" item="val" open= "(" separator= "," close= ")">
                    #{val}
                </foreach>
            </where>
        </if>
    </select>
    <!--trim查询-->
    <select id="getStudentByTrim" parameterType="Student" resultType="Student">
        select * from tbl_student
        <trim prefix="where" prefixOverrides="and|or">
            <if test="name!=null &amp;&amp;name!=''">
                and name=#{name}
            </if>
        </trim>
    </select>
    <!--trim增加-->
    <insert id="insertByTrim" parameterType="map">
        insert into tbl_student
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                name,
            </if>
            <if test="score != null">
                score,
            </if>
            <if test="birth != null">
                birth,
            </if>
            <if test="age != null">
                age,
            </if>
        </trim>
        <trim prefix=" values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name},
            </if>
            <if test="score != null">
                #{score},
            </if>
            <if test="birth != null">
                #{birth},
            </if>
            <if test="age != null">
                #{age},
            </if>
        </trim>
    </insert>

    <update id="updateStudent" parameterType="Student">
        update tbl_student
        <set>
            <if test="name  !=null and name !=''">name=#{name}</if>
            <if test="score !=null and score !=''">score=#{score}</if>
            <if test="birth !=null and birth !=''">birth=#{birth}</if>
            <if test="age !=null and age !=''">age=#{age}</if>
        </set>
        where id=#{id}
    </update>

</mapper>

测试类测试

public class TestStudent {
    public static void main(String[] args) {
        SqlSession session = SSF.getSqlSessionFactory().openSession();
        try {
            StudentMapper mapper = session.getMapper(StudentMapper.class);
            Student student=new Student();


            //if
//            List<Student> byAge = mapper.getStudent_Where(student);
//            for (Student student1 : byAge) {
//                System.out.println(student1);
//            }
            //where
//            List<Student> student_where1 = mapper.getStudent_Where1(student);
//            for (Student student1 : student_where1) {
//                System.out.println(student1);
//            }
            //choose
//            Map<String,Object> map=new HashMap<>();
//            List<Student> student_choosewhenotherwise = mapper.getStudent_choosewhenotherwise(map);
//            for (Student student1 : student_choosewhenotherwise) {
//                System.out.println(student1);
//            }
            //foreach
//            Map<String,Object> map=new HashMap<>();
//            map.put("ages", Arrays.asList(22,23));
//            List<Student> studentForeach = mapper.getStudentForeach(map);
//            for (Student foreach : studentForeach) {
//                System.out.println(foreach);
//            }
            //trim查询
//            List<Student> studentByTrim = mapper.getStudentByTrim(student);
//            for (Student student1 : studentByTrim) {
//                System.out.println(student1);
//            }

            //trim增加
//            Map<String,Object> map=new HashMap<>();
//            map.put("name","li7");
//            mapper.insertByTrim(map);
            //set修改
            student.setId(4);
            student.setName("li8");
            mapper.updateStudent(student);
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            session.close();
        }
    }
}