在使用hibernate3的过程中,有时需要一些复杂的查询,通过查询对象或者HQL语言可能写起来很繁琐或者很难实现,这时将不得不用支持原生sql的查询对象SQLQuery来实现。
具体实现可以参考我项目中使用到的一个DAO实现类,部分代码如下:
·1)
public PageControl getMailModels(int cpage, int len, int type, String depId)
{
StringBuffer sqlcount = new StringBuffer(
"SELECT count(*) FROM Wffzjbxx_mail b WHERE b.status=0");
sqlcount
.append(
" AND b.revDept_id =:depId UNION (SELECT b.id AS id, b.name,b.title")
.append(
" FROM Wffzjbxx_mailprocess a,Wffzjbxx_mail b WHERE a.mail_id=b.id")
.append(
" AND b.status = 1 AND b.validate=1 AND a.revDept_id =:depId AND")
.append(
" (SELECT COUNT(*) FROM Wffzjbxx_mailprocess c WHERE c.mail_id=b.id")
.append(" AND seddept_id =:depId) < 1) ORDER BY id");
StringBuffer sql = new StringBuffer(
"SELECT {b.*} FROM Wffzjbxx_mail b WHERE status = 0 AND revDept_id");
sql
.append(
" =:depId UNION (SELECT {b.*} FROM Wffzjbxx_mailprocess a,")
.append(
" Wffzjbxx_mail b WHERE a.mail_id = b.id AND b.status = 1 AND ")
.append(
" b.validate=1 AND a.revDept_id =:depId AND(SELECT COUNT(*)")
.append(" FROM Wffzjbxx_mailprocess c WHERE c.mail_id = b.id ")
.append(" AND seddept_id =:depId) < 1) ORDER BY id");
if (cpage == 0)
{
cpage = 1;
}
int start = (cpage - 1) * len;
SQLQuery querycount = getSession().createSQLQuery(sqlcount.toString());
querycount.setParameter("depId", depId);
SQLQuery query = getSession().createSQLQuery(sql.toString());
query.addEntity("b", MailModel.class); //添加表与对象的映射
query.setParameter("depId", depId);
query.setFirstResult(start);
query.setMaxResults(len);
PageControl pc = new PageControl();
pc.setPagesize(len);
pc.setCpage(cpage);
pc.setTotalitem(((Integer) querycount.uniqueResult()).intValue());
pc.setList(query.list());
return pc;
}
这种是通过建立表的别名与实体对象产生映射对应
·2)
public PageControl getMailModels1(int cpage, int len, int type, String depId)
{
StringBuffer sqlcount = new StringBuffer(
"SELECT count(*) FROM Wffzjbxx_mail b WHERE b.status = 0 AND b.revDept_id =:depId");
sqlcount
.append(
" UNION (SELECT b.id AS id, b.name,b.title FROM Wffzjbxx_mailprocess a, Wffzjbxx_mail b")
.append(
" WHERE a.mail_id = b.id AND b.status = 1 AND b.validate=1 AND a.revDept_id =:depId AND")
.append(
" (SELECT COUNT (*) FROM Wffzjbxx_mailprocess c WHERE c.mail_id = b.id AND seddept_id =:depId) < 1)")
.append(" ORDER BY id");
StringBuffer sql = new StringBuffer(
"SELECT b.id as id ,b.name as name,'测试 的 title' as title FROM Wffzjbxx_mail b WHERE status = 0 AND revDept_id =:depId");
sql
.append(
" UNION (SELECT b.id as id ,b.name as name,'测试的字段' as title FROM Wffzjbxx_mailprocess a, Wffzjbxx_mail b")
.append(
" WHERE a.mail_id = b.id AND b.status = 1 AND b.validate=1 AND a.revDept_id =:depId AND")
.append(
" (SELECT COUNT(*) FROM Wffzjbxx_mailprocess c WHERE c.mail_id = b.id AND seddept_id =:depId) < 1)")
.append(" ORDER BY id");
System.out.println("sql===" + sql);
if (cpage == 0)
{
cpage = 1;
}
int start = (cpage - 1) * len;
SQLQuery querycount = getSession().createSQLQuery(sqlcount.toString());
querycount.setParameter("depId", depId);
SQLQuery query = getSession().createSQLQuery(sql.toString());
query.setResultTransformer(Transformers.aliasToBean(MailModel.class)); //将查询的结果与实体对象建立关系
query.setParameter("depId", depId);
query.setFirstResult(start);
query.setMaxResults(len);
PageControl pc = new PageControl();
pc.setPagesize(len);
pc.setCpage(cpage);
pc.setTotalitem(((Integer) querycount.uniqueResult()).intValue());
pc.setList(query.list());
return pc;
}
这种是通过hibernate的结果关系转化类来实现的查询结果与实体对象的关联关系
第二种方法简洁方便,灵活易用,可以取出一个表中任意有用的字段,但是需要注意的是,在查询语句sql中,查询字段如id,name,title必须与所对应的实体对象中的字段名称大小写保持一致,这样才可以将值对应到实体对象中。
具体代码见附件