发表时间：2009-02-18
最后修改：2009-02-18
项目中使用的是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
