六、Mybatis的动态SQL查询之if、choose标记（一）

一、if标记
1、建立select

<select id="selectReaderMoney" resultType="jikeReader"             parameterType="jikeReader">
        select * from reader 
            where 1=1
            <if test="money!=null">
                and money>#{money}
            </if>
</select>

第一个和第三个money是传入类对象的money属性，与之字段名相同
第二个money是数据库的money
配置文件代码

<?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="/">
    <select id="selectReaderMoney" resultType="jikeReader" parameterType="jikeReader">
        select *from reader
          where 1=1
          <if test="money!=null">
              and money > #{money}
          </if>
    </select>

</mapper>

测试代码

@Test
    public void testIf(){
        String source = "jike/book/map/MyBatisConfig.xml";
        Reader reader =null;
        SqlSession session = null;
        try {
            reader = Resources.getResourceAsReader(source);
        } catch (IOException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }

        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);

        session = sessionFactory.openSession(false);


        try {

            JiKeReader oneJiKeReader = new JiKeReader();
            //oneJiKeReader.setMoney(10);

            System.out.println(session.selectList("selectReaderMoney", oneJiKeReader).size());
            session.commit();
        } catch (Exception e) {
            session.rollback();
        }finally {
            session.close();
        }
    }

二、choose多路判断
1、建立select，判断用户名，如果不存在就判断id，最后判断密码不为空

        select * from jikeuser where 1=1
    <choose>
           <when test="userName!=null">
               and userName like #{userName}
           </when>
            ……        
      <otherwise>
               and password is not null
          </otherwise>
    </choose> 

map配置文件内容

<?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="/">
    <select id="selectReaderMoney" resultType="jikeReader" parameterType="jikeReader">
        select *from reader
          where 1=1
          <if test="money!=null">
              and money > #{money}
          </if>
    </select>



    <select id="manytiaojian" resultType="jikeReader" parameterType="jikeReader">
        select *from jikeuser
            where 1=1
            <choose>
              <when test="username!=null">
                    and userName like #{username}
              </when>

             <when test="id!=null">
                    and id = #{id}
              </when>
              <otherwise>
                    and password is not null 
              </otherwise>

            </choose>


    </select>


</mapper>

测试文件的代码

@Test
    public void testChoose(){
        System.out.println("查询");   
        String source = "jike/book/map/MyBatisConfig.xml";
        Reader reader =null;
        SqlSession session = null;
        try {
            reader = Resources.getResourceAsReader(source);
        } catch (IOException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        SqlSessionFactory sessionFactory = new                       SqlSessionFactoryBuilder().build(reader);
        session = sessionFactory.openSession(false);
        try {
            JiKeUser user = new JiKeUser();
            user.setUsername("%文%");
            System.out.println(session.selectList("manytiaojian", user).size());
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
        }
    }