转导出oracle数据库所有表结构到excel、world中

这个是基本的连接数据库代码(注意要加入连接oracle数据库的jar包)：

导入的数据库就是String sc="jdbc:oracle:thin:@192.168.1.223:1521:test";对应的test数据库，所有的表结构都可以导出来。（需要修改为自己对应的ip和端口和数据库名称，上面的是我的地址和数据库）
Java代码 收藏代码

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;



public class ConnectionOracle {



/**

* @param args

*/

String sd="oracle.jdbc.driver.OracleDriver";

// String sc="jdbc:oracle:thin:@192.168.1.170:1521:cst";

String sc="jdbc:oracle:thin:@192.168.1.223:1521:test";

String userName = "adminss";

String password = "123456";

// String sd="com.mysql.jdbc.Driver";

// String sc="jdbc:mysql://localhost:3306/payManagerDB?useUnicode=true&characterEncoding=utf8";

Connection con=null;

Statement stmt=null;

ResultSet rs=null;


public ConnectionOracle()

{

try

{

Class.forName(sd);

}

catch(Exception e)

{

System.err.println(e.getMessage());

}

}

public static void main(String[] args) {

// TODO Auto-generated method stub


}

public ResultSet executeQuery(String sql) throws SQLException

{

con=DriverManager.getConnection(sc,userName,password);

Statement stmt=con.createStatement();

rs=stmt.executeQuery(sql);

return rs;

}

public void executeUpdate(String sql) throws SQLException

{

con=DriverManager.getConnection(sc,userName,password);

Statement stmt=con.createStatement();

stmt.executeUpdate(sql);

}

public void close() throws SQLException

{

if(rs!=null)

rs.close();

if(stmt!=null)

stmt.close();

if(con!=null)

con.close();

}

}





下面的代码读取数据库中所有表以及结构到excel中（如果出现问题，首先看是不是你已经打开了这个excel，要先关闭）：


Java代码 收藏代码

import java.io.File;

import java.io.FileOutputStream;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;



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

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

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

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

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

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

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

import org.apache.poi.hssf.util.Region;



/**

* 将oracle中的数据表结构导入到excel中保存

* @class DataToExcel

* @description

* @author 李智慧

* @copyRight copyright(c) 2011 广东南航易网通电子商务有限公司,Rights Reserved

* @time Dec 27, 2011 10:02:08 AM

*/

public class DataToExcel {

public static void main(String[] args) {

String result = "";

List listAll = new ArrayList();

System.out.println("正在读取数据库中所有的表");

try {

List tableList = getTableList();

System.out.println("数据库表读取完成");

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

String[] strings = (String[]) tableList.get(i);

String tableName = strings[0].toString();

List list = new ArrayList();

list.add(tableName);

list.add(getStructOfTable(tableName));

System.out.println("正在生成表"+tableName+"的结构");

listAll.add(list);

}

result = TableStructInfoToExcel(listAll,"D:");

System.out.println("数据库中表结构导入已完成");

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

File file = new File(e.getMessage().toString());

if(file.exists()){

file.delete();

}

}

System.out.println(result);

//showView(list);

}


/**

* 获取数据库中所有的表

* @return

*/

public static List getTableList(){

String sql = "select object_name From user_objects Where object_type='TABLE'";

return getResult(sql,1);

}


/**

* 根据表明

* @param tableName

* @return

*/

public static List getStructOfTable(String tableName){

String sql = "SELECT u.column_name,u.data_type,u.data_length,u.data_precision,u.data_Scale,u.nullable,u.data_default,c.comments FROM user_tab_columns u,user_col_comments c"+

" WHERE u.table_name='"+tableName+"' and u.table_name=c.table_name and c.column_name=u.column_name";

return getResult(sql,8);

}


/**

* 获取结果的公用类

* @param sql

* @param length

* @return

*/

public static List getResult(String sql,int length){

List list = new ArrayList();

ResultSet rs=null;

ConnectionOracle c=new ConnectionOracle();

try {

rs=c.executeQuery(sql);

while(rs.next()){

String[] string = new String[length];

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

string[i-1] = rs.getString(i);

}

list.add(string);

}

c.close();

} catch (SQLException e) {

e.printStackTrace();

}

return list;

}


/**

* 输出对应list中的数据

* @param list

*/

