getdatabaseinfo.java

在oracle总返回数据库表、表结构

package com.sunyard.database;

import java.sql.*;
import java.util.*;
//import com.sunyard.cab.view.columnInfoActionForm;

public class Getdatabaseinfo {
  Connection con = null;
  ResultSet rs = null;
  Statement st = null;
  //获得sql server连接
  public Connection getSQLServerConnetion() {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String url = "jdbc:odbc:JST";

    try {
      Class.forName(driver);
      con = DriverManager.getConnection(url, "sa", "sa");
    }
    catch (ClassNotFoundException e) {
      e.printStackTrace();
    }
    catch (SQLException e2) {
      e2.printStackTrace();
    }
    return con;
  }

  //获得指定的oracle连接
  public Connection getOracleConnection(String ip, String username, String pwd) {
    String driver = "oracle.jdbc.driver.OracleDriver";
//    String url="jdbc:oracle:thin:@172.16.102.11:1521:zjedu";
//    String url = "jdbc:oracle:thin:@192.168.0.123:1521:zfjdev";
    String url = "jdbc:oracle:thin:@" + ip;
    try {
      Class.forName(driver);
//      con = DriverManager.getConnection(url, "sunoa", "111111");
      con = DriverManager.getConnection(url, username, pwd);
    }
    catch (ClassNotFoundException e) {
      e.toString();
    }
    catch (SQLException ex) {
      ex.toString();
    }
    return con;
  }

//获得资源数据库连接
  public Connection getOracleConnection() {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@192.168.0.123:1521:zfjdev";
    try {
      Class.forName(driver);
      con = DriverManager.getConnection(url, "dev", "dev");
    }
    catch (ClassNotFoundException e) {
      e.toString();
    }
    catch (SQLException ex) {
      ex.toString();
    }
    return con;
  }

  //关闭连接
  public void disConnect() {
    try {
      if (rs != null) {
        rs.close();
        System.out.println("结果集已关闭");
      }
      if (st != null) {
        st.close();
        System.out.println("st已关闭");
      }
      if (con != null) {
        con.close();
        System.out.println("连接已关闭");
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
  }



  //得到当前用户的所有表名
  public ArrayList getTables(String ip, String username, String pwd) {
    ArrayList arraylist = new ArrayList();
//    String sql = "select * from user_tab_comments";
    String sql = "select OBJECT_ID,OBJECT_NAME,CREATED from OBJ where OBJECT_TYPE='TABLE'";
    try {
      con = this.getOracleConnection(ip, username, pwd);
      System.out.println("ookkkkk");
      st = con.createStatement();
      rs = st.executeQuery(sql);
      while (rs.next()) {
//        columnInfoActionForm form = new columnInfoActionForm();
//        form.setTableId(rs.getString("OBJECT_ID"));
//        form.setTableName(rs.getString("OBJECT_NAME"));
//        String createTime = rs.getString("CREATED").substring(0,10);
//        form.setCreateTime(createTime);
//        arraylist.add(form);
        System.out.println("id"+rs.getString("OBJECT_ID"));
        System.out.println("name"+rs.getString("OBJECT_NAME"));
        System.out.println("time"+rs.getString("CREATED").substring(0,10));
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    this.disConnect();
    return arraylist;
  }


//根据业务表名得到所有字段名，类型，长度，是否为空
  public ArrayList getColumnInfo(String strTable, String ip, String username,
                                 String pwd) {
    ArrayList arraylist = new ArrayList();
    String sql = "select * from " + strTable;
    try {
      con = this.getOracleConnection(ip, username, pwd);
      System.out.println("oookkk");
      st = con.createStatement();
      rs = st.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();
      //索引从1开始
      for (int i = 1; i < rsmd.getColumnCount(); i++) {
//        columnInfoActionForm form = new columnInfoActionForm();
//        form.setTableName(strTable);
//        form.setColumnName(rsmd.getColumnName(i));
//
//        form.setColumnTypeName(rsmd.getColumnTypeName(i));
//        form.setColumnPrecision(Integer.toString(rsmd.getPrecision(i)));
//        form.setIsNullable( (rsmd.isNullable(i) == 1) ? "是" : "否");
//        arraylist.add(form);
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    this.disConnect();
    return arraylist;
  }



//得到资源数据库所有表名
  public ArrayList getTables() {
    ArrayList arraylist = new ArrayList();
    String sql = "select OBJECT_ID,OBJECT_NAME,CREATED from OBJ where OBJECT_TYPE='TABLE'";
    try {
      con = this.getOracleConnection();
      st = con.createStatement();
      rs = st.executeQuery(sql);
      while (rs.next()) {
//        columnInfoActionForm form = new columnInfoActionForm();
//        form.setRes_tableId(rs.getString("OBJECT_ID"));
//        form.setRes_tableName(rs.getString("OBJECT_NAME"));
//        String createTime = rs.getString("CREATED").substring(0,10);
//        form.setRes_createTime(createTime);
//        arraylist.add(form);
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    this.disConnect();
    return arraylist;
  }

  //根据资源数据库表名得到所有字段名，类型，长度，是否为空
  public ArrayList getColumnInfo(String strTable) {
    ArrayList arraylist = new ArrayList();
    String sql = "select * from " + strTable;
    try {
      con = this.getOracleConnection();
      st = con.createStatement();
      rs = st.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();
      //索引从1开始
      for (int i = 1; i < rsmd.getColumnCount(); i++) {
//        columnInfoActionForm form = new columnInfoActionForm();
//        form.setTableName(strTable);
//        form.setRes_columnName(rsmd.getColumnName(i));
//
//        form.setRes_columnTypeName(rsmd.getColumnTypeName(i));
//        form.setRes_columnPrecision(Integer.toString(rsmd.getPrecision(i)));
//        form.setRes_isNullable( (rsmd.isNullable(i) == 1) ? "是" : "否");
//        arraylist.add(form);
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
    this.disConnect();
    return arraylist;
  }


public static void main(String[] a) {

    Getdatabaseinfo db=new Getdatabaseinfo();
//    db.getOracleConnection();


    db.getTables("192.168.0.123","dev","dev");

//  DBConnection db = new DBConnection();
//  ArrayList arraylist = new ArrayList();
//  arraylist = db.getColumnInfo("CG_DOG");
//    arraylist = db.getColumnInfo("ZJEDUACCEPT1");
//    Iterator iterator = arraylist.listIterator();
//    while(iterator.hasNext()) {
//      System.out.println((String)iterator.next());
//    }
}
}