The most complete and detailed web Sharding-JDBC entry

preface

Before we start, we have to make fun of the fact that there are very few sharding-JDBC materials available on the web, and most of them are in the 1.x version, which is very early, but sharding-JDBC has now grown to 4.x. What’s more, most of them stay at the conceptual level, and talk about some basic sharding-JDBC concepts back and forth. There are very few practical demos, and some of them don’t work at all. I just want to ask, have you ever run yourself? Oh, I’m so hard.

So I’m going to write a demo, and I hope you guys can add. If you don’t want to see the build process, you can just look at the last GitHub address and pull the code to test it.

specifications

SQL > create tables T_ORDER0, T_order1, T_ORDER_ITEM0; SQL > create tables T_order0, T_order1, t_order_ITEM0; T_order_item1, refer to sharding-Tbl-ms.sql in the SQL statement of the specific table building project.

Environment set up

Database: MySQL 5.1

JDK: 64-bit JDK1.8

Application framework: Spring-boot-2.0.3, Mybatis 3.4

Sharding – JDBC: Sharding – JDBC – spring – the boot – starter 3.1.0. M1

Shard configuration

If you don’t know what each item represents in the application.properties configuration file, let’s leave it at that until the demo runs and the individual configurations are explained in the next article.

Two library names
sharding.jdbc.datasource.names=ds0,ds1

Configuration information for the first library
sharding.jdbc.datasource.ds0.type=org.apache.commons.dbcp.BasicDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=root

Configuration information for the first librarysharding.jdbc.datasource.ds1.type=org.apache.commons.dbcp.BasicDataSourcesharding.jdbc.datasource.ds1.driver-class-name= com.mysql.jdbc.Driver sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1 sharding.jdbc.datasource.ds1.username=root sharding.jdbc.datasource.ds1.password=rootOrder table configuration informationsharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{0.. 1}.t_order$->{0.. 1} sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2} sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=order_idConfiguration information for the order listsharding.jdbc.config.sharding.tables.t_order_item.actual-data-nodes=ds$->{0.. 1}.t_order_item$->{0.. 1} sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id sharding.jdbc.config.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2} sharding.jdbc.config.sharding.tables.t_order_item.key-generator-column-name=order_item_idThe binding relationship between order table and order list
sharding.jdbc.config.sharding.binding-tables=t_order,t_order_item
sharding.jdbc.config.sharding.broadcast-tables=t_config

# default configuration
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}Copy the code

The code

In order to reduce the length, the code is simply pasted some, this part of the code is actually spring-boot and Mybatis integration, this part of the clear can be directly skipped.

Define entities (Order and OrderItem)

public class Order implements Serializable { private static final long serialVersionUID = 661434701950670670L; private long orderId; private int userId; private String status; private List<OrderItem> items=new ArrayList<>(); // Setter and getter methods..... @Override public StringtoString() {
        return String.format("order_id: %s, user_id: %s, status: %s", orderId, userId, status); }}Copy the code

public class OrderItem implements Serializable { private static final long serialVersionUID = 263434701950670170L; private long orderItemId; private long orderId; private int userId; private String status; // Setter and getter methods..... @Override public StringtoString() {
        return String.format("order_item_id:%s, order_id: %s, user_id: %s, status: %s", orderItemId, orderId, userId, status); }}Copy the code

