先贴代码,作为草稿:
第一个是工具类, MapUtil.java
import java.util.ArrayList;import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* map转换工具
*/
public class MapUtil {
/**
* 将List中的Key转换为小写
* @param list 返回新对象
* @return
*/
public static List<Map<String, Object>> convertKeyList2LowerCase(List<Map<String, Object>> list){
if(null==list) {
return null;
}
List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
//
Iterator<Map<String, Object>> iteratorL = list.iterator();
while (iteratorL.hasNext()) {
Map<String, Object> map = (Map<String, Object>) iteratorL.next();
//
Map<String, Object> result = convertKey2LowerCase(map);
if(null != result){
resultList.add(result);
}
}
//
return resultList;
}
/**
* 转换单个map,将key转换为小写.
* @param map 返回新对象
* @return
*/
public static Map<String, Object> convertKey2LowerCase(Map<String, Object> map){
if(null==map) {
return null;
}
Map<String, Object> result = new HashMap<String, Object>();
//
Set<String> keys = map.keySet();
//
Iterator<String> iteratorK = keys.iterator();
while (iteratorK.hasNext()) {
String key = (String) iteratorK.next();
Object value = map.get(key);
if(null == key){
continue;
}
//
String keyL = key.toLowerCase();
result.put(keyL, value);
}
return result;
}
/**
* 将List中Map的Key转换为小写.
* @param list
* @return
*/
public static List<Map<String, Object>> trimListKeyValue(List<Map<String, Object>> list){
if(null==list) {
return null;
}
List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
//
Iterator<Map<String, Object>> iteratorL = list.iterator();
while (iteratorL.hasNext()) {
Map<String, Object> map = (Map<String, Object>) iteratorL.next();
//
Map<String, Object> result = trimKeyValue(map);
if(null != result){
resultList.add(result);
}
}
//
return resultList;
}
/**
* 转换单个map,将key转换为小写.
* @param map 返回新对象
* @return
*/
public static Map<String, Object> trimKeyValue(Map<String, Object> map){
if(null==map) {
return null;
}
Map<String, Object> result = new HashMap<String, Object>();
//
Set<String> keys = map.keySet();
//
Iterator<String> iteratorK = keys.iterator();
while (iteratorK.hasNext()) {
String key = (String) iteratorK.next();
Object value = map.get(key);
if(null == key){
continue;
}
//
String keyT = key.trim();
if(value instanceof String){
String valueT = String.valueOf(value).trim();
result.put(keyT, valueT);
} else {
result.put(keyT, value);
}
}
return result;
}
}
然后是具体的实现工具类,使用了阿里巴巴的 fastjson 工具包；里面的测试类可以忽略
import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Properties;import com.alibaba.fastjson.JSONArray;/** * 需要注意,想要有表字段描述信息，获取连接时需要指定某些特殊属性<br/> * 数据交换-工具类 */public class DBMSMetaUtil {/** * 数据库类型,枚举 * */public static enum DATABASETYPE {ORACLE, MYSQL, SQLSERVER, SQLSERVER2005, DB2, INFORMIX, SYBASE, OTHER, EMPTY}/** * 根据字符串,判断数据库类型 * * @param databasetype * @return */public static DATABASETYPE parseDATABASETYPE(String databasetype) {// 空类型if (null == databasetype || databasetype.trim().length() < 1) {return DATABASETYPE.EMPTY;}// 截断首尾空格,转换为大写databasetype = databasetype.trim().toUpperCase();// Oracle数据库if (databasetype.contains("ORACLE")) {//return DATABASETYPE.ORACLE;}// MYSQL 数据库if (databasetype.contains("MYSQL")) {//return DATABASETYPE.MYSQL;}// SQL SERVER 数据库if (databasetype.contains("SQL") && databasetype.contains("SERVER")) {//if (databasetype.contains("2005") || databasetype.contains("2008") || databasetype.contains("2012")) { try {Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");} catch (ClassNotFoundException e) {e.printStackTrace();}return DATABASETYPE.SQLSERVER2005;} else {try {// 注册 JTDSClass.forName("net.sourceforge.jtds.jdbc.Driver");} catch (ClassNotFoundException e) {e.printStackTrace();}return DATABASETYPE.SQLSERVER;}}// 下面的这几个没有经过实践测试, 判断可能不准确// DB2 数据库if (databasetype.contains("DB2")) {//return DATABASETYPE.DB2;}// INFORMIX 数据库if (databasetype.contains("INFORMIX")) {//return DATABASETYPE.INFORMIX;}// SYBASE 数据库if (databasetype.contains("SYBASE")) {//return DATABASETYPE.SYBASE;}// 默认,返回其他return DATABASETYPE.OTHER;}/** * 列出数据库的所有表 */// 可以参考: http://www.cnblogs.com/chinafine/articles/1847205.htmlpublic static List<Map<String, Object>> listTables(String databasetype, String ip, String port, String dbname,String username, String password) {// 去除首尾空格databasetype = trim(databasetype);ip = trim(ip);port = trim(port);dbname = trim(dbname);username = trim(username);password = trim(password);//DATABASETYPE dbtype = parseDATABASETYPE(databasetype);//List<Map<String, Object>> result = null;String url = concatDBURL(dbtype, ip, port, dbname);Connection conn = getConnection(url, username, password);// Statement stmt = null;ResultSet rs = null;//try {// 这句话我也不懂是什么意思... 好像没什么用conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);// 获取Meta信息对象DatabaseMetaData meta = conn.getMetaData();// 数据库String catalog = null;// 数据库的用户String schemaPattern = null;// meta.getUserName();// 表名String tableNamePattern = null;//// types指的是table、viewString[] types = { "TABLE" };// Oracleif (DATABASETYPE.ORACLE.equals(dbtype)) {schemaPattern = username;if (null != schemaPattern) {schemaPattern = schemaPattern.toUpperCase();}// 查询rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);} else if (DATABASETYPE.MYSQL.equals(dbtype)) {// Mysql查询// MySQL 的 table 这一级别查询不到备注信息schemaPattern = dbname;rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);} else if (DATABASETYPE.SQLSERVER.equals(dbtype) || DATABASETYPE.SQLSERVER2005.equals(dbtype)) {// SqlServertableNamePattern = "%";rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);} else if (DATABASETYPE.DB2.equals(dbtype)) {// DB2查询schemaPattern = "jence_user";tableNamePattern = "%";rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);} else if (DATABASETYPE.INFORMIX.equals(dbtype)) {// SqlServertableNamePattern = "%";rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);} else if (DATABASETYPE.SYBASE.equals(dbtype)) {// SqlServertableNamePattern = "%";rs = meta.getTables(catalog, schemaPattern, tableNamePattern, types);} else {throw new RuntimeException("不认识的数据库类型!");}//result = parseResultSetToMapList(rs);} catch (Exception e) {e.printStackTrace();} finally {close(rs);close(conn);}//return result;}/** * 列出表的所有字段 */public static List<Map<String, Object>> listColumns(String databasetype, String ip, String port, String dbname,String username, String password, String tableName) {// 去除首尾空格databasetype = trim(databasetype);ip = trim(ip);port = trim(port);dbname = trim(dbname);username = trim(username);password = trim(password);tableName = trim(tableName);//DATABASETYPE dbtype = parseDATABASETYPE(databasetype);//List<Map<String, Object>> result = null;String url = concatDBURL(dbtype, ip, port, dbname);Connection conn = getConnection(url, username, password);// Statement stmt = null;ResultSet rs = null;//try {// 获取Meta信息对象DatabaseMetaData meta = conn.getMetaData();// 数据库String catalog = null;// 数据库的用户String schemaPattern = null;// meta.getUserName();// 表名String tableNamePattern = tableName;//// 转换为大写if (null != tableNamePattern) {tableNamePattern = tableNamePattern.toUpperCase();}// String columnNamePattern = null;// Oracleif (DATABASETYPE.ORACLE.equals(dbtype)) {// 查询schemaPattern = username;if (null != schemaPattern) {schemaPattern = schemaPattern.toUpperCase();}} else {//}rs = meta.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);// TODO 获取主键列,但还没使用meta.getPrimaryKeys(catalog, schemaPattern, tableNamePattern);//result = parseResultSetToMapList(rs);} catch (Exception e) {e.printStackTrace();} finally {// 关闭资源close(rs);close(conn);}//return result;}/** * 根据IP,端口,以及数据库名字,拼接Oracle连接字符串 * * @param ip * @param port * @param dbname * @return */public static String concatDBURL(DATABASETYPE dbtype, String ip, String port, String dbname) {//String url = "";// Oracle数据库if (DATABASETYPE.ORACLE.equals(dbtype)) {//url += "jdbc:oracle:thin:@";url += ip.trim();url += ":" + port.trim();url += ":" + dbname;// 如果需要采用 hotbackupString url2 = "";url2 = url2+"jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = "+ ip.trim() +")(PORT ="+ port.trim() +")))(CONNECT_DATA = (SERVICE_NAME ="+dbname+")(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))";//// url = url2;} else if (DATABASETYPE.MYSQL.equals(dbtype)) {//url += "jdbc:mysql://";url += ip.trim();url += ":" + port.trim();url += "/" + dbname;} else if (DATABASETYPE.SQLSERVER.equals(dbtype)) {//url += "jdbc:jtds:sqlserver://";url += ip.trim();url += ":" + port.trim();url += "/" + dbname;url += ";tds=8.0;lastupdatecount=true";} else if (DATABASETYPE.SQLSERVER2005.equals(dbtype)) {//url += "jdbc:sqlserver://";url += ip.trim();url += ":" + port.trim();url += "; DatabaseName=" + dbname;} else if (DATABASETYPE.DB2.equals(dbtype)) {url += "jdbc:db2://";url += ip.trim();url += ":" + port.trim();url += "/" + dbname;} else if (DATABASETYPE.INFORMIX.equals(dbtype)) {// Infox mix 可能有BUGurl += "jdbc:informix-sqli://";url += ip.trim();url += ":" + port.trim();url += "/" + dbname;// +":INFORMIXSERVER=myserver;user="+bean.getDatabaseuser()+";password="+bean.getDatabasepassword()} else if (DATABASETYPE.SYBASE.equals(dbtype)) {url += "jdbc:sybase:Tds:";url += ip.trim();url += ":" + port.trim();url += "/" + dbname;} else {throw new RuntimeException("不认识的数据库类型!");}//return url;}/** * 获取JDBC连接 * * @param url * @param username * @param password * @return */public static Connection getConnection(String url, String username, String password) {Connection conn = null;try {// 不需要加载Driver. Servlet 2.4规范开始容器会自动载入// conn = DriverManager.getConnection(url, username, password);//Properties info =new Properties();//info.put("user", username);info.put("password", password);// !!! Oracle 如果想要获取元数据 REMARKS 信息,需要加此参数info.put("remarksReporting","true");// !!! MySQL 标志位, 获取TABLE元数据 REMARKS 信息info.put("useInformationSchema","true");// 不知道SQLServer需不需要设置...//conn = DriverManager.getConnection(url, info);} catch (SQLException e) {e.printStackTrace();}return conn;}/** * 将一个未处理的ResultSet解析为Map列表. * * @param rs * @return */public static List<Map<String, Object>> parseResultSetToMapList(ResultSet rs) {//List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();//if (null == rs) {return null;}//try {while (rs.next()) {// Map<String, Object> map = parseResultSetToMap(rs);//if (null != map) {result.add(map);}}} catch (SQLException e) {e.printStackTrace();}//return result;}/** * 解析ResultSet的单条记录,不进行 ResultSet 的next移动处理 * * @param rs * @return */private static Map<String, Object> parseResultSetToMap(ResultSet rs) {//if (null == rs) {return null;}//Map<String, Object> map = new HashMap<String, Object>();//try {ResultSetMetaData meta = rs.getMetaData();//int colNum = meta.getColumnCount();//for (int i = 1; i <= colNum; i++) {// 列名String name = meta.getColumnLabel(i); // i+1Object value = rs.getObject(i);// 加入属性map.put(name, value);}} catch (SQLException e) {e.printStackTrace();}//return map;}//public static boolean TryLink(String databasetype, String ip, String port, String dbname, String username, String password) {//DATABASETYPE dbtype = parseDATABASETYPE(databasetype);String url = concatDBURL(dbtype, ip, port, dbname);Connection conn = null;//try {conn = getConnection(url, username, password);if(null == conn){return false;}DatabaseMetaData meta = conn.getMetaData();//if(null == meta){return false;} else {// 只有这里返回truereturn true;}} catch (Exception e) {e.printStackTrace();} finally{close(conn);}//return false;}//public static void close(Connection conn) {if(conn!=null) {try {conn.close();conn = null;} catch (SQLException e) {e.printStackTrace();}}}//public static void close(Statement stmt) {if(stmt!=null) {try {stmt.close();stmt = null;} catch (SQLException e) {e.printStackTrace();}}}//public static void close(ResultSet rs) {if(rs!=null) {try {rs.close();rs = null;} catch (SQLException e) {e.printStackTrace();}}}//public static String trim(String str){if(null != str){str = str.trim();}return str;}public static void main(String[] args) {//testLinkOracle();//testLinkSQLServer();testOracle();//testMySQL();}//public static void testLinkOracle() {//String ip= "192.168.0.100";String port= "1521"; String dbname= "orcl"; String username= "username"; String password= "*****pwd";String databasetype= "oracle";//String url = concatDBURL(parseDATABASETYPE(databasetype), ip, port, dbname);System.out.println("url="+url);//boolean result = TryLink(databasetype, ip, port, dbname, username, password);//System.out.println("result="+result);}//public static void testLinkSQLServer() {//String ip= "192.168.0.100";String port= "1433"; String dbname= "sqlserverdb1"; String username= "sa"; String password= "186957";String databasetype= "SQL Server";//String url = concatDBURL(parseDATABASETYPE(databasetype), ip, port, dbname);System.out.println("url="+url);//boolean result = TryLink(databasetype, ip, port, dbname, username, password);//System.out.println("result="+result);}public static void testOracle() {//String ip = "192.168.0.100";String port = "1521";String dbname = "orcl";String username = "unixsys";String password = "orpass";//String databasetype = "Oracle";// DATABASETYPE dbtype = parseDATABASETYPE(databasetype);// System.out.println(DATABASETYPE.ORACLE.equals(dbtype));//String tableName = "DBMS_CODE_CHEME_NEW";List<Map<String, Object>> tables = listTables(databasetype, ip, port, dbname, username, password);List<Map<String, Object>> columns = listColumns(databasetype, ip, port, dbname, username, password, tableName);//tables = MapUtil.convertKeyList2LowerCase(tables);columns = MapUtil.convertKeyList2LowerCase(columns);//String jsonT = JSONArray.toJSONString(tables, true);System.out.println(jsonT);System.out.println("tables.size()=" + tables.size());//System.out.println("-----------------------------------------" + "-----------------------------------------");System.out.println("-----------------------------------------" + "-----------------------------------------");//String jsonC = JSONArray.toJSONString(columns, true);System.out.println(jsonC);System.out.println("columns.size()=" + columns.size());}public static void testMySQL() {//String ip = "127.0.0.1";String port = "4050";String dbname = "cncounter";dbname = "eReqDlG";String username = "root";username = "6EhSiGpsmSMRr";String password = "eoNRNBgRk397mVy";//String databasetype = "mysql";// DATABASETYPE dbtype = parseDATABASETYPE(databasetype);// System.out.println(DATABASETYPE.ORACLE.equals(dbtype));//String tableName = "vote";List<Map<String, Object>> tables = listTables(databasetype, ip, port, dbname, username, password);List<Map<String, Object>> columns = listColumns(databasetype, ip, port, dbname, username, password, tableName);//tables = MapUtil.convertKeyList2LowerCase(tables);columns = MapUtil.convertKeyList2LowerCase(columns);//String jsonT = JSONArray.toJSONString(tables, true);System.out.println(jsonT);System.out.println("tables.size()=" + tables.size());//System.out.println("-----------------------------------------" + "-----------------------------------------");System.out.println("-----------------------------------------" + "-----------------------------------------");//String jsonC = JSONArray.toJSONString(columns, true);System.out.println(jsonC);System.out.println("columns.size()=" + columns.size());}// 演示 DatabaseMetaDatapublic static void demoDatabaseMetaData() {try {Class.forName("com.mysql.jdbc.Driver");Connection con = DriverManager.getConnection("jdbc:mysql://localhost/dbtest", "root", "root");//DatabaseMetaData dmd = con.getMetaData();System.out.println("当前数据库是：" + dmd.getDatabaseProductName());System.out.println("当前数据库版本：" + dmd.getDatabaseProductVersion());System.out.println("当前数据库驱动：" + dmd.getDriverVersion());System.out.println("当前数据库URL：" + dmd.getURL());System.out.println("当前数据库是否是只读模式？：" + dmd.isReadOnly());System.out.println("当前数据库是否支持批量更新？：" + dmd.supportsBatchUpdates());System.out.println("当前数据库是否支持结果集的双向移动（数据库数据变动不在ResultSet体现）？："+ dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE));System.out.println("当前数据库是否支持结果集的双向移动（数据库数据变动会影响到ResultSet的内容）？："+ dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE));System.out.println("========================================");ResultSet rs = dmd.getTables(null, null, "%", null);System.out.println("表名" + "," + "表类型");while (rs.next()) {System.out.println(rs.getString("TABLE_NAME") + "," + rs.getString("TABLE_TYPE"));}System.out.println("========================================");rs = dmd.getPrimaryKeys(null, null, "t_student");while (rs.next()) {System.out.println(rs.getString(3) + "表的主键是：" + rs.getString(4));}System.out.println("========================================");rs = dmd.getColumns(null, null, "t_student", "%");System.out.println("t_student表包含的字段:");while (rs.next()) {System.out.println(rs.getString(4) + " " + rs.getString(6) + "(" + rs.getString(7) + ");");}System.out.println("========================================");} catch (Exception e) {System.out.println("数据库操作出现异常");}}// ResultSetMetaData 使用示例// 此方法参考 http://blog.csdn.net/yirentianran/article/details/2950321public static void demoResultSetMetaData(ResultSetMetaData data) throws SQLException {for (int i = 1; i <= data.getColumnCount(); i++) {// 获得所有列的数目及实际列数int columnCount = data.getColumnCount();// 获得指定列的列名String columnName = data.getColumnName(i);// 获得指定列的列值// String columnValue = rs.getString(i);// 获得指定列的数据类型int columnType = data.getColumnType(i);// 获得指定列的数据类型名String columnTypeName = data.getColumnTypeName(i);// 所在的Catalog名字String catalogName = data.getCatalogName(i);// 对应数据类型的类String columnClassName = data.getColumnClassName(i);// 在数据库中类型的最大字符个数int columnDisplaySize = data.getColumnDisplaySize(i);// 默认的列的标题String columnLabel = data.getColumnLabel(i);// 获得列的模式String schemaName = data.getSchemaName(i);// 某列类型的精确度(类型的长度)int precision = data.getPrecision(i);// 小数点后的位数int scale = data.getScale(i);// 获取某列对应的表名String tableName = data.getTableName(i);// 是否自动递增boolean isAutoInctement = data.isAutoIncrement(i);// 在数据库中是否为货币型boolean isCurrency = data.isCurrency(i);// 是否为空int isNullable = data.isNullable(i);// 是否为只读boolean isReadOnly = data.isReadOnly(i);// 能否出现在where中boolean isSearchable = data.isSearchable(i);System.out.println(columnCount);System.out.println("获得列" + i + "的字段名称:" + columnName);// System.out.println("获得列" + i + "的字段值:" + columnValue);System.out.println("获得列" + i + "的类型,返回SqlType中的编号:" + columnType);System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);System.out.println("获得列" + i + "所在的Catalog名字:" + catalogName);System.out.println("获得列" + i + "对应数据类型的类:" + columnClassName);System.out.println("获得列" + i + "在数据库中类型的最大字符个数:" + columnDisplaySize);System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);System.out.println("获得列" + i + "的模式:" + schemaName);System.out.println("获得列" + i + "类型的精确度(类型的长度):" + precision);System.out.println("获得列" + i + "小数点后的位数:" + scale);System.out.println("获得列" + i + "对应的表名:" + tableName);System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);System.out.println("获得列" + i + "是否为空:" + isNullable);System.out.println("获得列" + i + "是否为只读:" + isReadOnly);System.out.println("获得列" + i + "能否出现在where中:" + isSearchable);}}}