springboot篇】二十一. 基于springboot电商项目 十三 分库分表应用项目中

中国加油,武汉加油!

篇幅较长,配合目录观看

案例准备

  1. 本案例基于springboot篇】二十一. 基于springboot电商项目 十三 分库分表介绍和案例

1. 分库整合项目中

1.1 脚本环境准备

在这里插入图片描述

1.2 order-service-api修改Service接口

package com.wpj.service;

import com.wpj.entity.User;

public interface IOrderService {
    public int addOrder(Integer addressId, User user);
}

1.3 order-service修改ServiceImpl实现类

package com.wpj.service.impl;

import com.alibaba.dubbo.config.annotation.Reference;
import com.alibaba.dubbo.config.annotation.Service;
import com.wpj.common.utils.OrderUtils;
import com.wpj.entity.*;
import com.wpj.mapper.IOrderMapper;
import com.wpj.service.IAddressService;
import com.wpj.service.ICartService;
import com.wpj.service.IOrderDetailService;
import com.wpj.service.IOrderService;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class OrderServiceImpl implements IOrderService {
    @Autowired
    private IOrderMapper orderMapper;
    @Reference
    private IAddressService addressService;
    @Reference
    private ICartService cartService;
    @Reference
    private IOrderDetailService orderDetailService;
    @Autowired
    private OrderUtils orderUtils;

    @Override
    public int addOrder(Integer addressId, User user) {
        // 根据地址id查询地址的对象
        Address address = addressService.selectById(addressId);
        List<Cart> cartList = cartService.getUserCartList(user,"");
        // 插入订单
        Order order = new Order();
        String orderId = orderUtils.createOrderId(user.getId());
        order.setId(orderId);
        order.setUid(user.getId());
        order.setPerson(address.getPhone());
        order.setAddress(address.getAddress());
        order.setCreateTime(new Date());
        order.setOstatus(0); // 0 未支付 1 已支付 2 超时 3 取消
        order.setPerson(address.getPerson());
        order.setTotalPrice(cartService.getTotalPrice(cartList));
        // 设置数据源
        orderMapper.addOrder(order);
        // 插入订单详情
        List<OrderDetail> orderDetailList = new ArrayList<>();
        for (Cart cart: cartList) {
            OrderDetail orderDetail = new OrderDetail();
            Goods goods = cart.getGoods();
            orderDetail.setGprice(goods.getGprice());
            orderDetail.setGpic(goods.getGpic());
            orderDetail.setGnum(cart.getNum());
            orderDetail.setGname(goods.getGname());
            orderDetail.setGid(goods.getId());
            orderDetail.setGcount(cart.getSubTotal());
            orderDetail.setGdesc(goods.getGdesc());
            orderDetailList.add(orderDetail);

            if (orderDetailList.size() == 300) {
                orderDetailService.addBarch(orderDetailList);
                orderDetailList.clear();
            }

        }
        if (orderDetailList.isEmpty()){
            orderDetailService.addBarch(orderDetailList);
        }
        // 清空购物车
        cartService.deleteCartByUid(user.getId());
        return orderMapper.addOrder(order);
    }
}

1.4 shop-order修改Controller

@RequestMapping("/addOrder")
@IsLogin(mustUser = true)
@ResponseBody
public String addOrder(Integer addressId,User user){
    orderService.addOrder(addressId, user);
    // 跳转到支付页面

    return "ok";
}

1.5 修改order-service的yml

spring:
  datasource1:
    url: jdbc:mysql://localhost:3306/nz1904-springboot-shop
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    aliases: db1
  datasource2:
    url: jdbc:mysql://localhost:3306/nz1904-springboot-shop-02
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
    aliases: db2
  redis:
    host: 192.168.59.100
    password: admin
