Mybatis自定义Sql

绝地灬酷狼 2024-04-17 05:26 30阅读 0赞

**前言**：近日遇到很复杂的业务逻辑需要处理（每每这个时候博主经常吐槽自己脑子不够用了?），数据库查询需要通过不同业务不同条件进行动态拼接，而且涉及数据量巨大，表也关联的多，这时候明显xml不适合这个场景，虽然说可以做到，但是麻烦程度绝对大于我接下来的做法，然后不经意间发现下面这种写法，发现Mybatis挺灵活的。有①xml方式(常用)、②普通注解（@Select、@Insert等），③高级注解（@SelectProvider、@InsertProvider等）④SQL语句构建器类http://www.mybatis.org/mybatis-3/zh/statement-builders.html  
…还有其它的方式等我去研究发现嘿嘿，言归正传。

假设需求根据给出的条件（此条件不固定），然后关联几张表，给我查出我需要的字段（视图）。看似很简单的需求，实则深似海哈哈哈

**下面贴出代码（看不太懂的，下面有解释说明）**

/** * 根据移库规则表检查该数据是否可自动生成任务 * * @param warehouseTransferRuleConfig * @return */
    @SelectProvider(type = WarehouseTransferRuleConfigMapperProvider.class, method = "canAutoCreateSourceDetailList")
    List<SourceDetail> canAutoCreateSourceDetailList(WarehouseTransferRuleConfig warehouseTransferRuleConfig,String deviceTypeCodeById,String specById);
    
    class WarehouseTransferRuleConfigMapperProvider { 
    
       /** * 用于复杂sql查询提供sql拼接 * * @param * @return */
       public String canAutoCreateSourceDetailList(WarehouseTransferRuleConfig warehouseTransferRuleConfig,String deviceTypeCodeById,String specById) { 
           //根据规则表数据拼接查询一机一档的sql
    
           StringBuilder sql = new StringBuilder("select material_number,vehicle_vin_number,device_code,device_type_code,bl_division_code,region_code,device_model_code,device_spec_code,depositary_officer,storage_area,storage_province,storage_city,storage_address,placement_note,dispose_status,first_handover_date,handover_address_type from(select material_number,vehicle_vin_number,device_code,device_type_code,bl_division_code,region_code,device_model_code,device_spec_code,depositary_officer,storage_area,storage_province,storage_city,storage_address,placement_note,dispose_status,first_handover_date,handover_address_type from rmp_source_detail s where first_handover_status='Y' and depositary_officer is not null and handover_address_type is not null and bl_division_code is not null and device_spec_code is not null and dispose_status='01' and is_active='Y'");
           if (warehouseTransferRuleConfig != null) { 
               //当前日期-定价日期＞7
               sql.append(" and to_days(now()) - to_days(device_pricing_date)>7");
               //设备规格
               if (StringUtils.isNotBlank(specById)) { 
                   String specStr="";
                   String[] split = specById.split(",");
                   for (int i = 0; i < split.length; i++) { 
                       specStr=specStr+"'"+split[i]+"',";
                   }
                   specStr=specStr+specStr.substring(0,specStr.length()-1);
                   sql.append(" and device_spec_code in(" + specStr + ")");
               }
               //如未配置设备规格，则以设备类型为条件
               if (StringUtils.isBlank(specById)) { 
                   String deviceTypeCodeSql="(select ds.device_type_code from(select device_type_code from rmp_warehouse_transfer_spec_config where id="+warehouseTransferRuleConfig.getRuleCode()+" and is_active='Y' limit 1)as ds)";
                   sql.append(" and device_type_code in(" + deviceTypeCodeSql + ")");
               }
               //过户判断
               if (warehouseTransferRuleConfig.getTransferJudge() != null) { 
                   sql.append(" and pass_judge=" + warehouseTransferRuleConfig.getTransferJudge());
               }
               //存放地类型
               if (warehouseTransferRuleConfig.getHandoverAddressType() != null) { 
                   sql.append(" and handover_address_type in(" + warehouseTransferRuleConfig.getHandoverAddressType() + ")");
               }
               sql=sql.append(") AS ts ");
    
               //获取预计整修成本的sql(不能为0)
               String estimateRepairCostsSql = "(select if(repair_budget='0',null,repair_budget)repair_budget from rmp_source_detail where material_number=ts.material_number)";
               //获取市场价的sql(不能为0)
               String marketPriceSql = "(select if(market_price='0',null,market_price)market_price from rmp3_price_adjustment_history_sheet where material_number=ts.material_number ORDER BY create_date DESC LIMIT 1)";
    
               sql.append(" where ts.material_number in (select material_number from rmp_source_detail where 1=1 ");
               //预计整修成本/市场全款价（区间始%）
               if (warehouseTransferRuleConfig.getIntervalValueStart() != null) { 
                   sql.append(" and repair_budget/" + estimateRepairCostsSql + ">" + (warehouseTransferRuleConfig.getIntervalValueStart() / 100));
               }
               //预计整修成本/市场全款价（区间止%）
               if (warehouseTransferRuleConfig.getIntervalValueEnd() != null) { 
                   sql.append(" and repair_budget/" + marketPriceSql + "<=" + (warehouseTransferRuleConfig.getIntervalValueEnd() / 100));
               }
    
    
               //条件（and or）
               if (StringUtils.isNotBlank(warehouseTransferRuleConfig.getConditions())) { 
                   sql.append(" " + warehouseTransferRuleConfig.getConditions() + " ");
               }
    
               if (warehouseTransferRuleConfig.getRepairCostsStart() != null || warehouseTransferRuleConfig.getRepairCostsEnd() != null) { 
                   sql.append("(");
    
                   //预计整修成本始（万）
                   if (warehouseTransferRuleConfig.getRepairCostsStart() != null) { 
                       sql.append(" repair_budget>" + warehouseTransferRuleConfig.getRepairCostsStart());
                   }
                   //预计整修成本止（万）
                   if (warehouseTransferRuleConfig.getRepairCostsEnd() != null && warehouseTransferRuleConfig.getRepairCostsStart() != null) { 
                       sql.append(" and repair_budget<=" + warehouseTransferRuleConfig.getRepairCostsEnd());
                   }
                   if (warehouseTransferRuleConfig.getRepairCostsEnd() != null && warehouseTransferRuleConfig.getRepairCostsStart() == null) { 
                       sql.append(" repair_budget<=" + warehouseTransferRuleConfig.getRepairCostsEnd());
                   }
    
                   sql.append(")");
               }
    
               sql.append(")");
    
               System.out.println("打印sql=" + sql.toString());
    
           }
           return sql.toString();
       }
    }

