Access数据库列名大小写转换（2012.11.29）

     这几天做项目的时候，碰到了一个很蛋疼的问题，我们的客户老外们，不会用mysql和oracle，就喜欢在office文档里面操作操作，看来他们认为这个是非常爽的，任何的bug啊，数据库啊什么的都通过office的办公文件给我们，每次过来的数据库都需要我们转换到自己的oracle里面，更蛋疼的是，他们access数据库里面的table列的大小写还个各不一致，奶奶的胸，看的十分的烦躁，就只能自己写个东西转换一下了，本里研究了一下用jackcess去处理这个东东，但是那个开源实在很难研究透，有些东西还不支持，我也写了邮件和他们的developor做了一些交流。

     直接上代码吧，菜鸟写的代码（大牛飘过，别笑 ，哈哈）。

  

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.lonnesol.dbmanager.uppAccessColumns;

import com.lonnesol.database.tools.SqlUtil;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 *
 * @author hualun-sunny.jiang
 */
public class UppAccessColumns {

    public static void main(String[] args) {
        UppAccessColumns test = new UppAccessColumns();
        test.upperAccessColumns("D://daks122_20120928_carl_1015.mdb");
    }

    private void upperAccessColumns(String path) {

        Connection con = SqlUtil.getInstance("access", path, "", "").getConnection();
        try {
            Statement smt = con.createStatement();
            DatabaseMetaData dma = con.getMetaData();
            //将数据库中的表的名称转储出来
            String[] types = new String[1];
            types[0] = "TABLE"; //设置查询类型
            //%匹配所有
            ResultSet rs = dma.getTables(null, null, "%", types);
            String regEx = "[a-z]";
            Pattern pat = Pattern.compile(regEx);
            Matcher mat = null;
            Set<String> tableNameList = new HashSet<String>();
            StringBuffer names = new StringBuffer();
            while (rs.next()) {
                String tableName = (String) rs.getObject("TABLE_NAME");
                LinkedHashMap<String, String> tableColumns = getTableColumns(con, tableName);
                Iterator<Map.Entry<String, String>> it = tableColumns.entrySet().iterator();
                while (it.hasNext()) {
                    Map.Entry<String, String> next = it.next();
                    String columnName = next.getKey();
                    mat = pat.matcher(columnName);
                    boolean find = mat.find();
                    if (find) {
                        //保存有小写列的表名
                        tableNameList.add(tableName);
                        if (!names.toString().contains(tableName)) {
                            names.append(tableName).append(" , ");
                        }
                    }
                }
            }
            Iterator<String> iterator = tableNameList.iterator();
            System.out.println("You access database have " + tableNameList.size() + " tables contain lower case in the column Name.");
            while (iterator.hasNext()) {
                String tableName = iterator.next();
                boolean bool = createTable(path, tableName);
                if (bool) {
                    System.out.println("Transform table " + tableName + " success !");
                } else {
                    System.out.println("Transform table " + tableName + " fail !");
                }
            }
            smt.close();
            con.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private boolean createTable(String path, String name) {
        boolean bo = false;
        Connection dstCon = SqlUtil.getInstance("access", path, "", "").getConnection();
        String sqlD = "drop table  if exists " + name;
        SqlUtil.executeUpdate(dstCon, sqlD);
        String tableName = ("`" + name + "`");
        String sql = "SELECT * FROM " + tableName;
        Statement stmt = null;
        try {
            String newTableName = "`" + name + "Temp`";
            String sqlTempCreate = "CREATE TABLE " + newTableName + "(";
            String sqlFinalCreate = "CREATE TABLE " + tableName + "(";
            String create = "";
            String columns = "";
            stmt = dstCon.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            rs = stmt.getResultSet();
            ResultSetMetaData rsmd = rs.getMetaData();
            int numcols = rsmd.getColumnCount();
            String colnames[] = new String[400];
            int coltypes[] = new int[400];
            int colsizes[] = new int[400];

            for (int i = 0; i < numcols; i++) {
                String colname = rsmd.getColumnName(i + 1);
                colname = colname.toUpperCase();
                int coltype = rsmd.getColumnType(i + 1);
                int colsize = rsmd.getColumnDisplaySize(i + 1);

                colnames[i] = colname;
                coltypes[i] = coltype;
                colsizes[i] = colsize;
                if (colname.equalsIgnoreCase("Key")) {
                    colname = "`KEY`";
                }
                if (i > 0) {
                    create += ", ";
                    columns += ",";
                }
                columns += colname;
                if (coltype == 4) {
                    create += colname + " int";
                } // 7 single and 8 double are both converted to real
                else if (coltype == 7 || coltype == 8) {
                    create += colname + " real";
                } else if (coltype == 12 && colsize < 255) {
                    create += colname + " varchar(" + colsize + ")";
                } else if (coltype == -1 || colsize > 255) {
                    create += colname + " text";
                } else {
                    create += colname + " varchar(255)";
                }
            }
            create += ");";

            //创建临时的新表
            sqlTempCreate += create;
            SqlUtil.executeUpdate(dstCon, sqlTempCreate);
            dstCon.commit();
            dstCon.close();

            Connection con = SqlUtil.getInstance("access", path, "", "").getConnection();
            //将需要转换的表数据存到临时的新表中
            String copyTempDatasSql = "INSERT INTO " + newTableName + " SELECT * FROM " + tableName + ";";
            con.prepareStatement(copyTempDatasSql).execute();
            con.commit();
            con.close();

            Connection con1 = SqlUtil.getInstance("access", path, "", "").getConnection();
            //删除原需要转换的小写表
            String dropOldSql = "DROP TABLE " + tableName + ";";
            con1.prepareStatement(dropOldSql).execute();
            con1.commit();
            con1.close();

            Connection con2 = SqlUtil.getInstance("access", path, "", "").getConnection();
            //创建目的大写表
            sqlFinalCreate += create;
            SqlUtil.executeUpdate(con2, sqlFinalCreate);
            con2.commit();
            con2.close();

            Connection con3 = SqlUtil.getInstance("access", path, "", "").getConnection();
            //将临时表数据存到大写表
            String copyFinalDatasSql = "INSERT INTO " + tableName + " SELECT * FROM " + newTableName + ";";
            con3.prepareStatement(copyFinalDatasSql).execute();
            con3.commit();
            con3.close();

            Connection con4 = SqlUtil.getInstance("access", path, "", "").getConnection();
            //删除临时表
            String dropTempSql = "DROP TABLE " + newTableName + ";";
            con4.prepareStatement(dropTempSql).execute();
            con4.commit();
            con4.close();

            bo = true;
            return bo;
        } catch (Exception ex) {
            ex.printStackTrace();
            System.out.println(ex.getMessage());
            return bo;
        }
    }

    private LinkedHashMap<String, String> getTableColumns(Connection connection, String tableName) {
        //用来保存每一列对应的类型
        LinkedHashMap<String, String> tableColumnsLinkedMap = new LinkedHashMap<String, String>();
        String sql = "";
        try {
            Statement statement = connection.createStatement();
            if (tableName.trim().contains(" ")) {
                tableName = "`" + tableName + "`";
            }
            sql = "SELECT top 1 * FROM " + tableName;
            ResultSet rsforTable = statement.executeQuery(sql);
            ResultSetMetaData rsTable = rsforTable.getMetaData();
            //表列数
            int numberOfColumn = rsTable.getColumnCount();
            for (int i = 1; i <= numberOfColumn; i++) {
                String columnName = rsTable.getColumnName(i);
                String columnType = rsTable.getColumnTypeName(i);
                int columnDisplaySize = rsTable.getColumnDisplaySize(i);
                String allType = "";
                if ("DOUBLE".equalsIgnoreCase(columnType) || "SMALLINT".equalsIgnoreCase(columnType) || "INTEGER".equalsIgnoreCase(columnType)) {
                    allType = columnType;
                } else {
                    allType = columnType + "(" + columnDisplaySize + ")";
                }

                tableColumnsLinkedMap.put(columnName, allType);
            }
            if ("M_FLD_DOC".equalsIgnoreCase(tableName) || "M_RVR_DOC".equalsIgnoreCase(tableName)) {
                if (rsforTable.next()) {
                    tableColumnsLinkedMap = null;
                }
            }
            rsforTable.close();
        } catch (SQLException ex) {
            System.out.println("ERROR SQL :" + sql);
            ex.printStackTrace();
        }
        return tableColumnsLinkedMap;
    }
}