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---------------------------
}
抽取oracle 所有表
最新推荐文章于 2021-04-05 01:43:15 发布