Jooq配置乐观锁

Love The Way You Lie 2023-06-16 08:23 58阅读 0赞

**参考资料：[https://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/optimistic-locking/][https_www.jooq.org_doc_latest_manual_sql-execution_crud-with-updatablerecords_optimistic-locking]**

**方法1：直接激活withExecuteWithOptimisticLocking相关设置**

/**
         * Jooq乐观锁1
         */
        @Test
        void testOptimisticLock1() {
            // Properly configure the DSLContext
            DSLContext optimistic = DSL.using(jdbcTemplate.getDataSource(), SQLDialect.POSTGRES,
                    new Settings().withExecuteWithOptimisticLocking(true));
    
            // Fetch a book two times
            BookRecord book1 = optimistic.fetchOne(BOOK, BOOK.ID.eq(1L));
            BookRecord book2 = optimistic.fetchOne(BOOK, BOOK.ID.eq(1L));
    
            // Change the title and store this book. The underlying database record has not been modified, it can be safely updated.
            book1.setTitle("Animal Farm" + UUID.randomUUID());
            book1.store();
    
            // Book2 still references the original TITLE value, but the database holds a new value from book1.store().
            // This store() will thus fail:
            book2.setTitle("1984");
            assertThrows(DataChangedException.class, () -> book2.store());
        }

**方法2：通过时间戳来控制 **

/**
         * Jooq乐观锁2：timestamp
         * 数据库包含字段：modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
         * Jooq生成器插件配置
         * <configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.12.0.xsd">
         *   <generator>
         *     <database>
         *       <recordTimestampFields>modified</recordTimestampFields>
         *     </database>
         *   </generator>
         * </configuration>
         */
        @Test
        void testOptimisticLock2() {
            // Properly configure the DSLContext
            DSLContext optimistic = DSL.using(jdbcTemplate.getDataSource(), SQLDialect.POSTGRES,
                    new Settings().withExecuteWithOptimisticLocking(true));
    
            // Fetch a book two times
            BookRecord book1 = optimistic.fetchOne(BOOK, BOOK.ID.eq(2L));
            BookRecord book2 = optimistic.fetchOne(BOOK, BOOK.ID.eq(2L));
    
            // Change the title and store this book. The MODIFIED value has not been changed since the book was fetched.
            // It can be safely updated
            book1.setTitle("Animal Farm" + UUID.randomUUID());
            book1.store();
    
            // Book2 still references the original MODIFIED value, but the database holds a new value from book1.store().
            // This store() will thus fail:
            book2.setTitle("1984");
            assertThrows(DataChangedException.class, () -> book2.store());
        }

**方法3：通过版本号来控制 **

/**
         * Jooq乐观锁3：version
         * 数据库包含字段：version INTEGER DEFAULT 0
         * Jooq生成器插件配置
         * <configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.12.0.xsd">
         *   <generator>
         *     <database>
         *       <recordVersionFields>version</recordVersionFields>
         *     </database>
         *   </generator>
         * </configuration>
         */
        @Test
        void testOptimisticLock3() {
            // Properly configure the DSLContext
            DSLContext optimistic = DSL.using(jdbcTemplate.getDataSource(), SQLDialect.POSTGRES,
                    new Settings().withExecuteWithOptimisticLocking(true));
    
            // Fetch a book two times
            AuthorRecord author1 = optimistic.fetchOne(AUTHOR, AUTHOR.ID.eq(1L));
            AuthorRecord author2 = optimistic.fetchOne(AUTHOR, AUTHOR.ID.eq(1L));
    
            // Change the title and store this book. The MODIFIED value has not been changed since the book was fetched.
            // It can be safely updated
            author1.setAge(Optional.ofNullable(author1.getAge()).orElse(0) + 1);
            author1.store();
    
            // Book2 still references the original MODIFIED value, but the database holds a new value from book1.store().
            // This store() will thus fail:
            author2.setAge(20);
            assertThrows(DataChangedException.class, () -> author2.store());
        }

[https_www.jooq.org_doc_latest_manual_sql-execution_crud-with-updatablerecords_optimistic-locking]: https://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/optimistic-locking/