JPA provides two primary query mechanisms: JPQL (Java Persistence Query Language) for string-based queries and the Criteria API for type-safe, programmatic query building.

JPQL Basics

JPQL operates on entities, not tables:

  // Select all
List<Book> books = em.createQuery("SELECT b FROM Book b", Book.class)
    .getResultList();

// With parameters
List<Book> books = em.createQuery(
    "SELECT b FROM Book b WHERE b.price > :minPrice ORDER BY b.title", Book.class)
    .setParameter("minPrice", new BigDecimal("20"))
    .setMaxResults(10)
    .getResultList();

// Aggregate
Long count = em.createQuery(
    "SELECT COUNT(b) FROM Book b WHERE b.category = :cat", Long.class)
    .setParameter("cat", "fiction")
    .getSingleResult();

JPQL Joins 

  // Join fetch (eager load association)
List<Order> orders = em.createQuery(
    "SELECT o FROM Order o JOIN FETCH o.items WHERE o.customer.id = :id", Order.class)
    .setParameter("id", customerId)
    .getResultList();

// Left join
List<Department> depts = em.createQuery(
    "SELECT d FROM Department d LEFT JOIN d.employees e WHERE e IS NULL", Department.class)
    .getResultList();

Update and Delete 

  int updated = em.createQuery(
    "UPDATE Book b SET b.price = b.price * 1.1 WHERE b.category = :cat")
    .setParameter("cat", "fiction")
    .executeUpdate();

int deleted = em.createQuery(
    "DELETE FROM Book b WHERE b.publishedDate < :cutoff")
    .setParameter("cutoff", LocalDate.of(2000, 1, 1))
    .executeUpdate();

Both require an active transaction.

Named Queries 

  @Entity
@NamedQueries({
    @NamedQuery(name = "Book.findByCategory",
                query = "SELECT b FROM Book b WHERE b.category = :category"),
    @NamedQuery(name = "Book.countByAuthor",
                query = "SELECT COUNT(b) FROM Book b WHERE b.author = :author")
})
public class Book { }

// Usage
List<Book> books = em.createNamedQuery("Book.findByCategory", Book.class)
    .setParameter("category", "sci-fi")
    .getResultList();

Criteria API

Type-safe alternative to JPQL strings:

  CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Book> cq = cb.createQuery(Book.class);
Root<Book> book = cq.from(Book.class);

cq.select(book)
  .where(
      cb.and(
          cb.greaterThan(book.get("price"), new BigDecimal("20")),
          cb.equal(book.get("category"), "fiction")
      )
  )
  .orderBy(cb.asc(book.get("title")));

List<Book> results = em.createQuery(cq).getResultList();

Dynamic Queries with Criteria 

  public List<Book> search(BookSearchCriteria criteria) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Book> cq = cb.createQuery(Book.class);
    Root<Book> root = cq.from(Book.class);

    List<Predicate> predicates = new ArrayList<>();
    if (criteria.getTitle() != null) {
        predicates.add(cb.like(root.get("title"), "%" + criteria.getTitle() + "%"));
    }
    if (criteria.getMinPrice() != null) {
        predicates.add(cb.ge(root.get("price"), criteria.getMinPrice()));
    }
    if (criteria.getCategory() != null) {
        predicates.add(cb.equal(root.get("category"), criteria.getCategory()));
    }

    cq.where(predicates.toArray(new Predicate[0]));
    return em.createQuery(cq).getResultList();
}

Native SQL Queries

When JPQL is insufficient:

  List<Object[]> rows = em.createNativeQuery(
    "SELECT b.title, a.name FROM books b JOIN authors a ON b.author_id = a.id " +
    "WHERE b.price > ?")
    .setParameter(1, 30.0)
    .getResultList();

// With result mapping
@SqlResultSetMapping(
    name = "BookAuthorMapping",
    classes = @ConstructorResult(
        targetClass = BookAuthorDto.class,
        columns = {
            @ColumnResult(name = "title"),
            @ColumnResult(name = "name")
        }
    )
)

Comparison

Feature JPQL Criteria API Native SQL
Type safety No Yes No
Dynamic queries Hard Easy Hard
Database-specific No No Yes
Readability High Medium High
Refactoring support Poor Excellent Poor

Best Practices

  • Use named queries for frequently executed static queries
  • Prefer Criteria API or Specifications for dynamic query building
  • Use JOIN FETCH to avoid N+1 problems in JPQL
  • Reserve native SQL for database-specific features (window functions, CTEs)
  • Always parameterize queries — never concatenate user input