jooq實踐

用法

　　sql語句

SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)FROM AUTHORJOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_IDWHERE BOOK.LANGUAGE = 'DE'AND BOOK.PUBLISHED > DATE '2008-01-01'
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAMEHAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRSTLIMIT 2OFFSET 1

　　java代碼

dsl.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count()).from(AUTHOR).join(BOOK).on(AUTHOR.ID.equal(BOOK.AUTHOR_ID)).where(BOOK.LANGUAGE.eq("DE")).and(BOOK.PUBLISHED.gt(date("2008-01-01"))).groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).having(count().gt(5)).orderBy(AUTHOR.LAST_NAME.asc().nullsFirst()).limit(2).offset(1)

示例代碼

增:
public void addMerchantUrgentIsochrone(ITbMerchantUrgentIsochrone merchantUrgentIsochrone) {TbMerchantUrgentIsochroneRecord record = dsl.newRecord(TB_MERCHANT_URGENT_ISOCHRONE, merchantUrgentIsochrone);record.store();merchantUrgentIsochrone.setId(record.getId());
}
刪:
public int deleteRecords() {return dsl.delete(TB_MERCHANT_URGENT_ISOCHRONE).where(TB_MERCHANT_URGENT_ISOCHRONE.CREATED_AT.ge(Timestamp.valueOf(LocalDateTime.now().minusDays(1)))).execute();
}
改:
public int update(ITbUserArea userArea) {return dsl.update(TB_USER_AREA).set(TB_USER_AREA.GEOHASH, userArea.getGeohash()).set(TB_USER_AREA.AREA, userArea.getArea()).set(TB_USER_AREA.CITY_ID, userArea.getCityId()).set(TB_USER_AREA.ORDER_COUNT, userArea.getOrderCount()).where(TB_USER_AREA.ID.eq(userArea.getId())).execute();
}
查:
public List<ITbUserArea> getAreas(String preHash) {return dsl.selectFrom(TB_USER_AREA).where(TB_USER_AREA.GEOHASH.like(preHash+"%")).fetchInto(TbUserArea.class);
}

jooq 可以執行sql語句

Result<Record> fetch(String var1) throws DataAccessException;
int execute(String var1) throws DataAccessException;

配置篇

　　maven配置

Maven依賴：(版本號可配)
<dependency><groupId>org.jooq</groupId><artifactId>jooq</artifactId><version>3.9.5</version>
</dependency>
<dependency><groupId>org.jooq</groupId><artifactId>jooq-meta</artifactId><version>3.9.5</version>
</dependency>
<dependency><groupId>org.jooq</groupId><artifactId>jooq-codegen</artifactId><version>3.9.5</version>
</dependency>工具生成映射配置：
建立 xxx.xml(名字任意)
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration><jdbc><driver>...</driver><url>...</url><user>...</user><password>...</password></jdbc><generator><!-- 指定代碼風格- org.jooq.util.ScalaGenerator- org.jooq.util.JavaGenerator--><name>org.jooq.util.JavaGenerator</name><database><!-- 數據庫類型 --><name>org.jooq.util.postgres.PostgresDatabase</name><!-- <name>org.jooq.util.mysql.MySQLDatabase</name> --><excludes><!-- 對tb_table1,tb_table2,tb_table3 表不生成代碼-->tb_table1|tb_table2|tb_table3</excludes><includeExcludeColumns>true</includeExcludeColumns><inputSchema>public</inputSchema><!-- jooq轉換對象類型，如用enum代替int目標轉換類型應實現org.jooq.Converter或org.jooq.Binding接口相應的配置標簽為<customType>，<forcedType>參考地址: https://www.jooq.org/doc/3.9/manual/code-generation/custom-data-types/https://www.jooq.org/doc/3.9/manual/code-generation/custom-data-type-bindings/--><customTypes><customType><name>EffectiveStatus</name><type>xxx.isochrone.constant.EffectiveStatus</type><converter>xxx.geo.jooq.converter.EffectiveStatusConverter</converter></customType></customTypes><forcedTypes><!-- 使用converter --><forcedType><name>EffectiveStatus</name><expressions>.*\.tb_isochrone_audit_info\.effective_status</expressions><types>.*</types></forcedType><!-- 使用binding --><forcedType><userType>xxx.isochrone.pojos.GeographyPolygon</userType><binding>xxx.geo.jooq.binding.PGgeometryPolygonBinding</binding><expression>.*\.tb_isochrone.range|.*\.tb_merchant_area_isochrone.range</expression><types>.*</types></forcedType></forcedTypes></database><generate><deprecated>false</deprecated><daos>true</daos><interfaces>true</interfaces></generate><target><!-- 生成的包名，生成的類在此包下 --><packageName>xxx.isochrone.jooq</packageName><!-- 輸出的目錄 --><directory>src/main/java</directory></target></generator>
</configuration>相應pom.xml里添加對應的配置信息
<profiles><profile><id>db-gen</id><build><plugins><plugin><groupId>org.codehaus.mojo</groupId><artifactId>exec-maven-plugin</artifactId><version>1.4.0</version><executions><execution><goals><goal>java</goal></goals></execution></executions><configuration><includeProjectDependencies>false</includeProjectDependencies><includePluginDependencies>true</includePluginDependencies><mainClass>org.jooq.util.GenerationTool</mainClass><cleanupDaemonThreads>false</cleanupDaemonThreads><arguments><!-- 匹配工具生成映射配置文件 --><argument>xxx.xml</argument></arguments></configuration><dependencies><dependency><groupId>org.jooq</groupId><artifactId>jooq</artifactId><version>3.9.5</version></dependency><dependency><groupId>org.jooq</groupId><artifactId>jooq-codegen</artifactId><version>3.9.5</version></dependency><dependency><groupId>org.jooq</groupId><artifactId>jooq-meta</artifactId><version>3.9.5</version></dependency><dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><version>9.4.1208.jre7</version></dependency></dependencies></plugin></plugins></build></profile>
</profiles>

