问题:
因为项目验收,需要提供数据库相关详细信息的excel表,所以才会有以下方案,
下面方案仅限于Mysql数据库
方案1:
获取数据库数据类型、字段名称、字段长度、java数据类型。 检索此
`ResultSet`对象的列的数量,类型和属性。
方案逻辑:
这种方案是通过sql语句跑出来获取到ResultSet,再根据getMetaData()获取
到ResultSetMetaData信息,来检索`ResultSet`对象的列的数量,类型和属性
弊端:
拿不到注释
代码
//获取mysql表数据信息 java类型 数据库类型 字段名称 字段长度
public Map getMySqlTableData(String tableName) throws SQLException, ClassNotFoundException {
String user = "root";
String password = "root";
String jdbcDriver = "com.mysql.jdbc.Driver";
String jdbcUrl = "jdbc:mysql://localhost:3306/test";
Connection conn = null;
Class.forName(jdbcDriver);
conn = DriverManager.getConnection(jdbcUrl, user, password);
Map map = new HashMap();
PreparedStatement pst = null;
try {
String sql = "select * from " + tableName;
pst = conn.prepareStatement(sql);
ResultSetMetaData rsd = pst.executeQuery().getMetaData();
for (int i = 0; i < rsd.getColumnCount(); i++) {
map.put("字段名称",rsd.getColumnName(i + 1));
map.put("java类型",rsd.getColumnClassName(i + 1));
map.put("数据库类型",rsd.getColumnTypeName(i + 1));
map.put("字段长度",rsd.getColumnDisplaySize(i + 1));
System.out.print("java类型:" + rsd.getColumnClassName(i + 1));
System.out.print(" 数据库类型:" + rsd.getColumnTypeName(i + 1));
System.out.print(" 字段名称:" + rsd.getColumnName(i + 1));
System.out.print(" 字段长度:" + rsd.getColumnDisplaySize(i + 1));
System.out.println();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
pst.close();
pst = null;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return map;
}
方案2:
结合方案1可以拿到表名,字段名,字段长度,注释,数据库字段类型,java字段类型
代码:
//获取mysql表数据信息 java类型 数据库类型 字段名称 字段长度
public Map getMySqlTableData(String tableName) throws SQLException, ClassNotFoundException {
String user = "root";
String password = "root";
String jdbcDriver = "com.mysql.jdbc.Driver";
String jdbcUrl = "jdbc:mysql://localhost:3306/test";
Connection conn = null;
Class.forName(jdbcDriver);
conn = DriverManager.getConnection(jdbcUrl, user, password);
Map map = new HashMap();
PreparedStatement pst = null;
try {
String sql = "select * from " + tableName;
pst = conn.prepareStatement(sql);
ResultSetMetaData rsd = pst.executeQuery().getMetaData();
for (int i = 0; i < rsd.getColumnCount(); i++) {
map.put("字段名称",rsd.getColumnName(i + 1));
map.put("java类型",rsd.getColumnClassName(i + 1));
map.put("数据库类型",rsd.getColumnTypeName(i + 1));
map.put("字段长度",rsd.getColumnDisplaySize(i + 1));
System.out.print("java类型:" + rsd.getColumnClassName(i + 1));
System.out.print(" 数据库类型:" + rsd.getColumnTypeName(i + 1));
System.out.print(" 字段名称:" + rsd.getColumnName(i + 1));
System.out.print(" 字段长度:" + rsd.getColumnDisplaySize(i + 1));
System.out.println();
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
pst.close();
pst = null;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
return map;
}
/**
* 读取mysql某数据库下表的注释信息
*
* @author xxx
*/
public Connection getMySQLConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
return conn;
}
/**
* 获取当前数据库下的所有表名称
*
* @return
* @throws Exception
*/
public List getAllTableName() throws Exception {
List tables = new ArrayList();
Connection conn = getMySQLConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SHOW TABLES ");
while (rs.next()) {
String tableName = rs.getString(1);
tables.add(tableName);
}
rs.close();
stmt.close();
conn.close();
return tables;
}
/**
* 获得某表的建表语句
*
* @param tableName
* @return
* @throws Exception
*/
public Map getCommentByTableName(List tableName) throws Exception {
Map map = new HashMap();
Connection conn = getMySQLConnection();
Statement stmt = conn.createStatement();
for (int i = 0; i < tableName.size(); i++) {
String table = (String) tableName.get(i);
ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + table);
if (rs != null && rs.next()) {
String createDDL = rs.getString(2);
String comment = parse(createDDL);
map.put(table, comment);
}
ResultSetMetaData rsd = rs.getMetaData();
for (int j = 0; j < rsd.getColumnCount(); j++) {
String columnName = rsd.getColumnName(j + 1);
System.out.print(" 字段名称:" + columnName);
System.out.print(" 字段长度:" + rsd.getColumnDisplaySize(j + 1));
System.out.print("java类型:" + rsd.getColumnClassName(j + 1));
System.out.print(" 数据库类型:" + rsd.getColumnTypeName(j + 1));
System.out.println();
}
rs.close();
}
stmt.close();
conn.close();
return map;
}
/**
* 获得某表中所有字段的注释
*
* @param tableName
* @return
* @throws Exception
*/
public Map getColumnCommentByTableName(List tableName) throws Exception {
Map map = new HashMap();
Connection conn = getMySQLConnection();
Statement stmt = conn.createStatement();
for (int i = 0; i < tableName.size(); i++) {
String table = (String) tableName.get(i);
ResultSet rs = stmt.executeQuery("show full columns from " + table);
// ResultSetMetaData rsd = rs.getMetaData();
System.out.println("【" + table + "】");
while (rs.next()) {
map.put(rs.getString("Field"), rs.getString("Comment"));
System.out.println(rs.getString("Field") + "\t:\t" + rs.getString("Comment"));
}
rs.close();
}
stmt.close();
conn.close();
return map;
}
/**
* 获得单个表中所有字段的注释
*
* @param tableName
* @return
* @throws Exception
*/
public List<Dict> getDict(String table) throws Exception {
List<Dict> dictList = new ArrayList<>();
Connection conn = getMySQLConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("show full columns from " + table);
System.out.println("【" + table + "】");
while (rs.next()) {
Dict dict = new Dict();
dict.setName(table);
dict.setField(rs.getString("Field"));
dict.setFieldName(rs.getString("Comment"));
dictList.add(dict);
System.out.println(rs.getString("Field") + "\t:\t" + rs.getString("Comment"));
}
rs.close();
stmt.close();
conn.close();
return dictList;
}
/**
* 返回注释信息
*
* @param all
* @return
*/
public String parse(String all) {
String comment = null;
int index = all.indexOf("COMMENT='");
if (index < 0) {
return "";
}
comment = all.substring(index + 9);
comment = comment.substring(0, comment.length() - 1);
return comment;
}
public static class Dict implements Serializable {
@ApiModelProperty("数据库用户")
private String root;
@ApiModelProperty("字段描述")
private String ziduanmiaoshu;
@ApiModelProperty("表名")
private String name;
@ApiModelProperty("中文名")
private Object comment;
@ApiModelProperty("字段名")
private String field;
@ApiModelProperty("字段中文名")
private String fieldName;
}
实际调用
public void jdbctest2() throws Exception {
List tables = getAllTableName();
Map tablesComment = getCommentByTableName(tables);
Set names = tablesComment.keySet();
Iterator iter = names.iterator();
List<Dict> rows3 = new ArrayList<>();
while (iter.hasNext()) {
String name = (String) iter.next();
Object o = tablesComment.get(name);
System.out.println("Table Name: " + name + ", Comment: " + o);
}
getColumnCommentByTableName(tables);
tables.stream().forEach(item -> {
try {
if (item.toString().startsWith("dri_")) {
List<Dict> dictList = getDict(item.toString());
dictList.stream().forEach(m -> {
rows3.add(m);
});
try {
getMySqlTableData(item.toString());
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
});
}