JPA Part 9: Native Queries and the Criteria API

Part 8 queried with derived methods and JPQL. This part covers two alternatives: native queries, plain SQL for when JPQL is not enough, and the Criteria API, for queries assembled in Java code when the conditions are known only at run time, such as optional search filters.

Native queries

A native query is plain SQL, sent to the database as written.

• It uses table and column names and your database's SQL dialect, so if you change databases, the queries may need to change too.
• JPA does not translate or check it. If it returns full entity rows, the entities are still managed (they enter the persistence context like any other); partial rows or UPDATE/DELETE statements bypass the persistence context entirely.

Use it instead of JPQL for:

• database-specific features, such as PostgreSQL's JSONB operators or LATERAL joins
• complex reporting queries that are easier in SQL
• bulk operations where speed matters, since nothing has to be kept in sync in the persistence context

(Two things JPQL can do, although you may hear otherwise: aggregates such as SELECT COUNT(u) FROM UserDetails u, and, in Hibernate, joins between unrelated entities with JOIN UserAddress a ON ....)

@Repository
public interface UserDetailsRepository extends JpaRepository<UserDetails, Long> {

    @Query(value = "SELECT * FROM user_details WHERE user_name = :userFirstName", nativeQuery = true)
    List<UserDetails> getUserDetailsByNameNativeQuery(@Param("userFirstName") String userName);
}

nativeQuery = true marks it as SQL. With SELECT *, every column is there, so JPA maps the columns to the entity's fields automatically.

Partial columns

@Query(value = "SELECT user_name, phone FROM user_details WHERE user_name = :userFirstName", nativeQuery = true)
List<UserDetails> getUserDetailsByNameNativeQuery(@Param("userFirstName") String userName);

This fails:

Unable to find column position by name: user_id [Column "user_id" not found]

An entity needs all its columns (at least the id). For partial results, tell JPA how to map them. Two ways.

1. @SqlResultSetMapping with @NamedNativeQuery, on the entity:

@Table(name = "user_details")
@Entity
@NamedNativeQuery(
    name = "UserDetails.getUserDetailsByName",
    query = "SELECT user_name, phone FROM user_details WHERE user_name = :userFirstName",
    resultSetMapping = "UserDTOMapping"
)
@SqlResultSetMapping(
    name = "UserDTOMapping",
    classes = @ConstructorResult(
        targetClass = UserDTO.class,
        columns = {
            @ColumnResult(name = "user_name", type = String.class),
            @ColumnResult(name = "phone", type = String.class)
        }
    )
)
public class UserDetails { ... }

public class UserDTO {
    String userName;
    String phone;

    public UserDTO(String userName, String phone) {
        this.userName = userName;
        this.phone = phone;
    }
    // getters and setters
}

@Query(name = "UserDetails.getUserDetailsByName", nativeQuery = true)
List<UserDTO> getUserDetailsByNameNativeQuery(@Param("userFirstName") String userName);

Each row is passed to the UserDTO constructor, columns in the listed order.

2. Manual mapping from Object[]:

@Query(value = "SELECT user_name, phone FROM user_details WHERE user_name = :userFirstName", nativeQuery = true)
List<Object[]> getUserDetailsByNameNativeQuery(@Param("userFirstName") String userName);

public List<UserDTO> getUserDetailsByNameNativeQuery(String name) {
    List<Object[]> results = userDetailsRepository.getUserDetailsByNameNativeQuery(name);
    return results.stream()
            .map(obj -> new UserDTO((String) obj[0], (String) obj[1]))
            .collect(Collectors.toList());
}

Dynamic native queries

An @Query string is fixed. When the conditions depend on the input (filter by name only if a name was given), build the SQL at runtime and run it through the EntityManager:

@Service
public class UserDetailsService {

    @PersistenceContext
    private EntityManager entityManager;

    public List<UserDTO> getUserDetailsByNameNativeQuery(String userName) {
        StringBuilder queryBuilder = new StringBuilder(
                "SELECT ud.user_name AS user_name, ud.phone AS phone, ua.city AS city ");
        queryBuilder.append("FROM user_details ud ");
        queryBuilder.append("JOIN user_address ua ON ud.user_address_id = ua.id ");
        queryBuilder.append("WHERE 1=1 ");

        List<Object> parameters = new ArrayList<>();

        // add conditions only when needed
        if (userName != null && !userName.isEmpty()) {
            queryBuilder.append("AND ud.user_name = ? ");
            parameters.add(userName);
        }

        Query nativeQuery = entityManager.createNativeQuery(queryBuilder.toString());
        for (int i = 0; i < parameters.size(); i++) {
            nativeQuery.setParameter(i + 1, parameters.get(i));
        }

        List<Object[]> result = nativeQuery.getResultList();
        return UserDTO.mapResultToDTO(result);
    }
}

WHERE 1=1 is always true; it just lets every real condition start with AND. Values go in as ? parameters, never glued into the string, which would open the door to SQL injection (see the chapter on attacks).

Pagination and sorting

1. In the SQL, for dynamic queries:

queryBuilder.append("ORDER BY ud.user_name DESC");

int size = 5;
int page = 0;
queryBuilder.append(" LIMIT ? OFFSET ? ");
parameters.add(size);
parameters.add(page * size);

2. With a Pageable, for @Query native queries:

@Query(value = "SELECT * FROM user_details ud WHERE ud.user_name = :userName", nativeQuery = true)
List<UserDetails> getUserDetailsByNameNativeQuery(@Param("userName") String userName, Pageable pageable);

Pageable pageableObj = PageRequest.of(0, 5, Sort.by("phone").descending());
return userDetailsRepository.getUserDetailsByNameNativeQuery(name, pageableObj);

