这是代码:
public Page pageCount(Page page,String userName,String startDate,String endDate,String deptid){
String sql="select sum(cnt) from (select count(*) as cnt from t_humn_leave where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? " +
" union select count(*) from t_humn_overtime where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? " +
" union select count(*) from t_humn_training where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? )";
Query queryCount= getSession().createSQLQuery(sql);
queryCount.setParameter(0, "'%"+userName+"%'");
queryCount.setParameter(1, "to_date("+startDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(2, "to_date("+endDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(3, "'%"+userName+"%'");
queryCount.setParameter(4, "to_date("+startDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(5, "to_date("+endDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(6, "'%"+userName+"%'");
queryCount.setParameter(7, "to_date("+startDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(8, "to_date("+endDate+", 'yyyy-mm-dd hh24:mi:ss')");
Object count =queryCount.uniqueResult();
//Query query = getSession().createSQLQuery(sql).addEntity(entityClass);
//query.list();
String sql1="select '请假',DEPTID,USERID,STARTDATE,ENDDATE,RECORDER,REMARK,ID from t_humn_leave where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? " +
"union select '加班',DEPTID,USERID,startDate,endDate,RECORDER,REMARK,ID from t_humn_overtime where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? " +
" union select '外出培训',DEPTID,USERID,startDate,endDate,RECORDER,REMARK,ID from t_humn_training where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? ";
Query query=getSession().createSQLQuery(sql1);
query.setParameter(0, "%"+userName+"%");
query.setParameter(1, "to_date('"+startDate+"', 'yyyy-mm-dd')");
query.setParameter(2, "to_date('"+endDate+"', 'yyyy-mm-dd')");
query.setParameter(3, "%"+userName+"%");
query.setParameter(4, "to_date('"+startDate+"', 'yyyy-mm-dd')");
query.setParameter(5, "to_date('"+endDate+"', 'yyyy-mm-dd')");
query.setParameter(6, "%"+userName+"%");
query.setParameter(7, "to_date('"+startDate+"', 'yyyy-mm-dd')");
query.setParameter(8, "to_date('"+endDate+"', 'yyyy-mm-dd')");
System.out.println("三表总记录条数"+count);
List list = query.list();
page.setResult(list);
page.setTotalCount(((Number)count).longValue());
return page;
}
这是错误:
2012-04-06 15:04:13,953 [http-8686-6] WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1858, SQLState: 22008
2012-04-06 15:04:13,953 [http-8686-6] ERROR [org.hibernate.util.JDBCExceptionReporter] - ORA-01858: 在要求输入数字处找到非数字字符
2012-04-06 15:05:02,109 [http-8686-6] ERROR [500.jsp] - could not execute query
org.hibernate.exception.DataException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:102)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2452)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2192)
at org.hibernate.loader.Loader.list(Loader.java:2187)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1832)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:179)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:859)
at com.pengtu.dao.human.LeaveDao.pageCount(LeaveDao.java:35)
at com.pengtu.service.human.LeaveOvertimeManager.getleaveOvertimeTrainingMap(LeaveOvertimeManager.java:331)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.pengtu.web.human.LeaveOvertimeAction.personnel(LeaveOvertimeAction.java:122)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:452)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:291)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:254)
at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:263)
at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)上面说:在要求输入数字处找到非数字字符 但是我sql语句中的占位符根本就没有用到数字的地方都是String类型的.难道是Date哪里的问题? 帮忙看看啊. 头疼了 我休息会.谢了
public Page pageCount(Page page,String userName,String startDate,String endDate,String deptid){
String sql="select sum(cnt) from (select count(*) as cnt from t_humn_leave where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? " +
" union select count(*) from t_humn_overtime where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? " +
" union select count(*) from t_humn_training where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? )";
Query queryCount= getSession().createSQLQuery(sql);
queryCount.setParameter(0, "'%"+userName+"%'");
queryCount.setParameter(1, "to_date("+startDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(2, "to_date("+endDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(3, "'%"+userName+"%'");
queryCount.setParameter(4, "to_date("+startDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(5, "to_date("+endDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(6, "'%"+userName+"%'");
queryCount.setParameter(7, "to_date("+startDate+", 'yyyy-mm-dd hh24:mi:ss')");
queryCount.setParameter(8, "to_date("+endDate+", 'yyyy-mm-dd hh24:mi:ss')");
Object count =queryCount.uniqueResult();
//Query query = getSession().createSQLQuery(sql).addEntity(entityClass);
//query.list();
String sql1="select '请假',DEPTID,USERID,STARTDATE,ENDDATE,RECORDER,REMARK,ID from t_humn_leave where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? " +
"union select '加班',DEPTID,USERID,startDate,endDate,RECORDER,REMARK,ID from t_humn_overtime where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? " +
" union select '外出培训',DEPTID,USERID,startDate,endDate,RECORDER,REMARK,ID from t_humn_training where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between ? and ? ";
Query query=getSession().createSQLQuery(sql1);
query.setParameter(0, "%"+userName+"%");
query.setParameter(1, "to_date('"+startDate+"', 'yyyy-mm-dd')");
query.setParameter(2, "to_date('"+endDate+"', 'yyyy-mm-dd')");
query.setParameter(3, "%"+userName+"%");
query.setParameter(4, "to_date('"+startDate+"', 'yyyy-mm-dd')");
query.setParameter(5, "to_date('"+endDate+"', 'yyyy-mm-dd')");
query.setParameter(6, "%"+userName+"%");
query.setParameter(7, "to_date('"+startDate+"', 'yyyy-mm-dd')");
query.setParameter(8, "to_date('"+endDate+"', 'yyyy-mm-dd')");
System.out.println("三表总记录条数"+count);
List list = query.list();
page.setResult(list);
page.setTotalCount(((Number)count).longValue());
return page;
}
这是错误:
2012-04-06 15:04:13,953 [http-8686-6] WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1858, SQLState: 22008
2012-04-06 15:04:13,953 [http-8686-6] ERROR [org.hibernate.util.JDBCExceptionReporter] - ORA-01858: 在要求输入数字处找到非数字字符
2012-04-06 15:05:02,109 [http-8686-6] ERROR [500.jsp] - could not execute query
org.hibernate.exception.DataException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:102)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2452)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2192)
at org.hibernate.loader.Loader.list(Loader.java:2187)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1832)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:179)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:859)
at com.pengtu.dao.human.LeaveDao.pageCount(LeaveDao.java:35)
at com.pengtu.service.human.LeaveOvertimeManager.getleaveOvertimeTrainingMap(LeaveOvertimeManager.java:331)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.pengtu.web.human.LeaveOvertimeAction.personnel(LeaveOvertimeAction.java:122)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:452)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:291)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:254)
at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248)
at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:263)
at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)上面说:在要求输入数字处找到非数字字符 但是我sql语句中的占位符根本就没有用到数字的地方都是String类型的.难道是Date哪里的问题? 帮忙看看啊. 头疼了 我休息会.谢了
解决方案 »
- struts2 标签问题
- 电子银行系统!
- 如何用TOMCAT同时部署两个JSP系统?
- JFreeChart 字体模糊不清的问题?
- 访问一张图片的时候都会发生错误!而且是毫无相关的错误,想不出为什么!
- 文件上传后,存入oracle数据库问题,有空的看一下.
- 菜鸟问一下用dreamweaver mx怎么写JSP
- 有关session的问题:是否当浏览器关掉时,session也随即断开。
- JSP读取SQLSERVER2000里的CHAR类型中文内容显示为乱码
- mevan与java web项目的结合与管理
- 求sql语句,想删除表A,同时删除其关联的表B。怎么写呢?
- NetworkError: 500 Internal Server Error 错误,求解
在代码中。这是哪一行。
String sql="select sum(cnt) from (select count(*) as cnt from t_humn_leave where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between to_date(?, 'yyyy-mm-dd') and to_date(?, 'yyyy-mm-dd') " +
" union select count(*) from t_humn_overtime where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between to_date(?, 'yyyy-mm-dd') and to_date(?, 'yyyy-mm-dd') " +
" union select count(*) from t_humn_training where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between to_date(?, 'yyyy-mm-dd') and to_date(?, 'yyyy-mm-dd') )";
Query queryCount= getSession().createSQLQuery(sql);
queryCount.setParameter(0, "'%"+userName+"%'");
queryCount.setParameter(1, startDate);
queryCount.setParameter(2, endDate);
queryCount.setParameter(3, "'%"+userName+"%'");
queryCount.setParameter(4, startDate);
queryCount.setParameter(5, endDate);
queryCount.setParameter(6, "'%"+userName+"%'");
queryCount.setParameter(7, startDate);
queryCount.setParameter(8, endDate);
Object count =queryCount.uniqueResult();
//Query query = getSession().createSQLQuery(sql).addEntity(entityClass);
//query.list();
String sql1="select '请假',DEPTID,USERID,STARTDATE,ENDDATE,RECORDER,REMARK,ID from t_humn_leave where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between to_date(?, 'yyyy-mm-dd') and to_date(?, 'yyyy-mm-dd') " +
"union select '加班',DEPTID,USERID,startDate,endDate,RECORDER,REMARK,ID from t_humn_overtime where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between to_date(?, 'yyyy-mm-dd') and to_date(?, 'yyyy-mm-dd') " +
" union select '外出培训',DEPTID,USERID,startDate,endDate,RECORDER,REMARK,ID from t_humn_training where DELFLAG=0 and USERID in (select ID from T_APP_USER where NAME like ? ) and startDate between to_date(?, 'yyyy-mm-dd') and to_date(?, 'yyyy-mm-dd') ";
Query query=getSession().createSQLQuery(sql1);
query.setParameter(0, "%"+userName+"%");
query.setParameter(1, startDate);
query.setParameter(2, endDate);
query.setParameter(3, "%"+userName+"%");
query.setParameter(4, startDate);
query.setParameter(5, endDate);
query.setParameter(6, "%"+userName+"%");
query.setParameter(7, startDate);
query.setParameter(8, endDate);
System.out.println("三表总记录条数"+count);
where NAME like ? and startDate between to_date(?, 'yyyy-mm-dd hh24:mi:ss') and to_date(?, 'yyyy-mm-dd hh24:mi:ss')