java使用jooq连接mysql数据库

编辑POM引入需要的库，自动生成表相关

    <dependencies>
    ...
        <!-- HikariCP is our jdbc connection pooler for MySQL-->
        <!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>3.4.5</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>
        
        <!-- jooq makes db access easy and rewardType-safe for MySQL-->  
        <dependency>  
            <groupId>org.jooq</groupId>  
            <artifactId>jooq</artifactId>  
            <version>3.14.0</version>  
        </dependency>
        <!-- MYSQL record fetch into class-->
        <dependency>
            <groupId>javax.persistence</groupId>
            <artifactId>persistence-api</artifactId>
            <version>1.0.2</version>
            <type>jar</type>
        </dependency>
    ...
    </dependencies>

    <build>
        <plugins>
            <!-- 自动根据数据库生成相应对象 -->
            <!-- docs:https://www.jooq.org/doc/3.13/manual/code-generation/codegen-configuration/ -->
            <plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>3.14.0</version>
                
                <!-- The plugin should hook into the generate goal
                   <executions>
                       <execution>
                           <goals>
                               <goal>generate</goal>
                           </goals>
                       </execution>
                   </executions>
                -->
                <!-- Manage the plugin's dependency. connect mysql -->
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.18</version>
                    </dependency>
                    <dependency>
                        <groupId>org.jooq</groupId>
                        <artifactId>jooq-meta</artifactId>
                        <version>3.14.0</version>
                    </dependency>
                    <dependency>
                        <groupId>org.jooq</groupId>
                        <artifactId>jooq-codegen</artifactId>
                        <version>3.14.0</version>
                    </dependency>
                    <dependency>
                        <groupId>org.jooq</groupId>
                        <artifactId>jooq</artifactId>
                        <version>3.14.0</version>
                    </dependency>
                </dependencies>
                
                <configuration>
                    <!-- JDBC connection parameters -->
                    <jdbc>  
                        <driver>com.mysql.jdbc.Driver</driver>  
                        <url>jdbc:mysql://databaseurl:3306/mySchema</url>  
                        <user>dbuser</user>  
                        <password>dbpwd</password>  
                    </jdbc>

                    <!-- Generator parameters -->
                    <generator>
                        <database>  
                            <name>org.jooq.meta.mysql.MySQLDatabase</name>  
                            <includes>.*</includes>
                            <!-- All elements that are excluded from your schema (A Java regular expression.
                                Use the pipe to separate several expressions). Excludes match before
                                includes, i.e. excludes have a higher priority
                           <excludes>
                                UNUSED_TABLE                # This table (unqualified name) should not be generated
                              | PREFIX_.*                   # Objects with a given prefix should not be generated
                              | SECRET_SCHEMA\.SECRET_TABLE # This table (qualified name) should not be generated
                              | SECRET_ROUTINE              # This routine (unqualified name) ...
                           </excludes> -->
                            <excludes>create_ts|update_ts</excludes>
                            <includeExcludeColumns>true</includeExcludeColumns>
                            <inputSchema>mySchema</inputSchema>  
                        </database>
                        <target>
                            <packageName>com.test.generated.jooq</packageName>  
                            <directory>src/main/java</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>
        </plugins>
    </build>

---

在pom.XML所在目录

>>mvn jooq-codegen:generate

就可以自动由数据库表生产对应java文件，方便使用。

ps:不更新java服务器，可以对数据库修改：添加表，在已有表最后加行，但是不能插到中间。

---

新建连接数据库用的DSLContextFactory.java

package com.test.inject.factory;

import javax.inject.Inject;
import javax.sql.DataSource;
import org.jooq.Configuration;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author Admin
 */
public class DSLContextFactory {

    private final SQLDialect sqlDialect;
    private final Settings settings;
    private final DataSource dataSource;
    private final Configuration configuration;

    @Inject
    public DSLContextFactory(SQLDialect sqlDialect, DataSource dataSource) {
        this.settings = new Settings();
//        this.settings.setRenderSchema(false);

        this.sqlDialect = sqlDialect;
        this.dataSource = dataSource;

        this.configuration = new DefaultConfiguration()
                .set(this.dataSource)
                .set(this.sqlDialect)
                .set(this.settings);
    }

    public DSLContext create() {
        return DSL.using(configuration);
//        return DSL.using(dataSource, sqlDialect, settings);  
    }

    public DSLContext create(Configuration configuration) {
        return DSL.using(configuration);
    }

