Find Last item with queryDSL and spring-data-jpa implementation with LockMode

Question

I want to find the last inserted item from table "product" with Spring-Data-JPA (Hibernate implementation) and QueryDSL(type safe) to generate SQL: SELECT * FROM product ORDER BY id DESC LIMIT 1

I have call findLast() from Service with @Transactional and want to setLockMode(LockModeType.PESSIMISTIC_WRITE) and to add "SELECT .. FOR UPDATE" in this SQL query.

public class ProductRepositoryImpl extends QueryDslRepositorySupport implements ProductRepositoryCustom {

    public ProductRepositoryImpl() {
        super(Product.class);
    }

    //SELECT * FROM PRODUCTS ORDER BY ID DESC LIMIT 1
    @Override
    public Product findLast() {
        QProduct product = QProduct.product;
        return from(product).orderBy(product.id.desc()).limit(1L).fetchOne();
    }
}

This is the generated SQL

Hibernate: select * from ( select product0_.id as id11, product0_.description as description21, product0_.price as price31, product0_.productStatus as productStatus41, product0_.quantity as quantity51 from PRODUCTS product0 order by product0.id desc ) where rownum <= ?

The question: What is the best way to add setLockMode() in repository extending org.springframework.data.jpa.repository.support.QueryDslRepositorySupport?

I have try this but it return Object and needs to cast (Product):

getQuerydsl().createQuery(product)
.setLockMode(LockModeType.PESSIMISTIC_WRITE).orderBy(product.id.desc()).limit(1L).fetchOne();

From the generated SQL, it does not appear that the limit is ignored, its just that it uses your database's internal rownum semantic to return all rows where rownum <= 1 — Naros
yes but is executed in subquery and this cannot work with locking selected rows with "SELECT .. FOR UPDATE" and I have check that if I skip limit() the generated SQL is the same rownum <= 1 is coming from fetchOne() — sytolk
What if you specify the limit() and tell it to call fetch() or fetchResults(). Does the generated SQL work as you would expect? — Naros

Ras1900 Ras1900 · Accepted Answer · 2017-03-10T07:29:27

Try with this part of code , I think it' s will works like that :

  QProduct product = QProduct.product;
 return (Product)getQuerydsl().createQuery(product)
.setLockMode(LockModeType.PESSIMISTIC_WRITE).orderBy(product.id.desc())
.limit(1L).fetchOne();

Hibernate will generate the following queries: Hibernate: select * from ( select product0_.id as id1_1_, ...... from PRODUCTS product0_ order by product0_.id desc ) where rownum <= ?

Hibernate: select product0_.id as id1_1_, .... from PRODUCTS product0_ where product0_.id=? for update

Attention: There are two SQL queries and the row can be locked only by ID. The problem is structural it' s better to separate query instead of let the Hibernate do that.

Find Last item with queryDSL and spring-data-jpa implementation with LockMode

1 Answers