这里用到的api是DatabaseMetaData，
接口文档地址：https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html
一、获取数据库里所有的表名
测试代码如下
public List<String> getTableNames() {
List<String> tableNames = new ArrayList<>();
Connection conn = null;
ResultSet rs = null;
try {
//Register JDBC driver
loadDriver();
//Open a connection
LogUtils.info(DBUtils.class,"Connecting to database...");
conn = getConnection();
//获取数据库的元数据
DatabaseMetaData db = conn.getMetaData();
//从元数据中获取到所有的表名
String[] types = {"TABLE"};
//mysql-connector-java 6.0以下用这个方法
//rs = db.getTables(null, null, null, new String[] { "TABLE" });
//mysql-connector-java 6.x用这个方法
rs = db.getTables(conn.getCatalog(), conn.getCatalog(), "%", types);
while(rs.next()) {
tableNames.add(rs.getString(3));
}
} catch (SQLException e) {
LogUtils.info(DBUtils.class, e.toString());
} finally {
try {
closeConnection(rs,null,null,conn);
} catch (Exception e) {
LogUtils.info(DBUtils.class, e.toString());
}
}
return tableNames;
}
二、获取一张表字段信息
这里用到的方法是：ResultSetMetaData. getColumnName
public List<String> getColumnNames(String tableName) {
List<String> columnNames = new ArrayList<>();
Connection conn = null;
PreparedStatement pStemt = null;
String tableSql = "SELECT * FROM " + tableName;
try {
//Register JDBC driver
loadDriver();
//Open a connection
LogUtils.info(DBUtils.class,"Connecting to database...");
conn = getConnection();
pStemt = conn.prepareStatement(tableSql);
//结果集元数据
ResultSetMetaData rsmd = pStemt.getMetaData();
//表列数
int size = rsmd.getColumnCount();
for (int i = 0; i < size; i++) {
columnNames.add(rsmd.getColumnName(i + 1));
}
} catch (SQLException e) {
LogUtils.info(DBUtils.class, "getColumnNames failure "+e.toString());
} finally {
if (pStemt != null) {
try {
closeConnection(null,null,pStemt,conn);
} catch (Exception e) {
LogUtils.info(DBUtils.class, e.toString());
}
}
}
return columnNames;
}
三、获取表中所有字段类型
public List<DBColumnDto> getColumnDetails(String tableName) {
List<DBColumnDto> columnDetailList = new ArrayList<>();
Connection conn = null;
PreparedStatement pStemt = null;
String tableSql = "SELECT * FROM " + tableName;
try {
//Register JDBC driver
loadDriver();
//Open a connection
LogUtils.info(DBUtils.class,"Connecting to database...");
conn = getConnection();
pStemt = conn.prepareStatement(tableSql);
//结果集元数据
ResultSetMetaData rsmd = pStemt.getMetaData();
//表列数
int size = rsmd.getColumnCount();
//decimal(16,2) scale=2,displaysize=18||varchar(64) scale=0,displaysize=64
for (int i = 0; i < size; i++) {
DBColumnDto columnDto = new DBColumnDto();
columnDto.setTableName(tableName);
columnDto.setColumnName(rsmd.getColumnName(i + 1));
columnDto.setColumnType(rsmd.getColumnTypeName(i + 1));
columnDetailList.add(columnDto);
}
} catch (SQLException e) {
LogUtils.info(DBUtils.class, "getColumnDetails failure "+e.toString());
} finally {
if (pStemt != null) {
try {
closeConnection(null,null,pStemt,conn);
} catch (Exception e) {
LogUtils.info(DBUtils.class, e.toString());
}
}
}
return columnDetailList;
}
