获取数据库表结构

开发中或许会用到获取数据库表结构，这里我写个获取mysql表结构的例子

在JDK1.8的环境中，用到的包如下

<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.5</version>
</dependency>

<!-- log4j -->

<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${org.slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${org.slf4j.version}</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>

jdbc配置文件jdbc.properties

driver=com.mysql.cj.jdbc.Driverurl=jdbc:mysql://localhost:3306/ai_say?serverTimezone=UTC&useTimezone=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&useUnicode=true&rewriteBatchedStatements=TRUEuser=rootpassword=123456remarks=trueuseInformationSchema=true

PropertiesUtil.java用来加载配置文件

package com.ai.generator.util;import java.io.IOException;import java.io.InputStream;import java.net.URL;import java.net.URLConnection;import java.util.Enumeration;import java.util.Properties;public class PropertiesUtil {Properties properties;public PropertiesUtil(Properties properties) {this.properties = properties;}public Properties getProperties() {return properties;}public String getProperty(String key) {return getProperties().getProperty(key);}public static Properties loadProperties(String resourceName)throws IOException {Properties properties = new Properties();Enumeration<URL> urls = PropertiesUtil.class.getClassLoader().getResources(resourceName);while (urls.hasMoreElements()) {URL url = (URL) urls.nextElement();InputStream input = null;try {URLConnection con = url.openConnection();con.setUseCaches(false);input = con.getInputStream();properties.load(input);} finally {if (input != null) {input.close();}}}return properties;}}

SqlTable.java、SqlColumn.java、SqlPrimaryKey.java分别是表、列、主键实体类

package com.ai.generator.entity.sql;/** * 数据库表 */public class SqlTable {/** * 备注 */private String sqlTableName;/** * 备注 */private String sqlTableRemarks;public String getSqlTableName() {return sqlTableName;}public void setSqlTableName(String sqlTableName) {this.sqlTableName = sqlTableName;}public String getSqlTableRemarks() {return sqlTableRemarks;}public void setSqlTableRemarks(String sqlTableRemarks) {this.sqlTableRemarks = sqlTableRemarks;}}

package com.ai.generator.entity.sql;/** * 数据库表 */public class SqlColumn {/** * 列名 */private String columnName;/** * 对应的java.sql.Types类型 */private int dataType;/** * java.sql.Types类型 */private String typeName; ///** * 列大小 */private int columnSize;/** * 小数位数 */private int decimalDigits;/** * 基数（通常是10或2） */private int numPrecRadix;/** * 是否允许为null */private int nullable;/** * 列描述 */private String remarks;/** * 默认值 */private String columnDef;/** * SQL日期时间分 */private int sqlDatetimeSub;/** * char类型的列中的最大字节数 */private int charOctetLength;/** * 表中列的索引（从1开始） */private int ordinalPosition;/** * ISO规则用来确定某一列的为空性。 是---如果该参数可以包括空值; 无---如果参数不能包含空值 空---如果参数为空性是未知的 */private String isNullable;/** * 指示此列是否自动增加 YES --- 如果该列自动增加 NO --- 如果该列不自动增加 空字符串 --- 如果不能确定该列是否是自动增加参数 */private String isAutoincrement;public String getColumnName() {return columnName;}public void setColumnName(String columnName) {this.columnName = columnName;}public int getDataType() {return dataType;}public void setDataType(int dataType) {this.dataType = dataType;}public String getTypeName() {return typeName;}public void setTypeName(String typeName) {this.typeName = typeName;}public int getColumnSize() {return columnSize;}public void setColumnSize(int columnSize) {this.columnSize = columnSize;}public int getDecimalDigits() {return decimalDigits;}public void setDecimalDigits(int decimalDigits) {this.decimalDigits = decimalDigits;}public int getNumPrecRadix() {return numPrecRadix;}public void setNumPrecRadix(int numPrecRadix) {this.numPrecRadix = numPrecRadix;}public int getNullable() {return nullable;}public void setNullable(int nullable) {this.nullable = nullable;}public String getRemarks() {return remarks;}public void setRemarks(String remarks) {this.remarks = remarks;}public String getColumnDef() {return columnDef;}public void setColumnDef(String columnDef) {this.columnDef = columnDef;}public int getSqlDatetimeSub() {return sqlDatetimeSub;}public void setSqlDatetimeSub(int sqlDatetimeSub) {this.sqlDatetimeSub = sqlDatetimeSub;}public int getCharOctetLength() {return charOctetLength;}public void setCharOctetLength(int charOctetLength) {this.charOctetLength = charOctetLength;}public int getOrdinalPosition() {return ordinalPosition;}public void setOrdinalPosition(int ordinalPosition) {this.ordinalPosition = ordinalPosition;}public String getIsNullable() {return isNullable;}public void setIsNullable(String isNullable) {this.isNullable = isNullable;}public String getIsAutoincrement() {return isAutoincrement;}public void setIsAutoincrement(String isAutoincrement) {this.isAutoincrement = isAutoincrement;}}

package com.ai.generator.entity.sql;public class SqlPrimaryKey {/** * 列名 */private String columnName;/** * 多列主键中列的序列号 */private short keySeq;/** * 主键的名称 */private String pkName;public String getColumnName() {return columnName;}public void setColumnName(String columnName) {this.columnName = columnName;}public short getKeySeq() {return keySeq;}public void setKeySeq(short keySeq) {this.keySeq = keySeq;}public String getPkName() {return pkName;}public void setPkName(String pkName) {this.pkName = pkName;}}

SqlManage.java封装了一些对数据库的操作

package com.ai.generator.service.manage;import java.io.IOException;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Properties;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.ai.generator.entity.sql.SqlColumn;import com.ai.generator.entity.sql.SqlPrimaryKey;import com.ai.generator.entity.sql.SqlTable;import com.ai.generator.util.PropertiesUtil;public class SqlManage {private static Logger logger = LoggerFactory.getLogger(SqlManage.class);private Connection connection;private DatabaseMetaData databaseMetaData;public SqlManage(ConfigManage configManage) {if (connection == null) {synchronized (this) {if (connection == null) {initialize(configManage.getJdbcConfig());}}}}/** * 创建JDBC */private void initialize(String jdbcConfigPath) {Properties properties = null;try {properties = PropertiesUtil.loadProperties(jdbcConfigPath);} catch (IOException e) {logger.error("加载jdbc配置文件失败");}if (properties == null || properties.isEmpty()) {logger.error("未加载到jdbc配置文件");}try {Class.forName(properties.getProperty("driver"));if (connection == null || connection.isClosed()) {connection = DriverManager.getConnection(properties.getProperty("url"), properties);databaseMetaData = connection.getMetaData();}} catch (SQLException | ClassNotFoundException e) {logger.error("连接数据库失败,请检查驱动或jdbc配置后在试");}}/** * 获取所有表 */public List<SqlTable> getTables() {logger.debug("获取所有表");List<SqlTable> list = new ArrayList<SqlTable>();try {ResultSet result = databaseMetaData.getTables(connection.getCatalog(), null, "%", null);while (result.next()) {SqlTable table = new SqlTable();table.setSqlTableName(result.getString("TABLE_NAME"));table.setSqlTableRemarks(result.getString("REMARKS"));list.add(table);}result.close();} catch (SQLException e) {e.printStackTrace();}return list;}/** * 获取表主键 */public SqlPrimaryKey getPrimaryKeys(SqlTable table) throws Exception {logger.debug("获取表主键");List<SqlPrimaryKey> list = new ArrayList<SqlPrimaryKey>();try {ResultSet result = databaseMetaData.getPrimaryKeys(connection.getCatalog(), null, table.getSqlTableName());while (result.next()) {SqlPrimaryKey primaryKey = new SqlPrimaryKey();primaryKey.setColumnName(result.getString("COLUMN_NAME"));primaryKey.setKeySeq(result.getShort("KEY_SEQ"));primaryKey.setPkName(result.getString("PK_NAME"));list.add(primaryKey);}result.close();} catch (SQLException e) {e.printStackTrace();}if (list == null || list.size() > 1) {logger.error("表没有主键或者复合主键,请检查表主键");throw new Exception("表没有主键或者复合主键,请检查表主键");}return list == null || list.isEmpty() ? null : list.get(0);}/** * 获取表的所有列 */public List<SqlColumn> getColumns(SqlTable table) {logger.debug("获取表的所有列");List<SqlColumn> list = new ArrayList<SqlColumn>();try {ResultSet rs = databaseMetaData.getColumns(connection.getCatalog(),null, table.getSqlTableName(), "%");while (rs.next()) {SqlColumn column = new SqlColumn();column.setColumnName(rs.getString("COLUMN_NAME"));column.setDataType(rs.getInt("DATA_TYPE"));column.setTypeName(rs.getString("TYPE_NAME"));column.setColumnSize(rs.getInt("COLUMN_SIZE"));column.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));column.setNumPrecRadix(rs.getInt("NUM_PREC_RADIX"));column.setNullable(rs.getInt("NULLABLE"));column.setRemarks(rs.getString("REMARKS"));column.setColumnDef(rs.getString("COLUMN_DEF"));column.setSqlDatetimeSub(rs.getInt("SQL_DATETIME_SUB"));column.setCharOctetLength(rs.getInt("CHAR_OCTET_LENGTH"));column.setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));column.setIsNullable(rs.getString("IS_NULLABLE"));column.setIsAutoincrement(rs.getString("IS_AUTOINCREMENT"));list.add(column);}rs.close();} catch (SQLException e) {e.printStackTrace();}return list;}}

实体类上的注释都很清楚。