Java 查询代码优化

通常我们查询是把所有的查询条件，校验后拼SQL，这篇文章做使用设计模式做一些优化。

springboot 1.5.9   java8  mybatis-generator

1、以下图第一个查询条件为例，通过姓名、工号、手机号查询。这些属于员工的基本信息存在user表中，查询的主表只存user_id , 所以我们应该先通过姓名、工号、手机号查到user_id，最后在查询主表。

2、查询传输实体、枚举设计

public class RecordQueryVO { @ApiModelProperty(value = "日期区间类型[ADJUST:调动时间,OPERATE:操作时间],为空表示全部") private enum1 intervalType; @ApiModelProperty(value = "起始日期") private LocalDate from; @ApiModelProperty(value = "结束日期") private LocalDate to; @ApiModelProperty(value = "关键字筛选类型[NAME:姓名,CODE:工号,MOBILE:手机号]") private enum2 queryType; @ApiModelProperty(value = "关键字") private String keyword; } public enum enum1 { ADJUST("调动时间"), OPERATE("操作时间"), ; private String description; enum1(String description) { this.description = description; } } public enum enum2 { NAME("姓名"), CODE("工号"), MOBILE("手机号"), ; private String description; enum2(String description) { this.description = description; } }

3、处理查询条件接口，这里我把查询条件放到上下文context中

public interface IEmployeePositionAdjustCriteriaHandler { /** * 处理查询条件 * * @param context 上下文 * @param example 查询条件 */ void handle(EmployeeAdjustPaginctaContext context, WaysHrmEmployeeChangeApplyExample example); }

4、抽象类实现接口

abstract class AbstractEmployeePositionAdjustInfoCriteriaHandler implements IEmployeePositionAdjustCriteriaHandler { @Autowired private IHrmEmployeeCrudService iHrmEmployeeCrudService; @Override public void handle(EmployeeAdjustPaginctaContext context, WaysHrmEmployeeChangeApplyExample example) { String keyword = context.getKeyword(); WaysHrmEmployeeExample employeeExample = new WaysHrmEmployeeExample(); //抽象方法、通过关键字查询，这里将会有不同的实现类，来继承抽象接口 this.queryStaffIds(employeeExample, keyword); List<Integer> ids = Optional.ofNullable(iHrmEmployeeCrudService.listByExample(employeeExample)) .filter(CollectionUtils::isNotEmpty) .map(list -> list.stream().map(WaysHrmEmployee::getEmployeeId).collect(Collectors.toList())) .orElse(Lists.newArrayListWithExpectedSize(0)); Optional.of(ids) .filter(CollectionUtils::isNotEmpty) .map(employeeIds -> { example.getOredCriteria().forEach(criteria -> criteria.andStaffIdIn(employeeIds)); return example; }).orElseGet(() -> { example.clear(); return null; }); } /** * * @param keyword */ protected abstract void queryStaffIds(WaysHrmEmployeeExample employeeExample, String keyword); }

5、具体的查询条件处理接口实现  bean命名以   接口名: 查询类型的枚举name，这样的好处后边可以看到

//工号 @Service(value = EmployeeCodeCriteriaHandlerImpl.SERVICE_QUALIFIER) public class EmployeeCodeCriteriaHandlerImpl extends AbstractEmployeePositionAdjustInfoCriteriaHandler { public static final String SERVICE_QUALIFIER = "AbstractEmployeePositionAdjustInfoCriteriaHandler:CODE"; @Override protected void queryStaffIds(WaysHrmEmployeeExample employeeExample, String keyword) { employeeExample.createCriteria().andEmployeeCodeEqualTo(keyword); } } //手机号 @Service(value = EmployeeMobileCriteriaHandlerImpl.SERVICE_QUALIFIER) public class EmployeeMobileCriteriaHandlerImpl extends AbstractEmployeePositionAdjustInfoCriteriaHandler { public static final String SERVICE_QUALIFIER = "AbstractEmployeePositionAdjustInfoCriteriaHandler:MOBILE"; @Override protected void queryStaffIds(WaysHrmEmployeeExample employeeExample, String keyword) { employeeExample.createCriteria().andMobileEqualTo(keyword); } } //姓名 @Service(value = EmployeeNameCriteriaHandlerImpl.SERVICE_QUALIFIER) public class EmployeeNameCriteriaHandlerImpl extends AbstractEmployeePositionAdjustInfoCriteriaHandler { public static final String SERVICE_QUALIFIER = "AbstractEmployeePositionAdjustInfoCriteriaHandler:NAME"; @Override protected void queryStaffIds(WaysHrmEmployeeExample employeeExample, String keyword) { employeeExample.createCriteria().andEmployeeNameLike("%" + keyword + "%"); } }

6、不同查询条件的 查询都写好了，解下来看service层的使用

      首先需要将不同实现的bean注入进来，可以通过bean Name 获取

@Autowired private Map<String, IEmployeePositionAdjustCriteriaHandler> iEmployeePositionAdjustCriteriaHandlerMap;

根据我们前端传的条件进行查询，通过查询条件类型的枚举名称，获取对应的实现

//员工基本信息 Optional.of(context) .map(EmployeeAdjustPaginctaContext::getKeyword) .ifPresent(keyword -> { IEmployeePositionAdjustCriteriaHandler infoHandler = Optional.of(context) .map(EmployeeAdjustPaginctaContext::getQueryType) .map(Enum::name) .map(enumName -> Joiner.on(":").join(AbstractEmployeePositionAdjustInfoCriteriaHandler.class.getSimpleName(), enumName)) .map(this.iEmployeePositionAdjustCriteriaHandlerMap::get) .orElseThrow(HrmEmployeeChangeException.INVALID_QUERY_TYPE.exception()); infoHandler.handle(context, example); });