SpringBoot整合Jooq框架

修改pom.xml文件

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

代码生成器插件配置

<plugins>
    <!---jooq 逆向工程插件-->
    <plugin>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-codegen-maven</artifactId>
        <executions>
            <execution>
                <phase>generate-sources</phase>
                <goals>
                    <goal>generate</goal>
                </goals>
            </execution>
        </executions>
        <dependencies>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.42</version>
            </dependency>
        </dependencies>
        <configuration>
            <jdbc>
                <driver>com.mysql.jdbc.Driver</driver>
                <!--配置数据库地址，账户密码-->
                <url>jdbc:mysql://locahost:3306/</url>
                <username>*</username>
                <password>*</password>
            </jdbc>
            <generator>
                <database>
                    <name>org.jooq.util.mysql.MySQLDatabase</name>
                    <!--include和exclude用于控制为数据库中哪些表生成代码-->
                    <!--<includes>.*</includes>-->
                    <includes>ide_user_test</includes>
                    <!--<excludes></excludes>-->
                    <!--数据库名称-->
                    <inputSchema>test</inputSchema>
                </database>
                <generate>
                    <!--生成dao和pojo-->
                    <daos>true</daos>
                    <pojos>true</pojos>
                    <!--把数据库时间类型映射到java 8时间类型-->
                    <javaTimeTypes>true</javaTimeTypes>
                    <!--<interfaces>true</interfaces>-->
                    <!--不在生成的代码中添加spring注释，比如@Repository-->
                    <springAnnotations>false</springAnnotations>
                </generate>
                <target>
                    <!--生成代码文件的包名及放置目录-->
                    <packageName>ys.manufacture.generate</packageName>
                    <directory>src/main/java</directory>
                </target>
            </generator>
        </configuration>
    </plugin>
</plugins>

生成代码

	运行 mvn clean install -Djooq 命令生成代码，点击maven里面的按钮也能生成，如下图：

执行命令后会生成以下几个类：

pojos下面的类是我们会用到的实体类，而tables文件夹下面的实体类是利用DSL查询时拼接sql的表名、字段名

