shareding-jdbc实现读写分离最简单的容易理解示例

╰+攻爆jí腚メ 2022-05-25 02:06 362阅读 0赞

<table> 
 <tbody> 
 <tr> 
 <td>数据库创建create database demo_ds_master; //创建主库create database demo_ds_slave_0; //创建从库1create database demo_ds_slave_1; //创建从库2导入依赖&lt;dependencies&gt;&lt;dependency&gt;&lt;groupId&gt;junit&lt;/groupId&gt;&lt;artifactId&gt;junit&lt;/artifactId&gt;&lt;version&gt;4.0&lt;/version&gt;&lt;scope&gt;test&lt;/scope&gt;&lt;/dependency&gt;&nbsp;&lt;dependency&gt;&lt;groupId&gt;io.shardingjdbc&lt;/groupId&gt;&lt;artifactId&gt;sharding-jdbc-core&lt;/artifactId&gt;&lt;version&gt;2.0.0.M2&lt;/version&gt;&lt;/dependency&gt;&nbsp;&lt;dependency&gt;&lt;groupId&gt;io.shardingjdbc&lt;/groupId&gt;&lt;artifactId&gt;sharding-jdbc-spring-namespace&lt;/artifactId&gt;&lt;version&gt;2.0.0.M2&lt;/version&gt;&lt;/dependency&gt;&nbsp;&lt;dependency&gt;&lt;groupId&gt;com.alibaba&lt;/groupId&gt;&lt;artifactId&gt;druid&lt;/artifactId&gt;&lt;version&gt;1.0.13&lt;/version&gt;&lt;/dependency&gt;&lt;dependency&gt;&lt;groupId&gt;mysql&lt;/groupId&gt;&lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;&lt;version&gt;5.1.28&lt;/version&gt;&lt;/dependency&gt;&nbsp;&lt;dependency&gt;&lt;groupId&gt;org.slf4j&lt;/groupId&gt;&lt;artifactId&gt;slf4j-api&lt;/artifactId&gt;&lt;version&gt;1.7.7&lt;/version&gt;&lt;/dependency&gt;&nbsp;&lt;dependency&gt;&lt;groupId&gt;ch.qos.logback&lt;/groupId&gt;&lt;artifactId&gt;logback-classic&lt;/artifactId&gt;&lt;version&gt;1.0.13&lt;/version&gt;&lt;/dependency&gt;&lt;/dependencies&gt;&nbsp;代码:1.创建数据源的工具类: package com.irisian.sharedjdbc.masterandslave;import javax.sql.DataSource;import com.alibaba.druid.pool.DruidDataSource;public class DataSourceUtil { &nbsp;&nbsp;&nbsp;&nbsp;private static final String URL_PREFIX = "jdbc:mysql://localhost:3306/";&nbsp;&nbsp;&nbsp;&nbsp;private static final String USER_NAME = "root";&nbsp;&nbsp;&nbsp;&nbsp;private static final String PASSWORD = "123456";&nbsp;&nbsp;&nbsp;&nbsp;public static DataSource createDataSource(final String dataSourceName) { &nbsp;&nbsp;&nbsp;&nbsp;DruidDataSource result = new DruidDataSource();&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;result.setUrl(URL_PREFIX + dataSourceName);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;result.setUsername(USER_NAME);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;result.setPassword(PASSWORD);&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return result;&nbsp;&nbsp;&nbsp;&nbsp;}}2.主从数据库配置package com.irisian.sharedjdbc.masterandslave;import java.sql.SQLException;import java.util.Arrays;import java.util.HashMap;import java.util.Map;import javax.sql.DataSource;import io.shardingjdbc.core.api.MasterSlaveDataSourceFactory;import io.shardingjdbc.core.api.config.MasterSlaveRuleConfiguration;public final class RawJdbcJavaMasterSlaveOnlyMain { &nbsp;public static void main(String[] args) throws SQLException { new RawJdbcRepository(getMasterSlaveDataSource()).demo();}&nbsp;public static DataSource getMasterSlaveDataSource() throws SQLException { MasterSlaveRuleConfiguration masterSlaveRule = new MasterSlaveRuleConfiguration();masterSlaveRule.setName("demo_ds_master_slave");//主从规则的名称，此名称随意起masterSlaveRule.setMasterDataSourceName("demo_ds_master");//主数据源名称，和createDataSourceMap对象masterSlaveRule.setSlaveDataSourceNames(Arrays.asList("demo_ds_slave_0", "demo_ds_slave_1"));//从数据源名称，和createDataSourceMap对象masterSlaveRule.setLoadBalanceAlgorithmClassName("io.shardingjdbc.core.api.algorithm.masterslave.RandomMasterSlaveLoadBalanceAlgorithm");//负载平衡的策略return MasterSlaveDataSourceFactory.createDataSource(createDataSourceMap(), masterSlaveRule);}&nbsp;private static Map&lt;String, DataSource&gt; createDataSourceMap() { final Map&lt;String, DataSource&gt; result = new HashMap&lt;&gt;(3, 1);result.put("demo_ds_master", DataSourceUtil.createDataSource("demo_ds_master"));//创建主库的数据源连接result.put("demo_ds_slave_0", DataSourceUtil.createDataSource("demo_ds_slave_0"));//创建从库的数据源连接result.put("demo_ds_slave_1", DataSourceUtil.createDataSource("demo_ds_slave_1"));//创建从库的数据源连接return result;}}&nbsp;3.测试的类package com.irisian.sharedjdbc.masterandslave;import io.shardingjdbc.core.api.HintManager;import javax.sql.DataSource;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;&nbsp;public class RawJdbcRepository { &nbsp;private final DataSource dataSource;&nbsp;public RawJdbcRepository(final DataSource dataSource) { this.dataSource = dataSource;}&nbsp;public void demo() throws SQLException { // createTable(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//创建表的语句&nbsp;// insertData();//插入仅仅会插入到主数据源里面，然后配置mysql主从同步(需要自己配置，shareding-jdbc无法自动完成),配置了之后就可以从从库中读取了&nbsp;//for (int i = 0; i &lt; 30; i++) { //查询均从从库中读取，如果从库集中有一个数据库中没有数据，刚好从此数据库中读取数据，则读取不到任何的数据//System.out.println("1.Equals Select--------------");//printEqualsSelect();//}System.out.println("1.Equals Select--------------");printEqualsSelect();&nbsp;// System.out.println("2.In Select--------------");// printInSelect();// System.out.println("3.Hint Select--------------");// printHintSimpleSelect();// dropTable();&nbsp;}&nbsp;public void createTable() throws SQLException { execute(dataSource,"CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id))");execute(dataSource,"CREATE TABLE IF NOT EXISTS t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (order_item_id))");}&nbsp;public void dropTable() throws SQLException { execute(dataSource, "DROP TABLE t_order_item");execute(dataSource, "DROP TABLE t_order");}&nbsp;public void insertData() throws SQLException { for (int i = 1; i &lt; 10; i++) { long orderId = executeAndGetGeneratedKey(dataSource,"INSERT INTO t_order (user_id, status) VALUES (10, 'INIT')");execute(dataSource, String.format("INSERT INTO t_order_item (order_id, user_id) VALUES (%d, 10)", orderId));orderId = executeAndGetGeneratedKey(dataSource,"INSERT INTO t_order (user_id, status) VALUES (11, 'INIT')");execute(dataSource, String.format("INSERT INTO t_order_item (order_id, user_id) VALUES (%d, 11)", orderId));}}&nbsp;public void printEqualsSelect() throws SQLException { String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=?";try (Connection conn = dataSource.getConnection();PreparedStatement preparedStatement = conn.prepareStatement(sql)) { preparedStatement.setInt(1, 10);printSimpleSelect(preparedStatement);}}&nbsp;public void printInSelect() throws SQLException { String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id IN (?, ?)";try (Connection conn = dataSource.getConnection();PreparedStatement preparedStatement = conn.prepareStatement(sql)) { preparedStatement.setInt(1, 10);preparedStatement.setInt(2, 11);printSimpleSelect(preparedStatement);}}&nbsp;public void printHintSimpleSelect() throws SQLException { String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id";try (HintManager hintManager = HintManager.getInstance();Connection conn = dataSource.getConnection();PreparedStatement preparedStatement = conn.prepareStatement(sql)) { hintManager.addDatabaseShardingValue("t_order", "user_id", 11);printSimpleSelect(preparedStatement);}}&nbsp;private void printSimpleSelect(final PreparedStatement preparedStatement) throws SQLException { StringBuilder builder = new StringBuilder();try (ResultSet rs = preparedStatement.executeQuery()) { while (rs.next()) { System.out.print("order_item_id:" + rs.getLong(1) + ", ");System.out.print("order_id:" + rs.getLong(2) + ", ");System.out.print("user_id:" + rs.getInt(3));System.out.println();}}}&nbsp;private void execute(final DataSource dataSource, final String sql) throws SQLException { try (Connection conn = dataSource.getConnection(); Statement statement = conn.createStatement()) { statement.execute(sql);}}&nbsp;private long executeAndGetGeneratedKey(final DataSource dataSource, final String sql) throws SQLException { long result = -1;try (Connection conn = dataSource.getConnection(); Statement statement = conn.createStatement()) { statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);ResultSet resultSet = statement.getGeneratedKeys();if (resultSet.next()) { result = resultSet.getLong(1);}}return result;}}</td> 
 </tr> 
 </tbody> 
</table>