    public Configuration getConfiguration() {
        return configuration;
    }
}

以及所需注入的factory：

package com.test.inject.factory;

import com.test.consts.PomProperties;
import org.glassfish.hk2.api.Factory;
import org.jooq.SQLDialect;

/**
 *
 * @author Admin
 */
public class SQLDialectFactory implements Factory<SQLDialect> {

    private final SQLDialect sqlDialect;

    public SQLDialectFactory() {
        sqlDialect = SQLDialect.valueOf(PomProperties.getInstance().getDatabaseSqlDialect());
    }

    @Override
    public SQLDialect provide() {
        return sqlDialect;
    }

    @Override
    public void dispose(SQLDialect t) {

    }
}

package com.test.inject.factory;

import com.test.consts.PomProperties;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.inject.Inject;
import javax.sql.DataSource;
import org.glassfish.hk2.api.Factory;
import org.jooq.SQLDialect;

/**
 * https://github.com/brettwooldridge/HikariCP
 *
 * @author Admin
 */
public class DataSourceFactory implements Factory<DataSource> {

    private final SQLDialect sqlDialect;

    @Inject
    public DataSourceFactory(final SQLDialect sqlDialect) {
        this.sqlDialect = sqlDialect;
    }

    @Override
    public DataSource provide() {
        PomProperties properties = PomProperties.getInstance();

        HikariConfig config = new HikariConfig();
        config.setRegisterMbeans(true);

        config.setMaxLifetime(25 * 60 * 1000);
//        config.setIdleTimeout(3 * 60 * 1000);没用了 因为minPoolSize和maxPoolSize一样大
        config.setLeakDetectionThreshold(60 * 1000);
        config.setConnectionTimeout(10 * 1000);
        config.setMaximumPoolSize(properties.getDatabaseSqlPoolMax());

        //mysql optimization  
        if (SQLDialect.MYSQL.equals(sqlDialect)) {
//        config.setDriverClassName(dataSourceClass);
            config.setJdbcUrl(properties.getDatabaseUrl());
            config.setUsername(properties.getDatabaseUser());
            config.setPassword(properties.getDatabasePassword());

            config.addDataSourceProperty("cachePrepStmts", true);
            config.addDataSourceProperty("prepStmtCacheSize", 250);
            config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
        } else {
            config.setDataSourceClassName(properties.getDatabaseDataSource());
            config.addDataSourceProperty("url", properties.getDatabaseUrl());
            config.addDataSourceProperty("user", properties.getDatabaseUser());
            config.addDataSourceProperty("password", properties.getDatabasePassword());
        }

        HikariDataSource dataSource = new HikariDataSource(config);

        return dataSource;
    }

    @Override
    public void dispose(DataSource t) {

    }
}

修改binder

@Override
    protected void configure() {
        //数据库  
        bindFactory(SQLDialectFactory.class).to(SQLDialect.class).in(Singleton.class);
        bindFactory(DataSourceFactory.class).to(DataSource.class).in(Singleton.class);
//        bindFactory(RenderNameStyleFactory.class).to(RenderNameStyle.class).in(Singleton.class);
        bindAsContract(DSLContextFactory.class).in(Singleton.class);
        ...
    }

---

准备完成，可以使用DSLContextFactory建立连接数据库。

 创建一张表:

CREATE TABLE `authentication` (`uid` int NOT NULL auto_increment, `session_number` int, `create_ts` datetime DEFAULT CURRENT_TIMESTAMP, `update_ts` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`uid`) );

UserProvider.java

package com.test.user;

import com.test.generated.jooq.tables.records.AuthenticationRecord;
import org.jvnet.hk2.annotations.Contract;

/**
 *
 * @author Admin
 */
@Contract
public interface UserProvider {

    /**
     * 创建新用户
     * insert 语句,重复主键时忽略
     * @param uid 指定用户uid
     * @param sessionNumber 
     */
    void insertUser(int uid, int sessionNumber);
    
    /**
     * 创建新用户
     * insert 语句,重复主键时更新sessionNumber
     * @param uid
     * @param sessionNumber 
     */
    void insertUpdateUser(int uid, int sessionNumber);

    /**
     * 创建新用户
     * insert 语句,返回自增
     *
     * @return 新用户uid
     */
    int newUser();
     
    /**
     * 查询用户sessionNumber
     * select 语句,返回sessionNumber
     * @param uid
     * @return 
     */
    int getSessionNumber(int uid);
    