public static void showView(List list){

for (Iterator iterator = list.iterator(); iterator.hasNext();) {

String[] name = (String[]) iterator.next();

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

System.out.println(name[i]);

}

}

}


/**

* 将数据导入到excel中

* @param list

* @param tableName

* @param path

* @return

* @throws Exception

*/

public static String TableStructInfoToExcel(List list,String path) throws Exception {

String FileName="";

FileOutputStream fos = null;

HSSFRow row = null;

HSSFCell cell = null;

HSSFCellStyle style = null;

HSSFFont font = null;

int currentRowNum = 0;

String[] tableFiled = {"column_name","data_type","data_length","data_precision","data_Scale","nullable","data_default","comments"};

try{

FileName = path +"\\"+"CSN数据库中表结构.xls";

fos = new FileOutputStream(FileName);

//创建新的sheet并设置名称

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet s = wb.createSheet();

wb.setSheetName(0, "CSN数据库表结构");

style = wb.createCellStyle();

font = wb.createFont();


for(int z=0;z<list.size();z++){

List listBean = (List) list.get(z);

//新建一行,再在行上面新建一列

row = s.createRow(currentRowNum);

int pad = currentRowNum;

currentRowNum++;

//设置样式

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗

style.setFont(font);

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

style.setFillForegroundColor((short) 13);// 设置背景色

style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

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

cell = row.createCell((short) i);

cell.setCellValue("");

cell.setCellStyle(style);

}

row.getCell((short) 0).setCellValue("数据库表"+listBean.get(0).toString()+"的结构");

//创建第二行

row = s.createRow(currentRowNum);

currentRowNum++;

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

//创建多列并设置每一列的值和宽度

cell = row.createCell((short) i);

cell.setCellValue(new HSSFRichTextString(tableFiled[i]));

s.setColumnWidth((short)i,(short)5000);

}

List list2 = (List) listBean.get(1);

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

row = s.createRow(currentRowNum);

currentRowNum++;

String[] strings = (String[]) list2.get(i);

for(int j=0;j<strings.length;j++){

cell = row.createCell((short) j);

cell.setCellValue(new HSSFRichTextString(strings[j]));

}

}

//合并单元格

s.addMergedRegion(new Region(pad,(short)0,pad,(short)(tableFiled.length-1)));

currentRowNum ++;

}


wb.write(fos);

fos.close();

}catch (Exception e) {

e.printStackTrace();

fos.close();

throw new Exception(FileName);

}

return FileName;

}

}


最后总结一下：
关键性的代码：
一：读取数据库中的所有表
Java代码 收藏代码

select object_name From user_objects Where object_type='TABLE';


二：读取对应表中的结构，下面测试是用CITYNB（这里没有加主键和外键信息，以后加上，有兴趣的朋友可以自己完成，弄好以后，希望给我借鉴一下，呵呵，相互学习嘛，如果还有问题，请联系我：lizhihui19871127@163.com）
Java代码 收藏代码

SELECT u.column_name,u.data_type,u.data_length,u.data_precision,u.data_Scale,u.nullable,u.data_default,c.comments FROM user_tab_columns u,user_col_comments c
WHERE u.table_name='CITYNB' and u.table_name=c.table_name and c.column_name=u.column_name;

因为需要写数据库文档，所以需要把数据库里边的表结构在word中用表格列出来，之前一直用powerdesigner，感觉有些麻烦，后来在网上找到了一段sql语句，经测试完全符合我的需求，不敢独享，语句如下：

SELECT t1.Table_Name AS "表名称",
t3.comments AS "表说明",
t1.Column_Name AS "字段名称",
t1.Data_Type AS "数据类型",
t1.Data_Length AS "长度",
t1.NullAble AS "是否为空",
t2.Comments AS "字段说明",
t1.Data_Default "默认值"

FROM cols t1 left join user_col_comments t2
on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
left join user_tab_comments t3
on t1.Table_name=t3.Table_name
WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
WHERE t4.Object_Type='TABLE'
AND t4.Temporary='Y'
AND t4.Object_Name=t1.Table_Name )
ORDER BY t1.Table_Name, t1.Column_ID;
查询完成后，粘贴指定的列即可

spool C:\sql_ddl.txt;
select dbms_metadata.get_ddl('TABLE','TABLE_NAME') FROM dual;
spool off;

-- TABLE_NAME 是你的表名

-- 然后打开C:\sql_ddl.txt文件，里面有你要的代码！