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;    }