JarCastingjOOQ.x - Vertx jOOQ DSL
jooqx leverages the power of typesafe SQL from jOOQ DSL and running on SQL connection in a reactive and non-blocking of SQL driver from Vert.x
Features
jooqx Provide uniform API for among
- Vertx Legacy SQL client
- Vertx Reactive SQL client
- Async jOOQ #33
jooqx supports:
- Lightweight and SPI
- Typesafe
- Native
jOOQ DSLAPI - Any database with
JDBCdriver and combine with legacy SQL - Any database with
Vert.x reactive client(JDBCPoolisreactive JDBC driverso almost databases should work properly) - JsonObject record
- Row/record transformation to
jOOQrecord - A unified format for
exceptionand able to replace/integrate seamlessly by your current application exception -
CRUDwith prepared query byjOOQ DSL - SQL Complex query with typesafe such as
join,with,having to, etc - SQL Batch insert/update/merge
- SQL Transaction
- [?] Nested transaction (not yet tested, but API is available)
- Transaction rollback condition
- Transaction annotation
- Procedure
- DAO
- [?] Resource Query Language (RQL) from rsql-jooq
- Rxified API
- Row streaming
- Publish/subscribe
- Test fixtures API to easy setup test for your application testing or produce a minimum reproducer
Usage
To use jooqx add the following dependency to the dependencies section of your build descriptor:
Maven(in yourpom.xml):
<dependency>
<groupId>io.github.zero88</groupId>
<artifactId>jooqx-core</artifactId>
<version>1.0.0</version>
</dependency>
<!-- For some SPI includes specific converter based on Vert.x database client -->
<dependency>
<groupId>io.github.zero88</groupId>
<artifactId>jooqx-spi</artifactId>
<version>1.0.0</version>
</dependency>
Gradle(in yourbuild.gradle):
dependencies {
api("io.github.zero88:jooqx-core:1.0.0")
// For some SPI includes specific converter based on Vert.x database client
api("io.github.zero88:jooqx-spi:1.0.0")
}
Hint
jooqx is only depended on 3 main libraries:
io.vertx:vertx-coreorg.jooq:jooqorg.slf4j:slf4j-api
Adding this jooqx library JAR will not automatically add a database driver JAR to your project. You should ensure that your project also has a suitable database driver as a dependency.
For example:
- With
legacy JDBCand connecting toMySQLdriver
dependencies {
api("mysql:mysql-connector-java:8.0.23")
// It is recommendation to use HikariCP instead of c3p0
api("com.zaxxer:HikariCP:4.0.2")
api("io.vertx:vertx-jdbc-client:4.0.2") {
exclude("com.mchange")
}
api("io.github.zero88:jooqx-core:1.0.0")
}
- With
reactive PostgreSQLclient
dependencies {
api("io.vertx:vertx-pg-client:4.0.2")
api("io.github.zero88:jooqx-core:1.0.0")
}
- With
reactive JDBCclient andH2
dependencies {
api("com.h2database:h2:1.4.200")
// Agroal pool - Default in Vertx SQL client - Not yet has alternatives
api("io.agroal:agroal-pool:1.9")
api("io.vertx:vertx-sql-client:4.0.2")
api("io.github.zero88:jooqx-core:1.0.0")
}
Getting started
Assume you know how to use jOOQ code generation and able to generate your database schema.
You can use: Maven jOOQ codegen or Gradle jOOQ plugin
Better experimental is my integtest to see some jOOQ generation setup
Simple query
Reactive SQL client
PgConnectOptions connectOptions = new PgConnectOptions()
.setPort(5432)
.setHost("the-host")
.setDatabase("the-db")
.setUser("user")
.setPassword("secret");
// Pool options
PoolOptions poolOptions = new PoolOptions().setMaxSize(5);
// Create the client pool
PgPool client = PgPool.pool(connectOptions, poolOptions);
// Init jOOQ DSL context
DSLContext dslContext = DSL.using(new DefaultConfiguration().set(SQLDialect.POSTGRES));
// Build jooqx reactive sql executor
ReactiveJooqx jooqx = ReactiveJooqx.builder().vertx(vertx)
.dsl(dslContext)
.sqlClient(client)
.build();
// It is table class in database that is generated by jOOQ
Authors table = DefaultCatalog.DEFAULT_CATALOG.DEFAULT_SCHEMA.AUTHOR;
// Start query
SelectConditionStep<Record1<Integer>> query = jooqx.dsl()
.selectCount()
.from(table)
.where(table.COUNTRY.eq("USA"));
jooqx.execute(query, DSLAdapter.fetchCount(query.asTable()), ar -> System.out.println(ar.result()));
//output: 10
Legacy SQL client
// Init JDBCClient legacy client
SQLClient client = JDBCClient.create(vertx, config);
DSLContext dslContext = DSL.using(new DefaultConfiguration().set(SQLDialect.H2));
// Build jooqx legacy sql executor
LegacyJooqx jooqx = LegacyJooqx.builder()
.vertx(Vertx.vertx())
.dsl(dslContext)
.sqlClient(client)
.build();
// It is table class in database that is generated by jOOQ
Authors table = DefaultCatalog.DEFAULT_CATALOG.DEFAULT_SCHEMA.AUTHOR;
// Start query
jooqx.execute(jooqx.dsl().selectFrom(table), DSLAdapter.fetchMany(table), ar -> {
// It is AuthorRecords class that is generated by jOOQ
AuthorRecords record = ar.result().get(0);
System.out.println(record.getId());
System.out.println(record.getName());
});
//output: 1
//output: zero88
Interesting? Please checkout more features, here, usage and java-doc
Contributions
Please go through on develop for how to setup environment
Short Note
Might you know, vertx-jooq is another library for vert.x and jOOQ integration was started in a longtime ago.
I already used it, too. However, in a while I realized it doesn't meet my requirements for dynamic SQL. Also, this library is narrow a power of jOOQ DSL and SQL feature likes paging, batch, transaction, complex query with join/having/group, etc. It is quite hard to extend due to mostly depends on Data Access Object (DAO) pattern, and the generation time.
Always have a different way to make it better, then I made this library to bring jOOQ DSL is first citizen and seamlessly combined with Vert.x SQL client.