Small example of how to get DB metadata using JDBC.
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class MetadataTest {
public static void main(String args[]) {
try {
Connection conn = getMySQLConnection();
DatabaseMetaData md = conn.getMetaData();
ResultSet rsCatalogs = md.getCatalogs();
System.out.println("====================================================================");
System.out.println("Databases: ");
while(rsCatalogs.next()) {
System.out.println(" * " + rsCatalogs.getString("TABLE_CAT"));
}
// getTables(catalog, schemaPattern, tableNamePattern, types)
ResultSet rsTables = md.getTables("metadata", null, null, null);
System.out.println("====================================================================");
System.out.println("Tables: ");
System.out.print("\n");
while(rsTables.next()) {
String tableName = rsTables.getString("TABLE_NAME");
System.out.print(" ** " + tableName);
System.out.print(" || Type: " + rsTables.getString("TABLE_TYPE"));
System.out.print(" || Catalog: " + rsTables.getString("TABLE_CAT"));
System.out.println(" || Scheme: " + rsTables.getString("TABLE_SCHEM"));
//getTables(catalog, schemaPattern, tableNamePattern, columnNamePattern)
ResultSet rsColumns = md.getColumns("metadata",null,tableName,null);
while(rsColumns.next()) {
System.out.print(" ====> " + rsColumns.getString("COLUMN_NAME"));
System.out.print(", " + rsColumns.getString("TYPE_NAME"));
System.out.print(" (" + rsColumns.getString("COLUMN_SIZE"));
System.out.println("," + rsColumns.getInt("DECIMAL_DIGITS") + ")");
}
System.out.println("--------------------------------------------------------------------");
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getMySQLConnection() throws Exception {
Connection conn;
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql:///metadata", "root",
"");
return conn;
}
}
The example above, displays all the databases available, plus tables/columns under database metadata. Below is the SQL script you can run (MySQL) to use with this example.
CREATE DATABASE metadata;
USE metadata;
CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
customerName VARCHAR(50),
customerMiddle VARCHAR(1),
customerSurname VARCHAR(50),
customerAge TINYINT,
customerBirthdate DATETIME);
CREATE TABLE stores (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
storeCode INT,
storeName VARCHAR(100),
storeAvgIncome DECIMAL(26,4));
CREATE TABLE products (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
productCode INT,
productName VARCHAR(100),
productPrice FLOAT(8,2));
CREATE TABLE stock (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
productCode INT,
storeCode INT,
stock SMALLINT UNSIGNED);
CREATE VIEW v_Stock AS
SELECT stores.storeCode,stores.storeName,products.productCode,products.productName,stock.stock
FROM stock stock
JOIN stores stores
ON stock.storeCode = stores.storeCode
JOIN products products
ON stock.productCode = products.productCode;
Once you execute the java program, you should get something like this:
====================================================================
Databases:
* information_schema
* metadata
* mysql
* rbs
* test
====================================================================
Tables:
** customers || Type: TABLE || Catalog: metadata || Scheme: null
====> id, INT (10,0)
====> customerName, VARCHAR (50,0)
====> customerMiddle, VARCHAR (1,0)
====> customerSurname, VARCHAR (50,0)
====> customerAge, INT (10,0)
====> customerBirthdate, DATETIME (19,0)
--------------------------------------------------------------------
** products || Type: TABLE || Catalog: metadata || Scheme: null
====> id, INT (10,0)
====> productCode, INT (10,0)
====> productName, VARCHAR (100,0)
====> productPrice, FLOAT (8,2)
--------------------------------------------------------------------
** stock || Type: TABLE || Catalog: metadata || Scheme: null
====> id, INT (10,0)
====> productCode, INT (10,0)
====> storeCode, INT (10,0)
====> stock, SMALLINT UNSIGNED (5,0)
--------------------------------------------------------------------
** stores || Type: TABLE || Catalog: metadata || Scheme: null
====> id, INT (10,0)
====> storeCode, INT (10,0)
====> storeName, VARCHAR (100,0)
====> storeAvgIncome, DECIMAL (26,2)
--------------------------------------------------------------------
** v_stock || Type: VIEW || Catalog: metadata || Scheme: null
====> storeCode, INT (10,0)
====> storeName, VARCHAR (100,0)
====> productCode, INT (10,0)
====> productName, VARCHAR (100,0)
====> stock, SMALLINT UNSIGNED (5,0)
--------------------------------------------------------------------