    /**
     * 查询用户
     * select 语句,返回整行
     * @param uid
     * @return record, 可以直接修改值,随后调用update()更新到数据库
     */
    AuthenticationRecord getAuthenticationRecord(int uid);
    
    /**
     * 更新用户sessionNumber
     * update 语句
     * @param uid
     * @param sessionNumber 
     */
    void updateSessionNumber(int uid, int sessionNumber);
}

import static com.test.generated.jooq.Tables.AUTHENTICATION;
import com.test.generated.jooq.tables.records.AuthenticationRecord;
import com.test.inject.factory.DSLContextFactory;
import java.util.Optional;
import javax.inject.Inject;
import org.jvnet.hk2.annotations.Service;

/**
 *
 * @author Admin
 */
@Service
public class DatabaseUserProvider implements UserProvider {

    private final DSLContextFactory dslContextFactory;

    @Inject
    public DatabaseUserProvider(
            final DSLContextFactory dslContextFactory
    ) {
        this.dslContextFactory = dslContextFactory;
    }

    @Override
    public void insertUser(int uid, int sessionNumber) {
        dslContextFactory.create()
                .insertInto(AUTHENTICATION, AUTHENTICATION.UID, AUTHENTICATION.SESSION_NUMBER)
                .values(uid, sessionNumber)
                .onDuplicateKeyIgnore()
                .execute();
    }

    @Override
    public void insertUpdateUser(int uid, int sessionNumber) {
        dslContextFactory.create()
                .insertInto(AUTHENTICATION)
                .set(AUTHENTICATION.UID, uid)
                .set(AUTHENTICATION.SESSION_NUMBER, sessionNumber)
                .onDuplicateKeyUpdate()
                .set(AUTHENTICATION.SESSION_NUMBER, sessionNumber)
                .execute();
    }

    @Override
    public int newUser() {
        Optional<AuthenticationRecord> optional = dslContextFactory.create()
                .insertInto(AUTHENTICATION)
                .set(AUTHENTICATION.SESSION_NUMBER, 0)
                .returning(AUTHENTICATION.UID)
                .fetchOptional();

//        if (optional.isPresent()) {
        return optional.get().getUid();
//        }
    }

    @Override
    public int getSessionNumber(int uid) {
        return dslContextFactory.create()
                .select(AUTHENTICATION.SESSION_NUMBER)
                .from(AUTHENTICATION)
                .where(AUTHENTICATION.UID.eq(uid))
                .fetchAny(AUTHENTICATION.UID);
    }

    @Override
    public AuthenticationRecord getAuthenticationRecord(int uid) {
        return dslContextFactory.create()
                .selectFrom(AUTHENTICATION)
                .where(AUTHENTICATION.UID.eq(uid))
                .fetchAny();
    }

    @Override
    public void updateSessionNumber(int uid, int sessionNumber) {
        dslContextFactory.create()
                .update(AUTHENTICATION)
                .set(AUTHENTICATION.SESSION_NUMBER, sessionNumber)
                .where(AUTHENTICATION.UID.eq(uid))
                .execute();
    }
}

有时候取自动生成的record类并不好用，希望直接转换成自己定义的类

    public MyEntity getEntity(int uid){
        return dslContextFactory.create()
                .selectFrom(AUTHENTICATION)
                .where(AUTHENTICATION.UID.eq(uid))
                .fetchOneInto(MyEntity.class);
    }

import javax.persistence.Column;

public class MyEntity{
    private int id;
    
    public int getId(){
         return id;
    }

    //数据库表字段名
    @Column(name = "uid")
    public void setId(int id){
        this.id = id;
    }

    private int session_number;

    public int getSession_number(){
        return session_number;
    }
    public void setSession_number(int session_number){
        this.session_number = session_number;
    }
}

---

一个排行榜的实例

分表操作,排序操作,连接操作

创建表

