首页 > 代码库 > jdbc读取数据库,表相关信息(含注释)
jdbc读取数据库,表相关信息(含注释)
读取数据库中的所有的表名
private Set<String> getTableNameByCon(Connection con) { Set<String> set = new HashSet<String>(); 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<String> tableNameList = new ArrayList<String>(); public static Map<String,String> getCommentByTableName() throws Exception { Map<String,String> map = new HashMap<String,String>(); 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<String,String> tablesComment = getCommentByTableName(); Set<String> names = tablesComment.keySet(); Iterator<String> iter = names.iterator(); while (iter.hasNext()) { String name = iter.next(); System.out.println("Table Name: " + name + ", Comment: "+ tablesComment.get(name)); } } }
jdbc读取数据库,表相关信息(含注释)
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。