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

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));

}

}

}

SetuniqueKeyMap = 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()) {

Mapmap = 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;

}