MySQL-Sharding-JDBC水平分库

1.
创建数据库创建一个order_db

CREATE DATABASE `order_db` CHARACTER 'utf8' COLLATE 'utf8_general_ci';

2.创建表

CREATE TABLE `t_order_1`(
`order_id` BIGINT(20) NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
`user_id` BIGINT(20) NOT NULL,
`status` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY(`order_id`)USING BTREE
)ENGINE =INNODB CHARACTER SET =utf8 COLLATE =utf8_general_ci ROW_FORMAT =DYNAMIC;

CREATE TABLE `t_order_2`(
`order_id` BIGINT(20) NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
`user_id` BIGINT(20) NOT NULL,
`status` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY(`order_id`)USING BTREE
)ENGINE =INNODB CHARACTER SET =utf8 COLLATE =utf8_general_ci ROW_FORMAT =DYNAMIC;

3.配置yml

server.port=56789
server.servlet.context-path=/sharding

spring.http.encoding.charset=UTF-8
spring.http.encoding.enabled=true
spring.http.encoding.force=true

spring.main.allow-bean-definition-overriding=true

#数据映射 驼峰命名
mybatis-plus.configuration.map-underscore-to-camel-case=true

#############################################################
#打印日志
logging.level.root=info
logging.level.org.springframework.web=info
logging.level.com.bdqn=debug
logging.level.druid.sql=debug

#############################################################
#数据源
spring.shardingsphere.datasource.names=ds1,ds2,ds0


spring.shardingsphere.datasource.ds0.type=org.apache.commons.dbcp2.BasicDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/order_db
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root


spring.shardingsphere.datasource.ds1.type=org.apache.commons.dbcp2.BasicDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/order_db_1
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root


spring.shardingsphere.datasource.ds2.type=org.apache.commons.dbcp2.BasicDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://localhost:3306/order_db_2
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=root


#先找数据库
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2+1}

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{1..2}.t_order_$->{1..2}


#spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m1.t_order_$->{1..2}

spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2+1}




#打开日志
spring.shardingsphere.props.sql.show=true

4.mapper

@Mapper
@Component
public interface DBMapper {


//    增加
    @Insert("insert into t_order (price,user_id,status) values (#{price},#{userId},#{status})")
    void insertOrder(@Param("price") BigDecimal price, @Param("userId") Long userId, @Param("status") String status);

}

main方法

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {DBApplication.class})
public class test {
    @Autowired(required = false)
    private DBMapper dbMapper;

    @Test
    public void test(){
        for(int i = 1;i <= 100; i++){
            dbMapper.insertOrder(new BigDecimal(i),1L,"SUCCESS");
            System.out.println("插入"+i+"完成");
        }
//        dbMapper.insertOrder(new BigDecimal(1),2L,"SUCCESS");
    }

    @Test
    public void test1(){
        List<Long> ids=new ArrayList<>();
        ids.add(394077371663122433L);
        ids.add(394077371709259777L );
        List<Map> maps= dbMapper.selectOrderbyIds(ids);
        System.err.println(maps);
    }

}