Java JDBC元数据示例 - 在数据库的所有数据库表中搜索给定字段名称

・4 分钟阅读

注意:你可以使用下面的Java程序完成此任务。尤其是如果你没有使用MySQL,但是如果你使用的是MySQL,则不用那么麻烦,可以直接使用这个查询:

SELECT DISTINCT TABLE_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE COLUMN_NAME IN ('field_photo', 'field_photo_alt')
 AND TABLE_SCHEMA='mydatabase';

查询搜索名为mydatabase的MySQL数据库,以显示(field_photo,field_photo_alt)的两个字段。

如果你不使用MySQL,下面的Java程序会帮助你。

使用Java查找给定列名的所有表

如果你需要查找数据库中的所有表,或者搜索所有数据库表的字段,可以使用此程序,

另外,从我使用的驱动程序和URL中可以看到,我在搜索一个Microsoft Access数据库,并使用JDBC ODBC驱动程序连接到Access数据库。

package my_package;
import java.sql.*;
import java.util.*;
/**
 * Use this program to search the entire database for a given column name.
 * The expected purpose of this program is to help find foreign keys that are not identified
 * in the schema.
 * Currently configured to search a Microsoft Access database.
 */
public class Main_SearchAllTablesForFieldname {
 // put the desired database field name here.
 // the program will search all tables in the database for this name.
 String colNameToSearchFor ="part_no";
 String catalog = null;
 String schema = null;
 List listOfTables = new ArrayList();
 public static void main(String[] args)
 {
 new Main_SearchAllTablesForFieldname();
 }
 public Main_SearchAllTablesForFieldname()
 {
 try
 {
 // connect with the jdbc odbc bridge driver
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 // the jdbc odbc connection string
 Connection con = DriverManager.getConnection("jdbc:odbc:DB_NAME","","");
 // get the database metadata
 DatabaseMetaData dmd = con.getMetaData();
 // get a list of all tables
 getListOfAllTables(listOfTables, dmd);
 // see if you can find the column name in any tables
 searchForColumnNameInTables(dmd);
 }
 catch (Exception e)
 {
 System.err.println("exception:" + e.getMessage());
 }
 }
 private void searchForColumnNameInTables(DatabaseMetaData dmd) 
 throws SQLException {
 Iterator iter = listOfTables.iterator();
 while (iter.hasNext()) {
 String tableName = (String) iter.next();
 java.sql.ResultSet rs = dmd.getColumns(catalog, schema, tableName,"%");
 while (rs.next()) {
 String colName = rs.getString(4);
 if (colName.trim().toLowerCase().equals(colNameToSearchFor)){
 System.out.println("found '" + colNameToSearchFor +"' in" + tableName );
 }
 }
 }
 }
 private void getListOfAllTables(List listOfTables, DatabaseMetaData dmd) 
 throws SQLException {
 String[] tableTypes = {
"TABLE",
"VIEW",
"ALIAS",
"SYNONYM",
"GLOBAL TEMPORARY",
"LOCAL TEMPORARY",
"SYSTEM TABLE"};
 ResultSet rs = dmd.getTables(catalog, schema,"%", tableTypes);
 while (rs.next()) {
 String tableName = rs.getString(3);
 listOfTables.add(tableName);
 }
 rs.close();
 }
}
Xn_warm profile image