CREATE TABLE `sdk_info` (`uid` int NOT NULL, `platform` int, `id` varchar(64), `session` varchar(255), `nickname` varchar(255), `create_ts` datetime DEFAULT CURRENT_TIMESTAMP, `update_ts` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`uid`), KEY `sdk_info_IDX1` (`platform`, `id`) );
CREATE TABLE `rank_def` (`uid` int NOT NULL, `score` int NOT NULL, `date_ts` char(6) NOT NULL, PRIMARY KEY (`uid`) );
CREATE TABLE `rank_daily` (`idx` int NOT NULL auto_increment, `id` int NOT NULL, `nickname` varchar(255), `score` int NOT NULL, `date_ts` char(6), PRIMARY KEY (`idx`), KEY `rank_daily_IDX1` (`id`) );
CREATE TABLE `rank_all` (`idx` int NOT NULL auto_increment, `id` int NOT NULL, `nickname` varchar(255), `score` int NOT NULL, `date_ts` char(14), PRIMARY KEY (`idx`), KEY `rank_all_IDX1` (`id`) );

RankProvider.java

package com.test.rank;

import java.util.List;
import org.jvnet.hk2.annotations.Contract;

/**
 *
 * @author Admin
 */
@Contract
public interface RankProvider {

    /**
     * 更新玩家今天的分数
     * @param uid
     * @param score 
     */
    void uploadScore(int uid, int score);

    /**
     * 获取当前每日排行(昨天的数据)
     *
     * @param from
     * @param to
     * @return
     */
    List<RankData> getDailyRank(int from, int to);

    /**
     * 获取用户每日排行名次
     * @param uid
     * @return 
     */
    RankData getUserDailyRank(int uid);

    /**
     * 获取当前总排行
     *
     * @param from
     * @param to
     * @return
     */
    List<RankData> getAllRank(int from, int to);

    /**
     * 获取用户总榜状态
     * @param uid
     * @return 
     */
    RankData getUserAllRank(int uid);

    /**
     * 刷新每日排行和总排行
     */
    void checkRank();
}

DatabaseRankProvider.java

package com.test.rank;

import static com.test.generated.jooq.Tables.RANK_ALL;
import static com.test.generated.jooq.Tables.RANK_ALL_DEL;
import static com.test.generated.jooq.Tables.RANK_DAILY;
import static com.test.generated.jooq.Tables.SDK_INFO;
import com.test.inject.factory.DSLContextFactory;
import com.test.time.TimeService;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.inject.Inject;
import org.jooq.Comment;
import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.Record3;
import org.jooq.Schema;
import org.jooq.SelectOrderByStep;
import org.jooq.TableOptions;
import org.jooq.impl.DSL;
import org.jooq.impl.SQLDataType;
import org.jooq.impl.TableImpl;
import org.jvnet.hk2.annotations.Service;

/**
 *
 * @author Admin
 */
@Service
public class DatabaseRankProvider implements RankProvider {

    //用于select 后as
    private static final Field<Integer> FIELD_A = DSL.field("a", SQLDataType.INTEGER);
    private static final Field<String> FIELD_B = DSL.field("b", SQLDataType.VARCHAR(255));
    private static final Field<Integer> FIELD_C = DSL.field("c", SQLDataType.INTEGER);
    private static final Field<Integer> FIELD_MAX_C = DSL.field("cc", SQLDataType.INTEGER);

    private final DSLContextFactory dslContextFactory;
    private final TimeService timeService;
    private final RankTableManager rankTableManager;

    @Inject
    public DatabaseRankProvider(
            final DSLContextFactory dslContextFactory,
            final TimeService timeService
    ) {
        this.dslContextFactory = dslContextFactory;
        this.timeService = timeService;
        this.rankTableManager = new RankTableManager(dslContextFactory);
    }

    @Override
    public void uploadScore(int uid, int score) {
        String now = timeService.getCurrentTimeString_14();
        RankTable currentTable = rankTableManager.getTable(now.substring(0, 8));
//      在java里比较
//        Record1<Integer> r = dslContextFactory.create()
//                .select(SCORE)
//                .from(currentTable)
//                .where(UID.eq(uid))
//                .limit(1)
//                .fetchAny();
//
//        String ts = now.substring(8);
//        if (r == null) {
//            dslContextFactory.create()
//                    .insertInto(currentTable)
//                    .set(UID, uid)
//                    .set(SCORE, score)
//                    .set(TS, ts)
//                    .onDuplicateKeyIgnore()
//                    .execute();
//        } else {
//            if (score > r.value1()) {
//                dslContextFactory.create()
//                        .update(currentTable)
//                        .set(SCORE, score)
//                        .set(TS, ts)
//                        .where(UID.eq(uid))
//                        .limit(1)
//                        .execute();
//            }
//        }
        String ts = now.substring(8);
        Field conditionScore = DSL.decode().when(currentTable.SCORE.lessThan(score), score).otherwise(currentTable.SCORE);
//        Field conditionTs =  DSL.decode().when(SCORE.lessThan(score), ts).otherwise(TS);

        dslContextFactory.create()
                .insertInto(currentTable)
                .set(currentTable.UID, uid)
                .set(currentTable.SCORE, score)
                .set(currentTable.TS, ts)
                .onDuplicateKeyUpdate()
                .set(currentTable.SCORE, conditionScore)
                .set(currentTable.TS, ts)
                .execute();
    }

