导出oracle数据库所有表结构到excel中
?
这个是基本的连接数据库代码(注意要加入连接oracle数据库的jar包)：
导入的数据库就是String sc="jdbc:oracle:thin:@192.168.1.223:1521:test";对应的test数据库，所有的表结构都可以导出来。（需要修改为自己对应的ip和端口和数据库名称，上面的是我的地址和数据库）
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，要先关闭）：
?
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 blocke.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;}}?最后总结一下：关键性的代码：一：读取数据库中的所有表
select object_name From user_objects Where object_type='TABLE';?二：读取对应表中的结构，下面测试是用CITYNB（这里没有加主键和外键信息，以后加上，有兴趣的朋友可以自己完成，弄好以后，希望给我借鉴一下，呵呵，相互学习嘛，如果还有问题，请联系我：lizhihui19871127@163.com）
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;?
一不小心，拿了作者的成果方便了一下自己的工作！谢谢分享~~