用英文字母做网站关键词,成都建设网站制作,网站目录权限 user,西部数码注册域名1 分表分库
LogicTable
数据分片的逻辑表#xff0c;对于水平拆分的数据库(表)#xff0c;同一类表的总称。
订单信息表拆分为2张表,分别是t_order_0、t_order_1#xff0c;他们的逻辑表名为t_order。
ActualTable
在分片的数据库中真实存在的物理表。即上个示例中的t_…1 分表分库
LogicTable
数据分片的逻辑表对于水平拆分的数据库(表)同一类表的总称。
订单信息表拆分为2张表,分别是t_order_0、t_order_1他们的逻辑表名为t_order。
ActualTable
在分片的数据库中真实存在的物理表。即上个示例中的t_order_0、t_order_1。
DataNode
数据分片的最小单元。由数据源名称和数据表组成例test_msg0.t_order_0。配置时默认各个分片数据库的表结构均相同直接配置逻辑表和真实表对应关系即可。
ShardingColumn
分片字段。用于将数据库(表)水平拆分的关键字段。SQL中如果无分片字段将执行全路由性能较差。Sharding-JDBC支持多分片字段。
ShardingAlgorithm
分片算法。Sharding-JDBC通过分片算法将数据分片支持通过等号、BETWEEN和IN分片。分片算法目前需要业务方开发者自行实现可实现的灵活度非常高。未来Sharding-JDBC也将会实现常用分片算法如rangehash和tag等。 2 自定义分片模式
2.1 数据库表结构
创建ds_0数据库新建表如下
CREATE TABLE t_order_0 (order_id bigint(20) NOT NULL,user_id bigint(20) NOT NULL,PRIMARY KEY (order_id)
) ENGINEInnoDB DEFAULT CHARSETutf8 COLLATEutf8_bin;CREATE TABLE t_order_1 (order_id bigint(20) NOT NULL,user_id bigint(20) NOT NULL,PRIMARY KEY (order_id)
) ENGINEInnoDB DEFAULT CHARSETutf8 COLLATEutf8_bin; 3 新建maven项目 parentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion2.0.3.RELEASE/versionrelativePath / !-- lookup parent from repository --/parentpropertiesproject.build.sourceEncodingUTF-8/project.build.sourceEncodingproject.reporting.outputEncodingUTF-8/project.reporting.outputEncodingjava.version1.8/java.version/propertiesdependencies!-- jpa --dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-data-jpa/artifactId/dependencydependencygroupIdorg.mybatis.spring.boot/groupIdartifactIdmybatis-spring-boot-starter/artifactIdversion1.3.2/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdscoperuntime/scope/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdoptionaltrue/optional/dependency!-- 引入shardingjdbc依赖信息 --dependencygroupIdio.shardingjdbc/groupIdartifactIdsharding-jdbc-core/artifactIdversion2.0.3/version/dependencydependencygroupIdcom.dangdang/groupIdartifactIdsharding-jdbc-self-id-generator/artifactIdversion1.4.2/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactId/dependencydependencygroupIdcom.alibaba/groupIdartifactIddruid/artifactIdversion1.0.12/version/dependency/dependenciesbuildpluginsplugingroupIdorg.springframework.boot/groupIdartifactIdspring-boot-maven-plugin/artifactId/plugin/plugins/build4 application配置
###数据库访问连接
spring:jdbc:db0:password: 123456className: com.mysql.jdbc.Driverurl: jdbc:mysql://192.168.222.157:3306/%s?characterEncodingutf-8username: rootjpa:database: mysqlshow-sql: truehibernate:## 自己建表ddl-auto: noneapplication:name: sharding-jdbc-first 5 配置分表算法
5.1 配置数据库连接
package com.example.demo.config;import com.alibaba.druid.pool.DruidDataSource;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.id.generator.IdGenerator;
import com.dangdang.ddframe.rdb.sharding.id.generator.self.CommonSelfIdGenerator;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;// 数据源相关配置信息
Configuration
public class DataSourceConfig {Value(${spring.jdbc.db0.className})private String className;Value(${spring.jdbc.db0.url})private String url;Value(${spring.jdbc.db0.username})private String username;Value(${spring.jdbc.db0.password})private String password;Beanpublic IdGenerator getIdGenerator() {return new CommonSelfIdGenerator();}Beanpublic DataSource getDataSource() {return buildDataSource();}private DataSource buildDataSource() {// 1.设置分库映射MapString, DataSource dataSourceMap new HashMap(2);dataSourceMap.put(ds_0, createDataSource(ds_0));// dataSourceMap.put(ds_1, createDataSource(ds_1));// 设置默认db为ds_0也就是为那些没有配置分库分表策略的指定的默认库// 如果只有一个库也就是不需要分库的话map里只放一个映射就行了只有一个库时不需要指定默认库// 但2个及以上时必须指定默认库否则那些没有配置策略的表将无法操作数据DataSourceRule rule new DataSourceRule(dataSourceMap, ds_0);// 2.设置分表映射将t_order_0和t_order_1两个实际的表映射到t_order逻辑表TableRule orderTableRule TableRule.builder(t_order).actualTables(Arrays.asList(t_order_0, t_order_1)).dataSourceRule(rule).build();// 3.具体的分库分表策略ShardingRule shardingRule ShardingRule.builder().dataSourceRule(rule).tableRules(Arrays.asList(orderTableRule))// 根据userid分片字段.tableShardingStrategy(new TableShardingStrategy(user_id, new TableShardingAlgorithm())).build();// 创建数据源DataSource dataSource ShardingDataSourceFactory.createDataSource(shardingRule);return dataSource;}private DataSource createDataSource(String dataSourceName) {// 使用druid连接数据库DruidDataSource druidDataSource new DruidDataSource();druidDataSource.setDriverClassName(className);druidDataSource.setUrl(String.format(url, dataSourceName));druidDataSource.setUsername(username);druidDataSource.setPassword(password);return druidDataSource;}
}5.2 配置分表策略
分表算法类需要实现SingleKeyTableShardingAlgorithmT接口
package com.example.demo.config;import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;import java.util.Collection;// 表分片算法
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithmLong {// sql 中关键字 匹配符为 的时候表的路由函数public String doEqualSharding(CollectionString availableTargetNames, ShardingValueLong shardingValue) {for (String tableName : availableTargetNames) {if (tableName.endsWith(shardingValue.getValue() % 2 )) {return tableName;}}throw new IllegalArgumentException();}Overridepublic CollectionString doInSharding(CollectionString availableTargetNames, ShardingValueLong shardingValue) {return null;}Overridepublic CollectionString doBetweenSharding(CollectionString availableTargetNames,ShardingValueLong shardingValue) {return null;}}6 新建实体类
package com.example.demo.entity;import lombok.Data;import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;Data
Entity
Table(name t_order)
public class OrderEntity {Idprivate Long orderId;private Long userId;} 7 OrderRepository
package com.example.demo.repository;import com.example.demo.entity.OrderEntity;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;import java.util.List;public interface OrderRepository extends CrudRepositoryOrderEntity, Long {Query(value SELECT order_id ,user_id FROM t_order where order_id in (?1);, nativeQuery true)public ListOrderEntity findExpiredOrderState(ListString bpIds);
} 8 OrderController
package com.example.demo.controller;import com.example.demo.entity.OrderEntity;
import com.example.demo.repository.OrderRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.ArrayList;
import java.util.List;RestController
public class OrderController {Autowiredprivate OrderRepository orderRepository;// 查询所有的订单信息RequestMapping(/getOrderAll)public ListOrderEntity getOrderAll() {return (ListOrderEntity) orderRepository.findAll();}// 使用in条件查询RequestMapping(/inOrder)public ListOrderEntity inOrder() {ListString ids new ArrayList();ids.add(2);ids.add(3);ids.add(4);ids.add(5);return orderRepository.findExpiredOrderState(ids);}// 增加RequestMapping(/inserOrder)public String inserOrder(OrderEntity orderEntity) {for (int i 0; i 10; i) {OrderEntity order new OrderEntity();order.setOrderId((long) i);order.setUserId((long) i);orderRepository.save(order);}return success;}}9 新建启动类
package com.example.demo;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;SpringBootApplication
EnableJpaRepositories(basePackages com.example.demo.repository)
public class AppSharding {public static void main(String[] args) {SpringApplication.run(AppSharding.class, args);}
}10 启动项目
访问http://localhost:8080/inserOrder 查看数据库 分表测试成功
