jdbc获取数据库表、字段接口

我是标题

JDBC获取数据库表和字段工具类
MySql、SQL Server、Sap Hana、Oracle

import com.lydata.handfulcode.common.constants.DataSourceConstants;
import com.lydata.handfulcode.common.exception.BusinessException;
import com.lydata.handfulcode.common.utils.DataBaseConnectionUtil;
import com.lydata.handfulcode.fdi.constant.Constants;
import com.lydata.handfulcode.fdi.model.vo.FieldVO;
import com.lydata.handfulcode.fdi.model.vo.TableVO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author: aQ
 * @program: workspace
 * @description:
 * @company date: 海葵 2021-07-17 17:57
 **/
public class aaa {

    public List<TableVO> getTableListByDataSource() {
        String dataSourceType = "数据库类型";
        String ip = "数据库ip";
        String host = "数据库端口";
        String databaseName = "数据库名称";
        String userName = "用户名";
        String password = "密码";
        String linkType = "Basic";
        Connection conn;
        StringBuilder sql = new StringBuilder();
        if (DataSourceConstants.MYSQL.equalsIgnoreCase(dataSourceType)) {
            conn = DataBaseConnectionUtil.initMysql(ip, host, databaseName, userName, password);
            sql.append(" SELECT t.TABLE_SCHEMA AS DB_NAME,t.TABLE_NAME,t.TABLE_COMMENT")
                    .append(" FROM information_schema.`TABLES` t")
                    .append(" WHERE t.TABLE_TYPE = 'BASE TABLE' AND t.TABLE_SCHEMA = '").append(databaseName).append("'");
        } else if (DataSourceConstants.ORACLE.equalsIgnoreCase(dataSourceType)) {
            conn = DataBaseConnectionUtil.initOracle(linkType, ip, host, databaseName, userName, password);
            sql.append(" SELECT t.TABLE_NAME,'' AS DB_NAME,'' AS TABLE_COMMENT")
                    .append(" FROM user_tables t")
                    .append(" WHERE t.TABLESPACE_NAME = 'USERS'");
        } else if (DataSourceConstants.SQL_SERVER.equalsIgnoreCase(dataSourceType)) {
            conn = DataBaseConnectionUtil.initSQLServer(ip, host, databaseName, userName, password);
            sql.append(" SELECT t.TABLE_CATALOG AS DB_NAME,t.TABLE_NAME,'' AS TABLE_COMMENT")
                    .append(" FROM INFORMATION_SCHEMA.TABLES t ");
        } else if (DataSourceConstants.HANA.equalsIgnoreCase(dataSourceType)) {
            conn = DataBaseConnectionUtil.initHana(ip, host, databaseName, userName, password);
            sql.append(" SELECT t.SCHEMA_NAME AS DB_NAME,t.TABLE_NAME,'' AS TABLE_COMMENT")
                    .append(" FROM M_TABLES t ")
                    .append(" WHERE SCHEMA_NAME = '").append(databaseName).append("'");
        } else {
            throw new BusinessException("类型错误");
        }
        if (null == conn) {
            throw new BusinessException("连接失败");
        }
        try {
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();
            return getTableList(rs);
        } catch (Exception e) {
            throw new BusinessException("获取表失败");
        } finally {
            DataBaseConnectionUtil.closeDB(conn);
        }
    }

    public List<FieldVO> getFieldByTables() {
        String dataSourceType = "数据库类型";
        String ip = "数据库ip";
        String host = "数据库端口";
        String databaseName = "数据库名称";
        String userName = "用户名";
        String password = "密码";
        String linkType = "Basic";
        String tables = "表名";
        Connection conn;
        StringBuilder sql = new StringBuilder();
        if (DataSourceConstants.MYSQL.equalsIgnoreCase(dataSourceType)) {
            conn = DataBaseConnectionUtil.initMysql(ip, host, databaseName, userName, password);
            sql.append(" SELECT t.TABLE_SCHEMA AS DB_NAME,t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,IFNULL(t.CHARACTER_MAXIMUM_LENGTH,t.NUMERIC_PRECISION) AS COLUMN_LENGTH")
                    .append(",t.NUMERIC_SCALE AS COLUMN_DECIMAL,t.COLUMN_COMMENT")
                    .append(" FROM information_schema.`COLUMNS` t")
                    .append(" WHERE t.TABLE_SCHEMA = '").append(databaseName)
                    .append("' AND t.TABLE_NAME IN ('").append(tables).append("')")
                    .append(" ORDER BY t.ORDINAL_POSITION ASC");
        } else if (DataSourceConstants.ORACLE.equalsIgnoreCase(dataSourceType)) {
            conn = DataBaseConnectionUtil.initOracle(linkType, ip, host, databaseName, userName, password);
            sql.append(" SELECT t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,NVL(t.DATA_PRECISION,t.CHAR_COL_DECL_LENGTH) AS COLUMN_LENGTH")
                    .append(",t.DATA_SCALE AS COLUMN_DECIMAL,'' AS DB_NAME,'' AS COLUMN_COMMENT")
                    .append(" FROM user_tab_columns  t")
                    .append(" WHERE t.TABLE_NAME IN ('").append(tables).append("')")
                    .append(" ORDER BY t.COLUMN_ID ASC");
        } else if (DataSourceConstants.SQL_SERVER.equalsIgnoreCase(dataSourceType)) {
            conn = DataBaseConnectionUtil.initSQLServer(ip, host, databaseName, userName, password);
            sql.append(" SELECT t.TABLE_CATALOG AS DB_NAME,t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE,ISNULL(t.CHARACTER_MAXIMUM_LENGTH,t.NUMERIC_PRECISION) AS COLUMN_LENGTH")
                    .append(",t.NUMERIC_SCALE AS COLUMN_DECIMAL,'' AS COLUMN_COMMENT")
                    .append(" FROM INFORMATION_SCHEMA.COLUMNS t")
                    .append(" WHERE t.TABLE_NAME IN ('").append(tables).append("')")
                    .append(" ORDER BY t.ORDINAL_POSITION ASC");
        } else if (DataSourceConstants.HANA.equalsIgnoreCase(dataSourceType)) {
            conn = DataBaseConnectionUtil.initHana(ip, host, databaseName, userName, password);
            sql.append(" SELECT t.SCHEMA_NAME AS DB_NAME,t.TABLE_NAME,t.COLUMN_NAME,t.DATA_TYPE_NAME AS DATA_TYPE")
                    .append(",t.LENGTH AS COLUMN_LENGTH,t.SCALE AS COLUMN_DECIMAL,'' AS COLUMN_COMMENT")
                    .append(" FROM TABLE_COLUMNS t")
                    .append(" WHERE  t.SCHEMA_NAME = '").append(databaseName)
                    .append("' AND t.TABLE_NAME IN ('").append(tables).append("')")
                    .append(" ORDER BY t.POSITION ASC");
        } else {
            throw new BusinessException("类型错误");
        }
        if (null == conn) {
            throw new BusinessException("连接失败");
        }
        try {
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();
            return getFieldList(rs, dataSourceType);
        } catch (Exception e) {
            throw new BusinessException("获取字段失败");
        } finally {
            DataBaseConnectionUtil.closeDB(conn);
        }
    }

