jooqx-integtest

Vert.x jOOQ DSL

License

License

Categories

Categories

jOOQ Data Databases
GroupId

GroupId

io.github.zero88
ArtifactId

ArtifactId

jooqx-integtest
Last Version

Last Version

1.0.0
Release Date

Release Date

Type

Type

pom.sha512
Description

Description

jooqx-integtest
Vert.x jOOQ DSL
Project URL

Project URL

https://github.com/zero88/vertx-jooq-dsl
Source Code Management

Source Code Management

https://github.com/zero88/vertx-jooq-dsl

Download jooqx-integtest

Dependencies

There are no dependencies for this project. It is a standalone project that does not depend on any other jars.

Project Modules

There are no modules declared in this project.

jOOQ.x - Vertx jOOQ DSL

build GitHub release (latest SemVer) Sonatype Nexus (Releases) Sonatype Nexus (Snapshots) Lines of Code Coverage Maintainability Rating Reliability Rating Security Rating Quality Gate Status

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

jooqx supports:

  • Lightweight and SPI
  • Typesafe
  • Native jOOQ DSL API
  • Any database with JDBC driver and combine with legacy SQL
  • Any database with Vert.x reactive client(JDBCPool is reactive JDBC driver so almost databases should work properly)
  • JsonObject record
  • Row/record transformation to jOOQ record
  • A unified format for exception and able to replace/integrate seamlessly by your current application exception
  • CRUD with prepared query by jOOQ 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 your pom.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 your build.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-core
  • org.jooq:jooq
  • org.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 JDBC and connecting to MySQL driver
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 PostgreSQL client
dependencies {
    api("io.vertx:vertx-pg-client:4.0.2")
    api("io.github.zero88:jooqx-core:1.0.0")
}
  • With reactive JDBC client and H2
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.

Versions

Version
1.0.0