Spring Data appends the ORDER BY and the limit and offset to your SQL. (In a native query, the sort names are used as column names.)

Criteria API

Native SQL can be built dynamically but is database-dependent and bypasses JPA's abstraction. JPQL is portable but is a string: a typo shows up only when the query runs. The Criteria API builds queries as Java objects: dynamic, portable, and checked by the compiler as far as possible.

The pieces:

@Table(name = "user_details")
@Entity
public class UserDetails {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long userId;

    @Column(name = "user_name")
    private String name;

    private Long phone;
    // getters and setters
}

@Service
public class UserDetailsService {

    @PersistenceContext
    private EntityManager entityManager;

    public List<UserDetails> getUserDetailsByPhoneCriteriaAPI(Long phoneNo) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();

        CriteriaQuery<UserDetails> crQuery = cb.createQuery(UserDetails.class);  // each row is a UserDetails
        Root<UserDetails> user = crQuery.from(UserDetails.class);               // FROM user_details
        crQuery.select(user);                                                    // SELECT all its columns

        Predicate predicate = cb.equal(user.get("phone"), phoneNo);             // WHERE phone = ?
        crQuery.where(predicate);

        TypedQuery<UserDetails> query = entityManager.createQuery(crQuery);
        return query.getResultList();
    }
}

Building conditions

user is the Root<UserDetails> from above.

Method	SQL
cb.equal(user.get("phone"), 123)	phone = 123
cb.notEqual(user.get("phone"), 123)	phone <> 123
cb.gt(user.get("phone"), 123)	phone > 123
cb.ge(user.get("phone"), 123)	phone >= 123
cb.lt(user.get("phone"), 123)	phone < 123
cb.le(user.get("phone"), 123)	phone <= 123
cb.and(predicate1, predicate2)	condition1 AND condition2
cb.or(predicate1, predicate2)	condition1 OR condition2
cb.not(predicate1)	NOT condition1
cb.like(user.get("name"), "S%")	name LIKE 'S%'
cb.notLike(user.get("name"), "S%")	name NOT LIKE 'S%'
cb.in(user.get("phone")).value(11).value(7)	phone IN (11, 7)
cb.not(user.get("phone").in(11, 7))	phone NOT IN (11, 7)

Selecting some fields

Make each row an Object[] and use multiselect:

CriteriaQuery<Object[]> crQuery = cb.createQuery(Object[].class);
Root<UserDetails> user = crQuery.from(UserDetails.class);
crQuery.multiselect(user.get("name"), user.get("phone"));
crQuery.where(cb.equal(user.get("phone"), phoneNo));

List<Object[]> results = entityManager.createQuery(crQuery).getResultList();
List<UserDTO> output = new ArrayList<>();
for (Object[] row : results) {
    output.add(new UserDTO((String) row[0], (Long) row[1]));
}

Joins

With a one-to-one userAddress field:

CriteriaQuery<Object[]> crQuery = cb.createQuery(Object[].class);
Root<UserDetails> user = crQuery.from(UserDetails.class);
Join<UserDetails, UserAddress> address = user.join("userAddress", JoinType.INNER);

crQuery.multiselect(user.get("name"), address.get("city"));
crQuery.where(cb.equal(user.get("phone"), phoneNo));

Pagination and sorting

crQuery.orderBy(cb.desc(user.get("name")));          // ORDER BY name DESC

TypedQuery<UserDetails> query = entityManager.createQuery(crQuery);
query.setFirstResult(0);                              // offset: rows to skip
query.setMaxResults(5);                               // page size
List<UserDetails> results = query.getResultList();

For page n of size s, the offset is n * s.

Next: The Criteria API is verbose and repeats conditions. Part 10 shows Spring Data's Specification API, which fixes both problems.

Check yourself

1. What is a native query, and what are its drawbacks compared with JPQL?
2. Give three reasons to use a native query.
3. A native query returns SELECT * rows as UserDetails. Are those entities managed?
4. A native query selects only user_name, phone into List<UserDetails>. What happens, and what are the two ways to fix it?
5. Why does a dynamic query use WHERE 1=1, and why must values be passed as parameters?
6. How do you paginate a native @Query?
7. Can JPQL count rows? Can Hibernate's JPQL join unrelated entities?
8. What problem does the Criteria API solve that native SQL and JPQL strings do not?
9. What are CriteriaBuilder, CriteriaQuery, Root and Predicate for?
10. How do you write WHERE phone NOT IN (11, 7) with the Criteria API?
11. How do you get the third page (page index 2, size 5) of a criteria query?

Answers

1. Plain SQL sent as written. It is database-specific, is not checked or translated by JPA, and partial results or bulk changes bypass the persistence context.
2. Database-specific features (JSONB, LATERAL), complex SQL-style reports, fast bulk operations.
3. Yes; full entity rows are managed like any others.
4. It fails ("column user_id not found"). Map to a DTO with @SqlResultSetMapping + @NamedNativeQuery, or return Object[] and map manually.
5. So every added condition can start with AND. Gluing values into the SQL allows SQL injection.
6. Add a Pageable parameter; Spring Data appends ORDER BY, limit and offset. Or write ORDER BY ... LIMIT ? OFFSET ? yourself.
7. Yes (SELECT COUNT(u) FROM UserDetails u). Yes, with JOIN Entity e ON ....
8. Building queries dynamically in Java, portably and type-safely, instead of concatenating strings.
9. CriteriaBuilder creates queries and conditions; CriteriaQuery is the query and its row type; Root is the FROM entity; Predicate is a WHERE condition.
10. crQuery.where(cb.not(user.get("phone").in(11, 7))).
11. query.setFirstResult(10); query.setMaxResults(5); (offset = 2 × 5).