这几天做项目的时候,碰到了一个很蛋疼的问题,我们的客户老外们,不会用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;
}
}