从数据连接获取表信息

从数据连接获取表信息

适配所有的数据库，如：oracle、mysql等。里面用到的异常请删除即可。

需要引入hutool jar 包

      <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.5.5</version>
            </dependency>

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

import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.dingxuan.sky.data.database.entity.ColumnInfo;
import com.dingxuan.sky.data.database.entity.TableInfo;
import com.dingxuan.sky.data.enums.SkyDataErrorCodeEnum;
import com.dingxuan.sky.data.exception.SkyDataRuntimeException;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;

public class TableInfoFactory extends AbstractDatabase {

    private static final Logger logger = LoggerFactory.getLogger(TableInfoFactory.class);

    protected TableInfoFactory(SqlSessionTemplate sqlSessionTemplate){
        super(sqlSessionTemplate);
    }

    public static TableInfoFactory instance() {
        return TableInfoFactory.TableInfoFactoryEnum.SINGLETON.getInstance();
    }

    private enum TableInfoFactoryEnum {

                                      /**
                                       * 单例
                                       */
                                      SINGLETON;

        /**
         *
         */
        private final TableInfoFactory instances;

        /**
         * 构造器
         */
        TableInfoFactoryEnum(){
            instances = new TableInfoFactory(SpringUtil.getBean(SqlSessionTemplate.class));
        }

        private TableInfoFactory getInstance() {
            return instances;
        }
    }

    public List<TableInfo> getTables() {
        return getTables(null);
    }

    /**
     * 获取当前链接的 catalog、schema对应的 表信息
     * 
     * @return 所有表的集合
     */
    public List<TableInfo> getTables(String schema) {
        List<TableInfo> tableInfos = new ArrayList<>();
        Connection connection;
        SqlSession sqlSession = null;
        try {
            sqlSession = super.sqlSession();
            connection = sqlSession.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = connection.getCatalog();
            if (StringUtils.isBlank(schema)) {
                schema = connection.getSchema();
                schema = StringUtils.isNotBlank(schema) ? schema : catalog;
            }
            ResultSet rs = metaData.getTables(catalog, schema, null, new String[] { "TABLE" });
            while (rs.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableCat(catalog);
                tableInfo.setTableName(rs.getString("TABLE_NAME"));
                tableInfo.setTableType(rs.getString("TABLE_TYPE"));
                tableInfo.setTableSchema(schema);
                tableInfo.setRemarks(rs.getString("REMARKS"));
                tableInfo.setTableNameCamel(StrUtil.toCamelCase(rs.getString("TABLE_NAME")));
                tableInfos.add(tableInfo);
            }
        } catch (Exception e) {
            logger.error("从METADATA获取所有表信息失败", e);
        } finally {
            super.closeSqlSession(sqlSession);
        }
        return tableInfos;
    }

    public TableInfo getTable(String tableName) {
        return getTable(tableName, null);
    }

    public TableInfo getTable(String tableName, String schema) {
        TableInfo tableInfo = new TableInfo();
        Connection connection;
        SqlSession sqlSession = null;
        try {
            sqlSession = super.sqlSession();
            connection = sqlSession.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = connection.getCatalog();
            if (StringUtils.isBlank(schema)) {
                schema = connection.getSchema();
                schema = StringUtils.isNotBlank(schema) ? schema : catalog;
            }
            ResultSet rs = metaData.getTables(catalog, schema, tableName, new String[] { "TABLE" });
            while (rs.next()) {
                tableInfo.setTableCat(catalog);
                tableInfo.setTableName(rs.getString("TABLE_NAME"));
                tableInfo.setTableType(rs.getString("TABLE_TYPE"));
                tableInfo.setTableSchema(schema);
                tableInfo.setRemarks(rs.getString("REMARKS"));
                tableInfo.setTableNameCamel(StrUtil.toCamelCase(rs.getString("TABLE_NAME")));
            }
        } catch (Exception e) {
            logger.error("从METADATA获取所有表信息失败", e);
        } finally {
            super.closeSqlSession(sqlSession);
        }
        return tableInfo;
    }

    public List<ColumnInfo> getColumns(String tableName) {
        return getColumns(tableName, null);
    }

