This tutorial shows you how to create a query that only selects specific columns in Spring Data JPA.
If you have a database table with a lot of columns, but only need to access certain columns, it would be better if the query only selects the relevant columns. By doing so, you may improve the performance of the application and reduce the resource usage of the database server. If you're using Spring Data JPA, there are several ways to do it.
For example, we have an entity named Order with several fields as shown below.
@Table(name = "orders")
public class Order {
@Id
@GeneratedValue
@GenericGenerator(name = "UUID", type = UuidGenerator.class)
private UUID id;
private String orderNumber;
private BigDecimal totalPrice;
private ZonedDateTime createdAt;
private UUID userId;
private String note;
@JdbcTypeCode(SqlTypes.JSON)
private Map<String, Object> detail;
@OneToMany(fetch = FetchType.LAZY, mappedBy = "order")
private Set<OrderLine> orderLines;
}There is another entity named OrderLine. The relationship between those two entities are one-to-many where an order can have multiple order lines.
@Entity
@Table(name = "order_lines")
public class OrderLine {
@Id
@GeneratedValue
@GenericGenerator(name = "UUID", type = UuidGenerator.class)
private UUID id;
private int quantity;
private String productName;
private BigDecimal price;
private String note;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "order_id", nullable = false)
private Order order;
}Each field above represents a column in the database. In the example below, we are going to create queries that select only selected columns using several ways.
Using Interface Projection
For example, we only want to select id, orderNumber, totalPrice, and createdAt columns. Spring Data JPA allows you to fetch using an interface projection. First, you need to create the interface. The interface has to define a getter property for each column to be selected. You have to use the correct type and name for the getters. The name of the getter depends on the field name defined in the entity. For example, to select the id field above, add a getId() property whose return type is UUID.
public interface OrderView {
UUID getId();
String getOrderNumber();
BigDecimal getTotalPrice();
ZonedDateTime getCreatedAt();
}Then, you can create a derived query that returns the interface above in the JpaRepository.
public interface OrderRepository extends JpaRepository<Order, UUID> {
Optional<OrderView> findOneProjectedById(UUID id);
}If you check the generated query from the application log or the database log, it should only select the columns defined in the interface.
select o1_0.id,o1_0.order_number,o1_0.total_price,o1_0.created_at from orders o1_0 where o1_0.id=?
binding parameter [1] as [UUID] - [09af2dc3-b6db-4869-b00f-9ee02ce37f71]Spring also supports nested projections. For example, if you want to join the Order entity with its related OrderLine entities, you can create a projection interface that includes another projection interface inside.
public interface OrderWithLinesView {
UUID getId();
String getOrderNumber();
BigDecimal getTotalPrice();
ZonedDateTime getCreatedAt();
List<OrderLineView> getOrderLines();
interface OrderLineView {
UUID getId();
int getQuantity();
String getProductName();
BigDecimal getPrice();
}
}Below is the derived query that uses the nested interface above.
public interface OrderRepository extends JpaRepository<Order, UUID> {
List<OrderWithLinesView> findAllProjectedWithLinesByUserId(UUID id);
}Logs:
select o1_0.id,o1_0.created_at,o1_0.detail,o1_0.note,o1_0.order_number,o1_0.total_price,o1_0.user_id from orders o1_0 where o1_0.user_id=?
binding parameter [1] as [UUID] - [6e047d38-03af-43a0-9404-e7ebb64cb13e]
select o1_0.order_id,o1_0.id,o1_0.note,o1_0.price,o1_0.product_name,o1_0.quantity from order_lines o1_0 where o1_0.order_id=?
binding parameter [1] as [UUID] - [09af2dc3-b6db-4869-b00f-9ee02ce37f71]
select o1_0.order_id,o1_0.id,o1_0.note,o1_0.price,o1_0.product_name,o1_0.quantity from order_lines o1_0 where o1_0.order_id=?
binding parameter [1] as [UUID] - [ae2a557a-5a5f-445e-8b11-febc0ec5649d]You have to be careful when using nested interfaces. As you can see from the log above, it may cause n + 1 select problem, which generates a query for each matching row and therefore affects the database and system performance. It should be avoided especially for queries where the number of matching rows can be more than one. It's better to create a custom query for that case.
Using Class Projection
Another way to create a projection is by defining a class. You can create a class whose fields are the selected columns. The class is supposed to have a constructor that has one argument for every field in the class.
public class OrderDto {
private UUID id;
private String orderNumber;
private BigDecimal totalPrice;
private ZonedDateTime createdAt;
public OrderDto(
UUID id,
String orderNumber,
BigDecimal totalPrice,
ZonedDateTime createdAt
) {
this.id = id;
this.orderNumber = orderNumber;
this.totalPrice = totalPrice;
this.createdAt = createdAt;
}
// Getters and setters are not included
}If the class only has one constructor and, Spring can automatically generated the queries. Therefore, you can just write a derived query like the example below, where the class is used as the return type.
public interface OrderRepository extends JpaRepository<Order, UUID> {
Optional<OrderDto> findOneProjectedByOrderNumber(@Param("orderNumber") String orderNumber);
}Logs:
select o1_0.id,o1_0.order_number,o1_0.total_price,o1_0.created_at from orders o1_0 where o1_0.order_number=?
binding parameter [1] as [VARCHAR] - [1234]If the class has more than one constructor, Spring cannot automatically choose which one to use. As a result, you have to create a custom query which can be done using the @Query annotation. Then, write a JPA query that calls the constructor in the SELECT statement. To call the constructor, you have to use the fully-qualified class name which includes the package name. You may also need to explicitly call the constructor if the fields in the entity are different from the fields in the projection class .
public interface OrderRepository extends JpaRepository<Order, UUID> {
@Query(
"SELECT new com.woolha.jpamulticolumnselect.model.projection.OrderDto(o.id, o.orderNumber, o.totalPrice, o.createdAt)"
+ " FROM Order o"
+ " WHERE orderNumber = :orderNumber"
)
Optional<OrderDto> findOneProjectedByOrderNumber(@Param("orderNumber") String orderNumber);
}Using Scalar Projection
If you don't want to create an interface or class, you can use the scalar projection. What you need to do is to create a JPA query that selects only the columns you want and set the return type to List<Object[]>. It returns a list of object arrays whose elements are the values of each selected field.
public interface OrderRepository extends JpaRepository<Order, UUID> {
@Query("SELECT o.id, o.orderNumber, o.totalPrice, o.createdAt FROM Order o WHERE userId = :userId")
List<Object[]> findAllProjectedByUserId(@Param("userId") UUID userId);
}Logs:
select o1_0.id,o1_0.order_number,o1_0.total_price,o1_0.created_at from orders o1_0 where o1_0.user_id=?
binding parameter [1] as [UUID] - [6e047d38-03af-43a0-9404-e7ebb64cb13e]The downside of using this method is accessing the values becomes more difficult. To access the value of a column, you have to obtain it from a specific index of the object array. In addition, you cannot get the typed results since a matching row is returned as Object[]. Therefore, to get the value of a column, you have to get an element of the object array at a specific index and cast the value.
Using CriteriaQuery.multiselect
JPA allows you to create a custom query using CriteriaQuery. The CriteriaQuery class has a method called multiselect which can be used to select which columns should be included in the query.
public Optional<OrderDto> findOneProjectedUsingCriteriaQueryById(UUID id) {
CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Order> root = cq.from(Order.class);
cq
.multiselect(
root.get(Order_.id).alias("id"),
root.get(Order_.orderNumber).alias("orderNumber"),
root.get(Order_.totalPrice).alias("totalPrice"),
root.get(Order_.createdAt).alias("createdAt")
)
.where(cb.equal(root.get(Order_.id), id));
try {
Tuple tuple = entityManager
.createQuery(cq)
.setMaxResults(1)
.getSingleResult();
OrderDto orderDto = OrderDto.builder()
.id((UUID) tuple.get("id"))
.orderNumber((String) tuple.get("orderNumber"))
.totalPrice((BigDecimal) tuple.get("totalPrice"))
.createdAt((ZonedDateTime) tuple.get("createdAt"))
.build();
return Optional.of(orderDto);
} catch (NoResultException ex) {
return Optional.empty();
}
}Logs:
select o1_0.id,o1_0.order_number,o1_0.total_price,o1_0.created_at from orders o1_0 where o1_0.id=? fetch first ? rows only
binding parameter [1] as [UUID] - [09af2dc3-b6db-4869-b00f-9ee02ce37f71]
binding parameter [2] as [INTEGER] - [1]With this way, you can also create a nested projection that doesn't cause the n + 1 select problem. For example, to join the Order entity with its related OrderLine entities, you can write the following code to define the join query.
Join<Order, OrderLine> orderLineJoin = root.join(Order_.orderLines);Then, select the columns to be projected in the multiselect.
public Optional<OrderWithLinesDto> findOneWithLinesProjectedUsingCriteriaQueryById(UUID id) {
CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Order> root = cq.from(Order.class);
Join<Order, OrderLine> orderLineJoin = root.join(Order_.orderLines);
cq
.multiselect(
root.get(Order_.id).alias("id"),
root.get(Order_.orderNumber).alias("orderNumber"),
root.get(Order_.totalPrice).alias("totalPrice"),
root.get(Order_.createdAt).alias("createdAt"),
orderLineJoin.get(OrderLine_.id).alias("orderLineId"),
orderLineJoin.get(OrderLine_.quantity).alias("orderLineQuantity"),
orderLineJoin.get(OrderLine_.productName).alias("orderLineProductName"),
orderLineJoin.get(OrderLine_.price).alias("orderLinePrice")
)
.where(cb.equal(root.get(Order_.id), id));
try {
List<Tuple> tuples = entityManager
.createQuery(cq)
.getResultList();
List<OrderWithLinesDto.OrderLineDto> orderLines = tuples.stream()
.map(tuple -> new OrderWithLinesDto.OrderLineDto(
(UUID) tuple.get("orderLineId"),
(Integer) tuple.get("orderLineQuantity"),
(String) tuple.get("orderLineProductName"),
(BigDecimal) tuple.get("orderLinePrice")
))
.toList();
OrderWithLinesDto orderWithLinesDto = new OrderWithLinesDto(
(UUID) tuples.get(0).get("id"),
(String) tuples.get(0).get("orderNumber"),
(BigDecimal) tuples.get(0).get("totalPrice"),
(ZonedDateTime) tuples.get(0).get("createdAt"),
orderLines
);
return Optional.of(orderWithLinesDto);
} catch (NoResultException ex) {
return Optional.empty();
}
}Output:
select o1_0.id,o1_0.order_number,o1_0.total_price,o1_0.created_at,o2_0.id,o2_0.quantity,o2_0.product_name,o2_0.price from orders o1_0 join order_lines o2_0 on o1_0.id=o2_0.order_id where o1_0.id=?
binding parameter [1] as [UUID] - [09af2dc3-b6db-4869-b00f-9ee02ce37f71]Summary
There are several ways to create a query that only selects specific columns. If possible, you should try to use the interface projection because it's the most simple. Other alternatives include using class projection, scalar projection, or JPA's CriteriaQuery.multiselect
The file for this tutorial can be downloaded here.
You can also read about:
