Jooq configuration with Play

[Alex Mueller]

I am using a sample Play app that uses Play-Authenticate with Java and MySQL. It is working for me. The sample uses ebean for the authentication/authorization classes. I want to use Jooq. I have used Jooq to generate my entities and dao classes at this point.

What is the best practice for using Jooq within my controllers or service classes?

The examples I see are trivial and create the database connection just before they run the example. I do not want to do that in my controller each time. 

How should I handle transactions, rollbacks, errors, etc? Are there any samples with these database and Jooq context classes already created that I can use?

Thanks for any advice.

[Alex Mueller]

In Play documentation, "It is important to note that resulting Connections are not automatically disposed at the end of the request cycle. In other words, you are responsible for calling their close() method somewhere in your code so that they can be immediately returned to the pool."

Where is the best place to do this? Override some function in Global, or somewhere else?

Thanks for any suggestions.

[Manuel Bernhardt]

Just to check, are you are of this blog series:

http://blog.jooq.org/2015/12/03/reactive-database-access-part-1-why-async/

and especially the comment at the end of the 3rd part where Sebastian Millies has reimplemented the example in Java?

http://blog.jooq.org/2016/01/14/reactive-database-access-part-3-using-jooq-with-scala-futures-and-actors/

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to play-framewor...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/play-framework/683d0723-1a09-4091-994b-9d0de5b80e70%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Alex Mueller]

I have looked as those examples. I am looking at a few examples that I can find.

I feel like I have to use this block of code everywhere I want to query, only because I am not sure how to do it differently, or what the best practice is.

try (Connection conn = DB.getConnection()) { 

    DSLContext dslContext = DSL.using(conn, SQLDialect.MYSQL);

    // Example 2: Custom Pojos

    playersList = dslContext.select().from(Players.PLAYERS).fetch().into(Players.class);

    return ok(players.render(playersList));

}

catch (Exception e) {

    e.printStackTrace();

}   

// return errors

If I am going to use Jooq within any Java web framework, I want to understand how to deal with transactions, commits, and rollback if needed. I believe Jooq may do some if not all of this for me, looking here, http://www.jooq.org/doc/3.7/manual/sql-execution/transaction-management. 

Another desire I have is the try-catch where I have to create that connection every time. Is there a more elegant way to do this throughout my application? Assuming every controller method of mine goes to the database to select/insert/update/delete, then I need to have this try-catch everywhere in order to close the database connection with Jooq?

Thanks for the help.

[Alex Mueller]

This is what I have so far. I am not sure if it is all necessary as Jooq may handle this for me. I am using Jooq and MySQL.

This code below will handle opening/closing of my connections and getting a connection to my clients. I have done nothing to handle transactions/commits/rollbacks at this point other than default to what Jooq provides.

public class JooqConnectionProvider implements ConnectionProvider {

    Database db;

    private Connection connection = null;

    public JooqConnectionProvider(Database database){

        db = database;

    }

    @Override

    public java.sql.Connection acquire() throws DataAccessException {

        try {

            connection = db.getConnection();

            return connection;

        }

        catch (DataAccessException ex) {

            throw new DataAccessException("Error getting connection from data source", ex);

        }

    }

    @Override

    public void release(java.sql.Connection releasedConnection) throws DataAccessException {

        if (connection != releasedConnection) {

            throw new IllegalArgumentException("Expected " + connection + " but got " + releasedConnection);

        }

        try {

            connection.close();

            connection = null;

        } catch (SQLException e) {

            throw new DataAccessException("Error closing connection " + connection, e);

        }

    }

}

public class JooqContextProvider {

    @Inject

    Database db;

    public DSLContext dsl() {

        return DSL.using(

            new JooqConnectionProvider(db),

            SQLDialect.MYSQL);

    }

}

// Then in my code, I inject the JooqContextProvider and use it.

@Inject

JooqContextProvider jooq;

public List<Players> fetchAll(){

    return jooq.dsl()

        .select()

        .from(Players.PLAYERS)

        .fetchInto(Players.class);

}

This is inspiration ifrom https://github.com/jaiew/play-jooq and how they do it.

[Lukas Eder]

Hi Alex,

Lukas from the jOOQ team here. A couple of remarks:

1. Try to avoid state in your ConnectionProvider

I've seen that you've followed a third-party suggestion on how to integrate jOOQ with Play. That suggestion includes caching the Connection in the ConnectionProvider implementation, only to implement some jOOQ integration test (Connection acquired == Connection released). We have that covered on our side already in dozens of integration tests, so no need for that check ;) Best just remove the Connection and avoid weird thread safety issues later in your project (perhaps only discovered in production).

