前言:近日遇到很复杂的业务逻辑需要处理(每每这个时候博主经常吐槽自己脑子不够用了?),数据库查询需要通过不同业务不同条件进行动态拼接,而且涉及数据量巨大,表也关联的多,这时候明显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="+