Spring-mybatis-data-common Program-level database sharding operation instance

The table sharding mechanism was added to the spring-mybatis-data-common-2.0, and some adjustments were made on the basis of 1.0.

Display sub-database applications based on racks
Database shard strength Creation

create table tb_example_1(  id bigint primary key auto_increment ,  eId bigint,  exampleName varchar(40),  exampleTitle varchar(200),  exampleDate datetime)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  create table tb_example_2 like tb_example_1;create table tb_example_3 like tb_example_1;create table tb_example(  id bigint primary key auto_increment ,  eId bigint,  exampleName varchar(40),  exampleTitle varchar(200),  exampleDate datetime)ENGINE=MERGE UNION=(tb_example_1,tb_example_2,tb_example_3) INSERT_METHOD=LAST AUTO_INCREMENT=1 ; 

Program table sharding
1. In spring-mybatis-common-data, com. spring. mybatis. data. common. model. ExampleModel is provided for the entity class used for Demo.
Add maven dependency

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">    <modelVersion>4.0.0</modelVersion>    <groupId>com.spring.mybatis</groupId>    <artifactId>com-spring-mybatis-common-data-demo</artifactId>    <packaging>war</packaging>    <version>0.0.1-SNAPSHOT</version>    <name>com-spring-mybatis-common-data-demo Maven Webapp</name>    <url>http://maven.apache.org</url>    <properties>        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>        <spring.mybatis.data.version>2.0</spring.mybatis.data.version>        <junit.version>4.11</junit.version>    </properties>    <dependencies>        <!-- spring-mybatis-data-common begin -->        <dependency>            <groupId>com.spring.mybatis</groupId>            <artifactId>spring-mybatis-data-common</artifactId>            <version>${spring.mybatis.data.version}</version>        </dependency>        <!-- spring-mybatis-data-common end -->        <dependency>            <groupId>junit</groupId>            <artifactId>junit</artifactId>            <version>${junit.version}</version>            <scope>test</scope>        </dependency>        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>druid</artifactId>            <version>0.2.26</version>        </dependency>        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>5.1.1</version>        </dependency>        <dependency>            <groupId>org.codehaus.jackson</groupId>            <artifactId>jackson-mapper-asl</artifactId>            <version>1.9.13</version>        </dependency>    </dependencies>    <build>        <finalName>com-spring-mybatis-common-data-demo</finalName>    </build></project>

2. The Persistence Layer inherits the sub-Table Dao interface and can also be customized.

@Repositorypublic interface ExampleModelDao extends ShardCudDao<ExampleModel>,ShardReadDao<ExampleModel>{    /**get common base table max auto_increment id*/    public Long selectMaxId() throws DaoException;}

In the persistence layer, a selectMaxId () is customized for multiple sub-tables to share the same auto-increment policy. Program-level control is used here.
3. The business layer inherits sub-tables. The business layer operations can be customized, or inherit the conventional services provided in com. spring. mybatis. data. common. service. BaseService.

@Servicepublic class ExampleModelService extends BaseShard{    @Autowired    private ExampleModelDao exampleModelDao;        @Override    public String getBaseShardTableName() {        return "tb_example_";    }    @Override    public int getShardTableCount() {        return 3;    }    public int deleteObject(ExampleModel entity) throws ServiceException {        try {            return this.exampleModelDao.delete(getShardTableName(entity.geteId()+"", 1), entity.getId());        } catch (DaoException e) {            e.printStackTrace();        }        return 0;    }    public int save(ExampleModel entity) throws ServiceException {        long mxId = 1;        try {            Long maxId = this.exampleModelDao.selectMaxId();            if(null != maxId && maxId >= 1){                mxId = maxId + 1;            }        } catch (DaoException e) {            LogUtils.dao.error("insert exampleModel before get Max Id error.",e);            e.printStackTrace();        }        try {            entity.setId(mxId);            return this.exampleModelDao.insert(getShardTableName(entity.geteId()+"", 1), entity);        } catch (DaoException e) {            LogUtils.dao.error("insert exampleModel to table " + getShardTableName(entity.geteId()+"", 1) + " error");            e.printStackTrace();        }        return 0;    }        public ExampleModel selectObject(ExampleModel entity)            throws ServiceException {        try {            return this.exampleModelDao.selectById(getShardTableName(entity.geteId()+"", 1), entity.geteId());        } catch (DaoException e) {            e.printStackTrace();        }        return null;    }        public void setExampleModelDao(ExampleModelDao exampleModelDao) {        this.exampleModelDao = exampleModelDao;    }    }

BaseShard is an abstract class. to inherit it, you need to implement two methods.

    /**     * get shard table count     * @return     */    public abstract int getShardTableCount();        /**     * get base shard name     * @return     */    public abstract String getBaseShardTableName();

GetShardTableCount () is used to return the number of table shards. public abstract String getBaseShardTableName () is used to return the unified prefix of table shards.
For example, in an instance, the table shards are tb_example, tb_example_1, tb_example_2, and tb_example_3. The table shard name prefix is "tb_example _", and the number of table shards is 3.
Operations to obtain the ing table name in BaseShard

    /**     * get shard table name <br>     *      * shard table index start with 0     *      * just like follows     *      * tb_example_0     * tb_example_1     * tb_example_2     * tb_example_3     *      * @param tableName     * @return     */    public String getShardTableName(String shardKey);    /**     * get shard table name <br>     *      * shard table index start with (0+baseNumber)     *      * just like follows     *      * tb_example_(0+baseNumber)     * tb_example_(1+baseNumber)     * tb_example_(2+baseNumber)     * tb_example_(3+baseNumber)     *      *      * @param shardKey     * @param baseNumber     * @return     */    public String getShardTableName(String shardKey,int baseNumber);

4. Persistence Layer implementation

<?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.spring.mybatis.common.data.demo.dao">      <!--insert entity-->    <insert id="insert" parameterType="exampleModel" flushCache="true">      INSERT INTO ${tablename}(            id,            eId,            exampleName,            exampleTitle,            exampleDate)    VALUES(        #{object.id},        #{object.eId},        #{object.exampleName},        #{object.exampleTitle},        #{object.exampleDate}    )  </insert>    <select id="selectMaxId" resultType="long">          select max(id) from tb_example  </select>    <delete id="delete" parameterType="long" flushCache="true">      DELETE FROM          ${tablename}      WHERE          id=#{id}  </delete>    <select id="selectById" parameterType="long" resultType="exampleModel">      SELECT           id AS id,eId AS eId,exampleName AS exampleName,exampleTitle AS exampleTitle,exampleDate AS exampleDate      FROM           ${tablename}      WHERE          id=#{id}  </select>  </mapper>

 

Program running result

Query sub-tables

Instance download: http://files.cnblogs.com/dennisit/spring-mybatis-data-common-2.0-and-demo.zip

For more information, see [http://www.cnblogs.com/dennisit/p/4103501.html].