MyBatis动态查询

1. 1. 查询-多条件-动态条件查询：

      SQL语句会随着用户的输入或外部条件的变化而变化，我们称为动态SQL。

      MyBatis对动态SQL有很强大的支撑：

      if

      choose(when,otherwise)

      trim(where,set)

      foreach

      在BrandMapper.xml下面添加

      <!--
          动态条件查询
              * if:条件判断
                  * test：逻辑表达式
              * 问题：
                  * 恒等式
                  * <where> 替换 where 关键字
      -->
      ​
      <select id="selectByCondition" resultMap="brandResultMap">
          select *
          from tb_brand
          /* where 1 = 1 */
          <where>
                <if test="status != null">
                    and status = #{status}
                </if>
                <if test="companyName != null and companyName != '' ">
                    and company_name like #{companyName}
                </if>
                <if test="brandName != null and brandName != '' ">
                    and brand_name like #{brandName};
                </if>
          </where>
      ​
      </select>

       

      总结动态SQL：

      if：用于判断参数是否有值，使用test属性进行条件判断

      *存在问题：第一个条件不需要逻辑运算符

      解决方法：

      （1）使用恒等式让所有条件格式都一样

      （2）<where>标签替换关键字

      查询-单条件-动态条件查询：

      从多个条件中选择一个

      choose(when,where)：选择，类似于Java中的switch语句，when相当于case语句，otherwisee相当于default语句

      在BrandMapper类里添加方法

      /**
       * 单条件动态查询
       * @param brand
       * @return
       */
      List<Brand> selectByConditionSingle(Brand brand);

       

      在BrandMapper.xml下面编写

      <!--    <select id="selectByConditionSingle" resultMap="brandResultMap">-->
      ​
      <!--        select *-->
      <!--        from tb_brand-->
      <!--        where-->
      <!--        <choose>&lt;!&ndash; 相当于switch &ndash;&gt;-->
      <!--            <when test="status != null">&lt;!&ndash; 相当于case &ndash;&gt;-->
      <!--                status = #{status}-->
      <!--            </when>-->
      ​
      <!--            <when test="companyName != null and companyName != '' ">&lt;!&ndash; 相当于case &ndash;&gt;-->
      <!--                company_name like #{companyName}-->
      <!--            </when>-->
      ​
      <!--            <when test="brandName != null and brandName != ''">&lt;!&ndash; 相当于case &ndash;&gt;-->
      <!--                brand_name like #{brandName};-->
      <!--            </when>-->
      <!--            <otherwise>-->
      <!--                1 = 1-->
      <!--            </otherwise>-->
      <!--        </choose>-->
      ​
      <!--    </select>-->
      ​
          <select id="selectByConditionSingle" resultMap="brandResultMap">
      ​
              select *
              from tb_brand
              <where>
              <choose><!-- 相当于switch -->
                  <when test="status != null"><!-- 相当于case -->
                      status = #{status}
                  </when>
      ​
                  <when test="companyName != null and companyName != '' "><!-- 相当于case -->
                      company_name like #{companyName}
                  </when>
      ​
                  <when test="brandName != null and brandName != ''"><!-- 相当于case -->
                      brand_name like #{brandName};
                  </when>
      ​
              </choose>
              </where>
          </select>

       

   在test类中添加代码

   @Test
   public void testSelectByConditionSingle() throws IOException {
   ​
       //接收参数
       int status = 1;
       String companyName = "华为";
       String brandName = "华为";
   ​
       //处理参数
       companyName = "%" + companyName + "%";
       brandName = "%" + brandName + "%";
   ​
       //封装对象
       Brand brand = new Brand();
       //brand.setStatus(status);
       brand.setCompanyName(companyName);
       //brand.setBrandName(brandName);
   ​
   ​
   ​
       //1.获取SqlSessionFactory
       String resource = "mybatis-config.xml";
       InputStream inputStream = Resources.getResourceAsStream(resource);
       SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
   ​
       //2.获取SqlSession对象
       SqlSession sqlSession = sqlSessionFactory.openSession();
   ​
       //3.获取Mapper接口的代理对象
       BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
   ​
       //4.执行方法
       //List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
       //List<Brand> brands = brandMapper.selectByCondition(brand);
       List<Brand> brands = brandMapper.selectByConditionSingle(brand);
       System.out.println(brands);
   ​
       //5.释放资源
       sqlSession.close();
   ​
   }