编辑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教程 (3.3.3jOOQ as a SQL executor)