I have had my share of performance problems caused by ORMs. Although I have to admit that most of these problems were really caused by yours truly, I have started to think that using ORMs in read-only operations is not worth it.<\/p>\n
I started to look for alternative ways to implement these operations.<\/p>\n
That is how I ran into jOOQ<\/a> which states that:<\/p>\n jOOQ generates Java code from your database and lets you build typesafe SQL queries through its fluent API.<\/p>\n<\/blockquote>\n This looks very interesting and I decided to give jOOQ a shot and share my findings with you.<\/p>\n This blog post is the first part of my Using jOOQ with Spring series and it describes how we can get the required dependencies and configure the application context of our application.<\/p>\n Let\u2019s get started.<\/p>\n The dependencies of our application are:<\/p>\n If you want to get more information about the modules of Spring Framework, read section 1.2 of the Spring Framework Reference Documentation<\/a>.<\/p>\n The reason why this example application uses Spring Framework 3.2.6 instead of 4.0 is that at the moment spring-test-dbunit isn\u2019t compatible with Spring Framework 4.0<\/a>.<\/p>\n The relevant part of the pom.xml<\/em> file looks as follows:<\/p>\n Let\u2019s move on find out how we can transform the exceptions thrown by jOOQ to Spring DataAccessExceptions<\/em>.<\/p>\n Why should we transform the exceptions thrown by jOOQ to Spring DataAccessExceptions<\/em><\/a>?<\/p>\n One reason to do so is that we want that our integration works in the same way than the DAO support of Spring Framework<\/a>. One essential part of this support is a consistent exception hierarchy<\/a>:<\/p>\n Spring provides a convenient translation from technology-specific exceptions like SQLException<\/em> to its own exception class hierarchy with the DataAccessException<\/em> as the root exception. These exceptions wrap the original exception so there is never any risk that one might lose any information as to what might have gone wrong.<\/p>\n<\/blockquote>\n In other words, if we want that our application is \u201ca good citizen\u201d, it makes sense to ensure that our configuration transforms the exceptions thrown by jOOQ to Spring DataAccessExceptions<\/em>.<\/p>\n We can create a component which provides this functionality by following these steps:<\/p>\n The source code of the JOOQToSpringExceptionTransformer<\/em> class looks as follows: This is not my idea<\/strong>. I got this idea from Adam Zell\u2019s Gist<\/a>.<\/p>\n Additional reading:<\/strong><\/p>\n Our job isn\u2019t done yet. Let\u2019s put all pieces together and finish our job by configuring the application context of our example application.<\/p>\n This section explains how we can configure the application context of our application by using Java configuration.<\/p>\n Let\u2019s start by creating a properties file which contains the configuration of our example application.<\/p>\n The build process of the actual application is based on Maven profiles. This ensures that we can use different configuration in different environments. You can get more information about this by reading my blog post titled Creating Profile Specific Configuration Files with Maven<\/a><\/p>\n<\/blockquote>\n We can create the properties file by following these steps:<\/p>\n The application.properties<\/em> file looks as follows:<\/p>\n Let\u2019s move on and find out how we can configure the application context of our application by using Java configuration.<\/p>\n We can configure the application context of our application by following these steps:<\/p>\n The source code of the PersistenceContext<\/em> class looks as follows:<\/p>\n If you want to configure the application context by using XML configurations files, the example application has a working XML configuration file<\/a> as well.<\/p>\n Credits:<\/strong><\/p>\n How do we know that this configuration works? That is a good question. We will talk about that in the following section.<\/p>\n When I started to investigate how I can ensure that the database queries created with jOOQ participate in Spring-managed transactions, I noticed that it isn\u2019t an easy problem to solve<\/a>.<\/p>\n The example application of this blog post has a few integration tests which ensure that transactions (commit and rollback) are working in a very simple scenario. However, there are two things which we must take into account when using the solution described in this blog post:<\/p>\n 1. All database queries created with jOOQ must be executed inside a transaction.<\/strong><\/p>\n The Javadoc of the TransactionAwareDataSourceProxy<\/em> class<\/a> states:<\/p>\n Delegates to DataSourceUtils<\/em> for automatically participating in thread-bound transactions, for example managed by DataSourceTransactionManager<\/em>. getConnection<\/em> calls and close calls on returned Connections<\/em> will behave properly within a transaction, i.e. always operate on the transactional Connection. If not within a transaction, normal DataSource<\/em> behavior applies.<\/p>\n<\/blockquote>\n In other words, if you perform multiple complex operations without a transaction, jOOQ will use a different connection for each operation. This can lead into race condition bugs.<\/p>\n I noticed this issue when I read this comment written by Ben Manes<\/a>.<\/p>\n 2. Using TransactionAwareDataSourceProxy<\/em> is not recommend by its Javadoc.<\/strong><\/p>\n The Javadoc of the TransactionAwareDataSourceProxy<\/em><\/a> class has a section which goes like this:<\/p>\n This proxy allows data access code to work with the plain JDBC API and still participate in Spring-managed transactions, similar to JDBC code in a J2EE\/JTA environment. However, if possible, use Spring\u2019s DataSourceUtils<\/em>, JdbcTemplate<\/em> or JDBC operation objects to get transaction participation even without a proxy for the target DataSource<\/em>, avoiding the need to define such a proxy in the first place.<\/p>\n That is a pretty vague comment because it offers no explanation why we shouldn\u2019t use it. Adam Zell suggested<\/a> that because the class uses reflection, using it might cause performance problems.<\/p>\n If you run in performance problems, you might want to use the approach described in We have now successfully configured the application context of our example application. This tutorial has taught four things:<\/p>\n The next part of this tutorial describes we can use the code generation support of jOOQ<\/a>.<\/p>\n The example application of this blog post is available at Github<\/a>.<\/p>\n Additional reading:<\/strong><\/p>\n Vlad Mihalcea has written about jOOQ<\/a> as well. Check out his blog! I have had my share of performance problems caused by ORMs. Although I have to admit that most of these problems were really caused by yours truly, I have started to think that using ORMs in read-only operations is not worth it. I started to look for alternative ways to implement these operations. That is …<\/p>\n","protected":false},"author":429,"featured_media":240,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[256,30],"class_list":["post-20557","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-enterprise-java","tag-jooq","tag-spring"],"yoast_head":"\n\n
Getting the Required Dependencies with Maven<\/h2>\n
\n
<dependency>\r\n <groupId>org.springframework<\/groupId>\r\n <artifactId>spring-aop<\/artifactId>\r\n <version>3.2.6.RELEASE<\/version>\r\n<\/dependency>\r\n<dependency>\r\n <groupId>org.springframework<\/groupId>\r\n <artifactId>spring-beans<\/artifactId>\r\n <version>3.2.6.RELEASE<\/version>\r\n<\/dependency>\r\n<dependency>\r\n <groupId>org.springframework<\/groupId>\r\n <artifactId>spring-core<\/artifactId>\r\n <version>3.2.6.RELEASE<\/version>\r\n<\/Dependency>\r\n<dependency>\r\n <groupId>org.springframework<\/groupId>\r\n <artifactId>spring-context<\/artifactId>\r\n <version>3.2.6.RELEASE<\/version>\r\n<\/dependency>\r\n<dependency>\r\n <groupId>org.springframework<\/groupId>\r\n <artifactId>spring-context-support<\/artifactId>\r\n <version>3.2.6.RELEASE<\/version>\r\n<\/dependency>\r\n<dependency>\r\n <groupId>org.springframework<\/groupId>\r\n <artifactId>spring-expression<\/artifactId>\r\n <version>3.2.6.RELEASE<\/version>\r\n<\/dependency>\r\n<dependency>\r\n <groupId>org.springframework<\/groupId>\r\n <artifactId>spring-jdbc<\/artifactId>\r\n <version>3.2.6.RELEASE<\/version>\r\n<\/dependency>\r\n<dependency>\r\n <groupId>org.springframework<\/groupId>\r\n <artifactId>spring-tx<\/artifactId>\r\n <version>3.2.6.RELEASE<\/version>\r\n<\/dependency>\r\n \r\n<dependency>\r\n <groupId>cglib<\/groupId>\r\n <artifactId>cglib<\/artifactId>\r\n <version>3.1<\/version>\r\n<\/dependency>\r\n\r\n<dependency>\r\n <groupId>com.jolbox<\/groupId>\r\n <artifactId>bonecp<\/artifactId>\r\n <version>0.8.0.RELEASE<\/version>\r\n<\/dependency>\r\n\r\n<dependency>\r\n <groupId>org.jooq<\/groupId>\r\n <artifactId>jooq<\/artifactId>\r\n <version>3.2.2<\/version>\r\n<\/dependency>\r\n\r\n<dependency>\r\n <groupId>com.h2database<\/groupId>\r\n <artifactId>h2<\/artifactId>\r\n <version>1.3.174<\/version>\r\n<\/dependency><\/pre>\n
\n
Transforming jOOQ Exceptions to Spring DataAccessExceptions<\/h2>\n
\n
\n
\n
\n
import org.jooq.ExecuteContext;\r\nimport org.jooq.SQLDialect;\r\nimport org.jooq.impl.DefaultExecuteListener;\r\nimport org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;\r\nimport org.springframework.jdbc.support.SQLExceptionTranslator;\r\nimport org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;\r\n\r\n\r\npublic class JOOQToSpringExceptionTransformer extends DefaultExecuteListener {\r\n\r\n @Override\r\n public void exception(ExecuteContext ctx) {\r\n SQLDialect dialect = ctx.configuration().dialect();\r\n SQLExceptionTranslator translator = (dialect != null)\r\n ? new SQLErrorCodeSQLExceptionTranslator(dialect.name())\r\n : new SQLStateSQLExceptionTranslator();\r\n\r\n ctx.exception(translator.translate(\"jOOQ\", ctx.sql(), ctx.sqlException()));\r\n }\r\n}<\/pre>\n\n
Configuring the Application Context<\/h2>\n
\n
Creating the Properties File<\/h3>\n
\n
#Database Configuration\r\ndb.driver=org.h2.Driver\r\ndb.url=jdbc:h2:target\/jooq-example\r\ndb.username=sa\r\ndb.password=\r\n\r\n#jOOQ Configuration\r\njooq.sql.dialect=H2\r\n\r\n#DB Schema\r\ndb.schema.script=schema.sql<\/pre>\n
\n
Creating the Configuration Class<\/h3>\n
\n
\n
import com.jolbox.bonecp.BoneCPDataSource;\r\nimport org.jooq.SQLDialect;\r\nimport org.jooq.impl.DataSourceConnectionProvider;\r\nimport org.jooq.impl.DefaultConfiguration;\r\nimport org.jooq.impl.DefaultDSLContext;\r\nimport org.jooq.impl.DefaultExecuteListenerProvider;\r\nimport org.springframework.beans.factory.annotation.Autowired;\r\nimport org.springframework.context.annotation.Bean;\r\nimport org.springframework.context.annotation.ComponentScan;\r\nimport org.springframework.context.annotation.Configuration;\r\nimport org.springframework.context.annotation.PropertySource;\r\nimport org.springframework.core.env.Environment;\r\nimport org.springframework.core.io.ClassPathResource;\r\nimport org.springframework.jdbc.datasource.DataSourceTransactionManager;\r\nimport org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;\r\nimport org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;\r\nimport org.springframework.jdbc.datasource.init.DataSourceInitializer;\r\nimport org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;\r\nimport org.springframework.transaction.annotation.EnableTransactionManagement;\r\n\r\nimport javax.sql.DataSource;\r\n\r\n@Configuration\r\n@ComponentScan({\"net.petrikainulainen.spring.jooq.todo\"})\r\n@EnableTransactionManagement\r\n@PropertySource(\"classpath:application.properties\")\r\npublic class PersistenceContext {\r\n\r\n @Autowired\r\n private Environment env;\r\n\r\n @Bean(destroyMethod = \"close\")\r\n public DataSource dataSource() {\r\n BoneCPDataSource dataSource = new BoneCPDataSource();\r\n\r\n dataSource.setDriverClass(env.getRequiredProperty(\"db.driver\"));\r\n dataSource.setJdbcUrl(env.getRequiredProperty(\"db.url\"));\r\n dataSource.setUsername(env.getRequiredProperty(\"db.username\"));\r\n dataSource.setPassword(env.getRequiredProperty(\"db.password\"));\r\n\r\n return dataSource;\r\n }\r\n\r\n @Bean\r\n public LazyConnectionDataSourceProxy lazyConnectionDataSource() {\r\n return new LazyConnectionDataSourceProxy(dataSource());\r\n }\r\n\r\n @Bean\r\n public TransactionAwareDataSourceProxy transactionAwareDataSource() {\r\n return new TransactionAwareDataSourceProxy(lazyConnectionDataSource());\r\n }\r\n\r\n @Bean\r\n public DataSourceTransactionManager transactionManager() {\r\n return new DataSourceTransactionManager(lazyConnectionDataSource());\r\n }\r\n\r\n @Bean\r\n public DataSourceConnectionProvider connectionProvider() {\r\n return new DataSourceConnectionProvider(transactionAwareDataSource());\r\n }\r\n\r\n @Bean\r\n public JOOQToSpringExceptionTransformer jooqToSpringExceptionTransformer() {\r\n return new JOOQToSpringExceptionTransformer();\r\n }\r\n\r\n @Bean\r\n public DefaultConfiguration configuration() {\r\n DefaultConfiguration jooqConfiguration = new DefaultConfiguration();\r\n\r\n jooqConfiguration.set(connectionProvider());\r\n jooqConfiguration.set(new DefaultExecuteListenerProvider(\r\n jooqToSpringExceptionTransformer()\r\n ));\r\n\r\n String sqlDialectName = env.getRequiredProperty(\"jooq.sql.dialect\");\r\n SQLDialect dialect = SQLDialect.valueOf(sqlDialectName);\r\n jooqConfiguration.set(dialect);\r\n\r\n return jooqConfiguration;\r\n }\r\n\r\n @Bean\r\n public DefaultDSLContext dsl() {\r\n return new DefaultDSLContext(configuration());\r\n }\r\n\r\n @Bean\r\n public DataSourceInitializer dataSourceInitializer() {\r\n DataSourceInitializer initializer = new DataSourceInitializer();\r\n initializer.setDataSource(dataSource());\r\n\r\n ResourceDatabasePopulator populator = new ResourceDatabasePopulator();\r\n populator.addScript(\r\n new ClassPathResource(env.getRequiredProperty(\"db.schema.script\"))\r\n );\r\n\r\n initializer.setDatabasePopulator(populator);\r\n return initializer;\r\n }\r\n}<\/pre>\n\n
Does This Really Work?<\/h2>\n
\n
\nAdam Zell\u2019s Gist<\/a>.<\/p>\nSummary<\/h2>\n
\n
\n <\/p>\n