Select only some columns from a table

Question

Is there a way to select only some columns from a table using jpa?

My tables are huge and I am not allowed to map all the columns in my entities. I tried to create an entity (as a side note, I don't have PKs in my tables):

@Entity
@Table(name = "SuperCat")
@Getter
@Setter
public class Cat{

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;

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

and then in my repository to

public interface CatRepository extends 
CrudRepository<Cat, Long> {

@Query(
  "SELECT name FROM Cat")
Page<Cat> getAlCats(Pageable pageable);

This is only a simple example, but the idea is the same. I have searched a lot and I found projections, but there you need to map the whole table, then I found native queries, but still doesn't apply. I know I can return an Object and the other solution is to use query with NEW and create my own object (no @entity, like a pojo). But is there a way that I can do this using jpa, to be able to use repository and services, if I am creating my own pojo then i will create a @transactional class put the queries (with NEW) there and this is it. I don't like this approach and I don't think that the jpa does't allow you to select only some columns, but I didn't find a proper way. Maybe you will ask what is the result if I am doing like this: I get this error: "Cannot create TypedQuery for query with more than one return using requested result type [java.lang.Long]" (For new queries, I am talking about : http://www.java2s.com/Tutorials/Java/JPA/4800__JPA_Query_new_Object.htm maybe I was not clear)

Try adding the @Transient attribute to those columns you do not want persisted/read. — OldProgrammer, Nov 15 '18 at 17:25
I want to read all the columns(attributes) that are in my entity class, there are 8 attributes (annotated with column and name), but my tables has more than 100 columns and I am not allowed to add attribute for that columns in my code — agata, Nov 15 '18 at 17:53
My code will not be accepted if they see all that columns in my class — agata, Nov 15 '18 at 17:55
What do you mean by _I found projections, but there you need to map the whole table_? See my answer to [this question](https://stackoverflow.com/q/53239376/6413377). Is there some `mapping to a whole table`? — pirho, Nov 15 '18 at 18:08
What I understand from there is that you have the entity testClass (which holds all your columns) and the tupleDto (which holds exactly what you need - in my case the 8 columns) and you create the query with new (I already mentioned this). Correct me, maybe I understood everything wrong. In the testClass you need to have all the columns or only some of them? — agata, Nov 15 '18 at 18:14
My problem is that “I don’t know” how to create the testClass. — agata, Nov 15 '18 at 18:15
If you create JPQL with a select clause selecting only a few columns, then you only have those columns mapped in the result class. And the problem is?!! Why not actually try it and tell people what "error" you get? You can have extra cols in a table that aren't mapped to a field ... JPA provider should just ignore them (as long as they have DEFAULT defined for those additional columns when you do a persist of new objects) — , Nov 15 '18 at 18:34
If you read my post I put the error there. I already tried. I have created an entity class with only 8 columns (from more that 100) created a repository and in it a query. Exactly how I added the code here. And it is not Working, idk what is the problem, i thought that maybe because my id is missing from the query and i added it, but the result was an error, something like “the id cannot be found in the database, I don’t remember exactly). This is why I posted here, it’s not like I am searching for the easy way. At the end I will do it with a pojo, but I thought that maybe there is another way — agata, Nov 15 '18 at 18:41
I don’t want to persist, i want only to read. Ok, so if this should work, than is something wrong with the Id? (Because I don’t have an Id in my table). — agata, Nov 15 '18 at 18:44
The JPA API is not your problem ... but you aren't using it. You are using Spring Data JPA, different thing. Presumably that "Long" in your XXXRepository class is what Spring interprets as the return type from the query, so change it. Basic JPA Query allows exactly what you want to do ... just select multiple columns and the JPQL query returns each row as `Object[]` as per the JPA spec — , Nov 15 '18 at 19:02
Ok, so the return will be an object and then I need to map the object. So I cannot return a Cat. Hmm...return type, ok. I will change it. — agata, Nov 15 '18 at 19:11

score 0 · Answer 1 · answered Nov 15 '18 at 19:02

0

You can do the same by using below approach.

Just create a constructor in entity class with all the required parameters and then in jpa query use new operator in query like below.

String query = "SELECT NEW com.dt.es.CustomObject(p.uniquePID)  FROM PatientRegistration AS p";
        TypedQuery<CustomObject> typedQuery = entityManager().createQuery(query , CustomObject.class);
        List<CustomObject> results = typedQuery.getResultList();
        return results;

And CustomObject class should look like below with the constructor.

public class CustomObject {

    private String uniquePID;

    public CustomObject(String uniquePID) {
        super();
        this.uniquePID = uniquePID;
    }

    public String getUniquePID() {
        return uniquePID;
    }

    public void setUniquePID(String uniquePID) {
        this.uniquePID = uniquePID;
    }


}

answered Nov 15 '18 at 19:02

Alien

15,141
6
37
57

Yes, i thought at this approach (and I start implementing it) Using a simple Java class and new in query. But I thought that maybe somehow Jpa is able to do the mapping. – agata Nov 15 '18 at 19:07
i have used this and it works fine....AFAIK this is the only recommended approach..:) – Alien Nov 15 '18 at 19:08
Ok, good. And then you have the customObject and class annotated with @transactional where you implement the query? I am asking because I was used to have a repository and a service and an entity. – agata Nov 15 '18 at 19:13
i call service from controller and then service to repository and i have not annotated the repository method as @transactional because actually no need. – Alien Nov 15 '18 at 19:16

score 0 · Answer 2 · answered Nov 19 '18 at 05:59

spring-data-jpa projection not need to map the whole table, just select the necessary fileds :

// define the dto interface
public interface CatDto {
  String getName();
  // other necessary fields
  ...
}

@Query(value = "select c.name as name, ... from Cat as c ...)
Page<CatDto> getAllCats(Pageable pageable);

By this way, CatDto is an interface and it only includes some fileds part of the whole table. Its fields name need to match the select field's alias name.

Select only some columns from a table

2 Answers2