    /**
     * 获取当前链接的 catalog、schema对应的 表的列的信息
     *
     * @return 所有表的集合
     */
    public List<ColumnInfo> getColumns(String tableName, String schema) {
        List<ColumnInfo> columnInfos = new ArrayList<>();
        SqlSession sqlSession = null;
        try {
            sqlSession = super.sqlSession();
            Connection connection = sqlSession.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = connection.getCatalog();
            if (StringUtils.isBlank(schema)) {
                schema = connection.getSchema();
                schema = StringUtils.isNotBlank(schema) ? schema : catalog;
            }
            ResultSet rs = metaData.getColumns(catalog, schema, tableName, null);
            while (rs.next()) {
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.setColumnName(rs.getString("COLUMN_NAME"));
                columnInfo.setDataType(rs.getString("DATA_TYPE"));
                columnInfo.setTypeName(rs.getString("TYPE_NAME"));
                columnInfo.setColumnSize(rs.getString("COLUMN_SIZE"));
                columnInfo.setColumnNameCamel(StrUtil.toCamelCase(rs.getString("COLUMN_NAME")));
                columnInfo.setIsNullable(rs.getString("IS_NULLABLE"));
                columnInfo.setRemarks(rs.getString("REMARKS"));
                columnInfo.setCharOctetLength(rs.getString("CHAR_OCTET_LENGTH"));
                columnInfos.add(columnInfo);
            }
        } catch (Exception e) {
            logger.error("从METADATA获取表的所有列信息失败", e);
  
        } finally {
            super.closeSqlSession(sqlSession);
        }
        return columnInfos;
    }

    public ColumnInfo getColumn(String tableName, String columnName) {
        return getColumn(tableName, columnName, null);
    }

    public ColumnInfo getColumn(String tableName, String columnName, String schema) {
        ColumnInfo columnInfo = new ColumnInfo();
        SqlSession sqlSession = null;
        try {
            sqlSession = super.sqlSession();
            Connection connection = sqlSession.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = connection.getCatalog();
            if (StringUtils.isBlank(schema)) {
                schema = connection.getSchema();
                schema = StringUtils.isNotBlank(schema) ? schema : catalog;
            }
            ResultSet rs = metaData.getColumns(catalog, schema, tableName, columnName);
            while (rs.next()) {
                columnInfo.setColumnName(rs.getString("COLUMN_NAME"));
                columnInfo.setDataType(rs.getString("DATA_TYPE"));
                columnInfo.setTypeName(rs.getString("TYPE_NAME"));
                columnInfo.setColumnSize(rs.getString("COLUMN_SIZE"));
                columnInfo.setColumnNameCamel(StrUtil.toCamelCase(rs.getString("COLUMN_NAME")));
                columnInfo.setIsNullable(rs.getString("IS_NULLABLE"));
                columnInfo.setRemarks(rs.getString("REMARKS"));
                columnInfo.setCharOctetLength(rs.getString("CHAR_OCTET_LENGTH"));
            }
        } catch (Exception e) {
            logger.error("从METADATA获取表的所有列信息失败", e);

        } finally {
            super.closeSqlSession(sqlSession);
        }
        return columnInfo;
    }

    public List<TableInfo> getTableColumns() {
        return getTableColumns(null);
    }

    /**
     * 获取当前链接的 catalog、schema对应的 所有表的列的信息
     *
     * @return 所有表的集合
     */
    public List<TableInfo> getTableColumns(String schema) {
        List<TableInfo> tableInfos = new ArrayList<>();
        SqlSession sqlSession = null;
        try {
            sqlSession = super.sqlSession();
            Connection connection = sqlSession.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = connection.getCatalog();
            if (StringUtils.isBlank(schema)) {
                schema = connection.getSchema();
                schema = StringUtils.isNotBlank(schema) ? schema : catalog;
            }
            ResultSet rsTable = metaData.getTables(catalog, schema, null, new String[] { "TABLE" });
            while (rsTable.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableCat(catalog);
                tableInfo.setTableName(rsTable.getString("TABLE_NAME"));
                tableInfo.setTableType(rsTable.getString("TABLE_TYPE"));
                tableInfo.setTableSchema(schema);
                tableInfo.setRemarks(rsTable.getString("REMARKS"));
                tableInfo.setTableNameCamel(StrUtil.toCamelCase(rsTable.getString("TABLE_NAME")));
                tableInfos.add(tableInfo);

            }
            if (CollectionUtil.isNotEmpty(tableInfos)) {
                for (TableInfo tableInfo : tableInfos) {
                    ResultSet rsColumn = metaData.getColumns(catalog, schema, tableInfo.getTableName(), null);
                    List<ColumnInfo> columnInfos = new ArrayList<>();
                    while (rsColumn.next()) {
                        ColumnInfo columnInfo = new ColumnInfo();
                        columnInfo.setColumnName(rsColumn.getString("COLUMN_NAME"));
                        columnInfo.setDataType(rsColumn.getString("DATA_TYPE"));
                        columnInfo.setTypeName(rsColumn.getString("TYPE_NAME"));
                        columnInfo.setColumnSize(rsColumn.getString("COLUMN_SIZE"));
                        columnInfo.setColumnNameCamel(StrUtil.toCamelCase(rsColumn.getString("COLUMN_NAME")));
                        columnInfo.setIsNullable(rsColumn.getString("IS_NULLABLE"));
                        columnInfo.setRemarks(rsColumn.getString("REMARKS"));
                        columnInfo.setCharOctetLength(rsColumn.getString("CHAR_OCTET_LENGTH"));
                        columnInfos.add(columnInfo);
                    }
                    tableInfo.setColumnInfoList(columnInfos);
                }
            }
        } catch (Exception e) {
            logger.error("从METADATA获取表的所有列信息失败", e);
        } finally {
            super.closeSqlSession(sqlSession);
        }
        return tableInfos;
    }

