SpringBoot整合Jooq框架

 
        
            org.springframework.boot
            spring-boot-starter-jooq
        
        
            org.jooq
            jooq
        
        
            org.jooq
            jooq-meta
        
        
            org.jooq
            jooq-codegen
        

    
    
        org.jooq
        jooq-codegen-maven
        
            
                generate-sources
                
                    generate
                
            
        
        
            
                mysql
                mysql-connector-java
                5.1.42
            
        
        
            
                com.mysql.jdbc.Driver
                
                jdbc:mysql://locahost:3306/
                *
                *
            
            
                
                    org.jooq.util.mysql.MySQLDatabase
                    
                    
                    ide_user_test
                    
                    
                    test
                
                
                    
                    true
                    true
                    
                    true
                    
                    
                    false
                
                
                    
                    ys.manufacture.generate
                    src/main/java
                
            
        
    

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

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

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

   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 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();
    }