//https://www.baeldung.com/jdbc-database-metadata
//https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/DatabaseMetaData.html
/**
* 获取某数据库中的表列表 涂聚文 Geovin Du geovindu
* @param
* @return TablesMetadata集合
* */
public ArrayList<TablesMetadata> selectTablesMetadata()
{
ArrayList<TablesMetadata> list=new ArrayList<TablesMetadata>();
TablesMetadata info=null;
Connection connection=null;
CallableStatement cstm =null;
PreparedStatement pstmt = null;
try {
Class.forName(DRIVER_CLASS);
}
catch (ClassNotFoundException exception)
{
exception.printStackTrace();
}
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// connection.getSchema();
DatabaseMetaData databaseMetaData=connection.getMetaData();
String productName = databaseMetaData.getDatabaseProductName();
String productVersion = databaseMetaData.getDatabaseProductVersion();
String driverName = databaseMetaData.getDriverName();
String driverVersion = databaseMetaData.getDriverVersion();
boolean supportsFullOuterJoins = databaseMetaData.supportsFullOuterJoins();
boolean supportsStoredProcedures = databaseMetaData.supportsStoredProcedures();
boolean supportsTransactions = databaseMetaData.supportsTransactions();
boolean supportsBatchUpdates = databaseMetaData.supportsBatchUpdates();
String userName = databaseMetaData.getUserName();
/*
catalog 包含目录名称的 String。对此参数提供 Null 值表示无需使用目录名称。
schema 包含架构名称模式的 String 值。对此参数提供 Null 值表示无需使用架构名称。
tableNamePattern 包含表名称模式的 String。
types 含有要包含的表类型的字符串数组。Null 表示应包含所有表类型。
* */
ResultSet resultSet = databaseMetaData.getTables(null, null, null, new String[]{"TABLE"});
while(resultSet.next()) {
info=new TablesMetadata();
info.setTableName(resultSet.getString("TABLE_NAME"));
info.setRemarks(resultSet.getString("REMARKS"));
info.setTableSchem(resultSet.getString("TABLE_SCHEM"));
info.setTableType(resultSet.getString("TABLE_TYPE"));
list.add(info);
}
resultSet.close();
resultSet=null;
connection.close();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
return list;
}
/**
* 返回某表的列属性列表 涂聚文 Geovin Du geovindu
* @param TableName
* @return 返回集合
* */
public ArrayList<ColumnsMetadata> selectColumnsMetadata(String TableName)
{
ArrayList<ColumnsMetadata> list=new ArrayList<ColumnsMetadata>();
ColumnsMetadata info=null;
Connection connection=null;
CallableStatement cstm =null;
PreparedStatement pstmt = null;
try {
Class.forName(DRIVER_CLASS);
}
catch (ClassNotFoundException exception)
{
exception.printStackTrace();
}
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// connection.getSchema();
DatabaseMetaData databaseMetaData=connection.getMetaData();
ResultSet resultSet = databaseMetaData.getColumns(null,null, TableName, null);
while(resultSet.next()) {
info=new ColumnsMetadata();
info.setColumnName(resultSet.getString("COLUMN_NAME"));
// String columnName = resultSet.getString("COLUMN_NAME");
info.setClumnSize(resultSet.getString("COLUMN_SIZE"));
info.setDataType(resultSet.getString("DATA_TYPE"));
info.setIsNullable(resultSet.getString("IS_NULLABLE"));
info.setIsAutoincRement(resultSet.getString("IS_AUTOINCREMENT"));
list.add(info);
}
// resultSet.close();
resultSet=null;
// connection.close();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
return list;
}
/**
* 主键
* @param TableName
* @return
*
* */
public ArrayList<ColumnsPrimaryKeys> selectColumnsPrimaryKeys(String TableName)
{
ArrayList<ColumnsPrimaryKeys> list=new ArrayList<ColumnsPrimaryKeys>();
ColumnsPrimaryKeys info=null;
Connection connection=null;
CallableStatement cstm =null;
PreparedStatement pstmt = null;
try {
Class.forName(DRIVER_CLASS);
}
catch (ClassNotFoundException exception)
{
exception.printStackTrace();
}
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// connection.getSchema();
DatabaseMetaData databaseMetaData=connection.getMetaData();
ResultSet resultSet = databaseMetaData.getPrimaryKeys(null, null, TableName);
while(resultSet.next()) {
info=new ColumnsPrimaryKeys();
info.setColumnName(resultSet.getString("COLUMN_NAME"));
info.setPkName(resultSet.getString("PK_NAME"));
list.add(info);
}
resultSet.close();
resultSet=null;
connection.close();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
return list;
}
/**
* 外键
* @param TableName
* @return
*
* */
public ArrayList<ColumnsForeignKeys> selectColumnsForeignKeys(String TableName)
{
ArrayList<ColumnsForeignKeys> list=new ArrayList<ColumnsForeignKeys>();
ColumnsForeignKeys info=null;
Connection connection=null;
CallableStatement cstm =null;
PreparedStatement pstmt = null;
try {
Class.forName(DRIVER_CLASS);
}
catch (ClassNotFoundException exception)
{
exception.printStackTrace();
}
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// connection.getSchema();
DatabaseMetaData databaseMetaData=connection.getMetaData();
ResultSet resultSet = databaseMetaData.getImportedKeys(null, null, TableName);
while(resultSet.next()) {
info=new ColumnsForeignKeys();
info.setPkTableName(resultSet.getString("PKTABLE_NAME"));
info.setPkColumnName(resultSet.getString("PKCOLUMN_NAME"));
info.setFkTableName(resultSet.getString("FKTABLE_NAME"));
info.setFkColumnName(resultSet.getString("FKCOLUMN_NAME"));
list.add(info);
}
resultSet.close();
resultSet=null;
connection.close();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
return list;
}
/**
*
*
* */
public ArrayList<SchemasMetadata> selectSchemasMetadata()
{
ArrayList<SchemasMetadata> list=new ArrayList<SchemasMetadata>();
SchemasMetadata info=null;
Connection connection=null;
CallableStatement cstm =null;
PreparedStatement pstmt = null;
try {
Class.forName(DRIVER_CLASS);
}
catch (ClassNotFoundException exception)
{
exception.printStackTrace();
}
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// connection.getSchema();
DatabaseMetaData databaseMetaData=connection.getMetaData();
ResultSet resultSet = databaseMetaData.getSchemas();
while (resultSet.next()){
info=new SchemasMetadata();
System.out.println("Schem:"+resultSet.getString("TABLE_SCHEM"));
info.setTableSchem(resultSet.getString("TABLE_SCHEM"));
info.setTableCatalog(resultSet.getString("TABLE_CATALOG"));
list.add(info);
}
// resultSet.close();
//resultSet=null;
connection.close();
}
catch (SQLException sqlException)
{
sqlException.printStackTrace();
}
return list;
}
