DB2数据库SQL异常?

各位大牛,本第一次接触DB2数据库,被一个com.ibm.db2.jcc.am.SqlSyntaxErrorException异常困扰了好几天,如有想法请提示一下,谢谢;以下是相关配置。

```java
SSM框架JDBC驱动依赖:
<!-- DB2数据库连接驱动 -->
<dependency>
<groupId>com.ibm.db2.jcc</groupId>
<artifactId>db2jcc</artifactId>
<version>db2jcc4</version>
</dependency>

数据库配置文件:
driver=com.ibm.db2.jcc.DB2Driver
url=jdbc:db2://localhost:50000/qy_bank
username=db2admin
password=123456
initialSize=2
maxActive=10

dao层配置:
<!-- 读取jdbc.properties -->
<util:properties id="dbconfig" location="classpath:jdbc.properties"/>

<!-- 配置数据源 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="url" value="#{dbconfig.url}"/>
    <property name="driverClassName" value="#{dbconfig.driver}"/>
    <property name="username" value="#{dbconfig.username}"/>
    <property name="password" value="#{dbconfig.password}"/>
    <property name="initialSize" value="#{dbconfig.initialSize}"/>
    <property name="maxActive" value="#{dbconfig.maxActive}"/>
</bean>

<!-- SqlSessionFactoryBean -->
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
    <!-- 指定数据源,值为以上配置的数据源 -->
    <property name="dataSource" ref="dataSource"/>
    <!-- 指定XML映射文件的位置 -->
    <property name="mapperLocations" value="classpath:mappers/*.xml"/>
</bean>

<!-- MapperScannerConfigurer -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <!-- 指定接口文件的位置 -->
    <property name="basePackage" value="cn.jpp.mapper"/>
</bean>

<!-- DataSourceTransactionManager -->
<bean id="transactionManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <!-- 数据源 -->
    <property name="dataSource" ref="dataSource"/>
</bean>

数据库连接验证代码:

y23iunr5l6i

连接成功,显示连接信息:
re8qx96xje

对数据库进行select:
1pcto9y0ukg

出现异常,报错信息:
org.springframework.jdbc.BadSqlGrammarException:

Error querying database. Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.USERINFO, DRIVER=4.25.13

The error may exist in file [D:\IDEA\SpringMVC_SSM\target\classes\mappers\UserMapper.xml]

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: select id,name,age from userinfo

Cause: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.USERINFO, DRIVER=4.25.13

; bad SQL grammar []; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.USERINFO, DRIVER=4.25.13

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy19.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy20.findAll(Unknown Source)
at db2Test.ConnectDemo.select(ConnectDemo.java:59)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2ADMIN.USERINFO, DRIVER=4.25.13
at com.ibm.db2.jcc.am.b6.a(b6.java:810)
at com.ibm.db2.jcc.am.b6.a(b6.java:66)
at com.ibm.db2.jcc.am.b6.a(b6.java:140)
at com.ibm.db2.jcc.am.k3.c(k3.java:2824)
at com.ibm.db2.jcc.am.k3.d(k3.java:2808)
at com.ibm.db2.jcc.am.k3.a(k3.java:2234)
at com.ibm.db2.jcc.am.k4.a(k4.java:8242)
at com.ibm.db2.jcc.t4.ab.i(ab.java:206)
at com.ibm.db2.jcc.t4.ab.b(ab.java:96)
at com.ibm.db2.jcc.t4.p.a(p.java:32)
at com.ibm.db2.jcc.t4.av.i(av.java:150)
at com.ibm.db2.jcc.am.k3.al(k3.java:2203)
at com.ibm.db2.jcc.am.k4.bq(k4.java:3730)
at com.ibm.db2.jcc.am.k4.a(k4.java:4609)
at com.ibm.db2.jcc.am.k4.b(k4.java:4182)
at com.ibm.db2.jcc.am.k4.bg(k4.java:2880)
at com.ibm.db2.jcc.am.k4.execute(k4.java:2855)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
... 31 more

Tomcat运行日志:
jk0woggfw8

2回答

tongshuaitongshuai  技术支持 , 上海新炬网络技术有限公司
michael1983baker_zpwuwenpin赞同了此回答
提示得很明显了,你要查询的这表不存在,十有八九是查询的时候没指定schema,所以默认就用连接的用户名作为schema名去查找表名了。比如有张表:test.user, 其中test是schema名,user是表名。有个用户db2user去连接并查询这个表,但是没加schema名select * from user;此时会报错提示没...显示全部

提示得很明显了,你要查询的这表不存在,十有八九是查询的时候没指定schema,所以默认就用连接的用户名作为schema名去查找表名了。
比如有张表:test.user, 其中test是schema名,user是表名。
有个用户db2user去连接并查询这个表,但是没加schema名
select * from user;
此时会报错提示没有这个表:db2user.user
所以在查询的时候加上指定的schema就好了。
select * from tetst.user;

收起
 2019-01-12
浏览196
aixchina 邀答
冰玉冰玉  数据库开发工程师 , 北京瑞尼尔
db2admin schema下没有userinfo表,配置里的url指定一下schema就好了,或者查询语句里加上schema 显示全部

db2admin schema下没有userinfo表,配置里的url指定一下schema就好了,或者查询语句里加上schema

收起
 2019-01-10
浏览201
aixchina 邀答

提问者

baker_zp软件开发工程师, 南京巧易科技有限公司

问题状态

  • 发布时间:2019-01-10
  • 关注会员:3 人
  • 问题浏览:270
  • 最近回答:2019-01-12
  • 关于TWT  使用指南  社区专家合作  厂商入驻社区  企业招聘  投诉建议  版权与免责声明  联系我们
    © 2019  talkwithtrend — talk with trend,talk with technologist 京ICP备09031017号-30