【JAVA开发小技巧】JDBC批量获取数据库名、字段名、字段类型和注释信息

---

前言

当我们在项目中遇到以下需求：
1.给定数据库，遍历获取出数据库下的数据表名；
2.给定数据表，遍历获取出表中的所有字段，对应的字段名、字段注释、字段类型等信息；

使用JAVA原装的JDBC就能简单地实现以上的需求。大家想必也用过EasyCode这个十分简单方便的插件，其实它的底层就是用原装的JDBC来实现的。

---

一、给定数据库遍历数据表

1.创建方法

public class DBUtil {
    	private static final Logger log = LoggerFactory.getLogger(DBUtil.class);
 		
 	/**
     * @param driver 驱动
     * @param url   数据库url
     * @param user 用户名
     * @param pwd  密码
     * @param schema 数据库名
     * @return
     */
        public static List<String> getTables(String driver, String url, String user, String pwd, String schema) {
        
        List<String> tables = new ArrayList<>();

        Connection conn = null;
        DatabaseMetaData dbmd = null;

        try {
            // 连接数据库
            conn = getConnections(driver, url, user, pwd);
            // 获取数据库元数据
            dbmd = conn.getMetaData();
            // 调用元数据类的getTables方法获取表信息结果集
            ResultSet resultSet = dbmd.getTables(conn.getCatalog(), schema, null, new String[]{"TABLE"});
            // 遍历结果集获取表名
            while (resultSet.next()) {
                tables.add(resultSet.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        finally {
            try {
                assert (conn != null);
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return tables;
    }
}

2.调用方法

	public static void main(String[] args) {
		
		//Oracle数据库
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@192.168.195.1:1521:orcl";
		String user = "username";
		String pwd = "123456";
		String schema = "USER_SCHEMA";
		
		//Mysql数据库
		/*
		String driver = "com.mysql.jdbc.Driver";
		String user = "root";
		String pwd = "123456";
		String url = "jdbc:mysql://localhost/navicat?useUnicode=true&characterEncoding=UTF-8";
		String schema = "USER_SCHEMA";
		*/
		
		List<String> tables = getTableInfo(driver, url, user, pwd, schema);
		for(table : tables){
			System.out.println(table);
		}
	}
	
}

二、给出数据表遍历字段信息

1.创建方法

代码如下（示例）：

public class DBUtil {
    	private static final Logger log = LoggerFactory.getLogger(DBUtil.class);
 		
    /**
     * @param driver 驱动
     * @param url   数据库url
     * @param user 用户名
     * @param pwd  密码
     * @param schema 数据库名
     * @param table 数据表名
     * @return 用map存储一个字段的字段名、类型、注释信息
     */
    public static List<Map<String, String>> getFiels(String driver, String url, String user, String pwd, String schema, String table){
        List<Map<String, String>> mapList = new ArrayList<>();

        Connection conn = null;
        DatabaseMetaData dbmd = null;

        try {
            conn = getConnections(driver, url, user, pwd);

            dbmd = conn.getMetaData();
            // 获取所有数据表结果集
            ResultSet resultSetTable = dbmd.getTables(conn.getCatalog(), schema, table, new String[] { "TABLE" });
		
            while (resultSetTable.next()) {
            	// 获取当前表名
                String tableName=resultSetTable.getString("TABLE_NAME");
				// 判断当前表名是否等于目标表名
                if(tableName.equals(table)){
                	// 获取目标表的字段结果集
                    ResultSet resultSetFiel= conn.getMetaData().getColumns(conn.getCatalog(), schema, tableName, null);

                    while(resultSetFiel.next()){
                        Map<String,String> map = new HashMap<>();
                        
                        String colName = resultSetFiel.getString("COLUMN_NAME");
                        map.put("colName", colName);

                        String remarks = resultSetFiel.getString("REMARKS");
                        map.put("remark", remarks);

						String typeName = rs.getString("TYPE_NAME");
		    			map.put("type", typeName);

                        mapList.add(map);
                    }
                }
            }
        }  catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                assert (conn != null);
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return mapList;
    }
}

2.调用方法

public static void main(String[] args) {
		
		//Oracle数据库
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@192.168.195.1:1521:orcl";
		String user = "username";
		String pwd = "123456";
		String schema = "USER_SCHEMA";
		String table = "T_USER";
		
		//Mysql数据库
		/*
		String driver = "com.mysql.jdbc.Driver";
		String user = "root";
		String pwd = "123456";
		String url = "jdbc:mysql://localhost/navicat?useUnicode=true&characterEncoding=UTF-8";
		String schema = "USER_SCHEMA";
		String table = "T_USER";
		*/
		List<Map<String, String>> mapList = getFiels(driver, url, user, pwd, schema, table);
		
		for(map : mapList){
			for(key : map.keySet()){
				System.out.println(key + ":" + map.get(key));
			}
			System.out.println();
		}
	
}

---