hibernate里面实现复杂的查询

minwh 2006-07-19

展开全文

hibernate里面实现复杂的查询

摘要:

由于hibernate对sql的二次封装，使部分复杂的查询语句不能被执行，我列举两种解决方法，和大家讨论！

第一种：查询结果集

Session session = this.getSession();
        List result = new ArrayList();
        String strSql = "select buy.isbn,buy.bookname,buy.bookengname,count(*) as counum from (select distinct usee.isbn from StuBasicInfo stu,Teachingmaterialuse usee ";
        strSql = strSql
                + " where stu.identityid = usee.identityid and stu.deptid = ‘"
                + banji + "‘ and usee.academicyearcode=‘" + xn
                + "‘ and usee.semestercode = ‘" + xq
                + "‘) ddd,Teachingmaterialbuyinto buy,Teachingmaterialuse usee ";
        strSql = strSql
                + "where usee.isbn= ddd.isbn and usee.isbn= buy.isbn group by buy.isbn,buy.bookname,buy.bookengname";
        System.out.println(strSql);
        try
        {
            Connection con = session.connection();
            Statement statement = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = statement.executeQuery(strSql);
            
            if (rs.next()) {
                while (!rs.isAfterLast()) {
                    List tempList = new ArrayList();
                    tempList.add(rs.getString("isbn"));
                    tempList.add(rs.getString("bookname"));
                    tempList.add(rs.getString("bookengname"));
                    tempList.add(rs.getString("counum"));
                    result.add(tempList);
                    rs.next();
                }
              }
        }catch (SQLException e)
        {
            System.out.println("----" + e);
        }
        catch (HibernateException e)
        {
            System.out.println("----" + e);
        }
        finally
        {
            this.closeSessionIfNecessary(session);
        }

第二种：使用session

Session session = this.getSession();
        int aCount = 0;
        try
        {
            String sqlText = "select count(*) from Classroominfo room where room.flag=‘1‘ and room.type=‘"
                    + userType + "‘ ";
            if ((name != null) && ((name != "null")))
            {
                sqlText = sqlText + " and room.name like ‘%" + name + "%‘ ";
            }
            else if ((buildingCode != null) && ((buildingCode != "null")))
            {
                sqlText = sqlText + " and room.buildingcode like ‘%"
                        + buildingCode + "%‘ ";
            }
            else if ((dept != null) && ((dept != "null")))
            {
                sqlText = sqlText + " and room.deptid =‘" + dept + "‘ ";
            }

            aCount = ((Integer) session.iterate(sqlText).next()).intValue();

        }
        catch (HibernateException e)
        {
            System.out.println("HibernateException" + e);
        }
        finally
        {
            this.closeSessionIfNecessary(session);
        }
        return aCount;