    /**
     * 返回表集合
     *
     * @param rs 结果集
     * @return java.util.List<com.lydata.handfulcode.fdi.model.vo.TableVO>
     * @author: aQ
     * @date 2021/5/19 17:44
     **/
    private List<TableVO> getTableList(ResultSet rs) throws SQLException {
        List<TableVO> list = new ArrayList<>();
        if (rs.next()) {
            TableVO vo;
            do {
                vo = new TableVO();
                vo.setDbName(rs.getString(TableVO.DB_NAME));
                vo.setTableName(rs.getString(TableVO.TABLE_NAME));
                vo.setTableComment(rs.getString(TableVO.TABLE_COMMENT));
                list.add(vo);
            } while (rs.next());
        }
        return list;
    }

    // 下面是创建表无需长度的字段过滤
    private static final List<String> noLengthFieldSqlServerList = Arrays.asList("BIGINT", "MONEY", "TEXT", "FLOAT", "INT", "TINYINT");
    private static final List<String> noLengthFieldMySqlList = Arrays.asList("BIGINT", "INT", "BLOB", "TEXT");
    private static final List<String> noLengthFieldHanaList = Arrays.asList("BIGINT", "INTEGER", "DOUBLE", "REAL", "SMALLDECIMAL", "TINYINT", "REAL", "DATE", "TIME", "TIMESTAMP", "BLOB", "CLOB", "TEXT");
//    private static final List<String> noLengthFieldOracleList = Arrays.asList("BIGINT","MONEY","TEXT","FLOAT","INT","TINYINT");

    /**
     * 返回字段集合
     *
     * @param rs 结果集
     * @return java.util.List<com.lydata.handfulcode.fdi.model.vo.FieldVO>
     * @author: aQ
     * @date 2021/5/19 20:13
     **/
    private List<FieldVO> getFieldList(ResultSet rs, String dbType) throws SQLException {
        List<FieldVO> list = new ArrayList<>();
        while (rs.next()) {
            FieldVO vo = new FieldVO();
            vo.setDbName(rs.getString(FieldVO.DB_NAME));
            vo.setTableName(rs.getString(FieldVO.TABLE_NAME));
            vo.setColumnName(rs.getString(FieldVO.COLUMN_NAME));
            vo.setDataType(rs.getString(FieldVO.DATA_TYPE));
            if ((DataSourceConstants.MYSQL.equalsIgnoreCase(dbType)
                    && noLengthFieldMySqlList.contains(vo.getDataType().toUpperCase()))
                    || (DataSourceConstants.SQL_SERVER.equalsIgnoreCase(dbType)
                    && noLengthFieldSqlServerList.contains(vo.getDataType().toUpperCase()))
                    || (DataSourceConstants.HANA.equalsIgnoreCase(dbType)
                    && noLengthFieldHanaList.contains(vo.getDataType().toUpperCase()))) {
                vo.setColumnLength(Constants.INT_0);
                vo.setColumnDecimal(Constants.INT_0);
            } else {
                vo.setColumnLength(rs.getInt(FieldVO.COLUMN_LENGTH));
                vo.setColumnDecimal(rs.getInt(FieldVO.COLUMN_DECIMAL));
            }
            vo.setColumnComment(rs.getString(FieldVO.COLUMN_COMMENT));
            list.add(vo);
        }
        return list;
    }

}

→DataBaseConnectionUtil 类在这里←

FieldVO和TableVO不过是查询出的字段类，无关紧要