JdbcUtil.rar_oracle_元数据

package com.util; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import oracle.jdbc.driver.OracleConnection; /** * @Description: JDBC操作元数据示例-- DatabaseMetaData接口 * @CreateTime: 2014-1-19 下午9:46:44 * @author: chenzw * @version V1.0 */ public class JdbcUtil { //获得驱动 private static String DRIVER = "oracle.jdbc.driver.OracleDriver"; //获得url private static String URL = "jdbc:oracle:thin:@localhost:1521:Orcl"; //获得连接数据库的用户名 private static String USER = "system"; //获得连接数据库的密码 private static String PASS = "123"; static { try { //初始化JDBC驱动并让驱动加载到jvm中 Class.forName(DRIVER); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection(){ Connection conn = null; try { //连接数据库 /* * 设置可获取REMARK备注信息 Properties props =new Properties(); props.put("remarksReporting","true"); props.put("user", USER); props.put("password", PASS); conn =DriverManager.getConnection(URL,props);*/ conn = DriverManager.getConnection(URL,USER,PASS); conn.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } return conn; } //关闭连接 public static void close(Object o){ if (o == null){ return; } if (o instanceof ResultSet){ try { ((ResultSet)o).close(); } catch (SQLException e) { e.printStackTrace(); } } else if(o instanceof Statement){ try { ((Statement)o).close(); } catch (SQLException e) { e.printStackTrace(); } } else if (o instanceof Connection){ Connection c = (Connection)o; try { if (!c.isClosed()){ c.close(); } } catch (SQLException e) { e.printStackTrace(); } } } public static void close(ResultSet rs, Statement stmt, Connection conn){ close(rs); close(stmt); close(conn); } public static void close(ResultSet rs, Connection conn){ close(rs); close(conn); } /** * @Description: 获取数据库相关信息 * @author: chenzw * @CreateTime: 2014-1-27 下午5:09:12 * @throws */ public static void getDataBaseInfo() { Connection conn = getConnection(); ResultSet rs = null; try{ DatabaseMetaData dbmd = conn.getMetaData(); System.out.println("数据库已知的用户: "+ dbmd.getUserName()); System.out.println("数据库的系统函数的逗号分隔列表: "+ dbmd.getSystemFunctions()); System.out.println("数据库的时间和日期函数的逗号分隔列表: "+ dbmd.getTimeDateFunctions()); System.out.println("数据库的字符串函数的逗号分隔列表: "+ dbmd.getStringFunctions()); System.out.println("数据库供应商用于 'schema' 的首选术语: "+ dbmd.getSchemaTerm()); System.out.println("数据库URL: " + dbmd.getURL()); System.out.println("是否允许只读:" + dbmd.isReadOnly()); System.out.println("数据库的产品名称:" + dbmd.getDatabaseProductName()); System.out.println("数据库的版本:" + dbmd.getDatabaseProductVersion()); System.out.println("驱动程序的名称:" + dbmd.getDriverName()); System.out.println("驱动程序的版本:" + dbmd.getDriverVersion()); System.out.println("数据库中使用的表类型"); rs = dbmd.getTableTypes(); while (rs.next()) { System.out.println(rs.getString("TABLE_TYPE")); } }catch (SQLException e){ e.printStackTrace(); } finally{ JdbcUtil.close(rs,conn); } } /** * @Description:获得数据库中所有Schemas(对应于oracle中的Tablespace) * @author: chenzw * @CreateTime: 2014-1-27 下午5:10:35 * @throws */ public static void getSchemasInfo(){ Connection conn = getConnection(); ResultSet rs = null; try{ DatabaseMetaData dbmd = conn.getMetaData(); rs = dbmd.getSchemas(); while (rs.next()){ String tableSchem = rs.getString("TABLE_SCHEM"); System.out.println(tableSchem); } } catch (SQLException e){ e.printStackTrace(); } finally{ JdbcUtil.close(rs,conn); } } /** * @Description: 获取数据库中所有的表信息 * @author: chenzw * @CreateTime: 2014-1-27 下午5:08:28 * @throws */ public static void getTablesList() { Connection conn = getConnection(); ResultSet rs = null; try { /** * 设置连接属性,使得可获取到表的REMARK(备注) */ ((OracleConnection)conn).setRemarksReporting(true); DatabaseMetaData dbmd = conn.getMetaData(); String[] types = { "TABLE" }; rs = dbmd.getTables(null, null, "%", types); while (rs.next()) { String tableName = rs.getString("TABLE_NAME"); //表名 String tableType = rs.getString("TABLE_TYPE"); //表类型 String remarks = rs.getString("REMARKS"); //表备注 System.out.println(tableName + " - " + tableType + " - " + remarks); } } catch (SQLException e) { e.printStackTrace(); } finally{ JdbcUtil.close(rs,conn); } } /** * @Description: 获取某表信息 * @author: chenzw * @CreateTime: 2014-1-27 下午3:26:30 * @throws */ public static void getTablesInfo(){ Connection conn = getConnection(); ResultSet rs = null; try { /** * 设置连接属性,使得可获取到表的REMARK(备注) */ ((OracleConnection)conn).setRemarksReporting(true); DatabaseMetaData dbmd = conn.getMetaData(); /** * 获取给定类别中使用的表的描述。 * 方法原型:ResultSet getTables(String catalog,String schemaPattern,String tableNamePattern,String[] types); * catalog - 表所在的类别名称;""表示获取没有类别的列,null表示获取所有类别的列。 * schema - 表所在的模式名称(oracle中对应于Tablespace);""表示获取没有模式的列,null标识获取所有模式的列; 可包含单字符通配符("_"),或多字符通配符("%"); * tableNamePattern - 表名称;可包含单字符通配符("_"),或多字符通配符("%"); * types - 表类型数组; "TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM";null表示包含所有的表类型;可包含单字符通配符("_"),或多字符通配符("%"); */ rs = dbmd.getTables(null, null, "MANTIS_CODE", new String[]{"TABLE","VIEW"}); while(rs.next()){ String tableCat = rs.getString("TABLE_CAT"); //表类别(可为null) String tableSchemaName = rs.getString("TABLE_SCHEM");//表模式（可能为空）,在oracle中获取的是命名空间,其它数据库未知 String tableName = rs.getString("TABLE_NAME"); //表名 String tableType = rs.g