How to fetch only selected attributes of an entity using Spring JPA?

Question

I'm using Spring Boot (1.3.3.RELEASE) and Hibernate JPA in my project. My entity looks like this:

@Data
@NoArgsConstructor
@Entity
@Table(name = "rule")
public class RuleVO {

    @Id
    @GeneratedValue
    private Long id;

    @Column(name = "name", length = 128, nullable = false, unique = true)
    private String name;

    @Column(name = "tag", length = 256)
    private String tag;

    @OneToMany(mappedBy = "rule", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<RuleOutputArticleVO> outputArticles;

    @OneToMany(mappedBy = "rule", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<RuleInputArticleVO> inputArticles;
}

My repository looks like this:

@Repository
public interface RuleRepository extends JpaRepository<RuleVO, Long> {
}

In some cases I need to fetch only id and name attributes of entity RuleVO. How can I achieve this? I found a notice it should be doable using Criteria API and Projections but how? Many thanks in advance. Vojtech

Solution 1

UPDATE:

As has been pointed out to me, I'm lazy and this can very well be done hence I'm updating my answer after having looked around the web for a proper one.

Here's an example of how to get only the id's and only the names:

@Repository
public interface RuleRepository extends JpaRepository<RuleVO, Long> {

    @Query("SELECT r.id FROM RuleVo r where r.name = :name") 
    List<Long> findIdByName(@Param("name") String name);

    @Query("SELECT r.name FROM RuleVo r where r.id = :id") 
    String findNameById(@Param("id") Long id);
}

Hopefully this update proves helpful

---

Old Answer:

Only retrieving the specific attributes name/id is not possible as this is not how spring was designed or any SQL database for that matter as you always select a row which is an entity.

What you CAN do is query over the variables in the entity, for instance:

@Repository
public interface RuleRepository extends JpaRepository<RuleVO, Long> {

    public RuleVo findOneByName(String name);
    public RuleVo findOneByNameOrId(String name, Long id);
    public List<RuleVo> findAllByName(String name);
    // etc, depending on what you want
}

You can modify these however you want w.r.t. your needs. You can call these methods directly via the autowired repository

See http://docs.spring.io/spring-data/jpa/docs/current/reference/html/ Section 5.3 for more options and examples

Solution 2

interface IdOnly{
    String getId();
}

@Repository
public interface RuleRepository extends JpaRepository<RuleVO, Long> {
    public List<IdOnly> findAllByName(String name);
}

I notice that this is a very old post, but if someone is still looking for an answer, try this. It worked for me.

Solution 3

You can also define custom constructor to fetch specific columns using JPQL.

Example:

Replace {javaPackagePath} with complete java package path of the class use as a constructor in JPQL.

public class RuleVO {
   public RuleVO(Long id, String name) {
    this.id = id;
    this.name = name;
   }
}


@Repository
public interface RuleRepository extends JpaRepository<RuleVO, Long> {

    @Query("SELECT new {javaPackagePath}.RuleVO(r.id, r.name) FROM RuleVo r where r.name = :name") 
    List<RuleVO> findIdByName(@Param("name") String name);
}

Solution 4

Yes, you can achieve it with projections. You have many ways to apply them:

If you could upgrade to Spring Data Hopper, it provides an easy to use support for projections. See how to use them in the reference documentation.

Otherwise, first of all create a DTO with the attributes you want to load, something like:

package org.example;

public class RuleProjection {

    private final Long id;

    private final String name;

    public RuleProjection(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }
}

Of course, you could use Lombok annotations also.

Then, you can use in the JPQL queries like this:

select new org.example.RuleProjection(rule.id, rule.name) from RuleVO rule order by rule.name

Another option, if you want to avoid using DTO class names in your queries, is to implement your own query method using QueryDSL. With Spring Data JPA, you have to:

• Create a new interface with the new method. Ex:

  public interface RuleRepositoryCustom {
     public List<RuleProjection> findAllWithProjection();
  }
  

• Change your repository to extend the new interface. Ex:

  public interface RuleRepository extends JpaRepository<RuleVO, Long>, RuleRepositoryCustom {
  ...
  

• Create an implementation of the Custom repository using the Spring Data JPA QueryDSL support. You have to previously generate the Q clases of QueryDSL, using its Maven plugin. Ex:

  public class RuleRepositoryImpl {
  
      public List<RuleProjection> findAllWithProjection() {
          QRuleVO rule = QRuleVO.ruleVO;
          JPQLQuery query = getQueryFrom(rule);     
          query.orderBy(rule.name.asc());
          return query.list(ConstructorExpression.create(RuleProjection.class, rule.id, rule.name));
      }
  }
  

Solution 5

You can do it by using @Query annotation(HQL).

Please refer to the Spring docs below:

http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query

(search for @Query in spring document)

View more solutions

Comments

[Essex Boy]

I can't be done, all you can do is Lazy load the collections. I would say there is no performance benefit of pulling back data selectively.

[Alan Hay]

See 19.1.7 docs.jboss.org/hibernate/orm/3.3/reference/en/html/… which essentially states that, in line with the comment above, for non-collection fields it is doable but you are wasting your time.

[Shivang Agarwal]

Spring Data Projection is the best solution for such problem. docs.spring.io/spring-data/jpa/docs/current/reference/html/…

[Martin Frey]

Thats not true. Sql is all about fetching columns of rows. select * from mytable is just our developer laziness ;)

[Roel Strolenberg]

You're correct. Haven't actually written a query in a while =D select field from table, voila: column

[zulkarnain shah]

Doesn't work if you have relationships in that entity

[user1445967]

Curious to see if this worked, since it is an answer that avoids writing SQL. I'll revisit when I can

[AlikElzin-kilaka]

The JPQL with the new Projection(...) did the trick for me. Thanks.

[Marx]

Projection works. One note - interface has to be public, it has to in separate file docs.spring.io/spring-data/jpa/docs/current/reference/html/…

[Omar]

Thank you. This solution is simpler and is recommended especially for associated entities. Note: while we've already imported the entity, no need to specify its package path in query annotation.

[Prakash]

Thank you. I tried our solution, but I got table is not mapped error. I always use nativeQuery=true, but in your case it is not a nativeQuery so I removed that so I got error like table is not mapped. How for you is working without using nativeQuery=true. Any Help...

[Abhilekh Singh]

@Prakash Have you actually mapped your table using @Entity? See, if you have the only @Table which only works for native queries. You might need both annotations if you are using JPQL/HQL and native queries both. I might need more information about the error and the Query you are using, then I can help better.

[Prakash]

@AbhilekhSingh Thanks for your response. I fixed my issue. Actually in JPQL instead of referring entity name, I just referred table name as it only works in native query. When I changed all my table and column names to entity classname and class variables it started working correctly.

[Prakash]

@AbhilekhSingh For the above case, you told to create the different constructors that need to require to fetch the data from table. For eg. If I need 2 column values from table then the constructor should contain two parameters and so on.. But If have around 20 columns in my table, for the first query I need firstname and lastname so both are strings I create one constructor with two parameters as string and get that data, for next query I need state and city that comes under string. Continued in next Command.....

[Prakash]

@AbhilekhSingh But this case I cannot able to create another constructor with 2 parameters as string. Because I already created one constructor for firstname and lastname which also 2 parameters as string. What I should in this situation. Any help. Thanks.

[yaroslavTir]

I just checked it. It looks like the query selects all fields, so this is not a real solution.