    public TableInfo getTableColumn(String tableName) {
        return getTableColumn(tableName, null);
    }

    public TableInfo getTableColumn(String tableName, String schema) {
        TableInfo tableInfo = new TableInfo();
        SqlSession sqlSession = null;
        try {
            sqlSession = super.sqlSession();
            Connection connection = sqlSession.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            String catalog = connection.getCatalog();
            if (StringUtils.isBlank(schema)) {
                schema = connection.getSchema();
                schema = StringUtils.isNotBlank(schema) ? schema : catalog;
            }
            ResultSet rsTable = metaData.getTables(catalog, schema, tableName, new String[] { "TABLE" });
            while (rsTable.next()) {
                tableInfo.setTableCat(catalog);
                tableInfo.setTableName(rsTable.getString("TABLE_NAME"));
                tableInfo.setTableType(rsTable.getString("TABLE_TYPE"));
                tableInfo.setTableSchema(schema);
                tableInfo.setRemarks(rsTable.getString("REMARKS"));
                tableInfo.setTableNameCamel(StrUtil.toCamelCase(rsTable.getString("TABLE_NAME")));
                ResultSet rsColumn = metaData.getColumns(catalog, schema, tableInfo.getTableName(), null);
                List<ColumnInfo> columnInfos = new ArrayList<>();
                while (rsColumn.next()) {
                    ColumnInfo columnInfo = new ColumnInfo();
                    columnInfo.setColumnName(rsColumn.getString("COLUMN_NAME"));
                    columnInfo.setDataType(rsColumn.getString("DATA_TYPE"));
                    columnInfo.setTypeName(rsColumn.getString("TYPE_NAME"));
                    columnInfo.setColumnSize(rsColumn.getString("COLUMN_SIZE"));
                    columnInfo.setColumnNameCamel(StrUtil.toCamelCase(rsColumn.getString("COLUMN_NAME")));
                    columnInfo.setIsNullable(rsColumn.getString("IS_NULLABLE"));
                    columnInfo.setRemarks(rsColumn.getString("REMARKS"));
                    columnInfo.setCharOctetLength(rsColumn.getString("CHAR_OCTET_LENGTH"));
                    columnInfos.add(columnInfo);
                }
                tableInfo.setColumnInfoList(columnInfos);
            }
        } catch (Exception e) {
            logger.error("从METADATA获取表的所有列信息失败", e);
        } finally {
            super.closeSqlSession(sqlSession);
        }
        return tableInfo;
    }

import java.sql.PreparedStatement;
import java.util.Properties;

import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.SqlSessionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.druid.pool.DruidDataSource;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.extra.spring.SpringUtil;

/**
 * @author xin
 * @version Created by xin on 2021/8/23 2:10 下午
 */
public abstract class AbstractDatabase {

    private static final Logger  logger = LoggerFactory.getLogger(AbstractDatabase.class);
    protected SqlSessionTemplate sqlSessionTemplate;

    protected AbstractDatabase(SqlSessionTemplate sqlSessionTemplate){
        this.sqlSessionTemplate = sqlSessionTemplate;
    }

    public boolean execute(String sql) {
        SqlSession sqlSession = null;
        try {
            sqlSession = sqlSession();
            PreparedStatement pst = sqlSession.getConnection().prepareStatement(sql);
            pst.execute();
        } catch (Exception e) {
            logger.error("执行sql失败{}", sql, e);
            return false;
        } finally {
            if (ObjectUtil.isNotNull(sqlSession)) {
                closeSqlSession(sqlSession);
            }
        }
        return true;
    }

    protected SqlSession sqlSession() {
        Properties properties = new Properties();
        properties.setProperty("remarks", "true");
        properties.setProperty("useInformationSchema", "true");
        DruidDataSource druidDataSource = SpringUtil.getBean(DruidDataSource.class);
        if (ObjectUtil.isNotEmpty(druidDataSource)) {
            druidDataSource.setConnectProperties(properties);
        }
        return SqlSessionUtils.getSqlSession(this.sqlSessionTemplate.getSqlSessionFactory(),
            this.sqlSessionTemplate.getExecutorType(),
            this.sqlSessionTemplate.getPersistenceExceptionTranslator());
    }

    /**
     * 关闭sqlSession
     *
     * @param session SqlSession
     */
    protected void closeSqlSession(SqlSession session) {
        SqlSessionUtils.closeSqlSession(session, this.sqlSessionTemplate.getSqlSessionFactory());
    }
}