Spring Data - Custom query: count and list

Question

I'm having difficulties generate an object to provide me data based on two tables.

Table One

public class User {
    private String name;
//...
}

public class Profile {
    @JoinColumn(name = "user_id")
    private User user;
}

I need an object to give me the name and number of per user profiles.

Something like (in JSON):

[
    {"name":"John", "profiles":1 },
    {"name":"Maria", "profiles":5 },
    {"name":"Victor", "profiles":3 }
]

The best solution to do this?

Nguyen Tuan Anh · Accepted Answer · 2016-07-01 05:20:34Z

2

First, create a bean that will be used to generate your Json:

public class ABean {
    private String name;
    private Integer profiles;

    // getters and setters here
}

Then, your spring data jpa query would look like this:

SELECT new ABean(p.user.name, count(p.id)) FROM Profile p JOIN p.user GROUP BY p.user.name

answered Jul 1, 2016 at 5:15

Nguyen Tuan Anh

1,0358 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.