多租户系统集成
1.2：技术选型
1.1：架构需求
系统集成采用多租户模式，每个租户对应一个数据库实例，
每个数据库实例采用主备模式，主库负责数据写入，备库负责数据读取，
即：数据存储层需要分库+读写分离，分库根据租户id- tenantid
1.2：技术选型
| 技术类别 | 技术选型 |
| 数据库 | KingbaseES V8R6 |
| 数据库连接池 | hikari |
| 数据库操作层 | jpa |
| 基础框架 | springboot 2.5.2 |
2：架构设计
2.1：数据库安装
人大金仓数据库windows单机版安装_你敞开了玩的博客-CSDN博客
2.2：数据库创建
创建两个主库，两个从库，待用，无需创建表格，JPA代码创建自动生成表格。
2.3：创建springboot项目
3：架构集成
3.1：集成shardingjdbc
pom引入shardingjdbc依赖：
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
application.properties配置：
#数据源 master01,master02,slave01,slave02
spring.shardingsphere.datasource.names=master01,master02,slave01,slave02
# master01
spring.shardingsphere.datasource.master01.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master01.driver-class-name=org.postgresql.Driver
spring.shardingsphere.datasource.master01.jdbc-url=jdbc:postgresql://localhost:54321/master01?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.master01.username=admin
spring.shardingsphere.datasource.master01.password=admin
# slave01
spring.shardingsphere.datasource.slave01.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave01.driver-class-name=org.postgresql.Driver
spring.shardingsphere.datasource.slave01.jdbc-url=jdbc:postgresql://localhost:54321/slave01?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.slave01.username=admin
spring.shardingsphere.datasource.slave01.password=admin# 配置01主从库
spring.shardingsphere.sharding.master-slave-rules.db31.master-data-source-name=master01
spring.shardingsphere.sharding.master-slave-rules.db31.slave-data-source-names=slave01# master02
spring.shardingsphere.datasource.master02.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master02.driver-class-name=org.postgresql.Driver
spring.shardingsphere.datasource.master02.jdbc-url=jdbc:postgresql://localhost:54321/master02?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.master02.username=admin
spring.shardingsphere.datasource.master02.password=admin# slave02
spring.shardingsphere.datasource.slave02.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave02.driver-class-name=org.postgresql.Driver
spring.shardingsphere.datasource.slave02.jdbc-url=jdbc:postgresql://localhost:54321/slave02?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.slave02.username=admin
spring.shardingsphere.datasource.slave02.password=admin# 配置02主从库
spring.shardingsphere.sharding.master-slave-rules.db32.master-data-source-name=master02
spring.shardingsphere.sharding.master-slave-rules.db32.slave-data-source-names=slave02
3.2：集成JPA
pom新增JPA依赖
<!-- JPA --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>
增加JPA配置：
#JPA配置 spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL9Dialect spring.jpa.properties.hibernate.hbm2ddl.auto=update spring.jpa.show-sql=true
3.3：集成人大金仓数据库驱动
此处我们安装的人大金仓数据库为：postgres版本，即使用postgres的数据库驱动
<!-- postgresql --> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency>
4: 代码实现
4.1：创建下基本的代码包和相关类
4.2：各类代码示例
entity:
package com.inspur.gs.soc.entity;
import lombok.Data;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;@Data
@Entity
@Table(name = "book")
public class Book {
/**
* 数据库表格主键
*/
@Id
@Column(name = "id", nullable = false, length = 36)
private String id;@Column(name = "name", nullable = true, length = 36)
private String name;@Column(name = "price", nullable = true, length = 100)
private String price;/**
* 分库键
*/
@Column(name = "tenantid", nullable = true, length = 100)
private Integer tenantid;
}
repository:
package com.inspur.gs.soc.repository;
import com.inspur.gs.soc.entity.Book;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface BookRepository extends JpaRepository<Book, String> {
}
iservice:
package com.inspur.gs.soc.service;
import com.inspur.gs.soc.entity.Book;
public interface IBookService {
/**
* 插入书籍信息
* @param book
*/
public void insert(Book book);
}
serviceImpl:
package com.inspur.gs.soc.service.impl;
import com.inspur.gs.soc.entity.Book;
import com.inspur.gs.soc.repository.BookRepository;
import com.inspur.gs.soc.service.IBookService;
import org.springframework.stereotype.Service;import javax.annotation.Resource;
@Service
public class BookServiceImpl implements IBookService {@Resource
private BookRepository bookRepository;
/**
* 插入书籍信息
*
* @param book
*/
@Override
public void insert(Book book) {
bookRepository.saveAndFlush(book);
}
}
controller:
package com.inspur.gs.soc.controller;
import com.inspur.gs.soc.entity.Book;
import com.inspur.gs.soc.service.IBookService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;import javax.annotation.Resource;
@RestController
@RequestMapping("soc")
public class BookController {@Resource
private IBookService bookService;@RequestMapping(value="insertBookInfo", method= RequestMethod.GET)
public void insertBookInfo(){
Book book=new Book();
book.setId("32");
book.setName("三国演义");
book.setPrice("200$");
book.setTenantid(31);
bookService.insert(book);
}
}
application.properties 新增book表策略配置
# 数据源选择策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=tenantid
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=db$->{tenantid%100}
#spring.shardingsphere.sharding.default-data-source-name=db31
spring.shardingsphere.props.sql.show=true#表格策略
spring.shardingsphere.sharding.tables.book.actual-data-nodes=db$->{31..32}.book
5：测试
5.1：请求调试
http://localhost:8080/soc/insertBookInfo
5.2：观察下shardingjdbc的日志打印
5.3：分析
当调用到实现类具体的插入方法时，shardingjdbc首先去从库查询数据是否存在，数据不存在，通过配置的数据库选择策略，计算数据去了真实位置master02，一切好像都实现了，
但是这里出现了一个问题，即：在调用JPA saveandflush方法时，每个从库都查询了一遍，当多租户配置数据源很多时，这里会出现效率问题，问题比较可怕。通过源码分析，调用saveandflush方法时，JPA每次都会根据ID去数据库查询数据是否存在，存在即更新，不存在则插入，没有使用我们的分库分表策略，具体源码自己去看。
接下来我们需要重写JPA的saveandflush方法，说到重写大家可能比较触头，你要记住没有一个框架是完全完美的，该下手时就要毫不犹豫。这次重写我们把JPA更新的毛病也一并处理了。JPA更新的毛病自行百度
6：重写
6.1：新建JPA重写相关代码包
6.2：启动类新增替换原生repository
@EnableJpaRepositories( repositoryBaseClass = JpaRepositoryReBuild.class)
6.4：测试
我们将租户id换成31，观察下日志：
Shardingjdbc通过算法表达式，去01从库查询数据，没有数据，则插入数据到01主库，至此我们集成完毕，如有问题，可私信讨论。
扫一扫
346
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
