博客

用户输入条件时，是否所有条件都会填写?

查询-多条件-动态条件查询

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

<select id="selectByCondition" resultMap=""brandResultMap">
    select *
    from tb_brandwhere
    status = #{status}
    and company_name like #{companyName)
    and brand_name like #{brandName}
</select>

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

if

choose (when,otherwise)

trim (where,set)

foreach

在BrandMapper.java的接口文件中添加

   List<Brand> selectByChangedCondition1(Map map);

在BrandMapper.xml文件中添加

<select id="selectByChangedCondition1" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <if test="status != null">
                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>

在MyBatisTest.java文件中添加

@Test
    public void testselectByChangedCondition1()throws Exception{

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

        //处理

        companyName = "%"+companyName+"%";
        brandName = "%"+brandName+"%";

        //封装对象
        Map map = new HashMap();
        map.put("status" ,status);
        map.put("companyName" ,companyName);
        //map.put("brandName" ,brandName);


        //1.加载mybatis的核心配置文件，获取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.selectByChangedCondition1(map);
        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }

小结

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

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

解决方案:

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

2) <where>标签替换where关键字

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

从多个条件中选择一个

choose (when, otherwise):选拯，类似于Java中的 switch 语句

在BrandMapper.java的接口文件中添加

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

在BrandMapper.xml文件中添加

    <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>

在MyBatisTest.java文件中添加

@Test
    public void testselectByConditionSingle()throws Exception{

        //接收id
        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.加载mybatis的核心配置文件，获取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.selectByConditionSingle(brand);
        System.out.println(brands);

        //5.释放资源
        sqlSession.close();
    }