Create a Repository

If you have no idea about EzyJPA, please take a look at the introduction.

Let's say we have a `Book` entity:

@Entity
public class Book extends CommonEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Long categoryId;
    private Long authorId;
    private String name;
    private BigDecimal price;
    private LocalDate releaseDate;
    private LocalDateTime releaseTime;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;
}

To create a repository, declare an interface and annotate it with @EzyRepository:

@EzyRepository
public interface BookRepository extends EzyDatabaseRepository<Long, Book> {}

The first generic type is the ID type, and the second one is the entity type. In this example, Long is the type of Book.id.

Available Methods

EzyDatabaseRepository already provides common CRUD methods, so you do not need to declare them again.

Common methods include:

bookRepository.save(book);
bookRepository.save(books);

Book book = bookRepository.findById(bookId);
Book book = bookRepository.findByField("name", "Clean Code");

List<Book> books = bookRepository.findListByIds(bookIds);
List<Book> books = bookRepository.findListByField("authorId", authorId);
List<Book> books = bookRepository.findListByField("authorId", authorId, 0, 20);
List<Book> books = bookRepository.findAll();
List<Book> books = bookRepository.findAll(0, 20);

boolean existed = bookRepository.containsById(bookId);
boolean existed = bookRepository.containsByField("name", "Clean Code");

bookRepository.delete(bookId);
bookRepository.deleteByIds(bookIds);
bookRepository.deleteAll();

long total = bookRepository.count();

When you call save, EzyJPA uses JPA merge inside a transaction. If the entity has an auto-generated ID, the generated ID is set back to the input entity after the transaction is committed.

For a complete example, you can look at here.

Query Methods From Method Name

EzyJPA can generate JPQL from repository method names. The supported prefixes are:

findBy
countBy
deleteBy

For example:

Book findByNameAndAuthorId(String name, Long authorId);

This method is converted to a query similar to:

SELECT e FROM Book e WHERE e.name = ?0 AND e.authorId = ?1

The field name is taken from the method name by lower-casing the first character. So AuthorId becomes authorId, CategoryId becomes categoryId.

You can combine conditions with And and Or:

List<Book> findByCategoryIdOrAuthorId(Long categoryId, Long authorId);

List<Book> findByCategoryIdAndName(Long categoryId, String name);

List<Book> findByCategoryIdAndAuthorIdOrName(
    Long categoryId,
    Long authorId,
    String name
);

The last method means:

WHERE (e.categoryId = ?0 AND e.authorId = ?1) OR e.name = ?2

Supported Operators

By default, a condition means equality:

Book findByName(String name);

EzyJPA also supports these suffixes:

Suffix	Meaning	Example
`In`	`in`	`findByAuthorIdIn(List<Long> authorIds)`
`Gt`	`>`	`findByPriceGt(BigDecimal price)`
`Gte`	`>=`	`findByReleaseDateGte(LocalDate date)`
`Lt`	`<`	`findByPriceLt(BigDecimal price)`
`Lte`	`<=`	`findByReleaseDateLte(LocalDate date)`

Examples:

List<Book> findByCategoryIdIn(List<Long> categoryIds);

List<Book> findByPriceGt(BigDecimal price);

List<Book> findByPriceGteAndReleaseDateLt(
    BigDecimal price,
    LocalDate releaseDate
);

int countByAuthorId(Long authorId);

int deleteByAuthorId(Long authorId);

For countBy..., the return type must be int or long.

For deleteBy..., the return type must be void or int. If it returns int, the value is the number of deleted rows.

Pagination

EzyJPA uses the Next class to represent skip and limit.

You can create a Next object by:

Next.fromSkipLimit(skip, limit);

// or
Next.fromPageSize(page, size);

A query method is treated as paginated when the last parameter is a Next object:

List<Book> findByAuthorId(Long authorId, Next next);

List<Book> books = bookRepository.findByAuthorId(
    authorId,
    Next.fromSkipLimit(0, 20)
);

The Next parameter must be the last parameter. EzyJPA does not bind it as a query parameter; it uses it to call setFirstResult and setMaxResults.

When To Use @EzyQuery

Method-name queries are good for simple conditions. For sorting, joins, projections, aggregations, native SQL, or more complex filtering, use @EzyQuery.

@EzyQuery("select e from Book e order by e.price desc, e.id desc")
List<Book> findBooksOrderByPriceAndId(Next next);

You can bind parameters by index:

@EzyQuery("select e from Book e where e.name = ?0 and e.authorId = ?1")
Book findByNameAndAuthorId(String name, Long authorId);

You can also use native SQL:

@EzyQuery(
    value = "select * from book e " +
        "where e.price > ?0 or (e.price = ?0 and e.id > ?1) " +
        "order by e.price desc, e.id desc",
    nativeQuery = true
)
List<Book> findBooks(
    BigDecimal priceExclusive,
    long idExclusive,
    Next next
);

For single-result methods, EzyJPA limits the result to one row and returns null if no row is found:

@EzyQuery("select e from Book e where e.name = ?0")
Book findOneByName(String name);

You can also return Optional:

@EzyQuery("select e from Book e where e.name = ?0")
Optional<Book> findOptionalByName(String name);

Count, Update, And Delete Queries

If an @EzyQuery method name starts with count, EzyJPA calls getSingleResult and converts the result to int or long:

@EzyQuery("select count(e) from Book e where e.authorId = ?0")
long countBooksByAuthorId(Long authorId);

If a method name starts with update or delete, EzyJPA executes it inside a transaction:

@EzyQuery("update Book e set e.price = ?1 where e.id = ?0")
int updatePrice(Long id, BigDecimal price);

@EzyQuery("delete from Book e where e.authorId = ?0")
int deleteByAuthorId(Long authorId);

The return type of update/delete query methods must be void or int.

Custom Query Result

@EzyQuery can return custom result objects. This is useful when you only select some columns or aggregate values.

public class SumBookPriceResult {
    private BigDecimal sum;
}

@EzyQuery(
    value = "select sum(e.price) as sum from book e",
    nativeQuery = true,
    resultType = SumBookPriceResult.class
)
SumBookPriceResult sumPrice();

For list results:

public class BookNamePriceResult {
    private String name;
    private BigDecimal price;
}

@EzyQuery(
    value = "select e.name, e.price from Book e where e.authorId = ?0",
    resultType = BookNamePriceResult.class
)
List<BookNamePriceResult> fetchNameAndPriceByAuthorId(Long authorId);

When resultType is not the entity type, EzyJPA deserializes each row into the result class.

Native Query Notes

For native queries that return the entity type, EzyJPA creates a native query with the entity class:

@EzyQuery(
    value = "select * from book where id = ?0",
    nativeQuery = true
)
Book findBookByNativeQuery(Long id);

For native queries that return custom result objects, specify resultType:

@EzyQuery(
    value = "select name, price from book where id = ?0",
    nativeQuery = true,
    resultType = BookNamePriceResult.class
)
BookNamePriceResult findBookNameAndPrice(Long id);

Notes And Limitations

Method-name queries support And, Or, In, Gt, Gte, Lt, and Lte.

Sorting such as OrderByPriceDesc is not parsed from method names. Use @EzyQuery for order by.

A Next parameter is only recognized as pagination when it is the last parameter.

The number of method parameters must match the number of generated conditions, excluding the final Next parameter.

Full source code of examples is available on GitHub.

You can take a look at how to use custom query result.

EzyJPA: Query Data