altertablesutil.java#1.1.1.1

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

package com.qixuan.jdbc.util;

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

public class ALTERTablesUtil {
	private static Connection connection;
	public Connection getConnection() {
		return connection;
	}
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	public static void AddColumnByRecordFlag(Connection connection){
		Statement stm=null;
		DatabaseMetaDataUtil dbmu = new DatabaseMetaDataUtil(connection);
		ResultSetMetaDataUtil rmdu = new ResultSetMetaDataUtil(connection);
		List<String> tableNames = dbmu.getTableNames();
		String dbms = dbmu.getDataBaseName();
		System.out.println("dbms:"+dbms);
		try {
			for (String tableName : tableNames) {
				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();
				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);
		ResultSetMetaDataUtil rmdu = new ResultSetMetaDataUtil(connection);
		List<String> tableNames = dbmu.getTableNames();
		try {
			for (String tableName : tableNames) {
				rmdu.setTableName(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);
		ResultSetMetaDataUtil rmdu = new ResultSetMetaDataUtil(connection);
		System.out.println("更新开始：");
		List<String> tableNames = dbmu.getTableNames();
		for (String tableName : tableNames) {
			String sql="update  "+tableName+" set record_flag = "+value+" ;";
			try {
				stm=connection.createStatement();
				int rs=stm.executeUpdate(sql);
				System.out.println(tableName+":"+rs);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
}