hibernate执行sql解决性能问题

 最近的项目中发现某些页面打开速度非常慢，最慢的情况下甚至到了30秒。
分析原因后发现是加载由于实体间配了1对1的双向关联，导致lazy加载失效，每次加载都会把关联实体的全部属性查询出来，对性能影响特别大。

最终的解决方案是直接执行sql取得需要的字段，然后再转为对象。代码如下：          

Session session = null;
			String sql = "select o.ID as id, o.OFFICE_NO as officeNo from ASY_OFFICE_INFO o where o.OFFICE_SYSTEM='" + sys + "' and o.OFFICE_STATUS = '1' and o.DEPARTMENT_ID =" + company + " and o.ISEXISTS is null";
			session = baseDao.getHibernate().getSessionFactory().openSession();
			SQLQuery query = session.createSQLQuery(sql);
			query.addScalar("id", Hibernate.LONG).addScalar(
					"officeNo", Hibernate.STRING);
			query.setResultTransformer(Transformers
					.aliasToBean(ManagerOfficeInfo.class));
			
			List<ManagerOfficeInfo> list = query.list();
			session.close();

===========另一种实现方式的分割线=========

private static DataSource dataSource = null;

/**
     * 获取系列生成器数据库连接,无论哪种用户都取同一个连接,确保hibernate缓存正确；.
     *
     * @return the connection
     * @throws SQLException the sQL exception
     */
    private static Connection getConnection() throws SQLException {
        if (dataSource == null) {
            dataSource = (DataSource) ApplicationContextKeeper.getAppCtx().getBean("dataSource");
        }
        return dataSource.getConnection();
    }

 /**
     * Gather over bill.
     *
     * @param dataList the data list
     * @param flowMonth the flow month
     * @throws Exception the exception
     */
    private void gatherOverBill(List<IbeFee> dataList, String flowMonth) throws Exception {
        // PreparedStatement pstm = null;
        PreparedStatement pstm2 = null;
        // ResultSet rs = null;
        Connection cn = null;
        int size = dataList.size();
        try {
            cn = getConnection();
            /*
             * StringBuffer sb = new StringBuffer(); sb.append(" select t.branch , t.agent , t.bill_month , t.total_charge , t.id from asy_ibe_fee t
             * where t.matching = '1'"); sb.append(" and t.bill_month = '" + flowMonth +"'");
             */


            StringBuffer sb2 = new StringBuffer();
            sb2.append(" insert into asy_ibe_bill (ID,BRANCH,AGENT,BILL_MONTH,BILL_AMOUNT,AUDITING,");
            sb2.append(" REFUNDED,CALCULATOR_NO,REMARKS,MAIN_ID)");
            sb2.append(" values (asy_ibe_fee_id.nextval ,? ,? ,? ,? ,'0' ,'0' ,'' ,'' , ? )");


            pstm2 = cn.prepareStatement(sb2.toString());


            // pstm = cn.prepareStatement(sb.toString());
            // rs = pstm.executeQuery();


            for (int i = 0; i < size; i++) {
                IbeFee ibe = dataList.get(i);
                if (!"1".equals(ibe.getMATCHING())) {
                    continue;
                }
                pstm2.setString(1, ibe.getBranch().getId().toString());
                pstm2.setString(2, ibe.getDepartment().getId().toString());
                pstm2.setString(3, ibe.getBILL_MONTH());
                pstm2.setString(4, ibe.getTOTAL_CHARGE());
                pstm2.setString(5, ibe.getId().toString());
                pstm2.addBatch();
                if (i > 0 && i % 1000 == 0) {
                    pstm2.executeBatch();
                    pstm2.clearBatch();
                }
            }
            /*
             * while(rs.next()){ pstm2.setString(1, rs.getString("branch")); pstm2.setString(2, rs.getString("agent")); pstm2.setString(3,
             * rs.getString("bill_month")); pstm2.setString(4, rs.getString("total_charge")); pstm2.setString(5, rs.getString("id"));
             * pstm2.addBatch();
             * 
             * i++; if(i>0 && i%1000==0){ pstm2.executeBatch(); pstm2.clearBatch(); } }
             */
            pstm2.executeBatch();
            // cn.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception(ex);
        } finally {
            if (pstm2 != null) {
                pstm2.close();
            }
            // if(pstm!=null) pstm.close();
            if (cn != null) {
                cn.close();
            }
        }
    }


    /**
     * Delete.
     *
     * @param flowMonth the flow month
     * @throws Exception the exception
     */
    private void delete(String flowMonth) throws Exception {
        PreparedStatement pstm = null;
        Connection cn = null;
        try {
            cn = getConnection();
            String sql = "delete from ASY_IBE_FEE where BILL_MONTH=?";
            pstm = cn.prepareStatement(sql);
            pstm.setString(1, flowMonth);
            pstm.execute();
            pstm.close();


            sql = "delete from ASY_IBE_BILL where BILL_MONTH=?";
            pstm = cn.prepareStatement(sql);
            pstm.setString(1, flowMonth);
            pstm.execute();
            // cn.commit();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception(ex);
        } finally {
            if (cn != null) {
                cn.close();
            }
            if (pstm != null) {
                pstm.close();
            }
        }
    }


    /**
     * 取得C系统的所有office.
     *
     * @return the map
     * @throws Exception the exception
     */
    protected Map<String, OfficeInfo> loadOfficeInfos() throws Exception {
        Map<String, OfficeInfo> map = new HashMap<String, OfficeInfo>();


        Connection cn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            StringBuffer sb = new StringBuffer();
            sb.append(" select id,office_status,office_system,office_no,department_id,branch_id ");
            sb.append(" from ASY_OFFICE_INFO where office_status='1' and office_system='1' and (isexists <> '1' or isexists is null) ");


            cn = getConnection();


            pstm = cn.prepareStatement(sb.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                String officeNo = StringUtils.trimToEmpty(rs.getString("office_no")).toUpperCase();


                OfficeInfo o = new OfficeInfo();
                o.setBranchId(rs.getLong("branch_id"));
                o.setDepartmentId(rs.getLong("department_id"));
                o.setOfficeNo(officeNo);
                o.setOfficeSystem(StringUtils.trimToEmpty(rs.getString("office_system")));


                if (StringUtils.isNotEmpty(officeNo) && !map.containsKey(officeNo)) {
                    map.put(officeNo, o);
                }
            }


        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception(ex);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstm != null) {
                pstm.close();
            }
            if (cn != null) {
                cn.close();
            }
        }


        return map;
    }