shardingjdbc配置

ShardingSphere是一个开源的分布式数据库中间件，提供了分库分表、读写分离、数据分片等功能，支持MySQL、Oracle、SQLServer等多种数据库。而Sharding-JDBC是ShardingSphere的一部分，是一个基于JDBC的轻量级Java框架，提供了分库分表、读写分离、数据分片等功能，可以无缝对接现有的JDBC应用，尤其适合中小型项目。

下面是Sharding-JDBC的配置步骤：

1. 引入相关依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>${sharding-jdbc.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>${sharding-jdbc.version}</version>
</dependency>

2. 配置数据源

spring:
  datasource:
    #主库
    master:
      url: jdbc:mysql://localhost:3306/db_master?useSSL=false&characterEncoding=utf8&serverTimezone=UTC
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    #从库1
    slave1:
      url: jdbc:mysql://localhost:3306/db_slave1?useSSL=false&characterEncoding=utf8&serverTimezone=UTC
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    #从库2
    slave2:
      url: jdbc:mysql://localhost:3306/db_slave2?useSSL=false&characterEncoding=utf8&serverTimezone=UTC
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

3. 配置分库分表规则

sharding:
  #默认的分库分表规则
  default-datasource-name: master
  #分库规则
  sharding-rule:
    #分库算法
    default-database-strategy:
      standard:
        #分库的列名
        sharding-column: user_id
        #分库的算法类
        precise-algorithm-class-name: com.example.algorithm.ModuloDatabaseShardingAlgorithm
    #分表规则
    default-table-strategy:
      standard:
        #分表的列名
        sharding-column: order_id
        #分表的算法名
        precise-algorithm-class-name: com.example.algorithm.ModuloTableShardingAlgorithm

4. 编写分库分表算法

//分库算法
public class ModuloDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
}
//分表算法
public class ModuloTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        for (String each : availableTargetNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }
}

5. 编写数据访问层代码

@Repository
public class OrderDaoImpl implements OrderDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void createOrder(Order order) {
        String sql = "insert into t_order(order_id, user_id, status) values (?, ?, ?)";
        Object[] params = {order.getOrderId(), order.getUserId(), order.getStatus()};
        jdbcTemplate.update(sql, params);
    }
    @Override
    public List<Order> getOrderByUserId(Long userId) {
        String sql = "select order_id, user_id, status from t_order where user_id=?";
        Object[] params = {userId};
        return jdbcTemplate.query(sql, params, new OrderMapper());
    }
    private class OrderMapper implements RowMapper<Order> {
        @Override
        public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
            Order order = new Order();
            order.setOrderId(rs.getLong("order_id"));
            order.setUserId(rs.getLong("user_id"));
            order.setStatus(rs.getString("status"));
            return order;
        }
    }
}

以上就是Sharding-JDBC配置的基本步骤，需要根据具体的业务场景进行调整。