https://blog.csdn.net/hhhliushen/article/details/105272439
昨天写的这篇jdbc连接数据库获得所有表 表的所有字段及类型对于mysql来说速度还是可以的,但是对于oracle来说 真的是太慢了。我只好用sql来获取。下面贴上我的代码。
- 获取当前登录用户的所有表
select table_name from all_tables a where a.OWNER = upper('username')
public static Map<String,Integer> getTables(Connection conn,String username) {
Map<String, Integer> map = new HashMap<>();
PreparedStatement stmt;
String createSql = "select table_name from all_tables a where a.OWNER = upper('"+username+"')";
try {
stmt = conn.prepareStatement(createSql);
ResultSet rs = stmt.executeQuery(createSql);
while (rs.next()) {
String name = rs.getString("table_name");
int count = getTablesCount(conn,name);
map.put(name,count);
}
} catch (SQLException e) {
return map;
}finally {
return map;
}
}
- 获取表的行数
SELECT count(1) as cou from tablename
public static Integer getTablesCount(Connection conn,String name) {
PreparedStatement stmt;
String createSql = "SELECT count(1) as cou from "+name;
int count = 0;
try {
stmt = conn.prepareStatement(createSql);
ResultSet rs = stmt.executeQuery(createSql);
while (rs.next()) {
count = rs.getInt("cou");
}
} catch (SQLException e) {
return count;
}finally {
return count;
}
}
- 获取指定表的所有列
select column_name,data_type from all_tab_columns c where c.TABLE_NAME like 'tablename'
public static Map<String,String> getColumn(Connection conn,String tableName) {
Map<String,String> map = new HashMap<>();
PreparedStatement stmt;
String createSql = "select column_name,data_type from all_tab_columns c where c.TABLE_NAME like '"+tableName+"'";
try {
stmt = conn.prepareStatement(createSql);
ResultSet rs = stmt.executeQuery(createSql);
while (rs.next()) {
String name = rs.getString("column_name");
String type = rs.getString("data_type");
map.put(name,type);
}
} catch (SQLException e) {
return map;
}finally {
return map;
}
}
- 获取指定表的主键
select column_name from user_cons_columns u,user_constraints c where u.table_name = 'tablename' and u.constraint_name = c.index_name and c.constraint_type ='P'
public static List<String> getPrimaryKey(Connection conn,String tableName){
List<String> PrimaryKeysist = new ArrayList<String>();
String createSql = "select column_name from user_cons_columns u,user_constraints c where u.table_name = '"+tableName+"' and u.constraint_name = c.index_name and c.constraint_type ='P'";
PreparedStatement stmt;
try {
stmt = conn.prepareStatement(createSql);
ResultSet rs = stmt.executeQuery(createSql);
while (rs.next()) {
String COLUMN_NAME = rs.getString("column_name");
PrimaryKeysist.add(COLUMN_NAME);
}
} catch (SQLException e) {
e.printStackTrace();
}
return PrimaryKeysist;
}