JDBC获取数据表字段名、注释等信息
需求：给定数据库信息和表名，扫描表的字段名、字段类型和注释。
注：数据库可以是Oracle、Mysql、DB2、SqlServer等。
解决方法：利用JDBC的DatabaseMetaData来获取数据库的元信息。
用法如下：
package util;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
/**
*
*
Description: 获取数据库基本信息的工具类
*
* @author qxl
* @date 2016年7月22日 下午1:00:34
*/
public class DbInfoUtil {
/**
* 根据数据库的连接参数，获取指定表的基本信息：字段名、字段类型、字段注释
* @param driver 数据库连接驱动
* @param url 数据库连接url
* @param user数据库登陆用户名
* @param pwd 数据库登陆密码
* @param table表名
* @return Map集合
*/
public static List getTableInfo(String driver,String url,String user,String pwd,String table){
List result = new ArrayList();
Connection conn = null;
DatabaseMetaData dbmd = null;
try {
conn = getConnections(driver,url,user,pwd);
dbmd = conn.getMetaData();
ResultSet resultSet = dbmd.getTables(null, "%", table, new String[] { "TABLE" });
while (resultSet.next()) {
String tableName=resultSet.getString("TABLE_NAME");
System.out.println(tableName);
if(tableName.equals(table)){
ResultSet rs = conn.getMetaData().getColumns(null, getSchema(conn),tableName.toUpperCase(), "%");
while(rs.next()){
//System.out.println("字段名："+rs.getString("COLUMN_NAME")+"--字段注释："+rs.getString("REMARKS")+"--字段数据类型："+rs.getString("TYPE_NAME"));
Map map = new HashMap();
String colName = rs.getString("COLUMN_NAME");
map.put("code", colName);
String remarks = rs.getString("REMARKS");
if(remarks == null || remarks.equals("")){
remarks = colName;
}
map.put("name",remarks);
String dbType = rs.getString("TYPE_NAME");
map.put("dbType",dbType);
map.put("valueType", changeDbType(dbType));
result.add(map);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return result;
}
private static String changeDbType(String dbType) {
dbType = dbType.toUpperCase();
switch(dbType){
case "VARCHAR":
case "VARCHAR2":
case "CHAR":
return "1";
case "NUMBER":
case "DECIMAL":
return "4";
case "INT":
case "SMALLINT":
case "INTEGER":
return "2";
case "BIGINT":
return "6";
case "DATETIME":
case "TIMESTAMP":
case "DATE":
return "7";
default:
return "1";
}
}
//获取连接
private static Connection getConnections(String driver,String url,String user,String pwd) throws Exception {
Connection conn = null;
try {
Properties props = new Properties();
props.put("remarksReporting", "true");
props.put("user", user);
props.put("password", pwd);
Class.forName(driver);
conn = DriverManager.getConnection(url, props);
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return conn;
}
//其他数据库不需要这个方法 oracle和db2需要
private static String getSchema(Connection conn) throws Exception {
String schema;
schema = conn.getMetaData().getUserName();
if ((schema == null) || (schema.length() == 0)) {
throw new Exception("ORACLE数据库模式不允许为空");
}
return schema.toUpperCase().toString();
}
public static void main(String[] args) {
//这里是Oracle连接方法
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.12.44:1521:orcl";
String user = "bdc";
String pwd = "bdc123";
//String table = "FZ_USER_T";
String table = "FZ_USER_T";
//mysql
/*
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pwd = "123456";
String url = "jdbc:mysql://localhost/onlinexam"
+ "?useUnicode=true&characterEncoding=UTF-8";
String table = "oe_student";
*/
List list = getTableInfo(driver,url,user,pwd,table);
System.out.println(list);
}
}注：需要导入数据库连接驱动
此工具类无需修改，适用于Oracle、Mysql、DB2、SqlServer数据库。