Inject java.sql.Connection into controller for Ninja Framework

Question

I want to use JOOQ to access my database from the Ninja Framework. How can I get a JDBC connection from a controller?

Here's resources I found that didn't quite work.

How to retrieve the datasource used by a persistence unit programmatically - Tedious set of steps to get the connection from an EntityManager.

http://blog.jooq.org/2015/05/26/type-safe-queries-for-jpas-native-query-api/ - works by building a query in JOOQ and passing to EntityManager.createNativeQuery. It's functional, but it's not as nice as just having the connection.

Could I inject the connection into a controller like so:

public Result myController(@DBConnection Connection connection) {
    List<String> articles = DSL.using(connection).selectFrom(ARTICLE).fetch(ARTICLE.TITLE);
    return Results.html().render("template", articles);
}

DropWizards has a plugin that looks like a winner: https://github.com/benjamin-bader/droptools/tree/master/dropwizard-jooq

public BlogPost getPost(@QueryParam("id") int postId, @Context DSLContext database) {
    BlogPostRecord post = database
        .selectFrom(POST)
        .where(POST.ID.equal(postId))
        .fetchOne();

    // do stuff
}

Is there any way to get a hold of a `DataSource` in ninja framework? — Lukas Eder, Dec 28 '15 at 08:00
Not that I could tell. It seems like EntityManager is the only easily available one. — Joe, Dec 28 '15 at 08:01

score 2 · Answer 1 · edited May 23 '17 at 12:23

2

Short of any option to retrieve a JDBC Connection or DataSource from ninja framework directly, the standard approach should be to "unwrap" it from the EntityManager:

Connection connection = em.unwrap(Connection.class);

See also: How to get DataSource or Connection from JPA2 EntityManager in Java EE 6

A Hibernate-specific approach is documented here: How to retrieve the datasource used by a persistence unit programmatically

edited May 23 '17 at 12:23

Community

1
1

answered Dec 28 '15 at 09:01

Lukas Eder

211,314
129
689
1,509

I got a NPE with this one, but I can't figure out why. – Joe Dec 28 '15 at 20:19

score 2 · Accepted Answer · answered Dec 28 '15 at 19:43

Following up on @LukasEder's answer this is the approach:

    HibernateEntityManagerFactory hibernateEntityManagerFactory = ((EntityManagerImpl) entityManager).getFactory();
    SessionFactoryImpl sessionFactoryImpl = (SessionFactoryImpl) hibernateEntityManagerFactory.getSessionFactory();
    C3P0ConnectionProvider c3P0ConnectionProvider = (C3P0ConnectionProvider) sessionFactoryImpl.getConnectionProvider();

    Connection connection = c3P0ConnectionProvider.getConnection();

This is obviously very very strange and bad code.

A clean solution is to provide access to Connection / DataSource by Ninja directly (separating the connection pool from Hibernate or any implementation). That is not too hard and is partly done in the ebeans plugin. Let's discuss that on our mailing list if you are interested in contributing code :)

This is the approach I went with. I used field injection to put it in ApplicationController. I'll shoot you a message on the mailing list, this seems bite-sized enough to get started with. — Joe, Dec 28 '15 at 20:19
Mailing list thread: https://groups.google.com/forum/#!topic/ninja-framework/LztS40CVpFA — Joe, Dec 28 '15 at 20:31

Inject java.sql.Connection into controller for Ninja Framework

2 Answers2