Mybatis 异常（SQL 语法异常 ambiguous）

分手后的思念是犯贱 2022-10-16 06:09 238阅读 0赞

# 内容 #

## 一、异常信息 ##

严重: Servlet.service() for servlet \[dispatcherServlet\] in context with path \[\] threw exception \[Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException:

\#\#\# Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ‘name’ in where clause is ambiguous  
\#\#\# The error may exist in com/yy/homework/mapper/EmployeeMapper.xml  
\#\#\# The error may involve defaultParameterMap  
\#\#\# The error occurred while setting parameters  
\#\#\# SQL: select e.id, e.username, e.name, e.password, e.email, e.age, e.admin, e.dept\_id, d.id d\_id, d.name d\_name, d.sn d\_sn from employee e join department d on e.dept\_id = d.id WHERE (name like concat(’%’, ? ,’%’) or e.email like concat(’%’, ? ,’%’)) limit ?, ?  
\#\#\# Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ‘name’ in where clause is ambiguous  
; \]; Column ‘name’ in where clause is ambiguous; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ‘name’ in where clause is ambiguous\] with root cause  
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column ‘name’ in where clause is ambiguous  
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)  
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)  
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)  
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)  
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:936)  
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)  
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)  
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)  
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)  
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)  
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)  
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)  
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)  
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
at java.base/java.lang.reflect.Method.invoke(Method.java:566)  
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)  
at com.sun.proxy. P r o x y 53. e x e c u t e ( U n k n o w n S o u r c e ) a t o r g . a p a c h e . i b a t i s . e x e c u t o r . s t a t e m e n t . P r e p a r e d S t a t e m e n t H a n d l e r . q u e r y ( P r e p a r e d S t a t e m e n t H a n d l e r . j a v a : 63 ) a t o r g . a p a c h e . i b a t i s . e x e c u t o r . s t a t e m e n t . R o u t i n g S t a t e m e n t H a n d l e r . q u e r y ( R o u t i n g S t a t e m e n t H a n d l e r . j a v a : 79 ) a t o r g . a p a c h e . i b a t i s . e x e c u t o r . S i m p l e E x e c u t o r . d o Q u e r y ( S i m p l e E x e c u t o r . j a v a : 63 ) a t o r g . a p a c h e . i b a t i s . e x e c u t o r . B a s e E x e c u t o r . q u e r y F r o m D a t a b a s e ( B a s e E x e c u t o r . j a v a : 324 ) a t o r g . a p a c h e . i b a t i s . e x e c u t o r . B a s e E x e c u t o r . q u e r y ( B a s e E x e c u t o r . j a v a : 156 ) a t o r g . a p a c h e . i b a t i s . e x e c u t o r . C a c h i n g E x e c u t o r . q u e r y ( C a c h i n g E x e c u t o r . j a v a : 109 ) a t o r g . a p a c h e . i b a t i s . e x e c u t o r . C a c h i n g E x e c u t o r . q u e r y ( C a c h i n g E x e c u t o r . j a v a : 83 ) a t o r g . a p a c h e . i b a t i s . s e s s i o n . d e f a u l t s . D e f a u l t S q l S e s s i o n . s e l e c t L i s t ( D e f a u l t S q l S e s s i o n . j a v a : 148 ) a t o r g . a p a c h e . i b a t i s . s e s s i o n . d e f a u l t s . D e f a u l t S q l S e s s i o n . s e l e c t L i s t ( D e f a u l t S q l S e s s i o n . j a v a : 141 ) a t j a v a . b a s e / j d k . i n t e r n a l . r e f l e c t . N a t i v e M e t h o d A c c e s s o r I m p l . i n v o k e 0 ( N a t i v e M e t h o d ) a t j a v a . b a s e / j d k . i n t e r n a l . r e f l e c t . N a t i v e M e t h o d A c c e s s o r I m p l . i n v o k e ( N a t i v e M e t h o d A c c e s s o r I m p l . j a v a : 62 ) a t j a v a . b a s e / j d k . i n t e r n a l . r e f l e c t . D e l e g a t i n g M e t h o d A c c e s s o r I m p l . i n v o k e ( D e l e g a t i n g M e t h o d A c c e s s o r I m p l . j a v a : 43 ) a t j a v a . b a s e / j a v a . l a n g . r e f l e c t . M e t h o d . i n v o k e ( M e t h o d . j a v a : 566 ) a t o r g . m y b a t i s . s p r i n g . S q l S e s s i o n T e m p l a t e Proxy53.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 org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:566) at org.mybatis.spring.SqlSessionTemplate Proxy53.execute(UnknownSource)atorg.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)atorg.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)atorg.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)atorg.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)atorg.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)atorg.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)atorg.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)atorg.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)atorg.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)atjava.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)atjava.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)atjava.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)atjava.base/java.lang.reflect.Method.invoke(Method.java:566)atorg.mybatis.spring.SqlSessionTemplateSqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)  
at com.sun.proxy. P r o x y 44. s e l e c t L i s t ( U n k n o w n S o u r c e ) a t o r g . m y b a t i s . s p r i n g . S q l S e s s i o n T e m p l a t e . s e l e c t L i s t ( S q l S e s s i o n T e m p l a t e . j a v a : 230 ) a t o r g . a p a c h e . i b a t i s . b i n d i n g . M a p p e r M e t h o d . e x e c u t e F o r M a n y ( M a p p e r M e t h o d . j a v a : 137 ) a t o r g . a p a c h e . i b a t i s . b i n d i n g . M a p p e r M e t h o d . e x e c u t e ( M a p p e r M e t h o d . j a v a : 75 ) a t o r g . a p a c h e . i b a t i s . b i n d i n g . M a p p e r P r o x y . i n v o k e ( M a p p e r P r o x y . j a v a : 59 ) a t c o m . s u n . p r o x y . Proxy44.selectList(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) at com.sun.proxy. Proxy44.selectList(UnknownSource)atorg.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)atorg.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)atorg.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)atorg.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)atcom.sun.proxy.Proxy45.selectAll(Unknown Source)  
at com.yy.homework.service.impl.EmployeeServiceImpl.selectAll(EmployeeServiceImpl.java:32)  
at com.yy.homework.web.controller.EmployeeController.selectAll(EmployeeController.java:36)  
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
at java.base/java.lang.reflect.Method.invoke(Method.java:566)  
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209)  
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)  
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)  
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877)  
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783)  
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)  
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991)  
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925)  
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974)  
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:877)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)  
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)  
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)  
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)  
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)  
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)  
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)  
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)  
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)  
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)  
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)  
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)  
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)  
at org.apache.coyote.AbstractProtocol A b s t r a c t C o n n e c t i o n H a n d l e r . p r o c e s s ( A b s t r a c t P r o t o c o l . j a v a : 589 ) a t o r g . a p a c h e . t o m c a t . u t i l . n e t . J I o E n d p o i n t AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint AbstractConnectionHandler.process(AbstractProtocol.java:589)atorg.apache.tomcat.util.net.JIoEndpointSocketProcessor.run(JIoEndpoint.java:312)  
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)  
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)  
at java.base/java.lang.Thread.run(Thread.java:834)

## 二、解决办法 ##

### 1、造成原因 ###

![在这里插入图片描述][20210527114056962.png]  
SQL语法错误，where 后面出现模棱两可的字段 name ，由于employee 和 department 中都存在 name，数据库不能识别你查询的是哪一个 name，所以报错。

### 2、解决 ###

把 SQL 语句中 where 后面的 name 改成 e.name 或者 d.name，根据你需求来就好。

[20210527114056962.png]: /images/20221014/258ca440649e40cb80686e69ea535b96.png