2. The jOOQ transaction API

jOOQ indeed ships with a simple transaction API, that uses JDBC transactions as a backing, out-of-the-box implementation (although you can rewire the implementation to use Spring or Java EE, or if Play has its own, you can use that). In Java, this API can be used easily as follows:

return ok(players.render(

    DSL.using(connectionProvider, SQLDialect.MYSQL)

       .transactionResult(configuration -> {

           DSL.using(configuration).selectFrom(PLAYERS).fetchInto(Players.class);

       })

));

3. Abstracting your wrapper in your own custom API

Now, you obviously don't want to type all that glue code all the time for every query. So, why not implement something like:

public static <E> List<E> fetch(ResultQuery<?> query, Class<E> type) {

    return

    DSL.using(connectionProvider, SQLDialect.MYSQL)

       .transactionResult(configuration -> {

           return DSL.using(configuration).fetch(query).into(type);

       })

));

Of course, there's even more room for abstraction in your API, I'll leave that up to you.

If you have any additional questions, just let me know, I'll be happy to help where I can.

Cheers,

Lukas

[Alex Mueller]

Lukas, 

Thanks for your suggestions. I have a few questions to clarify, to make sure to understand it.

1. Try to avoid state in your connection provider

If I am following you correctly, "best to just remove the connection" and "caching the connection" and "we have that covered." Is there any reason for me to have my JooqConnectionProvider at all then? Should I just remove the class entirely because Jooq is already doing this for me?

2. The JOOQ transaction API and 3. Abstractice your wrapper...

I can use an approach like this where I have similar queries, such as the fetch me all of type E, as you have shown. I will try to use that where I can.

What I am trying to do is understand JOOQ enough to use it correctly within my app and make it reusable for my project. 

If I have a controller with 5 methods in it, I will be calling JOOQ (via a service I write) five times. Every example I see for JOOQ has that connectionProvider (or configuration) and SQLDialect passed into the DSL.using(). I believe I need to create that connectionProvider (or configuration, or connection) each time I query with JOOQ, is that correct? As in, I cannot create a connectionProvider (or configuration) once in my controller's constructor and use it for all five of my method calls? I believe I need to new that up each time. That is where the abstraction and reusability examples will help.

Also, for DSL.using(configuration), what is a good example of how to new up that configuration? I have not found a good example of that, and this is what I have below.

public DSLContext dsl() {

    return DSL.using(

        new JooqConnectionProvider(db),

        SQLDialect.MYSQL);

}

public PlayerDao playerDao(){

    Configuration config = new DefaultConfiguration()

        .set(new JooqConnectionProvider(db))

        .set(SQLDialect.MYSQL);

    return new PlayerDao(config);

}

If I remove my ConnectionProvider class, I would have this class, as my JOOQ data wrapper.

public class JooqContextProvider {

    @Inject

    Database db;

    public DSLContext dsl() {

        return DSL.using(db.getConnection(),SQLDialect.MYSQL);

    }

    public PlayerDao playerDao(){

        Configuration config = new DefaultConfiguration()

            .set(db.getConnection())

            .set(SQLDialect.MYSQL);

        return new PlayerDao(config);

    }

}

// Then I would call into this class like this...

return jooq.playerDao().fetchOneByPlayerId(id);

return jooq.dsl().select().from(PLAYERS_TABLE).fetchInto(Player.class);

Thanks again for your help,

Alex

I do not want to write DSL.using(connectionProvider, SQLDialect.MYSQL) five tims. 

[Edgar Twigg]

I'm also interested in adopting jOOQ with Play, and I'm following this closely.  There's lots of snippets with questionable quality, but no definitive best practices.  It seems that the group of experts on jOOQ and Play 2 are disjoint for now.  It would be amazing if a jOOQ expert and a Play expert took a day to make a canonical example app on GitHub.  Interested parties could watch to learn about any changes in recommended best-practices as new versions come out.  jOOQ's db abstraction and Twirl's templates seem like the best available combination of typesafety without magic in the marketplace.  I'm still on Play 1, but I can't wait to find time to switch to jOOQ and Play 2.

ᐧ

--
You received this message because you are subscribed to a topic in the Google Groups "play-framework" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/play-framework/tXvA76ZQsJs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to play-framewor...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/play-framework/3c054975-5f7c-4620-9ac4-6d5abb4bbd7a%40googlegroups.com.

[Lukas Eder]

Hi Edgar,

