在最近的项目中碰到一个数据源的配置需求，就是需要配置公司所有系统的数据库、表等信息，其中大数据部门抽数时需要过滤某些表的敏感字段，如身份证号、手机号等敏感字段。

需要后端动态连接数据库，及获取相应数据库的表、字段等信息。

示例代码如下：

public class DatabaseUtilTest {
 private static final Logger logger = LoggerFactory.getLogger(DatabaseTest.class);
 private static final String DRIVER = "com.mysql. jdbc .Driver";
 private static final String DATABASE_URL = "jdbc:mysql://ip:3306/databasename?useUnicode=true&characterEncoding=utf8";
 private static final String USERNAME = "xxx";
 private static final String PASSWORD = "xxx";
 private static final String SQL = "SELECT * FROM ";// 数据库操作
 static {
 try {
 Class.forName(DRIVER);
 } catch (ClassNotFoundException e) {
 logger.error("can not load jdbc driver", e);
 }
 }
 /**
 * 获取数据库连接
 *
 * @return
 */ public static Connection getConnection() {
 Connection conn = null;
 try {
 conn = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);
 } catch (SQLException e) {
 logger.error("get connection failure", e);
 }
 return conn;
 }
 /**
 * 关闭数据库连接
 * @param conn
 */ public static void  close Connection(Connection conn) {
 if(conn != null) {
 try {
 conn.close();
 } catch ( SQL Exception e) {
 logger.error("close connection failure", e);
 }
 }
 }
 /**
 * 获取数据库下的所有表名
 */ public static List<String> getTableNames() {
 List<String> tableNames = new ArrayList<>();
 Connection conn = getConnection();
 ResultSet rs = null;
 try {
 //获取数据库的 元数据 
 DatabaseMetaData db = conn.getMetaData();
 //从元数据中获取到所有的表名
 rs = db.getTables(null, null, null, new String[] { "TABLE" });
 while(rs.next()) {
 tableNames.add(rs.getString(3));
 }
 } catch (SQLException e) {
 logger.error("getTableNames failure", e);
 } finally {
 try {
 rs.close();
 closeConnection(conn);
 } catch (SQLException e) {
 logger.error("close ResultSet failure", e);
 }
 }
 return tableNames;
 }
 /**
 * 获取表中所有字段名称
 * @param tableName 表名
 * @return
 */ public static List<String> getColumnNames(String tableName) {
 List<String> columnNames = new ArrayList<>();
 //与数据库的连接
 Connection conn = getConnection();
 PreparedStatement pStemt = null;
 String tableSql = SQL + tableName;
 try {
 pStemt = conn.prepareStatement(tableSql);
 //结果集元数据
 ResultSetMetaData rsmd = pStemt.getMetaData();
 //表列数
 int size = rsmd.getColumnCount();
 for (int i = 0; i < size; i++) {
 columnNames.add(rsmd.getColumnName(i + 1));
 }
 } catch (SQLException e) {
 logger.error("getColumnNames failure", e);
 } finally {
 if (pStemt != null) {
 try {
 pStemt.close();
 closeConnection(conn);
 } catch (SQLException e) {
 logger.error("getColumnNames close pstem and connection failure", e);
 }
 }
 }
 return columnNames;
 }
 /**
 * 获取表中所有字段类型
 * @param tableName
 * @return
 */ public static List<String> getColumnTypes(String tableName) {
 List<String> columnTypes = new ArrayList<>();
 //与数据库的连接
 Connection conn = getConnection();
 PreparedStatement pStemt = null;
 String tableSql = SQL + tableName;
 try {
 pStemt = conn.prepareStatement(tableSql);
 //结果集元数据
 ResultSetMetaData rsmd = pStemt.getMetaData();
 //表列数
 int size = rsmd.getColumnCount();
 for (int i = 0; i < size; i++) {
 columnTypes.add(rsmd.getColumnTypeName(i + 1));
 }
 } catch (SQLException e) {
 logger.error("getColumnTypes failure", e);
 } finally {
 if (pStemt != null) {
 try {
 pStemt.close();
 closeConnection(conn);
 } catch (SQLException e) {
 logger.error("getColumnTypes close pstem and connection failure", e);
 }
 }
 }
 return columnTypes;
 }
 /**
 * 获取表中字段的所有注释
 * @param tableName
 * @return
 */ public static List<String> getColumnComments(String tableName) {
 List<String> columnTypes = new ArrayList<>();
 //与数据库的连接
 Connection conn = getConnection();
 PreparedStatement pStemt = null;
 String tableSql = SQL + tableName;
 List<String> columnComments = new ArrayList<>();//列名注释集合
 ResultSet rs = null;
 try {
 pStemt = conn.prepareStatement(tableSql);
 rs = pStemt.executeQuery("show full columns from " + tableName);
 while (rs.next()) {
 columnComments.add(rs.getString("Comment"));
 }
 } catch (SQLException e) {
 e.printStackTrace();
 } finally {
 if (rs != null) {
 try {
 rs.close();
 closeConnection(conn);
 } catch (SQLException e) {
 logger.error("getColumnComments close ResultSet and connection failure", e);
 }
 }
 }
 return columnComments;
 }
 public static void main(String[] args) {
 List<String> tableNames = getTableNames();
 System.out.println("tableNames:" + tableNames);
 for (String tableName : tableNames) {
 System.out.println("================start==========================");
 System.out.println("==============================================");
 System.out.println("ColumnNames:" + getColumnNames(tableName));
 System.out.println("ColumnTypes:" + getColumnTypes(tableName));
 System.out.println("ColumnComments:" + getColumnComments(tableName));
 System.out.println("==============================================");
 System.out.println("=================end=======================");
 }
 }
}
 

如是获取多个数据库的信息，上面的DATABASE_URL,USERNAME,PASSWORD可以动态替换。 出于安全性的考虑，在实际的编码中建议数据库的用户名和密码不要硬编码在代码中，即使出现也建议进行加密处理。 数据库的用户名和密码是非常非常敏感的信息，任何时候都需要非常注意！！！