[JAVA] Map only specific columns to POJO by searching using Repository

Overview

There was a way to get only the necessary columns in the search using Repository of Spring Data JPA, so I wrote the code and tried it. I used to write it in JPQL like SELECT new com.example.domain.Fuga (...) FROM Hoge AS hoge ..., but now it's much easier to implement. ..

environment

reference

Sample code

Entity class

package com.example.domain.entity;

import com.example.domain.StandardType;
import lombok.*;

import javax.persistence.*;
import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.List;

@Entity
@Table(name="item")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ToString(exclude = {"itemStocks"})
@EqualsAndHashCode(exclude = {"itemStocks"})
public class Item implements Serializable {

    private static final long serialVersionUID = -3153084093423004609L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name="name", nullable = false)
    private String name;
    @Column(name="price", nullable = false)
    private Integer price;
    @Column(name="sales_from", nullable = false)
    private LocalDateTime salesFrom;
    @Column(name="sales_to", nullable = false)
    private LocalDateTime salesTo;
    @Enumerated(EnumType.ORDINAL)
    @Column(name="standard_type", nullable = false)
    private StandardType standardType;
    @JoinColumn(name = "category_id", nullable = false)
    @ManyToOne
    private Category category;
    @Column(name="del_flag", nullable = false)
    private Boolean delFlag;
    @Column(name="create_at", nullable = false)
    private LocalDateTime createAt;
    @Column(name="update_at", nullable = false)
    private LocalDateTime updateAt;

    @OneToMany(mappedBy = "item", cascade = CascadeType.ALL)
    private List<ItemStock> itemStocks;
}

Dto class

A Dto class that has only id, name, and price of Item entities. Maps the results searched in the Repository to this class.

package com.example.domain.dto;

import lombok.Value;

@Value
public class ItemNameAndPrice {
    private Long id;
    private String name;
    private Integer price;
}

Repository class

package com.example.domain.repository;

import com.example.domain.entity.Item;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ItemRepository extends JpaRepository<Item, Long> {
    // Class-Search methods for based projections
    <T> T findOneById(Long id, Class<T> type);
}

Search using findOne

Item item = repository.findOne(1L);

SQL issued

select
    item0_.id as id1_1_0_,
    item0_.category_id as categor10_1_0_,
    item0_.create_at as create_a2_1_0_,
    item0_.del_flag as del_flag3_1_0_,
    item0_.name as name4_1_0_,
    item0_.price as price5_1_0_,
    item0_.sales_from as sales_fr6_1_0_,
    item0_.sales_to as sales_to7_1_0_,
    item0_.standard_type as standard8_1_0_,
    item0_.update_at as update_a9_1_0_,
    category1_.id as id1_0_1_,
    category1_.create_at as create_a2_0_1_,
    category1_.del_flag as del_flag3_0_1_,
    category1_.name as name4_0_1_,
    category1_.update_at as update_a5_0_1_ 
from
    item item0_ 
inner join
    category category1_ 
       on item0_.category_id=category1_.id 
where
    item0_.id=?

Class-based projections

ItemNameAndPrice nameAndPrice = repository.findOneById(1L, ItemNameAndPrice.class);

It doesn't change that Dto is new internally.

select new com.example.domain.dto.ItemNameAndPrice(
    generatedAlias0.id,
    generatedAlias0.name,
    generatedAlias0.price) 
from
    Item as generatedAlias0 
where
    generatedAlias0.id=:param0

SQL issued

select
    item0_.id as col_0_0_,
    item0_.name as col_1_0_,
    item0_.price as col_2_0_ 
from
    item item0_ 
where
    item0_.id=?

Recommended Posts

Map only specific columns to POJO by searching using Repository
[Java] Add quotes only to specific CSV columns
How to delete only specific data from data created using Form object