javaweb--动态条件查询

动态sql

因为会出现三个条件只填其中一个或两个进行查询的情况，所以要使用动态sql语句进行查询

1、使用if进行条件判断，test：逻辑表达式

<select id="selectByid" resultType="brand">
    select * from tb_brand where id = #{id};
</select>

<select id="selectBycondition" resultType="brand">
    select * from tb_brand
    where 1 = 1
    <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>


</select>

2、where标签

<select id="selectBycondition" resultType="brand">
    select * from tb_brand
   <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>


使用choose标签
从多个条件中选择一个
choose：选择，类似switch
*占位符的变量名与封装类的变量名必须一致
sql映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace：名称空间。写接口给的全类名，相当于告诉MyBatis这个配置文件是实现哪个接口的。-->

<mapper namespace="com.avb.Mapper.BrandMapper">
    <!--resultType=""指定查询数据封装结果的时候使用自定义封装规则-->
    <select id="selectAll" resultType="brand">
        select * from tb_brand;
    </select>

    <select id="selectByid" resultType="brand">
        select * from tb_brand where id = #{id};
    </select>


    <select id="selectBycondition" resultType="brand">
        select * from tb_brand
        where
            <if test="status != null">
                status = #{status}
            </if>

            <if test="companyname != null and companyname !='' ">
                company_name like #{companyname}
            </if>

            <if test="brandname != null and brandname !='' ">
                brand_name like #{brandname};
            </if>



</select>

    <select id="selectByconditionSingle" resultType="brand">
        select * from tb_brand
        where
        <choose>
            <when test="status != null">
                status = #{status}
            </when>

            <when test="company_name != null and company_name !='' ">
                company_name like #{company_name}
            </when>

            <when test="brandname != null and brandname !='' ">
                brand_name like #{brandname};
            </when>
            <otherwise>1 = 1</otherwise>
        </choose>

    </select>

    <!--&lt;!&ndash;type指定为哪个javaBean自定义封装规则，id是唯一标识&ndash;&gt;
    <resultMap id="mycat" type="smq.javabean.Cat">
        &lt;!&ndash;id指定主键列的对应规则，column指定哪一列是主键列，property指定cat的哪个属性封装id这个列数据&ndash;&gt;
        <id column="id" property="id"/>
        &lt;!&ndash;普通列&ndash;&gt;
        <result column="cName" property="name"/>
        <result column="cgender" property="gender"/>
        <result column="cAge" property="age"/>
    </resultMap>-->

</mapper>

接口文件

package com.avb.Mapper;

import com.avb.pojo.Brand;
import com.avb.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface BrandMapper {
    //查询所有
    List<Brand> selectAll();
    //按照id查询
    Brand selectByid(int id);
    /*按照多种条件查询
    //List<Brand> selectBycondition(@Param("status")int status,@Param("company_name")String company_name,@Param("brand_name")String brand_name);
*/
    //按照类封装
    //List<Brand> selectBycondition(Brand brand);
    //使用Map
    List<Brand> selectBycondition(Map map);
    //单个条件查询
    List<Brand> selectByconditionSingle(Brand brand);


}

 操作文件

@Test
    public void testSelectByconditionSingle() throws IOException {
        //接收变量
        int status = 0;
        String companyname = "华为";
        //String brandname = "华为";
        String brandname = "";
        //处理数据
        //companyname = "%" + companyname + "%";
        brandname = "%" + brandname + "%";

        //封装类
        Brand brand = new Brand();
        brand.setCompany_name(companyname);
        //brand.setBrand_name(brandname);
       // brand.setStatus(status);
/*        Map map = new HashMap();
        map.put("status",status);
        map.put("companyname",companyname);
        map.put("brandname",brandname);*/
        //1、获取sqlSessionFactory
        String rescource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(rescource);
        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);
        //List<Brand> brands = brandMapper.selectBycondition(map);
        System.out.println(brands);
        //5、释放资源
        sqlSession.close();

    }