RSS テーブルの作成を参照
package com.snail.jdbc; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.LinkedList; import java.util.List; import java.util.Map; public final class DBAnalizer { public static void main(String[] args) { String user = "RSS"; String pass = "RSSPASS"; String url = "jdbc:mysql://192.168.1.6:3306/RSS_READER?useUnicode=true&characterEncoding=UTF8"; String dirver = "com.mysql.jdbc.Driver"; try { Class.forName(dirver); Connection con = DriverManager.getConnection(url, user, pass); con.setReadOnly(true); DatabaseMetaData dbmd = con.getMetaData(); ResultSet tableRs = dbmd.getTables("RSS_READER", "%", "%", null); while (tableRs.next()) { String category = tableRs.getString("TABLE_CAT"); String schema = tableRs.getString("TABLE_SCHEM"); String table = tableRs.getString("TABLE_NAME"); // get Primary Keys ResultSet pkRs = dbmd.getPrimaryKeys(category, schema, table); List<String> pkList = new LinkedList<String>(); while (pkRs.next()) { String column = pkRs.getString("COLUMN_NAME"); pkList.add(column); } pkRs.close(); // get Export Foreign Keys ResultSet fkRs = dbmd.getExportedKeys(category, schema, table); Map<String,String> exMap = new HashMap<String,String>(); while (fkRs.next()) { String pk = fkRs.getString("PKCOLUMN_NAME"); String foreignTable = fkRs.getString("FKTABLE_NAME"); String foreignColumn = fkRs.getString("FKCOLUMN_NAME"); String tbl = exMap.get(pk); if(tbl==null){ exMap.put(pk,foreignTable+"."+foreignColumn); }else{ exMap.put(pk,tbl + ", " +foreignTable+"."+foreignColumn); } } fkRs.close(); // get Import Foreign Keys ResultSet inRs = dbmd.getImportedKeys(category, schema, table); Map<String,String> inMap = new HashMap<String,String>(); while (inRs.next()) { String fk = inRs.getString("FKCOLUMN_NAME"); String foreignTable = inRs.getString("PKTABLE_NAME"); String foreignColumn = inRs.getString("PKCOLUMN_NAME"); String tbl = inMap.get(fk); if(tbl==null){ inMap.put(fk,foreignTable+"."+foreignColumn); }else{ inMap.put(fk,tbl + ", " +foreignTable+"."+foreignColumn); } } inRs.close(); ResultSet columnRs = dbmd.getColumns(category, schema, table, "%"); while (columnRs.next()) { String column = columnRs.getString("COLUMN_NAME"); String type = columnRs.getString("TYPE_NAME"); String size = columnRs.getString("COLUMN_SIZE"); String decimal = columnRs.getString("DECIMAL_DIGITS"); String nullable = columnRs.getString("IS_NULLABLE"); System.out.print(table + " " + column + " (" + type + "(" + size + "." + decimal + "))"); if("NO".equals(nullable)){ System.out.print(" NOT_NULL"); } if(pkList.contains(column)){ System.out.print(" PRIMARY_KEY"); } if(exMap.containsKey(column)){ System.out.print("\t=>"+exMap.get(column)); } if(inMap.containsKey(column)){ System.out.print("\t<="+inMap.get(column)); } System.out.println(""); } columnRs.close(); } tableRs.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } }
BANK_CONTROLLER ID (bigint(20.0)) NOT_NULL PRIMARY_KEY =>ITEM.BANK_ID BANK_CONTROLLER IS_AVAILABLE (BIT(1.0)) NOT_NULL BASYAN_DICTIONARY ID (bigint(20.0)) NOT_NULL PRIMARY_KEY BASYAN_DICTIONARY WORD (varchar(255.0)) NOT_NULL BASYAN_DICTIONARY CLICKED_AND_EXIST (bigint(20.0)) NOT_NULL BASYAN_DICTIONARY CLICKED_AND_NOTEXIST (bigint(20.0)) NOT_NULL BASYAN_DICTIONARY NOTCLICKED_AND_EXIST (bigint(20.0)) NOT_NULL BASYAN_DICTIONARY NOTCLICKED_AND_NOTEXIST (bigint(20.0)) NOT_NULL CATEGORY ID (bigint(20.0)) NOT_NULL PRIMARY_KEY =>CHANNEL.CATEGORY_ID CATEGORY NAME (varchar(255.0)) NOT_NULL CATEGORY DEFAULT_OFF (BIT(1.0)) NOT_NULL CHANNEL ID (bigint(20.0)) NOT_NULL PRIMARY_KEY =>ITEM.CHANNEL_ID, OPML.CHANNEL_ID CHANNEL TITLE (varchar(255.0)) NOT_NULL CHANNEL IMAGE (varchar(255.0)) CHANNEL SITE (varchar(255.0)) NOT_NULL CHANNEL FORMAT (varchar(15.0)) NOT_NULL CHANNEL DESCRIPTION (text(65535.0)) CHANNEL TITLE_ALIAS (varchar(255.0)) NOT_NULL CHANNEL CATEGORY_ID (bigint(20.0)) NOT_NULL <=CATEGORY.ID CLICK_TIMES IS_CLICKED (BIT(1.0)) NOT_NULL PRIMARY_KEY CLICK_TIMES TIME (bigint(20.0)) NOT_NULL ITEM ID (bigint(20.0)) NOT_NULL PRIMARY_KEY ITEM CHANNEL_ID (bigint(20.0)) NOT_NULL <=CHANNEL.ID ITEM TITLE (varchar(255.0)) NOT_NULL ITEM LINK (varchar(255.0)) NOT_NULL ITEM LAST_UPDATE (datetime(19.0)) NOT_NULL ITEM DESCRIPTION (text(65535.0)) ITEM BSCORE (double(22.0)) ITEM BANK_ID (bigint(20.0)) NOT_NULL <=BANK_CONTROLLER.ID OPML ID (bigint(20.0)) NOT_NULL PRIMARY_KEY OPML USER_ID (bigint(20.0)) NOT_NULL <=USER.ID OPML CHANNEL_ID (bigint(20.0)) NOT_NULL <=CHANNEL.ID USER ID (bigint(20.0)) NOT_NULL PRIMARY_KEY =>OPML.USER_ID USER USER (varchar(255.0)) NOT_NULL USER PASSWORD (varchar(255.0)) NOT_NULL USER COOKIE (varchar(255.0)) NOT_NULL USER LAST_LOGIN (datetime(19.0)) NOT_NULL