• jooq实践


    用法

      sql语句

    SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
        FROM AUTHOR
        JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
       WHERE BOOK.LANGUAGE = 'DE'
         AND BOOK.PUBLISHED > DATE '2008-01-01'
    GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
      HAVING COUNT(*) > 5
    ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
       LIMIT 2
      OFFSET 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 = true includeExcludeColumns = 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 = true deprecated = false records = true interfaces = true pojos = true daos = false fluentSetters = 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> {
        @Override
        public GeoPoint from(String databaseObject) {
            return new GeoPoint(databaseObject);
        }
    
        @Override
        public String to(GeoPoint point) {
            return point==null?"":point.gdFormat();
        }
    
        @Override
        public Class<String> fromType() {
            return String.class;
        }
    
        @Override
        public 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;
        }
    
        @Override
        public 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;
        }
    
        @Override
        public int hashCode() {
            int result = longitude != null ? longitude.hashCode() : 0;
            result = 31 * result + (latitude != null ? latitude.hashCode() : 0);
            return result;
        }
    
        @Override
        public 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> {
        @Override
        public WarehouseType from(Byte databaseObject) {
            return WarehouseType.getTypeByCode(databaseObject);
        }
    
        @Override
        public Byte to(WarehouseType warehouseType) {
            return warehouseType.getCode();
        }
    
        @Override
        public Class<Byte> fromType() {
            return Byte.class;
        }
    
        @Override
        public 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();

    DSLContext executeUpdate

       dsl.executeUpdate(dsl.newRecord(BASE_WAREHOUSE, baseWarehouse)), 如果vo类中的主键为null的时候

      

      要指定 condition

      

    乐观锁实现

      https://www.jooq.org/doc/3.10/manual-single-page/#optimistic-locking

      https://www.jooq.org/doc/3.10/manual-single-page/#codegen-config-record-version-timestamp-fields

  • 相关阅读:
    利用JQuery+EasyDrag jQuery Plugin实现弹出可拖动的Div,同时向Div传值,然后返回Div选中的值 Virus
    批量(导入导出)迁移AD中的用户信息和密码到新环境中,同时保持用户在MOSS中的权限【addusers,ldifde,copypwd,UserInfo,tp_SystemID】 Virus
    修改MOSS网站的URL访问地址 Virus
    MOSS安装目录Layouts中的文件不能用SharePoint Designer修改 Virus
    Office Server 共享服务报错,原因:对路径“c\windows\system32\drivers\ect\hosts”的访问被拒绝 Virus
    微软文档翻译:【最佳实践】使用sharepoint对象模型编程时候的常见问题 Virus
    WCF自定义行为 Step by Step (一)搭建环境
    土了,Silverlight调试一例
    WCF之自定义Channel
    WCF自定义行为 Step by Step (四)第二个自定义behavior
  • 原文地址:https://www.cnblogs.com/hujunzheng/p/8342999.html
Copyright © 2020-2023  润新知