jOOQ.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.
59 Sep 17, 2022
666 Dec 21, 2022
5 Aug 21, 2019
0 Jul 11, 2022
5.3k Jan 4, 2023
1 Jan 9, 2022
13.3k Jan 8, 2023
33 Jan 4, 2023
12 Oct 19, 2022
7 May 15, 2022
13 Oct 10, 2022
12 Jul 30, 2022
89 Dec 27, 2022
3.3k Dec 31, 2022
6.2k Dec 31, 2022
100 Oct 18, 2022
962 Dec 31, 2022
9.4k Dec 29, 2022