HibernateDAO总结.doc

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=session.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(););异常：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, data);appsourceDayStat.setStatstime(DateUtil.currentTimestamp();hibernateTemplate.saveOrUpdate(appsourceDayStat);succount+; dbLogger.warn(msg);hibernateTemplate.flush();hibernateTemplate.clear();/订单更新，重新统计int succountorder=0ListMap orderMapList = jdbcTemplate.queryForList(ordernumSql,starttime); if(orderMapList!=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);succountorder+; 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 e set e.status=:status where e.merchantid=:merchantid + and exists(from MovEcardBatch m where e.batchid=m.id and m.cardtype in(:cardtypes) ;Query query = session.createQuery(hql);query.setParameter(status, EcardConstant.DATA_HIS);/批次勾选掉，相当于冻结query.setParameter(merchantid, merchantid);query.setParameterList(cardtypes, cardtypes);return new Integer(query.executeUpdate(););createFilter 集合过滤器：Query query = getSession().createFilter(dealer.getDealers(), where this.user.userStatus=:userStatus);query.setBoolean(userStatus, userStatus);subDealers = query.list();通过Transformers.aliasToBean将结果集注入 VO对象中query.setResultTransformer(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(inty.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, SQLException 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语句必须加包名获取 添加后对象的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.class) .list();这里，结果集字段名被假设为与映射文件中指明的字段名相同。对于连接了多个表的查询，这就可能造成问题，因为可能在多个表中出现同样名字的字段。下面的方法就可以避免字段名重复的问题:List cats = sess.createSQLQuery(select cat.* from cats cat) .addEntity(cat, Cat.class) .list();这个查询指定了: SQL查询语句，它带一个占位符，可以让Hibernate使用字段的别名. 查询返回的实体，和它的SQL表的别名. addEntity()方法将SQL表的别名和实体类联系起来，并且确定查询结果集的形态。 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(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();后来我使用了命名SQL查询方式。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 testCaseSql = update TestCase t set t.caseStatus=: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 new Integer(query.executeUpdate();User - Project 多对多:(中间表映射)(查询主方表)关联方查询条件:String sql = select u from User as u join jectMidUsers 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” 只是取User表数据如果不关联查询(不需要join 连接)HibernateTemplate ht = this.getHibernateTemplate();Integer StatusActive = Constants.STATUS_ACTIVE;String hql = from User u where u.userMidRoles.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 = roleID, StatusActive ;return ht.find(hql, obj);from Department as d where =Tom;如果employees是普通组件属性，或单个的关联实体，则Hibernate会自动生成隐式的inner join如果myEvents是也一个集合，那么对不起！系统将会出现 org.hibernate.QueryException: illegal attempt to dereference collection异常。推荐这样写：from Department as d inner join fetch d.employees e where =Tom;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.findByCriteria(query);if(result=null|result.isEmpty() return 0;return (Long)result.get(0).intValue();(查询关联表)主表方查询条件:String sql = select u.userMidRoles from User u where u.id=:id;Join fetch（一定select inty）加载对象出来select inty from Inventory inty join fetch duct pd where 1=1如果：from Inventory inty join fetch duct 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, , 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); 也可以建立一个javabean显示结果:selectnewergal.CustomerRow(c.id,,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, , count(o) from 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, , 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); 统计每个客户的订单总价-加条件 订单大于一的:List result=session.createQuery(select c.id, , 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.println(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();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 as 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 mother.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 和 false: 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 maxelement(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)select p from NameList list, Person pwhere = some elements(s)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的属性可以被绑定到一个命名查询（named query）的参数上： Query q = s.createQuery(from foo Foo as foo where =: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.setMaxResults(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 express in the query if ( qux.calculateComplicatedAlgorithm() ) / delete the current instance iter.remove(); / dont need to process the rest break; 可滚动遍历(Scrollable iteration)如果你的JDBC驱动支持可滚动的ResuleSet，Query接口可以使用ScrollableResults，允许你在查询结果中灵活游走。 Query q = sess.createQuery(select , cat from DomesticCat cat + order by );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包裹起来。） ? 参数绑定及执行以编程方式(programatically)完成： Query q = sess.getNamedQuery(eg.DomesticC.and.minimum.weight);q.setString(0, name);q.setInt(1, minWeight);List cats = q.list();过滤集合集合过滤器(filter)是一种用于一个持久化集合或者数组的特殊的查询。查询字符串中可以使用this来引用集合中的当前元素。 Collection blackKittens = session.createFilter( pk.getKittens(), where this.color = ?) .setParameter( Color.BLACK, Hibernate.custom(ColorUserType.class) ) .list();过滤集合集合过滤器(filter)是一种用于一个持久化集合或者数组的特殊的查询。查询字符串中可以使用this来引用集合中的当前元素。 Collection blackKittens = session.createFilter( pk.getKittens(), where this.color = ?) .setParameter( Color.BLACK, Hibernate.custom(ColorUserType.class) ) .list();返回的集合可以被认为是一个包(bag, 无顺序可重复的集合(collection)，它是所给集合的副本。 原来的集合不会被改动（这与“过滤器(filter)”的隐含的含义不符，不过与我们期待的行为一致）。 请注意过滤器(filter)并不需要from子句（当然需要的话它们也可以加上）。过滤器(filter)不限定于只能返回集合元素本身。 Collection blackKittenMates = session.createFilter( pk.getKittens(), select this.mate where this.color = eg.Color.BLACK.intValue) .list();DetachedCriteria操作:创建别名即时是集合也是指其中元素相等时候可以判断，但是不等久不能单纯ne因为默认fetch时候，已经关联Inner Join detachedCriteria.createAlias(roles, rl);detachedCriteria.add(Restrictions.eq(rl.id, Constants.COORDINATOR_ROLE_ID);IN 操作：dc.add(Restrictions.in(tcp.District,array);搜索条件查询TestCase个数:this.getHibernateTemplate().findByCriteria(detachedCriteria.setProjection(Projections.rowCount().get(0).intValue();根据实体ID查询:detachedCriteria.add(Restrictions.eq(ph.id, testCase.getPhase().getId();匹配查询:detachedCriteria.add(Restrictions.like(caseName, %+ testCase.getCaseName().trim() + %)内连接抓起:detachedCriteria.setFetchMode(ph, FetchMode.SELECT);排序:detachedCriteria.addOrder(Order.asc(moduleName);Restrictions.like 匹配:Criterion lhs=Restrictions.like(userName,cName,MatchMode.ANYWHERE);Criterion rhs=Restrictions.like(ename,eName,MatchMode.ANYWHERE);detachedCriteria.add(Restrictions.or(lhs, rhs);MatchMode.EXACT：字符串中精确匹配，相当于like valueMatchMode.ANYWHERE：字符串在中间位置，相当于like%value%MatchMode.START：字符串在最前面，相当于likevalue%MatchMode.END：字符串在最后，相当于like%valuesqlRestriction()方法:DetachedCriteria detachedCriteria = DetachedCriteria.forClass(User.class);Object obj = projectID, StatusActive;Type types = Hibernate.INTEGER, Hibernate.INTEGER, Hibernate.INTEGER;detachedCriteria.add(Restrictions.sqlRestriction(alias.ID not in (select pu.UserID from projectmiduser pu where + pu.ProjectID=?) and alias.status =? order by alias.ename, obj, t