前提

应用中使用hibernate+jtds(1.2.7)+log4jdbc-log4j2-jdbc3-1.16

前景

同事使用hibernate的hql查询当天的业务数据，其中 begin=2017/12/06 00:00:00, end=2017/12/06 23:59:59,具体如下：

//begin 2017/12/06 00:00:00.000
//end 2017/12/06 23:59:59
    public List<FeedbackInfoBean> getFeedbackByFyid(final Integer fyid, final Date begin, final Date end) {
        Object r = dzjzFoundationDao.getHibernateTemplate().execute(new HibernateCallback() {
            @Override
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                String hql = new String(
                    "SELECT new com.thunisoft.fy.dzjz.httpapi.prescanner.timer.bean.FeedbackInfoBean ( clfx.CBhDsf as cBhDsf, fd.NAjbs as nAjbs, " +
                            "fd.NJbfy as nJbfy, fd.NAjlb as nAjlb, jz.CMc as cAh, clfx.dtScanTimesstamp as dScanTimesstamp, clfx.CSerialNum as cSerialNum )" +
                                " FROM TYwgyDzjzPrescannerClfx clfx, TYwgyDzjzPrescannerInfoFeedback fd, TYwgyDzjzJz jz" +
                                    " WHERE fd.CBhClfx = clfx.CBh AND clfx.CBhAj = jz.CBhAj AND clfx.NState = 1 AND clfx.NJbfy = :JBFY"+
                                        " AND clfx.dtScanTimesstamp > (:start) AND clfx.dtScanTimesstamp < (:end)"
                        );
                Query q = session.createQuery(hql);
                try {
                    q.setParameter("JBFY", fyid);
                    q.setDate("start", begin);
                    q.setDate("end", end);
                } catch (ParseException e) {
                    throw new SQLException(String.format("%s or %s is not in a valid date format", begin, end), e);
                }
                return q.list();
            }
        });
        return (List<FeedbackInfoBean>) r;
    }
}

但是，程序就是没有查到数据，jdbc日志输出sql如下：

select tywgydzjzp0_.DT_SCANTIMESSTAMP,tywgydzjzp0_.C_BH_DSF as col_0_0_, tywgydzjzp1_.N_AJBS as col_1_0_, tywgydzjzp1_.N_JBFY as col_2_0_, tywgydzjzp1_.N_AJLB as col_3_0_, tywgydzjzj2_.C_MC 
as col_4_0_, tywgydzjzp0_.DT_SCANTIMESSTAMP as col_5_0_, tywgydzjzp0_.C_SERIALNUM as col_6_0_ from YWST.dbo.T_YWGY_DZJZ_PRESCANNER_CLFX tywgydzjzp0_, YWST.dbo.T_YWGY_DZJZ_PRESCANNER_FEEDBCK 
tywgydzjzp1_, YWST.dbo.T_YWGY_DZJZ_JZ tywgydzjzj2_ where tywgydzjzp1_.C_BH_CLFX=tywgydzjzp0_.C_BH and tywgydzjzp0_.C_BH_AJ=tywgydzjzj2_.C_BH_AJ and tywgydzjzp0_.N_STATE=1 
and tywgydzjzp0_.N_JBFY=2400 and tywgydzjzp0_.DT_SCANTIMESSTAMP>'12/06/2017 00:00:00.000' and tywgydzjzp0_.DT_SCANTIMESSTAMP<'12/06/2017 23:59:59.000'

通过sql查询结果是有数据的：

image.png

猜想

为此，猜测，内存hibernate或jtds解析参数时，end对应的值为 2017/12/06 00:00:00,即与start对应的值相同，可能出现查询结果为空。

验证

AbstractQueryImpl.setDate 方法具体实现如下：

/**
 * AbstractQueryImpl.java
 */
public Query setDate(String name, Date date) {
        setParameter(name, date, Hibernate.DATE);
        return this;
    }
    
/**
 * Hibernate.java
 * Hibernate <tt>date</tt> type.
 */
public static final NullableType DATE = new DateType();

DateType中值替换参数时调用的set方法代码如下：

/**
 * 至于啥时候调用，就是hibernate bind的时候会用，具体得自己see一眼 
 *  bind
 * 注意：此处的sqlDate类型是java.sql.Date
 */
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);
    }

而真正使用的是jtds的JtdsPreparedStatement.setParameter(中间跳过JtdsPreparedStatement.setDate和log4jdbc.PreparedStatementSpy.setDate)有关键代码如下:

if (x instanceof Date) {
            x = new DateTime((Date) x);
        } else if (x instanceof Time) {
            x = new DateTime((Time) x);
        } else if (x instanceof Timestamp) {
            x = new DateTime((Timestamp) x);
        }

所以，最后Date或Timestamp对象的还是会转换成jtds的DateTime对象。对于，Date类型，初始化时就没有初始化time部分数据。

