Oracle 查询数据库所有表

最新推荐文章于 2024-05-06 21:10:29 发布

若湜少年青涩如初

最新推荐文章于 2024-05-06 21:10:29 发布

阅读量1.3k

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_34004756/article/details/120286135

版权

ArrayList<String> alPass = new ArrayList<>();

try {

// conn = SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();
Properties props = new Properties();
props.put("remarksReporting", "true");
props.put("user", "igadmin");
props.put("password", "igadmin");
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:LNNX", props);
//Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:LNNX?useInformationSchema=true","igadmin","igadmin");

DatabaseMetaData metadata = conn.getMetaData();
System.out.println("数据库已知的用户: " + metadata.getUserName());
System.out.println("数据库的系统函数的逗号分隔列表: " + metadata.getSystemFunctions());
System.out.println("数据库的时间和日期函数的逗号分隔列表: " + metadata.getTimeDateFunctions());
System.out.println("数据库的字符串函数的逗号分隔列表: " + metadata.getStringFunctions());
System.out.println("数据库供应商用于 'schema' 的首选术语: " + metadata.getSchemaTerm());
System.out.println("数据库URL: " + metadata.getURL());
System.out.println("是否允许只读:" + metadata.isReadOnly());
System.out.println("数据库的产品名称:" + metadata.getDatabaseProductName());
System.out.println("数据库的版本:" + metadata.getDatabaseProductVersion());
System.out.println("驱动程序的名称:" + metadata.getDriverName());
System.out.println("驱动程序的版本:" + metadata.getDriverVersion());

System.out.println();
System.out.println("数据库中使用的表类型");
ResultSet rs = metadata.getTableTypes();
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();

System.out.println();
/**
* 获取指定的数据库的所有表的类型,getTables()的第一个参数就是数据库名因为与MySQL连接时没有指定,这里加上,剩下的参数就可以为null了
* 第二个参数是模式名称的模式,但是输出也是什么都没有。谁知道告诉我一声
*/

SingleTableBL singleTableBL = (SingleTableBL) WebApplicationSupport.getBean("singleTableBL");
SingleTableAttributeBL singleTableAttributeBL = (SingleTableAttributeBL) WebApplicationSupport.getBean("singleTableAttributeBL");
List<SingleTableInfo> singleTableInfoList = singleTableBL.searchSingleTable();
for (int i = 0; i < singleTableInfoList.size(); i++) {
SingleTableInfo singleTableInfo = singleTableInfoList.get(i);
alPass.add(singleTableInfo.getStname());
}
System.out.println("获取指定的数据库的所有表的类型");
ResultSet rs1 = metadata.getTables("IG", "IGADMIN", null, null);
while (rs1.next()) {
System.out.println("Table name: " + rs1.getString("Table_NAME"));
System.out.println("Table type: " + rs1.getString("TABLE_TYPE"));
System.out.println("Table schema: " + rs1.getString("TABLE_SCHEM"));
System.out.println("Table catalog: " + rs1.getString("TABLE_CAT"));
System.out.println("Table catalog: " + rs1.getString("REMARKS"));
if (alPass.contains(rs1.getString(3))) {

} else {
if (rs1.getString(4).equals("TABLE")) {

if ("FRONT".equals(rs1.getString(3))) {
System.out.println("123");
}

String tablename = rs1.getString(3);
SingleTableTB singleTableTB = new SingleTableTB();
singleTableTB.setStname(tablename);
if (rs1.getString("REMARKS") != null && !rs1.getString("REMARKS").equals("null")) {
singleTableTB.setStdesc(rs1.getString("REMARKS"));
}
singleTableTB.setInitcreatetime(IGStringUtils.getCurrentDateTime());
singleTableTB.setLastupdatetime(IGStringUtils.getCurrentDateTime());
SingleTableInfo singleTableInfo = singleTableBL.registSingleTable(singleTableTB);

List<String> keyList = new ArrayList<String>();
ResultSet rs3 = metadata.getPrimaryKeys("stock", null, tablename);
while (rs3.next()) {
keyList.add(rs3.getString(4));
}
rs3.close();

PreparedStatement ps = conn.prepareStatement("select * from " + tablename);
ResultSet rs2 = ps.executeQuery();
ResultSetMetaData rsme = rs2.getMetaData();

int columnCount = rsme.getColumnCount();
//System.out.println("ResultSet对象中的列数" + columnCount);
System.out.println("列定义 | 自动编号 | 是否为空 | 主键 ");
for (int i = 1; i < columnCount; i++) {
System.out.print(rsme.getColumnName(i) + " " + rsme.getColumnTypeName(i) + "("
+ rsme.getPrecision(i) + ")");
System.out.print("| " + rsme.isAutoIncrement(i));
System.out.print("| " + rsme.isNullable(i));
System.out.println("| " + keyList.contains(rsme.getColumnName(i)));

SingleTableAttributeTB singleTableAttributeTB = new SingleTableAttributeTB();
singleTableAttributeTB.setStid(singleTableInfo.getStid());
singleTableAttributeTB.setStaname(rsme.getColumnName(i));
singleTableAttributeTB.setStadesc(rsme.getColumnName(i));
if (rsme.getColumnTypeName(i).contains("NUMBER")) {
singleTableAttributeTB.setStatype("1");
} else {
singleTableAttributeTB.setStatype("0");
}
singleTableAttributeTB.setStakey(rsme.getColumnName(i));
singleTableAttributeTB.setStalength(String.valueOf(rsme.getPrecision(i)));
if (rsme.isNullable(i) == 0) {
singleTableAttributeTB.setStaempty("0");
} else {
singleTableAttributeTB.setStaempty("1");
}
if (keyList.contains(rsme.getColumnName(i))) {
singleTableAttributeTB.setStaprimkey("1");
} else {
singleTableAttributeTB.setStaprimkey("0");
}
singleTableAttributeTB.setInitcreatetime(IGStringUtils.getCurrentDateTime());
singleTableAttributeTB.setLastupdatetime(IGStringUtils.getCurrentDateTime());
singleTableAttributeBL.registSingleTableAttribute(singleTableAttributeTB);

}
System.out.println();
rs2.close();
ps.close();
alPass.add(rs1.getString(3));
}
}
System.out.println(alPass.size());
}
rs1.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("================================================================");