jOOQ Programmatic configuration on a Spring Boot Application

[Durim Kryeziu]

Hello,

I'm very new to jOOQ library and I really like this library based on what I saw in the official web site.

I'm working on a Spring Boot application which I package as a war file to deploy to an external Tomcat Server.

First I want to use jOOQ only as a SQL query builder, and I have to connect to a database with many tables and don't want to generate sources every time because it takes a while to finish.

Is it possible, if so, then can you give me some hints or guide me how to do that ?
Thanks in advance

I want to use the Programmatic configuration to be able to access some environment variables for database credentials through Environment

So, I created a @RestController and @Autowired the Environment bean and exposed through a URL to be able to Generate the sources only when I want to:

@RestController
@RequestMapping("jooq/generate-sources")
public class CodeGeneratorController {

    private Environment env;

    @Autowired
    public CodeGeneratorController(Environment env) {
        this.env = env;
    }

    @RequestMapping(method = RequestMethod.POST)
    public ResponseEntity generateSources() throws Exception {

        Configuration configuration = new Configuration()
                .withLogging(Logging.INFO)
                .withJdbc(new Jdbc()
                        .withDriver("com.mysql.jdbc.Driver")
                        .withUsername(env.getRequiredProperty("DB_USERNAME"))
                        .withPassword(env.getRequiredProperty("DB_PASSWORD"))
                        .withUrl(env.getRequiredProperty("DB_URL")))
                .withGenerator(new Generator()
                        .withDatabase(new Database()
                                .withName("org.jooq.util.mysql.MySQLDatabase")
                                .withIncludes(".*")
                                .withInputSchema("my_schema"))
                        .withTarget(new Target()
                                .withEncoding("UTF-8")
                                .withPackageName("com.my.package.data.generated-sources")
                                .withDirectory("src/main/java")));

        GenerationTool.generate(configuration);

        return ResponseEntity.ok().build();
    }
}

Also jOOQ related I have these dependencies on my pom.xml file:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jooq</artifactId>
</dependency>

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen</artifactId>
</dependency>

Then when I try to start the application (with IntelliJ IDEA, already configured to run on Tomcat) I see this message:

2017-04-02 19:53:10.591  WARN 92073 --- [on(2)-127.0.0.1] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dslContext' defined in class path resource [org/springframework/boot/autoconfigure/jooq/JooqAutoConfiguration$DslContextConfiguration.class]: Post-processing of merged bean definition failed; nested exception is java.lang.StackOverflowError

And then this exception:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dslContext' defined in class path resource [org/springframework/boot/autoconfigure/jooq/JooqAutoConfiguration$DslContextConfiguration.class]: Post-processing of merged bean definition failed; nested exception is java.lang.StackOverflowError

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:526) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761) ~[spring-beans-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:866) ~[spring-context-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) ~[spring-context-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122) ~[spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]

at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:737) ~[spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]

at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:370) ~[spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]

at org.springframework.boot.SpringApplication.run(SpringApplication.java:314) ~[spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]

at org.springframework.boot.web.support.SpringBootServletInitializer.run(SpringBootServletInitializer.java:151) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]

at org.springframework.boot.web.support.SpringBootServletInitializer.createRootApplicationContext(SpringBootServletInitializer.java:131) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]

at org.springframework.boot.web.support.SpringBootServletInitializer.onStartup(SpringBootServletInitializer.java:86) [spring-boot-1.5.2.RELEASE.jar:1.5.2.RELEASE]

at org.springframework.web.SpringServletContainerInitializer.onStartup(SpringServletContainerInitializer.java:169) [spring-web-4.3.7.RELEASE.jar:4.3.7.RELEASE]

at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5240) [catalina.jar:8.0.33]

at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147) [catalina.jar:8.0.33]

at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725) [catalina.jar:8.0.33]

at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701) [catalina.jar:8.0.33]

at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717) [catalina.jar:8.0.33]

at org.apache.catalina.startup.HostConfig.manageApp(HostConfig.java:1696) [catalina.jar:8.0.33]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_60]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_60]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_60]

at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_60]

at org.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:300) [tomcat-coyote.jar:8.0.33]

at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819) [na:1.8.0_60]

at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801) [na:1.8.0_60]

at org.apache.catalina.mbeans.MBeanFactory.createStandardContext(MBeanFactory.java:484) [catalina.jar:8.0.33]

at org.apache.catalina.mbeans.MBeanFactory.createStandardContext(MBeanFactory.java:433) [catalina.jar:8.0.33]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_60]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_60]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_60]

at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_60]

at org.apache.tomcat.util.modeler.BaseModelMBean.invoke(BaseModelMBean.java:300) [tomcat-coyote.jar:8.0.33]

at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819) [na:1.8.0_60]

at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801) [na:1.8.0_60]

at javax.management.remote.rmi.RMIConnectionImpl.doOperation(RMIConnectionImpl.java:1471) [na:1.8.0_60]

at javax.management.remote.rmi.RMIConnectionImpl.access$300(RMIConnectionImpl.java:76) [na:1.8.0_60]

at javax.management.remote.rmi.RMIConnectionImpl$PrivilegedOperation.run(RMIConnectionImpl.java:1312) [na:1.8.0_60]

at javax.management.remote.rmi.RMIConnectionImpl.doPrivilegedOperation(RMIConnectionImpl.java:1404) [na:1.8.0_60]

at javax.management.remote.rmi.RMIConnectionImpl.invoke(RMIConnectionImpl.java:832) [na:1.8.0_60]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_60]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_60]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_60]

at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_60]

at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:323) [na:1.8.0_60]

