- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.Iterator;
- import java.util.List;
- import java.util.Map;
- import java.util.Set;
- /**
- * 读取mysql某数据库下表的注释信息
- *
- * @author xxx
- */
- public class MySQLTableComment {
- public static Connection getMySQLConnection() throws Exception {
- Class.forName("com.mysql.jdbc.Driver");
- Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/databaseName", "root", "root");
- return conn;
- }
- /**
- * 获取当前数据库下的所有表名称
- * @return
- * @throws Exception
- */
- public static 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 static 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);
- }
- rs.close();
- }
- stmt.close();
- conn.close();
- return map;
- }
- /**
- * 获得某表中所有字段的注释
- * @param tableName
- * @return
- * @throws Exception
- */
- public static void 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);
- System.out.println("【"+table+"】");
- // if (rs != null && rs.next()) {
- //map.put(rs.getString("Field"), rs.getString("Comment"));
- while (rs.next()) {
- // System.out.println("字段名称:" + rs.getString("Field") + "\t"+ "字段注释:" + rs.getString("Comment") );
- System.out.println(rs.getString("Field") + "\t:\t"+ rs.getString("Comment") );
- }
- // }
- rs.close();
- }
- stmt.close();
- conn.close();
- // return map;
- }
- /**
- * 返回注释信息
- * @param all
- * @return
- */
- public static 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 void main(String[] args) throws Exception {
- List tables = getAllTableName();
- Map tablesComment = getCommentByTableName(tables);
- Set names = tablesComment.keySet();
- Iterator iter = names.iterator();
- while (iter.hasNext()) {
- String name = (String) iter.next();
- System.out.println("Table Name: " + name + ", Comment: " + tablesComment.get(name));
- }
- getColumnCommentByTableName(tables);
- }
java读取mysql表的注释及字段注释
最新推荐文章于 2024-08-04 15:33:44 发布