Mybatis复杂sql写法：注解+内部类来实现

浅浅的花香味﹌ 2022-05-12 01:06 165阅读 0赞

代码如下：

@Mapper
    @Component
    public interface DynamicDataMapper { 
    
        /** * 根据名称查询动态数据结果 * @param dataConditionBO 筛选条件 * @return */
        @SelectProvider(type = DynamicDataDaoProvider.class, method = "findRobotDynamicDataByCondition")
        RobotDataResultDO  findRobotDynamicData(DataConditionBO dataConditionBO);
    
        class DynamicDataDaoProvider {
            public String findRobotDynamicDataByCondition(DataConditionBO dataConditionBO) {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" SELECT `rdd`.`id`,`rdd`.`name`, `rdd`.`value`,GROUP_CONCAT(\"{\",w_key,\":\",w_value,\"}\") as `condition` FROM `robot_dynamic_data` AS `rdd` JOIN `robot_dynamic_data_condition` AS `rddc` ON `rdd`.`id` = `rddc`.`dynamic_data_id` ");
                String name = dataConditionBO.getName();
                sqlBuilder.append(" WHERE `rdd`.`name`='").append(name).append("' ");
    
                sqlBuilder.append(" GROUP BY `rdd`.`id` ");
                if (Objects.nonNull(dataConditionBO.getCondition()) && !dataConditionBO.getCondition().isEmpty()) {
                    sqlBuilder.append(" HAVING ");
                    int index = 0;
                    for (Map.Entry<String, String> entry: dataConditionBO.getCondition().entrySet()) {
                        index++;
                        String whereClause = " Find_In_Set('{"+entry.getKey()+":"+entry.getValue()+"}',`condition`) ";
                        sqlBuilder.append(whereClause);
                        if (index < dataConditionBO.getCondition().size()) {
                            sqlBuilder.append(" AND ");
                        }
                    }
                }
    
    
                return sqlBuilder.toString();
            }
        }
    
    }