后台打印的sql语句：

SELECT
    	material_number,
    	vehicle_vin_number,
    	device_code,
    	device_type_code,
    	bl_division_code,
    	region_code,
    	device_model_code,
    	device_spec_code,
    	depositary_officer,
    	storage_area,
    	storage_province,
    	storage_city,
    	storage_address,
    	placement_note,
    	dispose_status,
    	first_handover_date,
    	handover_address_type 
    FROM
    	(
    	SELECT
    		material_number,
    		vehicle_vin_number,
    		device_code,
    		device_type_code,
    		bl_division_code,
    		region_code,
    		device_model_code,
    		device_spec_code,
    		depositary_officer,
    		storage_area,
    		storage_province,
    		storage_city,
    		storage_address,
    		placement_note,
    		dispose_status,
    		first_handover_date,
    		handover_address_type 
    	FROM
    		rmp_source_detail s 
    	WHERE
    		first_handover_status = 'Y' 
    		AND depositary_officer IS NOT NULL 
    		AND handover_address_type IS NOT NULL 
    		AND bl_division_code IS NOT NULL 
    		AND device_spec_code IS NOT NULL 
    		AND dispose_status = '01' 
    		AND is_active = 'Y' 
    		AND to_days(
    		now()) - to_days( device_pricing_date )> 7 
    		AND device_spec_code IN (
    			'50米-3桥-奔驰-国三',
    			'50米-4桥-五十铃-国三',
    			'50米-4桥-奔驰-国三',
    			'50米-4桥-日野-国三',
    			'52米-3桥-奔驰-国五',
    			'52米-4桥-五十铃-国三',
    			'52米-4桥-奔驰-国三',
    			'52米-4桥-奔驰-国四',
    			'52米-4桥-斯堪尼亚-国三',
    			'52米-4桥-日野-国三',
    			'53米-4桥-奔驰-国五',
    			'53米-4桥-斯堪尼亚-国五',
    			'54米-4桥-斯堪尼亚-国三',
    			'56米-4桥-奔驰-国三',
    			'56米-4桥-奔驰-国五',
    			'56米-4桥-奔驰-国四',
    			'56米-4桥-斯堪尼亚-国三',
    			'56米-4桥-斯堪尼亚-国五',
    			'56米-4桥-斯堪尼亚-国四',
    			'56米-4桥-解放-国五',
    			'56米-碳纤维-3桥-奔驰-国四',
    			'58米-5桥-斯堪尼亚-国三',
    			'58米-5桥-斯堪尼亚-国五',
    			'50米-3桥-奔驰-国三',
    			'50米-4桥-五十铃-国三',
    			'50米-4桥-奔驰-国三',
    			'50米-4桥-日野-国三',
    			'52米-3桥-奔驰-国五',
    			'52米-4桥-五十铃-国三',
    			'52米-4桥-奔驰-国三',
    			'52米-4桥-奔驰-国四',
    			'52米-4桥-斯堪尼亚-国三',
    			'52米-4桥-日野-国三',
    			'53米-4桥-奔驰-国五',
    			'53米-4桥-斯堪尼亚-国五',
    			'54米-4桥-斯堪尼亚-国三',
    			'56米-4桥-奔驰-国三',
    			'56米-4桥-奔驰-国五',
    			'56米-4桥-奔驰-国四',
    			'56米-4桥-斯堪尼亚-国三',
    			'56米-4桥-斯堪尼亚-国五',
    			'56米-4桥-斯堪尼亚-国四',
    			'56米-4桥-解放-国五',
    			'56米-碳纤维-3桥-奔驰-国四',
    			'58米-5桥-斯堪尼亚-国三',
    			'58米-5桥-斯堪尼亚-国五' 
    		) 
    	AND handover_address_type IN ( 1, 2, 4 )) AS ts 
    WHERE
    	ts.material_number IN (
    	SELECT
    		material_number 
    	FROM
    		rmp_source_detail 
    	WHERE
    		1 = 1 
    		AND repair_budget /(
    		SELECT
    		IF
    			( market_price = '0', NULL, market_price ) market_price 
    		FROM
    			rmp3_price_adjustment_history_sheet 
    		WHERE
    			material_number = ts.material_number 
    		ORDER BY
    			create_date DESC 
    			LIMIT 1 
    		)<= 0.3 
    	AND ( repair_budget <= NULL ))

