How to do POJO projection in spring data jpa for native query

Question

I have the following MySQL query. I have tried interface projection in spring data jpa but the projection field id is UUID. So it is not getting mapped in interface projection. So I want to try POJO projection but it is not working.

SELECT 
    f.receiver_user_id, f.modified_at, f.text
FROM
    (SELECT 
        receiver_user_id, MAX(modified_at) AS modified_at
    FROM
        text_log
    GROUP BY receiver_user_id) AS x
        INNER JOIN
    text_log AS f ON f.receiver_user_id = x.receiver_user_id
        AND f.modified_at = x.modified_at
ORDER BY f.modified_at DESC;

Also, it would helpfull if it is suggested using JPA criteria builder implementation for the above query.

I have tried following spring data JPA repository query implementation

@Query(value = "SELECT " + 
            "f.receiver_user_id as receiverUserId, f.modified_at as modifiedAt, f.text as text" + 
            "FROM " + 
            "    (SELECT  " + 
            "        receiver_user_id, MAX(modified_at) AS modified_at " + 
            "    FROM " + 
            "        text_log " + 
            "    GROUP BY receiver_user_id) AS x " + 
            "        INNER JOIN " + 
            "    text_log AS f ON f.receiver_user_id = x.receiver_user_id " + 
            "        AND f.modified_at = x.modified_at " + 
            "ORDER BY f.modified_at DESC limit ?1  offset ?2 ", 
            nativeQuery = true)
    List<ITextLog> findTextLog(int l , int f);

    public interface ITextLog {

            public UUID getReceiverUserId();

            public Date getModifiedAt();

            public String getText();

    }

What isn't working? Also a UUID is just a String, so instead of making the type UUID you might want to use a String as type. — M. Deinum, Jan 22 '20 at 11:00
I have tried string also if I use the string as datatype then it fetches incorrect data in the string. — Ashish Bakwad, Jan 22 '20 at 11:07
Please show what you tried, currently there is only a query. — M. Deinum, Jan 22 '20 at 12:01
Is it only `receiverUserId` the problem? When you remove it from sql and `ITextLog` then it is working? — lczapski, Jan 23 '20 at 06:54

score 0 · Answer 1 · answered Jan 23 '20 at 07:04

0

Based on this answers: Maybe you can use this

@org.hibernate.annotations.Type(type="org.hibernate.type.UUIDCharType")
public UUID getReceiverUserId();

Or

@org.hibernate.annotations.Type(type="uuid-char")
public UUID getReceiverUserId();

answered Jan 23 '20 at 07:04

lczapski

4,026
3
16
32

I am getting this exception java.lang.IllegalArgumentException: Projection type must be an interface! – Ashish Bakwad Jan 29 '20 at 13:03

How to do POJO projection in spring data jpa for native query

1 Answers1