在做查询语句时,我用到了别名,而且查询的字段是表里其中的几个,而且别名是对应表里的字段名!!实体类也有对应属性!
在 Repository 接口定义了对应的借口,返回的是一个List集合
@Query(value = "select min(begin_date) as begin_date , max(end_date) as end_date, DATEDIFF(max(end_date),min(begin_date)) as days from workTime where user_id=:userId", nativeQuery=true)
List<WorkTimeEntity> findDay(@Param("userId") Long userId);
然后就出现了问题:
解决方法:
方法1: 用Map来接收(简单)
数据库的日期格式传到java有(.0)所以要转换一下格式
@Query(value = "select DATE_FORMAT(min(begin_date),'%Y-%m-%d %H:%I:%s') as begin_date ,DATE_FORMAT(max(end_date),'%Y-%m-%d %H:%I:%s') as end_date, DATEDIFF(max(end_date),min(begin_date)) as days from workTime where project_id=:projectId", nativeQuery=true)
Map<String,Object> findDay(@Param("projectId") Long projectId);
正好我的Controller接收也是Map,然后就直接传到前端!!
方法二:用对象来接收
首先要创建一个根据你要查询的字段的entity对象,@Id 注解必须要有,不然会报错,@Column注解的name要和数据库查询出来的字段要一致
直接在service层写
@PersistenceContext
EntityManager entityManager;
public AttendanceEntity findTime(Long id){
//自定义sql
String sql="select min(begin_date) as begin_date ,max(end_date) as end_date, DATEDIFF(max(end_date),min(begin_date)) as days from workTime where user_id=:userId";
//传入两个参数,一个是sql语句,一个是返回的entity对象!!
Query query = entityManager.createNativeQuery(sql, AttendanceEntity.class);
//把参数传入query中,接收多少个参数,就要set多少个参数
query.setParameter("userId", id);
//使用query的getSingleResult方法,返回对象
//也可以用List来接收,query有对应的方法
AttendanceEntity attendanceEntity = (AttendanceEntity)query.getSingleResult();
System.out.println("userEntity::"+attendanceEntity);
return attendanceEntity;
}