错误完整提示org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the MS-RPT-DEV-DETAIL-EFFECT-COUNT-FOR-PAGING-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00942: table or view does not exist at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:220)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
at com.taobao.union.usc.dal.imp.IbatisRptCpsDevDetailEffectImpl.getItems(IbatisRptCpsDevDetailEffectImpl.java:45)
at com.taobao.union.usc.dal.imp.IbatisRptCpsDevDetailEffectImpl.findDevDetailEffect(IbatisRptCpsDevDetailEffectImpl.java:27)
at com.taobao.union.usc.dal.imp.IbatisRptCpsDevDetailEffectImplTest.testFindDevDetailEffect(IbatisRptCpsDevDetailEffectImplTest.java:27)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:168)
at junit.framework.TestCase.runBare(TestCase.java:134)
at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:76)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:79)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the MS-RPT-DEV-DETAIL-EFFECT-COUNT-FOR-PAGING-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00942: table or view does not exist at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:120)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:273)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
... 24 more
Caused by: java.sql.SQLException: ORA-00942: table or view does not exist at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:256)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)
at $Proxy1.execute(Unknown Source)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
... 30 more
使用ibatIS访问的数据库,在pl/sql里访问那个表没有任何问题。
第一次做,不知道大神们还需要什么代码。
--- The error occurred while applying a parameter map.
--- Check the MS-RPT-DEV-DETAIL-EFFECT-COUNT-FOR-PAGING-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00942: table or view does not exist at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:220)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
at com.taobao.union.usc.dal.imp.IbatisRptCpsDevDetailEffectImpl.getItems(IbatisRptCpsDevDetailEffectImpl.java:45)
at com.taobao.union.usc.dal.imp.IbatisRptCpsDevDetailEffectImpl.findDevDetailEffect(IbatisRptCpsDevDetailEffectImpl.java:27)
at com.taobao.union.usc.dal.imp.IbatisRptCpsDevDetailEffectImplTest.testFindDevDetailEffect(IbatisRptCpsDevDetailEffectImplTest.java:27)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:168)
at junit.framework.TestCase.runBare(TestCase.java:134)
at org.springframework.test.ConditionalTestCase.runBare(ConditionalTestCase.java:76)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:124)
at junit.framework.TestSuite.runTest(TestSuite.java:232)
at junit.framework.TestSuite.run(TestSuite.java:227)
at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:79)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the MS-RPT-DEV-DETAIL-EFFECT-COUNT-FOR-PAGING-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00942: table or view does not exist at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:120)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:273)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
... 24 more
Caused by: java.sql.SQLException: ORA-00942: table or view does not exist at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:256)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)
at $Proxy1.execute(Unknown Source)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
... 30 more
使用ibatIS访问的数据库,在pl/sql里访问那个表没有任何问题。
第一次做,不知道大神们还需要什么代码。
java数据库连接rptdb1=new JdbcDatabaseTester("oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@119.42.225.77:1521:rptdb1","alimm","alimm","ALIMM");
<select id="MS-CPS-DEV-TOOL-COUNT-FOR-PAGING" resultClass="int">
<![CDATA[
select count(*)
From cps_dev_tool c
where c.top_status=1 and (c.status = 1 or c.status=3)
]]>
</select>
java数据库连接rptdb1=new JdbcDatabaseTester("oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:@119.42.225.77:1521:rptdb1","alimm","alimm","ALIMM");
<select id="MS-RPT-DEV-DETAIL-EFFECT-COUNT-FOR-PAGING" resultClass="int">
<![CDATA[
select count(*)
From rpt_cps_dev_detail_effect c
where c.dev_id = #devId#
and c.thedate>=to_date(#sstarttime#,'yyyy-mm-dd')
and c.thedate<=to_date(#sendtime#,'yyyy-mm-dd')
]]>
<dynamic>
<isNotEmpty property="toolId" prepend="and">
c.tool_id=#toolId#
</isNotEmpty>
</dynamic>
</select>
兄弟,你的参数呢?怎么传进来?替你改了一下。。parameterClass="java.util.HashMap"为传进来的map类型的参数取值的时候直接取第一个就行了
<select id="MS-RPT-DEV-DETAIL-EFFECT-COUNT-FOR-PAGING" parameterClass="java.util.HashMap" resultClass="Integer">
select count(*) as value
From
rpt_cps_dev_detail_effect c
where
c.dev_id = #devId#
and c.thedate >= to_date(#sstarttime#,'yyyy-mm-dd')
and c.thedate <= to_date(#sendtime#,'yyyy-mm-dd')
<isNotEmpty prepend="and" property="toolId">
c.tool_id=#toolId#
</isNotEmpty>
</select>
这个问题我已经解决了
是ibatiS自身的问题。。
他在定位数据库的时候,貌似只能定位到ip地址,对服务没有准确定位。
因为每次他注入给我的数据库,都是同一个ip下的另一个数据源。
这是个很蛋疼,但却很考验人的问题
我还要仔细看ibatIS的代码才能确定问题。
而且查询的是 coun(*) 也出现了 数据库没有该表或视图
2012-02-16 20:20:29,188 [JakartaCommonsLoggingImpl.java:27] [com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl] DEBUG java.sql.Connection :: {conn-100000} Preparing Statement: select count(t.SUPPLIER_IC_ID) from vendor.vendor_tb_supplier_ic t
2012-02-16 20:20:34,243 [JakartaCommonsLoggingImpl.java:27] [com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl] DEBUG java.sql.PreparedStatement :: {pstm-100001} Executing Statement: select count(t.SUPPLIER_IC_ID) from vendor.vendor_tb_supplier_ic t
2012-02-16 20:20:34,243 [JakartaCommonsLoggingImpl.java:27] [com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl] DEBUG java.sql.PreparedStatement :: {pstm-100001} Parameters: []
2012-02-16 20:20:34,243 [JakartaCommonsLoggingImpl.java:27] [com.ibatis.common.logging.jakarta.JakartaCommonsLoggingImpl] DEBUG java.sql.PreparedStatement :: {pstm-100001} Types: []
2012-02-16 20:20:42,698 [SupplierServiceImpl.java:176] [com.nfsq.vendorPlatform.supplier.service.impl.SupplierServiceImpl] ERROR com.nfsq.vendorPlatform.supplier.service.impl.SupplierServiceImpl :: org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in ibatis/sqlmap-supplier.xml.
--- The error occurred while applying a parameter map.
--- Check the supplier.getSupplierIcCount-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00942: table or view does not exist
首先感谢大家的回复原因是由于spring的问题
由于在我继承的模板里,存在一个默认的数据源,名字就是叫做dataSourse
所以,当我默认用名字作为spring注入的方法时
他就会无论怎样,都把dataSourse弄成默认数据源
无法正确注入
够犀利,你虽然没有解决我的问题
但是,看得出,你对ibatis还是很有研究 啊。。