20220629_MyBatis_数据库增删改查练习(二)

本篇为动态sql查询
当用户输入的信息只提供一个条件时，使用动态sql查询
在映射文件里，直接使用if和where标签实现动态

    <select id="selByCond" resultMap="brand_resultMap">
        select *
        from 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>

执行代码中用map集合，只传入中间一个条件

 @Test
    public void selByCond() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqs = sqlSessionFactory.openSession();

        BrandMapper mapper = sqs.getMapper(BrandMapper.class);
        /**
         * 本次将参数提前写在前面
         * 因为条件有like模糊查询，所以参数也要模糊处理
         */
        int status = 1;
        String companyName = "huawei";
        String brandName = "huawei";
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";

        /**
         * 封装成对象或map集合的方式传入参数
         */
        Brand br = new Brand();
        br.setStatus(status);
        br.setCompanyName(companyName);
        br.setBrandName(brandName);

        Map ma = new HashMap<>();
//        ma.put("status",status);
        ma.put("companyName",companyName);
//        ma.put("brandName",brandName);

        /**三种方法都可以*/
//        List<Brand> rs = mapper.selByCond(status, companyName, brandName);
//        List<Brand> rs = mapper.selByCond(br);
        List<Brand> rs = mapper.selByCond(ma);

        for (Brand r : rs) {
            System.out.println(r);
        }

        System.out.println();

        sqs.close();

    }

结果中看到预编译语句语法正确，mybatis的标签有自动修正语法的功能

接下来做单条件的动态sql查询
先在代理接口里添加方法

List<Brand> selBySingleCond(Brand brand);

在sql映射文件里添加单条件查询的动态sql语句
本例可用otherwise标签，或where标签包裹，解决一个参数中一个条件都不传入的问题

    </select>
    <select id="selBySingleCond" resultMap="brand_resultMap">
        select *
        from 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>
                <!--<otherwise>
                    where 1 = 1
                </otherwise>-->
            </choose>
        </where>

    </select>

执行代码

    @Test
    public void selBySingleCond() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqs = sqlSessionFactory.openSession();

        BrandMapper mapper = sqs.getMapper(BrandMapper.class);

        int status = 1;
        String companyName = "huawei";
        String brandName = "huawei";
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";

        Brand br = new Brand();
//        br.setStatus(status);
//        br.setCompanyName(companyName);
//        br.setBrandName(brandName);

        List<Brand> rs = mapper.selBySingleCond(br);

        for (Brand r : rs) {
            System.out.println(r);
        }

        System.out.println();

        sqs.close();

    }

在运行结果中观察预编译的sql语句，看otherwise和where标签的效果，略