mybatis-plus:
  type-aliases-package: com.wpj.entity
  mapper-locations: classpath:/mapper/*.xml
dubbo:
  application:
    name: order-service
  registry:
    address: zookeeper://192.168.59.100:2181
  protocol:
    port: -1

1.6 程序入口忽略自动配置数据源

package com.wpj;

import com.alibaba.dubbo.config.spring.context.annotation.DubboComponentScan;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(scanBasePackages = "com.wpj", exclude = DataSourceAutoConfiguration.class)
@DubboComponentScan(basePackages = "com.wpj.service")
@MapperScan(basePackages = "com.wpj.mapper")
public class OrderServiceApplication {
    public static void main(String[] args) {
        SpringApplication.run(OrderServiceApplication.class, args);
    }
}

1.7 定义config类

1.7.1 定义BaseDataSource

package com.wpj.config;

import com.zaxxer.hikari.HikariDataSource;
import lombok.Data;

@Data
public class BaseDataSource {

    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String aliases;

    public HikariDataSource getDataSource(){
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setUsername(username);
        dataSource.setJdbcUrl(url);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);
        return dataSource;
    }
}

1.7.2 定义Order1和Order2DataSource

package com.wpj.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@ConfigurationProperties(prefix = "spring.datasource1")
public class Order1DataSource extends BaseDataSource {
}
package com.wpj.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@ConfigurationProperties(prefix = "spring.datasource2")
public class Order2DataSource extends BaseDataSource {
}

1.7.3 定义DynamicDataSource

package com.wpj.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;

public class DynamicDataSource extends AbstractRoutingDataSource {

    private static ThreadLocal<String> threadLocal = new ThreadLocal<>();

    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return threadLocal.get();
    }

    public static void setDbName(String dbName){
        threadLocal.set(dbName);
    }
}

1.7.4 定义MyBatisConfig

package com.wpj.config;

import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class MyBatisConfig {
    @Autowired
    private Order1DataSource order1DataSource;
    @Autowired
    private Order2DataSource order2DataSource;
    @Value("${mybatis-plus.mapper-locations}")
    private String mapperLocations;
    @Bean
    public DynamicDataSource dynamicDataSource(){
        // 创建一个动态数据源
        DynamicDataSource dataSource = new DynamicDataSource();
        Map<Object,Object> map = new HashMap<>();
        map.put(order1DataSource.getAliases(),order1DataSource.getDataSource());
        map.put(order2DataSource.getAliases(),order2DataSource.getDataSource());
        // 把map放进入
        dataSource.setTargetDataSources(map);
        // 设置默认的数据源
        dataSource.setDefaultTargetDataSource(order1DataSource.getDataSource());
        return dataSource;
    }
    @Bean
    public MybatisSqlSessionFactoryBean sqlSessionFactoryBean(){
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean= new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource());
        sqlSessionFactoryBean.setTypeAliasesPackage("com.wpj.entity");
        try {
            // 设置mapper的路径
            sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return  sqlSessionFactoryBean;
    }
}

1.8 Test

在这里插入图片描述
在这里插入图片描述

2. 分表整合项目中

2.1 shop-mapper修改IOrderMapper接口

package com.wpj.mapper;

import com.wpj.entity.Order;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface IOrderMapper {
    public int addOrder(@Param("order") Order order, @Param("tabIndex") Integer tabIndx);
    
    void updateOrderState(@Param("map") Map<String, String> map, @Param("tabIndex") Integer tabIndex);
    
    Order selectById(@Param("oid") String oid, @Param("tabIndex") Integer tabIndex);
    
    List<Order> getOrderListByUid(@Param("uid") Integer uid, @Param("tabIndex") Integer tabIndex);
}

2.2 修改IOrderMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wpj.mapper.IOrderMapper">

    <insert id="addOrder">
		INSERT INTO t_order${tabIndex} (
			id,
			total_price,
			ocreate_time,
			ostatus,
			u_id,
			person,
			phone,
			address
		)
		VALUES
			(
			  #{order.id},
			  #{order.totalPrice},
			  #{order.createTime},
			  #{order.ostatus},
			  #{order.uid},
			  #{order.person},
			  #{order.phone},
			  #{order.address}
			)
	</insert>
</mapper>

2.3 修改IOrderDetailMapper接口

package com.wpj.mapper;

import com.wpj.entity.OrderDetail;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface IOrderDetailMapper {
    public int addBarch(@Param("list") List<OrderDetail> list, @Param("tabIndex") Integer tabIndex);
}

2.4 修改IOrderDetailMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wpj.mapper.IOrderDetailMapper">
	
	<insert id="addBarch">
		INSERT INTO t_order_detail${tabIndex} (
				id,
				oid,
				gid,
				gname,
				gprice,
				gnum,
				gcount,
				gpic,
				gdesc
			)
			VALUES
			<foreach collection="list" item="od" separator=",">
				(
					#{od.id},
					#{od.oid},
					#{od.gid},
					#{od.gname},
					#{od.gprice},
					#{od.gnum},
					#{od.gcount},
					#{od.gpic},
					#{od.gdesc}
				)
			</foreach>
	</insert>
</mapper>

2.5 order-service修改ServiceImpl实现类

@Override
public int addBarch(List<OrderDetail> list) {
    return 0;
}

2.6 order-service修改ServiceImpl实现类

package com.wpj.service.impl;

import com.alibaba.dubbo.config.annotation.Reference;
import com.alibaba.dubbo.config.annotation.Service;
import com.wpj.common.constant.Constants;
import com.wpj.common.utils.OrderUtils;
import com.wpj.config.DynamicDataSource;
import com.wpj.entity.*;
import com.wpj.mapper.IOrderDetailMapper;
import com.wpj.mapper.IOrderMapper;
import com.wpj.service.IAddressService;
import com.wpj.service.ICartService;
import com.wpj.service.IOrderDetailService;
import com.wpj.service.IOrderService;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class OrderServiceImpl implements IOrderService {
    @Autowired
    private IOrderMapper orderMapper;
    @Reference
    private IAddressService addressService;
    @Reference
    private ICartService cartService;
    @Autowired
    private IOrderDetailMapper orderDetailMapper;
    @Autowired
    private OrderUtils orderUtils;

    @Override
    public int addOrder(Integer addressId, User user) {
        // 根据地址id查询地址的对象
        Address address = addressService.selectById(addressId);
        List<Cart> cartList = cartService.getUserCartList(user,"");
        // 插入订单
        Order order = new Order();
        String orderId = orderUtils.createOrderId(user.getId());
        order.setId(orderId);
        order.setUid(user.getId());
        order.setPerson(address.getPhone());
        order.setAddress(address.getAddress());
        order.setCreateTime(new Date());
        order.setOstatus(0); // 0 未支付 1 已支付 2 超时 3 取消
        order.setPerson(address.getPerson());
        order.setTotalPrice(cartService.getTotalPrice(cartList));
        // 设置数据源
        int tabIndex = setOrderDataSource(user.getId());
        orderMapper.addOrder(order,tabIndex);
        // 插入订单详情
        List<OrderDetail> orderDetailList = new ArrayList<>();
        for (Cart cart: cartList) {
            OrderDetail orderDetail = new OrderDetail();
            Goods goods = cart.getGoods();
            orderDetail.setGprice(goods.getGprice());
            orderDetail.setGpic(goods.getGpic());
            orderDetail.setGnum(cart.getNum());
            orderDetail.setGname(goods.getGname());
            orderDetail.setGid(goods.getId());
            orderDetail.setGcount(cart.getSubTotal());
            orderDetail.setGdesc(goods.getGdesc());
            orderDetailList.add(orderDetail);

            if (orderDetailList.size() == 300) {
                orderDetailMapper.addBarch(orderDetailList, tabIndex);
                orderDetailList.clear();
            }

        }
        if (orderDetailList.isEmpty()){
            orderDetailMapper.addBarch(orderDetailList, tabIndex);
        }
        // 清空购物车
        cartService.deleteCartByUid(user.getId());
        return 1;
    }
    /**
     * 设置order的数据源
     * @param userId
     * @return 表的索引
     */
    public Integer setOrderDataSource(Integer userId) {
        // 获取用户id后四位
        Integer userIdEnd = Integer.parseInt(orderUtils.getUserIdEnd(userId));
        // 根据用户id后四位获取数据源编号
        Integer dbIndex = (userIdEnd % Constants.ORDER_DB_NUM)+1;
        // 设置数据源
        DynamicDataSource.setDbName("db"+dbIndex);
        // 算出表的索引
        Integer tabIndex = (userId/Constants.ORDER_TAB_NUM %Constants.ORDER_TAB_NUM )+1;
        return tabIndex;
    }
}

2.7 启动程序入口测试

  1. 用不同id的用户取购物,然后查看存放在那些库中
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值