分表设计

由于每天生成的日志文件比较多，可以分表保存，每个月自动生成下个月的表（使用spring定时器，案例用的是xml配置，现在都是用注解配置）。

考虑到上线时的意外，在项目初始化的时候就生成下2个月的表，使用监听器。里面复制表的语句有判断是否表存在。

动态生成表名的方法可以参考一下。这样log的查询和插入语句就要动态更换表名，这里就不写了。

生成表的语句是用hibernate

/**
 * 使用spring集成的石英调度,动态生成日志表
 */
public class GenerateLogsTableTask extends QuartzJobBean {

    //
    private LogService logService ;
    
    public void setLogService(LogService logService) {
        this.logService = logService;
    }

    /**
     * 执行任务
     */
    protected void executeInternal(JobExecutionContext arg0) throws JobExecutionException {
        String tableName =  LogUtil.generateLogTableName(1);
        String sql = "create table if not exists " + tableName + " like logs";
        logService.executeSQL(sql);
        System.out.println(tableName + " 生成了! " );
        
        tableName =  LogUtil.generateLogTableName(2);
        sql = "create table if not exists " + tableName + " like logs";
        logService.executeSQL(sql);
        System.out.println(tableName + " 生成了! " );
    }
}

/**
 * 初始化日志表监听器
 */
@SuppressWarnings("rawtypes")
@Component
public class IniLogTablesListener implements ApplicationListener{

    @Resource
    private LogService logService;
    
    public void onApplicationEvent(ApplicationEvent arg0) {
        //是否是上下文刷新事件
        if(arg0 instanceof ContextRefreshedEvent){
            String tableName = LogUtil.generateLogTableName(0);
            String sql = "create table if not exists " + tableName + " like logs";
            logService.executeSQL(sql);
            
            sql = "create table if not exists " + LogUtil.generateLogTableName(1) + " like logs";
            logService.executeSQL(sql);
            
            sql = "create table if not exists " + LogUtil.generateLogTableName(2) + " like logs";
            logService.executeSQL(sql);
            System.out.println("日志表-"+tableName+",初始化完成");
        }
    }
}

public class LogUtil {
    
    /**
     * 动态生成表名
     */
    public static String generateLogTableName(int offset){
        Calendar c = Calendar.getInstance();
        int year = c.get(Calendar.YEAR);
        int month = c.get(Calendar.MONTH) + 1 + offset;//1-12
        
        if(month > 12){
            year ++ ;
            month = month - 12 ;
        }
        else if(month < 1){
            year -- ;
            month = month + 12 ;
        }
        return "logs_" + year + "_" + month ;
    }
}

 

    //执行原生的sql语句
    public void executeSQL(String sql,Object...objects){
        SQLQuery q = sf.getCurrentSession().createSQLQuery(sql);
        for(int i = 0 ; i < objects.length ; i ++){
            q.setParameter(i, objects[i]);
        }
        q.executeUpdate();
    }