Query.setDate和Query.setTimestamp区别，附赠log4jdbc的bug

前提

应用中使用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,具体如下：

1. //begin 2017/12/06 00:00:00.000
2. //end 2017/12/06 23:59:59
3. publicList<FeedbackInfoBean> getFeedbackByFyid(finalInteger fyid,finalDate begin,finalDate end){
4. Object r = dzjzFoundationDao.getHibernateTemplate().execute(newHibernateCallback(){
5. @Override
6. publicObject doInHibernate(Session session)throwsHibernateException,SQLException{
7. String hql =newString(
8. "SELECT new com.thunisoft.fy.dzjz.httpapi.prescanner.timer.bean.FeedbackInfoBean ( clfx.CBhDsf as cBhDsf, fd.NAjbs as nAjbs, "+
9. "fd.NJbfy as nJbfy, fd.NAjlb as nAjlb, jz.CMc as cAh, clfx.dtScanTimesstamp as dScanTimesstamp, clfx.CSerialNum as cSerialNum )"+
10. " FROM TYwgyDzjzPrescannerClfx clfx, TYwgyDzjzPrescannerInfoFeedback fd, TYwgyDzjzJz jz"+
11. " WHERE fd.CBhClfx = clfx.CBh AND clfx.CBhAj = jz.CBhAj AND clfx.NState = 1 AND clfx.NJbfy = :JBFY"+
12. " AND clfx.dtScanTimesstamp > (:start) AND clfx.dtScanTimesstamp < (:end)"
13. );
14. Query q = session.createQuery(hql);
15. try{
16. q.setParameter("JBFY", fyid);
17. q.setDate("start", begin);
18. q.setDate("end", end);
19. }catch(ParseException e){
20. thrownewSQLException(String.format("%s or %s is not in a valid date format", begin, end), e);
21. }
22. return q.list();
23. }
24. });
25. return(List<FeedbackInfoBean>) r;
26. }
27. }

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

1. 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
2. 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
3. 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
4. and tywgydzjzp0_.N_JBFY=2400and tywgydzjzp0_.DT_SCANTIMESSTAMP>'12/06/2017 00:00:00.000'and tywgydzjzp0_.DT_SCANTIMESSTAMP<'12/06/2017 23:59:59.000'

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

猜想

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

验证

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

1. /**
2. * AbstractQueryImpl.java
3. */
4. publicQuery setDate(String name,Date date){
5. setParameter(name, date,Hibernate.DATE);
6. returnthis;
7. }
9. /**
10. * Hibernate.java
11. * Hibernate <tt>date</tt> type.
12. */
13. publicstaticfinalNullableType DATE =newDateType();

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

1. /**
2. * 至于啥时候调用，就是hibernate bind的时候会用，具体得自己see一眼
3. * bind
4. * 注意：此处的sqlDate类型是java.sql.Date
5. */
6. publicvoid set(PreparedStatement st,Object value,int index)throwsSQLException{
7. Date sqlDate;
8. if( value instanceofDate){
9. sqlDate =(Date) value;
10. }
11. else{
12. sqlDate =newDate(((java.util.Date) value ).getTime());
13. }
14. st.setDate(index, sqlDate);
15. }

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

1. if(x instanceofDate){
2. x =newDateTime((Date) x);
3. }elseif(x instanceofTime){
4. x =newDateTime((Time) x);
5. }elseif(x instanceofTimestamp){
6. x =newDateTime((Timestamp) x);
7. }

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

1. DateTime(Date d)throwsSQLException{
2. dateValue = d;
3. GregorianCalendar cal =newGregorianCalendar();
4. cal.setTime(d);
6. if(cal.get(Calendar.ERA)!=GregorianCalendar.AD)
7. thrownewSQLException(Messages.get("error.datetime.range.era"),"22007");
9. year =(short)cal.get(Calendar.YEAR);
10. month =(short)(cal.get(Calendar.MONTH)+1);
11. day =(short)cal.get(Calendar.DAY_OF_MONTH);
12. //请关注到这里，time是没被使用的，时 分 秒都是0
13. hour =0;
14. minute =0;
15. second =0;
16. millis =0;
17. packDate();
18. time = TIME_NOT_USED;
19. unpacked =true;
20. }
21. //but timestamp with 时分秒
22. DateTime(Timestamp ts)throwsSQLException{
23. tsValue = ts;
24. GregorianCalendar cal =newGregorianCalendar();
25. cal.setTime(ts);
27. if(cal.get(Calendar.ERA)!=GregorianCalendar.AD)
28. thrownewSQLException(Messages.get("error.datetime.range.era"),"22007");
30. if(!Driver.JDBC3){
31. // Not Running under 1.4 so need to add milliseconds
32. cal.set(Calendar.MILLISECOND,
33. ts.getNanos()/1000000);
34. }
35. year =(short)cal.get(Calendar.YEAR);
36. month =(short)(cal.get(Calendar.MONTH)+1);
37. day =(short)cal.get(Calendar.DAY_OF_MONTH);
38. hour =(short)cal.get(Calendar.HOUR_OF_DAY);
39. minute =(short)cal.get(Calendar.MINUTE);
40. second =(short)cal.get(Calendar.SECOND);
41. millis =(short)cal.get(Calendar.MILLISECOND);
42. packDate();
43. packTime();
44. unpacked =true;
45. }

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

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

