不小结就得付出代价

不总结就得付出代价
    好久没有去研究Hibernate了，过去的东西已经全忘。今天拾起来时我付的代价非常之大，最终根源在于没有总结以往的开发经验，异常非常之熟悉确不能快速锁定问题源。我就以下面几几个问题做为切入点来总结一下，这块里所涉及到的知识点。

    问题列表：
    1，分组查询
    2，left outer join/right outer join/full outer join/inner outer join区别


问题1：分组查询
    分组查询不就是group by嘛，确实是这样。但是在hibernate里面查出来的数据就不一样，我所有指的定是每列数据。例如：
    I)HQL(Hibernate Query Language)
 

  from SysUser user group by userId

    使用HQL查询出来的是符合条件，所有字段的数据
    II)QBC(Query By Criteria)

    DetacheCriteria dc = DetachedCriteria.forClass(SysUser.class);    dc.setProjection(Projections.groupProperty("this.userId"));    List<SysUser> list = sysUserService.findByCriteria(dc);

    这段码看上去没有一点错，我们还是用后面打印的SQL语句来说话吧。这里我就手工模拟写出后台打印SQL语句，其实意思是一样。
   

select this_.user_id from sys_user this_ group by this_.user_id

    看完这一段SQL语句，你还认为上面代码没有错吗？相信你一眼就可以看会出现什么样的问题：ClassCastException这个异常会抛出来。问题出来那总得有解决方案吧，肯定是有的。具体怎么选择那你自己的事了。
    方案一（不推荐）：直接用SQL查询
    方案二:是用示例查询

    select new SysUser(user.userId,user.name) from SysUser user group by user.userId;

    这样查底的前提是SysUser类中必须有一个SysUser(userId,name)构造方法
    方案三：直接返回对象，这又是什么意思呢？看下面代码就知道意思啦
 

  select user from SysUser user group by user.userId

   
    查询这样的简单的表不用费多大劲。下面再看看一个复杂点的表结构。

@Entity@Table(name = "doc_document")@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)public class DocDocument implements java.io.Serializable {private static final long serialVersionUID = 8811969619989885931L;@Id@GeneratedValue(strategy = GenerationType.IDENTITY)@Column(name = "doc_document_id")private Integer docDocumentId;@OneToMany(mappedBy = "docDocument")private Set<DocReceiveApprove> docReceiveApproves = new HashSet<DocReceiveApprove>(0);@OneToMany(mappedBy = "docDocument")private Set<DocFbTotal> docFbTotals = new HashSet<DocFbTotal>(0);@OneToMany(mappedBy = "docDocument")private Set<DocSend> docSends = new HashSet<DocSend>(0);@OneToMany(mappedBy = "docDocument")private Set<DocAttach> docAttachs = new HashSet<DocAttach>(0);@OneToMany(mappedBy="docDocument")private Set<DocDocumentReturn> docDocumentReturns = new HashSet<DocDocumentReturn>(0);//@Fetch(FetchMode.SELECT)@OneToMany(mappedBy="docDocument")private Set<DocDocumentUser> dcDocumentUsers = new HashSet<DocDocumentUser>(0);........}@Entity@Table(name = "doc_document_user")@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)public class DocDocumentUser implements java.io.Serializable {private static final long serialVersionUID = 8811969619989885931L;@Id@GeneratedValue(strategy = GenerationType.IDENTITY)@Column(name = "doc_document_user_id")private Integer docDocumentUserId;private Integer isflow;@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE})@JoinColumn(name = "doc_document_id")private DocDocument docDocument;@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE })@JoinColumn(name = "user_id")private CommonUser commonUser;.........}@Entity@Table(name="common_user")@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)public class CommonUser implements java.io.Serializable {private static final long serialVersionUID = 6147253484121456257L;@Id    @GeneratedValue(strategy=GenerationType.IDENTITY)     @JoinColumn(name = "user_id")private Integer userId;//用户ID//@Fetch(FetchMode.SELECT)@OneToMany(mappedBy="commonUser")private Set<DocDocumentUser> dcDocumentUsers = new HashSet<DocDocumentUser>(0);@OneToMany(mappedBy="commonUser")private Set<DocFbTotal> docFbTotals = new HashSet<DocFbTotal>(0);@OneToMany(mappedBy="commonUser")private Set<DocReceiveApproveCheck> docReceiveApproveChecks = new HashSet<DocReceiveApproveCheck>(0);@OneToMany(mappedBy="commonUser")private Set<DocReceiveinfoHst> docReceiveinfoHsts = new HashSet<DocReceiveinfoHst>(0);@OneToMany(mappedBy="commonUser")private Set<DocRrSend> docRrSends = new HashSet<DocRrSend>(0);@OneToMany(mappedBy="commonUser")private Set<DocReceiveApproveDraw> docReceiveApproveDraws = new HashSet<DocReceiveApproveDraw>(0);@OneToMany(mappedBy="commonUser")private Set<InfoSender> infoSenders = new HashSet<InfoSender>(0);@OneToMany(mappedBy="commonUser")private Set<DocPrintTrackHst> docPrintTrackHsts = new HashSet<DocPrintTrackHst>(0);@OneToMany(mappedBy="commonUser")private Set<LogOperation> logOperations = new HashSet<LogOperation>(0);......}

      代码就贴一部分，可以达到理解的用途就行。
      下面方法的目录还是按组查询：

public List<DocDocument> getWaitDocument() { DetachedCriteria dc = DetachedCriteria.forClass(DocDocument.class); dc.createCriteria("dcDocumentUsers", "ddu"); dc.setFetchMode("ddu", FetchMode.SELECT); dc.add(Restrictions.eq("ddu.dealstatus", "1")); dc.createCriteria("ddu.commonUser", "user"); dc.add(Restrictions.eq("user.userCode", "111")); dc.setProjection(Projections.groupProperty("this.docDocumentId")); List<DocDocument> list = this.documentService.findByCriteria(dc);}

      好啦，问题源肯定不用说就可以看出来啦。
      如果我想得到DocDocument对象的某个Set集合对象，我想方案一/方案二全部用不上了。但是方案三就得改造一下，如下：
     

String hql = "select doc from DocDocument as doc left join doc.dcDocumentUsers as ddu  left join ddu.commonUser as user group by doc.docDocumentId order by doc.docDocumentId desc";

     好啦问题总算解决啦。
     总结一下，我们的解决方案用的就是下一个问题所要提到的东西。那咱们就进入第二个问题讲解。
     首先我们得了解一下，left outer join/right outer join/full outer join/inner outer join的工作原理。
    



     今天就总结这两个问题
   


    DetacheCriteria dc = DetachedCriteria.forClass(SysUser.class); dc.setProjection(Projections.groupProperty("this.userId")); List<SysUser> list = sysUserService.findByCriteria(dc);
出错问题的根本在于，数据库中不会允许类似下面的SQL

  select * from t_city group by province_id

那么我要问下

  from SysUser user group by userId

这句HQL到底想得到什么？
from SysUser user group by userId

DetacheCriteria dc = DetachedCriteria.forClass(SysUser.class); dc.setProjection(Projections.groupProperty("this.userId")); List<SysUser> list = sysUserService.findByCriteria(dc);
出错问题的根本在于，数据库中不会允许类似下面的SQL

  select * from t_city group by province_id

那么我要问下

  from SysUser user group by userId

这句HQL到底想得到什么？

from SysUser user group by user.userId
不好意思，漏了个别名，这个跟上面提出的问题还没有太大的关联。

查看更多 下一篇