at sun.rmi.transport.Transport$1.run(Transport.java:200) [na:1.8.0_60]

at sun.rmi.transport.Transport$1.run(Transport.java:197) [na:1.8.0_60]

at java.security.AccessController.doPrivileged(Native Method) [na:1.8.0_60]

at sun.rmi.transport.Transport.serviceCall(Transport.java:196) [na:1.8.0_60]

at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568) [na:1.8.0_60]

at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826) [na:1.8.0_60]

at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$256(TCPTransport.java:683) [na:1.8.0_60]

at java.security.AccessController.doPrivileged(Native Method) [na:1.8.0_60]

at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682) [na:1.8.0_60]

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[na:1.8.0_60]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[na:1.8.0_60]

at java.lang.Thread.run(Thread.java:745) ~[na:1.8.0_60]....

Hope I was clear explaining my problem
Thanks

[Lukas Eder]

Hi Durim,

Thanks for your message and for your nice words.

Let me comment inline

2017-04-02 19:13 GMT+01:00 Durim Kryeziu <durimk...@gmail.com>:

Hello,

I'm very new to jOOQ library and I really like this library based on what I saw in the official web site.

I'm working on a Spring Boot application which I package as a war file to deploy to an external Tomcat Server.

First I want to use jOOQ only as a SQL query builder, and I have to connect to a database with many tables and don't want to generate sources every time because it takes a while to finish.

Is it possible, if so, then can you give me some hints or guide me how to do that ?
Thanks in advance

We generally recommend to use Flyway for database migrations and then listen to Flyway-generated schema changes prior to regenerating jOOQ code. I've recently written a blog post about this:

https://blog.jooq.org/2014/06/25/flyway-and-jooq-for-unbeatable-sql-development-productivity/

Of course, it would work with any other database migration tool, just Flyway makes this very easy.

Another decision you should make is whether you want to re-generate the jOOQ classes at every build, or check them in to your version control system. I personally favour the latter (although I perfectly understand arguments in favour of the former), and then build a jooq-db.jar file, which you can then package in your war file. This should be rather easy to do with Maven.

Hmm, that looks like a Spring Boot related exception. I'd report that to:

https://github.com/spring-projects/spring-boot

From a high level, I'm not sure if there's a bug in that JooqAutoConfiguration class, or if you've made a mistake configuring it, though...

Hope this helps,

Lukas

[Durim Kryeziu]

Thanks for reply Lukas,

In my case, the best situation is to execute only when I want, unfortunately our database doesn't have versions neither we want to use Maven plugin. For me best is to expose like I tried to do and call that endpoint only when I want to call it.
Did I placed that code in the right place ?

I also disabled the JooqAutoConfiguration from my Spring Boot app and did all configuration what you did here:

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-spring-boot-example/src/main/java/org/jooq/example/spring/config

And got the same error:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dsl' defined in class path resource [com/hotelkey/analytics/data/JooqSpringBootConfiguration.class]: Post-processing of merged bean definition failed; nested exception is java.lang.StackOverflowError

Would be very grateful to guide me how to configure programmatically

Thanks

[Lukas Eder]

2017-04-03 14:24 GMT+01:00 Durim Kryeziu <durimk...@gmail.com>:

Thanks for reply Lukas,

In my case, the best situation is to execute only when I want, unfortunately our database doesn't have versions neither we want to use Maven plugin. For me best is to expose like I tried to do and call that endpoint only when I want to call it.
Did I placed that code in the right place ?

I just now realised that you put that logic inside of a RestController. Why? I mean, code generation is something that happens at build time, not at runtime? What problem is running code generation through rest solving?

 

I also disabled the JooqAutoConfiguration from my Spring Boot app and did all configuration what you did here:

https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-spring-boot-example/src/main/java/org/jooq/example/spring/config

I suspect that the stack trace is incomplete. Where does the StackOverflowError happen? In Spring, I guess, but why? Have you debugged through it?

Cheers,

Lukas

[Durim Kryeziu]

Yeah, you're right, doesn't make sense to generate code on runtime, how I missed it :(

But is there any simple way to generate code just when I want programmatically ?
Does it work to do something like this ? .withSchemaVersionProvider("1") ? and to save that value to a env var and change from time to time ?

[Lukas Eder]

2017-04-03 14:39 GMT+01:00 Durim Kryeziu <durimk...@gmail.com>:

Yeah, you're right, doesn't make sense to generate code on runtime, how I missed it :(

No worries ;)

 

But is there any simple way to generate code just when I want programmatically ?

Of course. You could put that code in a main(String[] args) method and call that from anywhere, e.g. the console. Or you could call it from ant or from the ant-maven plugin:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-ant

 

Does it work to do something like this ? .withSchemaVersionProvider("1") ? and to save that value to a env var and change from time to time ?

Yes the schema version provider is the tool to prevent re-generation for the same schema version. An environment variable would work, but it would be tedious. But if you run the code generator programmatically (and explicitly, e.g. through some ant call), why would you need the schema version provider? You would only manually re-generate the code when needed, right?

[pawank...@gmail.com]

Its slightly older thread but anyone trying to generate jOOQ configuration using Java, please make sure you have below jars on your classpath:

jooq-3.9.6.jar

jooq-meta-3.9.6.jar

jooq-codegen-3.9.6.jar

Source: 

https://www.jooq.org/doc/3.9/manual/code-generation/codegen-programmatic/

Reason for confusion:

org.jooq.Configuration(Interface) vs org.jooq.util.jaxb.Configuration(Class)

Without above jars on classpath, compiler will think you are trying to instantiate an abstract class. Took me a while to figure out, thought to share with community.

Happy coding!