You can run queries with jOOQ by creating a DSLContext instance. One way of getting this instance is with the DSL.using method:
Connection connection = pool.getConnection();
DSLContext dslContext = DSL.using(connection);
With this, you can easily run queries using the fluent API offered by jOOQ. For example, to get all the rows in the messages table, you can use the following:
List<MessagesRecord> messages = dslContext.select()
.from(MESSAGES)
.fetchInto(MessagesRecord.class);
The MessagesRecord class and MESSAGES instance are provided by the code generated by jOOQ. This makes the previous query type-safe.
If, for some reason, your database schema changes, you'll get a compilation error and will have the chance to fix the problem before deploying it to production. This is one of the strengths of jOOQ.
From here, you can imagine how to implement a MessageRepository class using jOOQ. Here's the solution to such a puzzle, though:
public class MessageRepository {
public static List<MessagesRecord> findAll() {
try {
return JooqService.runWithDslContext(context ->
context.select()
.from(MESSAGES)
.fetchInto(MessagesRecord.class)
);
} catch (SQLException e) {
e.printStackTrace();
return Collections.emptyList();
}
}
public static void save(MessagesRecord message) {
try {
JooqService.runWithDslContext(context ->
context.insertInto(MESSAGES, MESSAGES.CONTENT)
.values(message.getContent())
.execute()
);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
And the convenient JooqService.runWithDslContext method:
public class JooqService {
...
public static <T> T runWithDslContext(Function<DSLContext, T>
function) throws SQLException {
try (Connection connection = pool.getConnection(); DSLContext
dslContext = DSL.using(connection)) {
T t = function.apply(dslContext);
return t;
}
}
}
If you are interested in jOOQ, you might want to evaluate Ebean (http://ebean-orm.github.io) and Querydsl (http://www.querydsl.com), which are both ORM frameworks that also allow you to implement type-safe queries in Java.