Home > Software design >  JPA/JPQL automatic method query generation
JPA/JPQL automatic method query generation

Time:07-16

I have a co-worker that just blew my mind. We have a Java 11/Spring Boot/Hibernate/JPA app talking to a MySQL DB. Apparently JPA JPQL (or something similar to that) is capable of -- but only if you write the repository methods correctly -- building out queries based on your method name.

So for instance if we have a JPA entity:

@Entity
@Table(name = "accounts")
@Data
public class Account {
    @Column(name = "account_email")
    private String email;

    // ... many more fields down here
}

And then a repository for it:

@Repository
public interface AccountRepository extends JpaRepository<Account,Long> {
    @Query("FROM Account WHERE email = :email")
    Account findByEmail(@Param(value = "email") String email);
}

Apparently (and this might be a bad example) I could just simplify that to:

@Repository
public interface AccountRepository extends JpaRepository<Account,Long> {
    Account findByEmail(String email);
}

And JPA/JPQL will figure out that since I want to "findByEmail" and Account#email exists, it just wants me to do a SELECT * FROM accounts where email = ?. Amazing!

The only problem is: I don't see this documented anywhere well, and I don't see it documented anywhere officially. There's a few old blogs that I was able to find that insinuate the same things, but nowhere official (JPA docs, JPQL docs, etc.) that go into detail as to how it works and what its limitations are.

Can anyone point me in the right direction? What is this mysterious feature/technology called and what are its limitations/capabilities? Can it only work on SELECTs or can it handle inserts/updates/deletes as well?

CodePudding user response:

This is part of the Spring Data support for JPA. You can find more info at the documentation Query Methods and all the supported query-keywords in the appendix section. Here is an excerpt from the documentation:

Query subject keywords

Keyword Description
find…By, read…By, get…By, query…By, search…By, stream…By General query method returning typically the repository type, a Collection or Streamable subtype or a result wrapper such as Page, GeoResults or any other store-specific result wrapper. Can be used as findBy…, findMyDomainTypeBy… or in combination with additional keywords.
exists…By Exists projection, returning typically a boolean result.
count…By Count projection returning a numeric result.
delete…By, remove…By Delete query method returning either no result (void) or the delete count.
…First…, …Top… Limit the query results to the first of results. This keyword can occur in any place of the subject between find (and the other keywords) and by.
…Distinct… Use a distinct query to return only unique results. Consult the store-specific documentation whether that feature is supported. This keyword can occur in any place of the subject between find (and the other keywords) and by.

Query predicate keywords

Logical keyword Keyword expressions
AND And
OR Or
AFTER After, IsAfter
BEFORE Before, IsBefore
CONTAINING Containing, IsContaining, Contains
BETWEEN Between, IsBetween
ENDING_WITH EndingWith, IsEndingWith, EndsWith
EXISTS Exists
FALSE False, IsFalse
GREATER_THAN GreaterThan, IsGreaterThan
GREATER_THAN_EQUALS GreaterThanEqual, IsGreaterThanEqual
IN In, IsIn
IS Is, Equals, (or no keyword)
IS_EMPTY IsEmpty, Empty
IS_NOT_EMPTY IsNotEmpty, NotEmpty
IS_NOT_NULL NotNull, IsNotNull
IS_NULL Null, IsNull
LESS_THAN LessThan, IsLessThan
LESS_THAN_EQUAL LessThanEqual, IsLessThanEqual
LIKE Like, IsLike
NEAR Near, IsNear
NOT Not, IsNot
NOT_IN NotIn, IsNotIn
NOT_LIKE NotLike, IsNotLike
REGEX Regex, MatchesRegex, Matches
STARTING_WITH StartingWith, IsStartingWith, StartsWith
TRUE True, IsTrue
WITHIN Within, IsWithin

CodePudding user response:

Not much of a mystery and quite well documented to be perfectly honest. What you mention is called a derived query which basically infers the JPQL query from the method name. A simple Google search can turn up quite some documentation of this feature. Also the Spring Data JPA documentation clearly documents this and well as its usage. Check here for more.

  • Related