mysql型別批量替換工具

朋友有個把postgresql中所有numric替換成int8型別,如果記錄存在小數位則不替換的需求.幫忙寫了個小工具,mysql中測試可用.

 package com.oywy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;
/**
 * 資料庫型別批量替換,新型別替換老型別,如果有存在小數則不替換
 * 只對mysql做過測試
 * @version 1.0
 * @author oywy
 *
 */
public class TypeChanger {
/**
 * 
 * @param DriverName    驅動名
 * @param url           jdbc:mysql://xxxxxxx?xxxx
 * @param user          使用者名稱
 * @param pwd           密碼
 * @param database      資料庫例項名
 * @param oldType       老型別
 * @param newType       新型別
 * @throws Exception
 */
    public static void updateType(String DriverName, String url, String user,
            String pwd, String database, String oldType, String newType)
            throws Exception {
        Class.forName(DriverName);
        Connection con = DriverManager.getConnection(url, user, pwd);
        // 獲取表名,列名,資料型別
        Statement st1 = con.createStatement();
        Set<String[]> set = new HashSet<>();
        ResultSet rs1 = st1
                .executeQuery("select table_name ,column_name ,data_type from information_schema.columns where table_schema = '"
                        + database + "' and data_type = '" + oldType + "'");
//oracle查詢此表user_tab_columns 
        while (rs1.next()) {
            String table_name = rs1.getString("table_name");
            String column_name = rs1.getString("column_name");
            String[] arr = { table_name, column_name };
            set.add(arr);
        }
        for (String[] arr : set) {
            String sql = "select " + arr[1] + " from " + database + "."
                    + arr[0] + " where " + arr[1] + " = floor(" + arr[1] + ")";
            ResultSet rs2 = st1.executeQuery(sql);
            if (!rs2.next()) {
                System.out.println("alter table " + arr[0] + " change "
                        + arr[1] + " " + arr[1] + " " + newType + ";");
            }
        }
    }
}