抽取oracle 所有表

package db;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.configuration.Configuration;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class DbUpload {

	// 主方法
	public static void main(String[] args) {

		try {

			List<DbModel> listTab = getListTab();

			Map<String, List<DbModel>> map = getListCol(listTab);

			db_down(listTab, map);

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	// --------------- DB Start --------------------
	public static List<DbModel> getListTab() throws Exception {

		List<DbModel> tabList = new ArrayList<DbModel>();

		// 定义了数据库连接串
		String dbUrl = getProValue("DB.Url");
		// 数据库的用户名
		String user = getProValue("DB.NAME");
		// 数据库的用户口令
		String password = getProValue("DB.PASSWORD");
		// 加载jdbc-odbc bridge驱动程序
		Class.forName("oracle.jdbc.driver.OracleDriver");
		// 与url指定的数据源建立连接
		Connection c = DriverManager.getConnection(dbUrl, user, password);
		// 采用Statement进行查询
		PreparedStatement p = c
				.prepareStatement("SELECT A.TABLE_NAME, B.COMMENTS FROM DBA_TABLES A,USER_TAB_COMMENTS B WHERE A.TABLE_NAME=B.TABLE_NAME AND OWNER=? ");

		p.setString(1, user);

		ResultSet r = p.executeQuery();

		while (r.next()) {

			DbModel model = new DbModel();
			model.setTableName(r.getString(1));
			model.setTableComments(r.getString(2));
			tabList.add(model);
		}
		r.close();
		p.close();
		c.close();
		return tabList;
	}

	public static Map<String, List<DbModel>> getListCol(List<DbModel> modelList)
			throws Exception {

		Map<String, List<DbModel>> m = new HashMap<String, List<DbModel>>();

		// 定义了数据库连接串
		String dbUrl = getProValue("DB.Url");
		// 数据库的用户名
		String user = getProValue("DB.NAME");
		// 数据库的用户口令
		String password = getProValue("DB.PASSWORD");
		// 加载jdbc-odbc bridge驱动程序
		Class.forName("oracle.jdbc.driver.OracleDriver");
		// 与url指定的数据源建立连接

		String sql = "SELECT DISTINCT(A.COLUMN_NAME),A.COMMENTS  ,B.DATA_TYPE,B.DATA_LENGTH "
				+ "FROM USER_COL_COMMENTS  A, ALL_TAB_COLUMNS B "
				+ "WHERE A.TABLE_NAME = B.TABLE_NAME AND  A.TABLE_NAME = ?";

		if (modelList.size() != 0 && modelList != null) {

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

				Connection c = DriverManager.getConnection(dbUrl, user,
						password);

				PreparedStatement p = null;
				ResultSet r = null;

				List<DbModel> tabList = new ArrayList<DbModel>();

				// 采用Statement进行查询
				p = c.prepareStatement(sql);

				p.setString(1, modelList.get(i).getTableName());

				r = p.executeQuery();

				while (r.next()) {
					DbModel model = new DbModel();
					model.setColName(r.getString(1));
					model.setColComments(r.getString(2));
					model.setDataType(r.getString(3));
					model.setDataLength(r.getString(4));
					tabList.add(model);
				}
				m.put(modelList.get(i).getTableName(), tabList);
				r.close();
				p.close();
				c.close();
			}
		}

		return m;
	}

	// --------------------DB End -----------------------

	// -------------Properties Start-----------------------
	public static String getProValue(String key) throws Exception {

		Configuration config = new PropertiesConfiguration(
				"filePath.properties");
		String value = config.getString(key);

		return value;
	}

	// -------------Properties End-----------------------

	// -----------------------DB down start----------------------

	private static void db_down(List<DbModel> tab,
			Map<String, List<DbModel>> col) throws Exception {

		String outputFile = getProValue("DOWN_FILE_PATH");

		// 工作簿
		HSSFWorkbook hssfworkbook = new HSSFWorkbook();

		String tableName = "";
		String tableComments = "";
		if (tab.size() != 0 && tab != null) {

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

				tableName = tab.get(i).getTableName();
				tableComments = tab.get(i).getTableComments();
				List<DbModel> colList = col.get(tableName);

				// 创建sheet页
				HSSFSheet hssfsheet = hssfworkbook.createSheet();

				// sheet名称乱码处理
				hssfworkbook.setSheetName(i, tableName);

				hssfsheet.setColumnWidth(0, 20 * 256);
				hssfsheet.setColumnWidth(1, 30 * 256);
				hssfsheet.setColumnWidth(2, 15 * 256);
				if (colList.size() != 0 && colList != null) {

					//------------------表 名称  start--------------
					// 取得第一行
					HSSFRow hssfrow_table_title = hssfsheet.createRow(0);

					HSSFCell cell_table_title_name = hssfrow_table_title.createCell((short) 0);
					cell_table_title_name.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell_table_title_name.setCellValue(changeCharset("表名称"));


					HSSFCell cell_table_title_0 = hssfrow_table_title.createCell((short) 1);
					cell_table_title_0.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell_table_title_0.setCellValue(changeCharset(tableName));

					HSSFCell cell_table_title_1 = hssfrow_table_title.createCell((short) 2);
					cell_table_title_1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell_table_title_1.setCellValue(changeCharset(tableComments));

					//------------------表 名称  start--------------

					// -------------标题 start----------------------

					// 取得第一行
					HSSFRow hssfrow = hssfsheet.createRow(1);

					// 创建第一个单元格 并处理乱码
					HSSFCell cell_t_0 = hssfrow.createCell((short) 0);

					cell_t_0.setCellType(HSSFCell.CELL_TYPE_STRING);
					// 对第1个单元格赋值
					cell_t_0.setCellValue(changeCharset("字段"));

					// 创建第1个单元格 并处理乱码
					HSSFCell cell_t_1 = hssfrow.createCell((short) 1);

					cell_t_1.setCellType(HSSFCell.CELL_TYPE_STRING);
					// 对第1个单元格赋值
					cell_t_1.setCellValue(changeCharset("注释"));

					// 创建第2个单元格 并处理乱码
					HSSFCell cell_t_2 = hssfrow.createCell((short) 2);

					cell_t_2.setCellType(HSSFCell.CELL_TYPE_STRING);
					// 对第2个单元格赋值
					cell_t_2.setCellValue(changeCharset("类型"));

					// 创建第3个单元格 并处理乱码
					HSSFCell cell_t_3 = hssfrow.createCell((short) 3);

					cell_t_3.setCellType(HSSFCell.CELL_TYPE_STRING);
					// 对第4个单元格赋值
					cell_t_3.setCellValue(changeCharset("长度"));

					// -------------标题 End----------------------

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

						HSSFRow hssfrow_c = hssfsheet.createRow(j + 2);

						HSSFCell cell_c_0 = hssfrow_c.createCell((short) 0);
						cell_c_0.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell_c_0.setCellValue(changeCharset(colList.get(j)
								.getColName()));

						HSSFCell cell_c_1 = hssfrow_c.createCell((short) 1);
						cell_c_1.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell_c_1.setCellValue(changeCharset(colList.get(j)
								.getColComments()));

						HSSFCell cell_c_2 = hssfrow_c.createCell((short) 2);
						cell_c_2.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell_c_2.setCellValue(changeCharset(colList.get(j)
								.getDataType()));

						HSSFCell cell_c_3 = hssfrow_c.createCell((short) 3);
						cell_c_3.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell_c_3.setCellValue(changeCharset(colList.get(j)
								.getDataLength()));

					}

				}

			}
		}

		// 输出
		System.out.println("PATH:"+outputFile);
		FileOutputStream fileoutputstream = new FileOutputStream(outputFile);
		hssfworkbook.write(fileoutputstream);
		fileoutputstream.close();

		System.out.println("文件生成完了。。。。。。。。。。。。。。。。");
	}

	// -----------------------DB down End----------------------

	// ------------字符编码 Start---------------------------
	private static String changeCharset(String str) throws Exception {
		if (str != null) {
			// 用默认字符编码解码字符串。
			byte[] bs = str.getBytes();
			// 用新的字符编码生成字符串
			return new String(bs, "UTF-8");
		}
		return "";
	}
	// ------------字符编码 End---------------------------
}