I need to get 20 users from the postgres database (without sorting), do some actions with them, and then delete them as part of the transaction. If I were using a native sql query, I would write like this:
SELECT
user_id,
birthday,
username
FROM users
WHERE
username = $1
FOR UPDATE SKIP LOCKED limit 20;
But I need to make a query using Spring Data Jpa. To do this, I'm trying to use locks and limit. I am using Spring Boot 3.2.3 and PostgreSql.
@Service
@RequiredArgsConstructor
public class UserService {
private final UserRepository userRepository;
@Transactional
public void getUsersByUsernameWithLock() {
List<User> users = userRepository.findByUsername("user");
System.out.println(users, Limit.of(20));
//do something
//delete
}
}
public interface UserRepository extends JpaRepository<User, Long> {
List<User> findByUsernameAndBirthday(String username, LocalDate birthday);
@Lock(LockModeType.PESSIMISTIC_WRITE)
@QueryHints({@QueryHint(name = "jakarta.persistence.lock.timeout", value ="-2")})
List<User> findByUsername(String username, Limit limit);
}
When I look at the query that spring jpa builds, I see that it adds for no key update and offset instead of limit:
Hibernate:
/* <criteria> */ select
u1_0.user_id,
u1_0.birthday,
u1_0.username
from
users u1_0
where
u1_0.username=?
offset
? rows
fetch
first ? rows only for no key update skip locked
org.hibernate.orm.jdbc.bind : binding parameter (1:VARCHAR) <- [user]
org.hibernate.orm.jdbc.bind : binding parameter (2:INTEGER) <- [0]
org.hibernate.orm.jdbc.bind : binding parameter (3:INTEGER) <- [10]
If using offset and fetch instead of limit seems like a good practice, for no key update is not exactly what I would like. How do I tell JPA to generate a query with for update?