朋友有个把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 + ";");
            }
        }
    }
}