CODEWITHSUNDEEP
javaspringjpaspring-data-jpa
topcan5
topcan5

Reputation: 1707

Spring JPA DATA with @Query brings back an extra field

I have 2 tables: USER and ADDRESS. USER has first, last and count as @Transient to show count of addresses a user has. I have a Spring JPA Data method that uses @Query (select user.*, COUNT(address.id) from ... group by user.id) to get user info and address COUNT. I have confirmed that my query is good, but I am not able to map the COUNT from result to count variable in user class. What am I missing?

User.java:

@Transient
private int count;
private String first;
private String last;

UserRepository.java

@Query(value = "SELECT "
        + "    user.*, COUNT(address.id) AS COUNT "
        + "FROM "
        + "    user "
        + "        INNER JOIN "
        + "    address ON user.id = address.user_id "
        + "GROUP BY user.ID "
        + "ORDER BY user.last ASC;", nativeQuery = true)
List<User> findUserWithCount();

Upvotes: 2

Views: 2592

Answers (1)

Bonifacio
Bonifacio

Reputation: 1502

"@Transient" annotation is not supposed to work the way you are trying to make. The correct way to get all the addresses of an user is to map Address entity in User entity with @OneToMany annotation, then make a query to fetch the Addresses of this User and finally get the .size() of the Adrdesses get on User entity.

To better explain this I will give an example, let's start with the mappings (some parts of the codes are not included):

@Entity
@Table(name = "User")
public class User {
    // All user attributes

    @OneToMany
    public List<Address> addresses;

   // All getters and setters, including addresses get and set.
}

@Entity
@Table(name = "Address")
public class Address {
    // All user attributes getters/setters
}

Now we have the two entities mapped you should be able to get the Addresses count from a User just by calling getAddresses().size() method, however when using @OneToMany annotation the collection mapped are lazy loaded, which means you will need to fetch the data from database before using it om Java. To do this fetching you can use your query with some modifications:

First, you will not need to use a native query, because your entities are mapped correctly now. Second you will need to re-write the query in a JPQL syntax, and third you will need to fetch the Address collection. The example given bellow will probably work in your case:

@Query(value = "SELECT u FROM User u
       LEFT JOIN FETCH u.addresses")
List<User> findUserWithCount();

Please, not that you will not be able to order the data with the "last" field, because it has a @Transient annotation, which means that this field is not persisted on database, which will make it unable to be used inside a JPQL query.

If everything is done correctly, you should now be able to get the Addresses count from a User just by calling the getAddresses.size() method.

PS: I highly recommend you to study some starter tutorials of Hibernate, the way way you are trying to get the Address count of the user can be done far easily using the correct Hibernate specification mappings.

Anyways, I hope you can make it with this answer, good luck.

Upvotes: 1

Related Questions