    @Override
    public List<RankData> getDailyRank(int from, int to) {
        return dslContextFactory.create()
                .selectFrom(RANK_DAILY)
                .where(RANK_DAILY.IDX.between(from, to))
                .fetchInto(RankData.class);
    }

    @Override
    public RankData getUserDailyRank(int uid) {
        return dslContextFactory.create()
                .selectFrom(RANK_DAILY)
                .where(RANK_DAILY.ID.eq(uid))
                .fetchAnyInto(RankData.class);
    }

    @Override
    public List<RankData> getAllRank(int from, int to) {
        return dslContextFactory.create()
                .selectFrom(RANK_ALL)
                .where(RANK_ALL.IDX.between(from, to))
                .fetchInto(RankData.class);
    }

    @Override
    public RankData getUserAllRank(int uid) {
        return dslContextFactory.create()
                .selectFrom(RANK_ALL)
                .where(RANK_ALL.ID.eq(uid))
                .fetchAnyInto(RankData.class);
    }

    /* 在java完成排序&去重
    @Override
    public void checkRank() {
        LocalDateTime now = timeService.getCurrentDate();
        DSLContext dslContext = dslContextFactory.create();

        //昨天记录
        String lastDay = timeService.getCurrentTimeString_14(now.minusDays(1)).substring(0, 8);
        List<Record4<Integer, Integer, String, String>> newDailyRanks = getRankAtSomeDay(lastDay, DAILY_RANK_AMOUNT);

        //更新每日
        dslContext.execute("drop table IF EXISTS `rank_daily_del`;");
        dslContext.execute("alter table `rank_daily` rename to `rank_daily_del`;");
        dslContext.execute("create table `rank_daily` like `rank_daily_del`;");

        newDailyRanks = newDailyRanks.stream()
                .filter(r -> r.value4() != null && r.value4().length() > 0)
                .sorted((r1, r2) -> r2.value2() - r1.value2())
                .collect(Collectors.toList());

        if (newDailyRanks.isEmpty()) {
            return;
        }

        InsertValuesStep5 dailyIVS = dslContext.insertInto(RANK_DAILY, RANK_DAILY.IDX, RANK_DAILY.ID, RANK_DAILY.NICKNAME, RANK_DAILY.SCORE, RANK_DAILY.DATE_TS);
        for (int i = 0; i < newDailyRanks.size(); i++) {
            Record4<Integer, Integer, String, String> r = newDailyRanks.get(i);
            dailyIVS = dailyIVS.values(i, r.value1(), r.value4(), r.value2(), r.value3());
        };
        dailyIVS.execute();

        //更新总榜
        List<Record4<Integer, Integer, String, String>> allRanks = getAllRAnkRecord(ALL_RANK_AMOUNT);

        allRanks.addAll(newDailyRanks);
        //排序,排除相同id
        allRanks = allRanks.stream()
                .sorted((r1, r2) -> r2.value2() - r1.value2())
                .filter(distinctByKey(r -> r.value1()))
                .collect(Collectors.toList());

        dslContext.execute("drop table IF EXISTS `rank_all_del`;");
        dslContext.execute("alter table `rank_all` rename to `rank_all_del`;");
        dslContext.execute("create table `rank_all` like `rank_all_del`;");

        InsertValuesStep5 allIVS = dslContext.insertInto(RANK_ALL, RANK_ALL.IDX, RANK_ALL.ID, RANK_ALL.NICKNAME, RANK_ALL.SCORE, RANK_ALL.DATE_TS);
        String today = timeService.getCurrentTimeString_14(now).substring(0, 8);
        String ts;
        Record4<Integer, Integer, String, String> r;
        for (int i = 0, j = 0; i < ALL_RANK_AMOUNT && j < allRanks.size(); i++) {
            r = allRanks.get(j++);
            ts = r.value3();
            if (ts == null || ts.length() <= 6) {
                ts = today + ts;
            }
            allIVS = allIVS.values(i, r.value1(), r.value4(), r.value2(), ts);
        }
        allIVS.execute();
    }

    
//    @Override
    //uid, score, ts, nickname
    public List<Record4<Integer, Integer, String, String>> getRankAtSomeDay(String date, int limit) {
        RankTable currentTable = rankTableManager.getTable(date);

        return dslContextFactory.create()
                .select(currentTable.UID, currentTable.SCORE, currentTable.TS, SDK_INFO.NICKNAME)
                .from(currentTable)
                .join(SDK_INFO)
                .on(currentTable.UID.eq(SDK_INFO.UID))
                .orderBy(currentTable.SCORE.desc())
                .limit(limit)
                .fetch();
    }

    //uid, score, ts, nickname
    public List<Record4<Integer, Integer, String, String>> getAllRAnkRecord(int limit) {
        return dslContextFactory.create()
                .select(RANK_ALL.ID, RANK_ALL.SCORE, RANK_ALL.DATE_TS, RANK_ALL.NICKNAME)
                .from(RANK_ALL)
                .orderBy(RANK_ALL.IDX.asc())
                .limit(limit)
                .fetch();
    }

    //去重
    private static <T> Predicate<T> distinctByKey(Function<? super T, ?> keyExtractor) {
        Map<Object, Boolean> seen = new ConcurrentHashMap<>();
        return t -> seen.putIfAbsent(keyExtractor.apply(t), Boolean.TRUE) == null;
    }
     */
    //由mysql完成排序&去重
    @Override
    public void checkRank() {
        LocalDateTime now = timeService.getCurrentDate();
        String lastDay = timeService.getCurrentTimeString_14(now.minusDays(1)).substring(0, 8);
        RankTable currentTable = rankTableManager.getTable(lastDay);
        DSLContext dslContext = dslContextFactory.create();

        //每日
        //清理
        dslContext.execute("drop table IF EXISTS `rank_daily_del`;");
        dslContext.execute("alter table `rank_daily` rename to `rank_daily_del`;");
        dslContext.execute("create table `rank_daily` like `rank_daily_del`;");

        //复制并且排序
        dslContext.insertInto(RANK_DAILY, RANK_DAILY.ID, RANK_DAILY.NICKNAME, RANK_DAILY.SCORE, RANK_DAILY.DATE_TS)
                .select(DSL.select(currentTable.UID, SDK_INFO.NICKNAME, currentTable.SCORE, currentTable.TS)
                        .from(currentTable)
                        .join(SDK_INFO)
                        .on(currentTable.UID.eq(SDK_INFO.UID))
                        .orderBy(currentTable.SCORE.desc())
                )
                .execute();

        //总
        dslContext.execute("drop table IF EXISTS `rank_all_del`;");
        dslContext.execute("alter table `rank_all` rename to `rank_all_del`;");
        dslContext.execute("create table `rank_all` like `rank_all_del`;");

        //复制并排序
        /*
        sql:
        insert into `rank_all`(`id`,`nickname`,`score`)
        (
			select a,b, max(c) from 
				(
					select `rank_all_del`.`id` a, `rank_all_del`.`nickname` b, `rank_all_del`.`score` c from `rank_all_del`
					union all
					select `_rank_20220623`.`uid` a, `sdk_info`.`nickname` b,`_rank_20220623`.`score` c from `_rank_20220623` join `sdk_info` on `_rank_20220623`.`uid` = `sdk_info`.`uid`
				)t
			group by a
			order by max(c) desc
        );
         */
        SelectOrderByStep<Record3<Integer, String, Integer>> unionSelect
                = DSL.select(RANK_ALL_DEL.ID.as(FIELD_A), RANK_ALL_DEL.NICKNAME.as(FIELD_B), RANK_ALL_DEL.SCORE.as(FIELD_C))
                        .from(RANK_ALL_DEL)
                        .unionAll(
                                DSL.select(currentTable.UID.as(FIELD_A), SDK_INFO.NICKNAME.as(FIELD_B), currentTable.SCORE.as(FIELD_C))
                                        .from(currentTable)
                                        .join(SDK_INFO)
                                        .on(currentTable.UID.eq(SDK_INFO.UID))
                        );

        dslContext.insertInto(RANK_ALL, RANK_ALL.ID, RANK_ALL.NICKNAME, RANK_ALL.SCORE)
                .select(DSL.select(FIELD_A, FIELD_B, DSL.max(FIELD_C).as(FIELD_MAX_C))
                        .from(unionSelect.asTable())
                        .groupBy(FIELD_A)
                        .orderBy(FIELD_MAX_C.desc()))
                //                .getSQL();
                //        System.out.println(sql);
                .execute();
    }

