jooq mysql例子_jooq实践

public ListsearchWarehouses(WarehouseCondition warehouseCondition) {

SelectConditionStep> step =dsl.selectFrom(Tables.BASE_WAREHOUSE).where(DSL.trueCondition());

StringBuilder likeValue= newStringBuilder();if (!Objects.isNull(warehouseCondition.getWarehouseId())) {

step.and(Tables.BASE_WAREHOUSE.WAREHOUSE_ID.eq(warehouseCondition.getWarehouseId()));

}if(StringUtils.isNotBlank(warehouseCondition.getWarehouseName())) {

likeValue.setLength(0);

likeValue.append("%").append(warehouseCondition.getWarehouseName()).append("%");

step.and(Tables.BASE_WAREHOUSE.WAREHOUSE_NAME.likeIgnoreCase(likeValue.toString()));

}if(StringUtils.isNotBlank(warehouseCondition.getCompany())) {

likeValue.setLength(0);

likeValue.append("%").append(warehouseCondition.getCompany()).append("%");

step.and(Tables.BASE_WAREHOUSE.COMPANY.likeIgnoreCase(likeValue.toString()));

}if(StringUtils.isNotBlank(warehouseCondition.getProvinceName())) {

step.and(Tables.BASE_WAREHOUSE.PROVINCE_NAME.eq(warehouseCondition.getProvinceName()));

}if (!Objects.isNull(warehouseCondition.getProvinceId())) {

step.and(Tables.BASE_WAREHOUSE.PROVINCE_ID.eq(warehouseCondition.getProvinceId()));

}if(StringUtils.isNotBlank(warehouseCondition.getCityName())) {

step.and(Tables.BASE_WAREHOUSE.CITY_NAME.eq(warehouseCondition.getCityName()));

}if (!Objects.isNull(warehouseCondition.getCityId())) {

step.and(Tables.BASE_WAREHOUSE.CITY_ID.eq(warehouseCondition.getCityId()));

}if(StringUtils.isNotBlank(warehouseCondition.getDistrictName())) {

step.and(Tables.BASE_WAREHOUSE.DISTRICT_NAME.eq(warehouseCondition.getDistrictName()));

}if (!Objects.isNull(warehouseCondition.getDistrictId())) {

step.and(Tables.BASE_WAREHOUSE.DISTRICT_ID.eq(warehouseCondition.getDistrictId()));

}if(StringUtils.isNotBlank(warehouseCondition.getAddress())) {

likeValue.setLength(0);

likeValue.append("%").append(warehouseCondition.getAddress()).append("%");

step.and(Tables.BASE_WAREHOUSE.ADDRESS.like(likeValue.toString()));

}if (!Objects.isNull(warehouseCondition.getWarehouseLnglat())) {

step.and(Tables.BASE_WAREHOUSE.WAREHOUSE_LNGLAT.eq(warehouseCondition.getWarehouseLnglat()));

}if(StringUtils.isNotBlank(warehouseCondition.getContactName())) {

step.and(Tables.BASE_WAREHOUSE.CONTACT_NAME.eq(warehouseCondition.getContactName()));

}if(StringUtils.isNotBlank(warehouseCondition.getContactPhone())) {

step.and(Tables.BASE_WAREHOUSE.CONTACT_PHONE.eq(warehouseCondition.getContactPhone()));

}return step.fetchInto(BaseWarehouse.class);

}private Collection>getListField() {

Collection> fields = new ArrayList<>();

fields.add(SHIPMENT.SHIPMENT_ID);

fields.add(SHIPMENT.OUTBOUND_ID);

fields.add(SHIPMENT.BIZ_TYPE);

fields.add(SHIPMENT.BIZ_SUBTYPE);

fields.add(SHIPMENT.PICKUP_NAME);

fields.add(SHIPMENT.PICKUP_CONTACT_NAME);

fields.add(SHIPMENT.PICKUP_CONTACT_PHONE);

fields.add(SHIPMENT.PICKUP_PROVINCE_NAME);

fields.add(SHIPMENT.PICKUP_CITY_NAME);

fields.add(SHIPMENT.PICKUP_DISTRICT_NAME);

fields.add(SHIPMENT.PICKUP_ADDRESS);

fields.add(SHIPMENT.DESTINATION_NAME);

fields.add(SHIPMENT.DESTINATION_CONTACT_NAME);

fields.add(SHIPMENT.DESTINATION_CONTACT_PHONE);

fields.add(SHIPMENT.DESTINATION_PROVINCE_NAME);

fields.add(SHIPMENT.DESTINATION_CITY_NAME);

fields.add(SHIPMENT.DESTINATION_DISTRICT_NAME);

fields.add(SHIPMENT.DESTINATION_ADDRESS);

fields.add(SHIPMENT.DESTINATION_REMARK);

fields.add(SHIPMENT.ORDER_ID);

fields.add(SHIPMENT.ORDER_CREATED_AT);

fields.add(SHIPMENT.STATUS);

fields.add(DSL.ifnull(SHIPMENT_SKU.SHIPMENT_ID.count(),0).as("COUNT"));

fields.add(DSL.ifnull(SHIPMENT_SKU.SKU_COUNT.sum(), BigDecimal.ZERO).as("SKU_COUNT"));

fields.add(DSL.ifnull(SHIPMENT_SKU.OUTBOUND_COUNT.sum(), BigDecimal.ZERO).as("OUTBOUND_COUNT"));

fields.add(DSL.ifnull(SHIPMENT_SKU.SIGN_COUNT.sum(), BigDecimal.ZERO).as("SIGN_COUNT"));

fields.add(SHIPMENT.PICKUP_LNGLAT);

fields.add(SHIPMENT.DESTINATION_LNGLAT);

fields.add(SHIPMENT.EXPECT_ARRIVE_BEGIN_AT);

fields.add(SHIPMENT.EXPECT_ARRIVE_END_AT);

fields.add(SHIPMENT.BIZ_JSON);

fields.add(SHIPMENT.WAREHOUSE_ID);

fields.add(SHIPMENT.DRIVER_REMARK);//detail使用字段

fields.add(SHIPMENT.DRIVER_ID);

fields.add(SHIPMENT.CUSTOMER_SERVICE);

fields.add(SHIPMENT.CARRIER_ID);

fields.add(SHIPMENT.CARRIER_NAME);

fields.add(SHIPMENT.ROUTE_ID);returnfields;

}

Result records =dsl.select(getListField())

.from(SHIPMENT)

.leftJoin(SHIPMENT_SKU)

.on(SHIPMENT.SHIPMENT_ID.eq(SHIPMENT_SKU.SHIPMENT_ID))

.where(getListConditions(query))

.groupBy(SHIPMENT.SHIPMENT_ID)

.orderBy(SHIPMENT.CREATED_AT.desc())

.offset(query.getOffset())

.limit(query.getLimit())

.fetch();