java – 在Oracle数据库中调用查询时更改绑定后的参数值
我们在公司里有使用
Java 7,Spring Framework 3.1.2,MyBatis 3.1.1,MyBatis Spring 1.2.2,JasperReports 6.1.0等开发的应用程序.应用程序在Tomcat 7.0.35上运行,使用Tomcat连接池与Oracle数据库10g企业版10.2.0.4.0版的连接 – 64位. JRE版本1.7.0_09-b05.应用程序适用于RHEL Server 6.5.
有时出现问题,然后在几小时(3-6小时)后消失,有时几天(1-3天)消失.当创建一个报告Web服务,应用程序调用从MyBatis的映射器,它返回列表与LT方法; MonthlyReport> ;,然后应用程序传递这个列表来JasperReport的发动机的文件系统上创建的报告,并且在响应文件的结尾,应用程序返回流(MTOM ).问题是,当尝试在数据库中运行查询以创建报告时,会定期导致以下异常: ERROR 2015-07-23 11:44:03,012 [http-bio-8280-exec-2] exception type: org.springframework.jdbc.UncategorizedSQLException ERROR 2015-07-23 11:44:03,012 [http-bio-8280-exec-2] exception message: ### Error querying database. Cause: java.sql.SQLException: ORA-12801: error signaled in parallel query server P010 ORA-01841: (full) year must be between -4713 and +9999,and not be 0 这是查询: <select id="getMonthlyReportData" resultType="MonthlyReport" parameterType="map"> <![CDATA[ SELECT r.bank_name bankName,r.user_name userName,r.descr userDescription,CASE WHEN r.parent_bank_id IS NULL THEN 1 ELSE 0 END isParentBankInt,COUNT (CASE WHEN r.p_type NOT IN ('SS','DR') THEN 1 ELSE NULL END) postpaidPaymentCount,SUM (CASE WHEN r.p_type NOT IN ('SS','DR') THEN r.amount ELSE 0 END) postpaidPaymentAmount,COUNT (CASE WHEN r.p_type = 'SS' THEN 1 ELSE NULL END) prepaidPaymentCount,SUM (CASE WHEN r.p_type = 'SS' THEN r.amount ELSE 0 END) prepaidPaymentAmount,COUNT (CASE WHEN r.p_type = 'DR' THEN 1 ELSE NULL END) depositRepayCount,SUM (CASE WHEN r.p_type = 'DR' THEN r.amount ELSE 0 END) depositRepayAmount FROM (SELECT q.queue_id,q.amount,q.p_type,q.user_name,q.action_date,b.parent_bank_id,U.descr,b.bank_name FROM rbp_queue q,rbp_all_banks b,rbp_users U WHERE q.user_name = U.user_name AND U.working_bank_id = b.bank_id AND q.err_code = -1000000 AND q.action_date BETWEEN TO_DATE (#{start_date,javaType=STRING,jdbcType=VARCHAR},'YYYYMMDDHH24MISS') AND TO_DATE (#{end_date,'YYYYMMDDHH24MISS') AND U.working_bank_id IN (SELECT bank_id FROM rbp_all_banks WHERE bank_id = #{bank_id,javaType=Integer,jdbcType=NUMERIC} OR parent_bank_id = #{bank_id,jdbcType=NUMERIC}) UNION SELECT qa.queue_id,qa.amount,qa.p_type,qa.user_name,qa.action_date,ba.parent_bank_id,Ua.descr,ba.bank_name FROM sysadm.rbp_queue_arch@azis_archdb qa,rbp_all_banks ba,rbp_users Ua WHERE qa.user_name = Ua.user_name AND Ua.working_bank_id = ba.bank_id AND qa.err_code = -1000000 AND qa.action_date BETWEEN TO_DATE (#{start_date,'YYYYMMDDHH24MISS') AND TO_DATE (#{end_date,'YYYYMMDDHH24MISS') AND Ua.working_bank_id IN (SELECT bank_id FROM rbp_all_banks WHERE bank_id = #{bank_id,jdbcType=NUMERIC})) r GROUP BY r.bank_name,r.user_name,r.descr,CASE WHEN r.parent_bank_id IS NULL THEN 1 ELSE 0 END ORDER BY isParentBankInt DESC,bankName,userName ]]> 应用程序不使用日期类型参数,因为在这种情况下Oracle使用不同的计划,并且查询运行很长时间.因此,应用程序将查询日期作为文本传递给查询日期,然后使用TO_DATE函数将其转换为日期. DEBUG 2015-07-22 15:10:52,720 [http-apr-8281-exec-2] ooo Using Connection [ProxyConnection[PooledConnection[oracle.jdbc.driver.T4CConnection@344482ac]]] DEBUG 2015-07-22 15:10:52,724 [http-apr-8281-exec-2] ==> Preparing: SELECT r.bank_name bankName,... DEBUG 2015-07-22 15:10:52,725 [http-apr-8281-exec-2] ==> Parameters: 20150601000000(String),20150621235959(String),31(Integer),20150601000000(String),31(Integer) 如此处所示,MyBatis将日期参数(作为字符串)传递给查询,但是如果要查看Oracle跟踪,我们可以看到,日期参数的值是“”(不是空,而是两个双引号). 呼唤 SELECT TO_DATE ('','YYYYMMDDHH24MISS') FROM dual at TOAD返回null值,但是 SELECT TO_DATE ('""','YYYYMMDDHH24MISS') FROM dual 引发异常:ORA-01841 :(完整)年份必须介于-4713和9999之间,而不是0. 奇怪的是,当服务器出现问题时,同时应用程序在其他计算机(例如我的工作笔记本电脑)上运行时没有问题(创建此报告). Bind#0 oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=02 csi=2000 siz=224 off=0 kxsbbbfp=9fffffffbf330908 bln=32 avl=28 flg=05 value="" Bind#1 oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=32 kxsbbbfp=9fffffffbf330928 bln=32 avl=28 flg=01 value="" Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=02 csi=2000 siz=0 off=64 kxsbbbfp=9fffffffbf330948 bln=22 avl=02 flg=01 value=31 Bind#3 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=02 csi=2000 siz=0 off=88 kxsbbbfp=9fffffffbf330960 bln=22 avl=02 flg=01 value=31 Bind#4 oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=112 kxsbbbfp=9fffffffbf330978 bln=32 avl=28 flg=01 value="" Bind#5 oacdty=01 mxl=32(28) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=144 kxsbbbfp=9fffffffbf330998 bln=32 avl=28 flg=01 value="" Bind#6 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=02 csi=2000 siz=0 off=176 kxsbbbfp=9fffffffbf3309b8 bln=22 avl=02 flg=01 value=31 Bind#7 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=02 csi=2000 siz=0 off=200 kxsbbbfp=9fffffffbf3309d0 bln=22 avl=02 flg=01 value=31 以下是未出现问题时Oracle数据库的跟踪文件的一部分: Bind#0 oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=31 siz=224 off=0 kxsbbbfp=9fffffffbf323e50 bln=32 avl=14 flg=05 value="20150601000000" Bind#1 oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=31 siz=0 off=32 kxsbbbfp=9fffffffbf323e70 bln=32 avl=14 flg=01 value="20150621235959" Bind#2 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=64 kxsbbbfp=9fffffffbf323e90 bln=22 avl=02 flg=01 value=31 Bind#3 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=88 kxsbbbfp=9fffffffbf323ea8 bln=22 avl=02 flg=01 value=31 Bind#4 oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=31 siz=0 off=112 kxsbbbfp=9fffffffbf323ec0 bln=32 avl=14 flg=01 value="20150601000000" Bind#5 oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000010 frm=01 csi=31 siz=0 off=144 kxsbbbfp=9fffffffbf323ee0 bln=32 avl=14 flg=01 value="20150621235959" Bind#6 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=176 kxsbbbfp=9fffffffbf323f00 bln=22 avl=02 flg=01 value=31 Bind#7 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=200 kxsbbbfp=9fffffffbf323f18 bln=22 avl=02 flg=01 value=31 注意绑定的值:0,1,4,5.出现问题时,值为value =“”. 该问题与MyBatis无关,因为在请求进入已编译的JasperReports文件(monthlyReport.jasper)之前,应用程序将数据库连接传递给JasperReports引擎以创建报告. JasperReports本身连接到数据库并运行查询. MyBatis不用于创建报告,但在应用程序中用于所有其他目的.同样的Oracle错误(ORA-01841 :(完整)年份必须在-4713和9999之间,而不是0)定期发布和那里.从旧日志文件: ERROR 2015-06-11 08:57:17,559 [http-apr-8280-exec-9] Fill 1: exception net.sf.jasperreports.engine.JRException: Error executing SQL statement for : monthlyReport_New32Dataset321_1432644594876_272524 at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1087) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:668) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:845) at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:651) at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59) at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:203) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) at java.lang.Thread.run(Thread.java:722) Caused by: java.sql.SQLException: ORA-12801: error signaled in parallel query server P002 ORA-01841: (full) year must be between -4713 and +9999,and not be 0 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493) at sun.reflect.GeneratedMethodAccessor349.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:235) at $Proxy99.executeQuery(Unknown Source) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 11 more 请帮忙解决这个问题. 解决方法
您正在观察症状.这不是引号的问题.在跟踪中,字符串参数显示为doulbe引号.即
value="20150621235959" 表示客户端传递了字符串’20150621235959’和 value="" 表示客户端传递了一个空字符串”.领导ORA-12801 根本原因是客户端能够将空字符串传递给数据库. 谓词 action_date BETWEEN date1 and date2 如果一个或两个日期为NULL,则不返回任何行.如果你不信任简单运行这个查询 -- return nothing SELECT * FROM dual where sysdate between to_date('','ddmmyyyy') and to_date('','ddmmyyyy'); 没有必要为什么客户端应该向数据库发送空字符串,如果你知道,什么都不会返回. 所以IMHO应该在客户端执行一些验证,只接受有效的字符串(至少是正确的长度).这将解决问题. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |