java中jooq,在Spring中使用jOOQ源码案例

Spring专题

在Spring中使用jOOQ源码案例

虽然ORM大部分性能问题是由开发者自己引起的，以只读方式使用ORM是不值得的，现在有一种其他可选方式，使用JOOQ，jOOQ从您的数据库生成Java代码，并允许您通过它流畅的API构建类型安全的SQL查询。

依赖包：

Spring Framework 3.2.6. 使用其 aop, beans, core, context, context-support, jdbc, 和 tx 模块.

BoneCP 0.8.0. 作为数据库连接池

jOOQ 3.2.2.

H2 1.3.174. 作为案例的数据库.

pom.xml的配置：

org.springframework

spring-aop

3.2.6.RELEASE

org.springframework

spring-beans

3.2.6.RELEASE

org.springframework

spring-core

3.2.6.RELEASE

org.springframework

spring-context

3.2.6.RELEASE

org.springframework

spring-context-support

3.2.6.RELEASE

org.springframework

spring-expression

3.2.6.RELEASE

org.springframework

spring-jdbc

3.2.6.RELEASE

org.springframework

spring-tx

3.2.6.RELEASE

cglib

cglib

3.1

com.jolbox

bonecp

0.8.0.RELEASE

org.jooq

jooq

3.2.2

com.h2database

h2

1.3.174

DB 连接配置:

#Database Configuration

db.driver=org.h2.Driver

db.url=jdbc:h2:target/jooq-example

db.username=sa

db.password=

#jOOQ Configuration

jooq.sql.dialect=H2

#DB Schema

db.schema.script=schema.sql

负责持久的PersistenceContext ：

@Configuration

@ComponentScan({"net.petrikainulainen.spring.jooq.todo"})

@EnableTransactionManagement

@PropertySource("classpath:application.properties")

public class PersistenceContext {

@Autowired

private Environment env;

@Bean(destroyMethod = "close")

public DataSource dataSource() {

BoneCPDataSource dataSource = new BoneCPDataSource();

dataSource.setDriverClass(env.getRequiredProperty("db.driver"));

dataSource.setJdbcUrl(env.getRequiredProperty("db.url"));

dataSource.setUsername(env.getRequiredProperty("db.username"));

dataSource.setPassword(env.getRequiredProperty("db.password"));

return dataSource;

}

@Bean

public LazyConnectionDataSourceProxy lazyConnectionDataSource() {

return new LazyConnectionDataSourceProxy(dataSource());

}

@Bean

public TransactionAwareDataSourceProxy transactionAwareDataSource() {

return new TransactionAwareDataSourceProxy(lazyConnectionDataSource());

}

@Bean

public DataSourceTransactionManager transactionManager() {

return new DataSourceTransactionManager(lazyConnectionDataSource());

}

@Bean

public DataSourceConnectionProvider connectionProvider() {

return new DataSourceConnectionProvider(transactionAwareDataSource());

}

@Bean

public JOOQToSpringExceptionTransformer jooqToSpringExceptionTransformer() {

return new JOOQToSpringExceptionTransformer();

}

@Bean

public DefaultConfiguration configuration() {

DefaultConfiguration jooqConfiguration = new DefaultConfiguration();

jooqConfiguration.set(connectionProvider());

jooqConfiguration.set(new DefaultExecuteListenerProvider(

jooqToSpringExceptionTransformer()

));

String sqlDialectName = env.getRequiredProperty("jooq.sql.dialect");

SQLDialect dialect = SQLDialect.valueOf(sqlDialectName);

jooqConfiguration.set(dialect);

return jooqConfiguration;

}

@Bean

public DefaultDSLContext dsl() {

return new DefaultDSLContext(configuration());

}

@Bean

public DataSourceInitializer dataSourceInitializer() {

DataSourceInitializer initializer = new DataSourceInitializer();

initializer.setDataSource(dataSource());

ResourceDatabasePopulator populator = new ResourceDatabasePopulator();

populator.addScript(

new ClassPathResource(env.getRequiredProperty("db.schema.script"))

);

initializer.setDatabasePopulator(populator);

return initializer;

}

}

将jOOQ Exceptions 传递到 Spring DataAccessExceptions

import org.jooq.ExecuteContext;

import org.jooq.SQLDialect;

import org.jooq.impl.DefaultExecuteListener;

import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;

import org.springframework.jdbc.support.SQLExceptionTranslator;

import org.springframework.jdbc.support.SQLStateSQLExceptionTranslator;

public class JOOQToSpringExceptionTransformer extends DefaultExecuteListener {

@Override

public void exception(ExecuteContext ctx) {

SQLDialect dialect = ctx.configuration().dialect();

SQLExceptionTranslator translator = (dialect != null)

? new SQLErrorCodeSQLExceptionTranslator(dialect.name())

: new SQLStateSQLExceptionTranslator();

ctx.exception(translator.translate("jOOQ", ctx.sql(), ctx.sqlException()));

}

}

以上案例的源码下载：at Github.