摘要
本文讲述如何通过Java读取Oracle数据库中的表的每个列的信息。
本文讲述如何通过Java读取Oracle数据库中的表的每个列的信息。
本文将通过Java读取Oracle数据库中的表的列信息,包含字段名称,字段类型,是否为主键,是否允许为空,列的注释。
首先创建一个实体类来作为列信息的载体:
package com.example.demo.entity; import lombok.Data; /** * @project: * @description: 数据库表的列信息。 * @version 1.0.0 * @errorcode * 错误码: 错误描述 * @author * <li>2020-12-14 825338623@qq.com Create 1.0 * @copyright ©2019-2020 https://www.blog-cn.com,版权所有。 */ @Data public class DbTableInfo { /*** 列名 */ private String colomCode; /** 类型 */ private String colomType; /*** 注释 */ private String colomComment; /*** 是否主键 */ private boolean isPrimary; /*** 不允许为空 */ private boolean notAllowNull; }
主方法如下:
package com.example.demo.readTable; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * @project: * @description: 中文类名。 * 类功能简介。 * @version 1.0.0 * @errorcode * 错误码: 错误描述 * @author * <li>2020-12-14 825338623@qq.com Create 1.0 * @copyright ©2019-2020 北银金科,版权所有。 */ public class DatabaseUtil4MySql { private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil4MySql.class); private static final String DRIVER = "com.mysql.jdbc.Driver"; private static final String URL = "jdbc:mysql://localhost:3306/javaweb?useUnicode=true&characterEncoding=utf8"; private static final String USERNAME = "root"; private static final String PASSWORD = "mysql"; 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(URL, USERNAME, PASSWORD); } catch (SQLException e) { LOGGER.error("get connection failure", e); } return conn; } /** * 关闭数据库连接 * @param conn */ public static void closeConnection(Connection conn) { if(conn != null) { try { conn.close(); } catch (SQLException 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("ColumnNames:" + getColumnNames(tableName)); System.out.println("ColumnTypes:" + getColumnTypes(tableName)); System.out.println("ColumnComments:" + getColumnComments(tableName)); } } }
测试打印结果如下:
{"colomCode":"CI_ID","colomComment":"主键","colomType":"VARCHAR2","notAllowNull":true,"primary":true} {"colomCode":"CI_COMPANY_NAME","colomComment":"企业名称","colomType":"VARCHAR2","notAllowNull":false,"primary":false} {"colomCode":"CI_CREDIT_CODE","colomComment":"统一社会信用代码","colomType":"VARCHAR2","notAllowNull":false,"primary":false} {"colomCode":"CI_ORG_CODE","colomComment":"组织机构代码","colomType":"VARCHAR2","notAllowNull":false,"primary":false} {"colomCode":"CI_COMPANY_TYPE","colomComment":"企业(机构)类型","colomType":"VARCHAR2","notAllowNull":false,"primary":false} {"colomCode":"CI_COMPANY_TYPE_NAME","colomComment":"企业类型名称","colomType":"VARCHAR2","notAllowNull":false,"primary":false} {"colomCode":"CI_INDUSTRY_CODE","colomComment":"行业代码","colomType":"VARCHAR2","notAllowNull":false,"primary":false} {"colomCode":"CI_INDUSTRY_TYPE","colomComment":"行业门类","colomType":"VARCHAR2","notAllowNull":false,"primary":false} {"colomCode":"CI_INDUSTRY_NAME","colomComment":"行业名称","colomType":"VARCHAR2","notAllowNull":false,"primary":false} {"colomCode":"CI_MANAGE_FROM","colomComment":"经营(驻在)期限自","colomType":"DATE","notAllowNull":false,"primary":false} {"colomCode":"CI_MANAGE_TO","colomComment":"经营(驻在)期限至","colomType":"DATE","notAllowNull":false,"primary":false} {"colomCode":"CI_POSTAL_CODE","colomComment":"邮政编码","colomType":"VARCHAR2","notAllowNull":false,"primary":false}