mysql中用hibernate分表设置方案

因需求原因项目中需要用到分表，，现将分表思路说一下

现有二个数据库:

db_Master 

主库 有一个表File 里面有用来存放文件记录信息字段有:FileUUID(varchar(36)),FileSize............N个字段

db_MasterFile

分表的库：用来存放分表的二进制文件（别问为什么把文件存在数据库里面）,有三种二进制文件，原文件二进制，PDF文件二进制，图片二进制

分表的库：表MyFile只有二个字段: FileUUID(varchar(36)与File表的FileUUID关联),  FilePDFData(longblob) 

 

想法是每月所有的记录生成一表 db_MasterFile中的生成的表如下

 

 

@Entity
@Table(name = "MyFile", catalog = "db_MasterFile")
public class MyFile {
    private String FileUUID;
    private byte[] fileData;

    private String yearMonth;//属于哪一个库

    @Id
    @Column(name = "FileUUID")
    public String getFileUUID() {
        return fileUUID;
    }

    public void setFileUUID(String fileUUID) {
        this.fileUUID = fileUUID;
    }


    @Column(name = "FileData")
    public byte[] getFileData() {
        return fileData;
    }

    public void setFileData(byte[] FileData) {
        this.fileData = fileData;
    }

    @Transient
    public String getYearMonth() {        return yearMonth;    }

    public void setYearMonth(String yearMonth) {        this.yearMonth = yearMonth;    }
}

 

 

先添加一个HibernetSQL拦截器代码如下:

/**
 * HibernateSQL拦截器
 */
public class SQLInterceptor extends EmptyInterceptor {

    private static final long serialVersionUID = 8067794420796870506L;

    public SQLInterceptor(String table) {
        this.table = table;
    }

    public String table;//源表名
    private String newTable;//新表名

    public void setNewTable(String newTable) {
        this.newTable = newTable;
    }

    @Override
    public String onPrepareStatement(String sql) {
        System.out.println(sql);
        if (StringUtils.isNotEmpty(newTable)) {
            sql = sql.replaceAll(table, newTable);
        }
        return super.onPrepareStatement(sql);
    }
}

直接贴出Dao层FileDaoImpl的代码如下

@Repository("MyFileDao")
public class MyFileDaoImpl extends BaseDaoImpl<MyFile,String>{
    String DatabaseName="db_MasterFile";//数据库名
    String tableName="MyFile";//表名
    SQLInterceptor interceptor = new SQLInterceptor(tableName);

    /**
     * 判断表是否存在，不存在复制表结构
     * @param YearMonth
     */
    @Transactional(propagation = Propagation.REQUIRED)
    public void checkCreateTable(String YearMonth){
        String sql="create table if not exists "+DatabaseName+"."+tableName+YearMonth+" like "+DatabaseName+"."+tableName;
        SQLQuery query=super.getCurSession().createSQLQuery(sql);
        query.executeUpdate();
    }


    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public MyFile insertOK(MyFile entity){

        checkCreateTable(entity.getYearMonth());

        interceptor.setNewTable(tableName+entity.getYearMonth());
        Session session=getSessionFactory().withOptions().interceptor(interceptor).openSession();
        Transaction tx=session.beginTransaction();

        try {
            session.save(entity);
            tx.commit();
        } catch (Exception e) {
            if(tx!=null) tx.rollback();
        } finally {
            session.close();
        }

        return entity;

    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public String insert(MyFile entity){
        interceptor.setNewTable(tableName+entity.getYearMonth());
        Session session=getSessionFactory().withOptions().interceptor(interceptor).openSession();
        Transaction tx=session.beginTransaction();

        try {
            session.save(entity);
            tx.commit();
        } catch (Exception e) {
            if(tx!=null) tx.rollback();
        } finally {
            session.close();
        }

        return entity.getArchiveDetailUUID();
    }

    @Transactional(propagation = Propagation.REQUIRED)
    public MyFile find(String getArchiveDetailUUID,String YearMonth){
        interceptor.setNewTable(tableName+YearMonth);
        Session session=getSessionFactory().withOptions().interceptor(interceptor).openSession();
        Transaction tx=session.beginTransaction();

        MyFile entity2= null;
        try {
            entity2 = (MyFile)session.get(MyFile.class, getArchiveDetailUUID);
            tx.commit();
        } catch (Exception e) {
            if(tx!=null) tx.rollback();
        } finally {
            session.close();
        }


        return entity2;
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public boolean update(MyFile entity) {
        try {
            interceptor.setNewTable(tableName + entity.getYearMonth());
            Session session = getSessionFactory().withOptions().interceptor(interceptor).openSession();
            Transaction tx = session.beginTransaction();

            try {
                session.saveOrUpdate(entity);
                tx.commit();
            } catch (Exception e) {
                if(tx!=null) tx.rollback();
            } finally {
                session.close();
            }

            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }
}