service层部分代码示例

   ys.manufacture.generate.tables.IdeUserTest USER = ys.manufacture.generate.tables.IdeUserTest.IDE_USER_TEST;
    /**
     * 通过主键查询
     *
     * @param user_number
     * @return
     */
    public IdeUserTest getByJooqFindId(String user_number) {
        //用DSL
        DSLContext create = DSL.using(dataSource,SQLDialect.MYSQL);
        List<IdeUserTest> ideUserTests = create.select(USER.USER_AGE,USER.USER_NAME)
                .from(USER).where(USER.USER_NUMBER.eq(user_number)).fetchInto(IdeUserTest.class);
    }

    /**
     * 通过jooq新增demo
     * @param inputBean
     * @return
     */
    public UserTestViewOutputBean addByJooq(UserTestViewInputBean inputBean) {
        String meacon = genNoService.getNo("userId", commonService.getCurrentDateTime().jaDateValue());
        LocalDateTime localDateTime = Instant.ofEpochMilli(JaDate.today().dateValue().getTime()).atZone(ZoneId.systemDefault()).toLocalDateTime();
        //DSL方法
        DSLContext create = DSL.using(dataSource,SQLDialect.MYSQL);
        int execute = create.insertInto(USER)
                .columns(USER.USER_NUMBER, USER.USER_NAME, USER.USER_AGE, USER.USER_DATE, USER.USER_TEL)
                .values(meacon, inputBean.getUser_name(), inputBean.getUser_age(), localDateTime, inputBean.getUser_tel())
                .execute();
        return new UserTestViewOutputBean();
    }

    /**
     * 删除
     * @param inputBean
     * @return
     */
    public UserTestViewOutputBean delByJooq(UserTestViewInputBean inputBean) {
        //dao 方法
        dao.deleteById(inputBean.getUser_number());
        return new UserTestViewOutputBean();
    }

    /**
     * 修改
     * @param inputBean
     * @return
     */
    public UserTestViewOutputBean editByJooq(UserTestViewInputBean inputBean) {
        //DSL方法
        DSLContext create = DSL.using(dataSource, SQLDialect.MYSQL);
        int execute = create.update(USER)
                .set(USER.USER_NAME, inputBean.getUser_name())
                .where(USER.USER_NUMBER.eq(inputBean.getUser_number()))
                .execute();
        return new UserTestViewOutputBean();
    }


    @Autowired
    DSLContext dslContext;
    /**
     *
     * 事务测试
     * @param inputBean
     * @return
     * @throws SqlParserException
     */
    public UserTestViewOutputBean transactionJooq(UserTestViewInputBean inputBean)throws SqlParserException {
         DSL.using(dataSource,SQLDialect.MYSQL).transaction(new TransactionalRunnable() {
             @Override
             public void run(Configuration configuration) throws Exception {
                 DSLContext create = DSL.using(configuration);
                 int execute = create.update(USER)
                         .set(USER.USER_AGE, inputBean.getUser_age())
                         .where(USER.USER_NUMBER.eq(inputBean.getUser_number()))
                         .execute();
                 int execute1 = dslContext.deleteFrom(USER).where(USER.USER_AGE.eq(inputBean.getUser_age())).execute();
             }
        });
        return new UserTestViewOutputBean();
    }

 上面查询语句代码可读性很强，实现了java代码代替了sql语句。除了用这种方式是查询之外，还可以利用自动生成的dao层，
 不过在使用dao时，需先调用它的构造方法注入，不能直接用@Autowired或@Resource注解注入，代码如下图：

 	@Autowired
    DataSource dataSource;

    private IdeUserTestDao dao;

    @PostConstruct//表示在UserServiceImpl构造完成之后执行
    private void createDao() {
        // DSL上下文是所有数据库操作的入口，意思就是要用jooq干任何事都要先实例化这个DSLContext
        DSLContext dsl = DSL.using(dataSource, SQLDialect.MYSQL);
        dao = new IdeUserTestDao(dsl.configuration());
    }
    
 	ys.manufacture.generate.tables.IdeUserTest USER = ys.manufacture.generate.tables.IdeUserTest.IDE_USER_TEST;
   
    /**
     * 通过主键查询
     *
     * @param user_number
     * @return
     */
    public IdeUserTest getByJooqFindId(String user_number) {
        //用dao方法
        IdeUserTest userTest = dao.findById(user_number);
        return userTest;
    }

    /**
     * 通过jooq新增demo
     * @param inputBean
     * @return
     */
    public UserTestViewOutputBean addByJooq(UserTestViewInputBean inputBean) {
        String meacon = genNoService.getNo("userId", commonService.getCurrentDateTime().jaDateValue());
        LocalDateTime localDateTime = Instant.ofEpochMilli(JaDate.today().dateValue().getTime()).atZone(ZoneId.systemDefault()).toLocalDateTime();
        //dao方法
        IdeUserTest userTest = new IdeUserTest();
        userTest.setUserNumber(meacon);
        userTest.setUserTel(inputBean.getUser_tel());
        userTest.setUserName(inputBean.getUser_name());
        userTest.setUserAge(inputBean.getUser_age());
        userTest.setUserDate(localDateTime);
        dao.insert(userTest);
        return new UserTestViewOutputBean();
    }

    /**
     * 删除
     * @param inputBean
     * @return
     */
    public UserTestViewOutputBean delByJooq(UserTestViewInputBean inputBean) {
        //dao 方法
        dao.deleteById(inputBean.getUser_number());
        return new UserTestViewOutputBean();
    }

    /**
     * 修改
     * @param inputBean
     * @return
     */
    public UserTestViewOutputBean editByJooq(UserTestViewInputBean inputBean) {
        //dao 方法
        IdeUserTest userTest = new IdeUserTest();
        userTest.setUserNumber(inputBean.getUser_number());
        userTest.setUserTel(inputBean.getUser_tel());
        userTest.setUserName(inputBean.getUser_name());
        userTest.setUserAge(inputBean.getUser_age());
        dao.update(userTest);
        return new UserTestViewOutputBean();
    }

事务

1. 可以直接在数据库中发出特定于供应商的COMMIT，ROLLBACK和其他语句；
2. 可以在JDBC驱动程序上调用JDBC的Connection.commit（），Connection.rollback（）和其他方法；
3. 可以使用第三方事务管理库，例如Spring TX（@Transactional）；
4. 可以从容器中使用符合JTA的Java EE事务管理器；