Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to use Sharding-Jdbc for sub-database and sub-table". The editor shows you the operation process through an actual case. The operation method is simple and fast, and it is practical. I hope this article "how to use Sharding-Jdbc for sub-database and sub-table" can help you solve the problem.

1. Sharding-Jdbc introduction

Https://shardingsphere.apache.org/

Sharding-jdbc is a distributed relational database middleware.

Client proxy mode, you don't need to build a server, you just need a back-end database, and you can have an IDE.

Positioned as a lightweight Java framework to provide services in the way of jar

Can be understood as an enhanced version of the jdbc driver

Fully compatible with mainstream ORM frameworks

Sharding-jdbc provides four configurations

Java API

Yaml

Properties

Spring Namespace

The difference from MyCat

MyCat is the server agent, and Sharding-Jdbc is the client agent.

In actual development, if an enterprise has a DBA that suggests using MyCat, it is the developer who recommends using sharding-jdbc.

MyCat does not support horizontal table splitting in a single database, while sharding-jdbc supports horizontal table segmentation in the same database.

Noun interpretation

Logical table: the merger table of logistics

Real table: a place where data is stored

Data node: MySQL node where data is stored

Binding table: equivalent to a child table in MyCat

Broadcast table: equivalent to a global table in MyCat

2. Introduction and use of Sharding-Jdbc # 0. First, create two pieces of data on two MySQL: shard_order# 1. 0. Create two tables for the two libraries: order_info_1,order_info_2CREATE TABLE `order_info_ 1` (`id` int (11) NOT NULL, `order_ amount `DEFAULT NULL, `order_ status` int (255) DEFAULT NULL, `user_ id` int (11) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `order_info_ 2` (`id` int (11) NOT NULL, `order_ amount `decimal (10Magi 2) DEFAULT NULL, `order_ status` int (255) DEFAULT NULL, `user_ id` int (11) DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 2. According to the segmentation rule, split into two databases according to the odd and even number of id, and split the table according to user_id in your own database.

Code imports POM dependencies

Org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.0.0-RC2

Configure properties

# name two data sources spring.shardingsphere.datasource.names=ds0 Ds1# data source link ds0 and named consistent spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_orderspring.shardingsphere.datasource.ds0.username=gavinspring.shardingsphere.datasource.ds0.password=123456# data source link ds1 and named consistent spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSourcespring. Specific slicing rules for shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_orderspring.shardingsphere.datasource.ds1.username=gavinspring.shardingsphere.datasource.ds1.password=123456# Rule spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$- based on data node spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$- > {0.1} .order _ info_$- > {1.. 2} # sublibrary > {id% 2} # subtable rule spring.shardingsphere.sharding.tables.order_info.table -strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$- > {user_id% 2 + 1} / / Test code @ SpringBootTestclass ShardingjdbcProjectApplicationTests {@ Autowired JdbcTemplate jdbcTemplate @ Test void insertTest () {String sql = "insert into order_info (id,order_amount,order_status,user_id) values (3213.88); int I = jdbcTemplate.update (sql); System.out.println (" number of rows affected: "+ I);}}

Homework: practice the sub-database table of sharding-jdbc by yourself

3. Configure broadcast tabl

First create the broadcast table province_info on the two libraries

