这里的归档水平分表,针对的是一张表内的数据在经过一段时间或某些状态位变化后,需要将这部分数据清除出该表以提高性能,而清除出的数据放入归档表中。这篇文章主要讲述如何尽量少的改动代码来读取归档表数据,并能将归档表数据重新移动至原表。
代码以java、hibernate、mysql为例。
首先是制作定时器,定时将数据归档至归档表,以SysAccount账户表归档至SysAccountAr表为例。SysAccount表主键自增,SysAccountAr表主键不设置自增,使用从主表归档过来的数据的主键。
之后是尽量少的改动代码,这里采用的是hibernate的entity-name功能,即不需要创建实体类,只有hbm.xml,得到的单个实体数据为Map形式,key为类的属性,value为值。
SysAccount.hbm.xml内容如下
<hibernate-mapping>
<class name="com.rbac.entity.SysAccount" table="sys_account" catalog="rbac" dynamic-insert="true" dynamic-update="true">
<id name="id" type="java.lang.Long">
<column name="ID" />
<generator class="native" />
</id>
<property name="username" type="java.lang.String">
<column name="USERNAME" length="20" not-null="true">
<comment>用户登录名</comment>
</column>
</property>
</class>
</hibernate-mapping>
SysAccountAr.hbm.xml内容如下
<hibernate-mapping>
<class entity-name="SysAccountAr" table="sys_account_ar" catalog="rbac" dynamic-insert="true" dynamic-update="true">
<id name="id" type="java.lang.Long">
<column name="ID" />
<generator class="assigned" />
</id>
<property name="username" type="java.lang.String">
<column name="USERNAME" length="20" not-null="true">
<comment>用户登录名</comment>
</column>
</property>
</class>
</hibernate-mapping>
区别是归档表的id需要设置为指定而非自增
而entity-name里的值就是hql语句中的表名,所以不用加包名更简洁
由于使用entity-name查找到的是Map,所以需要有Map和实体类互相转换的方法,可以在别的网站搜索,例如
package com.rbac.util;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Date;
import java.util.Map;
import org.apache.commons.beanutils.PropertyUtils;
public class MapConvertUtil {
/**
* 把Map<String,Object>处理成实体类,不会对非java基础类型进行转换
* @param clazz 想要的实体类
* @param map 包含信息的Map对象
* @return
*/
public static Object mapToObject(Class clazz, Map<String,Object> map){
if(map == null){
return null;
}
Field[] fields = clazz.getDeclaredFields();
Field field;
Object o = null;
try {
o = clazz.newInstance();
} catch (InstantiationException e1) {
e1.printStackTrace();
} catch (IllegalAccessException e1) {
e1.printStackTrace();
}
for(int i=0; i<fields.length; i++){
field = fields[i];
String fieldName = field.getName();
//跳过值为null的属性
if(map.get(fieldName)==null){
continue;
}
//把属性的第一个字母处理成大写
String stringLetter=fieldName.substring(0, 1).toUpperCase();
//取得set方法名,比如setId
String setterName="set"+stringLetter+fieldName.substring(1);
Method setMethod = null;
Class fieldClass = field.getType();
try {
if(isHaveSuchMethod(clazz, setterName)){
if(fieldClass == String.class){
setMethod = clazz.getMethod(setterName, fieldClass);
setMethod.invoke(o, String.valueOf(map.get(fieldName)));
}else if(fieldClass == Integer.class || fieldClass == int.class){
setMethod = clazz.getMethod(setterName, fieldClass);
setMethod.invoke(o, Integer.parseInt(String.valueOf(map.get(fieldName))));
}else if(fieldClass == Boolean.class || fieldClass == boolean.class){
setMethod = clazz.getMethod(setterName, fieldClass);
setMethod.invoke(o, Boolean.getBoolean(String.valueOf(map.get(fieldName))));
}else if(fieldClass == Short.class || fieldClass == short.class){
setMethod = clazz.getMethod(setterName, fieldClass);
setMethod.invoke(o, Short.parseShort(String.valueOf(map.get(fieldName))));
}else if(fieldClass == Long.class || fieldClass == long.class){
setMethod = clazz.getMethod(setterName, fieldClass);
setMethod.invoke(o, Long.parseLong(String.valueOf(map.get(fieldName))));
}else if(fieldClass == Double.class || fieldClass == double.class){
setMethod = clazz.getMethod(setterName, fieldClass);
setMethod.invoke(o, Double.parseDouble(String.valueOf(map.get(fieldName))));
}else if(fieldClass == Float.class || fieldClass == float.class){
setMethod = clazz.getMethod(setterName, fieldClass);
setMethod.invoke(o, Float.parseFloat(String.valueOf(map.get(fieldName))));
}else if(fieldClass == BigInteger.class ){
setMethod = clazz.getMethod(setterName, fieldClass);
setMethod.invoke(o, BigInteger.valueOf(Long.parseLong(String.valueOf(map.get(fieldName)))));
}else if(fieldClass == BigDecimal.class){
setMethod = clazz.getMethod(setterName, fieldClass);
setMethod.invoke(o, BigDecimal.valueOf(Double.parseDouble(String.valueOf(map.get(fieldName)))));
}else if(fieldClass == Date.class){
setMethod = clazz.getMethod(setterName, fieldClass);
if(map.get(fieldName).getClass() == java.sql.Date.class){
setMethod.invoke(o, new Date(((java.sql.Date)map.get(fieldName)).getTime()));
}else if(map.get(fieldName).getClass() == java.sql.Time.class){
setMethod.invoke(o, new Date(((java.sql.Time)map.get(fieldName)).getTime()));
}else if(map.get(fieldName).getClass() == java.sql.Timestamp.class){
setMethod.invoke(o, new Date(((java.sql.Timestamp)map.get(fieldName)).getTime()));
}
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
return o;
}
/**
* 判断某个类里是否有某个方法
* @param clazz
* @param methodName
* @return
*/
private static boolean isHaveSuchMethod(Class<?> clazz, String methodName){
Method[] methodArray = clazz.getMethods();
boolean result = false;
if(null != methodArray){
for(int i=0; i<methodArray.length; i++){
if(methodArray[i].getName().equals(methodName)){
result = true;
break;
}
}
}
return result;
}
/**
* 实体转Map,不会对非java基础类型进行转换
* @param entity
* @return
*/
public static Map objectToMap(Object entity){
try {
return PropertyUtils.describe(entity);
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
return null;
}
}
然后是dao层的操作
package com.rbac.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.hibernate.Hibernate;
import org.springframework.stereotype.Component;
import com.rbac.common.BaseDaoSupport;
import com.rbac.entity.SysAccount;
import com.rbac.util.MapConvertUtil;
@Component("archiveDao")
public class ArchiveDao extends BaseDaoSupport{
public void saveOrUpdateAccountAr(SysAccount account){
super.getHibernateTemplate().saveOrUpdate("SysAccountAr", account);
}
public void saveOrUpdateAccountAr(Map map){
super.getHibernateTemplate().saveOrUpdate("SysAccountAr", map);
}
public SysAccount getAccountById(Long id){
//必须用HibernateDaoSupport.getSession才能initialize
//如果使用getHibernateTemplate就会报错collection is not associated with any session
SysAccount account = (SysAccount)super.getSession().get(SysAccount.class, id);
return account;
}
public Map getAccountArById(Long id){
Map map = (Map)super.getSession().get("SysAccountAr", id);
return map;
}
/**
* 根据用户名读取用户实体,主表有数据返回主表数据,否则返回归档表数据
* @param username
* @return HashMap,得到主表实体key为SysAccount,得到归档表实体key为SysAccountAr
*/
public Map<String,SysAccount> loadByNameIncludeAr(String username){
Map<String,SysAccount> tableEntityMap = new HashMap<String,SysAccount>();
List accountList = super.getSession().createQuery("from SysAccount where username=:username").setString("username", username).list();
if(accountList.size()>0){
tableEntityMap.put("SysAccount", (SysAccount)accountList.get(0));
return tableEntityMap;
}
List accountArList = super.getSession().createQuery("from SysAccountAr where username=:username").setString("username", username).list();
if(accountArList.size()>0){
Map accountArMap = (Map)accountArList.get(0);
SysAccount account = (SysAccount)MapConvertUtil.mapToObject(SysAccount.class, accountArMap);
tableEntityMap.put("SysAccountAr", account);
return tableEntityMap;
}
return tableEntityMap;
}
/**
* 保存SysAccount,根据tabelname决定保存到主表还是归档表,如果要保存到归档表,主键必须赋值
* @param tableName 主表为SysAccount,归档表为SysAccountAr
* @param account
*/
public void saveOrUpdateAccount(String tableName, SysAccount account) throws Exception{
if("SysAccount".equals(tableName)){
super.getSession().saveOrUpdate(account);
}
else if("SysAccountAr".equals(tableName)){
if(account.getId()==null){
throw new Exception("归档表必须指定id");
}
Map arMap = MapConvertUtil.objectToMap(account);
super.getSession().saveOrUpdate("SysAccountAr", arMap);
}
}
/**
* 将归档表记录移动到主表中,id保持不变
* @param username
*/
public void moveAccountFromAr(String username) throws Exception{
Map<String,SysAccount> map = loadByNameIncludeAr(username);
if(map.containsKey("SysAccountAr")){
SysAccount account = map.get("SysAccountAr");
Long arId = account.getId();
//对主表插入数据时,需要判断arId在主表中是否已存在
if(super.getSession().get(SysAccount.class, arId)!=null){
throw new Exception("id为"+arId+",username为"+username+"的记录在主表中已存在");
}
//因为有id,却要做新增操作,此时保存主表会报错,目前采用save后修改id的方法
//用persist会报错 detached entity passed to persist
//用save不会报错,但id取自增的数,且save后无法回滚
//用saveOrUpdate不会报错,但没有新增到account表
super.getSession().save(account);
//这个更新需要考虑arId在主表中是否已存在
super.getSession().createQuery("update SysAccount set id=:arId where id=:id").setLong("arId", arId).setLong("id", account.getId()).executeUpdate();
//删除归档表记录
super.getSession().createQuery("delete from SysAccountAr where id=:id").setLong("id", arId).executeUpdate();
}
else{
throw new Exception("归档表内找不到"+username);
}
}
}
以上功能包括了将实体保存至归档表、从归档表读取实体、将归档表记录移动至主表。
之后的扩展包括建立二级归档表,依赖主表的其他主表建立归档表等。