看到打印的sql大家应该也遇到同样的需求，需要负责的查询SQL或者新增的SQL吧，都可以采用上述方式?

## 解释说明和注意事项： ##

**1)为什么不推荐这个时候使用xml呢？其实xml也可以达到效果，但是灵活性不高，例如for循环里面切割字符再次拼接，而且编辑sql时容易出错，项目改动xml文件还要重新启动，而重新加载类可以不用重新启动，搭配Jrebel热加载使用，不知道热加载的可以参考这篇文章[https://blog.csdn.net/zeal9s/article/details/84204812][https_blog.csdn.net_zeal9s_article_details_84204812]  
虽然说debug也可以做到实时部署，但是不稳定，而且遇到springboot项目，还要添加依赖才能实现，种种综合来看xml方式在此不适合**

2）

> @SelectProvider提供自定义sql的注解  
> type：自定义SQL的类型（在这里就是提供查询的那个内部类WarehouseTransferRuleConfigMapperProvider）  
> method:该类的具体哪个方法提供sql

**3）注解下面一行代表你需要的数据（数据字段不确定，可以用Map、List或者自定义Dto层接收）和参数（参数不确定建议用Map接受，或者List等，如多个参数，可在参数前面加@param注解，例如@param String sex,@param int age）但是我的没加也能正常运作，可不加**

List<SourceDetail> canAutoCreateSourceDetailList(WarehouseTransferRuleConfig warehouseTransferRuleConfig,String deviceTypeCodeById,String specById);

**4）下面的类是Mapper层的内部类，注意 在此类不能注入其它mapper或者service对象，会导致注入对象失败（已踩坑，具体原因尚且不知道），这也意味着所有的sql和逻辑层，必须在此类完成**

class WarehouseTransferRuleConfigMapperProvider {
     ....
     }

**5）内部类中的方法名必须和注解@SelectProvider的说明保持一致，不然引用失败**

**6）避免sql出现拼写错误，最好初始化一个简单查询写在最前面和最后面，。。。。。。  
处再去处理复杂SQL拼接，例如**

StringBuilder sql = new StringBuilder("select material_number,vehicle_vin_number,device_code,device_type_code,bl_division_code,region_code,device_model_code,device_spec_code,depositary_officer,storage_area,storage_province,storage_city,storage_address,placement_note,dispose_status,first_handover_date,handover_address_type from(select material_number,vehicle_vin_number,device_code,device_type_code,bl_division_code,region_code,device_model_code,device_spec_code,depositary_officer,storage_area,storage_province,storage_city,storage_address,placement_note,dispose_status,first_handover_date,handover_address_type from rmp_source_detail s where first_handover_status='Y' and depositary_officer is not null and handover_address_type is not null and bl_division_code is not null and device_spec_code is not null and dispose_status='01' and is_active='Y'");
    。。。。。。
    sql = sql.append(")");

**7）这里采用StringBuilder是提高拼接效率，也可以使用String方式的+=或者concat拼接**

**8）最后最好打印一下sql，方便排查sql错误可复制到数据库运行**

**9）纪念多次子查询博主踩的坑**  
**①子查询获取最外层查询的值：可以将最外层的查询再包裹一层，给这个视图取个别名，子查询使用别名.字段的方式获取，如果直接获取最外层数据你要想想sql的执行顺序哦)  
②子查询不能使用limit关键字查询（本来是想用limit提高查询效率，sql本身没错不料执行出错，解决方法请看[https://blog.csdn.net/zeal9s/article/details/99739003][https_blog.csdn.net_zeal9s_article_details_99739003]）  
③使用关键词in查询时，出现数据偏差，原因是拼接这个sql时**

AND device_spec_code IN (
    			'50米-3桥-奔驰-国三',
    			'50米-4桥-五十铃-国三',
    			'50米-4桥-奔驰-国三',
    			'50米-4桥-日野-国三',
    			'52米-3桥-奔驰-国五',
    		)

**，java代码是**

sql = sql.append(" and device_spec_code in(" + specStr + ")");

**specStr作为字符串变量，它的值是没有加上引号的，所以拼接的时候要重新加上引号，不加引号sql不会报错，但是数据不对**

50米-3桥-奔驰-国三,50米-4桥-五十铃-国三

**④拼接字符串时一定要注意变量重新赋值，也可以采用concat和StringBuilder的append方法（不用重新赋值）**

specStr=specStr+specStr.substring(0,specStr.length()-1);  ✔️
    
    specStr.substring(0,specStr.length()-1); ❌

**10）每一次拼接的注释最好写上，方便维护。防止几天之后，自己都不知道这写的什么，又要重新浪费时间阅读代码**

**12）除了查询@SelectProvider还有@InsertProvider等用法可研究**

**11）技术多多实践啦，把业务和技术结合起来才能解决实际问题哦~**

**说在最后的话：编写实属不易，若喜欢或者对你有帮助记得点赞+关注或者收藏哦~**

[https_blog.csdn.net_zeal9s_article_details_84204812]: https://blog.csdn.net/zeal9s/article/details/84204812
[https_blog.csdn.net_zeal9s_article_details_99739003]: https://blog.csdn.net/zeal9s/article/details/99739003