欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

jdbc获取mysql、oracle数据库的表、字段、主键、唯一索引

程序员文章站 2024-03-21 20:59:46
...

根据数据库的四大参数获取指定库中的所有表

//根据数据库的参数获取指定库中的所有表
 public static List<Map<String, String>> getDbTables(String driver, String url, String user, String pwd) {
        Connection conn = null;
        ResultSet rs = null;
        List<Map<String, String>> tables = new ArrayList<>();
        try {
            conn = getConnections(driver, url, user, pwd);
            DatabaseMetaData dbMetaData = conn.getMetaData();
            rs = dbMetaData.getTables(null, getSchema(conn), null, new String[]{"TABLE"});
            while (rs.next()) {
                Map<String, String> map = new HashMap<>(1 << 3);
                //获取数据库的表名
                map.put("tableNam", rs.getString("TABLE_NAME"));
                //获取数据库的表类型
                map.put("tableType", rs.getString("TABLE_TYPE"));
                // 所属数据库
                map.put("tableCat", rs.getString("TABLE_CAT"));
                // 所属用户
                map.put("tableSchem", rs.getString("TABLE_SCHEM"));
                map.put("tableRemarks", rs.getString("REMARKS"));

                tables.add(map);
            }
        } catch (Exception e) {
            LoggerUtil.error(String.valueOf(RetCode.DB_CONN_ERROR), Utils.getStackTrace(e));
            throw new ServiceException(RetCode.DB_CONN_ERROR, "failed to get db tables", null);
        } finally {
            safeClose(rs, conn);
        }
        return tables;
    }

根据数据库的表名获取数据库的字段、主键、唯一索引

注意:oracle区分大小写,所以在获取唯一索引时和oracle有区别。
public static Map<String, List<Map<String, Object>>> getColumnsByTableList(String driver, String url,
                                                                               String user, String pwd, List<String> tableList, int dbType) {
        LoggerUtil.debug("tableList: {}", tableList);
        if (CollectionUtils.isEmpty(tableList)) {
            return null;
        }

        Map<String, List<Map<String, Object>>> tableColumns = new HashMap<>(tableList.size());

        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = getConnections(driver, url, user, pwd);
            DatabaseMetaData dbMetaData = conn.getMetaData();
            String userName = conn.getMetaData().getUserName();
            if ((userName == null) || (userName.length() == 0)) {
                throw new Exception("ORACLE数据库模式不允许为空");
            }


            List<Map<String, Object>> columns = null;
            for (String tableName : tableList) {
                columns = new ArrayList<>();

                // 获取主键字段
                ResultSet primaryKeys = dbMetaData.getPrimaryKeys(null, userName, tableName);
                Set<String> keySet = new HashSet<>(8);

                //根据结果集元数据打印内容
                ResultSetMetaData pkmd = primaryKeys.getMetaData();
                while (primaryKeys.next()) {
                    for (int i = 1; i <= pkmd.getColumnCount(); i++) {
                        //获取主键的名称
                        if ("COLUMN_NAME".equalsIgnoreCase(pkmd.getColumnName(i))) {
                            keySet.add(primaryKeys.getString(i));
                        }
                    }
                }

                Set<String> uniqueKeyMap = new HashSet<>(16);
                //获取唯一索引
                if (Constants.DB_TYPE_ORACLE11 == dbType || Constants.DB_TYPE_ORACLE12 == dbType) {
                	// 由于oracle区分大小写,在写oracle的sql语句时,表名或字段是小写的话,需要添加双引号
                    String tableName1 = "\'" + tableName + "\'";
                    String sql = "select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and i.uniqueness='UNIQUE' and t.table_name = " + tableName1;
                    PreparedStatement pstmt = conn.prepareStatement(sql);
                    ResultSet rsKey = pstmt.executeQuery();

                    while (rsKey.next()) {
                        String indexName = rsKey.getString("COLUMN_NAME");
                        uniqueKeyMap.add(indexName);
                    }
                } else {
                    //mysql数据库
                    ResultSet indexInfo = dbMetaData.getIndexInfo(null, null, tableName, false, false);
                    while (indexInfo.next()) {
                        String indexName = indexInfo.getString("INDEX_NAME");
                        //如果为真则说明索引值不唯一,为假则说明索引值必须唯一。
                        boolean nonUnique = indexInfo.getBoolean("NON_UNIQUE");
                        if (!nonUnique) {
                            uniqueKeyMap.add(indexName);
                        }
                    }
                }
                rs = dbMetaData.getColumns(null, "%", tableName, "%");
                while (rs.next()) {
                    Map<String, Object> map = new HashMap<>(1 << 4);
                    map.put("columnName", rs.getString("COLUMN_NAME"));
                    map.put("columnType", rs.getString("TYPE_NAME"));
                    map.put("datasize", rs.getInt("COLUMN_SIZE"));
                    map.put("remarks", rs.getString("REMARKS"));
                    // 默认值
                    map.put("columnDefault", rs.getString("COLUMN_DEF"));
                    // 是否允许为null, NO:不允许空,YES:可以为空,空: 不确定
                    map.put("isNullable", rs.getString("IS_NULLABLE"));
                    // 是否主键
                    map.put("key", keySet.contains(map.get("columnName")) ? Constants.IS_KEY : Constants.NOT_KEY);
                    map.put("uniqueKey", uniqueKeyMap.contains(rs.getString("COLUMN_NAME")) ? Constants.IS_UNIQUE_KEY : Constants.NOT_UNIQUE_KEY);
                    columns.add(map);
                }

                if (!columns.isEmpty()) {
                    tableColumns.put(tableName, columns);
                }
            }
        } catch (Exception e) {
            LoggerUtil.warn("get columns by table list happened exception, detail:{}", Utils.getStackTrace(e));
            throw new ServiceException(RetCode.DB_CONN_ERROR, "failed to get columns by table list");
        } finally {
            safeClose(rs, conn);
        }
        return tableColumns;
    }