search

mybatis / mybatis-3

MyBatis SQL mapper framework for Java
http://mybatis.github.io/mybatis-3/
Apache License 2.0
19.8k stars 12.86k forks source link

I want to return promary key with @SelectKey,but throws com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: PROCEDURE neptune.identity does not exist #1348

Closed leezhmwty closed 6 years ago

leezhmwty commented 6 years ago

MyBatis version

3.3.1

Database vendor and version

MySQL

Test case or example project

Tested method:

@SelectKey(keyProperty = "userId", resultType = String.class, before = false, statement = "call identity()") @Insert(SqlConstants.INSERT_RESIDENT_BASE_SQL) String insert(ResidentUserInfoDO user);

Steps to reproduce

I use junit to test insert method:

public class ResidentUserInfoServiceTest extends BaseJunitRunner {

@Autowired
private ResidentUserInfoService residentUserInfoService;

@Test
public void insert() {

    ResidentUserInfoDO residentUserInfoDO = new ResidentUserInfoDO();
    String userId = "qwqwqwqwqwqwq";
    residentUserInfoDO.setUserId(userId);
    residentUserInfoDO.setIsCertification(0);
    String insert = residentUserInfoService.insert(residentUserInfoDO);
    Assert.assertEquals(insert, userId);
}

}

Expected result

Inserted successfully and return "qwqwqwqwqwqwq"

Actual result

Below is the log:

2018-09-11 14:59:52.038 INFO [neptune-system,,,] 18920 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited 2018-09-11 14:59:52.076 DEBUG [neptune-system,,,] 18920 --- [ main] c.h.b.c.n.s.b.d.m.R.insert : ==> Preparing: INSERT INTO resident_user_info (user_id,name,personnel_number, paper_type, paper_number,paper_front_url,paper_contrary_url, face_url,face_upload_date,is_certification) values(?,?,?, ?,?, ?, ?, ?,?, ?) 2018-09-11 14:59:52.097 DEBUG [neptune-system,,,] 18920 --- [ main] c.h.b.c.n.s.b.d.m.R.insert : ==> Parameters: qwqwqwqwqwqwq(String), null, null, null, null, null, null, null, null, 0(Integer) 2018-09-11 14:59:52.106 DEBUG [neptune-system,,,] 18920 --- [ main] c.h.b.c.n.s.b.d.m.R.insert : <== Updates: 1 2018-09-11 14:59:52.118 DEBUG [neptune-system,,,] 18920 --- [ main] c.h.b.c.n.s.b.d.m.R.insert!selectKey : ==> Preparing: call identity() 2018-09-11 14:59:52.118 DEBUG [neptune-system,,,] 18920 --- [ main] c.h.b.c.n.s.b.d.m.R.insert!selectKey : ==> Parameters: 2018-09-11 14:59:52.138 INFO [neptune-system,,,] 18920 --- [ main] o.s.b.f.xml.XmlBeanDefinitionReader : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml] 2018-09-11 14:59:52.164 INFO [neptune-system,,,] 18920 --- [ main] o.s.jdbc.support.SQLErrorCodesFactory : SQLErrorCodes loaded: [DB2, Derby, H2, HDB, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]

org.springframework.jdbc.BadSqlGrammarException: Error selecting key or setting result to parameter object. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: PROCEDURE neptune.identity does not exist ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: PROCEDURE neptune.identity does not exist

at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:91)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy185.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:57)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy193.insert(Unknown Source)
at com.hikvision.building.cloud.neptune.system.biz.service.ResidentUserInfoService.insert(ResidentUserInfoService.java:81)
at com.hikvision.building.cloud.neptune.system.biz.service.ResidentUserInfoService$$FastClassBySpringCGLIB$$e3692fe0.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
at com.hikvision.building.cloud.neptune.system.biz.service.ResidentUserInfoService$$EnhancerBySpringCGLIB$$4a9fc137.insert(<generated>)
at com.hikvision.building.cloud.neptune.system.biz.service.ResidentUserInfoServiceTest.insert(ResidentUserInfoServiceTest.java:21)
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:497)
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.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
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.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
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.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: PROCEDURE neptune.identity does not exist at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931) at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929) at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:131) at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493) 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:497) at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) at com.sun.proxy.$Proxy278.execute(Unknown Source) 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:324) 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 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:497) at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) at com.github.pagehelper.SqlUtil._processPage(SqlUtil.java:401) at com.github.pagehelper.SqlUtil.processPage(SqlUtil.java:374) at com.github.pagehelper.PageHelper.intercept(PageHelper.java:250) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.$Proxy276.query(Unknown Source) at org.apache.ibatis.executor.keygen.SelectKeyGenerator.processGeneratedKeys(SelectKeyGenerator.java:68) at org.apache.ibatis.executor.keygen.SelectKeyGenerator.processAfter(SelectKeyGenerator.java:54) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:50) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) 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:497) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) at com.sun.proxy.$Proxy276.update(Unknown Source) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185) 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:497) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ... 45 more

