java+mybatis分表路由

概念：

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

1、定义路由接口

public interface RouteTableAble {/** * 共多少表,默认10 * @return */public int tableSize();/** * 使用该值 % tableSize  * @return */public int routeValue();/**  * 路由table的后缀 * @return */public String tableExt();/** * 连接table的字符串 * @return */public String concat();}

2、实现接口（默认10张子表，以‘_’作为连接符，采用routeValue和子表总数取模作为表序号）

public abstract class AbstractRouteTable implements RouteTableAble {@Overridepublic int tableSize() {// TODO Auto-generated method stubreturn 10;}@Transient@Overridepublic String concat() {// TODO Auto-generated method stubreturn "_";}@Transient@Overridepublic String tableExt() {// TODO Auto-generated method stubreturn concat().concat(String.valueOf(routeValue() % tableSize()));}}

3、继承父类，重写子表总数方法，采用VIN字段后8位的hashcode作为routeValue

public class CarHealthBasic extends AbstractRouteTable implements Serializable {private static final long serialVersionUID = 2291302184719452210L;/**     * id     */@Id    private Long id;/** * 上传的企业id */private Long companyid;    /**     * 车牌号码     */    private String vehicleplatenumber;    /**     * 维修企业名称     */    private String companyname;    /**     * 维修企业编码     */    private String companycode;    /**     * 车辆实别代码     */    private String vin;    /**     * 送修日期     */    private Date repairdate;    /**     * 送修里程     */    private String repairmileage;    /**     * id     * @return id id     */    public Long getId() {        return id;    }    /**     * id     * @param id id     */    public void setId(Long id) {        this.id = id;    }    /**     * 车牌号码     * @return vehicleplatenumber      */    public String getVehicleplatenumber() {        return vehicleplatenumber;    }    /**     * 车牌号码     * @param vehicleplatenumber      */    public void setVehicleplatenumber(String vehicleplatenumber) {        this.vehicleplatenumber = vehicleplatenumber == null ? null : vehicleplatenumber.trim();    }    /**     * 维修企业名称     * @return companyname      */    public String getCompanyname() {        return companyname;    }    /**     * 维修企业名称     * @param companyname      */    public void setCompanyname(String companyname) {        this.companyname = companyname == null ? null : companyname.trim();    }    /**     * 维修企业编码     * @return companycode      */    public String getCompanycode() {        return companycode;    }    /**     * 维修企业编码     * @param companycode      */    public void setCompanycode(String companycode) {        this.companycode = companycode == null ? null : companycode.trim();    }    /**     * 车辆实别代码     * @return vin      */    public String getVin() {        return vin;    }    /**     * 车辆实别代码     * @param vin      */    public void setVin(String vin) {        this.vin = vin == null ? null : vin.trim();    }    /**     * 送修日期     * @return repairdate      */    public Date getRepairdate() {        return repairdate;    }    /**     * 送修日期     * @param repairdate      */    public void setRepairdate(Date repairdate) {        this.repairdate = repairdate;    }    /**     * 送修里程     * @return repairmileage      */    public String getRepairmileage() {        return repairmileage;    }    /**     * 送修里程     * @param repairmileage      */    public void setRepairmileage(String repairmileage) {       this.repairmileage = repairmileage == null ? null : repairmileage.trim();   }    public Long getCompanyid() {return companyid;   }    public void setCompanyid(Long companyid) {this.companyid = companyid;   }    @Override    public int routeValue() {// TODO Auto-generated method stubreturn vin.substring(vin.length()-8, vin.length()).hashCode();   }        @Override    public int tableSize() {// TODO Auto-generated method stubreturn 20;    }    }

4、mybatis（增删改差都需要拼接）

public String insertSQL(Object t) {StringBuilder buffer = new StringBuilder();buffer.append("INSERT INTO ").append(getTableFromClass(t.getClass())).append(getRouteTableFromObject(t)).append(" ");try {List<com.ctjy.support.mybaits.provider.Entry>list = getKeyValueEntry(t);buffer.append("(");for (com.ctjy.support.mybaits.provider.Entry entry : list) {buffer.append(entry.getColumn()).append(",");}String str = buffer.substring(0, buffer.length() - 1);buffer.setLength(0);buffer.append(str).append(") ");buffer.append("VALUES (");for (com.ctjy.support.mybaits.provider.Entry entry : list) {buffer.append("?").append(",");}str = buffer.substring(0, buffer.length() - 1);buffer.setLength(0);buffer.append(str).append(") ");} catch (IntrospectionException e) {e.printStackTrace();}return buffer.toString();}

5、getRouteTableFromObject方法代码如下：判断该pojo是否为RouteTable的子类，如果是则返回表名后缀

public String getRouteTableFromObject(Object obj) {if (obj == null) {return "";}String ext = null;if (RouteTableAble.class.isAssignableFrom(obj.getClass())) {RouteTableAble route = (RouteTableAble) obj;ext = route.tableExt();}return ext == null ? "" : ext;}

6、关于mapper文件的处理，通过tablesize拼接sql语句中的表名（查询参数中多加一个tablesize，该值通过分表规则算出）

<!-- 查询维修记录 --><select id="findRepairLog" resultType="com.ctjy.wxmis.carhealth.dto.CarHealthBasicSuperviseDto">select a.id,a.vehicleplatenumber,a.companyname,a.companycode,a.vin,a.repairdate,a.repairmileage,a.settledate,a.faultdescription,a.costlistcodefrom <choose><when test="healthParam.tablesize != null">car_health_basic${healthParam.tablesize} a</when><otherwise>car_health_basic a</otherwise></choose><include refid="cond" />limit #{index},#{pagesize}</select>