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, could you please provide an example ?

Pilcomayo

Answer 1

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

Pupil

For spring data to be able to match the NamedNativeQuery, the Class name of the domain entity followed by a dot, needs to be prefixed to the name of the NamedNativeQuery. So the name should be (Assuming the domain entity is Group) 'Group.getGroupDetails'. – Fahland

How will I return a list of such Objects? – Emptyheaded

@GrantLay When I prefix the entity name Group, get error - "No property getGroupDetails found for type Group". – Carolyncarolyne

To get it work, should GroupDetails marked with @Entity ? If possible can you please tell on which class the annotation @NamedNativeQuery has to be applied? – Carolyncarolyne

@SqlResultSetMapping and @NamedNativeQuery annotations must be present on the entity used in your Spring Data repository(e.g. for public interface CustomRepository extends CrudRepository<CustomEntity, Long> it is the CustomEntity class) – Engedi

How would you do an alias, if you column name is slightly different from the bean object's field name? You're assuming they're the same here. – Officious

Don't forget put @Query(nativeQuery = true) on GroupDetails getGroupDetails(@Param("userId")... – Herminiahermione

If the User class is Non-Entity pojo will this work, as per the question? Because i still get Caused by: java.lang.IllegalArgumentException: Not a managed type: class com.org.example.NonEntityType – Melchior

Answer 2

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<User, Integer> {

   @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.

Purchasable

It is a cleaner solution. I had checked but the performance is much worse than using SqlResultSetMapping (it is slower about 30-40% :( ) – Bluefarb

works nicely! make the interface public if you want to use it elsewhere – Vizcacha

Does not work if you want to extract XML type (clob) field. Any suggestion? – Roesch

@Roesch I'd rather use JdbcTemplate (docs.spring.io/spring-framework/docs/current/javadoc-api/org/…) instead. You can use getClob method on resultSet in order to fetch clob InputStream. For an example: rs.getClob("xml_column").getCharacterStream(). – Hornbeck

What if I use SELECT * in the query and the query is a native one? – Paleoecology

@SalmanKazmi I wouldn't even considier doing so because if table in database changes your view object must be changed as well, so maintaining this projection would be a hell. But it should work if the fields in the projection are the same as in the interface / dto. – Hornbeck

When I am extending this solution for JOIN query, I am facing the problem as Incorrect syntax near 'SD_User'. is there a way need to handle for JOIN queries. – Tojo

I have to explicitly map my snake case column names to camel case in the native query for projection to work. e.g. @Query(value = "SELECT user.first_name AS firstName, user.last_name AS lastName FROM SD_User user WHERE id = ?1", nativeQuery = true), otherwise those fields would be null. – Wingover

You explained it well, but it will work only if there are no joints. how to map if there are joints? – Zebulon

The keywords public static before the interface are apparently redundant here. – Sammysamoan

I don't understand why Spring is able to wire up a projection but not a POJO. – Retrospection

It is okey when using small data set. But I have already tested for 100000 data, service performance approximatelly degraded to 1/12 of without projection case. In other words, without projection web service is approximatelly 12x faster. – Dioptrics

Answer 3

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.

Checkpoint

simple and elegant – Lightless

how do you do the conversion from a 2D array to a class? – Alpestrine

This is too risky. If column order is changed in table (for example - new column was added, or existing column is modified) then result values will be able to be mixed. E.g. if your entity has ClientId field, but column was changed from client_id to user_id then it is hard to detect this change, because of column name validation lack. – Plante

Simple workaround for challenge suggested by @manushin-igor would be to enlist column names in order in the query itself so that it becomes safe to map it . Something like this: @Query(value = "SELECT g.group_id, g.userId, gm.group_name 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); – Mickeymicki

Answer 4

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

Misconduct

correction: same names are not mandatory... just same sequence of parameters in constructor and returned result set. – Misconduct

This works only if Country is your java entity class. This won't if Country is not your java entity class. – Animalist

You say this should also work with native queries? Could you give an example of that? – Hysteresis

OP asks for native query , but the example given is a non-native one – Mcnulty

"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." - you can't do that with a native SQL query. – Winstonwinstonn

Answer 5

This is my solution for converting to Map and then to custom Object

private ObjectMapper objectMapper;

public static List<Map<String, Object>> convertTuplesToMap(List<?> tuples) {
    List<Map<String, Object>> result = new ArrayList<>();

    tuples.forEach(object->{
        if(object instanceof Tuple single) {
            Map<String, Object> tempMap = new HashMap<>();
            for (TupleElement<?> key : single.getElements()) {
                tempMap.put(key.getAlias(), single.get(key));
            }
            result.add(tempMap);
        }else{
            throw new RuntimeException("Query should return instance of Tuple");
        }
    });

    return result;
}

public <T> List<T> parseResult(List<?> list, Class<T> clz){
    List<T> result = new ArrayList<>();
    convertTuplesToMap(list).forEach(map->{
        result.add(objectMapper.convertValue(map, clz));
    });
    return result;
}

public static class CustomDTO{
    private String param1;
    private Integer param2;
    private OffsetDateTime param3;
}

public List<CustomDTO> doSomeQuery(){
    Query query = entityManager.createNativeQuery("SELECT param1, param2 param3 ... ", Tuple.class);
    return parseResult(query.getResultList(), CustomDTO.class);
}

Schnitzler

Answer 6

USE JPA PROJECTIONS In your case it may be desirable to retrieve data as objects of customized types. These types reflect partial views of the root class, containing only properties we care about. This is where projections come in handy. first declare Entity as @immutable

@Entity
@Immutable

public class Address {

@Id
private Long id;

set your Repository

public interface AddressView {
    String getZipCode();
}

Then use it in a repository interface:

public interface AddressRepository extends Repository<Address, Long> {
      @Query("EXEC SP_GETCODE ?1")
    List<AddressView> getAddressByState(String state);
}

Wrangle

Answer 7

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.

Execrable

Answer 8

If you are looking for running a custom SQL query in spring boot with @repository and @service structures. Please have a look.

https://mcmap.net/q/160090/-is-it-possible-to-use-jparepository-without-entity

Ootid