patitionmanager.java

用java程序的oracle数据库的一些分区操作

package com.lonton.par;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.dom4j.Element;

public class PatitionManager {

	Connection con = null;
	Statement stmt = null;
	public boolean createPartition(String tableName,
			String subareaName,String upLimitTime){
		//tableName 表示表名称，subareaName表示分区名，upLimitTime表示范围分区的上限时间
		boolean b = false;
		try{
			String strSQL = "alter table "+tableName;
			strSQL += " ADD PARTITION " + subareaName;
			strSQL += " VALUES LESS THAN  (TO_DATE('" + upLimitTime;
			strSQL +=  "','YYYY-MM-DD')) TABLESPACE USERS";
			//创建表分区的SQL语句
			
			System.out.println(strSQL);
			if(!setStatement())return false;
			b = stmt.execute(strSQL);
		    b = true;
		}catch(SQLException e){
			b = false;
			e.printStackTrace();
		}
		return b;
	}
//	连接和操作数据库
	private boolean setStatement(){
		ConnectDB conn = new ConnectDB();
		try{
            con = conn.getConnection();
			if(con == null) return false;
			stmt = con.createStatement();
		}catch(SQLException e){
			e.printStackTrace();
		}
		if(stmt != null)return true;
		else return false;
	}
	
	//更新日期分区对照表信息
	public void updateDateAndParMap(String tableName,
		String subareaName,String upLimitTime){
		try{
			String str = "";
			String strSQL = "";
			str = "insert into " + tableName;
			str += " values('" + subareaName+"','";
			String date = getMaxParDate(tableName);
			while(true){
				date = changeDate(date,1);
				if(date.compareTo(upLimitTime)>=0) break;
				strSQL = str + date+"')";
				try{
					if(!setStatement())return;
					stmt.execute(strSQL);
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}	
	}
	private String getMaxParDate(String tableName){
		String max = "";
		String strSQL = "select distinct max(vdate) from "+tableName;
		ResultSet rs = null;
		try{
			if(!setStatement())return max;
			rs = stmt.executeQuery(strSQL);
			if(rs.next()){
				max = rs.getString(1);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try{
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){}
		}
		return max;
	}
//	得到yyyy-MM-dd格式时间的第num天前的时间，以yyyy-MM-dd格式返回
	public  String changeDate(String date,int num){
		String date1 = null;
		int year = 0;
		int month = 0;
		int day = 0;
		try{
			year = Integer.parseInt(date.substring(0,4));
			month = Integer.parseInt(date.substring(5,7));
			day = Integer.parseInt(date.substring(8,10));
			//System.out.print(year);
		}catch(Exception e){
			e.printStackTrace();
		}
		Date myDate = new Date(year-1900,month-1,day);
		long passTime = myDate.getTime();
		passTime = passTime + num*24*60*60*1000;
		try{
			 Date currdata = new Date(passTime);
			 SimpleDateFormat myFormatter = new SimpleDateFormat("yyyy-MM-dd");
			 date1 = myFormatter.format(currdata);
		}catch(Exception e){
			e.printStackTrace();
		}
		return date1;
	}
//	更新xml文件
    public void updateXml(String strXMLPath,String ChildValue){
    	//System.out.println(strXMLPath);
    	
    	//创建xml文件管理对象
    	XMLManage xmlManage = new XMLManage(strXMLPath);
    	Element element = xmlManage.getElement("//data_partion","table_Name",ChildValue);
    	//System.out.println(element);
	    String upLimitDate = xmlManage.getChildElementValue(element,"up_limit_date");
	    String distanceSize = xmlManage.getChildElementValue(element,"time_distance");  
	    try{
	    	int size = 0;
	    	String nowDate = null;
	    	size = Integer.parseInt(distanceSize);
	    	if(upLimitDate != null){
	        	nowDate = changeDate(upLimitDate,size);
	        }
	        element.element("up_limit_date").setText(nowDate);
		    xmlManage.updateXML();
		    
	    }catch(Exception e){
	    	e.printStackTrace();
	    }
    }public String dateFormat(){
		String time = new Date().toLocaleString();
		try{
			// 得到系统的当前时间
		     Date currdata = new Date();
		     
		     //将时间格式化为yyyy-MM-dd HH:mm:ss
			 SimpleDateFormat myFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			 time = myFormatter.format(currdata);
		}catch(Exception e){
			e.printStackTrace();
		}
		
		//去掉时间的空格和点和-
	    time = time.replaceAll(" ","");
	    time = time.replaceAll("-","");
	    time = time.replaceAll(":","");
		return time;
	}
    public void createPartition(String strTableName,String tableName1){
    	String xmlPath = XMLManage.class.getClassLoader().getResource("").getPath();
		String strXMLPath = xmlPath+"\\DataBase_Manage.xml";
		File file = new File(strXMLPath);
		String str = file.getAbsolutePath();
		//System.out.println(str);
        XMLManage xmlManage = new XMLManage(str);
        Element element = xmlManage.getElement("//data_partion","table_Name",strTableName);
		String tableName = xmlManage.getChildElementValue(element,"table_Name");
	    String upLimitDate = xmlManage.getChildElementValue(element,"up_limit_date");
	    String prefixName = xmlManage.getChildElementValue(element,"prefix_name");
	    String date = dateFormat();
	    String parName = prefixName+date;
	    System.out.println("创建表"+strTableName+"的分区开始");
		boolean b = createPartition(tableName,parName,upLimitDate);
		if(b){
			System.out.println("更新日期对照表");
			updateDateAndParMap(tableName1,parName,upLimitDate);
			System.out.println("更新xml文件");
			updateXml(strXMLPath,tableName); 
		}
    }
	public static void main(String[] args){
		PatitionManager pm = new PatitionManager();
		pm.createPartition("ADMIN.DG_DATA_TAB","UNIONDBA.DG_PARTITION_DATE");	
	}
}