sharding-jdbc实现分库分表
1.定义DataSourceUtil
public class DataSourceUtil {
private static final String HOST = "localhost";
private static final int PORT = 3306;
private static final String USER_NAME = "root";
private static final String PASSWORD = "123456";
public static DataSource createDataSource(String dataSourceName) {
HikariDataSource dataSource = new HikariDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl(String.format("jdbc:mysql://%s:%s/%s?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", HOST, PORT, dataSourceName));
dataSource.setUsername(USER_NAME);
dataSource.setPassword(PASSWORD);
return dataSource;
}
}
2.定义数据库/表 分片键 分片算法
public class ShardingDatabaseAndTableConfiguration {
private static Map<String, DataSource> createDataSourceMap(){
//代表真实的数据源
Map<String,DataSource> dataSourceMap=new HashMap<>();
dataSourceMap.put("ds0",DataSourceUtil.createDataSource("erp01"));
dataSourceMap.put("ds1",DataSourceUtil.createDataSource("erp02"));
return dataSourceMap;
}
//创建分片规则
// * 针对数据库
// * 针对表
//* 一定要配置分片键
//* 一定要配置分片算法
//* 完全唯一id的问题
private static ShardingRuleConfiguration createShardingRuleConfiguration(){
ShardingRuleConfiguration configuration=new ShardingRuleConfiguration();
//把逻辑表和真实表的对应关系添加到分片规则配置中
configuration.getTables().add(getOrderTableRuleConfiguration());
//设置数据库分库规则
configuration.setDefaultDatabaseShardingStrategy(
new StandardShardingStrategyConfiguration
("user_id","db-inline"));
Properties properties=new Properties();
properties.setProperty("algorithm-expression","ds${user_id%2}");
//设置分库策略
configuration.getShardingAlgorithms().
put("db-inline",new ShardingSphereAlgorithmConfiguration("INLINE",properties));
//设置表的分片规则（数据的水平拆分）
configuration.setDefaultTableShardingStrategy(new StandardShardingStrategyConfiguration
("order_id","order-inline"));
//设置分表策略
Properties props=new Properties();
props.setProperty("algorithm-expression","order_${order_id%3}");
configuration.getShardingAlgorithms().put("order-inline",
new ShardingSphereAlgorithmConfiguration("INLINE",props));
//设置主键生成策略
// * UUID
// * 雪花算法
Properties idProperties=new Properties();
idProperties.setProperty("worker-id","123");
configuration.getKeyGenerators().put("snowflake",new ShardingSphereAlgorithmConfiguration(
"SNOWFLAKE",idProperties));
return configuration;
}
//配置逻辑表以及表的id策略
private static ShardingTableRuleConfiguration getOrderTableRuleConfiguration(){
ShardingTableRuleConfiguration tableRuleConfiguration=
new ShardingTableRuleConfiguration("t_order","ds${0..1}.order_${0..2}");
tableRuleConfiguration.setKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("order_id","snowflake"));
return tableRuleConfiguration;
}
public static DataSource getDataSource() throws SQLException {
return ShardingSphereDataSourceFactory
.createDataSource(createDataSourceMap(), Collections.singleton(createShardingRuleConfiguration()),new Properties());
}
}
3.编写SQL Dao
public class OrderServiceImpl implements IOrderService{
private final DataSource dataSource;
public OrderServiceImpl(final DataSource dataSource){
this.dataSource=dataSource;
}
/**
* 创建表
*/
@Override
public void initOrderTable() {
String sql = "CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, address_id BIGINT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id))";
try{
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
}
@Override
public void saveOrder(List<OrderVo> orderList) {
for(OrderVo vo:orderList){
saveOrder(vo);
}
}
@Override
public void saveOrder(OrderVo orderVo) {
String sql = "INSERT INTO t_order (user_id, address_id, status) VALUES (?, ?, ?)";
try{
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.SUCCESS_NO_INFO);
preparedStatement.setInt(1, orderVo.getUserId());
preparedStatement.setLong(2, orderVo.getAddressId());
preparedStatement.setString(3, orderVo.getStatus());
preparedStatement.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}
}
}
4.main方法调用
public class ShardingSimpleDemo {
public static void main(String[] args) throws SQLException {
try{
DataSource ds=ShardingDatabaseAndTableConfiguration.getDataSource();
IOrderService orderService=new OrderServiceImpl(ds);
orderService.initOrderTable();
for (int i = 1; i <= 10; i++) {
OrderVo order = getOrder(i);
orderService.saveOrder(order);
}
System.out.println("----end-----");
}catch(Exception e){
e.printStackTrace();
}
}
static Random random=new Random();
private static OrderVo getOrder(int i) {
OrderVo orderVo = new OrderVo();
orderVo.setOrderId(i);
orderVo.setUserId(random.nextInt(10000));
orderVo.setAddressId(i);
orderVo.setStatus("TEST"+i);
return orderVo;
}
}
5 Vo对象
public class OrderVo implements Serializable{
private long orderId;
private int userId;
private long addressId;
private String status;
}
