Skip to content

why it has two join? #100

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
jiatongwu opened this issue Jun 27, 2019 · 3 comments
Closed

why it has two join? #100

jiatongwu opened this issue Jun 27, 2019 · 3 comments

Comments

@jiatongwu
Copy link

jiatongwu commented Jun 27, 2019 •
edited by jeffgbutler
Loading 

QueryExpressionDSL<SelectModel>.JoinSpecificationFinisher on = SqlBuilder
							.select(StudentDynamicSqlSupport.id, StudentDynamicSqlSupport.name,
									StudentDynamicSqlSupport.idcard)
							.from(StudentDynamicSqlSupport.student).join(StudentRegDynamicSqlSupport.studentReg)
							.on(StudentDynamicSqlSupport.id, SqlBuilder.equalTo(StudentRegDynamicSqlSupport.studentid));
					// build()
					// render(RenderingStrategy.MYBATIS3)
					if ((byIdcard) && (byRegCode) && (byExamcode)) {
						if (StringUtils.isBlank(examcodeCellString) || StringUtils.isBlank(regCodeCellString)
								|| StringUtils.isBlank(idcardCellString)) {
							errorMsgStringBuilder.append(" 准考证号不能为空 报名号不能为空 身份证号不能为空 ");
							isDataFormatOk = false;
						} else {
							on.where(StudentDynamicSqlSupport.idcard, SqlBuilder.isEqualTo(idcardCellString))
									.and(StudentRegDynamicSqlSupport.examnumber,
											SqlBuilder.isEqualTo(examcodeCellString))
									.and(StudentRegDynamicSqlSupport.regcode, SqlBuilder.isEqualTo(regCodeCellString));
						}
					} else if ((!byIdcard) && (byRegCode) && (byExamcode)) {
						if (StringUtils.isBlank(examcodeCellString) || StringUtils.isBlank(regCodeCellString)) {
							errorMsgStringBuilder.append(" 准考证号不能为空 报名号不能为空  ");
							isDataFormatOk = false;
						} else {
							on.where(StudentRegDynamicSqlSupport.examnumber, SqlBuilder.isEqualTo(examcodeCellString))
									.and(StudentRegDynamicSqlSupport.regcode, SqlBuilder.isEqualTo(regCodeCellString));
						}
					} else if ((byIdcard) && (!byRegCode) && (byExamcode)) {
						if (StringUtils.isBlank(examcodeCellString) || StringUtils.isBlank(idcardCellString)) {
							errorMsgStringBuilder.append(" 准考证号不能为空 身份证号不能为空 ");
							isDataFormatOk = false;
						} else {
							on.where(StudentDynamicSqlSupport.idcard, SqlBuilder.isEqualTo(idcardCellString)).and(
									StudentRegDynamicSqlSupport.examnumber, SqlBuilder.isEqualTo(examcodeCellString));
						}
					} else if ((byIdcard) && (byRegCode) && (!byExamcode)) {
						if (StringUtils.isBlank(idcardCellString) || StringUtils.isBlank(regCodeCellString)) {
							errorMsgStringBuilder.append("  报名号不能为空 身份证号不能为空 ");
							isDataFormatOk = false;
						} else {
							on.where(StudentDynamicSqlSupport.idcard, SqlBuilder.isEqualTo(idcardCellString))
									.and(StudentRegDynamicSqlSupport.regcode, SqlBuilder.isEqualTo(regCodeCellString));
						}
					} else if ((!byIdcard) && (!byRegCode) && (byExamcode)) {
						if (StringUtils.isBlank(examcodeCellString)) {
							errorMsgStringBuilder.append(" 准考证号不能为空  ");
							isDataFormatOk = false;
						} else {
							on.where(StudentRegDynamicSqlSupport.examnumber, SqlBuilder.isEqualTo(examcodeCellString));
						}
					} else if ((byIdcard) && (!byRegCode) && (!byExamcode)) {
						if (StringUtils.isBlank(idcardCellString)) {
							errorMsgStringBuilder.append("  身份证号不能为空 ");
							isDataFormatOk = false;
						} else {
							on.where(StudentDynamicSqlSupport.idcard, SqlBuilder.isEqualTo(idcardCellString));
						}
					} else if ((!byIdcard) && (byRegCode) && (!byExamcode)) {
						if (StringUtils.isBlank(regCodeCellString)) {
							errorMsgStringBuilder.append(" 报名号不能为空  ");
							isDataFormatOk = false;
						} else {
							on.where(StudentRegDynamicSqlSupport.regcode, SqlBuilder.isEqualTo(regCodeCellString));
						}
					}
					if (isDataFormatOk) {
						SelectStatementProvider selectStatementProvider = on.build().render(RenderingStrategy.MYBATIS3);
						List<Student> students = studentDynamicSqlMapper.selectMany(selectStatementProvider);
						if (students.size() == 1) {
							studentId = students.get(0).getId();
							student = students.get(0);
						} else {
							errorMsgStringBuilder.append(" 此考生不存在  ");
							isDataFormatOk = false;
						}
					}

