CODEWITHSUNDEEP
springhibernatespring-bootjpaspring-data-jpa
Vojtech
Vojtech

Reputation: 2603

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

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

Upvotes: 29

Views: 92940

Answers (5)

Pranjal Gore
Pranjal Gore

Reputation: 592

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.

Upvotes: 11

Abhilekh Singh
Abhilekh Singh

Reputation: 2953

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);
}

Upvotes: 7

C&#232;sar
C&#232;sar

Reputation: 1246

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));
    }
}

Upvotes: 5

Roel Strolenberg
Roel Strolenberg

Reputation: 2950

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

Upvotes: 33

shankarsh15
shankarsh15

Reputation: 1967

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)

Upvotes: 2

Related Questions