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 ?
This question is related to
spring
hibernate
jpa
spring-data
pojo
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.
In my computer, I get this code works.It's a little different from Daimon's answer.
@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_
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.
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;
}
}
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.
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;
}
Source: Stackoverflow.com