使用jdbc获取数据库基础信息(附源码)

如果你做的项目要与数据库基础信息打交道，如你要获取一个数据库中有多少个表，哪些表有外键，这些外键的字段名是什么，哪些表被哪些表引用。怎么办呢？在jdbc中，这些我们都能很轻易的做到，只要你通过Connection获取到数据库元信息对象DatabaseMetaData,你就能轻易的获取到你数据库基础信息，下面给出部分代码：

[color=red]（注：本人是直接在自己的项目中使用单元测试的方式试验的，如果读者需要跑起此例子，请自行获取Connection）,[/color]

package com.pay.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.pay.core.Dao;

/**
 *
 */
@SuppressWarnings("unchecked")
public class TestTest
{
    private Connection conn;
    private static BeanFactory fac;
    private static Dao dao;

    @Before
    public void init()
    {
        try
        {
            fac = new ClassPathXmlApplicationContext(
                    "\\conf\\spring\\applicationContext*.xml");
            dao = (Dao) fac.getBean("dao");
            conn = dao.getJdbcTemplate().getDataSource().getConnection();
        }
        catch (Throwable t)
        {
            t.printStackTrace();
        }
    }

    @Test
    public void testGetConnection()
    {
        System.out.println("dao: " + dao);
        System.out.println("conn: " + conn);
    }


    @Test
    public void testGetForeignKey() throws SQLException
    {
        List tableList=getAllTableName(conn);
        for (Object object : tableList)
        {
            System.out.println("tb: "+object);
        }
    }

    @Test
    public void getDatabaseMetaData() throws SQLException
    {
        DatabaseMetaData dbMetaData = conn.getMetaData();
        System.out.println("getUserName: "+dbMetaData.getUserName());
        System.out.println("getDatabaseProductName: "+dbMetaData.getDatabaseProductName());
        System.out.println("getDatabaseProductVersion: "+dbMetaData.getDatabaseProductVersion());
        System.out.println("getDriverMajorVersion: "+dbMetaData.getDriverMajorVersion());
        System.out.println("getDriverName: "+dbMetaData.getDriverName());
        System.out.println("getDriverVersion: "+dbMetaData.getDriverVersion());
        System.out.println("getCatalogTerm: "+dbMetaData.getCatalogTerm());//database

        ResultSet rs=dbMetaData.getPrimaryKeys(null, null, "admin_t");
        while (rs.next())
        {
            String PK_NAME=rs.getString("PK_NAME");
            String COLUMN_NAME=rs.getString("COLUMN_NAME");
            System.out.println("getPrimaryKeys: PK_NAME: "+PK_NAME);
            System.out.println("getPrimaryKeys: COLUMN_NAME: "+COLUMN_NAME);
        }

        rs=dbMetaData.getExportedKeys(null, null, "test_dept");
        while (rs.next())
        {
            System.out.println("\n\n====getExportedKeys: ");
            String PKTABLE_CAT=rs.getString("PKTABLE_CAT");//所在的数据库
            String PKTABLE_SCHEM=rs.getString("PKTABLE_SCHEM");
            String PKTABLE_NAME=rs.getString("PKTABLE_NAME");
            String PKCOLUMN_NAME=rs.getString("PKCOLUMN_NAME");
            String FKTABLE_NAME=rs.getString("FKTABLE_NAME");
            String FKCOLUMN_NAME=rs.getString("FKCOLUMN_NAME");
            String KEY_SEQ=rs.getString("KEY_SEQ");
            String UPDATE_RULE=rs.getString("UPDATE_RULE");
            String FK_NAME=rs.getString("FK_NAME");
            String PK_NAME=rs.getString("PK_NAME");
            String DEFERRABILITY=rs.getString("DEFERRABILITY");
            System.out.println("getExportedKeys: PKTABLE_CAT: "+PKTABLE_CAT);
            System.out.println("getExportedKeys: PKTABLE_SCHEM: "+PKTABLE_SCHEM);
            System.out.println("getExportedKeys: PKTABLE_NAME: "+PKTABLE_NAME);
            System.out.println("getExportedKeys: PKCOLUMN_NAME: "+PKCOLUMN_NAME);
            System.out.println("getExportedKeys: FKTABLE_NAME: "+FKTABLE_NAME);
            System.out.println("getExportedKeys: FKCOLUMN_NAME: "+FKCOLUMN_NAME);
            System.out.println("getExportedKeys: KEY_SEQ: "+KEY_SEQ);
            System.out.println("getExportedKeys: UPDATE_RULE: "+UPDATE_RULE);
            System.out.println("getExportedKeys: FK_NAME: "+FK_NAME);
            System.out.println("getExportedKeys: PK_NAME: "+PK_NAME);
            System.out.println("getExportedKeys: DEFERRABILITY: "+DEFERRABILITY);
        }

        rs=dbMetaData.getImportedKeys(null, null, "test_person");
        while (rs.next())
        {
            System.out.println("\n\n====getImportedKeys: ");
            String PKTABLE_NAME=rs.getString("PKTABLE_NAME");
            String PKCOLUMN_NAME=rs.getString("PKCOLUMN_NAME");
            String FKTABLE_NAME=rs.getString("FKTABLE_NAME");
            String FKCOLUMN_NAME=rs.getString("FKCOLUMN_NAME");
            String KEY_SEQ=rs.getString("KEY_SEQ");
            System.out.println("getImportedKeys: PKTABLE_NAME: "+PKTABLE_NAME);
            System.out.println("getImportedKeys: PKCOLUMN_NAME: "+PKCOLUMN_NAME);
            System.out.println("getImportedKeys: FKTABLE_NAME: "+FKTABLE_NAME);
            System.out.println("getImportedKeys: FKCOLUMN_NAME: "+FKCOLUMN_NAME);
            System.out.println("getExportedKeys: KEY_SEQ: "+KEY_SEQ);
        }

    }

    /**
     * 获取所有的表
     * @param cnn
     * @return
     * @throws SQLException
     */
    private List getAllTableName(Connection cnn) throws SQLException
    {
        List tables = new ArrayList();

        DatabaseMetaData dbMetaData = cnn.getMetaData();

        // 可为:"TABLE", "VIEW", "SYSTEM   TABLE",
        // "GLOBAL   TEMPORARY", "LOCAL   TEMPORARY", "ALIAS", "SYNONYM"
        String[] types = { "TABLE" }; 

        ResultSet tabs = dbMetaData.getTables(null, null, null, types);
        while (tabs.next())
        {
            tables.add(tabs.getObject("TABLE_NAME"));

        }
        return tables;
    }

}