MyBatis动态查询

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

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

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

   if

   choose(when,otherwise)

   trim(where,set)

   foreach

   在BrandMapper.xml下面添加

   
   ?
   <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）标签替换关键字

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

   从多个条件中选择一个

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

   在BrandMapper类里添加方法

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

   在BrandMapper.xml下面编写

   
   ?
   
   
   
   
   
   
   
   ?
   
   
   
   ?
   
   
   
   
   
   
   
   ?
   
   ?
       <select id="selectByConditionSingle" resultMap="brandResultMap">
   ?
           select *
           from tb_brand
           <where>
           <choose>
               <when test="status != null">
                   status = #{status}
               when>
   ?
               <when test="companyName != null and companyName != '' ">
                   company_name like #{companyName}
               when>
   ?
               <when test="brandName != null and brandName != ''">
                   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 brands = brandMapper.selectByCondition(status, companyName, brandName);
    //List brands = brandMapper.selectByCondition(brand);
    List brands = brandMapper.selectByConditionSingle(brand);
    System.out.println(brands);
?
    //5.释放资源
    sqlSession.close();
?
}