JOOQ增删改查

0.注入配置

    @Autowired
    private Configuration configuration;

1.查找全部

    public List<ContentArticle> fetchContentArticle() {
        DSLContext create = DSL.using(configuration);
        return create.selectFrom(Tables.CONTENT_ARTICLE).
                fetchInto(ContentArticle.class);
    }

2.根据文章编号删除

    public int deleteContentArticleById(Long id) {
        try {
            DSLContext create = DSL.using(configuration);
            return create.deleteFrom(Tables.CONTENT_ARTICLE)
                    .where(Tables.CONTENT_ARTICLE.ID.eq(id))
                    .execute();
        } catch (Exception ex) {
            log.warn("delete", ex);
            return -1;
        }
    }

3.更新文章

    public int updateContentArticle(ContentArticle contentArticle) {

        try {
            DSLContext create = DSL.using(configuration);

            contentArticle.setUpdatedAt(Timestamp.valueOf(LocalDateTime.now()));
            return create.update(Tables.CONTENT_ARTICLE)
                    .set(Tables.CONTENT_ARTICLE.TITLE, contentArticle.getTitle())
                    .set(Tables.CONTENT_ARTICLE.CONTENT, contentArticle.getContent())
                    .set(Tables.CONTENT_ARTICLE.AUTHOR, contentArticle.getAuthor())
                    .set(Tables.CONTENT_ARTICLE.UPDATED_AT, contentArticle.getUpdatedAt())
                    .where(Tables.CONTENT_ARTICLE.ID.eq(contentArticle.getId()))
                    .execute();
        } catch (Exception ex) {
            log.warn("update", ex);
            return -1; // Error occurred during the update
        }
    }

4.用Record对象实现新增文章

    public int insertContentArticle(ContentArticle contentArticle) {

        try {
            DSLContext create = DSL.using(configuration);
            // 设置创建和更新时间戳
            Timestamp createdAt = Timestamp.valueOf(LocalDateTime.now());
            Timestamp updatedAt = Timestamp.valueOf(LocalDateTime.now());

            ContentArticleRecord newRecord = create.newRecord(Tables.CONTENT_ARTICLE);

            newRecord.setColumnId(contentArticle.getColumnId());
            newRecord.setTitle(contentArticle.getTitle());
            newRecord.setBrief(contentArticle.getBrief());
            newRecord.setAuthor(contentArticle.getAuthor());
            newRecord.setImages(contentArticle.getImages());
            newRecord.setContent(contentArticle.getContent());
            newRecord.setWeight(contentArticle.getWeight());
            newRecord.setPublish(contentArticle.getPublish());
            newRecord.setCreatedAt(createdAt);
            newRecord.setUpdatedAt(updatedAt);

            return newRecord.insert();
        } catch (Exception ex) {
            log.warn("insert", ex);
            return -1;
        }
    }