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