CREATE DATABASE ds_master DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
CREATE DATABASE ds_slave0 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
CREATE DATABASE ds_slave1 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;


CREATE TABLE t_user(
 user_id BIGINT(20) PRIMARY KEY,
 user_name VARCHAR(40)
);

CREATE TABLE t_order(
 order_id BIGINT(20) PRIMARY KEY,
 user_id BIGINT(20),
 order_num VARCHAR(40)
);

<!-- dbcp2 -->
<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-dbcp2</artifactId>
  <version>2.7.0</version>
</dependency>
<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-pool2</artifactId>
  <version>2.7.0</version>
</dependency>

<!-- mysql-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.49</version>
</dependency>

/*
 * 讀寫分離
 * ① 插入、更新、刪除只會影響主庫的數(shù)據(jù)，即從庫的數(shù)據(jù)不會被影響（不會同步插入、更新、刪除）。因為Sharding-JDBC并沒有主從庫數(shù)據(jù)同步的功能。
 *  所以我們?nèi)绻褂玫氖荕ySQL，可以采用binlog的方法進行同步?？傊枰_發(fā)者額外處理
 * ② 查詢時，如果主庫沒有數(shù)據(jù)，從庫有數(shù)據(jù)，可以查詢到數(shù)據(jù)，所以刪除的時候必須保證主庫和從庫一起刪除。
 */

// 配置真實數(shù)據(jù)源
Map<String, DataSource> dataSourceMap = new HashMap<>();

// 配置主庫
BasicDataSource masterDataSource = new BasicDataSource();
masterDataSource.setDriverClassName("com.mysql.jdbc.Driver");
masterDataSource.setUrl("jdbc:mysql://localhost:3305/ds_master");
masterDataSource.setUsername("root");
masterDataSource.setPassword("123456");
dataSourceMap.put("ds_master", masterDataSource);

// 配置第一個從庫
BasicDataSource slaveDataSource1 = new BasicDataSource();
slaveDataSource1.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource1.setUrl("jdbc:mysql://localhost:3305/ds_slave0");
slaveDataSource1.setUsername("root");
slaveDataSource1.setPassword("123456");
dataSourceMap.put("ds_slave0", slaveDataSource1);

// 配置第二個從庫
BasicDataSource slaveDataSource2 = new BasicDataSource();
slaveDataSource2.setDriverClassName("com.mysql.jdbc.Driver");
slaveDataSource2.setUrl("jdbc:mysql://localhost:3305/ds_slave1");
slaveDataSource2.setUsername("root");
slaveDataSource2.setPassword("123456");

dataSourceMap.put("ds_slave1", slaveDataSource2);

// 配置讀寫分離規(guī)則
MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("ds_master_slave", "ds_master", Arrays.asList("ds_slave0", "ds_slave1"));

// 獲取數(shù)據(jù)源對象
DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, masterSlaveRuleConfig, new Properties());
Connection conn = dataSource.getConnection();

// 插入數(shù)據(jù)
//ShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
//long orderId = ((Long) keyGenerator.generateKey()).longValue();
//long userId = 1027543L;
//ShardingKeyGenerator orderGenerator = new UUIDShardingKeyGenerator();
//String orderNum = (String) orderGenerator.generateKey();
//
//String insertSql = "insert into t_order(order_id, user_id, order_num) values(?, ?, ?)";
//PreparedStatement ps = conn.prepareStatement(insertSql);
//ps.setLong(1, orderId);
//ps.setLong(2, userId);
//ps.setString(3, orderNum);
//int result = ps.executeUpdate();
//System.out.println("執(zhí)行結(jié)果數(shù)：" + result);

//讀取數(shù)據(jù)
String querySql = "select * from t_order";
PreparedStatement qryPs = conn.prepareStatement(querySql);
ResultSet resultSet = qryPs.executeQuery();
while (resultSet.next()){
  String ud = resultSet.getString("user_id");
  String om = resultSet.getString("order_num");
  System.out.println(String.format("user_id = [%s], order_num = [%s]", ud, om));
}

// 刪除數(shù)據(jù)
String deleteSql = "delete from t_order where user_id = 1027543";
PreparedStatement dropPs = conn.prepareStatement(deleteSql);
int delResult = dropPs.executeUpdate();
System.out.println("刪除結(jié)果數(shù)：" + delResult);

dataSources:
 ds_master: !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3305/ds_master
  username: root
  password: 123456
 ds_slave0: !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3305/ds_slave0
  username: root
  password: 123456
 ds_slave1: !!org.apache.commons.dbcp2.BasicDataSource
  driverClassName: com.mysql.jdbc.Driver
  url: jdbc:mysql://localhost:3305/ds_slave1
  username: root
  password: 123456

masterSlaveRule:
 name: ds_ms
 masterDataSourceName: ds_master
 slaveDataSourceNames: [ds_slave0, ds_slave1]

props:
 sql.show: true

ClassPathResource pathResource = new ClassPathResource("sharddb.yml");
DataSource dataSource = YamlMasterSlaveDataSourceFactory.createDataSource(pathResource.getFile());
Connection conn = dataSource.getConnection();

// 插入數(shù)據(jù)
ShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
long orderId = ((Long) keyGenerator.generateKey()).longValue();
long userId = 1027548L;
ShardingKeyGenerator orderGenerator = new UUIDShardingKeyGenerator();
String orderNum = (String) orderGenerator.generateKey();

//String insertSql = "insert into t_order(order_id, user_id, order_num) values(?, ?, ?)";
//PreparedStatement ps = conn.prepareStatement(insertSql);
//ps.setLong(1, orderId);
//ps.setLong(2, userId);
//ps.setString(3, orderNum);
//int result = ps.executeUpdate();
//System.out.println("執(zhí)行結(jié)果數(shù)：" + result);

//讀取數(shù)據(jù)
String querySql = "select * from t_order";
PreparedStatement qryPs = conn.prepareStatement(querySql);
ResultSet resultSet = qryPs.executeQuery();
while (resultSet.next()) {
  String ud = resultSet.getString("user_id");
  String om = resultSet.getString("order_num");
  System.out.println(String.format("user_id = [%s], order_num = [%s]", ud, om));
}