CREATE TABLE `province_ info` (`id` int (11) NOT NULL, `name` varchar (255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Add configuration in properties

Spring.shardingsphere.sharding.broadcast-tables=province_info

Test the code for inserts and queries

@ Test void insertBroadcast () {String sql = "insert into province_info (id,name) values"; int I = jdbcTemplate.update (sql); System.out.println ("* result of influence:" + I);} @ Test void selectBroadcast () {String sql = "select * from province_info"; List result = jdbcTemplate.queryForList (sql) For (Map val: result) {System.out.println ("=" + val.get ("id") + "-" + val.get ("name"));} 4. Configuration binding table

First, create order_item according to the order in which order_info tables are built, and set up order_item_1,order_item_2 on the two libraries respectively.

@ Test void insertBroadcast () {String sql = "insert into province_info (id,name) values"; int I = jdbcTemplate.update (sql); System.out.println ("* result of influence:" + I);} @ Test void selectBroadcast () {String sql = "select * from province_info"; List result = jdbcTemplate.queryForList (sql) For (Map val: result) {System.out.println ("=" + val.get ("id") + "-" + val.get ("name");}}

Configure the bound table to keep the partition logic of the two tables consistent with order_info

# name two data sources spring.shardingsphere.datasource.names=ds0 Ds1# data source link ds0 and named consistent spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_orderspring.shardingsphere.datasource.ds0.username=gavinspring.shardingsphere.datasource.ds0.password=123456# data source link ds1 and named consistent spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSourcespring. Specific slicing rules for shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.ds1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_orderspring.shardingsphere.datasource.ds1.username=gavinspring.shardingsphere.datasource.ds1.password=123456# Rule spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$- based on data node spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$- > {0.1} .order _ info_$- > {1.. 2} # sublibrary > {id% 2} # subtable rule spring.shardingsphere.sharding.tables.order_info.table -strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$- > {user_id% 2 + 1} # specific sharding rules Rule spring.shardingsphere.sharding.tables.order_item.database-strategy.inline.sharding-column=order_idspring.shardingsphere.sharding.tables.order_item.database-strategy.inline.algorithm-expression=ds$- based on data node spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds$- > {0.1} .order _ item_$- > {1.. 2} # sublibrary > {order_id% 2} # subtable rule spring.shardingsphere.sharding.tables.order _ item.table-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_item.table-strategy.inline.algorithm-expression=order_item_$- > {user_id% 2 + 1} # bind table relation spring.shardingsphere.sharding.binding-tables=order_info Order_item# broadcast table spring.shardingsphere.sharding.broadcast-tables=province_info5. Read-write separation configuration

First configure the data source of properties. If there is a host configuration, you must have a slave configuration.

# specify the configuration node spring.shardingsphere.datasource.names=master0,master0slave0,master1 of the master / slave Master1slave0# master0 data Source Link configuration spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master0.jdbcUrl=jdbc:mysql://39.103.163.215:3306/shard_orderspring.shardingsphere.datasource.master0.username=gavinspring.shardingsphere.datasource.master0.password=123456# master0slave0 data Source Link configuration spring.shardingsphere.datasource.master0slave0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.master0slave0.driver-class- Name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master0slave0.jdbcUrl=jdbc:mysql://39.99.212.46:3306/shard_orderspring.shardingsphere.datasource.master0slave0.username=gavinspring.shardingsphere.datasource.master0slave0.password=123456# master1 data source link configuration spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master1.jdbcUrl=jdbc:mysql://39.101.221.95:3306/shard_orderspring.shardingsphere. Datasource.master1.username=gavinspring.shardingsphere.datasource.master1.password=123456# master1slave0 data source link configuration spring.shardingsphere.datasource.master1slave0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.master1slave0.driver-class-name=com.mysql.jdbc.Driverspring.shardingsphere.datasource.master1slave0.jdbcUrl=jdbc:mysql://localhost:3306/shard_orderspring.shardingsphere.datasource.master1slave0.username=rootspring.shardingsphere.datasource.master1slave0.password=gavin# specific sharding rules Rule spring.shardingsphere.sharding.tables.order_info.database-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.order_info.database-strategy.inline.algorithm-expression=ds$- based on data node spring.shardingsphere.sharding.tables.order_info.actual-data-nodes=ds$- > {0.1} .order _ info_$- > {1.. 2} # sublibrary > {id% 2} # subtable rule spring.shardingsphere.sharding.tables.order_info.table -strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_info.table-strategy.inline.algorithm-expression=order_info_$- > {user_id% 2 + 1} # specific sharding rules Rule spring.shardingsphere.sharding.tables.order_item.database-strategy.inline.sharding-column=order_idspring.shardingsphere.sharding.tables.order_item.database-strategy.inline.algorithm-expression=ds$- based on data node spring.shardingsphere.sharding.tables.order_item.actual-data-nodes=ds$- > {0.1} .order _ item_$- > {1.. 2} # sublibrary > {order_id% 2} # subtable rule spring.shardingsphere.sharding.tables.order _ item.table-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.order_item.table-strategy.inline.algorithm-expression=order_item_$- > {user_id% 2 + 1} # bind table relation spring.shardingsphere.sharding.binding-tables=order_info Order_item# broadcast table spring.shardingsphere.sharding.broadcast-tables=province_info# read-write separation master-slave relationship binding spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0spring.shardingsphere.sharding.master-slave-rules.ds0.load-balance-algorithm-type=round_robinspring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master1spring.shardingsphere.sharding.master -this is the end of slave-rules.ds1.slave-data-source-names=master1slave0spring.shardingsphere.sharding.master-slave-rules.ds1.load-balance-algorithm-type=random 's introduction to "how to divide the database and table with Sharding-Jdbc". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.