Spring JPA: Select specific columns on join with annotation/JPQL

Question

I am trying to get familiar with spring and jpa. For start there is a table anime_details containing details of an anime. As it can have many genres, db has another table named genre. The intermediate table to contain their many to many relationship entries is also there. When I query for any anime by id, it should return the details of the anime along with the genres.

It does return an object with details of the anime and list of Genre objects (which is as expected). But what I want is to restrict the columns that will be fetched from Genre objects. For example only id or just id and name (In case there are more columns other than these).

AnimeDetails

@Getter
@Setter
@Entity
@Table(name = "anime_details")
public class AnimeDetails {
    @Id
    @SequenceGenerator(name = "animeDetailsSeq", sequenceName =
        "anime_details_id_seq",
        allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator =
        "animeDetailsSeq")
    private Integer id;

    private String name;
    private Integer episodes;
    private Date started;
    private Date ended;
    private String status;

    @ManyToMany
    @JoinTable(
        name = "anime_genre",
        joinColumns = @JoinColumn(name = "details_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "genre_id", referencedColumnName = "id"))
    @JsonManagedReference
    private List<Genre> genres;

    protected AnimeDetails() {
    }
}

Genre

@Data
@Entity
@Table(name = "genre")
public class Genre {
    @Id
    @SequenceGenerator(name = "genreSeq", sequenceName = "genre_id_seq",
        allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "genreSeq")
    private Integer id;
    private String name;

    @ManyToMany(mappedBy = "genres")
    List<AnimeDetails> animes;

    protected Genre() {

    }
}

Expected payload

 {
    "id": 2,
    "name": "Your Name",
    "episodes": 1,
    "started": "2016-08-25T18:00:00.000+0000",
    "ended": "2016-08-25T18:00:00.000+0000",
    "status": "Completed",
    "genres": [
        {
            "id": 5,
            "name": "Drama"
        },
        {
            "id": 10,
            "name": "Supernatural"
        }
    ]
}

Right now, I get the result and manually get columns one by one and set those in a DTO. But that is not efficient as the database query is already fetching more data than needed. Is there any specific annotation/property/jpql to reduce it?

Answer 1

I tried a different solution today. Lets look at the code first.

Genre

@Data
@Entity
@Table(name = "genre")
public class Genre {
    @Id
    @SequenceGenerator(name = "genreSeq", sequenceName = "genre_id_seq",
        allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "genreSeq")
    private Integer id;
    private String name;

    @ManyToMany
    @JoinTable(
        name = "anime_genre",
        joinColumns = @JoinColumn(name = "genre_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "details_id", referencedColumnName = "id"))
    List<AnimeIdentity> animes;

    protected Genre() {

    }
}

AnimeIdentity

@Getter
@Setter
@Entity
@Table(name = "anime_details")
public class AnimeIdentity {
    @Id
    @SequenceGenerator(name = "animeDetailsSeq", sequenceName =
        "anime_details_id_seq",
        allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator =
        "animeDetailsSeq")
    private Integer id;

    private String name;

    protected AnimeIdentity() {};
}

Queries Hibernate made

Hibernate: select genre0_.id as id1_2_0_, genre0_.name as name2_2_0_ from genre genre0_ where genre0_.id=?<br>
Hibernate: select animes0_.genre_id as genre_id2_1_0_, animes0_.details_id as details_1_1_0_, animeident1_.id as id1_0_1_, animeident1_.name as name2_0_1_ from anime_genre animes0_ inner join anime_details animeident1_ on animes0_.details_id=animeident1_.id where animes0_.genre_id=?

Feel free to show me the pros and cons of this solution. To me its a good solution if my necessity is limited to only this. But in case of different type of queries making more and more entity pojos will be a tiresome task.

Answer 2

Indeed was looking for a proper solution regarding the same issue , cause as you pointed out it is creating performance issues as there are huge useless data loads between the APP and the DB. Imagine that there could be, not only one query but much more and you need a global optimization solution...

• From the first place Spring DATA is not supporting this operation so its leading you at the manual configuration and set up on a DTO reference. The same applies if you were using a custom Object and returning that inside the JPQL with the constructor trick , or else write a native query , get back a List<Object> and again manually map the data back to your actual object , which is the most efficient but not elegant solution More info in this link , but try to check both answers for the details.

• The other thing is that as you are using hibernate underneath , which is providing custom mappers , you could always write up your custom HQL(not jpql) , set up a proper DAO , wire up the EntityManager or directly the SessionFactory (which is breaking the abstract JPA contract , but you can utilize the full goodies that hibernates offers) and then return the same object, but only with the columns you need.

Example for the second point:

import javax.persistence.EntityManager;
import org.hibernate.query.Query;
import org.hibernate.transform.Transformers;

public CustomEntity getEntity(){
        Query<CustomEntity> q = (Query<CustomEntity>) entityManager.createQuery("select 
                         e.id,e.name from CustomEntity e where e.name = 'parameter'");
        q.setResultTransformer(Transformers.aliasToBean(CustomEntity.class));
        CustomEntity entity = (CustomEntity) q.getSingleResult();
        return name;
    }

Note CustomEntity is a managed Entity Bean / Table in the database , just placing this example to be close on what you might need to achieve.

Tried with

• Spring boot 2.0.5.RELEASE
• Spring Data 2.0.5.RELEASE
• Hibernate-core 5.2.17.Final