1. /**
2. * TimeStampType类
3. * Hibernate <tt>timestamp</tt> type.
4. */
5. publicvoid set(PreparedStatement st,Object value,int index)throwsSQLException{
6. Timestamp ts;
7. if(value instanceofTimestamp){
8. ts =(Timestamp) value;
9. }
10. else{
11. ts =newTimestamp(((java.util.Date) value ).getTime());
12. }
13. st.setTimestamp(index, ts);
14. }
16. /**
17. * AbstractQueryImpl类
18. * Hibernate <tt>timestamp</tt> type.
19. */
20. publicQuery setTimestamp(int position,Date date){
21. setParameter(position, date,Hibernate.TIMESTAMP);
22. returnthis;
23. }
25. /**
26. * Hibernate类
27. * Hibernate <tt>timestamp</tt> type.
28. */
29. publicstaticfinalNullableType TIMESTAMP =newTimestampType();

so:

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

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

注意: 版本是jtds-1.2.7，没有验证其他版本是否存在这个差异，不过想想也觉得应该有这个差异,这个差异是正常的:)

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

1. //PreparedStatementSpy类 仅贴关键代码
2. protectedvoid argTraceSet(int i,String typeHelper,Object arg){
3. // 替换的预编译的参数值
4. synchronized(argTrace)
5. {
6. try
7. {
8. arg = argTrace.get(argIdx);
9. }
10. catch(IndexOutOfBoundsException e)
11. {
12. arg ="?";
13. }
14. }
15. if(arg ==null)
16. {
17. arg ="?";
18. }
20. argIdx++;
22. dumpSql.append(sql.substring(lastPos,Qpos));// dump segment of sql up to question mark.
23. lastPos =Qpos+1;
24. Qpos= sql.indexOf('?', lastPos);
25. dumpSql.append(arg);
26. }
28. //替换的对象
29. publicvoid setDate(int parameterIndex,Date x)throwsSQLException
30. {
31. String methodCall ="setDate("+ parameterIndex +", "+ x +")";
32. argTraceSet(parameterIndex,"(Date)", x);
33. try
34. {
35. realPreparedStatement.setDate(parameterIndex, x);
36. }
37. catch(SQLException s)
38. {
39. reportException(methodCall, s);
40. throw s;
41. }
42. reportReturn(methodCall);
43. }
45. protectedvoid argTraceSet(int i,String typeHelper,Object arg)
46. {
47. String tracedArg;
48. try
49. {
50. //注意这行是关键
51. tracedArg = rdbmsSpecifics.formatParameterObject(arg);
52. }
53. catch(Throwable t)
54. {
55. // rdbmsSpecifics should NEVER EVER throw an exception!!
56. // but just in case it does, we trap it.
57. log.debug("rdbmsSpecifics threw an exception while trying to format a "+
58. "parameter object ["+ arg +"] this is very bad!!! ("+
59. t.getMessage()+")");
61. // backup - so that at least we won't harm the application using us
62. tracedArg = arg==null?"null":arg.toString();
63. }
65. i--;// make the index 0 based
66. synchronized(argTrace)
67. {
68. // if an object is being inserted out of sequence, fill up missing values with null...
69. while(i >= argTrace.size())
70. {
71. argTrace.add(argTrace.size(),null);
72. }
73. if(!showTypeHelp)
74. {
75. argTrace.set(i, tracedArg);
76. }
77. else
78. {
79. argTrace.set(i, typeHelper + tracedArg);
80. }
81. }
82. }
84. //RdbmsSpecifics类
85. protectedstaticfinalString dateFormat ="MM/dd/yyyy HH:mm:ss.SSS";
86. publicString formatParameterObject(Object object)
87. {
88. if(object ==null)
89. {
90. return"NULL";
91. }
93. if(object instanceofString)
94. {
95. return"'"+ escapeString((String)object)+"'";
96. }
97. elseif(object instanceofDate)
98. {
100. return"'"+newSimpleDateFormat(dateFormat).format(object)+"'";
101. }
102. elseif(object instanceofBoolean)
103. {
104. returnProperties.isDumpBooleanAsTrueFalse()?
105. ((Boolean)object).booleanValue()?"true":"false"
106. :((Boolean)object).booleanValue()?"1":"0";
107. }
108. else
109. {
110. return object.toString();
111. }
112. }

结论:因为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开源组件,待续