I've put together a simple demo project at https://bitbucket.org/marshallpierce/ktor-demo/src that shows how to use jOOQ with postgres, along with a number of other useful libraries that approximate a good starting point for "a service that uses a database". (Feedback is welcome BTW! I'm new to jOOQ as well, so I'm probably missing some idioms.)
https://bitbucket.org/marshallpierce/ktor-demo/src/459b75c38a68916ae52307073741da2a0d9c756b/src/main/kotlin/org/mpierce/ktordemo/KtorDemo.kt#lines-41 shows how to create a HikariCP connection pool with PostgreSQL and create a jOOQ DSLContext with it. HTH, Marshall On 05/03/2018 09:42 AM, David Hoffer wrote: > Yes can you give me an example of a proper way to set this up with > connection pool? I've not used JOOQ before and don't see any examples > so far other then just this DefaultConfiguration. I'd like to see a > more enterprise ready example. > > Thanks, > -Dave > > On Thu, May 3, 2018 at 8:37 AM, Lukas Eder <[email protected] > <mailto:[email protected]>> wrote: > > Hi David, > > Yeah, there you go... You're creating PostgreSQL connections by > calling new PgConnection(), but you're not managing those > connections. There can be many reasons why such a connection is > closed. jOOQ doesn't close it if you're passing it to > DefaultConnection.set(Connection), but perhaps it closes itself on > some error? You can probably figure this out somehow from the logs > or by setting breakpoints, or by intercepting the close call (you > could wrap your connection with jOOQ's DefaultConnection and > intercept the close call there, printing the stack trace) > > I definitely recommend you use a connection pool and manage the > connections correctly, though. > > Thanks, > Lukas > > > 2018-05-03 16:26 GMT+02:00 David Hoffer <[email protected] > <mailto:[email protected]>>: > > Hi Lukas, > > Sure I can post some code...here is our JOOQ configuration. > This is probably where we are doing things wrong as we aren't > doing anything with the SPIs in the Configuration, we are just > using defaults. We call this method several times when our > application starts and pass the Connection instances to several > DAO instances. Note this is not a Singleton but could be if > that is better. > > | > > > publicConfigurationgetConfiguration()throwsOdinDomainConfigurationException > { > try > { > finalInputStreamresourceAsStream > =getClass().getResourceAsStream("/conf/config.json"); > JsonObjectjsonObject > =newJsonObject(newString(IOUtils.toByteArray(resourceAsStream))); > > finalStringdbHost =jsonObject.getString("db.host","odindb"); > finalIntegerdbPort =jsonObject.getInteger("db.port",5432); > finalStringdbUser > =jsonObject.getString("db.user","odin_owner"); > finalStringdbDatabase > =jsonObject.getString("db.database","odindb"); > finalStringdbEncryptedPassword > =jsonObject.getString("db.password.encrypted"); > > StringdbPassword > =encryptEngine.decryptString(dbEncryptedPassword); > > finalDefaultConfigurationconfiguration > =newDefaultConfiguration(); > configuration.set(SQLDialect.POSTGRES_9_4); > > finalPropertiesproperties =newProperties(); > properties.setProperty("password",dbPassword); > > configuration.set(newPgConnection( > newHostSpec[]{newHostSpec(dbHost,dbPort)}, > dbUser, > dbDatabase, > properties, > > > String.format("jdbc:postgresql://%s:%d/%s",dbHost,dbPort,dbDatabase))); > returnconfiguration; > } > catch(Exceptione) > { > thrownewOdinDomainConfigurationException("Failed to > connect to the database",e); > } > } > > | > > Thanks, > -Dave > > > On Thursday, May 3, 2018 at 8:05:10 AM UTC-6, Lukas Eder wrote: > > Hi David, > > Well, perhaps the information about the third party plugin > wasn't strictly necessary to get to the bottom of the issue. > It's always helpful to provide an MVCE (minimal complete > verifiable example) > https://stackoverflow.com/help/mcve > <https://stackoverflow.com/help/mcve> > > Such examples with code usually beat prose any time :) > > 2018-05-03 15:51 GMT+02:00 David Hoffer <[email protected]>: > > Okay I can get with the author of the customized code > generator for more specifics but I believe that just > adds a bit of functionality on top of standard JOOQ > generated code. I understand the plugin itself is from > JOOQ. > > Specifically it is just using the standard JOOQ > Configuration class and all the DAOs extend from DAOImpl > which too is standard JOOQ. > > So for those two classes how should things be structured > so that we never loose DB connection? E.g. what is the > lifecycle of those classes/instances? > > > They don't have a lifecycle. All lifecycle objects are > inside of the Configuration. > > > Currently we are assuming we can create any number of > DefaultConfiguration instances and they each last > indefinitely and we can use those instances in any > number of DAOs which also last indefinitely. However is > that correct? > > > Could be. Neither the configuration itself, nor the DAOs > have anything to do with lifecycles. The important SPIs are > *inside* of the Configuration > > > E.g. we are not expecting any lifecycle/termination of > either objects. However perhaps that is not the case or > we need more than a DefaultConfiguration? Also should > our DefaultConfiguration instance be a Singleton? > Currently it is not. Also are there any DB connection > pools we need to be concerned with? > > > You're completely responsible yourself for those connection > pools. jOOQ works with JDBC Connection or DataSource > instances. jOOQ doesn't care how they're created / > maintained. If you want, jOOQ works just like JDBC itself. > > > We are just looking for guidance on how to bootstrap > JOOQ's DB configuration with DAOImpl instances. Are > there code examples of this? > > > There's the manual: > https://www.jooq.org/doc/latest/manual/sql-execution/daos > <https://www.jooq.org/doc/latest/manual/sql-execution/daos> > > But you won't get anything out of there that I haven't > mentioned above. If you can make your verticles work with > JDBC, they can work with jOOQ as well. > > This *should* be really simple, but without seeing any code > / configuration, it's very difficult to asses where it went > wrong. Most probably outside of jOOQ, though. > > I hope this helps, > Lukas > > -- > You received this message because you are subscribed to the > Google Groups "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from > it, send an email to [email protected] > <mailto:[email protected]>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. > > > -- > You received this message because you are subscribed to the Google > Groups "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, > send an email to [email protected] > <mailto:[email protected]>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. > > > -- > You received this message because you are subscribed to the Google > Groups "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send > an email to [email protected] > <mailto:[email protected]>. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
