ShardingSphere：SpringBoot+MyBatis+ShardingSphere实现分表分库

Black_Sir00

已于 2023-08-01 14:02:32 修改

阅读量584

点赞数

于 2023-07-30 11:41:54 首次发布

本文链接：https://blog.csdn.net/u013075337/article/details/131991307

版权

SpringBoot+MyBatis+ShardingSphere实现分表分库

ERROR 23316 --- [reate-872736196] com.alibaba.druid.pool.DruidDataSource : create connection SQLException, url: jdbc:mysql://localhost:3306/testdb0?characterEncoding=utf-8, errorCode 0, state 01S00

java.sql.SQLException: The server time zone value '�й��׼ʱ��' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

报错原因：原因是为驱动包里面的时区和数据的时区不一致的引起的，SpringBoot整合MySQL的依赖包版本过高,在高版本的MySQL依赖中数据库和系统时区差异所造成的。系统为SQL默认美国时间,而我们中国要比他们迟8小时,因此将时区设置为当前系统时区即可,采用+8:00格式

解决办法：在数据库连接语句中加入相应信息&serverTimezone=GMT

url=jdbc:mysql://localhost:3306/testdb0?characterEncoding=utf8&serverTimezone=GMT

2023-07-29 10:02:18.732 ERROR 21984 --- [reate-925976643] com.alibaba.druid.pool.DruidDataSource : create connection SQLException, url: jdbc:mysql://localhost:3306/testdb0?characterEncoding=utf-8?serverTimezone=UTC, errorCode 0, state S1009

java.sql.SQLException: Unsupported character encoding 'utf-8?serverTimezone=UTC'

报错原因：由“character encoding 'utf-8”可分析应该是连接配置上出了问题

解决办法：1、把原来的“ ？ ”改为“ & ”，2、把原来的“ utf-8 ” 改为 “ utf8 ”

url=jdbc:mysql://localhost:3306/testdb0?characterEncoding=utf8&serverTimezone=GMT

2023-07-29 10:18:02.201 ERROR 15004 --- [reate-355977028] com.alibaba.druid.pool.DruidDataSource : create connection SQLException, url: jdbc:mysql://localhost:3306/testdb1?characterEncoding=utf8&serverTimezone=GMT, errorCode 1049, state 42000

java.sql.SQLSyntaxErrorException: Unknown database 'testdb1'

报错原因：针对报错中提示的com.alibaba的jar包推测，如果是jar包有问题

解决办法：com.alibaba的jar包升级为1.2.6版本

2023-07-29 22:10:34.932 ERROR 17032 --- [ main] o.s.boot.SpringApplication : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration': Initialization of bean failed; nested exception is org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Can't find datasource type!

报错原因：没有引入数据库连接池组件com.alibaba

2023-07-29 22:37:05.074 ERROR 3524 --- [ main] o.s.boot.SpringApplication : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [org/mybatis/spring/boot/autoconfigure/MybatisAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.apache.ibatis.session.SqlSessionFactory]: Factory method 'sqlSessionFactory' threw exception; nested exception is java.io.FileNotFoundException: class path resource [mybatis-config.xml] cannot be opened because it does not exist

报错原因：mybatis-config.xml配置文件不存在

2023-07-29 23:36:55.089 ERROR 7152 --- [ main] o.s.boot.SpringApplication : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [org/mybatis/spring/boot/autoconfigure/MybatisAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.apache.ibatis.session.SqlSessionFactory]: Factory method 'sqlSessionFactory' threw exception; nested exception is java.lang.IllegalStateException: Property 'configuration' and 'configLocation' can not specified with together

报错原因：在配置文件中指定了：config-location，然后我们又配置了关于configuration的信息，导致的报错。大体意思就是，二者只能选择一种，要么指定config-location我们自己的xml配置文件，要么我们就使用Mybatis的start提供的配置。
如果两者都存在，那么他就会包冲突。

解决方案：把config-location删掉

ERROR 3420 --- [nio-8084-exec-8] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.NullPointerException] with root cause

Cannot find local variable 'hashMapMenu'

[org/springframework/boot/autoconfigure/web/ServerPropertiesAutoConfiguration.class] cannot be opened because it does not exist

springcloud使用hystrix断路器错误
org.springframework.beans.factory.BeanDefinitionStoreException: Failed to process import candidates for configuration class [com.carfi.springcustormerservice.SpringcustormerserviceApplication]; nested exception is java.io.FileNotFoundException: class path resource [org/springframework/boot/autoconfigure/web/ServerPropertiesAutoConfiguration.class] cannot be opened because it does not exist
原因： spring2.x.x>=2.0.0已经去掉了org/springframework/boot/autoconfigure/web/ServerPropertiesAutoConfiguration.class，如果导入的Jar包需要依赖这个就会报错

项目依赖

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.cloud.demo.user</groupId>
    <artifactId>springcloud-user-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springcloud-user-demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-consul-discovery</artifactId>
            <version>2.2.1.RELEASE</version>
        </dependency>
        <!-- mybatis-springboot-starter-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- sharding-jdbc依赖 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>


        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

配置文件

server.port=8084
spring.application.name=user-demo
#×¢²áÖÐÐÄ·ÃÎÊºÍ¶Ë¿Ú
spring.cloud.consul.host=localhost
spring.cloud.consul.port=8500
#×¢²áµ½consulµÄ·þÎñÃû³Æ
spring.cloud.consul.discovery.serviceName=${spring.application.name}

#mybatisµÄÏà¹ØÅäÖÃ
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath:mapper/*.xml

###############################ShardingSphereÊµÏÖ·Ö¿â·Ö±í###############################
#Ö¸¶¨mybatisÐÅÏ¢
#mybatis.config-location=classpath:mybatis-config.xml
#´òÓ¡sql
spring.shardingsphere.props.sql.show=true
#Êý¾ÝÔ´ÅäÖÃ
spring.shardingsphere.datasource.names=testdb0,testdb1
#Êý¾ÝÔ´testdb0ÅäÖÃ
spring.shardingsphere.datasource.testdb0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.testdb0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.testdb0.url=jdbc:mysql://localhost:3306/testdb0?characterEncoding=utf8&serverTimezone=GMT
spring.shardingsphere.datasource.testdb0.username=root
spring.shardingsphere.datasource.testdb0.password=root
#Êý¾ÝÔ´testdb1ÅäÖÃ
spring.shardingsphere.datasource.testdb1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.testdb1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.testdb1.url=jdbc:mysql://localhost:3306/testbd1?characterEncoding=utf8&serverTimezone=GMT
spring.shardingsphere.datasource.testdb1.username=root
spring.shardingsphere.datasource.testdb1.password=root
#·Ö¿â²ßÂÔ
#¸ù¾ÝÄêÁä·Ö¿â
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=age
#配置分库策略,主键取模0在0库,1在1库
#分片算法行表达式
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=testdb$->{age % 2}
#·Ö±í²ßÂÔ
#¸ù¾Ýid·Ö±í
#配置分表策略，主键取模，0在0表，1在1表
spring.shardingsphere.sharding.tables.user.actual-data-nodes=testdb$->{0..1}.user$->{0..1}
#分片列名称
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
#分片算法行表达式
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user$->{id % 2}
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
###############################ShardingSphereÊµÏÖ·Ö¿â·Ö±í###############################

Black_Sir00

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
ShardingSphere：SpringBoot+MyBatis+ShardingSphere实现分表分库

原因是为驱动包里面的时区和数据的时区不一致的引起的，SpringBoot整合MySQL的依赖包版本过高,在高版本的MySQL依赖中数据库和系统时区差异所造成的。在配置文件中指定了：config-location，然后我们又配置了关于configuration的信息，导致的报错。大体意思就是，二者只能选择一种，要么指定config-location我们自己的xml配置文件，要么我们就使用Mybatis的start提供的配置。”改为“ & ”，2、把原来的“ utf-8 ” 改为 “ utf8 ”
复制链接

扫一扫