第3篇：Sharding-JDBC(版本3.0) 入门demo，纯java 代码 【了解】

先创建ds_0,ds_1两个数据库，并同时在两个数据库中创建表t_order_0,t_order_1两个表

CREATE TABLE `t_order_0` ( `order_id` int(11) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, `status` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1 新建maven项目，并添加依赖

<dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>3.0.0</version> </dependency> <!-- mysql 数据库驱动. --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.31</version> </dependency> <!-- 数据源 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.26</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> </dependency>

2 编写主类并测试

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; import java.util.Properties; import javax.sql.DataSource; import com.alibaba.druid.pool.DruidDataSource; import com.mysql.cj.jdbc.Driver; import io.shardingsphere.api.config.ShardingRuleConfiguration; import io.shardingsphere.api.config.TableRuleConfiguration; import io.shardingsphere.api.config.strategy.InlineShardingStrategyConfiguration; import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory; public class ShardTest_1 { // 通过Druid构建数据源. public DataSource createDataSource(String url, String username, String password) { DruidDataSource ds = new DruidDataSource(); // driver : 数据库驱动. url: 数据库地址 username/pwd : 账号和密码 ds.setDriverClassName(Driver.class.getName()); ds.setUrl(url); ds.setUsername(username); ds.setPassword(password); return ds; } /** * 通过ShardingDataSourceFactory 构建分片数据源 * * @return * @throws SQLException */ public DataSource getShardingDataSource() throws SQLException { /* * 1. 数据源集合：dataSourceMap * * 2. 分片规则：shardingRuleConfig * */ // 配置真实数据源 Map<String, DataSource> dataSourceMap = new HashMap<String, DataSource>(); // 添加数据源.两个数据源ds_0和ds_1 dataSourceMap.put("ds_0", createDataSource( "jdbc:mysql://localhost:3306/ds_0?useUnicode=true&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&tinyInt1isBit=false&useSSL=false", "root", "123456")); dataSourceMap.put("ds_1", createDataSource( "jdbc:mysql://localhost:3306/ds_1?useUnicode=true&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&tinyInt1isBit=false&useSSL=false", "root", "123456")); /** * 需要构建表规则 1. 指定逻辑表. 2. 配置实际节点》 3. 指定主键字段. 4. 分库和分表的规则》 * * 数据库：ds_0和ds_1 表：t_order_0 和 t_order_1 t_order表的字段 (order_id,user_id,status) * * 表的分片策略 order_id 库的分片策略 user_id */ // 配置t_order表规则 TableRuleConfiguration orderTableRuleConfiguration = new TableRuleConfiguration(); // 指定逻辑表 orderTableRuleConfiguration.setLogicTable("t_order"); // 配置实际节点 // ds_0.t_order_0 , ds_0.t_order_1, ds_1.t_order_0 , ds_1.t_order_1 orderTableRuleConfiguration.setActualDataNodes("ds_${0..1}.t_order_${0..1}"); // 指定主键字段 orderTableRuleConfiguration.setKeyGeneratorColumnName("order_id"); // 表的分片策略根据 order_id,分成t_order_0,t_order_1两个表 orderTableRuleConfiguration.setTableShardingStrategyConfig( new InlineShardingStrategyConfiguration("order_id", "t_order_${order_id%2}")); // 数据库的分片策略 根据user_id，分成ds_0,ds_1两个数据库 orderTableRuleConfiguration.setDatabaseShardingStrategyConfig( new InlineShardingStrategyConfiguration("user_id", "ds_${user_id%2}")); // 分片规则：shardingRuleConfig ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfiguration); DataSource ds = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new HashMap<String, Object>(), new Properties()); return ds; } /** * 3. 编写测试例子： 通过DataSource获取到Connection. * * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { /* * 1. 需要到DataSource 2. 通过DataSource获取Connection 3. 定义一条SQL语句. 4. * 通过Connection获取到PreparedStament. 5. 执行SQL语句. 6. 关闭连接. */ ShardTest_1 app = new ShardTest_1(); // * 1. 需要到DataSource DataSource dataSource = app.getShardingDataSource(); // * 2. 通过DataSource获取Connection Connection connection = dataSource.getConnection(); // * 3. 定义一条SQL语句. // 注意：******* sql语句中 使用的表是 上面代码中定义的逻辑表 ******* String sql = "insert into t_order(order_id,user_id,status) values(10,1,'insert')"; // * 4. 通过Connection获取到PreparedStament. PreparedStatement preparedStatement = connection.prepareStatement(sql); // * 5. 执行SQL语句. preparedStatement.execute(); sql = "insert into t_order(order_id,user_id,status) values(11,2,'insert')"; preparedStatement = connection.prepareStatement(sql); preparedStatement.execute(); // * 6. 关闭连接. preparedStatement.close(); connection.close(); } }

运行结果：当用户id为1时，数据到ds_1数据库
当用户id为2时，数据到ds_0数据库

当order_id为10时，到表t_order_0中
当order_id为11时，到表t_order_1中