阜阳营销型网站建设,学生服务器,网站建设推广员工资,知末网su模型免费下载# 创建分库与分表
创建两个数据库【order_db_1、order_db_2】。 然后在两个数据库下分别创建三个表【orders_1、orders_2、orders_3】。 建表sql请参考#xff1a;
CREATE TABLE orders_1 (id bigint NOT NULL,order_type varchar(255) NULL DEFAULT NULL,customer_id bigi…# 创建分库与分表
创建两个数据库【order_db_1、order_db_2】。 然后在两个数据库下分别创建三个表【orders_1、orders_2、orders_3】。 建表sql请参考
CREATE TABLE orders_1 (id bigint NOT NULL,order_type varchar(255) NULL DEFAULT NULL,customer_id bigint NULL DEFAULT NULL,amount double NULL DEFAULT NULL,PRIMARY KEY (id) USING BTREE
)
ENGINE InnoDB# 下面开始写测试代码
【pom.xml】
dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactIdversion2.3.12.RELEASE/version
/dependency
dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-actuator/artifactIdversion2.3.12.RELEASE/version
/dependency
dependencygroupIdorg.mybatis.spring.boot/groupIdartifactIdmybatis-spring-boot-starter/artifactIdversion2.2.0/version
/dependency
dependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.4.3.1/version
/dependency
dependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-core/artifactIdversion3.4.3.1/version
/dependency
dependencygroupIdorg.apache.shardingsphere/groupIdartifactIdsharding-jdbc-spring-boot-starter/artifactIdversion4.1.1/version
/dependency
dependencygroupIdcom.alibaba/groupIdartifactIddruid/artifactIdversion1.2.22/version
/dependency
dependencygroupIdcom.mysql/groupIdartifactIdmysql-connector-j/artifactIdversion8.3.0/version
/dependency
dependencygroupIdcom.alibaba.fastjson2/groupIdartifactIdfastjson2/artifactIdversion2.0.49/version
/dependency【application.properties】
server.port8080
spring.application.namemyShardingSpheremanagement.server.port8080
management.endpoints.web.exposure.include*########################################management.health.db.enabledfalse
management.health.elasticsearch.enabledfalsemybatis-plus.mapper-locationsclasspath:mapper/*.xml
mybatis-plus.type-aliases-packagetest/persistence/mapper##########################################################spring.shardingsphere.datasource.namesds-order-db-1, ds-order-db-2######################################################
# 数据源spring.shardingsphere.datasource.ds-order-db-1.typecom.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-order-db-1.driver-class-namecom.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-order-db-1.urljdbc:mysql://192.168.44.228:3306/order_db_1
spring.shardingsphere.datasource.ds-order-db-1.usernameroot
spring.shardingsphere.datasource.ds-order-db-1.passwordrootspring.shardingsphere.datasource.ds-order-db-2.typecom.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-order-db-2.driver-class-namecom.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds-order-db-2.urljdbc:mysql://192.168.44.228:3306/order_db_2
spring.shardingsphere.datasource.ds-order-db-2.usernameroot
spring.shardingsphere.datasource.ds-order-db-2.passwordroot#################################################
# tablespring.shardingsphere.sharding.tables.orders.actual-data-nodesds-order-db-${1..2}.orders_${1..3}spring.shardingsphere.sharding.tables.orders.database-strategy.inline.sharding-columncustomer_id
spring.shardingsphere.sharding.tables.orders.database-strategy.inline.algorithm-expressionds-order-db-$-{customer_id % 2 1}spring.shardingsphere.sharding.tables.orders.table-strategy.inline.sharding-columncustomer_id
spring.shardingsphere.sharding.tables.orders.table-strategy.inline.algorithm-expressionorders_${customer_id % 3 1}##################################################spring.shardingsphere.props.sql.showtrue################################################【mapper/OrdersMapper.xml】
?xml version1.0 encodingUTF-8?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.chz.myShardingSphere.persistence.mapper.OrdersMapperresultMap idorders typecom.chz.myShardingSphere.persistence.po.Ordersid columnid propertyid /result columnorderType propertyorder_type /result columncustomerId propertycustomer_id /result columnamount propertyamount //resultMapselect idtestSelectOrder parameterTypelong resultMapordersSELECT *FROM orderswhereif testcustomerId!nulland customer_id #{customerId}/if/whereorder by idLIMIT 10 OFFSET 0/select/mapper【TestController.java】
package com.chz.myShardingSphere.controller;Slf4j
RestController
RequestMapping(/test)
public class TestController {Value(${spring.application.name})private String applicationName;Autowiredprivate TestService testService;GetMapping(/testAddOrder)public String testAddOrder(RequestParam(value id, defaultValue 1) Long id) {testService.testAddOrder(id);return testAddOrder;}GetMapping(/testSelectOrder)public String testSelectOrder(RequestParam(value customerId, required false) Long customerId) {ListOrders orders testService.testSelectOrder(customerId);return JSON.toJSONString(orders);}GetMapping(/testDeleteOrder)public String testDeleteOrder() {testService.testDeleteOrder();return success;}
}【OrdersMapper.java】
package com.chz.myShardingSphere.persistence.mapper;Repository
Mapper
public interface OrdersMapper extends BaseMapperOrders
{ListOrders testSelectOrder(Param(customerId) Long customerId);
}【Orders.java】
package com.chz.myShardingSphere.persistence.po;Getter
Setter
public class Orders {private Long id;private Long orderType;private Long customerId;private Double amount;Overridepublic String toString() {return Orders{ id id , orderType orderType , customerId customerId , amount amount };}
}【TestService.java】
package com.chz.myShardingSphere.server;Service
public class TestService {Autowiredprivate OrdersMapper ordersMapper;Transactionalpublic void testAddOrder(long id){Orders orders new Orders();orders.setId(id);orders.setCustomerId(id);orders.setOrderType(id);orders.setAmount((double)id);ordersMapper.insert(orders);}Transactionalpublic ListOrders testSelectOrder(Long customerId){ListOrders orders ordersMapper.testSelectOrder(customerId);return orders;}Transactionalpublic void testDeleteOrder(){LambdaQueryWrapperOrders queryWrapper new LambdaQueryWrapper();ordersMapper.delete(queryWrapper);}
}【MyShardingSphereTest.java】
package com.chz.myShardingSphere;SpringBootApplication
public class MyShardingSphereTest {public static void main(String[] args) {SpringApplication.run(MyShardingSphereTest.class, args);}
}启动【MyShardingSphereTest】
# 下面进行测试
访问以下url创建6条数据
http://localhost:8080/test/testAddOrder?id1
http://localhost:8080/test/testAddOrder?id2
http://localhost:8080/test/testAddOrder?id3
http://localhost:8080/test/testAddOrder?id4
http://localhost:8080/test/testAddOrder?id5
http://localhost:8080/test/testAddOrder?id6然后访问【http://localhost:8080/test/testSelectOrder】看下数据能不能查出来 可以看到数据都查出来了。
增加个条件试试访问【http://localhost:8080/test/testSelectOrder?customerId1】 也运行正常