h3adache commented 6 years ago

This doesn't seem to be mybatis related. Maybe something in your db setup? Can you create a project (on your github) that can reproduce this issue?

leezhmwty commented 6 years ago

This doesn't seem to be mybatis related. Maybe something in your db setup? Can you create a project (on your github) that can reproduce this issue?

Ok,thank you for your answer, due to the project involving the company, I will restore this problem on my personal computer as much as possible. In other words, you mean there is no problem with the use of @SelectKey, but may be a database configuration problem?

h3adache commented 6 years ago

Or a sql issue. Can you change your statement to this:

SELECT LAST_INSERT_ID()

leezhmwty commented 6 years ago

Or a sql issue. Can you change your statement to this:

SELECT LAST_INSERT_ID()

Ok, I will try to modify the statement tomorrow, thank you.

leezhmwty commented 6 years ago

Or a sql issue. Can you change your statement to this:

SELECT LAST_INSERT_ID()

According to your suggestion, I changed the statement to

SELECT LAST_INSERT_ID() The following is the log


2018-09-13 13:51:26.648  INFO [neptune-system,,,] 11840 --- [nfoReplicator-0] com.netflix.discovery.DiscoveryClient    : DiscoveryClient_NEPTUNE-SYSTEM/pc-hz20081949.hikvision.com:neptune-system:0 - registration status: 204
2018-09-13 13:51:27.215  INFO [neptune-system,,,] 11840 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2018-09-13 13:51:27.226 DEBUG [neptune-system,,,] 11840 --- [           main] c.h.b.c.n.s.b.d.m.R.insertAndReturnKey   : ==>  Preparing: INSERT INTO resident_user_info (resident_id,name,personnel_number, paper_type, paper_number,paper_front_url,paper_contrary_url, face_url,face_upload_date,is_certification) values(?,?,?, ?,?, ?, ?, ?,?, ?) 
2018-09-13 13:51:27.258 DEBUG [neptune-system,,,] 11840 --- [           main] c.h.b.c.n.s.b.d.m.R.insertAndReturnKey   : ==> Parameters: qwqwqwqwqwqwq(String), null, null, null, null, null, null, null, null, 0(Integer)
2018-09-13 13:51:27.284 DEBUG [neptune-system,,,] 11840 --- [           main] c.h.b.c.n.s.b.d.m.R.insertAndReturnKey   : <==    Updates: 1
2018-09-13 13:51:27.307 DEBUG [neptune-system,,,] 11840 --- [           main] n.s.b.d.m.R.insertAndReturnKey!selectKey : ==>  Preparing: SELECT LAST_INSERT_ID() 
2018-09-13 13:51:27.307 DEBUG [neptune-system,,,] 11840 --- [           main] n.s.b.d.m.R.insertAndReturnKey!selectKey : ==> Parameters: 
2018-09-13 13:51:27.326 DEBUG [neptune-system,,,] 11840 --- [           main] n.s.b.d.m.R.insertAndReturnKey!selectKey : <==      Total: 1

org.apache.ibatis.binding.BindingException: Mapper method 'com.hikvision.building.cloud.neptune.system.biz.domain.mapper.ResidentUserInfoMapper.insertAndReturnKey' has an unsupported return type: class java.lang.String


According to Mybatis logs, the date inserts succeeds and returns a result, but does not support the String type,whether @SelectKey does not support the type of String?
h3adache commented 6 years ago

@leezhmwty sorry I didn't read what you were doing, I thought this was about how to get the last generated id. In your case, you are simply passing in the id. <SelectKey> is more for the case of autogenerated ids from dbs. The insert returns number of rows affected and not the last id generated.

h3adache commented 6 years ago

Closed as this is a usage issue.