刚开始是这样写的

    @Query(value = "SELECT ll.user_id id ,u.catong_img catong_img,ll.locationId location_id,ll.address address,ll.latitude latitude,ll.longitude longitude,ll.update_time update_time,ll.user_id user_id FROM t_user as u left JOIN (SELECT l.id locationId, address,latitude,longitude,l.update_time,l.user_id FROM t_user_location AS l RIGHT JOIN ( SELECT user_id, max( update_time ) update_time FROM t_user_location GROUP BY user_id ) AS t ON l.user_id = t.user_id AND l.update_time = t.update_time ) ll ON u.zu_id =?1 and u.id = ll.user_id",nativeQuery = true) 
    @Transactional 
    List getAllUserLocationByZuId(long zuId); 

发现返回的数据是这样的,设置的别名没有返回

[ 
  [ 
    1, 
    "吕志豪", 
    "", 
    3, 
    "3", 
    3, 
    3, 
    1513670259000, 
    1 
  ], 
  [ 
    2, 
    "胡勇", 
    "", 
    2, 
    "2", 
    2, 
    2, 
    1513151843000, 
    2 
  ] 
] 

之后是这样写的,使用EntityManager

    @PersistenceContext 
    private EntityManager em; 
    @Override 
    public List getAllUserLocation(long zuId) { 
       return em.createNativeQuery("SELECT  u.name name ,u.catong_img catongImg,ll.locationId locationId,ll.address address,ll.latitude latitude,ll.longitude longitude,ll.update_time updateTime,ll.user_id userId FROM t_user as u left JOIN (SELECT l.id locationId, address,latitude,longitude,l.update_time,l.user_id FROM t_user_location AS l RIGHT JOIN ( SELECT user_id, max( update_time ) update_time FROM t_user_location GROUP BY user_id ) AS t ON l.user_id = t.user_id AND l.update_time = t.update_time ) ll ON u.zu_id =1 and u.id = ll.user_id") 
        .unwrap(SQLQuery.class) 
                .setResultTransformer( 
                        AliasToEntityMapResultTransformer.INSTANCE 
                ) 
                .list(); 
    } 

结果返回

[ 
  { 
    "catongImg": "", 
    "address": "3", 
    "locationId": 3, 
    "latitude": 3, 
    "name": "吕志豪", 
    "updateTime": 1513670259000, 
    "userId": 1, 
    "longitude": 3 
  }, 
  { 
    "catongImg": "", 
    "address": "2", 
    "locationId": 2, 
    "latitude": 2, 
    "name": "胡勇", 
    "updateTime": 1513151843000, 
    "userId": 2, 
    "longitude": 2 
  } 
] 
原文地址:https://www.jianshu.com/p/e6bc577d03f0

发布评论

分享到:

IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

spring data jpa 原生sql 别名字段无法注入知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。