前提
应用中使用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
publicList<FeedbackInfoBean> getFeedbackByFyid(finalInteger fyid,finalDate begin,finalDate end){
Object r = dzjzFoundationDao.getHibernateTemplate().execute(newHibernateCallback(){
@Override
publicObject doInHibernate(Session session)throwsHibernateException,SQLException{
String hql =newString(
"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){
thrownewSQLException(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=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 方法具体实现如下:
/**
* AbstractQueryImpl.java
*/
publicQuery setDate(String name,Date date){
setParameter(name, date,Hibernate.DATE);
returnthis;
}
/**
* Hibernate.java
* Hibernate <tt>date</tt> type.
*/
publicstaticfinalNullableType DATE =newDateType();
DateType中值替换参数时调用的set方法代码如下:
/**
* 至于啥时候调用,就是hibernate bind的时候会用,具体得自己see一眼
* bind
* 注意:此处的sqlDate类型是java.sql.Date
*/
publicvoid set(PreparedStatement st,Object value,int index)throwsSQLException{
Date sqlDate;
if( value instanceofDate){
sqlDate =(Date) value;
}
else{
sqlDate =newDate(((java.util.Date) value ).getTime());
}
st.setDate(index, sqlDate);
}
而真正使用的是jtds的JtdsPreparedStatement.setParameter(中间跳过JtdsPreparedStatement.setDate和log4jdbc.PreparedStatementSpy.setDate)有关键代码如下:
if(x instanceofDate){
x =newDateTime((Date) x);
}elseif(x instanceofTime){
x =newDateTime((Time) x);
}elseif(x instanceofTimestamp){
x =newDateTime((Timestamp) x);
}
所以,最后Date或Timestamp对象的还是会转换成jtds的DateTime对象。对于,Date类型,初始化时就没有初始化time部分数据。
DateTime(Date d)throwsSQLException{
dateValue = d;
GregorianCalendar cal =newGregorianCalendar();
cal.setTime(d);
if(cal.get(Calendar.ERA)!=GregorianCalendar.AD)
thrownewSQLException(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)throwsSQLException{
tsValue = ts;
GregorianCalendar cal =newGregorianCalendar();
cal.setTime(ts);
if(cal.get(Calendar.ERA)!=GregorianCalendar.AD)
thrownewSQLException(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.
*/
publicvoid set(PreparedStatement st,Object value,int index)throwsSQLException{
Timestamp ts;
if(value instanceofTimestamp){
ts =(Timestamp) value;
}
else{
ts =newTimestamp(((java.util.Date) value ).getTime());
}
st.setTimestamp(index, ts);
}
/**
* AbstractQueryImpl类
* Hibernate <tt>timestamp</tt> type.
*/
publicQuery setTimestamp(int position,Date date){
setParameter(position, date,Hibernate.TIMESTAMP);
returnthis;
}
/**
* Hibernate类
* Hibernate <tt>timestamp</tt> type.
*/
publicstaticfinalNullableType TIMESTAMP =newTimestampType();
so:
如果仅精确到日,注意请使用Query.setDate,就算给的Date有时分秒的值
如果要精确到时分秒的,注意请使用Query.setTimestamp
注意: 版本是jtds-1.2.7,没有验证其他版本是否存在这个差异,不过想想也觉得应该有这个差异,这个差异是正常的:)
有同学会注意到一个问题:为啥jdbc sql日志却输出的是时间格式?请看如下摘取代码:
//PreparedStatementSpy类 仅贴关键代码
protectedvoid 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);
}
//替换的对象
publicvoid setDate(int parameterIndex,Date x)throwsSQLException
{
String methodCall ="setDate("+ parameterIndex +", "+ x +")";
argTraceSet(parameterIndex,"(Date)", x);
try
{
realPreparedStatement.setDate(parameterIndex, x);
}
catch(SQLException s)
{
reportException(methodCall, s);
throw s;
}
reportReturn(methodCall);
}
protectedvoid 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类
protectedstaticfinalString dateFormat ="MM/dd/yyyy HH:mm:ss.SSS";
publicString formatParameterObject(Object object)
{
if(object ==null)
{
return"NULL";
}
if(object instanceofString)
{
return"'"+ escapeString((String)object)+"'";
}
elseif(object instanceofDate)
{
return"'"+newSimpleDateFormat(dateFormat).format(object)+"'";
}
elseif(object instanceofBoolean)
{
returnProperties.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开源组件,待续
相关推荐
javascript Date.setDate()方法按照本地时间设置月份的某一天,在指定的日期。 语法 Date.setDate( dayValue ) 下面是参数的详细信息: dayValue : 从1到31的整数,代表一个月份中某一天。 返回值: NA 例子: ...
但是涉及到每个月天数的判断,如果是2月份的话,还要涉及到闰年的判断,有些复杂,应用过程中总是出现问题,于是查了下资料,以在某个日期上加减天数来说,其实只要调用Date对象的setDate()函数就可以了,具体方法...
android 日历源码,用不同颜色显示本月下月和周末。
import java.sql.Timestamp; import java.util.ArrayList; import java.util.List; import com.wsy.model.Back; import com.wsy.model.BookInfo; import com.wsy.model.BookType; import com.wsy.model.Borrow; ...
tomcat中配jdbc就不用说了, <br> <br>4. Event.java 文件 <br>package events; <br>import java.util.Date; <br>public class Event { private Long id; <br> private String title;...
Display.getDisplay(this).setCurrent(form); // 显示表单form } // 重载抽象类MIDlet的抽象方法destroyApp() protected void destroyApp(boolean unconditional) { } // 重载抽象类MIDlet的抽象方法...
date.setDate(date.getDate()+number*7); return date; break; } case "d " : { date.setDate(date.getDate()+number); return date; break; } case "h " : { date.setHours(date.getHours()+number); ...
1、如何将java.util.Date...2、如果要插入到数据库并且相应的字段为Date类型那么可以用PreparedStatement.setDate(int ,java.sql.Date)方法其中的java.sql.Date可以用上面的方法得到也可以用数据库提供TO_DATE函数
指定开始和结束时间,范围该范围内的所有日期放入数组 Date.prototype.addDays = function(days) { var dat = new Date(this.valueOf()); dat.setDate(dat.getDate() + days); return dat; }; function getDates...
前些天做项目时遇到了个浏览器兼容问题,解决后记录一下,也将模式化的资料放上! 详细问题描述: 在火狐浏览器中弹出一个子窗口,...b.window.showModalDialog()方法用来创建一个显示HTML内容的模态对话框(IE 4+支持) c.win
*@list System.out.println(new DateUtil().setDate(1921,23,12).changeFirstDayOfMonth().toString()); *@list System.out.println(new DateUtil("2192-4--1 0:0:0","yyyy-MM-dd HH:mm:ss").changeLastDayOfMonth...
调用方法("MyCalendar.SetDate(this)"): ;" onkeypress="return false" id="txtBirthday" onfocus="MyCalendar.SetDate(this)" readonly type="text" size="10" name="Text1" runat="server">
不过它可以通过this.setDate()方法修改数据属性,日常需求是没什么问题的。学习小程序前一定仔细阅读文档啊。 https://mp.weixin.qq.com/debug/wxadoc/dev/ 不多说,小程序还在陆续开发更多的功能,最后希望越来越...
Character Set Section 2.2. Case Sensitivity Section 2.3. Whitespace and Line Breaks Section 2.4. Optional Semicolons Section 2.5. Comments Section 2.6. Literals Section 2.7. Identifiers ...
修改系统时间_Delphi修改windows7系统时间的开源源码,按照提示输入新的时间,点击“修改”按钮后即可改变windows当前时间,主要是学习delphi7和windows之间的时间接口,如何操作等。相关代码如下: For i := 0 to...
firstDate.setDate(1); //第一天 var endDate = new Date(firstDate); endDate.setMonth(firstDate.getMonth()+1); endDate.setDate(0); alert(“第一天:”+new XDate(firstDate).toString(‘yyyy-MM-dd’)...
(4) 该列是否作为生成 insert语句的一个列 boolean updatable() default true; (5) 该列是否作为生成 update语句的一个列 String columnDefinition() default ""; (6) 默认值 String table() default ""; (7) ...
return year%4==0&&year0!=0||year%400==0; } void Print() { cout; } private: int year,month,day; }; void main() { Date d1,d2,*pd=&d2; //定义对象时分配存储单元 d1.SetDate(2005,6,24); pd->SetDate...
flash action script 经典字典教程大全,学flash必备 -- --(递减) ++ ++(递增) ! !(逻辑 NOT) ......._highquality _highquality、Button._highquality、MovieClip._highquality、TextField._highquality ...
为了更好的讲这节的内容,提示一个 JS 处理日期的小技巧,想获取上个月最后一天,只需要设置SetDate参数为0即可。 var endDate = new Date(); //上个月最后一天 endDate.setDate(0); 接着正文开始: 使用 JS 获取...