mybatis 动态sql标签

public interface AnimalDynamicMapper {
    //如果年龄为0，and性别为""或null,则不纳入查询条件
    List<Animal> findByAgeAndSexOptional(@Param("age") int age,@Param("sex") String sex);
    //如果年龄为0，或性别为""或null,则不纳入查询条件
    List<Animal> findByAgeOrSexOptional(@Param("age") int age,@Param("sex") String sex);

    //可选择修改，如果新的动物名字，性别非空，年龄，脚数非负，及
    int updateOption(Animal animal);
    //批量添加
    int addBatch(List<Animal> list);
}

<?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">

<!--namespace命名空间,namespace必须为长类名-->
<mapper namespace="com.wance.mapper.AnimalDynamicMapper">
    <!--where if-->
    <!--
    <select id="findByAgeAndSexOptional" resultType="com.wance.entity.Animal">
        select * from animal
        <where>
            <if test="age>0">
                age=#{age}
            </if>
            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </where>
    </select>
    -->
    <select id="findByAgeAndSexOptional" resultType="com.wance.entity.Animal">
        select * from animal
        <trim prefix="where" suffix="order by age desc" prefixOverrides="and">
            <if test="age>0">
                age=#{age}
            </if>
            <if test="sex!=null and sex!=''">
                and sex=#{sex}
            </if>
        </trim>
    </select>

    <select id="findByAgeOrSexOptional" resultType="com.wance.entity.Animal">
        select * from animal
        <where>
              <choose>
                  <when test="age>0">
                          and age=#{age}
                  </when>
                  <otherwise>
                      and sex=#{sex}
                  </otherwise>
              </choose>
        </where>
    </select>
<!--
    <update id="updateOption" parameterType="Animal">
          update animal
          <set>
              <if test="aname!=null and aname!=''">
                  aname=#{aname},
              </if>
              <if test="age>=0">
                 age=#{age},
              </if>
              <if test="sex!=null and sex!=''">
                  sex=#{sex},
              </if>
              <if test="feetCount>=0">
                  feetcount=#{feetCount}
              </if>
          </set>
        where aid=#{aid};
    </update>
    -->
    <!--trim实现-->
    <update id="updateOption" parameterType="Animal">

        <trim prefix="update animal set " suffixOverrides=",">
            <if test="aname!=null and aname!=''">
                aname=#{aname},
            </if>
            <if test="age>=0">
                age=#{age},
            </if>
            <if test="sex!=null and sex!=''">
                sex=#{sex},
            </if>
            <if test="feetCount>=0">
                feetcount=#{feetCount},
            </if>
        </trim>
        where aid=#{aid};
    </update>

    <insert id="addBatch">
        insert into animal(aname,age,sex,feetCount)values
      <foreach collection="list" item="list" separator=",">
          (#{list.aname},#{list.age},#{list.sex},#{list.feetCount})
      </foreach>
    </insert>
</mapper>

实现类：

public class AnimalDynamicMapperTest {
    private static SqlSessionFactory factory=null;
    @BeforeClass
    public static void init(){
        try {
            //1 读取配置文件
            InputStream in= Resources.getResourceAsStream("mybatis-config.xml");
            //2 创建SqlSessionFactory工厂
            SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
            factory=builder.build(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    @Test
    public void findByAgeAndSexOptional() {
        //3 使用工厂生产SqlSession对象
        SqlSession session=factory.openSession();
        AnimalDynamicMapper animalMapper=session.getMapper(AnimalDynamicMapper.class);
        List<Animal> list=animalMapper.findByAgeAndSexOptional(16,"man");
        System.out.println(list);
    }

    @Test
    public void findByAgeOrSexOptional() {
        //3 使用工厂生产SqlSession对象
        SqlSession session=factory.openSession();
        AnimalDynamicMapper animalMapper=session.getMapper(AnimalDynamicMapper.class);
        List<Animal> list=animalMapper.findByAgeOrSexOptional(16,"man");
        System.out.println(list);
    }

    @Test
    public void updateOption() {
        //3 使用工厂生产SqlSession对象
        SqlSession session=factory.openSession();
        AnimalDynamicMapper animalMapper=session.getMapper(AnimalDynamicMapper.class);
        Animal animal=new Animal();
        animal.setAid(5);
        animal.setAname("admin");
        animal.setAge(50);
        animal.setFeetCount(90);
        animalMapper.updateOption(animal);
        session.commit();
    }

    @Test
    public void addBatch() {
        //3 使用工厂生产SqlSession对象
        SqlSession session=factory.openSession();
        AnimalDynamicMapper animalMapper=session.getMapper(AnimalDynamicMapper.class);
        Animal animal=new Animal(-1,"root",12,"woman",6);
        Animal animal2=new Animal(-1,"king",75,"woman",9);
        List<Animal> list=new ArrayList<>();
        list.add(animal);
        list.add(animal2);
        animalMapper.addBatch(list);
        session.commit();
    }