Best approach to manage complex queries with complex DTO

Question

I am using Spring JPA Specifications to create criteria query. My code looks like this:

 public CollectionWrapper<OrderTableItemDto> getOrders(Integer page,
                                         Integer size,
                                         String search,
                                         OrderShow show) {

    Pageable pageable = PageRequest.of(page, size, Sort.by(OrderEntity_.ID).descending());

    try {
        Specification<OrderEntity> specifications = buildOrderSpecification(show, search);
        Page<OrderEntity> orders = orderDao.findAll(specifications, pageable);

        List<OrderTableItemDto> result = orders.getContent().stream().map(order -> {
            OrderTableItemDto orderTableItemDto = new OrderTableItemDto();
            orderTableItemDto.setCreatedDate(order.getCreatedDate());
            orderTableItemDto.setCustomerId(order.getCustomer().getId());
            orderTableItemDto.setId(order.getId());
            orderTableItemDto.setStatus(order.getStatus());
            orderTableItemDto.setTotalCost(order.getTotalCost());
            orderTableItemDto.setOrderType(order.getOrderType());
            orderTableItemDto.setOrderTypeLabel(order.getOrderType().getLabel());
            if(order.getOrderInShippingMethod() != null) {
                orderTableItemDto.setShipped(OrderShippingStatus.DELIVERED.equals(order.getOrderInShippingMethod().getStatus()));
            } else {
                orderTableItemDto.setShipped(false);
            }
            StringBuilder sb = new StringBuilder();
            sb.append("#")
                    .append(order.getId())
                    .append(" ");
            if(order.getOrderType().equals(OrderType.WEB_STORE)) {
                sb
                        .append(order.getBilling().getFirstName())
                        .append(" ")
                        .append(order.getBilling().getLastName());

            } else {
                sb.append(order.getCustomer().getFullName());
            }

            orderTableItemDto.setTitle(sb.toString());
            return orderTableItemDto;
        }).collect(Collectors.toList());
        return new CollectionWrapper<>(result, orders.getTotalElements());

I was told it is bad approach and that i should use projections (DTOs) to read from database, because it is expensive to create entities and to map them afterward. The problem is that I do not know how can I combine specifications with DTOs. What would be the optimal way in order to manage complex and dynamic queries (filters from user inputs etc.) with complex DTOs which contains nested DTOs and lots of properties?

score 1 · Answer 1 · answered Apr 17 '20 at 06:52

I don't buy the argument that creating an entity is faster then using DTOs directly. JPA still has to provide an intermediate representation before DTOs are created. And even if it is faster or uses less memory the question is: Is it relevant?

If performance is the reason for this code change, the first thing you should do is to implement a proper benchmark. See How to run JMH from inside JUnit tests?

You have as so often various options to create DTOs.

With derived queries (those based on the method name) or based on @Query annotations you may use class based DTOs, i.e. classes that look like your entity but may have fewer properties. Or you may use interfaces which only have getters for the values you are interested in. In both cases you may replace the return type with the projection. See https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections

If you are using JPQL or Criteria API you may use constructor expressions. See for example this answer for an example: https://stackoverflow.com/a/12286281/66686

Of course when you are tuning on this level you should consider if JPA is wasting performance and compare it to something that works more directly on the database, for example JdbcTemplate, Querydsl or jOOQ.

score 0 · Answer 2 · answered Apr 17 '20 at 12:59

Fetching entities and then creation DTOs is usually a waste because entities normally have a lot more columns than you really need. If you use eager fetching somewhere, you will also do unnecessary joins. If you have nested collections or complex expressions which you want to fetch, checkout Blaze-Persistence Entity-Views, a library that works on top of JPA which allows you map arbitrary structures against your entity model. It allows you to decouple the projection from your business logic i.e. you can apply that DTO to an existing query. An entity view for your use case could look like this

@EntityView(OrderEntity.class)
interface OrderTableItemDto {
  @IdMapping
  Long getId();
  Date getCreatedDate();
  String getStatus();
  BigDecimal getTotalCost();
  @Mapping("orderType.label")
  String getOrderTypeLabel();
  @Mapping("CASE WHEN orderInShippingMethod.status = OrderShippingStatus.DELIVERED THEN true ELSE false END")
  boolean isShipped();
  @Mapping("CONCAT('#', id, CASE WHEN orderType = OrderType.WEB_STORE THEN CONCAT(billing.firstName, ' ', billing.lastName) ELSE customer.fullName END)")
  String getTitle();
}

With the spring data integration provided by Blaze-Persistence you can define a repository like this and directly use the result

@Transactional(readOnly = true)
interface OrderRepository extends Repository<OrderEntity, Long> {
  Page<OrderTableItemDto> findAll(Specification<OrderEntity> specification, Pageable pageable);
}

It will generate a query that selects just what you mapped in the OrderTableItemDto.

Best approach to manage complex queries with complex DTO

2 Answers2