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
}

Answer 1

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 :)