19.Mybatis之动态SQL

　　测试

@Test
    public void test1() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession();
        UserMapper dao = session.getMapper(UserMapper.class);
        User user = new User();
        user.setName("王五");
        user.setId(2);
        user.setAge(18);
        List<User> list = dao.query(user);
        for (User user2 : list) {
            System.out.println(user2);
        }
        session.close();
    }

　　结果

2.where语句

<select id="query1" resultType="user" parameterType="user">
          select
              *
          from
              t_user
          <where>
              <if test="name!=null">
                  and name=#{name}
              </if>
              <if test="age>0">
                  and age=#{age}
              </if>
              <if test="id!=null and id>0">
                  and id=#{id}
              </if>
          </where>
      </select>

　　测试

@Test
    public void test2() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession();
        UserMapper dao = session.getMapper(UserMapper.class);
        User user = new User();
//        user.setName("王五");
//        user.setId(2);
//        user.setAge(18);
        List<User> list = dao.query1(user);
        for (User user2 : list) {
            System.out.println(user2);
        }
        session.close();
    }

　　结果

3.choose语句

<!-- 首先判断name是否为空 如果不为空根据name查询 结束
          如果name为空 判断age是否传达 ，不为空大于0根据age查询
          否则根据id倒叙
       -->
      <select id="query2" resultType="user" parameterType="user">
          select
              *
          from
              t_user
          where 1=1
          <choose>
              <when test="name!=null">
                  and name=#{name}
              </when>
              <when test="age!=null and age>0">
                  and age=#{age}
              </when>
              <otherwise>
                  order by desc
              </otherwise>
          </choose>
      </select>

　　测试

@Test
    public void test3() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession(true);
        UserMapper dao = session.getMapper(UserMapper.class);
        User user = new User();
//        user.setName("王五");
//        user.setAge(18);
        List<User> list = dao.query2(user);
        for (User user2 : list) {
            System.out.println(user2);
        }
        session.close();
    }

　　结果

4.set语句

<!-- set标签会截取最后一个"," -->
      <update id="updateUser2" parameterType="user">
          update
              t_user
          <set>
              <if test="name!=null">
                  name=#{name},
              </if>
              <if test="age!=null and age>0">
                  age=#{age},
              </if>
          </set>
          where
              id=#{id}
      </update>

　　测试

@Test
    public void test4() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession(true);
        UserMapper dao = session.getMapper(UserMapper.class);
        User user = new User();
        user.setId(3);
        user.setName("渣渣");
        user.setAge(1);
        dao.updateUser2(user);
        session.close();
    }

　　结果

5.trim语句

　　trim标记是一个格式化的标记，可以完成set或者是where标记的功能

<select id="query3" resultType="user" parameterType="user">
          select
              *
          from
              t_user
          <trim prefix="where" prefixOverrides="and">
              <if test="name!=null">
                  and name=#{name}
              </if>
              <if test="age>0">
                  and age=#{age}
              </if>
              <if test="id!=null and id>0">
                  and id=#{id}
              </if>
          </trim>
      </select>
      

　　测试

@Test
    public void test5() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession();
        UserMapper dao = session.getMapper(UserMapper.class);
        User user = new User();
//        user.setName("王五");
//        user.setId(2);
//        user.setAge(18);
        List<User> list = dao.query3(user);
        for (User user2 : list) {
            System.out.println(user2);
        }
        session.close();
    }
    

　　结果

6.update语句

<update id="updateUser3" parameterType="user">
          update
              t_user
          <trim prefix="set" suffixOverrides=",">
              <if test="name!=null">
                  name=#{name},
              </if>
              <if test="age!=null and age>0">
                  age=#{age},
              </if>
          </trim>
          where
              id=#{id}
      </update>

　　测试

@Test
    public void test6() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession(true);
        UserMapper dao = session.getMapper(UserMapper.class);
        User user = new User();
        user.setName("花花");
        user.setId(5);
//        user.setAge(1);
        Integer count = dao.updateUser3(user);
        System.out.println(count);
        session.close();
    }

　　结果

7.sql块

　　sql片段一般用来定义sql中的列

<sql id="baseSql">
          id,name,age
      </sql>
      
      <select id="query4" resultType="user" parameterType="user">
          select
              <include refid="baseSql"></include>
          from
              t_user
          <trim prefix="where" prefixOverrides="and">
              <if test="name!=null">
                  and name=#{name}
              </if>
              <if test="age>0">
                  and age=#{age}
              </if>
              <if test="id!=null and id>0">
                  and id=#{id}
              </if>
          </trim>
      </select>

　　测试

@Test
    public void test7() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-cfg.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession();
        UserMapper dao = session.getMapper(UserMapper.class);
        User user = new User();
//        user.setName("王五");
//        user.setId(2);
//        user.setAge(18);
        List<User> list = dao.query4(user);
        for (User user2 : list) {
            System.out.println(user2);
        }
        session.close();
    }

　　结果

19.Mybatis之动态SQL

标签：col   部分   tst   odi   bsp   close   NPU   一半   逗号