Indeed the jOOQ and Play teams are disjoint. This is probably due to the Play vendor also trying to promote the second-best Scala/SQL API commonly referred to as Slick - so they don't want to help with a competitor product integration :) We would love to contribute more to Lightbend, but thus far, we have not received any positive reply on this matter.

One of the most promising third party integrations has been written by Manuel Bernhardt in collaboration with us. Here's a blog post series that also advertises his book:

- http://blog.jooq.org/2015/12/03/reactive-database-access-part-1-why-async

- http://blog.jooq.org/2015/12/16/reactive-database-access-part-2-actors

- http://blog.jooq.org/2016/01/14/reactive-database-access-part-3-using-jooq-with-scala-futures-and-actors

His examples will be kept up to date

Thanks for the hint about Twirl. That looks indeed like a very interesting approach. Reminds me of JSP, which I still think is one of the best technologies for webapps. Maybe, we can come up with an example in the jOOQ GitHub repository...

To view this discussion on the web visit https://groups.google.com/d/msgid/play-framework/CADRTJga965et2d36VX%2B%2ByeRUjzrTLUJM_UDy2te%2BJnsjSoqsfA%40mail.gmail.com.

[Lukas Eder]

Hi Alex,

I will comment inline...

2016-03-26 23:02 GMT+01:00 Alex Mueller <bamu...@gmail.com>:

Lukas, 

Thanks for your suggestions. I have a few questions to clarify, to make sure to understand it.

1. Try to avoid state in your connection provider

If I am following you correctly, "best to just remove the connection" and "caching the connection" and "we have that covered." Is there any reason for me to have my JooqConnectionProvider at all then? Should I just remove the class entirely because Jooq is already doing this for me?

You will need to implement that bridge between jOOQ's ConnectionProvider SPI and Play's Database at some point. In the Java ecosystem, most people use a javax.sql.DataSource as a standard means to provide JDBC Connections to libraries like jOOQ. If you do have a DataSource, you can pass that directly to DSL.using(), or to the jOOQ Configuration.

The reason why you might need this implementation is because it will govern the complete lifecycle of your Connection, i.e. obtaining it from your Database reference, and then calling Connection.close() when it is no longer needed.

2. The JOOQ transaction API and 3. Abstractice your wrapper...

I can use an approach like this where I have similar queries, such as the fetch me all of type E, as you have shown. I will try to use that where I can.

What I am trying to do is understand JOOQ enough to use it correctly within my app and make it reusable for my project. 

If I have a controller with 5 methods in it, I will be calling JOOQ (via a service I write) five times. Every example I see for JOOQ has that connectionProvider (or configuration) and SQLDialect passed into the DSL.using(). I believe I need to create that connectionProvider (or configuration, or connection) each time I query with JOOQ, is that correct?

You can, but if it is stateless (i.e. no cached Connection), then you can also register it globally somewhere and share it. In fact, you can share the entire DSLContext reference instead of creating it all the time. That's how most people usually do it, e.g. when using Spring or Java EE's depedency injection mechanisms. Just like you already @Inject Database db; you can also @Inject DSLContext ctx;

As in, I cannot create a connectionProvider (or configuration) once in my controller's constructor and use it for all five of my method calls? I believe I need to new that up each time. That is where the abstraction and reusability examples will help.

Also, for DSL.using(configuration), what is a good example of how to new up that configuration? I have not found a good example of that, and this is what I have below.

public DSLContext dsl() {

    return DSL.using(

        new JooqConnectionProvider(db),

        SQLDialect.MYSQL);

}

public PlayerDao playerDao(){

    Configuration config = new DefaultConfiguration()

        .set(new JooqConnectionProvider(db))

        .set(SQLDialect.MYSQL);

    return new PlayerDao(config);

}

That would certainly work. It's a bit of extra work for the garbage collector, but that won't really hurt much.

 

If I remove my ConnectionProvider class, I would have this class, as my JOOQ data wrapper.

public class JooqContextProvider {

    @Inject

    Database db;

    public DSLContext dsl() {

        return DSL.using(db.getConnection(),SQLDialect.MYSQL);

    }

    public PlayerDao playerDao(){

        Configuration config = new DefaultConfiguration()

            .set(db.getConnection())

            .set(SQLDialect.MYSQL);

        return new PlayerDao(config);

    }

}

// Then I would call into this class like this...

return jooq.playerDao().fetchOneByPlayerId(id);

return jooq.dsl().select().from(PLAYERS_TABLE).fetchInto(Player.class);

