search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.87k stars 6.73k forks source link

Integrate sharding jdbc with sql server #3662

Closed EricLye closed 4 years ago

EricLye commented 4 years ago

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.

terrymanu commented 4 years ago

The exception is thrown form SQLServer, can you google the exception first?

The value is not set for the parameter number 1.

EricLye commented 4 years ago

But all the parameters are set. Here is the Book.

@EqualsAndHashCode(callSuper = true)
@Data
@TableName("book")
public class Book extends Model<Book> {
    @TableId(value = "id",type = IdType.INPUT)
    private Integer id;
    @TableField(value = "name")
    private String name;
    @TableField(value = "count")
    private Integer count;
}

and my test code.

@Test
public void saveBatch() {
    List<Book> bookList = new ArrayList<>();
    for (int i = 0;i < 10;i ++) {
        Book book = new Book();
        book.setId(i);
        book.setCount(i);
        book.setName("test" + i);
        bookList.add(book);
    }
    bookService.saveBatch(bookList);
}

Besides,I tried druid + sqlserver,without sharding jdbc,and it works.So I blame this on sharding jdbc.Am I wrong? And I accept your suggestion,I will debug the sqlserver driver. Maybe this will force me to quit sqlserver ,to use mysql.

EricLye commented 4 years ago

I think I found something.In the shardingPreparedStatement,parameters are empty. 微信图片_20191205102445 But in org.apache.ibatis.mapping.MappedStatement,there are the parameters.Look at the picture below. 微信图片_20191205103543

EricLye commented 4 years ago

I think , it is my yaml ,maybe.

EricLye commented 4 years ago

Three is an important information:

line 3:0 no viable alternative at input '(id,name,count'

It is printed at org.antlr.v4.runtime.ConsoleErrorListener#syntaxError Is it a rule parse error?

EricLye commented 4 years ago

There is nothing I can do for it.At least give me a useful suggestion.please.

terrymanu commented 4 years ago

It looks like parse problem, does anybody interesting this issue?

SteNicholas commented 4 years ago

@terrymanu Please assign the SQL parse bug to me. I will be responsible for this module bugs.

terrymanu commented 4 years ago

Thank you, assigned