    //分表管理
    static class RankTableManager {

        final DSLContextFactory dslContextFactory;
        final Map<String, RankTable> tables;

        public RankTableManager(
                final DSLContextFactory dslContextFactory
        ) {
            this.dslContextFactory = dslContextFactory;
            this.tables = new HashMap<>();
            initTables();
        }

        final void initTables() {
            List e1 = dslContextFactory.create().fetchValues("SHOW TABLES LIKE '_rank_%'");
            Pattern p = Pattern.compile("_rank_(\\d{8})");
            String a;
            Matcher m;
            int i;
            for (Object o : e1) {
                a = String.valueOf(o);
                m = p.matcher(a);
                if (m.find()) {
                    try {
                        this.tables.put(m.group(1), new RankTable(a));//new TableImpl(DSL.name(a), SCHEMA, null, null, COMMENT, TableOptions.table()));
                    } catch (Exception ex) {

                    }
                }
            }
        }

        /**
         * 输入日期获取表
         *
         * @param date
         * @return
         */
        public RankTable getTable(String date) {
            if (tables.containsKey(date)) {
                return tables.get(date);
            }

            String tableName = "_rank_" + date;
            RankTable table = createNewEquipInfoTable(tableName);
            tables.put(date, table);

            return table;
        }

        /**
         * 创建一张新的表
         *
         * @param tableName
         */
        private RankTable createNewEquipInfoTable(String tableName) {
            DSLContext context = dslContextFactory.create();

            if (context.fetch(String.format("SHOW TABLES LIKE '%s'", tableName)).isEmpty()) {
                context.execute(String.format("create table `%s` like rank_def", tableName));
            }

            RankTable table = new RankTable(tableName);
            return table;
        }
    }

