java代码实现mysql分表(用户行为记录)

设置项目气动执行次方法(每天检查一次表记录)

public class DayInterval implements ServletContextListener{
	private static SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static void showDayTime() {
			Timer dTimer = new Timer();
			dTimer.schedule(new TimerTask() {
			@Override
			public void run() {	
			     System.out.println("每日任务执行:"+simpleDateFormat.format(new Date()));
			     LogTableCreate logTableCreate = new LogTableCreate();
			     Thread thread=new Thread(logTableCreate);
			     thread.start();
			}
			}, 1000 , 24* 60* 60 * 1000);//24* 60* 60 * 1000(第一次一秒后执行，以后每次一天后执行)
	}

	@Override
	public void contextDestroyed(ServletContextEvent arg0) {
//		showDayTime();
	}

	@Override
	public void contextInitialized(ServletContextEvent arg0) {
		showDayTime();
	}

}

LogTableCreate 用来做表分表是否已经创建，如现在是9月在启动时检查是否存在当月表记录，不存在则创建存在则不创建，另外检查是否存在10月份表记录，不存在则创建(提前创建一个也空表，以此类推)。
拷贝代码修改createsql(建表sql)，URL (数据库地址)，USER (数据库连接用户)，PASSWORD(数据库连接密码)

public  class LogTableCreate extends TimerTask  {
	
	private static final Log log = LogFactory.getLog(LogTableCreate.class);
	public static final String TBASENAME="tb_log";
		private String createsql = " (\r\n" + 
				"  `ID` varchar(64) NOT NULL COMMENT '主键id',\r\n" + 
				"  `userid` varchar(255) DEFAULT NULL COMMENT '用户id',\r\n" + 
				"  `username` varchar(255) DEFAULT NULL COMMENT '用户姓名',\r\n" + 
				"  `useridcard` varchar(255) DEFAULT NULL COMMENT '用户身份证号码',\r\n" + 
				"  `realname` varchar(64) DEFAULT NULL COMMENT '真实姓名',\r\n" + 
				"  `logintime` varchar(255) DEFAULT NULL COMMENT '登录时间',\r\n" + 
				"  `exittime` varchar(64) DEFAULT NULL COMMENT '退出时间',\r\n" + 
				"  `ippath` varchar(255) DEFAULT NULL COMMENT 'ip地址',\r\n" + 
				"  `macpath` varchar(255) DEFAULT NULL COMMENT 'mac地址',\r\n" + 
				"  `usercreatedtime` varchar(255) DEFAULT NULL COMMENT '用户创建时间',\r\n" + 
				"  `userbusidaddress` varchar(255) DEFAULT NULL COMMENT '用户钱包地址',\r\n" + 
				"  `member` int(11) DEFAULT NULL COMMENT '是否是会员',\r\n" + 
				"  `membertype` int(11) DEFAULT NULL COMMENT '会员类型',\r\n" + 
				"  `spare1` varchar(255) DEFAULT NULL,\r\n" + 
				"  `spare2` varchar(255) DEFAULT NULL,\r\n" + 
				"  `spare3` varchar(255) DEFAULT NULL,\r\n" + 
				"  PRIMARY KEY (`ID`)\r\n" + 
				")";	

	private SimpleDateFormat sdyyyy = new SimpleDateFormat("yyyy");
	private SimpleDateFormat sdmm = new SimpleDateFormat("MM");

    private static final String URL = "";
    private static final String USER = "";
    private static final String PASSWORD = "";
    //得到表名
    public static String gettable() {
    	Date date = new Date();
    	LogTableCreate logTableCreate=new LogTableCreate();
		String yyyy = logTableCreate.sdyyyy.format(date);
		String mm = logTableCreate.sdmm.format(date);
		String nmm = logTableCreate.getNextMM(mm);
		return TBASENAME+yyyy+mm;
		
    }
    
	//得到下一个月
	private String getNextMM(String mm){
		String nmm = "";
		int imm = Integer.parseInt(mm);
		if(imm>=12){
			nmm = "01";
		}else{
			imm++;
			if(imm>9)
				nmm = ""+imm;
			else
				nmm = "0"+imm;
		}
		return nmm;
	} 

	@Override
	public void run() {
		Date date = new Date();
		String yyyy = sdyyyy.format(date);
		String mm = sdmm.format(date);
		String nmm = getNextMM(mm);
		
		String nyyyy = "";
		if("01".equals(nmm)){
			nyyyy = ""+(Integer.parseInt(yyyy)+1);
		}else{
			nyyyy = yyyy;
		}
		
		log.info("日志表检查及创建："+yyyy+" - "+mm+" | "+nyyyy+"-"+nmm);
		String temp = TBASENAME+yyyy+mm;  //日志表名称
		boolean has =  false;
	
		try{
			has = hasTable(temp);
		}catch(Exception e){
			log.error("当前操作日志表是否存在判断时发生错误:"+e.getMessage());
			return;
		}
		if(!has){
			try{
				createTable(temp);
			}catch(Exception e){
				log.error("当前操作日志表创建时发生错误:"+e.getMessage());
				return;
			}
		}
		temp = TBASENAME+nyyyy+nmm;
		has =  false;
		try{
			has =  hasTable(temp);
		}catch(Exception e){
			log.error("待用日志表是否存在判断时发生错误:"+e.getMessage());
			return;
		}
		if(!has){
			try{
				createTable(temp);
			}catch(Exception e){
				log.error("待用日志表创建时发生错误:"+e.getMessage());
				return;
			}
		}
		
		log.info("日志表检查及创建结束");
	}
	
	public boolean hasTable(String table) throws Exception{

		Class.forName("com.mysql.jdbc.Driver");
        //2. 获得数据库连接
    Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
		boolean state = false;
		DatabaseMetaData meta = conn.getMetaData();
		ResultSet set;
		set = meta.getTables(null, null, table.toLowerCase(), null);
		while (set.next()) {
			state = true;
			break;
		}
		Statement stmt = null;
		try{
			stmt = conn.createStatement();
		}catch(Exception e){
			log.error("检查日志表是否存在时发生错误:"+e.getMessage());
			throw e;
		}finally{
			if(stmt!=null)
				try {
					stmt.close();
				} catch (Exception e) {
					//e.printStackTrace();
				}
		}
		conn.close();
		return state;
	}
	public void createTable(String table)throws Exception{
		try{
		Class.forName("com.mysql.jdbc.Driver");
	        //2. 获得数据库连接
	    Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
		String sql = "create table "+table+createsql;
		Statement stmt = null;
		stmt = conn.createStatement();
		stmt.execute(sql);
		}catch(Exception e){
			log.error("初始化日志表时发生错误:"+e.getMessage());
			throw e;
		}
	}
}