Tag Archives: MyBatisPlus

[Solved] mybatis-plus getById() Method Error: Expected one result (or null) to be returned by selectOne(), but found: 2

Error:

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at com.sun.proxy.$Proxy153.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:90)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
	at com.sun.proxy.$Proxy156.selectById(Unknown Source)
	at com.baomidou.mybatisplus.extension.service.IService.getById(IService.java:199)
	at com.baomidou.mybatisplus.extension.service.IService$$FastClassBySpringCGLIB$$f8525d18.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:685)
	at cn.com.chnsys.eleac4.sys_common.service.impl.ConfigRuleDetailServiceImpl$$EnhancerBySpringCGLIB$$4e131247.getById(<generated>)
	at cn.com.chnsys.eleac4.rule.RuleApplicationTests.test3(RuleApplicationTests.java:91)
	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.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:675)
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:125)
	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:132)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:124)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:74)
	at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:104)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:62)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:43)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:35)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:202)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:198)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:69)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at java.util.ArrayList.forEach(ArrayList.java:1249)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at java.util.ArrayList.forEach(ArrayList.java:1249)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)
	at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)
	at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
	at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:69)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:230)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:58)
Caused by: org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:80)
	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:426)
	... 75 more

SQL query log:

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@140fa482] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2027818863 wrapping com.mysql.jdbc.JDBC4Connection@3b063aaf] will not be managed by Spring
==>  Preparing: SELECT ID,ZONGTPZID AS configTotalId,ZONGTPZLX AS configTotalType,ZONGTPZMC AS configTotalName,XUNCXMC AS configRuleDetailName,ZHUCID AS registrId,JISGZ AS calRule,BEIYZFC8 AS ReservedString8,BEIYZFC7 AS ReservedString7,BEIYZFC6 AS ReservedString6,BEIYZFC5 AS ReservedString5,BEIYZFC4 AS ReservedString4,BEIYZFC3 AS ReservedString3,BEIYZFC2 AS ReservedString2,BEIYZFC1 AS ReservedString1,BEIYZT5 AS ReservedInt5,BEIYZT4 AS ReservedInt4,BEIYZT3 AS ReservedInt3,BEIYZT2 AS ReservedInt2,BEIYZT1 AS ReservedInt1 FROM eleac_guizpz WHERE ID=?
==> Parameters: 4(Integer)
<==    Columns: ID, configTotalId, configTotalType, configTotalName, configRuleDetailName, registrId, calRule, ReservedString8, ReservedString7, ReservedString6, ReservedString5, ReservedString4, ReservedString3, ReservedString2, ReservedString1, ReservedInt5, ReservedInt4, ReservedInt3, ReservedInt2, ReservedInt1
<==        Row: 4, 4, 4, Full, 2, <<BLOB>>, null, null, null, null, null, null, null, null, null, null, null, null, null
<==        Row: 4C56B386-8257-4991-A6BF-29D2EEFBF299, 1, 1, 1, <<BLOB>>, null, null, null, null, null, null, null, null, null, null, null, null, null
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@140fa482]

error analysis

Viewing the entity object, it is found that the primary key ID data type is string and the database primary key type is varcher

The parameter of SQL is integer, which is different from the entity data type. It is related to the underlying principle of mybayis plus and will not be analyzed here.

Error resolution: modify the query parameter to string,

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@439047ef] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@2114163679 wrapping com.mysql.jdbc.JDBC4Connection@301b2259] will not be managed by Spring
==>  Preparing: SELECT ID,ZONGTPZID AS configTotalId,ZONGTPZLX AS configTotalType,ZONGTPZMC AS configTotalName,XUNCXMC AS configRuleDetailName,ZHUCID AS registrId,JISGZ AS calRule,BEIYZFC8 AS ReservedString8,BEIYZFC7 AS ReservedString7,BEIYZFC6 AS ReservedString6,BEIYZFC5 AS ReservedString5,BEIYZFC4 AS ReservedString4,BEIYZFC3 AS ReservedString3,BEIYZFC2 AS ReservedString2,BEIYZFC1 AS ReservedString1,BEIYZT5 AS ReservedInt5,BEIYZT4 AS ReservedInt4,BEIYZT3 AS ReservedInt3,BEIYZT2 AS ReservedInt2,BEIYZT1 AS ReservedInt1 FROM eleac_guizpz WHERE ID=?
==> Parameters: 4(String)
<==    Columns: ID, configTotalId, configTotalType, configTotalName, configRuleDetailName, registrId, calRule, ReservedString8, ReservedString7, ReservedString6, ReservedString5, ReservedString4, ReservedString3, ReservedString2, ReservedString1, ReservedInt5, ReservedInt4, ReservedInt3, ReservedInt2, ReservedInt1
<==        Row: 4, 4, 4, Full, 2, <<BLOB>>, null, null, null, null, null, null, null, null, null, null, null, null, null
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@439047ef]

After introducing mybatisplus, the user-defined method is used to solve the invalid bound statement (not found) error

I tried all the methods on the Internet, but they didn’t work.

Finally, I took a look at the. YML configuration file,

mybatis-plus:
  mapper-locations: classpath:mappers/*Mapper.xml
  type-aliases-package: com.xxxxx.entity

　　

Here I wrote mybatis: XXXXXX

When I used tkmapper before, there was no problem with writing this here.

 

If you use mybatis plus, write the configuration of mybatis plus here. The problem is solved

Treatment of MySQL database keyword as column name by mybatisplus — sqlsyntax errorexception: you have an error in your SQL syntax;

explain:

When designing the database, if you use the MySQL keyword as the column name (for example, order is used for sorting), an error will be reported: java.sql.sqlsyntax errorexception: you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ORDER,CREATE_ TIME,MODIFY_ TIME FROM xxx’ at line 1

terms of settlement:

Modify the column name, do not use the keyword as the column name; (recommended)

When querying, use quotation marks (single quotation marks, double quotation marks and back quotation marks) to enclose the column name of its keyword,

If you use mybatis to write SQL, there is no problem. You can use quotation marks to solve it;

If you use mybatis plus, when querying all columns, you need to list all columns, because the corresponding column names containing keywords need to be processed.

How to Solve MyBatisPlus Error: Failed to process, please exclude the tableName or statementId

Error reporting details:
error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.mybatisplus exception: failed to process, please exclude the tablename or statementid. Error SQL: XXXXXXXX 

Error reason:
the user-defined SQL is used, which may contain special functions or complex syntax, so it is not supported by jsqlparser (the fields such as tenant ID cannot be added), so the jsqlparserexception is thrown.

The exception is caught by mybatisplus and is encapsulated as mybatisplus exception and then thrown.

Source code: com.baomidou.mybatisplus.core.parser.abstractjsqlparser

solution:
failed to process, please exclude the tablename or statementid
translation is: processing failed, please exclude the table name or statement ID
the P.S. prompt is very specific, but it’s easy to be confused, because the unfamiliar people don’t know that the tenant ID has a problem

Method 1: exclude the table name
when configuring tenanthandler of mybatisplus, filter out the table in dotablefilter method (that is, do not process the tenant ID field of the table).

Method 2: exclude the statement
mark @ sqlparser (filter = true) on the interface of custom SQL

    @SqlParser(filter = true)
    @Select("SQL")
    void selectDemo();

The official explanation for this note is:

Tenant annotation supports method and mapper interface. The default value is false
true means to filter SQL parsing, that is, it will not enter the isqlparser parsing chain
if it is false, it will enter the parsing chain and append a tenant_ ID and so on.