DateTime(Date d) throws SQLException {
        dateValue = d;
        GregorianCalendar cal = new GregorianCalendar();
        cal.setTime(d);

        if (cal.get(Calendar.ERA) != GregorianCalendar.AD)
            throw new SQLException(Messages.get("error.datetime.range.era"), "22007");

        year   = (short)cal.get(Calendar.YEAR);
        month  = (short)(cal.get(Calendar.MONTH) + 1);
        day    = (short)cal.get(Calendar.DAY_OF_MONTH);
        //请关注到这里，time是没被使用的，时 分 秒都是0
        hour   = 0;
        minute = 0;
        second = 0;
        millis = 0;
        packDate();
        time = TIME_NOT_USED;
        unpacked  = true;
    }
    //but timestamp with 时分秒
    DateTime(Timestamp ts) throws SQLException {
        tsValue = ts;
        GregorianCalendar cal = new GregorianCalendar();
        cal.setTime(ts);

        if (cal.get(Calendar.ERA) != GregorianCalendar.AD)
            throw new SQLException(Messages.get("error.datetime.range.era"), "22007");

        if (!Driver.JDBC3) {
            // Not Running under 1.4 so need to add milliseconds
            cal.set(Calendar.MILLISECOND,
                    ts.getNanos() / 1000000);
        }
        year   = (short)cal.get(Calendar.YEAR);
        month  = (short)(cal.get(Calendar.MONTH) + 1);
        day    = (short)cal.get(Calendar.DAY_OF_MONTH);
        hour   = (short)cal.get(Calendar.HOUR_OF_DAY);
        minute = (short)cal.get(Calendar.MINUTE);
        second = (short)cal.get(Calendar.SECOND);
        millis = (short)cal.get(Calendar.MILLISECOND);
        packDate();
        packTime();
        unpacked = true;
    }
    

而只有原本是Timestamp类型的才会使得发送到数据库的查询中包含time部分。

hibernate有一个TimeStampType类。(并附上AbstractQueryImpl.setTimestamp代码)

    /**
     * TimeStampType类
     * Hibernate <tt>timestamp</tt> type.
     */
    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);
    }
    
    /**
     * AbstractQueryImpl类
     * Hibernate <tt>timestamp</tt> type.
     */
    public Query setTimestamp(int position, Date date) {
        setParameter(position, date, Hibernate.TIMESTAMP);
        return this;
    }
    
    /**
     * Hibernate类
     * Hibernate <tt>timestamp</tt> type.
     */
    public static final NullableType TIMESTAMP = new TimestampType();

so:

如果仅精确到日，注意请使用Query.setDate,就算给的Date有时分秒的值

如果要精确到时分秒的，注意请使用Query.setTimestamp

<font size="5" color="red">注意: 版本是jtds-1.2.7，没有验证其他版本是否存在这个差异，不过想想也觉得应该有这个差异,这个差异是正常的:)</font>

有同学会注意到一个问题：为啥jdbc sql日志却输出的是时间格式？请看如下摘取代码:

//PreparedStatementSpy类 仅贴关键代码
 protected void argTraceSet(int i, String typeHelper, Object arg){
      // 替换的预编译的参数值
      synchronized (argTrace)
      {
        try
        {
          arg = argTrace.get(argIdx);
        }
        catch (IndexOutOfBoundsException e)
        {
          arg = "?";
        }
      }
      if (arg == null)
      {
        arg = "?";
      }

      argIdx++;

      dumpSql.append(sql.substring(lastPos, Qpos));  // dump segment of sql up to question mark.
      lastPos = Qpos + 1;
      Qpos = sql.indexOf('?', lastPos);
      dumpSql.append(arg);
    }
    
    //替换的对象
    public void setDate(int parameterIndex, Date x) throws SQLException
  {
    String methodCall = "setDate(" + parameterIndex + ", " + x + ")";
    argTraceSet(parameterIndex, "(Date)", x);
    try
    {
      realPreparedStatement.setDate(parameterIndex, x);
    }
    catch (SQLException s)
    {
      reportException(methodCall, s);
      throw s;
    }
    reportReturn(methodCall);
  }
  
  protected void argTraceSet(int i, String typeHelper, Object arg)
  {
    String tracedArg;
    try
    {
    //注意这行是关键
      tracedArg = rdbmsSpecifics.formatParameterObject(arg);
    }
    catch (Throwable t)
    {
      // rdbmsSpecifics should NEVER EVER throw an exception!!
      // but just in case it does, we trap it.
      log.debug("rdbmsSpecifics threw an exception while trying to format a " +
        "parameter object [" + arg + "] this is very bad!!! (" +
        t.getMessage() + ")");

      // backup - so that at least we won't harm the application using us
      tracedArg = arg==null?"null":arg.toString();
    }

    i--;  // make the index 0 based
    synchronized (argTrace)
    {
      // if an object is being inserted out of sequence, fill up missing values with null...
      while (i >= argTrace.size())
      {
        argTrace.add(argTrace.size(), null);
      }
      if (!showTypeHelp)
      {
        argTrace.set(i, tracedArg);
      }
      else
      {
        argTrace.set(i, typeHelper + tracedArg);
      }
    }
  }
  
  //RdbmsSpecifics类
  protected static final String dateFormat = "MM/dd/yyyy HH:mm:ss.SSS";
    public String formatParameterObject(Object object)
    {
        if (object == null)
        {
            return "NULL";
        }

        if (object instanceof String)
        {
            return "'" + escapeString((String)object) + "'";
        }
        else if (object instanceof Date)
        {
        
            return "'" + new SimpleDateFormat(dateFormat).format(object) + "'";
        }
        else if (object instanceof Boolean)
        {
            return Properties.isDumpBooleanAsTrueFalse()?
                    ((Boolean)object).booleanValue()?"true":"false"
                        :((Boolean)object).booleanValue()?"1":"0";
        }
        else
        {
            return object.toString();
        }
    }
    

结论:因为log4jdbc认为参数对象如果是Date类型的都会按照格式:MM/dd/yyyy HH:mm:ss.SSS 做format，不区分Date和Timestamp，导致拼出的sql带有时分秒精度

如何解决log4jdbc打出的sql在Date处理和真实sql不一致

方案一

找到了log4jdbc-log4j2的github，他们目前最新版本也是1.16。所以，在他们的issues上提了问题，看看他们给不给答复吧! issues link

方案二

逛逛新的log4jdbc开源组件,待续