# kit-jooq-spring-transaction

**Repository Path**: thlws/kit-jooq-spring-transaction

## Basic Information

- **Project Name**: kit-jooq-spring-transaction
- **Description**: SpringBoot2 + MySql +JOOQ + Spring声明式事务处理    示例项目。
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 3
- **Created**: 2019-01-12
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 框架说明
> 基于springboot2 + mysql + jooq 整合(含Spring事务支持)


### 使用说明
#### 生成JOOQ代码

JOOQ默认生成策略Table表名与POJO 名称一样,导致使用时很不方便，这里拓展了生成策略，代码请查阅下面这个class.
`com.thlws.demojooq.strategy.JooqGeneratorStrategyExt`
- 生成策略配置 jooq_config.xml
```xml
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.11.0.xsd">

    <jdbc>
        <driver>com.mysql.jdbc.Driver</driver>
        <url>jdbc:mysql://localhost:3306/db1</url>
        <user>root</user>
        <password>123456</password>
    </jdbc>

    <generator>

        <strategy>
            <!--自定义生成策略-->
            <name> com.thlws.demojooq.strategy.JooqGeneratorStrategyExt</name>
        </strategy>

        <name>org.jooq.codegen.JavaGenerator</name>
        <database>

            <!--下面这两行是为view也生成代码的关键-->
            <!--force generating id's for everything in public schema, that has an 'id' field-->
            <syntheticPrimaryKeys>public\..*\.id</syntheticPrimaryKeys>

            <!--name for fake primary key-->
            <overridePrimaryKeys>override_primmary_key</overridePrimaryKeys>

            <!--<name>org.jooq.meta.postgres.PostgresDatabase</name>-->
            <!--<name>org.jooq.meta.postgres.PostgresDatabase</name>-->

            <name>org.jooq.meta.mysql.MySQLDatabase</name>

            <!--include和exclude用于控制为数据库中哪些表生成代码-->
            <includes>.*</includes>
            <!--<excludes></excludes>-->

            <!--数据库名称-->
            <inputSchema>db1</inputSchema>
        </database>

        <generate>
            <!--生成dao和pojo-->
            <daos>true</daos>
            <pojos>true</pojos>

            <!--把数据库时间类型映射到java 8时间类型-->
            <javaTimeTypes>true</javaTimeTypes>
            <!--<interfaces>true</interfaces>-->
            <!--生成的代码中添加spring注释，比如@Repository-->
            <springAnnotations>true</springAnnotations>
            <pojosToString>true</pojosToString>
        </generate>

        <target>
            <!--生成代码文件的包名及放置目录-->
            <packageName>com.thlws.demojooq.jooq</packageName>
            <directory>src/main/java</directory>
        </target>
    </generator>
</configuration>
```

- 数据源及事务配置
```java
@Configuration
public class SpringConfig {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    private final DbConfig dbConfig;

    @Autowired
    public SpringConfig(DbConfig dbConfig) {
        this.dbConfig = dbConfig;
    }

    @PostConstruct
    private void init() {
        logger.debug("初始化db信息......");
    }




    @Bean("jooqDataSource")
    public TransactionAwareDataSourceProxy transactionAwareDataSourceProxy(){

        DataSource dataSource = DataSourceBuilder.create()
                .driverClassName(dbConfig.getDriver_class_name())
                .password(dbConfig.getPassword())
                .username(dbConfig.getUsername())
                .url(dbConfig.getUrl()).build();

        return new TransactionAwareDataSourceProxy(dataSource);
    }



    @Bean(name = "transactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("jooqDataSource") TransactionAwareDataSourceProxy jooqDataSource){
        return new DataSourceTransactionManager(jooqDataSource);
    }


    @Bean("jooqConfiguration")
    public DefaultConfiguration jooqConfiguration(@Qualifier("jooqDataSource") DataSource jooqDataSource ){

        ExecuteListener executeListener = new ExceptionTranslator();
        DataSourceConnectionProvider myConnectionProvider = new DataSourceConnectionProvider(jooqDataSource);
        DefaultExecuteListenerProvider executeListenerProvider = new DefaultExecuteListenerProvider(executeListener);

        DefaultConfiguration configuration = new DefaultConfiguration();
        configuration.setConnectionProvider(myConnectionProvider);
        configuration.setExecuteListenerProvider(executeListenerProvider);
        configuration.setSQLDialect(SQLDialect.MYSQL);
        return configuration;
    }

    @Bean("myDSLContext")
    public DefaultDSLContext jooqDSLContext(@Qualifier("jooqConfiguration") DefaultConfiguration configuration ){

        return new DefaultDSLContext(configuration);
    }


}
```

执行maven开始生成: `mvn jooq-codegen:generate`

#### JOOQ示例
```
//插入代码1
Demo demo = new Demo();
demo.setCreateTime(LocalDateTime.now());
DemoRecord demoRecord = dsl.newRecord(Tables.DEMO,demo);
demoRecord.insert();


//插入代码2
dsl.insertInto(Tables.DEMO)
.set(DemoTable.DEMO.PRIMARYKEY, System.currentTimeMillis())
.set(DemoTable.DEMO.NAME, "Hanley")
.execute();
```


### 注意有坑

- 新版springboot 若使用servlet,filter时，必须配置包路径,否则会导致请求无法抵达controller
- 当然也可以不在Application中配置,独立建立一个Configuration class,在加入Servlet ,Filter 配置

### 参考资料
- [http://www.jooq.org/](http://www.jooq.org/)
- [http://www.jooq.org/learn/](http://www.jooq.org/learn/)
- [http://www.jooq.org/doc/3.11/manual/getting-started/tutorials/jooq-in-7-steps/](http://www.jooq.org/doc/3.11/manual/getting-started/tutorials/jooq-in-7-steps/)
- [http://www.jooq.org/doc/3.11/manual-single-page](http://www.jooq.org/doc/3.11/manual-single-page)
- [https://docs.spring.io/spring-boot/docs/2.1.0.RELEASE/reference/htmlsingle/#boot-features-jooq](https://docs.spring.io/spring-boot/docs/2.1.0.RELEASE/reference/htmlsingle/#boot-features-jooq)
- [http://amao12580.github.io/post/2016/04/JOOQ-from-entry-to-improve/](http://amao12580.github.io/post/2016/04/JOOQ-from-entry-to-improve/)