HIBERNATE的时间检索问题

最新推荐文章于 2024-10-05 09:16:25 发布

anranran

最新推荐文章于 2024-10-05 09:16:25 发布

阅读量125

点赞数

文章标签： Hibernate Java Oracle SQL .net

因为公司要保持上一个员工构建的系统结构,让我接手这个项目,我第一次用起了HIBERNATE.项目是一个日志收集与展现系统.每天要收集与展现的日志一张表就将近30W.用户要求保留3个月的数据,差不多也就2000W吧. 数据的检索基本上要用到时间条件.
我在时间上建了索引,并设为不可为空. 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)));   
        }   
    }  

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

看别人都用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;   
    }  

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

}  

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

}

不出所料，果然如此．这时却有了意外收获，不好意思，时间紧文档看得少．

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

}  

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

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查看绑字信息：

Code
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类型．据我对ORACLE的理解，肯定是该字段的类型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

果真如此，无办法了，改TO_DATE吧，我最初放弃的（因为与数据库偶合），却是我的最终选择．呵呵，有恋爱的感觉？

Code

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

如果仁兄看过这篇文章之后，有更好的方法．请留言我会代表项目组成员感谢你．