Spring Data JPA: Cannot Invoke Data Transfer Object Because it's null

[Bryan Carty]

For some reason I'm getting null returned from my database through Spring data JPA and I'm not quite sure why because when I run the query in Postgres it works fine. I'd really appreciate someone taking a look at the code if they've a few minutes to spare.

The error is:

   

"Cannot invoke "com.jrp.note.dto.FriendList.getEmail()" because "fDisplay" is null" 

which implies that nothing is getting returned from the database. This is the controller:

     

 @ResponseBody
    	@PostMapping("/getnewfrienddata")
    	public FriendList getNewFriendData(@RequestParam("id") long id) {
    		FriendList fDisplay = uRepo.findFriend(id);
    		System.out.println("/n/n/n/n"+fDisplay.getEmail()+"/n/n/n/n");//For testing purposes.
    		return fDisplay;
    	}

This is the corresponding query in the repository:

 

 @Query(nativeQuery=true, value="select user_name as name, email, COUNT(*) as numNotes from user_accounts JOIN notes ON user_id = note_user WHERE user_id = ?1 GROUP BY user_name, email")
    FriendList findFriend(long id);

The data transfer object I'm using

 

 public interface FriendList {
    	public String getName();
    	public int getNumNotes();
    	public String getEmail();
    }

Let me know if you need more code or have any questions about it.

Thanks again

[Himai Minh]

Maybe, your return type should be Collection<Friend> instead of FriendList?
Reference: https://www.baeldung.com/spring-data-jpa-query

[Rob Spoor]

Himai Minh wrote:Maybe, your return type should be Collection<Friend> instead of FriendList?
Reference: https://www.baeldung.com/spring-data-jpa-query

Because the name is not descriptive, it's not a list but a simple POJO.

@Bryan, are you sure this user exists in the database? Because if it doesn't, then you'll definitely get a null from the repository.

[Bryan Carty]

@Rob, first thanks for answering. I've been trying stackoverflow to no avail. To answer your question, yeah it's definetly in the database. If you've any more ideas about what is causing the error I'd really love to hear them.

Also, just to clarify. Despite it being called FriendList it doesn't return a list. Poor naming on my part. I have a different query that returns a list as shown below but in this case I just want to return a single row of data from the database. Also, I want to note that the below query that returns a list works fine.

	@Query(nativeQuery=true, value="SELECT user_name as name, email, COUNT(note_user) as numNotes FROM (SELECT * FROM notes RIGHT JOIN (Select user_id, user_name, email from user_accounts join friendship_relationships on user_accounts.user_id =  friendship_relationships.follower_id where followee_id = ?1) as u ON u.user_id = notes.note_user) as nu GROUP BY name, email ORDER BY COUNT(note_user) DESC")
	List<FriendList> findFriends(long id);

[Rob Spoor]

What happens if your method returns Optional<FriendList>?

@Query(...)
Optional<FriendList> findFriend(long id);

[Bryan Carty]

@Rob, I got it working. Thanks for the help anyway though it was really appreciated. I think it may have been to do with the SQL Query and the JOIN I was using. If I figure out what was causing the problem for sure I'll leave a reply here to make sure anyone else who runs into the same problem can see why it happened to me.

Thanks again

[Rob Spoor]

Ah yes, I see a possible issue with that query. If the user has no notes, then both queries will lead to zero results. I can think of two possible fixes:

1. Use a sub-select in the SELECT part:
SELECT a, b, (SELECT COUNT(*) FROM t2 WHERE t2.t1_id = t1.id) FROM t1

2. Use a LEFT OUTER JOIN, and explicitly count on the right table (counting * will always have at least 1 result):
SELECT a, b, COUNT(t2.id) FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.t1_id GROUP BY a, b

[Bryan Carty]

Yeah, doing a left join fixed it. Thanks again. Your help was really appreciated.

[Rob Spoor]

You're welcome