Mapper mapping (OrderMapper.xml and OrderItemMapper.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.forezp.sharedingjdbcmasterslavetables.repository.OrderRepository">
    <resultMap id="baseResultMap" type="com.forezp.sharedingjdbcmasterslavetables.entity.Order">
        <result column="order_id" property="orderId" jdbcType="INTEGER"/>
        <result column="user_id" property="userId" jdbcType="INTEGER"/>
        <result column="status" property="status" jdbcType="VARCHAR"/>
    </resultMap>

    <resultMap type="Order" id="orderMap">
        <id column="order_id" property="orderId"/>
        <result column="user_id" property="userId"/>
        <result column="status" property="status"/>

        <collection property="items" ofType="OrderItem">
            <id column="order_item_id" property="orderItemId"/ > <! --> <result column="user_id" property="userId"/ > <! -- Property corresponds to the JavaBean property name --> <result column="status" property="status"/>
        </collection>
    </resultMap>
    
    <insert id="addOrder" useGeneratedKeys="true" keyProperty="orderId">
        INSERT INTO t_order (user_id, status) VALUES (#{userId,jdbcType=INTEGER}, #{status,jdbcType=VARCHAR});
    </insert>

    <select id="list" resultMap="baseResultMap">
        SELECT * FROM t_order;
    </select>

    <select id="get" resultMap="orderMap">
        SELECT * FROM t_order,t_order_item where t_order.order_id=t_order_item.order_id and t_order.order_id=#{orderId,jdbcType=INTEGER};
    </select>
</mapper> Copy the code

<? 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.forezp.sharedingjdbcmasterslavetables.repository.OrderItemRepository">
<resultMap id="baseResultMap" type="com.forezp.sharedingjdbcmasterslavetables.entity.OrderItem">
    <result column="order_item_id" property="orderItemId" jdbcType="INTEGER"/>
    <result column="order_id" property="orderId" jdbcType="INTEGER"/>
    <result column="user_id" property="userId" jdbcType="INTEGER"/>
    <result column="status" property="status" jdbcType="VARCHAR"/>
</resultMap>

<insert id="addOrderItem" useGeneratedKeys="true" keyProperty="orderItemId">
        INSERT INTO t_order_item (order_id,user_id, status) VALUES (#{orderId,jdbcType=INTEGER},#{userId,jdbcType=INTEGER}, #{status,jdbcType=VARCHAR});
    </insert>
</mapper> Copy the code

The dao (OrderRepository and OrderItemRepository)

@Mapper
public interface OrderRepository {

    Long addOrder(Order order);

    List<Order> list();

    Object get(Long id);

} Copy the code

@Mapper
public interface OrderItemRepository {

    Integer addOrderItem(OrderItem orderitem);

}Copy the code

Service interface (OrderServiceImpl)

@Service
public class OrderServiceImpl implements OrderService {

    @Autowired
    OrderRepository orderRepository;
    @Autowired
    OrderItemRepository orderItemRepository;

    @Override
    public Long addOrder(Order order) {
        orderRepository.addOrder(order);

        OrderItem orderItem=new OrderItem();
        orderItem.setOrderId(order.getOrderId());
        orderItem.setUserId(order.getUserId());
        orderItem.setStatus("insert");
        orderItemRepository.addOrderItem(orderItem);
        return order.getOrderId();
    }

    @Override
    public List<Order> list() {
        return orderRepository.list();
    }

    @Override
    public Object get(Long id){
        returnorderRepository.get(id); }}Copy the code

The controller (OrderController)

@RestController
public class OrderController {

    Logger logger= LoggerFactory.getLogger(OrderController.class);

    @Autowired
    private OrderService orderService;

    @GetMapping("/orders")
    public Object list() {
        return orderService.list();
    }

    @GetMapping("/add")
    public Object add() {
        for(int i=100; i<150; i++) { Order order = new Order(); order.setUserId(i); order.setStatus("insert");
           long resutl=   orderService.addOrder(order);
            logger.info("insert:"+order.toString()+" result:"+resutl);
        }
        return "ok";
    }

    @GetMapping("/get")
    public Object get() {
        returnorderService.get(386632135886241793L); }}Copy the code

The ultimate test

Open a browser, type localhost:8080/add, and return OK, indicating that the data was inserted successfully.

That we’ll look into the database and found the data is sent in the ds0 library and ds1 library, but the problem is why are t_order1 table, not in t_order0 table, is why the repository doesn’t order_id table? In fact, the question is not here, but why are the generated order ids odd? Hahaha, please listen to the next breakdown. Easy day online.

Other queries and other methods are also written, we can try ha.

Making the address

Making:

Github.com/sunnysabor/…

Note:

1. You need to manually build databases and tables by yourself. The specific table construction sentences are in Sharding-Tbl-ms.sql. Both libraries need to be executed.

2. Configure the connection to the database by referring to the local database, and change the account password.

conclusion

Pay attention to me and get more learning materials.