内容简介:ShardingJDBC是一个数据库分库分表框架,它通过实现自定义的以SpringBoot+Maven+MySQL为例创建2库3表共6个user表: ds0.user0 ~ ds1.user2
ShardingJDBC是一个数据库分库分表框架,它通过实现自定义的 javax.sql.DataSource
接口,将分库分表的逻辑封装在了里头,让客户端可以通过 JDBC
相对透明地访问分片数据库。但是,分片数据库有其固有之局限性,需要谨慎使用。
ShardingJDBC快速入门示例
以SpringBoot+Maven+MySQL为例
1. 引入ShardingJDBC依赖
<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 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>bj</groupId> <artifactId>hellomaven</artifactId> <packaging>jar</packaging> <version>1.0-SNAPSHOT</version> <name>hellomaven</name> <url>http://maven.apache.org</url> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.0.RELEASE</version> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>3.0.0</version> </dependency> </dependencies> </project>
2. 创建数据表
创建2库3表共6个user表: ds0.user0 ~ ds1.user2
import mysql.connector conn = mysql.connector.connect(host='localhost', user='root', password='root', db='foo') conn.autocommit = True cursor = conn.cursor() for i in range(0, 2): cursor.execute(f"DROP SCHEMA IF EXISTS ds{i}") cursor.execute(f"CREATE SCHEMA ds{i}") for j in range(0, 3): cursor.execute(f"CREATE TABLE ds{i}.user{j}(_id BIGINT, id INT, username VARCHAR(16))")
对应的SQL
DROP SCHEMA IF EXISTS ds0; CREATE SCHEMA ds0; CREATE TABLE ds0.user0(_id BIGINT, id INT, username VARCHAR(16)); CREATE TABLE ds0.user1(_id BIGINT, id INT, username VARCHAR(16)); CREATE TABLE ds0.user2(_id BIGINT, id INT, username VARCHAR(16)); DROP SCHEMA IF EXISTS ds1; CREATE SCHEMA ds1; CREATE TABLE ds1.user0(_id BIGINT, id INT, username VARCHAR(16)); CREATE TABLE ds1.user1(_id BIGINT, id INT, username VARCHAR(16)); CREATE TABLE ds1.user2(_id BIGINT, id INT, username VARCHAR(16));
3. 配置ShardingJDBC
ShardingJDBC的文档乍一看啥都有,仔细一看下不了手,文档跟项目还不同步,需要面向异常,随机应变。
以分库策略对2取模,分表策略对3取模为例:
application.yml
sharding: jdbc: datasource: names: ds0,ds1 ds0: type: com.zaxxer.hikari.HikariDataSource jdbc-url: jdbc:mysql://localhost/ds0 username: root password: root ds1: type: com.zaxxer.hikari.HikariDataSource jdbc-url: jdbc:mysql://localhost/ds1 username: root password: root config: sharding: tables: user: database-strategy: inline: sharding-column: id algorithm-expression: ds$->{id % 2} table-strategy: inline: sharding-column: id algorithm-expression: user$->{id % 3} key-generator-column-name: _id logging: level: root: debug
4. 在主程序中调用ShardingJDBC
package bj; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import javax.annotation.Resource; import java.util.List; import java.util.Map; @SpringBootApplication @RestController public class App { public static void main(String[] args) { SpringApplication.run(App.class, args); } @Resource private JdbcTemplate jdbcTemplate; @GetMapping("/") public Object index() { jdbcTemplate.update("INSERT INTO user(id, username) VALUES (1, 'alpha')"); jdbcTemplate.update("INSERT INTO user(id, username) VALUES (2, 'beta')"); jdbcTemplate.update("INSERT INTO user(id, username) VALUES (3, 'gamma')"); jdbcTemplate.update("INSERT INTO user(id, username) VALUES (4, 'theta')"); List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT * FROM user WHERE id IN (0,1,2,3,4,5,6,7)"); System.out.println(maps); return maps; } }
执行主程序,访问 localhost:8080/
,返回的JSON如下:
[ { "_id": 273847537209704448, "id": 4, "username": "theta" }, { "_id": 273847537138401280, "id": 2, "username": "beta" }, { "_id": 273847537163567104, "id": 3, "username": "gamma" }, { "_id": 273847537096458240, "id": 1, "username": "alpha" } ]
MySQL的 SQL 日志如下:
2018-11-26T08:12:20.690370Z 1697 Query INSERT INTO user1(id, username, _id) VALUES (1, 'alpha', 273847537096458240) 2018-11-26T08:12:20.698059Z 1696 Query INSERT INTO user2(id, username, _id) VALUES (2, 'beta', 273847537138401280) 2018-11-26T08:12:20.702720Z 1697 Query INSERT INTO user0(id, username, _id) VALUES (3, 'gamma', 273847537163567104) 2018-11-26T08:12:20.717724Z 1696 Query INSERT INTO user1(id, username, _id) VALUES (4, 'theta', 273847537209704448) 2018-11-26T08:12:20.729676Z 1697 Query SELECT * FROM user0 WHERE id IN (0,1,2,3,4,5,6,7) 2018-11-26T08:12:20.729705Z 1696 Query SELECT * FROM user0 WHERE id IN (0,1,2,3,4,5,6,7) 2018-11-26T08:12:20.732935Z 1697 Query SELECT * FROM user1 WHERE id IN (0,1,2,3,4,5,6,7) 2018-11-26T08:12:20.733504Z 1696 Query SELECT * FROM user1 WHERE id IN (0,1,2,3,4,5,6,7) 2018-11-26T08:12:20.736153Z 1697 Query SELECT * FROM user2 WHERE id IN (0,1,2,3,4,5,6,7) 2018-11-26T08:12:20.736612Z 1696 Query SELECT * FROM user2 WHERE id IN (0,1,2,3,4,5,6,7)
可见,ShardingJDBC确实执行了多库多表的插入与查询操作,而且生成了分布式自增ID
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
编程珠玑(第二版)
[美] Jon Bentley / 谢君英、石朝江 / 中国电力出版社 / 2004-4 / 28.00元
《编程珠玑(第2版)》是计算机科学方面的经典名著。书的内容围绕程序设计人员面对的一系列实际问题展开。作者Jon Bentley 以其独有的洞察力和创造力,引导读者理解这些问题并学会解决方法,而这些正是程序员实际编程生涯中至关重要的。一起来看看 《编程珠玑(第二版)》 这本书的介绍吧!