We have completed the springboot integration of shardingjdbc and obtained a valid DataSource, which proves that we can already operate the database through shardingjdbc connection. In this paper, we will use Springboot to integrate ShardingJDBC + Mybatis, the real implementation of sub-library sub-table operation. Go ahead, please

Integration of mybatis

  • Add myBatis dependency
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
</dependency>
Copy the code
  • Build the project structure and add the configuration for Mybatis

mybatis.mapper-locations=classpath:mapping/*.xml
mybatis.type-aliases-package=com.echo.shardingjdbc.po
Copy the code
  • Add the DAO scan configuration on the startup class
@MapperScan("com.echo.shardingjdbc.dao")
Copy the code
  • Generate the corresponding code in reverse, mainly write the code of increase, deletion and change, the code is as follows:
/ * * *@author tang.sl
 * @date2021/6/30003 PM 16:37 */
@RestController
@RequestMapping("/order")
public class TOrderController {

    @Autowired
    private TOrderService tOrderService;

    @PostMapping("/save")
    public String save(@RequestBody TOrder tOrder) {
        tOrderService.save(tOrder);
        return "success";
    }

    @PostMapping("/delete")
    public String delete(@RequestParam(value = "id") Long id) {
        tOrderService.delete(id);
        return "success";
    }

    @PostMapping("/update")
    public int update(@RequestBody TOrder tOrder) {
        return tOrderService.update(tOrder);
    }

    @GetMapping("/getList")
    public List<TOrder> getList(a) {
        returntOrderService.getList(); }}public interface TOrderService {
    void save(TOrder tOrder);

    void delete(Long id);

    int update(TOrder tOrder);

    List<TOrder> getList(a);
}

@Service
public class TOrderServiceImpl implements TOrderService {

    @Autowired
    private TOrderDao tOrderDao;

    @Override
    public void save(TOrder tOrder) {
        tOrderDao.insert(tOrder);
    }

    @Override
    public void delete(Long id) {
        tOrderDao.delete(id);
    }

    @Override
    public int update(TOrder tOrder) {
        return tOrderDao.update(tOrder);
    }

    @Override
    public List<TOrder> getList(a) {
        returntOrderDao.getList(); }}public interface TOrderDao {

    void insert(TOrder tOrder);

    List<TOrder> getList(a);

    void delete(Long id);

    int update(TOrder tOrder); } <? 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.echo.shardingjdbc.dao.TOrderDao">
    <resultMap id="BaseResultMap" type="com.echo.shardingjdbc.po.TOrder">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="user_id" jdbcType="INTEGER" property="userId"/>
        <result column="order_id" jdbcType="INTEGER" property="orderId"/>
        <result column="cloumn" jdbcType="VARCHAR" property="cloumn"/>
    </resultMap>
    <sql id="Base_Column_List">
        id, user_id, order_id, cloumn
    </sql>

    <insert id="insert" parameterType="com.echo.shardingjdbc.po.TOrder">
        insert into t_order (user_id, order_id, cloumn) value (#{userId}, #{orderId}, #{cloumn})
    </insert>

    <select id="getList" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from t_order
    </select>

    <delete id="delete" parameterType="java.lang.Long">
        delete from t_order
        where id = #{id,jdbcType=BIGINT}
    </delete>

    <update id="update" parameterType="com.echo.shardingjdbc.po.TOrder">
        update t_order
        set
          cloumn = #{cloumn,jdbcType=VARCHAR},
          order_id = #{orderId,jdbcType=INTEGER},
          user_id = #{userId,jdbcType=INTEGER}
        where id = #{id,jdbcType=BIGINT}
    </update>
</mapper>
Copy the code

The project structure is as follows

Add a primary key generation policy

spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
Copy the code

Adjust the strategy of dividing databases and tables

Note: multiple fields to determine the database table, it will be difficult to get started, it is also difficult to clear the logical relationship, so here directly use id to divide the database table. The configuration is as follows:

# configure the branch library policy
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}
# Configure a sub-table policy
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0.. 1}.t_order$->{0.. 1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{id % 2}
Copy the code

Add log output configuration to observe shardingjdbc operations

spring.shardingsphere.props.sql.show = true
logging.level.com.echo.shardingjdbc.dao=DEBUG
Copy the code

Now that we’ve done the basic coding, we can start the project to see if it works, and if it doesn’t, we need to check the configuration. If successful, then we can do something to observe the operation of ShardingJDBC

Enter the test process

  • Use Postman to create points of data to call the interface we wrote earlier

There is no need for us to enter the ID manually, we have configured the id generation strategy in the configuration, it will automatically generate, and finally we can see that the application will go into ds0 if id%2 is 0, and ds1 if it is 1, according to our repository rules. So if id%2 is equal to 0 then it’s going to enter T_order0, and if id%2 is equal to 1 it’s going to enter T_order1.

And we can see that id ends in 4, so that fits id%2 is equal to 0, so it goes into ds0 and T_order0 as we said above

  • Test the UPDATE process

Note that if you are using a database with an ID, you must have an ID when updating the database, otherwise it will not reach the database table earlier.

Using this example to test, we can finally see that ShardingJDBC helped us push the information to the corresponding database and effectively updated it

When our condition is id, we can see that it helps us find the library and table, but if the updated information does not contain id, it will report an error

The reason is that, after the database is divided into tables, if the update, it does not use the database and table field as the condition, it cannot find the table to be updated

  • Deleting is the same as updating, so I won’t go into details here

  • The query

Query is actually relatively simple, we can according to the sub-database sub-table field to query, also can directly check all, when checking all, we can see that it puts the corresponding library table in the information all out

There are only two reasons shown here

conclusion

  • When using ShardingJDBC, you need to pay attention to many places where you need to bring the sub-database sub-table field, otherwise you will not be able to operate
  • Configuration isn’t really that hard, but it’s easy to write wrong