![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
|
JavaSE データベース構造の読み取り |
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