That approach won't work, because no one will close that JDBC Connection, unless you remember to do so explicitly every time. When you pass the JDBC Connection directly to jOOQ's Configuration, this means that you have to manage the JDBC Connection lifecycle (i.e. the close() call). Sometimes, that's desireable, e.g. when most of the work is done with JDBC directly, but 1-2 calls are made via jOOQ.

In your case, however, you want jOOQ to call close() on your Connection either:

- After each statement

- After each transaction

I do not want to write DSL.using(connectionProvider, SQLDialect.MYSQL) five tims. 

Sure, I understand. You don't have to. I hope these explanations already helped.

Best,

Lukas

[Edgar Twigg]

As a java 8 fan who's hoping not to have any Scala in our stack besides a teensy bit for Twirl templates, Slick isn't an option.  Also, I believe jOOQ is as far ahead of Anorm as Play 2's Twirl is ahead of Play 1's groovy templates.

> One of the most promising third party integrations has been written by Manuel Bernhardt in collaboration with us... His examples will be kept up to date.

Thanks!

> Maybe, we can come up with an example in the jOOQ GitHub repository...

I would love that!  But I sure understand we're all busy :)  jOOQ and Twirl just seem like such a great pairing though, can't wait to see them better supported as a couple.

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/play-framework/CAB4ELO79OJWYABH9SUHZnUu%2Bs%3DJoPLjkTZFjTVXYfXBrWRWr9w%40mail.gmail.com.

[Lukas Eder]

2016-03-31 19:28 GMT+02:00 Edgar Twigg <edgar...@gmail.com>:

As a java 8 fan who's hoping not to have any Scala in our stack besides a teensy bit for Twirl templates, Slick isn't an option.  Also, I believe jOOQ is as far ahead of Anorm as Play 2's Twirl is ahead of Play 1's groovy templates.

I understand. Even for Scala projects, using Slick is a rather challenging decision as it is very opinionated about your database access patterns, from what I hear. I absolutely agree that jOOQ is far ahead of any competition :)

> Maybe, we can come up with an example in the jOOQ GitHub repository...

I would love that!  But I sure understand we're all busy :)  jOOQ and Twirl just seem like such a great pairing though, can't wait to see them better supported as a couple.

Well, we are busy indeed, but having had a quick glance at Twirl, I definitely agree with you. It would be a really great pairing!

[Will Sargent]

On Thu, Mar 31, 2016 at 7:24 AM, Lukas Eder <lukas...@gmail.com> wrote:

Hi Edgar,

Indeed the jOOQ and Play teams are disjoint. This is probably due to the Play vendor also trying to promote the second-best Scala/SQL API commonly referred to as Slick - so they don't want to help with a competitor product integration :) We would love to contribute more to Lightbend, but thus far, we have not received any positive reply on this matter.

Play really depends on a strong network of contributors, so anything you can do is great.

The problem right now is maintainability: Play comes with no less than four ORMs documented -- EBean, Anorm, JPA and Slick -- and already has issues where those projects (and their documentation) release on a different schedule than Play does, not to mention the confusion that results from having so many to choose from.  For now, the best way to ensure your project shows up in Play's documentation is to add really good tutorials and example projects to the tutorials page:

https://www.playframework.com/documentation/2.5.x/Tutorials#2.5.x

You can do this by editing this page and submitting a pull request to the following markdown document:

https://github.com/playframework/playframework/blob/master/documentation/manual/gettingStarted/Tutorials.md

and then it'll show up in the next release.

Will.

[Lukas Eder]

Hi Will

Thank you very much for this info. Indeed, I perfectly understand the complexity of keeping track of all these dependencies when maintaining a platform. I suspect that's just the platform maintainer's life :) I gues this will be inevitable for the Play / Lagom teams.

The team at Spring Boot is doing similar work. For instance, they have integrated a short Spring Boot + jOOQ tutorial in the manual (https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-jooq.html) and keep updating jOOQ versions all the time as soon as we release them.

I'm very happy to look into providing an initial tutorial for your documentation, either myself, or perhaps together with Manuel Bernhardt, who has already included examples in his book Reactive Web Applications (https://manning.com/books/reactive-web-applications).

I'll get back in touch with a PR as soon as possible.

Thanks,

Lukas

--

You received this message because you are subscribed to a topic in the Google Groups "play-framework" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/play-framework/tXvA76ZQsJs/unsubscribe.
To unsubscribe from this group and all its topics, send an email to play-framewor...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/play-framework/CAJmgB63p5vtUnhpx55ZuRaVtGD_RkEt79LUgKvM1eS9q0U7P-A%40mail.gmail.com.