How to count row table in JPA Query

Question

I'm new to Spring Boot. I have a mysql database, I use a query to count row in my table. But it's not work, it still return my original table data. Can you help me check my code.

Here is my Entity:

@Entity
@Table(name = "act_id_membership", schema = "activiti", catalog = "")
@IdClass(ActIdMembershipEntityPK.class)
public class ActIdMembershipEntity {
    private String userId;
    private String groupId;

    @Id
    @Column(name = "USER_ID_")
    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    @Id
    @Column(name = "GROUP_ID_")
    public String getGroupId() {
        return groupId;
    }

    public void setGroupId(String groupId) {
        this.groupId = groupId;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        ActIdMembershipEntity that = (ActIdMembershipEntity) o;
        return Objects.equals(userId, that.userId) &&
                Objects.equals(groupId, that.groupId);
    }

    @Override
    public int hashCode() {
        return Objects.hash(userId, groupId);
    }
}

Here is my query:

@Repository
public interface MemershipRepository extends JpaRepository<ActIdMembershipEntity, String> {
    @Query ("select new com.example.activiti_restful.dtos.UserMembership(i.userId, count(i)) from ActIdMembershipEntity i where i.userId ='kermit'")
    UserMembership countMemberships(String userId);
}

Updated code: My service class:

@Service
public class MembershipService {
@Autowired
private MemershipRepository repository;

public long count() {
    return repository.count();
}

My resource class:

@RestController
public class MembershipResource {
@Autowired
private MembershipService membershipService;

@GetMapping("/membership")
public long list() {return membershipService.count();}
}

My custom JSON Object class:

public class UserMembership {
    private String userId;

    private long numberOfusers;

    public UserMembership(String userId, long numberOfusers) {
        this.userId = userId;
        this.numberOfusers = numberOfusers;
    }
} 

MySQL Table: act_id_membership

Solution 1

According repositories documentation using CrudRepository provides a method called count() that is one of the Superinterfaces which JpaRepository is implemented.

Based CrudRepository documentation says:

long count(); Returns the number of entities.

Then you should use CrudRepository method. In addition Remove Uppercase MembershipREPOSITORY, by java code convention, you have to use by following way MembershipRepository.

 @Repository
 public interface MembershipRepository extends JpaRepository <ActIdMembershipEntity, String> {
 }

And use it in your Service:

 @Service
 public class MembershipService {

     @Autowired
     private MembershipRepository repo;

     public long count() {
         return repo.count();
     }
 }

UPDATED

Based on your requirement: In Controller:

@RestController
public class MembershipResource {

    @Autowired
    private MembershipService membershipService;

    @GetMapping("/membership")
    public List<Object> list() { return membershipService.countMemberships();
    }
}

In Service:

@Service
public class MembershipService {

    @Autowired
    private MemershipRepository repository;

    public List<Object> countMemberships() {
        return repository.countMemberships();
    }

}

In Repository:

@Repository
public interface MemershipRepository extends JpaRepository<ActIdMembershipEntity, String> {
    @Query ("select i.userId, count(i) from ActIdMembershipEntity i where i.userId ='kermit'")
    List<Object> countMemberships();
}

Solution 2

*> Actually I want it return a json format like [{ name: kermit, value:6}]. Now it just return a number 6 only. How I can do that? Thank you!

First, create a class to wrap your data:

public class UserMembership {

 private String userId;

 private long numberOfUsers;

 public UserMembership(String userId, long numberOfUsers) {
       this.userId = userId;
       this.numerOfUsers = numberOfUsers;     
 }

}

Then

@Repository 
public interface MembershipRepository extends JpaRepository <ActIdMembershipEntity, String> {
     @Query ("select new *****.UserMembership(i.userId, count(i)) from ActIdMembershipEntity i where i.userId = :userId")
     UserMembership countMemberships(String userId);
}

*****: your full package name

Hope it help!

Comments

[Tomato]

Sorry because I use Spring first time, I tried to fix my code like you say, but it still not work. I updated my code. Can you check it?

[Tomato]

When I call my json link in Postman, it say: "message": "[Ljava.lang.Object; cannot be cast to java.lang.Long",

[Jonathan JOhx]

It is no problem. Remove line @Query ("select i.userId, count(i) from ActIdMembershipEntity i where i.userId = 'kermit'")

[Jonathan JOhx]

and keep JpaRepository as well. that should fix your code.

[Tomato]

wow! it worked. Thank you very much! But it's only return a number, if I want to write a query to show user id with a count number like my @Query, how I should do? Can you help me a little more.

[Jonathan JOhx]

I'm going to help you. But for that case you'd have to create a custom json object, for example public UserStatistic implements Serializable { private long userId; private long count; //getters/setters } once this is created you should return it from your method with that @Query in Repository.

[Tomato]

Thanks for your help! But it's appear error in log: Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate class [UserMembership]

[Twister]

select new *****.UserMembership(i.userId, ... add your full package name at "****"

[Tomato]

I created but there are a error in my log: Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate class [UserMembership] -> this is my created class

[Tomato]

I'm really sorry for annoying but it still return only a number :(

[Twister]

Show me your code and value return after calling countMemberships

[Tomato]

Thanks for your help! I updated all my code in this post and a image of my table. When I call in Postman, it only return a number 13 (actually it should be 6)

[Twister]

public class MembershipService { @Autowired private MemershipRepository repository; public long count() { return repository.count(); }

[Twister]

while you still call function count() while new function is countMemberships()

[Jonathan JOhx]

@Tomato I update it, copy and paste it. it will work. I tested it

[Tomato]

I'm so sorry, as I say I just do Spring in the first time, I don't really understand the code, how I should fix, in the service class? :(

[Jonathan JOhx]

OK, please mark it as answer if this helped you. thank you.

[Tomato]

sorry because annoy again but can you tell me how to add key into this json response? Ex: now it's return [["kermit", 6]], i want it return [["name":"kermit", 6]]. I added @jsonproperty into my entity model class but it still have nothing. Thank you very much.

[Jonathan JOhx]

@Tomato could you please ask a new question? So that I can answer you with more details. Copy and paste the link here and I'm going to help you. Thanks.

[Tomato]

stackoverflow.com/questions/54139950/… Thank you