spring-data-jpa 查询视图,报错,找不到table或者视图!花了一天的时间在网上找了一份资料,亲测后分享给大家,参见:参考资料。
Controller层
@RestController
@RequestMapping("/api")
@ComponentScan
public class UserCertificationController extends BaseController {
@Autowired
private UserCertificationService userCerService;
/**
* 视图查询Demo,返回值data
*
* @return {"data":[{"id":"1","user_name":"mo","userid":"mo101"}],"code":"200","msg":"success"}
*/
@RequestMapping(value = "/findKdOrView", method = RequestMethod.GET)
public String findKdOrView() {
List<KdOrView> vlist = this.userCerService.findKdByNativeSQL();
return renderSuccess(vlist);
}
}
Service层
ObjectDaoService 是共通
ObjectDaoService.java
public interface ObjectDaoService {
EntityManager getEntityManager();
}
ObjectDaoServiceImpl.java
@Service
public class ObjectDaoServiceImpl implements ObjectDaoService {
@PersistenceContext
private EntityManager entityManager;
@Override
public EntityManager getEntityManager(){
return this.entityManager;
}
}
业务逻辑层
UserCertificationService
@Service
public interface UserCertificationService {
public List<KdOrView> findKdByNativeSQL();
}
UserCertificationServiceImpl
@Service
public class UserCertificationServiceImpl implements UserCertificationService {
@Resource
private ObjectDaoService objectDaoService;
public List<KdOrView> findKdByNativeSQL() {
// 1.组织sql语句,这里设置参数 ":id" 方式,还有一种是 "?1,?2",需要指定
String sql = "select id,user_name,userid from kdorview k where k.id = :id";
// 2.创建实体管理对象
EntityManager entityManager = objectDaoService.getEntityManager();
// 3.使用jpa 包装查询,获取query 对象
Query query = entityManager.createNativeQuery(sql);
// 4.再使用去包装查询获取 nativeQuery 对象
SQLQuery nativeQuery = query.unwrap(SQLQuery.class);
// 5.设置参数,对应上面的参数 ":id"
nativeQuery.setParameter("id", "1");
// 设置返回值类型Map,然后对Map进行处理。
nativeQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
// 6.执行查询,获取viewlist
List<KdOrView> viewlist = new ArrayList<KdOrView>();
// 解析viewlist,根据功能逻辑封装
@SuppressWarnings("unchecked")
List<Map<String, Object>> retVal = nativeQuery.list();
if (retVal != null && retVal.size() > 0) {
for (Map<String, Object> map : retVal) {
KdOrView kv = new KdOrView();
// key字段全部要大写,这里使用的是oracle数据库
kv.setId(map.get("ID").toString());
kv.setUser_name(map.get("USER_NAME").toString());
kv.setUserid(map.get("USERID").toString());
viewlist.add(kv);
}
}
return viewlist;
}
Domain
// domain 中不需要任何的注解,这是一个映射到视图的enity,并没有真实的表。
public class KdOrView implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
// KD Table Field
private String id;
private String user_name;
// Or Table Field
private String userid;
public KdOrView() {
}
public KdOrView(String id, String user_name, String userid) {
this.id = id;
this.user_name = user_name;
this.userid = userid;
}
// GET SET....... 省略
}
DB View:kdorView 视图结构。
备注:
中间因为想把view当做table使用,创建了接口Repository,但是总会报错找不到表或者视图。
使用
@Override public List<Report> getReport() { String jpql= String.format("select new %3$s(a.deptId, a.deptName, (select count(*) from %2$s b where b.deptId= a.deptId) as totalCount) from %1$s a", Depts.class.getName(), Students.class.getName(), Report.class.getName()); EntityManager entityManager= objectDaoService.getEntityManager(); //建立有类型的查询 TypedQuery<Report> reportTypedQuery= entityManager.createQuery(jpql, Report.class); //另外有详细查询条件的在jpql中留出参数位置来(?1 ?2 ?3....),然后在这设置 //reportTypedQuery.setParameter(1, params); List<Report> reports= reportTypedQuery.getResultList(); return reports; }
上面这种方法时候也总是提示no pressi......
参考资料:
http://blog.csdn.net/chaijunkun/article/details/8442406