参考如下
例如:student表和score表需要做联合查询。
1)sql: select s.id,s.name,sc.score from student as s,score as sc where s.id = sc.userId;
(字段都是用的数据库中字段名称)
2)HQL: select s.id,s.name,sc.score from Student as s,Score as sc where s.id = sc.userId;
(上面字段都是 javabean的属性)
如果按1)查询的话,必须调用 session.createSQLQuery();方法
如果按2)查询,还是调用 session.createQuery();
只是要注意,平时查询的时候,例如:“from Student ”查询的结果集 封装的全都是student对象,但是2)执行的结果集里面不是对象,而是一系列数组。需要转换成需要的样式。
下面 是查询的一段代码:
Session session = getHibernateTemplate().getSessionFactory()
.getCurrentSession();
StringBuffer sb = new StringBuffer(" select user.username,user.truename,user.sex,user.idnum,user.level,s.sumScore from Score as s,Examuser as user where s.id.userId = user.id ");
if(score != null){
if(score.getExamId()!=null && !"".equals(score.getExamId())
&& !"null".equals(score.getExamId())){
sb.append(" and s.examId =:examId ");
}
if(score.getExamPlace()!=null && !"".equals(score.getExamPlace())
&& !"null".equals(score.getExamPlace())){
sb.append(" and s.examPlace =:examPlace ");
}
if(score.getUsername()!=null && !"".equals(score.getUsername())
&& !"null".equals(score.getUsername())){
sb.append(" and s.username like:username ");
}
}
sb.append(" order by s.sumScore desc ");
Query q = session.createQuery(sb.toString());
if(score != null){
if(score.getExamId()!=null && !"".equals(score.getExamId())
&& !"null".equals(score.getExamId())){
q.setParameter("examId",score.getExamId());
}
if(score.getExamPlace()!=null && !"".equals(score.getExamPlace())
&& !"null".equals(score.getExamPlace())){
q.setParameter("examPlace",score.getExamPlace());
}
if(score.getUsername()!=null && !"".equals(score.getUsername())
&& !"null".equals(score.getUsername())){
q.setParameter("username","%"+score.getUsername()+"%");
}
}
List list = q.list();
需要将查询的结果集 进行一下转换:
List stuList = scoreService.findAllScore(queryScore, null); // 返回的结果集
if(stuList != null && stuList.size()>0){
list = new LinkedList();
StudentScore st;
for(int i = 0; i < stulist.size();i++){="" st="new" studentscore();="" object[]="" object="(Object[])stuList.get(i);//" 每行记录不在是一个对象="" 而是一个数组="" string="" userid="(String)object[0];" string="" username="(String)object[1];" string="" truename="(String)object[2];" string="" sex="(String)object[3];" string="" idnum="(String)object[4];" string="" level="(String)object[5];" double="" sumscore="Double.parseDouble(String.valueOf(object[6]));" string="" paperid="(String)object[7];" 重新封装在一个javabean里面="" st.setuserid(userid);="" st.setusername(username);="" st.settruename(truename);="" st.setidnum(idnum);="" st.setlevel(dictswitch.getvalue("dict_level",level));="" st.setsex(dictswitch.getvalue("dict_sex",sex));="" st.setpaperid(paperid);="" st.setsumscore(sumscore);="" st.setexamplace(dictswitch.getvalue("dict_examstation",examplace));="" list.add(st);="" 最终封装在list中="" 传到前台。="">
用户登录
还没有账号?立即注册
用户注册
投稿取消
文章分类: |
|
还能输入300字
上传中....