RT。
PL/SQL中验证SQL正确,但是转成HQL语句出现若干错误,多方寻找之后发现原来是Hibernate问题
不支持select子查询、from子查询、* 查询,on 关键字 等复杂查询...
解决方案1:
若页面非封装POJO对象输出数据,可在Hibernate中使用本地SQL查询,籍此解决问题。
public List queryAgentPaperStatus(String planID,String userCode,int perAgents){
List agentPaperStatus = new ArrayList();
String sql = "select user_name, to_char(" +perAgents+ " - to_number(qc)) ac from " +
"(select * from " +
"(select user_name, user_code from tbl_sys_users where user_code in " +
"(select user_code from tbl_qc_vgroup_users where plan_id like '" + planID + "')) lc" +
" left join " +
"(select agent_code, count(*) qc from tbl_qc_paper group by ID, agent_code having ID in " +
"(select paper_id from tbl_qc_paper_dispose where plan_id like '" + planID + "' and qc_user_code like '" + userCode + "')) rc" +
" on lc.user_code = rc.agent_code)";
System.out.println("----------------- :"+sql);
try{
agentPaperStatus = this.getHibernateTemplate().getSessionFactory().getCurrentSession()
.createSQLQuery(sql.toString()).list();
}catch(Exception e){
e.printStackTrace();
}
return agentPaperStatus;
}
解决方案2:
若页面封装POJO对象输出数据,可建立视图替代子查询部分,籍此解决问题
介于工作时间,一般都使用上述两种方法解决,网上还有其他解决方法,未试过~~