Spring Data JPA map the native query result to Non-Entity POJO

Question

I have a Spring Data repository method with a native query   Query value    SELECT g    gm   FROM group g LEFT JOIN group members gm ON g group id   gm group id and gm user id    userId WHERE g group id    groupId   nativeQuery   true      GroupDetails getGroupDetails  Param  userId   Integer userId   Param  groupId   Integer groupId     and I d like to map the result to Non-Entity POJO GroupDetails   Is it possible and if so  could you please provide an example

User · Answer

Use the default method in the interface and get the EntityManager to get the opportunity to set the ResultTransformer, then you can return the pure POJO, like this:

final String sql = "SELECT g.*, gm.* FROM group g LEFT JOIN group_members gm ON g.group_id = gm.group_id and gm.user_id = ? WHERE g.group_id = ?";
default GroupDetails getGroupDetails(Integer userId, Integer groupId) {
    return BaseRepository.getInstance().uniqueResult(sql, GroupDetails.class, userId, groupId);
}

And the BaseRepository.java is like this:

@PersistenceContext
public EntityManager em;

public <T> T uniqueResult(String sql, Class<T> dto, Object... params) {
    Session session = em.unwrap(Session.class);
    NativeQuery q = session.createSQLQuery(sql);
    if(params!=null){
        for(int i=0,len=params.length;i<len;i++){
            Object param=params[i];
            q.setParameter(i+1, param);
        }
    }
    q.setResultTransformer(Transformers.aliasToBean(dto));
    return (T) q.uniqueResult();
}

This solution does not impact any other methods in repository interface file.

User · Answer

You can write your native or non-native query the way you want  and you can wrap JPQL query results with instances of custom result classes  Create a DTO with the same names of columns returned in query and create an all argument constructor with same sequence and names as returned by the query   Then use following way to query the database     Query  SELECT NEW example CountryAndCapital c name  c capital name  FROM Country AS c     Create DTO   package example   public class CountryAndCapital       public String countryName      public String capitalName       public CountryAndCapital String countryName  String capitalName            this countryName   countryName          this capitalName   capitalName

User · Answer

In my computer  I get this code works It s a little different from Daimon s answer    x000D   x000D   SqlResultSetMapping  x000D      name  groupDetailsMapping   x000D      classes   x000D           ConstructorResult  x000D              targetClass GroupDetails class  x000D              columns   x000D                   ColumnResult name  GROUP ID  type Integer class   x000D                   ColumnResult name  USER ID  type Integer class  x000D                x000D            x000D        x000D    x000D   x000D   NamedNativeQuery name  User getGroupDetails   query  SELECT g    gm   FROM group g LEFT JOIN group members gm ON g group id   gm group id and gm user id    userId WHERE g group id    groupId   resultSetMapping  groupDetailsMapping   x000D   x000D   x000D

User · Answer

You can do something like   NamedQuery name  IssueDescriptor findByIssueDescriptorId         query   select new com test live dto IssuesDto  idc id  dep department  iss issueName                  cat issueCategory  idc issueDescriptor  idc description               from Department dep              inner join dep issues iss              inner join iss category cat              inner join cat issueDescriptor idc              where idc id in  1      And there must be Constructor like   public IssuesDto long id  String department  String issueName  String issueCategory  String issueDescriptor              String description            super            this id   id          this department   department          this issueName   issueName          this issueCategory   issueCategory          this issueDescriptor   issueDescriptor          this description   description

User · Answer

I think Michal s approach is better  But  there is one more way to get the result out of the native query    Query value    SELECT g    gm   FROM group g LEFT JOIN group members gm ON g group id   gm group id and gm user id    userId WHERE g group id    groupId   nativeQuery   true  String     getGroupDetails  Param  userId   Integer userId   Param  groupId   Integer groupId     Now  you can convert this 2D string array into your desired entity

User · Answer

Assuming GroupDetails as in orid s answer have you tried JPA 2 1  ConstructorResult    SqlResultSetMapping      name  groupDetailsMapping       classes            ConstructorResult              targetClass GroupDetails class              columns                    ColumnResult name  GROUP ID                     ColumnResult name  USER ID                                     NamedNativeQuery name  getGroupDetails   query  SELECT g    gm   FROM group g LEFT JOIN group members gm ON g group id   gm group id and gm user id    userId WHERE g group id    groupId   resultSetMapping  groupDetailsMapping     and use following in repository interface   GroupDetails getGroupDetails  Param  userId   Integer userId   Param  groupId   Integer groupId     According to Spring Data JPA documentation  spring will first try to find named query matching your method name - so by using  NamedNativeQuery   SqlResultSetMapping and  ConstructorResult you should be able to achieve that behaviour

User · Answer

I think the easiest way to do that is to use so called projection  It can map query results to interfaces  Using SqlResultSetMapping is inconvienient and makes your code ugly      An example right from spring data JPA source code   public interface UserRepository extends JpaRepository lt User  Integer gt         Query value    SELECT firstname  lastname FROM SD User WHERE id    1   nativeQuery   true     NameOnly findByNativeQuery Integer id       public static interface NameOnly         String getFirstname          String getLastname             You can also use this method to get a list of projections   Check out this spring data JPA docs entry for more info about projections   Note 1   Remember to have your User entity defined as normal - the fields from projected interface must match fields in this entity  Otherwise field mapping might be broken  getFirstname   might return value of last name et cetera    Note 2   If you use SELECT table column     notation always define aliases matching names from entity  For example this code won t work properly  projection will return nulls for each getter     Query value    SELECT user firstname  user lastname FROM SD User user WHERE id    1   nativeQuery   true  NameOnly findByNativeQuery Integer id     But this works fine    Query value    SELECT user firstname AS firstname  user lastname AS lastname FROM SD User user WHERE id    1   nativeQuery   true  NameOnly findByNativeQuery Integer id     In case of more complex queries I d rather use JdbcTemplate with custom repository instead

[spring] Spring Data JPA map the native query result to Non-Entity POJO

Examples related to spring

Examples related to hibernate

Examples related to jpa

Examples related to spring-data

Examples related to pojo