altertablesutil.java

来自「数据库远程同步软件NetBeans项目源文件 项目采用Jdesktop集成组件」· Java 代码 · 共 111 行

package com.qixuan.jdbc.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class ALTERTablesUtil {

	public static void AddColumnByRecordFlag(Connection connection){
		Statement stm=null;
		DatabaseMetaDataUtil dbmu = new DatabaseMetaDataUtil(connection);
		
		List<String> tableNames = dbmu.getTableNames();
		String dbms = dbmu.getDataBaseName();
		System.out.println("dbms:"+dbms);
		try {
			for (String tableName : tableNames) {
                 if("dtproperties".equals(tableName)){
				continue;
			}
                ResultSetMetaDataUtil rmdu = new ResultSetMetaDataUtil(connection,tableName);
				//rmdu.setTableName(tableName);
				List<ColumnData> dataList =  rmdu.getColumnNames();
				boolean flag=true;
				for(ColumnData column:dataList){
					//System.out.println("ColumnName:"+column.getColumnName());
					if("record_flag".equals(column.getColumnName())){
						flag=false;
					}			
				}
				if(flag){
				String sql="ALTER TABLE "+tableName+" ADD record_flag smallint not null default 0;";
				stm=connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
				int rs=stm.executeUpdate(sql);
				System.out.println(tableName+":"+rs);
				}else{
					System.out.println(tableName+": Duplicate column name 'record_flag'");
				}		
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void AddColumn(Connection connection,String ColumnName,String other){
		Statement stm=null;
		DatabaseMetaDataUtil dbmu = new DatabaseMetaDataUtil(connection);
		
		List<String> tableNames = dbmu.getTableNames();
		try {
			for (String tableName : tableNames) {
				 ResultSetMetaDataUtil rmdu = new ResultSetMetaDataUtil(connection,tableName);
				List<ColumnData> dataList =  rmdu.getColumnNames();
				boolean flag=true;
				for(ColumnData column:dataList){
					if(column.getColumnName().equalsIgnoreCase(ColumnName)){
						flag=false;
					}
				}
				if(flag){
					String sql="ALTER TABLE "+tableName+" ADD "+ColumnName+" "+other+" ;";
					stm=connection.createStatement();
					int rs=stm.executeUpdate(sql);
					System.out.println(tableNames+":"+rs);
				}else{
					System.out.println(tableNames+": Duplicate column name '"+ColumnName+"'");
				}
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void UpdateColumnRecordFlag(Connection connection,int value){
		Statement stm=null;
		DatabaseMetaDataUtil dbmu = new DatabaseMetaDataUtil(connection);
		
		System.out.println("更新开始：");
		List<String> tableNames = dbmu.getTableNames();
        List<ColumnData> columns;
		for (String tableName : tableNames) {
             if("dtproperties".equals(tableName)){
				continue;
			}
            ResultSetMetaDataUtil rmdu = new ResultSetMetaDataUtil(connection,tableName);
            System.out.println(tableName);
            columns=rmdu.getColumnNames();
            for(ColumnData column:columns ){
                if(!column.getColumnName().equals("record_flag")){
                    continue;
                }
            }
			String sql="update  "+tableName+" set record_flag = "+value+" ;";
           //System.out.println(sql);
			try {
				stm=connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
				int rs=stm.executeUpdate(sql);
				System.out.println(tableName+":"+rs);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
}