hibernate3中通过nativesql或取部分字段并映射为具体对象的实现

本文介绍如何使用 Hibernate 的 SQLQuery 对象进行复杂查询。通过两个示例展示了如何使用原生 SQL 进行分页查询,并将查询结果映射到实体对象上。第一种方法通过表别名与实体对象映射,第二种方法利用 Hibernate 结果转换器简化操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

          在使用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必须与所对应的实体对象中的字段名称大小写保持一致,这样才可以将值对应到实体对象中。

具体代码见附件

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值