    //自己的表
    static class RankTable extends TableImpl {

        private static final Name UID_NAME = DSL.name("uid");
        private static final Name SCORE_NAME = DSL.name("score");
        private static final Name DATE_TS_NAME = DSL.name("date_ts");

        private final static Schema SCHEMA = null;
        private final static Comment COMMENT = DSL.comment("");

        public final Field<Integer> UID;// = DSL.field("uid", SQLDataType.INTEGER.nullable(false));
        public final Field<Integer> SCORE;// = DSL.field("score", SQLDataType.INTEGER.nullable(false));
        public final Field<String> TS;// = DSL.field("date_ts", SQLDataType.CHAR(6));

        public RankTable(String tableName) {
            super(DSL.name(tableName), SCHEMA, null, null, COMMENT, TableOptions.table());

            UID = this.createField(UID_NAME, SQLDataType.INTEGER.nullable(false));
            SCORE = this.createField(SCORE_NAME, SQLDataType.INTEGER.nullable(false));
            TS = this.createField(DATE_TS_NAME, SQLDataType.CHAR(6));
        }
    }
}

RankData.java

package com.test.rank;

import com.fasterxml.jackson.annotation.JsonProperty;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import javax.persistence.Column;

/**
 *
 * @author Admin
 */
@ApiModel(value = "RankData")
public class RankData {

    @ApiModelProperty(value = "idx")
    @JsonProperty("idx")
    private int idx;

    public int getIdx() {
        return idx;
    }

    @Column(name = "idx")
    public void setIdx(int idx) {
        this.idx = idx;
    }

    @ApiModelProperty(value = "id")
    @JsonProperty("id")
    private int id;

    public int getId() {
        return id;
    }

    @Column(name = "id")
    public void setId(int id) {
        this.id = id;
    }

    @ApiModelProperty(value = "nickname")
    @JsonProperty("nickname")
    private String nickname;

    public String getNickname() {
        return nickname;
    }

    @Column(name = "nickname")
    public void setNickname(String nickname) {
        this.nickname = nickname;
    }

    @ApiModelProperty(value = "score")
    @JsonProperty("score")
    private int score;

    public int getScore() {
        return score;
    }

    @Column(name = "score")
    public void setScore(int score) {
        this.score = score;
    }
}

---

jooq详细文档 Ver3.9

jOOQ教程 (3.3.3jOOQ as a SQL executor)