When I use entitymanager. Createnativequery()，Is there a simple way to put query results into a dto or vo

Question

When I use entitymanager. Createnativequery()

Is there a simple way to put query results into dto The solution I found on the Internet is

     List<PostVO> res = (List<PostVO>) en.createNativeQuery(s,PostVO.class).unwrap( org.hibernate.query.NativeQuery.class )
                .setResultTransformer( Transformers.aliasToBean( PostVO.class ) );

But it seems to have been abandoned

        var s = """
 SELECT     t.id,t.title,t.user_id,t.comments,t.view,t.collects,t.top,
          t.essence,t.create_time , t.modify_time ,u.username,u.alias,u.avatar
          FROM bms_post t LEFT JOIN ums_user u ON t.user_id = u.id""";
        if(tab.equals("hot")){
          s+=  """
                  date(t.create_time) <= date_add(curdate(), interval 1 day)
                  and date(t.create_time) >= date_sub(curdate(), interval 7 day)
                  order by t.view desc, t.create_time desc
                    """.indent(1);
        }else s+="order by t.create_time desc".indent(1);
      var res =  en.createNativeQuery(s).getResultList();

dto code

@Setter
@Getter
@NoArgsConstructor
//@AllArgsConstructor
public class PostVO implements Serializable {
    private static final long serialVersionUID = -261082150965211545L;
    private String id;
    private String userId;
    private String title;
    private Integer comments;
    private Boolean top;
    private Boolean essence;
    private Integer collects;
    private List<BmsTag> tags;
    private Integer view;
    private Date createTime;
    private Date modifyTime;
    private String avatar;
    private String alias;
    private String username;
}

Maybe [this](https://stackoverflow.com/questions/64762080/how-to-map-sql-native-query-result-into-dto-in-spring-jpa-repository/64776376) will be helpful. — SternK, Jun 25 '21 at 12:16

Tuan Hoang · Accepted Answer · 2021-06-27T09:51:07.980

0

Yes you can. What you need to do is

1/ Have constructor with parameters

2/ Modify your query to something like

"SELECT new " + PostVO.class.getName() + "(t.id,t.title,t.user_id,t.comments,t.view,t.collects,t.top, t.essence,t.create_time , t.modify_time ,u.username,u.alias,u.avatar ) <your FROM query here> "

Edit: using getSimpleName() make it incorrect because it cannot locate the class, using getName() solves the problem.

And use createQuery for this case

TypedQuery<PostVO> typedQuery = entityManager.createQuery(<your sql>, PostVO.class);
typedQuery.setParameter("id", id); // or whatever paramerters of yours
return typedQuery.getSingleResult(); // or get a list, based on your needs

edited Jun 27 '21 at 09:51

answered Jun 25 '21 at 11:34

Tuan Hoang

586
1
7
14

why have trouble? ,help check my code ``` var a="SELECT new " + PostVO.class.getSimpleName() + "(t.id,t.user_id ,t.title) from bms_post t"; var res = en.createNativeQuery(a).getResultList(); System.out.println(res);``` – edcjian Jun 27 '21 at 04:02
sorry my mistake, it should be `getName()` instead of `getSimpleName()`. You will get the path of your class for example `com.yourbusiness.dto.PostVO` – Tuan Hoang Jun 27 '21 at 09:06

When I use entitymanager. Createnativequery()，Is there a simple way to put query results into a dto or vo

1 Answers1