How to select all the data of two columns from a table using JPA query method

Question

I want to add in my Repository interface a method for the following SQL query:

SELECT ID, NAME  FROM TABLE_NAME

This SQL query works as expected, but I want to write it as a JPA query method, I've tried in many ways but didn't get it working, Please help me.

Following which I've tried but didn't work:

findAllByIdName(){}
findAllByIdAndName(){}
findByIdName(){}
findByIdAndName(){}

Please refer this post: https://stackoverflow.com/questions/16876681/read-data-from-jpa-the-ways-to-do-it — Prashant Srivastava, Nov 10 '17 at 13:08
You should use `@Query` annotation for get selected column please refer this post https://stackoverflow.com/questions/22007341/spring-jpa-selecting-specific-columns — hrdkisback, Nov 10 '17 at 13:14

Maciej Kowalski · Accepted Answer · 2017-11-10T14:31:55.107

7

Create a result class first:

package com.example;

public class ResultClass{

  private Long id;
  private String name;

  public ResultCalss(Long id, String name){
     // set
  }
}

and then use a custom @Query:

@Query("select new com.example.ResultClass(e.id, e.name) from MyEntity e")
public List<ResultClass> findIdsAndNames();

edited Nov 10 '17 at 14:31

answered Nov 10 '17 at 13:06

Maciej Kowalski

25,605
12
54
63

Does that MyEntity is same as ResultClass ? – tsoni Nov 10 '17 at 13:31
no.. MyEntity is a class annotated with @Entity. ResultClass is a POJO – Maciej Kowalski Nov 10 '17 at 13:33
Unable to locate appropriate constructor on class [com.example.ResultClass]. Expected arguments are: int, java.lang.String – tsoni Nov 10 '17 at 13:37
In ResultClass & POJO both are int for Id & String for name – tsoni Nov 10 '17 at 13:41
what is the definition of constructor that you are using exacly? – Maciej Kowalski Nov 10 '17 at 13:42
I'm not using my defined constructor even on class ResultCalss – tsoni Nov 10 '17 at 13:45
there has to be a constructor with id and name params in that particular order for this to work – Maciej Kowalski Nov 10 '17 at 13:47
Now it build successfuly but when I hit API, then it returned org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet – tsoni Nov 10 '17 at 14:28
try to add the pojo class and the repository method with @Query in post marking the section with an UPDATE. There has to be something trivial missing – Maciej Kowalski Nov 10 '17 at 14:42
Hey thanks a lot, it's works!! I've removed nativeQuery = true, & value="SQL Query which you suggest", it worked with only @Query("SQL Query which you suggest") – tsoni Nov 10 '17 at 14:45

score 2 · Answer 2 · answered Nov 10 '17 at 13:17

I have similar implementation. Custom query required to achieve this.

e.g -

@Query("SELECT usr.id, usr.name FROM User usr")
public List<User> findIdsAndNames();

And the User class is

@Entity
@Table(name="t_user")
public class User{

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id", nullable = false)
    private Long id;

    @Column(name="name")
    private String name;
............
}

Cepr0 · Answer 3 · 2017-11-10T15:16:04.083

2

Just use interface- or class-based projections:

public interface IdAndName {
    Long getId();
    String getName();
}

public interface MyRepo extends CrudRepository<MyEntity, Long> {
    List<IdAndName> findBy();
}

More info.

edited Nov 10 '17 at 15:16

answered Nov 10 '17 at 13:34

Cepr0

28,144
8
75
101

How to select all the data of two columns from a table using JPA query method

3 Answers3

Linked