Andrew-Yang999 opened a new issue #7381:
URL: https://github.com/apache/shardingsphere/issues/7381
**Thank you check my questions.
I have a question. It's " Column 'ID' specified twice". When I use
ShardingJdbc+mybatis+mysql save some data, Would I use ShardingJdbc's function:
built-in Snowflake, Here is my properties:**
`
spring.profiles=sit
#当遇到同样名字的时候，是否允许覆盖注册
spring.main.allow-bean-definition-overriding=true
#数据分片配置
spring.shardingsphere.datasource.names=ds0,ds1
#ds0
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456
#ds1
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
#默认分片规则
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=age
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{age
% 2}
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->{0..1}.t_user$->{0..1}
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=ds$->{age
% 2}
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=score
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$->{score
% 2}
spring.shardingsphere.props.sql.low=true
spring.shardingsphere.sharding.tables.t_user.key-generator.column=ID
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
`
**I wrote something about ID, meanwhile, I implement "ShardingKeyGenerator"
class, here is my class:**
`
@Data
public class SimpleShardingKeyGenerator implements ShardingKeyGenerator {
private AtomicLong atomic = new AtomicLong(0);
private Properties properties = new Properties();
@Override
public Comparable<?> generateKey() {
// return atomic.incrementAndGet();
SnowflakeIdUtil snowflakeIdUtil = new SnowflakeIdUtil();
return snowflakeIdUtil.nextId();
}
@Override
public String getType() {
//声明类型
return "SIMPLE";
}
}
`
**After then I save some data , here is my datas:**
`
userList.add(new UserEntity("大大", 4, 8));
userList.add(new UserEntity("小小", 3, 3));
userList.add(new UserEntity("爸爸", 31, 1));
userList.add(new UserEntity("妈妈", 28, 0));
userList.add(new UserEntity("爷爷", 63, 2));
userList.add(new UserEntity("奶奶", 62, 7));
`
**And my SQL:**
`
CREATE TABLE IF NOT EXISTS `T_USER` (
`ID` BIGINT NOT NULL,
`USER_NAME` VARCHAR(20) NOT NULL,
`AGE` int NOT NULL DEFAULT 0,
`SCORE` int NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
`
**But, I got a bug, here is the bug:**
`
Closing non transactional SqlSession
[org.apache.ibatis.session.defaults.DefaultSqlSession@426c5120]
[ ERROR] [2020-09-10 16:26:50.055] [] [http-nio-8087-exec-3]
o.a.catalina.core.ContainerBase.[Tomcat].[localhost].[/sharding-sphere-demo].[dispatcherServlet]
[175] - Servlet.service() for servlet [dispatcherServlet] in context with path
[/sharding-sphere-demo] threw exception [Request processing failed; nested
exception is org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLSyntaxErrorException:
Column 'ID' specified twice
### The error may exist in file
[D:\myCode\dream\sharding-sphere-demo\target\classes\mapper\t\UserMapper.xml]
### The error may involve
com.example.shardingspheredemo.model.t.mapper.UserMapper.insertForeach-Inline
### The error occurred while setting parameters
### SQL: insert into t_user (id,user_name,age,score) values
(?, ?,?,?) , (?,
?,?,?) , (?, ?,?,?) ,
(?, ?,?,?) , (?, ?,?,?)
, (?, ?,?,?)
### Cause: java.sql.SQLSyntaxErrorException: Column 'ID' specified twice
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException:
Column 'ID' specified twice] with root cause
java.sql.SQLSyntaxErrorException: Column 'ID' specified twice
at
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at
com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at
com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
at
com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at
com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at
org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:65)
at
org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:61)
at
org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:69)
at
org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
at
org.apache.shardingsphere.core.execute.ShardingExecuteEngine.syncGroupExecute(ShardingExecuteEngine.java:126)
at
org.apache.shardingsphere.core.execute.ShardingExecuteEngine.parallelExecute(ShardingExecuteEngine.java:103)
at
org.apache.shardingsphere.core.execute.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:83)
at
org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:73)
at
org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:56)
at
org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:128)
at
org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:156)
at
org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:172)
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.$Proxy83.execute(Unknown Source)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
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.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
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)
at com.sun.proxy.$Proxy67.insert(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271)
at
com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:68)
at
com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64)
at com.sun.proxy.$Proxy68.insertForeach(Unknown Source)
at
com.example.shardingspheredemo.model.t.service.impl.UserServiceImpl.insertForeach(UserServiceImpl.java:30)
at
com.example.shardingspheredemo.model.t.service.impl.UserServiceImpl$$FastClassBySpringCGLIB$$bfa7d93d.invoke(<generated>)
at
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)
at
com.example.shardingspheredemo.model.t.service.impl.UserServiceImpl$$EnhancerBySpringCGLIB$$f0b96ca3.insertForeach(<generated>)
at
com.example.shardingspheredemo.model.t.controller.UserController.saveUser(UserController.java:51)
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:190)
at
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
at
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
at
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
at
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
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:53)
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:100)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
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.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
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:201)
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
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:202)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at
org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
at
org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at
org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
at
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1589)
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)
`
**Please tell me my fault, Thank you.**
@tristaZero
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
