• springboot + sharding-jdbc 学习



    官网地址:http://shardingsphere.io/document/current/cn/overview/

    sharding-jdbc事务:https://blog.csdn.net/yanyan19880509/article/details/78335935

     

    1简介

        通过docker搭建四台mysql,两主,每台一从;springboot搭建简单的web项目,并配置sharding-jdbc实现分库分表+独写分离;

    sharding-jdbc是在datasource层做的代理,对应用透明,只需在datasource配置好读写分离/分库分表策略即可,原理可参见官网;

    2 docker搭建mysql

        硬件环境:虚拟机 centos7 4G内存 20G硬盘  mysql5.7.13

       1 安装docker : yum install docker   并启动

       2 获取mysql镜像: docker pull mysql:5.7.13

       3 在虚拟机上配置四台mysql配置文件my.cnf,目录结构如下,主要是配置log-bin和server-id,主从之间server-id不能相同

     tip:可以先启动一台mysql容器,然后拷贝容器的配置到本地 docker cp mysql:/etc/mysql/my.cnf /etc/mysql/master-order0/,再在这配置中修改log-bin和server-id

    4 启动mysql容器 :

      docker run  --name master-order0 -d -p 13306:3306 -v /etc/mysql/master-order0/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.13 //主 master0

      docker run  --name master-order1 -d -p 23306:3306 -v /etc/mysql/master-order1/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.13 //主master1

      docker run  --name slave-order0 -d -p 13307:3306 -v /etc/mysql/slave-order0/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.13  //从slave0 作为master0的从

      docker run  --name slave-order1 -d -p 23307:3306 -v /etc/mysql/master-order0/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.13 //从slave1 作为master1的从

    说明  --name 指定容器名称,-d后台运行 -v 使用本地配置代替容器自身的配置文件 -e 设置mysql root密码

    配置主从:配置前需要关闭虚拟机防火墙 : service firewalld stop

     进入 slave-order0: docker exec -it slave-order0 /bin/bash

     进入mysql:mysql -uroot -p123456

    配置主从: change master to MASTER_HOST='主机ip',MASTER_PORT=13306,MASTER_USER='ROOT',MASTER_PASSWORD='123456',

                     MASTER_LOG_FILE='主中的log-bin名称',MASTER_LOG_POS=主机log-bin的pos  ;

         其中MASTER_LOG_FILE和MASTER_LOG_POS需要到主上查看  ,进入主master-order0数据库,show master status G  可以查看到这两个参数

    另外的master-order1和slave-order1配置方式相同;

    在两主上建立相同的库和表,库 tyyd; 每个库上新建两张表 order0,order1 表结构相同,表id不要设置为自增,由sharding-jdbc生成;

    可以到从数据库上查看到对应的库和表也已经生成;

    3springboot+sharding-jdbc搭建web项目

     idea中新建web项目,引入sharding-jdbc maven依赖

    <!-- https://mvnrepository.com/artifact/io.shardingsphere/sharding-jdbc -->
    <dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>3.0.0.M2</version>
    </dependency>
    1 配置sharding-jdbc 分库分表+独写分离 datasource(也可以采用只独写分离或只分库分表),可以有多种配置方式,我采用的是代码的配置方式:
    /**
     * @author cgl
     * @date 2018/8/15 11:12
     * <p>
     * 读写分离+分库分表
     */
    @Configuration
    public class ShardingJDBCSplitAndMsDataSourceConfig {
        @Bean
        DataSource getDataSource() throws SQLException {
            ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
            shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
    //        shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration());
    //        shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");

    //分库策略:根据参数userId取摸计算,此处的ds和下面一行中的order都是逻辑库/表名 shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
    //分表策略:根据参数id取模计算 shardingRuleConfig.setDefaultTableShardingStrategyConfig(
    new InlineShardingStrategyConfiguration("id", "order${id % 2}")); shardingRuleConfig.setMasterSlaveRuleConfigs(getMasterSlaveRuleConfigurations()); //实际项目需自定义keyGenerator或使用默认的 shardingRuleConfig.setDefaultKeyGenerator(System::currentTimeMillis ); return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new HashMap<>(), new Properties()); } TableRuleConfiguration getOrderTableRuleConfiguration() { TableRuleConfiguration result = new TableRuleConfiguration(); result.setLogicTable("order"); result.setActualDataNodes("ds${0..1}.order${0..1}"); result.setKeyGeneratorColumnName("id"); return result; } List<MasterSlaveRuleConfiguration> getMasterSlaveRuleConfigurations() { MasterSlaveRuleConfiguration masterSlaveRuleConfig1 = new MasterSlaveRuleConfiguration("ds0", "ds_master_0", Arrays.asList("ds_master_0_slave_0")); MasterSlaveRuleConfiguration masterSlaveRuleConfig2 = new MasterSlaveRuleConfiguration("ds1", "ds_master_1", Arrays.asList("ds_master_1_slave_0")); return Lists.newArrayList(masterSlaveRuleConfig1, masterSlaveRuleConfig2); } Map<String, DataSource> createDataSourceMap() { final Map<String, DataSource> result = new HashMap<>(); result.put("ds_master_0", createDatasource("com.mysql.jdbc.Driver", "jdbc:mysql://192.168.137.128:13306/tyyd?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "123456")); result.put("ds_master_1", createDatasource("com.mysql.jdbc.Driver", "jdbc:mysql://192.168.137.128:23306/tyyd?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "123456")); result.put("ds_master_0_slave_0", createDatasource("com.mysql.jdbc.Driver", "jdbc:mysql://192.168.137.128:13307/tyyd?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "123456")); result.put("ds_master_1_slave_0", createDatasource("com.mysql.jdbc.Driver", "jdbc:mysql://192.168.137.128:23307/tyyd?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "123456")); return result; } private HikariDataSource createDatasource(String driverClassName, String jdbcUrl, String userName, String password) { HikariConfig hikariConfig0 = new HikariConfig(); hikariConfig0.setDriverClassName(driverClassName); hikariConfig0.setJdbcUrl(jdbcUrl); hikariConfig0.setUsername(userName); hikariConfig0.setPassword(password); return new HikariDataSource(hikariConfig0); } }

    2 编写order Controller crud操作

    package com.sj.web.controller;
    import com.sj.client.request.order.OrderAddRequest;
    import com.sj.client.request.order.OrderUpdateRequest;
    import com.sj.client.response.order.OrderDetail;
    import com.sj.client.service.order.OrderService;
    import com.sj.web.dto.BaseResponse;
    import com.sj.web.dto.order.OrderDTO;
    import org.apache.commons.lang3.RandomUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestBody;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    import java.util.List;
    import java.util.Optional;
    import java.util.stream.Collectors;
    
    /**
     * @author cgl
     * @date 2018/8/16 15:00
     */
    @RestController
    @RequestMapping("/order")
    public class OrderController {
    
        @Autowired
        private OrderService orderService;
        @RequestMapping("/get/{id}/{userId}")
        public OrderDTO getOrderInfo(@PathVariable("id") long id,@PathVariable("userId") long userId){
            Optional<OrderDetail> orderDetailOptional = orderService.getOrderInfoByOrderId(id,userId);
            if(orderDetailOptional.isPresent()){
                OrderDetail detail = orderDetailOptional.get();
                return convert(detail);
            }
            return new OrderDTO();
        }
    
        private OrderDTO convert(OrderDetail detail) {
            OrderDTO dto=new OrderDTO();
            dto.setId(detail.getId());
            dto.setPrice(detail.getPrice());
            dto.setProdId(detail.getProdId());
            dto.setUserId(detail.getUserId());
            return dto;
        }
    
        @RequestMapping("/delete/{id}/{userId}")
        public BaseResponse delete(@PathVariable("id") long id,@PathVariable("userId") long userId){
            orderService.deleteOrder(id,userId);
            return new BaseResponse();
        }
    
        @RequestMapping("/update")
        public BaseResponse update(@RequestBody OrderDTO order){
            OrderUpdateRequest updateOrder=new OrderUpdateRequest();
            updateOrder.setId(order.getId());
            updateOrder.setPrice(order.getPrice());
            updateOrder.setProdId(order.getProdId());
            updateOrder.setUserId(order.getUserId());
            orderService.updateOrder(updateOrder);
            return new BaseResponse();
        }
    
        @RequestMapping("/add")
        public BaseResponse add(@RequestBody OrderDTO order){
            OrderAddRequest request=new OrderAddRequest();
            request.setPrice(order.getPrice());
            request.setProdId(order.getProdId());
            request.setUserId(RandomUtils.nextInt(10,20));
            orderService.insertOrder(request);
            BaseResponse baseResponse = new BaseResponse();
            return baseResponse;
        }
    
        @RequestMapping("/getlist/{userId}")
        public BaseResponse orderList(@PathVariable long userId){
            List<OrderDetail>orders= orderService.getOrderListByUserId(userId);
            List<OrderDTO> orderDTOS = orders.stream().map(this::convert).collect(Collectors.toList());
            BaseResponse response=new BaseResponse();
            response.setSuccess(true);
            response.setCode(200);
            response.setData(orderDTOS);
            return response;
        }
    }

    其他service层代码就是简单调用,mapper代码

    @Mapper
    public interface OrderMapper {
    
        @Select("select id,prod_id prodId,user_id userId,price  from order where id =#{id} and user_id=#{userId}")
        Order selectByPrimaryKey(@Param("id") long id,@Param("userId") long userId);
    
    //此处不应出现id,否则sharding-jdbc不会自动生成id @Insert(
    "insert into order (prod_id,user_id,price) value(#{prodId},#{userId},#{price} )") void insertOrder(Order order); @Delete("delete from order where id=#{id} and user_id=#{userId}") void deleteById(@Param("id") long id,@Param("userId")long userId); @Update("update order set prod_id=#{prodId},user_id=#{userId},price=#{price} where id=#{id}") void updateOrder(Order order); @Select("select id,prod_id prodId,user_id userId,price from order where user_id=#{userId}") List<Order> selectListByUserId(long userId); }

    4测试

    新增/获取/获取列表 等都正常。测试结果略

    以上是这两天学习sharding-jdbc的简单入门demo,前路漫漫~~

  • 相关阅读:
    js中undefined,null,NaN的区别
    js中数字计算精度
    BestCoder Round #32
    POJ 2299 求逆序对(归并排序或树状数组)
    POJ 2603
    CodeForces 515C
    POJ 1853 背包问题
    UVA 10115 子符串替换
    POJ 1155 树状dp
    HDU 2196 树状dp 求树中节点之间的最长距离
  • 原文地址:https://www.cnblogs.com/yoohot/p/9471168.html
Copyright © 2020-2023  润新知