用nativeQuery,写在@Query中无法转换为自定义的实体类
@Query(value = "SELECT name AS orderName,DATE_FORMAT(create_date, ?1) AS time from order_image GROUP BY time ORDER BY time", nativeQuery = true)
List<Map<String, Object>> findCountByTime(String time);
@Data @NoArgsConstructor
@AllArgsConstructor
public class orderDto {
private String orderName;
private String time;
}
JPA官方推荐的多表关联查询使用不便,接触的有些项目可能会使用JPA 做简单查询,Mybaits做复杂查询。所以想要寻找一种好用的解决方案。
JPA多表关联的实现方式
1.使用Specification实现映射关系匹配,如@ManyToOne等
2.使用NativeQuery等sql或hql来实现
优缺点对比
1.映射关系是hibernate的入门基础,很多人都会习惯去使用。个人不太喜欢这种方式,复用性太弱,且不灵活特别是在多表复杂业务情况下。
2.使用Specification方式需要继承JpaSpecificationExecutor接口,构造对应的方法后传入封装查询条件的Specification对象。逻辑上简单易懂,但是构造Specification对象需要拼接格式条件非常繁琐。
3.直接使用NativeQuery等方式实现复杂查询个人比较喜欢,直观且便利,弊端在于无法返回自定义实体类。需要手动封装工具类来实现Object到目标对象的反射。
使用sql并返回自定义实体类
import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;
@Repository
public class EntityManagerDAO {
@PersistenceContext
private EntityManager entityManager;
/**
* 人员列表排序
* @return
*/
@Transactional
public List<BackstageUserListDTO> listUser(){
String sql = "select a.create_time createTime," +
"a.mobilephone phoneNum," +
"a.email email,a.uid uid," +
"a.enabled enabled," +
"c.id_number idNumber," +
" (case b.`status` when 1 then 1 else 0 end) status " +
"from tbl_sys_user a " +
"LEFT JOIN user_high_qic b on a.uid=b.u_id" +
"LEFT JOIN user_qic c on a.uid=c.uid " +
"ORDER BY status desc";
SQLQuery sqlQuery = entityManager.createNativeQuery(sql).unwrap(SQLQuery.class);
Query query =
sqlQuery.setResultTransformer(Transformers.aliasToBean(BackstageUserListDTO.class));
List<BackstageUserListDTO> list = query.list();
entityManager.clear();
return list;
}
}