You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2019/12/04 10:12:53 UTC
[GitHub] [incubator-shardingsphere] EricLye opened a new issue #3662:
Integrate sharding jdbc with sql server
EricLye opened a new issue #3662: Integrate sharding jdbc with sql server
URL: https://github.com/apache/incubator-shardingsphere/issues/3662
## Can anybody help me?
I have worked on it for about 3 days,and it didnot work yet.
Here is my yaml
```
server:
port: 8081
spring:
shardingsphere:
sharding:
default-database-strategy:
inline:
algorithm-expression: db$->{id % 3}
sharding-column: id
tables:
book:
#由数据源名 + 表名组成，以小数点分隔。
#多个表以逗号分隔，支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点。
#用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
actual-data-nodes: db$->{0..2}.book_$->{0..1}
# key-generator:
# #自增列名称，缺省表示不使用自增主键生成器
# column: id
# #自增列值生成器类型，缺省表示使用默认自增列值生成器。可使用用户自定义的列值生成器或选择内置类型：SNOWFLAKE/UUID/LEAF_SEGMENT
# type: SNOWFLAKE
table-strategy:
inline:
algorithm-expression: book_$->{id % 2}
sharding-column: id
binding-tables: book
datasource:
db0:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=db0
password:
type: com.alibaba.druid.pool.DruidDataSource
username: sa
db1:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=db1
password:
type: com.alibaba.druid.pool.DruidDataSource
username: sa
db2:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=db2
password:
type: com.alibaba.druid.pool.DruidDataSource
username: sa
dsmaster: ''
names: db0,db1,db2
props:
sql:
show: true
main:
allow-bean-definition-overriding: true
mybatis-plus:
typeAliasesPackage: cn.wccy.sharding.shardingtest.entity
mapper-locations:
- classpath*:mapper/*.xml
```
And the maven pom is below:`mssql-jdbc 6.4.0.jre8`,`sharding-jdbc-spring-boot-starter 4.0.0-RC2`
```
<!-- Sharding jdbc -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.1.1</version>
<!--<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</exclusion>
</exclusions>-->
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.4.0.jre8</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.58</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.18</version>
</dependency>
```
And I have tried mysql also.It works well.I do not know why.
Here is the test code below
```
@Test
public void saveBatch() {
List<Book> bookList = new ArrayList<>();
for (int i = 0;i < 100;i ++) {
Book book = new Book();
book.setId(i);
book.setCount(i);
book.setName("test" + i);
bookList.add(book);
}
bookService.saveBatch(bookList);
}
```
and the exception stack trace:
```
org.apache.ibatis.exceptions.PersistenceException:
### Error flushing statements. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1.
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:254)
at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.saveBatch(ServiceImpl.java:132)
at com.baomidou.mybatisplus.extension.service.IService.saveBatch(IService.java:58)
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$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at org.springframework.transaction.interceptor.TransactionInterceptor$$Lambda$519/1914538677.proceedWithInvocation(Unknown Source)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
at cn.wccy.sharding.shardingtest.service.impl.BookServiceImpl$$EnhancerBySpringCGLIB$$2f2168f1.saveBatch(<generated>)
at cn.wccy.sharding.shardingtest.service.BookServiceTest.saveBatch(BookServiceTest.java:61)
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:483)
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.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
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:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
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:190)
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.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildParamTypeDefinitions(SQLServerPreparedStatement.java:377)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildPreparedStrings(SQLServerPreparedStatement.java:329)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:2607)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:2545)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7344)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2458)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeBatch(DruidPooledPreparedStatement.java:565)
at org.apache.shardingsphere.shardingjdbc.executor.BatchPreparedStatementExecutor$4.executeSQL(BatchPreparedStatementExecutor.java:168)
at org.apache.shardingsphere.shardingjdbc.executor.BatchPreparedStatementExecutor$4.executeSQL(BatchPreparedStatementExecutor.java:164)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:68)
at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:57)
at org.apache.shardingsphere.core.execute.ShardingExecuteEngine.syncGroupExecute(ShardingExecuteEngine.java:125)
at org.apache.shardingsphere.core.execute.ShardingExecuteEngine.serialExecute(ShardingExecuteEngine.java:90)
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:132)
at org.apache.shardingsphere.shardingjdbc.executor.BatchPreparedStatementExecutor.executeBatch(BatchPreparedStatementExecutor.java:171)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.executeBatch(ShardingPreparedStatement.java:231)
at com.baomidou.mybatisplus.core.executor.MybatisBatchExecutor.doFlushStatements(MybatisBatchExecutor.java:132)
at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:129)
at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:122)
at org.apache.ibatis.executor.CachingExecutor.flushStatements(CachingExecutor.java:114)
at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:252)
... 45 more
```
I need help,really.
----------------------------------------------------------------
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:
users@infra.apache.org
With regards,
Apache Git Services