Skip to content

Instantly share code, notes, and snippets.

@siordache

siordache/JooqUpsertV1.java

Last active April 30, 2018 18:41
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save siordache/c89777eb69756b2b1295840625846cbf to your computer and use it in GitHub Desktop.
Save siordache/c89777eb69756b2b1295840625846cbf to your computer and use it in GitHub Desktop.
Download ZIP
Programmatically create updatable table with JOOQ (see https://groups.google.com/forum/#!topic/jooq-user/K1Fe3UIWLr8)
Raw
JooqUpsertV1.java
import org.hsqldb.Server;
import org.hsqldb.persist.HsqlProperties;
import org.jooq.*;
import java.nio.file.Files;
import java.nio.file.Path;
import static org.jooq.impl.DSL.*;
public class JooqUpsertV1 {
private final DSLContext dsl;
final static Table<Record> PRODUCT = table(name("PRODUCT"));
final static Field<String> NAME = field(name("NAME"), String.class);
final static Field<String> COLOR = field(name("COLOR"), String.class);
public JooqUpsertV1(DSLContext dsl) {
this.dsl = dsl;
}
public void create() {
dsl.createTable(PRODUCT)
.column(NAME, NAME.getDataType().nullable(false))
.column(COLOR, COLOR.getDataType().nullable(false))
.constraints(
constraint(name("PK_PRODUCT")).primaryKey(NAME)
)
.execute();
}
int upsert(String name, String color) {
return dsl.insertInto(PRODUCT)
.columns(NAME, COLOR)
.values(name, color)
.onDuplicateKeyUpdate()
.set(COLOR, color)
.execute();
}
private static Server startServer(String dbName, int port) throws Exception {
Path dbDir = Files.createTempDirectory("hsqldb");
HsqlProperties props = new HsqlProperties();
props.setProperty("server.database.0", "file:" + dbDir + "/" + dbName + ";");
props.setProperty("server.dbname.0", dbName);
props.setProperty("server.port", port);
Server server = new Server();
server.setProperties(props);
server.start();
return server;
}
public static void main(String[] args) throws Exception {
String dbName = "mydb";
int port = 9137;
Server server = startServer(dbName, port);
try {
String dbUrl = "jdbc:hsqldb:hsql://localhost:" + port + "/" + dbName;
JooqUpsertV1 productDB = new JooqUpsertV1(using(dbUrl));
productDB.create();
// The following line throws a java.lang.IllegalStateException:
// The ON DUPLICATE KEY IGNORE/UPDATE clause cannot be emulated when inserting into non-updatable tables : "PRODUCT"
productDB.upsert("car", "red");
productDB.upsert("car", "green");
} finally {
server.shutdown();
}
}
}
Raw
JooqUpsertV2.java
import org.hsqldb.Server;
import org.hsqldb.persist.HsqlProperties;
import org.jooq.*;
import org.jooq.impl.Internal;
import org.jooq.impl.TableImpl;
import java.nio.file.Files;
import java.nio.file.Path;
import static org.jooq.impl.DSL.*;
public class JooqUpsertV2 {
private final DSLContext dsl;
final static UpsertTable PRODUCT = upsertTable("PRODUCT");
final static Field<String> NAME = field(name("NAME"), String.class);
final static Field<String> COLOR = field(name("COLOR"), String.class);
private static class UpsertTable extends TableImpl<Record> {
private UniqueKey<Record> primaryKey;
protected UpsertTable(Name name) {
super(name);
}
public void setPrimaryKey(String keyName, TableField<Record,?> field) {
this.primaryKey = Internal.createUniqueKey(this, keyName, field);
}
@Override
public UniqueKey<Record> getPrimaryKey() {
return primaryKey;
}
}
private static final UpsertTable upsertTable(String tableName) {
return new UpsertTable(name(tableName));
}
private static <T>ConstraintFinalStep primaryKeyConstraint(String keyName, UpsertTable table, Field<T> field) {
table.setPrimaryKey(keyName, (TableField)field);
return constraint(name(keyName)).primaryKey(field);
}
public JooqUpsertV2(DSLContext dsl) {
this.dsl = dsl;
}
public void create() {
dsl.createTable(PRODUCT)
.column(NAME, NAME.getDataType().nullable(false))
.column(COLOR, COLOR.getDataType().nullable(false))
.constraints(
primaryKeyConstraint("PK_PRODUCT", PRODUCT, NAME)
)
.execute();
}
int upsert(String name, String color) {
return dsl.insertInto(PRODUCT)
.columns(NAME, COLOR)
.values(name, color)
.onDuplicateKeyUpdate()
.set(COLOR, color)
.execute();
}
private static Server startServer(String dbName, int port) throws Exception {
Path dbDir = Files.createTempDirectory("hsqldb");
HsqlProperties props = new HsqlProperties();
props.setProperty("server.database.0", "file:" + dbDir + "/" + dbName + ";");
props.setProperty("server.dbname.0", dbName);
props.setProperty("server.port", port);
Server server = new Server();
server.setProperties(props);
server.start();
return server;
}
public static void main(String[] args) throws Exception {
String dbName = "mydb";
int port = 9137;
Server server = startServer(dbName, port);
try {
String dbUrl = "jdbc:hsqldb:hsql://localhost:" + port + "/" + dbName;
JooqUpsertV2 productDB = new JooqUpsertV2(using(dbUrl));
productDB.create();
productDB.upsert("car", "red");
productDB.upsert("car", "green");
} finally {
server.shutdown();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment