SpringBoot日常：集成shareingsphere-jdbc-EW帮帮网

文章目录

本章内容我们来聊聊如何将shareingsphere-jdbc集成到我们自己的springboot项目中，本章采用的shareingsphere-jdbc版本是5.1.2，springboot项目是2.6.13，下面是具体的集成步骤

pom依赖

引入相关的依赖，包括数据库驱动、mybatis、以及springboot-web等依赖

<?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>
    <groupId>com.cys</groupId>
    <artifactId>test-shareingsphere</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>test-shareingsphere</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>17</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.6.13</spring-boot.version>
        <shardingsphere.version>4.1.1</shardingsphere.version>
        <mybatis-plus-boot-starter.version>3.5.3.1</mybatis-plus-boot-starter.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>${spring-boot.version}</version>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.17.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-slf4j-impl</artifactId>
            <version>2.17.2</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring-boot.version}</version>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring-boot.version}</version>
            <exclusions><!-- 去掉springboot默认配置 -->
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.2</version>
            <exclusions>
                <exclusion>
                    <groupId>log4j</groupId>
                    <artifactId>log4j</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.30</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus-boot-starter.version}</version>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>17</source>
                    <target>17</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring-boot.version}</version>
                <configuration>
                    <mainClass>com.cys.testshareingsphere.TestShareingsphereApplication</mainClass>
                    <skip>true</skip>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

application.yml配置

在启动配置配置好相关的信息，这里我们用了ds0和ds1两个数据源作为分库

spring:
  profiles:
    active: dev
  application:
    name: test-shareingsphere  # 应用名称，对应 Nacos 中的 Data ID
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource 
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.110.178:3307/cys?useSSL=false&useUnicode=true&characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: 121212s4!
      ds1:
        type: com.zaxxer.hikari.HikariDataSource  #com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 如果是用HikariDataSource则key为jdbc-url   如果用druid 则key 为url
        url: jdbc:mysql://192.168.110.178:3307/cys1?useSSL=false&useUnicode=true&characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
        username: root
        password: 121212s4!
    rules:
      sharding:
        tables:
          user: # 逻辑表名
            actual-data-nodes: ds${0..1}.user_${0..1}
            database-strategy: # 分库策略
              standard:
                sharding-column: id
                sharding-algorithm-name: database-inline
            #指定分表策略和分表算法，根据id%分表总数来进行分表    
            table-strategy: 
              standard:
                sharding-column: id
                sharding-algorithm-name: table-inline
            #指定主键生成策略，使用雪花算法进行生成  如果使用雪花算法主键则需要使用bigInt类型，实体类主键需要使用long类型
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds${id % 2}
          table-inline:
            type: INLINE
            props:
              algorithm-expression: user_${id % 2}
    props:
      sql-show: true


server:
  port: 9006

logging:
  config: classpath:log4j2.xml
  level:
    root: info
    org.apache.shardingsphere: debug

log4j2.xml

这里把log配置也列出来，主要用到了sql日志打印

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss} [%t] %-5level %logger{36} - %msg%n"/>
        </Console>
    </Appenders>

    <Loggers>
        <!-- 设置 ShardingSphere SQL 日志为 DEBUG -->
        <Logger name="org.apache.shardingsphere" level="DEBUG" additivity="false">
            <AppenderRef ref="Console"/>
        </Logger>

        <Root level="INFO">
            <AppenderRef ref="Console"/>
        </Root>
    </Loggers>
</Configuration>

实体类

@Data
@TableName(value="user")
public class UserEntity implements Serializable {

    @TableId(type = IdType.ASSIGN_ID)
    private Long id;

    private String name;
}

Mapper

mapper 接口，集成mybatis的基础接口

@Mapper
public interface UserMapper extends BaseMapper<UserEntity> {

    @Select("select * from user where id = #{id}")
    UserEntity selectMyUser(Long id);

}

Service

service 具体的一个逻辑，分别是获取数据和插入数据的两个方法

@Service
@Slf4j
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    public void getUser(Long id) {
        UserEntity userEntity = userMapper.selectMyUser(id);
        System.out.println(userEntity);
    }

    @Override
    public void createUser(String name) {
        UserEntity userEntity = new UserEntity();
        userEntity.setName(name);
        userMapper.insert(userEntity);
    }
}

controller

controller开放两个查询和写入的接口，用于测试调用

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/getUser")
    public String getUser(@RequestParam("id") Long id, HttpServletRequest request) {
        userService.getUser(id);
        return "getUser";
    }

    @GetMapping("/createUser")
    public String getUser(@RequestParam("name") String name, HttpServletRequest request) {
        userService.createUser(name);
        return "true";
    }
}