oracle jdbc获取表结构,JDBC根据数据库表名得到表结构

JDBC根据数据库表名得到表结构

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class comparefileandtable {

/**

* @param args

*/

private String url =”jdbc:oracle:thin:@127.0.0.1:1521:hhh”;

private String dbUserName =”dhh”;

private String dbUserPassword =”a”;

private Connection conn =null;

private Statement stmt =null;

private ResultSet rs =null;

public List restList = new ArrayList();

public static void main(String[] args) throws IOException {

// TODO Auto-generated method stub

comparefileandtable nj = new comparefileandtable();

//String sql = “select * from MUREX_DE_TP_RT”;

String tnsn = “aaaa,bbb”;//表名以逗号分开

String nn [] = tnsn.split(“,”);

List resl = new ArrayList();

for (int i = 0; i < nn.length; i++) {

String tableName = nn[i];

System.out.println(“tableName is “+tableName);

resl.addAll(nj.query(tableName));

}

HSSFWorkbook swb = new HSSFWorkbook();

HSSFSheet dsheet = swb.createSheet(“sheet1″);

for (int i = 0; i < resl.size(); i++) {

HSSFRow drow = dsheet.createRow(i);

HSSFCell dtcell = drow.createCell(0);

dtcell.setCellValue(resl.get(i).getTableName());

HSSFCell dtcell1 = drow.createCell(1);

dtcell1.setCellValue(resl.get(i).getColumnName());

HSSFCell dtcell2 = drow.createCell(2);

dtcell2.setCellValue(resl.get(i).getColumnType());

HSSFCell dtcell3 = drow.createCell(3);

dtcell3.setCellValue(resl.get(i).getPmaAble());

HSSFCell dtcell4 = drow.createCell(4);

dtcell4.setCellValue(resl.get(i).getNullAble());

}

FileOutputStream fo = new FileOutputStream(“d:/testtanle.xls”);//将表结构相关信息输出到excel中

swb.write(fo);

fo.close();

System.out.println(“end”);

}

public comparefileandtable() {

try {

Class.forName(“oracle.jdbc.driver.OracleDriver”);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public Connection getConnection() {

try {

conn = DriverManager.getConnection(url, dbUserName,dbUserPassword);

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

public void close(ResultSet rs, Statement stmt, Connection conn){

if (rs !=null) {

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (stmt !=null) {

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (conn !=null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

public List query(String tableName)

throws IOException {

String sql = “select * from “+ tableName.toUpperCase();

System.out.println(sql);

ResultSetMetaData rsm = null;

conn = this.getConnection();

try {

stmt = conn.createStatement();

rs = stmt.executeQuery(sql);

rsm = rs.getMetaData();

int col = rsm.getColumnCount();

System.out.println(“col is ” + col);

DatabaseMetaData dmd = conn.getMetaData();

ResultSet pkRSet = dmd.getPrimaryKeys(null, null, tableName.toUpperCase());

List listpmcol = new ArrayList();

while(pkRSet.next()){//得到主键字段

listpmcol.add(pkRSet.getObject(4));

}

for (int i = 1; i < col; i++) {

String pm = “”;

for (int j = 0; j < listpmcol.size(); j++) {

if(listpmcol.get(j).toString().trim().equals(rsm.getColumnName(i).trim())){

pm = “Y”;

break;

}else {

pm = “N”;

}

}

String na = 1 == rsm.isNullable(i) ? “Y” : “N”;

System.out.println(rsm.getColumnName(i) + ” ”

+ rsm.getColumnTypeName(i) + “(” + rsm.getPrecision(i)+”,”+rsm.getScale(i)

+ “)” + ” ” + pm+” “+na);

tableStruc ts = new tableStruc();

ts.setTableName(tableName);

ts.setColumnName(rsm.getColumnName(i));

if(“NUMBER”.equals(rsm.getColumnTypeName(i))){

ts.setColumnType(rsm.getColumnTypeName(i) + “(” + rsm.getPrecision(i)+”,”+rsm.getScale(i)+ “)”);

}else{

ts.setColumnType(rsm.getColumnTypeName(i) + “(” + rsm.getPrecision(i)+ “)”);

}

ts.setPmaAble(pm);

ts.setNullAble(na);

restList.add(ts);

}

/*

System.out.println(“下面这些方法是ResultSetMetaData中方法”);

System.out.println(“获得1列所在的Catalog名字 : ” + rsmd.getCatalogName(1));

System.out.println(“获得1列对应数据类型的类 ” + rsmd.getColumnClassName(1));

System.out.println(“获得该ResultSet所有列的数目 ” + rsmd.getColumnCount());

System.out.println(“1列在数据库中类型的最大字符个数” + rsmd.getColumnDisplaySize(1));

System.out.println(” 1列的默认的列的标题” + rsmd.getColumnLabel(1));

System.out.println(“1列的模式” + rsmd.GetSchemaName(1));

System.out.println(“1列的类型,返回SqlType中的编号 ” + rsmd.getColumnType(1));

System.out.println(“1列在数据库中的类型，返回类型全名” + rsmd.getColumnTypeName(1));

System.out.println(“1列类型的精确度(类型的长度): ” + rsmd.getPrecision(1));

System.out.println(“1列小数点后的位数 ” + rsmd.getScale(1));

System.out.println(“1列对应的模式的名称(应该用于Oracle) ” + rsmd.getSchemaName(1));

System.out.println(“1列对应的表名 ” + rsmd.getTableName(1));

System.out.println(“1列是否自动递增” + rsmd.isAutoIncrement(1));

System.out.println(“1列在数据库中是否为货币型” + rsmd.isCurrency(1));

System.out.println(“1列是否为空” + rsmd.isNullable(1));

System.out.println(“1列是否为只读” + rsmd.isReadOnly(1));

System.out.println(“1列能否出现在where中” + rsmd.isSearchable(1));

*/

} catch (SQLException e) {

e.printStackTrace();

} finally {

this.close(rs, stmt, conn);

}

return restList;

}

}

tableStruc 类如下

public class tableStruc {

private String tableName;

private String columnName;

private String columnType;

private String pmaAble;

private String nullAble;

public String getTableName() {

return tableName;

}

public void setTableName(String tableName) {

this.tableName = tableName;

}

public String getColumnName() {

return columnName;

}

public void setColumnName(String columnName) {

this.columnName = columnName;

}

public String getColumnType() {

return columnType;

}

public void setColumnType(String columnType) {

this.columnType = columnType;

}

public String getPmaAble() {

return pmaAble;

}

public void setPmaAble(String pmaAble) {

this.pmaAble = pmaAble;

}

public String getNullAble() {

return nullAble;

}

public void setNullAble(String nullAble) {

this.nullAble = nullAble;

}