20220629

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

    
        select *
        from brand
        
            
                and status = #{status}
            
            
                and company_name like #{companyName}
            
            
                and brand_name like #{brandName}
            
        
    

执行代码中用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);
        
        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);

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

        
//        List rs = mapper.selByCond(status, companyName, brandName);
//        List rs = mapper.selByCond(br);
        List rs = mapper.selByCond(ma);

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

        System.out.println();

        sqs.close();

    }

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

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

List selBySingleCond(Brand brand);

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

    
    
        select *
        from brand
        
            
                
                    status = #{status}
                
                
                    company_name like #{companyName}
                
                
                    brand_name like #{brandName}
                
                
            
        

    

执行代码

    @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 rs = mapper.selBySingleCond(br);

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

        System.out.println();

        sqs.close();

    }

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