《HibernateDAO总结.doc》由会员分享，可在线阅读，更多相关《HibernateDAO总结.doc（28页珍藏版）》请在三一文库上搜索。
1、protected int queryBySQLNameParamsCount(final String sql,final String paramnames,final Object. params)return hibernateTemplate.execute(new HibernateCallback() /返回个数非List时候必须用execute 非 executeFindOverridepublic Object doInHibernate(Session session)throws HibernateException, SQLException Query query=s
2、ession.createSQLQuery(sql);String names = StringUtils.split(paramnames, ,);for (int i=0,length=names.length; i length; i+) if(paramsi instanceof Collection)query.setParameterList(namesi, (Collection)paramsi);elsequery.setParameter(namesi, paramsi);return (BigInteger)query.uniqueResult().intValue();)
3、;异常：a different object with the same identifier value was already associated with如果必须两次操作可以用：两次操作中间用户 hibernateTemplate.flush();hibernateTemplate.clear();如：for (Map map : qryMapList) RecordMap data = new RecordMap(map);appsourceDayStat = new AppSourceDayStat();BindUtils.bindData(appsourceDayStat, da
4、ta);appsourceDayStat.setStatstime(DateUtil.currentTimestamp();hibernateTemplate.saveOrUpdate(appsourceDayStat);succount+; dbLogger.warn(msg);hibernateTemplate.flush();hibernateTemplate.clear();/订单更新，重新统计int succountorder=0ListMap orderMapList = jdbcTemplate.queryForList(ordernumSql,starttime); if(or
5、derMapList!=null&!orderMapList.isEmpty() for (Map map : orderMapList) RecordMap data = new RecordMap(map);appsourceDayStat = new AppSourceDayStat();BindUtils.bindData(appsourceDayStat, data);appsourceDayStat.setStatstime(DateUtil.currentTimestamp();hibernateTemplate.saveOrUpdate(appsourceDayStat);su
6、ccountorder+; JdbcTemplate query.list() 转换为返回Mapquery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);return (Integer) getHibernateTemplate().execute(new HibernateCallback()Overridepublic Object doInHibernate(Session session)throws HibernateException, SQLException String hql=update EcardExtra
7、 e set e.status=:status where e.merchantid=:merchantid + and exists(from MovEcardBatch m where e.batchid=m.id anrdtype in(:cardtypes) ;Query query = session.createQuery(hql);query.setParameter(status, EcardConstant.DATA_HIS);/批次勾选掉，相当于冻结query.setParameter(merchantid, merchantid);query.setParameterLi
8、st(cardtypes, cardtypes);return new Integer(query.executeUpdate(););createFilter 集合过滤器：Query query = getSession().createFilter(dealer.getDealers(), whererStatus=:userStatus);query.setBoolean(userStatus, userStatus);subDealers = query.list();通过Transformers.aliasToBean将结果集注入 VO对象中query.setResultTransf
9、ormer(Transformers.aliasToBean(MyVO. class)VO对象必须有无参数构造函数，Hibernate要生成默认构造函数对象，然后Setter属性 public List findMergeAvailableReqInventoVos(final String authorPin, final String chargeCode) final String sql = select inty.authorPin as authorPin,inty.stockNo as stockNo,sum(inty.qtyOnHand) as qtyOnHand,sum(in
10、ty.quantity) as quantity + from Inventory as inty where inty.authorPin=? and inty.chargeCode=? group by inty.stockNo,inty.authorPin,inty.chargeCode; return (List) this.getHibernateTemplate().execute( new HibernateCallback() public Object doInHibernate(Session session) throws HibernateException, SQLE
11、xception SQLQuery query = session.createSQLQuery(sql); query.setParameter(0, authorPin); query.setParameter(1, chargeCode); query.setResultTransformer(Transformers.aliasToBean(com.mmm.china.scms.domain.vo.ReqInventoVo.class); return query.list(); ); 如果”select new com.whl.Entity() from AAA where ”HQL
12、语句必须加包名获取 添加后对象的Id： System.out.println(+e.getId();/ null externalCustomerDAO.saveCustomer(e); System.out.println(*+e.getId();/ 可以获取到ID使用SQLQuery：对原生SQL查询执行的控制是通过SQLQuery接口进行的，通过执行Session.createSQLQuery()获取这个接口。最简单的情况下，我们可以采用以下形式：List cats = sess.createSQLQuery(select * from cats) .addEntity(Cat.clas
13、s) .list();这里，结果集字段名被假设为与映射文件中指明的字段名相同。对于连接了多个表的查询，这就可能造成问题，因为可能在多个表中出现同样名字的字段。下面的方法就可以避免字段名重复的问题:List cats = sess.createSQLQuery(select cat.* from cats cat) .addEntity(cat, Cat.class) .list();这个查询指定了: SQL查询语句，它带一个占位符，可以让Hibernate使用字段的别名. 查询返回的实体，和它的SQL表的别名. addEntity()方法将SQL表的别名和实体类联系起来，并且确定查询结果集的形
14、态。 addJoin()方法可以被用于载入其他的实体和集合的关联. List cats = sess.createSQLQuery( select cat.*, kitten.* from cats cat, cats kitten where kitten.mother = cat.id ) .addEntity(cat, Cat.class) .addJoin(kitten, cat.kittens) .list();原生的SQL查询可能返回一个简单的标量值或者一个标量和实体的结合体。 Double max = (Double) sess.createSQLQuery(select max
15、(cat.weight) as maxWeight from cats cat) .addScalar(maxWeight, Hibernate.DOUBLE); .uniqueResult();除此之外，你还可以在你的hbm文件中描述结果集映射信息，在查询中使用。List cats = sess.createSQLQuery( select cat.*, kitten.* from cats cat, cats kitten where kitten.mother = cat.id ) .setResultSetMapping(catAndKitten) .list();后来我使用了命名SQ
16、L查询方式。HQL 操作:TestCase - Steps 一对多:根据testCase 查找其所有Steps:his.getHibernateTemplate().find(select t.steps from TestCase t where t.id=?,new Object caseId );批量删除操作:Integer idsQuery query = session.createQuery(delete TestCase where id in(:id)query.setParameterList(id, ids).executeUpdate()更新:String testCas
17、eSql = update TestCase t seseStatus=:status where t.id in(:id); Query query = session.createQuery(testCaseSql);query.setEntity(status, caseStatus);/设定实体 (等同下)(3)setEntity()方法把参数与一个持久类的实例绑定query.setParameter(status ,caseStatus,Hibernate.entity(Dropdown.class);query.setParameterList(id, ids);return ne
18、w Integer(query.executeUpdate();User - Project 多对多:(中间表映射)(查询主方表)关联方查询条件:String sql = select u from User as u join u.projectMidUsers as pu with pu.id=:id where u.id=:uid order by u.ename ;Query query = session.createQuery(sql);query.setInteger(id, projectId);query.setInteger(uid , userId);“select u”
19、 只是取User表数据如果不关联查询(不需要join 连接)HibernateTemplate ht = this.getHibernateTemplate();Integer StatusActive = Constants.STATUS_ACTIVE;String hql = from User u whererMidRoles.id = ? and u.status= ? order by u.ename;/效果相同 String hql=select m from Menu as m join m.roles as r where r.id = 1 ;Object obj = role
20、ID, StatusActive ;return ht.find(hql, obj);from Department as d wher=Tom;如果employees是普通组件属性，或单个的关联实体，则Hibernate会自动生成隐式的inner join如果myEvents是也一个集合，那么对不起！系统将会出现 org.hibernate.QueryException: illegal attempt to dereference collection异常。推荐这样写：from Department as d inner join fetch d.employees e wher=Tom;
21、Count DetachedCriteria 查询个数：Overridepublic int getChannelFeeListCount(ChannelFee channelFee,String timetype, Timestamp starttime,Timestamp endtime) DetachedCriteria query = buidlQuery(channelFee,timetype,starttime,endtime);query.setProjection(Projections.rowCount();List result = hibernateTemplate.fi
22、ndByCriteria(query);if(result=null|result.isEmpty() return 0;return (Long)result.get(0).intValue();(查询关联表)主表方查询条件:String sql = selecerMidRoles from User u where u.id=:id;Join fetch（一定select inty）加载对象出来select inty from Inventory inty join fetch inty.product pd where 1=1如果：from Inventory inty join fet
23、ch inty.product pd where 1=1 报错检索单个对象uniqueResult() 方法如果不确定返回的时单个对象先用Query和Criteria的serMaxResult(1)方法 设定只返回一个对象.如果确定只返回一个对象 比如from Customer c where c.id1比较运算 条件为null的某个对象Listresult=session.createQuery(fromOrderowhereo.customerisnull).list();投影查询 只检索出需要的字段List result=session.createQuery(select c.id,
24、c.name, o.orderNumber from Customer c join c.orders o where o.orderNumber like T%).list(); Iterator it=result.iterator(); while(it.hasNext() . Object row=(Object)it.next(); Long id=(Long)row0; String name=(String)row1; String orderNumber=(String)row2; System.out.println(id+ +name+ +orderNumber); 也可以
25、建立一个javabean显示结果:selectnewergal.CustomerRow(c.id,c.name,o.orderNumber)fromCustomercjoinc.ordersowhereo.orderNumberlikeT%while(it.hasNest()CustomerRowcr=(CustomerRow)it.next();Longid=(Long)cr.getId();Customer - Order 一对多:统计数目(统计订单数目):List result=session.createQuery(select c.id, c.name, count(o) from
26、Customer c join c.orders o group by c.id).list(); Iterator it=result.iterator(); while(it.hasNext() . Object row=(Object)it.next(); Long id=(Long)row0; String name=(String)row1; Long num=(Long)row2; System.out.println(num+ +name+ +id); 统计每个客户的订单总价-右外连接:List result=session.createQuery(select c.id, c.
27、name, sum(o.price) from Customer c right outer join c.orders o group by c.id).list(); Iterator it=result.iterator(); while(it.hasNext() Object row=(Object)it.next(); Long id=(Long)row0; String name=(String)row1; Double num=(Double)row2; System.out.println(num+ +name+ +id); 统计每个客户的订单总价-加条件 订单大于一的:Lis
28、t result=session.createQuery(select c.id, c.name, sum(o.price) from Customer c join c.orders o group by c.id having(count(o)1).list(); Iterator it=result.iterator(); while(it.hasNext() Object row=(Object)it.next(); Long id=(Long)row0; String name=(String)row1; Double num=(Double)row2; System.out.pri
29、ntln(num+ +name+ +id); 查询时间段:tx=session.beginTransaction();Date beginDate=java.sql.Date.valueOf(2006-8-1);Date endDate=java.sql.Date.valueOf(2006-9-1);List result=session.createQuery(from Customer c where c.retime =:beginTime).setTimestamp(endTime, endDate).setTimestamp(beginTime, beginDate).list();
30、for(Iterator it=result.iterator(); it.hasNext();)Customer c =(Customer)it.next();System.out.println(Customers name : + c.getName();System.out.println(Customers regiestertime : + c.getRetime();查询语句可以返回多个对象和（或）属性，存放在 Object队列中, select mother, offspr, from DomesticCat as mother inner join mother.mate a
31、s mate left outer join mother.kittens as offspr或存放在一个List对象中, select new list(mother, offspr, )from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr也可能直接返回一个实际的类型安全的Java对象, （元素以逗号隔开，）select new Family(mother, mate, offspr)from DomesticCat as mother join m
32、other.mate as mate left join mother.kittens as offspr（元素以逗号隔开，）select new map( max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n ) from Cat cat在Hibernate配置文件中声明HQL“查询替代（query substitutions）”之后， 布尔表达式（Booleans）可以在其他表达式中轻松的使用: true 1, false 0系统将该HQL转换为SQL语句时，该设置表明将用字符 1 和 0 来 取代关键字true 和 f
33、alse: from Cat cat where cat.alive = true你可以用特殊属性size, 或是特殊函数size()测试一个集合的大小。 from Cat cat where cat.kittens.size 0from Cat cat where size(cat.kittens) 0对于索引了（有序）的集合，你可以使用minindex 与 maxindex函数来引用到最小与最大的索引序数。 同理，你可以使用minelement 与 maxelement函数来 引用到一个基本数据类型的集合中最小与最大的元素。 from Calendar cal where maxeleme
34、nt(cal.holidays) current datefrom Order order where maxindex(order.items) 100from Order order where minelement(order.items) 10000在传递一个集合的索引集或者是元素集(elements与indices 函数) 或者传递一个子查询的结果的时候，可以使用SQL函数any, some, all, exists, in select mother from Cat as mother, Cat as kitwhere kit in elements(foo.kittens)se
35、lect p from NameList list, Person pwher = some elements)from Cat cat where exists elements(cat.kittens)from Player p where 3 all elements(p.scores)from Show show where fizard in indices(show.acts)查询是否存在关联的子元素：from CycleTest c where exists elements(c.exeTestCaseses) and c.id=? ,JavaBean的属性可以被绑定到一个命名查
36、询（named query）的参数上： Query q = s.createQuery(from foo Foo as foo wher=:name and foo.size=:size);q.setProperties(fooBean); / fooBean包含方法getName()与getSize()List foos = q.list();Collection 分页：通过将接口Query与一个过滤器（filter）一起使用，集合（Collections）是可以分页的： Query q = s.createFilter( collection, ); / 一个简单的过滤器q.setMaxR
37、esults(PAGE_SIZE);q.setFirstResult(PAGE_SIZE * pageNumber);List page = q.list();迭代式获取结果(Iterating results) 迭代筛选/ fetch idsIterator iter = sess.createQuery(from eg.Qux q order by q.likeliness).iterate();while ( iter.hasNext() ) Qux qux = (Qux) iter.next(); / fetch the object / something we couldnt ex
38、press in the query if ( lculateComplicatedAlgorithm() ) / delete the current instance iter.remove(); / dont need to process the rest break; 可滚动遍历(Scrollable iteration)如果你的JDBC驱动支持可滚动的ResuleSet，Query接口可以使用ScrollableResults，允许你在查询结果中灵活游走。 Query q = sess.createQuery(selec, cat from DomesticCat cat + or
39、der b);ScrollableResults cats = q.scroll();if ( cats.first() ) / find the first name on each page of an alphabetical list of cats by name firstNamesOfPages = new ArrayList(); do String name = cats.getString(0); firstNamesOfPages.add(name); while ( cats.scroll(PAGE_SIZE) ); / Now get the first page of cats pageOfCats = new ArrayList(); cats.beforeFirst(); int i=0; while( ( PAGE_SIZE i+ ) & cats.next() ) pageOfCats.add( cats.get(1) );cats.close()外置命名查询(Externalizing named queries)你可以在映射文件中定义命名查询(named queries)。 （如果你的查询串中包含可能被解释为XML标记(markup)的字符，别忘了用CDATA包裹起来。）