使用Hibernate实现多表查询
项目中使用的是Hibernate框架，对于表查询一直只针对一张表，原码：
// 搜索员工
?public List searchUseraccount(String username, String deptid, String cid,
????????????????????????????????????????? String groupid) throws Exception {
???? List accountList = new ArrayList();
???? try {
????????????? session = HibernateSessionFactory.getSession();
????????????? t = session.beginTransaction();
????????????? StringBuffer hql = new StringBuffer();
????????????? hql.append("from Account where cid ='" + cid
????????????????????????????? + "' and username like '%" + username + "%'");
????????????? if (null!=deptid&&!"0".equals(deptid)) {
???????????????????? hql.append(" and deptid='" + deptid + "'");
??????????????}
????????????? if (null!=groupid&&!"0".equals(groupid)) {
???????????????????? hql.append(" and gid='" + groupid + "'");
??????????????}
????????????? Query query = session.createQuery(hql.toString());
????????????? accountList = query.list();
????????????? t.commit();
?????} catch (Exception e) {
????????????? System.out.println("searchUseraccount fail!");
????????????? e.printStackTrace();
???? } finally {
????????????? HibernateSessionFactory.closeSession();
???? }
???? return accountList;
?}
但需求变化--->表结构变化，查询的结果必须通过两张表，通过搜索（http://zjsoft.iteye.com/blog/152328）得到了解决办法，写法如下：
// 搜索员工
?public List searchUseraccount(String username, String deptid, String cid,
?????????????????????????????????????????? String groupid) throws Exception {
???? List accountList = new ArrayList();
???? try {
???????????? session = HibernateSessionFactory.getSession();
??????????? ?t = session.beginTransaction();
???????????? StringBuffer hql = new StringBuffer();
?????????????hql.append("select * from account where cid ='" + cid
???????????????????????????? + "' and username like '%" + username + "%'");
?????????????if (null != deptid && !"0".equals(deptid) && !"".equals(deptid)) {
???????????????????? hql.append(" and deptid='" + deptid + "'");
?????????????}
???????????? if (null != groupid && !"0".equals(groupid) && !"".equals(groupid)) {
???????????????????? hql.append(" and (aid in (select aid from Agmapping where?"
???????????????????????????????????? +" gid = '" + groupid + "')))");
?????????????}
??????????? Query query = session.createSQLQuery(hql.toString())
?????????????????????????????????????????????????????? .addEntity(Account.class);
??????????? accountList = query.list();
??????????? t.commit();
???? } catch (Exception e) {
??????????? System.out.println("searchUseraccount fail!");
??????????? e.printStackTrace();
???? } finally {
??????????? HibernateSessionFactory.closeSession();
???? }
???? return accountList;
?}
不同于我以前的写法在于红色区域，createSQLQuery方法的参数是纯sql语句，关于createSQLQuery的用法参见：http://jihongbin12329.iteye.com/blog/88678
