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);
}
}