執行代碼自動生成命令
mvn -P db-gen exec:java

　　gradle配置

application.yml

spring:
　　jooq:
　　　　#請勿動，當使用了ejdbc的配置后，Spring Boot無法識別正確的Dialect
　　　　sqlDialect: Mysql


jooq模塊下的gradle文件
apply plugin: 'io.spring.dependency-management'
dependencyManagement {resolutionStrategy {cacheChangingModulesFor 0, 'seconds'}imports {mavenBom 'io.spring.platform:platform-bom:Brussels-SR6'mavenBom 'org.jooq:jooq:3.10.1'}dependencies {dependency 'org.springframework.statemachine:spring-statemachine-core:1.2.6.RELEASE'}
}apply plugin: 'nu.studer.jooq'emodule {dependency 'tms-jooq-model'
}
dependencies {compile 'org.jooq:jooq'jooqRuntime 'mysql:mysql-connector-java'
}// Jooq Gradle Plugin 參考文檔 https://github.com/etiennestuder/gradle-jooq-plugin
jooq {version = '3.10.1'tms(sourceSets.main) {jdbc {driver = 'com.mysql.jdbc.Driver'url = 'jdbc:mysql://localhost:3306/test'user = 'xxx'password = 'xxx'}generator {name = 'org.jooq.util.DefaultGenerator'database {name = 'org.jooq.util.mysql.MySQLDatabase'inputSchema = 'scm_tms'outputSchemaToDefault = trueincludeExcludeColumns = true//重構，相關字段遷移到tb_docker_deployment_log中excludes = ".*_bak|.*\\.drc_check_time|.*\\.is_delete|.*\\.updated_at"forcedTypes {forcedType {userType = "xxx.common.model.model.GeoPoint"converter = "xxx.jooq.model.converter.GeoPointConverter"expression = ".*_lnglat"types = ".*"}forcedType {userType = "xxx.base.api.model.UsedType"converter = "xxx.jooq.model.converter.VehicleIsUsedTypeConverter"/*A Java regular expression matching fully-qualified columns. Use the pipe to separate several expressions. ->database.table.column*/expression = ".*\\.base_vehicle\\.is_used"/*Add a Java regular expression matching data types to be forced to have this type*/types = ".*"}}}generate {relations = truedeprecated = falserecords = trueinterfaces = truepojos = truedaos = falsefluentSetters = true}target {packageName = project.groupPrefix + "." + project.groupName + ".db.model"directory = 'src/main/java'}}}
}//不自動執行Jooq的代碼生成和清除任務
//數據庫更改后，手動進入 jooq模塊
//執行 gradle cleanGenerateTmsJooqSchemaSource generateTmsJooqSchemaSource
//生成數據庫對象在tms-db-model下
project.tasks.getByName('compileJava').dependsOn -= 'generateTmsJooqSchemaSource'
project.tasks.getByName('clean').dependsOn -= 'cleanGenerateTmsJooqSchemaSource'

jooq Converter示例

import xxx.GeoPoint;
import org.jooq.Converter;public class GeoPointConverter implements Converter<String, GeoPoint> {@Overridepublic GeoPoint from(String databaseObject) {return new GeoPoint(databaseObject);}@Overridepublic String to(GeoPoint point) {return point==null?"":point.gdFormat();}@Overridepublic Class<String> fromType() {return String.class;}@Overridepublic Class<GeoPoint> toType() {return GeoPoint.class;}
}public class GeoPoint {/*** 經度值*/private Double longitude;/*** 緯度值*/private Double latitude;public GeoPoint() {}public GeoPoint(String location) {if (!StringUtils.isEmpty(location)) {String[] lnglat = location.split(",");this.longitude = Double.valueOf(lnglat[0]);this.latitude = Double.valueOf(lnglat[1]);}}public Double getLongitude() {return longitude;}public void setLongitude(Double longitude) {this.longitude = longitude;}public Double getLatitude() {return latitude;}public void setLatitude(Double latitude) {this.latitude = latitude;}public String gdFormat() {if (longitude == null || latitude == null) {return "";}return longitude + "," + latitude;}@Overridepublic boolean equals(Object o) {if (this == o) return true;if (o == null || getClass() != o.getClass()) return false;GeoPoint geoPoint = (GeoPoint) o;if (longitude != null ? !longitude.equals(geoPoint.longitude) : geoPoint.longitude != null) return false;return latitude != null ? latitude.equals(geoPoint.latitude) : geoPoint.latitude == null;}@Overridepublic int hashCode() {int result = longitude != null ? longitude.hashCode() : 0;result = 31 * result + (latitude != null ? latitude.hashCode() : 0);return result;}@Overridepublic String toString() {return "GeoPoint{" +"longitude=" + longitude +", latitude=" + latitude +'}';}}

import xxx.scm.tms.common.model.model.WarehouseType;
import org.jooq.Converter;/*** WarehouseTypeConverter*/
public class WarehouseTypeConverter implements Converter<Byte, WarehouseType> {@Overridepublic WarehouseType from(Byte databaseObject) {return WarehouseType.getTypeByCode(databaseObject);}@Overridepublic Byte to(WarehouseType warehouseType) {return warehouseType.getCode();}@Overridepublic Class<Byte> fromType() {return Byte.class;}@Overridepublic Class<WarehouseType> toType() {return WarehouseType.class;}
}import java.util.EnumSet;
import java.util.HashMap;
import java.util.Map;public enum WarehouseType {NORMAL_TEMPERATURE((byte)1, "常溫"),COLD_STORAGE((byte)2, "冷藏"),FREEZING((byte)3, "冷凍");private byte code;private String label;WarehouseType(byte code, String label) {this.code = code;this.label = label;}public byte getCode() {return code;}public String getLabel() {return label;}private static Map<Byte, WarehouseType> map = new HashMap<>();static {EnumSet.allOf(WarehouseType.class).forEach(e -> map.put(e.getCode(), e));}public static WarehouseType getTypeByCode(Byte code) {return map.get(code);}
}

高級用法代碼示例

public List<BaseWarehouse> searchWarehouses(WarehouseCondition warehouseCondition) {SelectConditionStep<?> step = dsl.selectFrom(Tables.BASE_WAREHOUSE).where(DSL.trueCondition());StringBuilder likeValue = new StringBuilder();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<SelectField<?>> getListField() {Collection<SelectField<?>> 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);return fields;
}
Result<Record> 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();