MyBatis 六--动态条件查询

查询多条件——动态查询

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

　　修改SQL语句即可，有两种方法：

　　　　　　　　首先利用if 标签来进行判断，where 后面跟恒等式或者利用where标签。

 

 

 

 

 

 

 

 单条件的动态查询：

　　　　利用choose(when,otherwise)来选择

　　

//单条件动态查询
    @Test
    public  void test_SelectByConditionSingle() throws IOException {

        int id = 1;
        int status =1;
        String companyName = "华为";
        String brandName = "华为";


        //处理参数
        companyName = "%"+companyName+"%";
        brandName = "%"+brandName+"%";



        //封装对象——方法2
        Brand brand = new Brand();
       // brand.setStatus(status);
      //  brand.setBrandName(brandName);
        // brand.setCompanyName(companyName);


        //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();
    }

SQL语句编写两种方法

<!--单条件查询-->
 <!--   <select id="selectByConditionSingle" resultMap="barandResultMap">
    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="barandResultMap">
        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>