In modern Java Spring based applications Spring Data JPA is quite common way to interface with database. Domain/Business objects carry persistable state of the business process. With few JPA annotations, POJOs can be enhanced to persistable domain objects. Unlike Grails framework that underpins HIBERNATE, leverages GORM and elevates and enriches domain objects to the higher level by making them persistence aware, Spring Data JPA keeps the persistence in another abstraction layer called Repository.
With Spring Data JPA, Repository is the central interface and it requires one be familiar with Repository abstractions. Queries can be defined as interface methods and implementation is provided by Spring Data JPA framework by 1) deriving from method naming conventions 2) using manually defined queries with @Query annotation by writing JPQL or native SQL queries. My first choice is interface method naming by following the conventions. Next is JPQL. I avoid native queries unless there is a strong reason for.
JPQL Limitation with LIMIT fetch
One of the limitations I ran into recently with JPQL was limiting query results to limit fetching to limited number of records, say one record from the query results. Typically in native SQL this is done by adding LIMIT clause by specifying LIMIT 1 to limit to the first result to fetch. JPQL lets you specify LIMIT which also works, but under the covers the LIMIT is applied in memory to the results fetched. In other words the LIMIT clause doesn't exist in the generated native SQL. So, the SQL fetches all the results that match the criteria and a collection of entity objects get created and then the LIMIT is applied to get one object. With this the JPQL query does it's job as specified but will incur into expensive query by fetching more than needed records and creating the objects in collection and then returning one object by considering the LIMIT 1.
So, an example Repository method annotated like the following would return one object, but fetches all records that match the criteria into memory and return the first one from the collection.
@Query("""
SELECT msg
FROM Message msg
WHERE msg.type = :type
ORDER BY msg.createdOn DESC
LIMIT 1
""")
Optional<Message> findLatestByType(MessageType type);
In order to truly fetch the most recent message of a given type the JPQL needs to be optimized to fetch only one record.
With JPQL the query may need to be rewritten something like the following without using LIMIT, assuming id is primary key, and is a sequence. It is more performant with no additional index created than using createdOn auditable column if there is one.
@Query("""
SELECT msg
FROM Message msg
WHERE msg.id = (
SELECT MAX(m.id) FROM Message m
WHERE m.type = :type
)
""")
Optional<Message> findLatestByType(MessageType type);
The last resort is by writing a native query and using LIMIT 1 to fetch one.
Posts
