这是我以前BLOG的东东，写于2005年初做用户\单位管理模块的那个时期

---

单位代码是由行政区划+序号组成的,所以不好用序列,但我要取出当前最大的一个单位序号,因此我在TDWDM.HBM.XML中加上这段HSQL:
    <query name="maxdwdm1">
      <![CDATA[
        select max(substr(tdwdm.ccode,7,2)) from com.sugem.appAdmin.hbm.Tdwdm as tdwdm
      ]]>
    </query>

Java类中这样用:
    public String findByMaxdw1Code() throws HibernateDaoException {
        Session sess = this.getSession();
        try {
            Query query = sess.getNamedQuery("maxdwdm1");
            //如果HSQL中有参数的话就这样用setParameter
            Object results = (Object) query.uniqueResult();
            return (String) results;
        } catch (HibernateException he) {
            throw new HibernateDaoException("通过单位id查找单位时出错" + he.getMessage(), he);
        }
    }

还有一种情况,我要得到用户没有的权限,可以这样用:

    public List findOutUserAuthByUserid(String userid) throws AppException {
        Session s = this.initSession();
        TuserauthorityDao dao = new TuserauthorityDao(s);
        try {
            List l = dao.find("from com.sugem.appAdmin.hbm.Tappywlx as ywlx "
                              +"where ywlx.ccode not in "
                              +"(select userauth.cywlx "
                              +"from com.sugem.appAdmin.hbm.Tuserauthority as userauth "
                              + "where userauth.cuserid='" + userid + "')");
            this.closeSession(s);
            return l;
        } catch (HibernateDaoException he) {
            this.closeSession(s);
            throw new AppException("查找组数据findOutUserAuthByUserid时出错：" +
                                   he.getMessage());
        }
    }

---

现在看来，hibernateHSQL的确没有ibatis的方便，现在有时临时做一些报表我还是喜欢采用以前老早用的org.apache.commons.beanutils.RowSetDynaClass类......