Skip to content

Instantly share code, notes, and snippets.

@stantonk
Last active July 8, 2022 15:06
Show Gist options
  • Save stantonk/f09b632d18c776fd414aee08ccfccbb2 to your computer and use it in GitHub Desktop.
Save stantonk/f09b632d18c776fd414aee08ccfccbb2 to your computer and use it in GitHub Desktop.
JDBI + jOOQ. Combine SQL Builder of jOOQ with all the awesomeness of JDBI, but avoid using jOOQ's codegen, having to keep it up to date with schema changes (just update the single query that's changed) or overcomplicating your build process. jOOQ never touches the database.
// see:
// http://jdbi.org/fluent_queries/
// http://www.jooq.org/doc/3.7/manual/getting-started/use-cases/jooq-as-a-standalone-sql-builder/
// Your Java Bean
public static class Reminder {
private long id;
private long customerId;
public Reminder(long id, long customerId) {
this.id = id;
this.customerId = customerId;
}
public long getId() { return id; }
public long getCustomerId() { return customerId; }
}
// Dynamic SQL generation with conditional logic+params
// Executed with JDBI Fluent Queries
// Mapped cleanly to Java Bean
DBI dbi = new DBI(//...construct one however makes sense for your app);
Set<Integer> authorIds = Sets.newHashSet(99, 98, 97);
// execute dynamically generated SQL using jOOQ but executed with JDBI Fluent Queries
List<Reminder> reminders = jdbi.inTransaction((tx, status) -> {
DSLContext create = DSL.using(SQLDialect.MYSQL);
SelectConditionStep<?> select = create
.select(field("reminder.id as id"), field("reminder.customer_id as customer_id"))
.from(table("reminder"))
.join(table("reminder_audit"))
.on(field("reminder_audit.id").equal(field("reminder.audit_id")))
.and(field("reminder_audit.is_current").equal(UByte.valueOf(1)))
.where(field("reminder.customer_id").equal(param("customerId")))
.and(field("reminder.group_id").equal(param("groupId")));
if (authorIds != null && !authorIds.isEmpty()) {
select = select.and(field("reminder.login_id").in(authorIds));
}
String sql = select.getSQL(ParamType.NAMED_OR_INLINED);
// print the sql
System.out.println(sql);
/*
select reminder.id as id, reminder.customer_id as customer_id
from reminder
join reminder_audit on (reminder_audit.id = reminder.audit_id and reminder_audit.is_current = 1)
where (
reminder.customer_id = :customerId and
reminder.group_id = :groupId and
reminder.login_id in (97, 98, 99))
*/
// execute the query with JDBI
return tx.createQuery(sql)
.bind("customerId", 5)
.bind("groupId", 42)
.map(new BeanMapper<Reminder>(Reminder.class))
.list();
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment