HIBERNATE时间检索问题

因为公司要保持上一个员工构建的系统结构--struct1+hibernate,让我接手这个项目,我第一次用起了HIBERNATE.算学学用

项目是一个日志收集与展现系统.每天要收集与展现的日志一张物理表就将近30W,每个元组平均900字节.用户要求保留3个月的数据

在线,差不多也就2000W吧. 数据库为ORACLE,数据的检索基本上要用到时间条件,我在时间上建了索引,并设为不可为空(防止全表扫描).

 600W数据时我采用分区表了.在PL/SQLD执行，效果还不错．但连接到系统上,统计记录数这一步居然要几分钟，有时10几分钟．

因为我采用数据库分页，第一步就是统计符合条件的记录数．我感到不对了，很多人都在用HIBERNATE有这么差吗？

起初的代码是这样的(只粘了统计记录数这一步)：

public int getLogsCount(StmmLog log) throws SyslogException {
        try {
            Criteria criteria = getSession().createCriteria(StmmLog.class);
            attachRestrictions(criteria, log);

            criteria.setProjection(Projections.rowCount());
            return ((Integer) criteria.uniqueResult()).intValue();

        } catch (RuntimeException re) {

            throw new SyslogException(re, this.getClass(), "银企日志条件查询获得纪录数异常");
        } finally {
            getSession().close();
        }
    }
private void attachRestrictions(Criteria criteria, StmmLog log) {

        if (StringUtils.isNotBlank(log.getFromHost())) {
            criteria.add(Restrictions.like("fromHost", log.getFromHost(),
                    MatchMode.ANYWHERE));
        }

        if (StringUtils.isNotBlank(log.getFromHostIp())) {
            criteria.add(Restrictions.eq("fromHostIp", log.getFromHostIp()));
        }

        if (null != log.getEventReportedTime()) {
            criteria.add(Restrictions.ge("eventReportedTime", log
                    .getEventReportedTime()));
        }
        if (null != log.getEventReportedEndTime()) {
            criteria.add(Restrictions.le("eventReportedTime", new Date(log
                    .getEventReportedEndTime().getTime()
                    + 1 * 24 * 3600 * 1000 - 1)));
        }
    }

 因为Criteria拼绑定SQL比较方便. 所以起初采用他.现在看别人都用HSQL,我想也别用Criteria 了，可能他的性能真的不行吧．

换： 

public int getLogsCount(OracleAlarmLog log) throws SyslogException {
        try {
            // Criteria criteria = getSession().createCriteria(
            // OracleAlarmLog.class);
            // attachRestrictions(criteria, log);
            //
            // criteria.setProjection(Projections.count("id"));
            StringBuffer sb = new StringBuffer(
                    "select count(log.id) from OracleAlarmLog log where 1=1 ");
            List<SQLParameter> lst = addRestrictions(sb, log);

            Object[] values = new Object[lst.size()];
            Type[] types = new Type[lst.size()];
            for (int i = 0; i < lst.size(); i++) {
                values[i] = lst.get(i).getValue();
                types[i] = lst.get(i).getType();
            }
            Query query = getSession().createSQLQuery(sb.toString());
            query.setParameters(values, types);
            BigDecimal ret = (BigDecimal) query.uniqueResult();
            return ret.intValue();
            // return ((Integer) criteria.uniqueResult()).intValue();

        } catch (RuntimeException re) {

            throw new SyslogException(re, this.getClass(), "获取ORACLE日志记录数异常");
        } finally {
            getSession().close();
        }
    }
    private List<SQLParameter> addRestrictions(StringBuffer sb, StmmLog log) {

        List<SQLParameter> lst = new ArrayList<SQLParameter>();

        if (StringUtils.isNotBlank(log.getFromHost())) {
            sb.append(" AND fromHost like ?");
            lst.add(new SQLParameter("%" + log.getFromHost() + "%",
                    Hibernate.STRING));
        }
        if (StringUtils.isNotBlank(log.getFromHostIp())) {
            sb.append(" AND fromHostIp = ?");
            lst.add(new SQLParameter(log.getFromHostIp(), Hibernate.STRING));
        }

        if (null != log.getEventReportedTime()) {
            sb.append(" AND eventReportedTime >= ?");
            lst
                    .add(new SQLParameter(log.getEventReportedTime(),
                            Hibernate.DATE));
        }
        if (null != log.getEventReportedEndTime()) {
            sb.append(" AND eventReportedTime <= ?");
            lst.add(new SQLParameter(new Date(log.getEventReportedEndTime()
                    .getTime()
                    + 1 * 24 * 3600 * 1000 - 1), Hibernate.DATE));
        }
        return lst;
    }

 这一下终于有了起色，快了很多,大功告成．然而问题又出现了．终计一天的数据时，起止时间都是'2009-10-15' ,结果居然为０，

逻辑错误肯定不允许.但库里明明有数据呀(ORACLE的DATE类型)．把结束时间换成'2009-10-16'结果出来了．哦,到可能是

 Hibernate.DATE只传了年月日部分．打开Hibernate源码： 

public class DateType extends MutableType implements IdentifierType, LiteralType {

    private static final String DATE_FORMAT = "dd MMMM yyyy";

    public Object get(ResultSet rs, String name) throws SQLException {
        return rs.getDate(name);
    }

    public Class getReturnedClass() {
        return java.util.Date.class;
    }

    public void set(PreparedStatement st, Object value, int index) throws SQLException {

        Date sqlDate;
        if ( value instanceof Date) {
            sqlDate = (Date) value;
        }
        else {
            sqlDate = new Date( ( (java.util.Date) value ).getTime() );
        }
        st.setDate(index, sqlDate);
    }

    public int sqlType() {
        return Types.DATE;
    }

    public boolean isEqual(Object x, Object y) {

        if (x==y) return true;
        if (x==null || y==null) return false;

        java.util.Date xdate = (java.util.Date) x;
        java.util.Date ydate = (java.util.Date) y;
        
        if ( xdate.getTime()==ydate.getTime() ) return true;
        
        Calendar calendar1 = java.util.Calendar.getInstance();
        Calendar calendar2 = java.util.Calendar.getInstance();
        calendar1.setTime( xdate );
        calendar2.setTime( ydate );

        return Hibernate.CALENDAR_DATE.isEqual(calendar1, calendar2);
    }

    public int getHashCode(Object x, EntityMode entityMode) {
        Calendar calendar = java.util.Calendar.getInstance();
        calendar.setTime( (java.util.Date) x );
        return Hibernate.CALENDAR_DATE.getHashCode(calendar, entityMode);
    }
    
    public String getName() { return "date"; }

    public String toString(Object val) {
        return new SimpleDateFormat(DATE_FORMAT).format( (java.util.Date) val );
    }

}

不出所料'dd MMMM yyyy'，没有时分秒.果然如此．怎么办呢,这时却有了意外收获，不好意思，时间紧文档看得少．

发现还有一个在Timestamp类型在旁边.CODE:

public static final NullableType TIMESTAMP = new TimestampType();

public class TimestampType extends MutableType implements VersionType, LiteralType {

    private static final String TIMESTAMP_FORMAT = "yyyy-MM-dd HH:mm:ss";

    public Object get(ResultSet rs, String name) throws SQLException {
        return rs.getTimestamp(name);
    }
    
    public Class getReturnedClass() {
        return java.util.Date.class;
    }
    
    public void set(PreparedStatement st, Object value, int index) throws SQLException {
        Timestamp ts;
        if (value instanceof Timestamp) {
            ts = (Timestamp) value;
        }
        else {
            ts = new Timestamp( ( (java.util.Date) value ).getTime() );
        }
        st.setTimestamp(index, ts);
    }

}

这不正是我要的吗．呵呵,换成 Hibernate.TIMESTAMP吧：

private List<SQLParameter> addRestrictions(StringBuffer sb, StmmLog log) {

        List<SQLParameter> lst = new ArrayList<SQLParameter>();

                if (StringUtils.isNotBlank(log.getFromHost())) {
            sb.append(" AND fromHost like ?");
            lst.add(new SQLParameter("%" + log.getFromHost() + "%",
                    Hibernate.STRING));
        }
        if (StringUtils.isNotBlank(log.getFromHostIp())) {
            sb.append(" AND fromHostIp = ?");
            lst.add(new SQLParameter(log.getFromHostIp(), Hibernate.STRING));
        }

        if (null != log.getEventReportedTime()) {
            sb.append(" AND eventReportedTime >= ?");
            lst
                    .add(new SQLParameter(log.getEventReportedTime(),
                            Hibernate.TIMESTAMP));
        }
        if (null != log.getEventReportedEndTime()) {
            sb.append(" AND eventReportedTime <= ?");
            lst.add(new SQLParameter(new Date(log.getEventReportedEndTime()
                    .getTime()
                    + 1 * 24 * 3600 * 1000 - 1), Hibernate.TIMESTAMP));
        }
        return lst;
    }

噩梦并没有结束,这时性能又降到刚开始的状态．为什么两个都不可用呢，查文档，也没找到可用信息．到底问题在哪儿.

通过ORACLE查看绑字信息：

select t.HASH_VALUE,
       t.datatype,
       t.NAME,
       t.LAST_CAPTURED,
       t.WAS_CAPTURED,
       t.VALUE_STRING,
       t.VALUE_ANYDATA
  from v$sql_bind_capture t
  where sql_id = (select sql_id--,bind_data
                   from v$sqlarea
                  where sql_text like '%log.id%')

从输出发现用Hibernate.Date时，绑字是Date类型，但时分秒都为０，明显不对．用Hibernate.TIMESTAMP时绑定的是

TIMESTAMP类型．但数据库类型是DATE,据我对ORACLE的理解，肯定是该字段的类型DATE发生了隐式类型转换，导致索引无效了．

验证(eventreportedtime 是DATE类型)：

SQL> select * from stmmlog where eventreportedtime = cast(sysdate as timestamp)
  2  /

未选定行

已用时间:  00: 06: 40.53

执行计划
----------------------------------------------------------
Plan hash value: 2651278941

--------------------------------------------------------------------------------

---------------

| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
Pstart| Pstop |

--------------------------------------------------------------------------------

---------------

|   0 | SELECT STATEMENT    |         | 66490 |    55M|   321K  (1)| 01:04:18 |
      |       |

|   1 |  PARTITION RANGE ALL|         | 66490 |    55M|   321K  (1)| 01:04:18 |
    1 |     9 |

|*  2 |   TABLE ACCESS FULL | STMMLOG | 66490 |    55M|   321K  (1)| 01:04:18 |
    1 |     9 |

--------------------------------------------------------------------------------

---------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(INTERNAL_FUNCTION("EVENTREPORTEDTIME")=CAST(SYSDATE@! AS timestamp

))

 

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1883117  consistent gets
    1866294  physical reads
        116  redo size
       1482  bytes sent via SQL*Net to client
        389  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

果真如此，PARTITION RANGE ALL,分区都是全扫描,无办法了，改TO_DATE吧，我最初放弃的（因为与数据库偶合），却是我的最终选择．

呵呵，有恋爱的感觉？

private List<SQLParameter> addRestrictions(StringBuffer sb, StmmLog log) {
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
        List<SQLParameter> lst = new ArrayList<SQLParameter>();

                if (StringUtils.isNotBlank(log.getFromHost())) {
            sb.append(" AND fromHost like ?");
            lst.add(new SQLParameter("%" + log.getFromHost() + "%",
                    Hibernate.STRING));
        }
        if (StringUtils.isNotBlank(log.getFromHostIp())) {
            sb.append(" AND fromHostIp = ?");
            lst.add(new SQLParameter(log.getFromHostIp(), Hibernate.STRING));
        }

        if (null != log.getEventReportedTime()) {
            sb
                    .append(" AND eventReportedTime >= to_date(?,'yyyy-MM-dd HH24:mi:ss')");
            lst.add(new SQLParameter(sf.format(log.getEventReportedTime())
                    + " 00:00:00", Hibernate.STRING));
        }
        if (null != log.getEventReportedEndTime()) {

            sb
                    .append(" AND eventReportedTime <= to_date(?,'yyyy-MM-dd HH24:mi:ss')");
            lst.add(new SQLParameter(sf.format(log.getEventReportedTime())
                    + " 23:59:59", Hibernate.STRING));
        }
}

到这里问题算解决了,但是突然有些不明,HIBERNATE为什么没有作这方面的处理呢,对于大数据量的查询来说,时间索引没了,

结果将是致命的.因为为HIBERNATE不熟,如果仁兄看过这篇文章之后，有更好的方法．请留言.谢谢.