2019-06-27 14:45:01.513 DEBUG 19673 --- [io-18080-exec-1] c.x.m.S.selectMany : ==> Preparing: select student.id, student.name, student.idcard from student join student_reg on student.id = student_reg.studentId join student_reg on student.id = student_reg.studentId
2019-06-27 14:45:01.520 DEBUG 19673 --- [io-18080-exec-1] c.x.m.S.selectMany : ==> Parameters:
2019-06-27 14:45:01.818 INFO 19673 --- [io-18080-exec-1] o.s.b.f.xml.XmlBeanDefinitionReader : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
org.springframework.jdbc.BadSqlGrammarException:

Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Not unique table/alias: 'student_reg'

The error may exist in cn/xvkang/mapperdynamicsql/StudentDynamicSqlMapper.java (best guess)

The error may involve cn.xvkang.mapperdynamicsql.StudentDynamicSqlMapper.selectMany-Inline

The error occurred while setting parameters

SQL: select student.id, student.name, student.idcard from student join student_reg on student.id = student_reg.studentId join student_reg on student.id = student_reg.studentId

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Not unique table/alias: 'student_reg'

; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Not unique table/alias: 'student_reg'
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.$Proxy85.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.$Proxy97.selectMany(Unknown Source)
at cn.xvkang.service.impl.ExcelAutoLuquServiceImpl.excelAutoLuqu(ExcelAutoLuquServiceImpl.java:211)
at cn.xvkang.controller.ExcelAutoLuquController.excelAutoLuqu(ExcelAutoLuquController.java:28)
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.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:854)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:765)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:101)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at cn.xvkang.securiy.ImageValidateCodeFilter.doFilterInternal(ImageValidateCodeFilter.java:38)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:347)
at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:263)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:798)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:808)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Not unique table/alias: 'student_reg'
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:423)
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:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
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:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242)
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.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
at com.sun.proxy.$Proxy119.execute(Unknown Source)
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.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy120.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:326)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:108)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
at com.sun.proxy.$Proxy116.query(Unknown Source)
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)
@jeffgbutler
Copy link
Member

jeffgbutler commented Jun 27, 2019 •
edited
Loading

I've recreated this and there is definitely something to fix here - the query should only have one join specification for sure.

You can resolve it now.... the calls to on.where(...) return an object of type QueryExpressionDSL<SelectModel>.QueryExpressionWhereBuilder. If you call the build() method on that object, you will get the correct output.

Your logic here is pretty complex - you will also need to make sure that you don't call the where method more than once or you might get other strange results.

jeffgbutler referenced this issue in jeffgbutler/mybatis-dynamic-sql Jul 2, 2019
@jeffgbutler
Merge branch 'master' into gh-100
aaf9cd8 
Conflicts:
	src/main/java/org/mybatis/dynamic/sql/select/QueryExpressionDSL.java
	src/main/java/org/mybatis/dynamic/sql/select/SelectDSL.java
@jeffgbutler jeffgbutler mentioned this issue Jul 2, 2019
Merged
@jeffgbutler
Copy link
Member

Resolved via #106

@jeffgbutler jeffgbutler mentioned this issue Jul 2, 2019
Closed
@jeffgbutler
Copy link
Member

@jiatongwu a fix for this is released and available now in version 1.1.2

This page has an example of how I think the new version could make your code a bit cleaner: http://www.mybatis.org/mybatis-dynamic-sql/docs/complexQueries.html

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@jeffgbutler @jiatongwu