jdbc 获取mysql表注解_jdbc读取数据库，表相关信息（含注释）

读取数据库中的所有的表名

private Set getTableNameByCon(Connection con) {

Set set = new HashSet();

try {

DatabaseMetaData meta = con.getMetaData();

ResultSet rs = meta.getTables(null, null, null,

new String[] { "TABLE" });

while (rs.next()) {

set.add(rs.getString(3));

}

con.close();

} catch (Exception e) {

try {

con.close();

} catch (SQLException e1) {

e1.printStackTrace();

}

e.printStackTrace();

}

return set;

}

读取表介绍

private void getTableNotes(Connection con){

try {

DatabaseMetaData dbmd=con.getMetaData();

ResultSet resultSet = dbmd.getTables(null, "%", "%", new String[] { "TABLE" });

while (resultSet.next()) {

String tableName=resultSet.getString("TABLE_NAME");

String remarkes = resultSet.getString("REMARKS");

System.out.println(tableName+"="+remarkes);

}

} catch (Exception e) {

e.printStackTrace();

}

}

其他属性等

String tableName = tableSet.getString("TABLE_NAME");

String tableComment = tableSet.getString("REMARKS");

String columnName = columnSet.getString("COLUMN_NAME");

String columnComment = columnSet.getString("REMARKS");

String sqlType = columnSet.getString("DATA_TYPE");

另一种变通的方式(有时从如上的信息中，取不到注释信息)

这使用下面字符串截取的方式-(可能会出现乱码问题)

package docs;

import java.io.UnsupportedEncodingException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

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;

public class TestTableInfo {

private static String url = "jdbc:mysql://xxxx";

private static String user = "xxx";

private static String pwd = "xxxx";

public static Connection getConnection() {

Connection conn = null;

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(url, user, pwd);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

private static Statement stmt = null;

private static List tableNameList = new ArrayList();

public static Map getCommentByTableName() throws Exception {

Map map = new HashMap();

for (int i = 0; i < tableNameList.size(); i++) {

String table = (String) tableNameList.get(i);

ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + table);

if (rs != null && rs.next()) {

String create = rs.getString(2);

String comment = parse(create);

map.put(table, comment);

}

rs.close();

}

return map;

}

public static void getAllTableName() throws Exception {

Connection conn = getConnection();

stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SHOW TABLES ");

while (rs.next()) {

String tableName = rs.getString(1);

tableNameList.add(tableName);

}

rs.close();

}

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);

try {

comment = new String(comment.getBytes("utf-8"));

} catch (UnsupportedEncodingException e) {

e.printStackTrace();

}

return comment;

}

public static void main(String[] args) throws Exception {

getAllTableName();

Map tablesComment = getCommentByTableName();

Set names = tablesComment.keySet();

Iterator iter = names.iterator();

while (iter.hasNext()) {

String name = iter.next();

System.out.println("Table Name: " + name + ", Comment: "+ tablesComment.get(name));

}

}

}