public class MainTest {
private static ThreadPoolExecutor pool = new ThreadPoolExecutor(
5,
20,
8,
TimeUnit.SECONDS,
new ArrayBlockingQueue<>(5),
Executors.defaultThreadFactory(),
new ThreadPoolExecutor.AbortPolicy()
);
public static void main(String[] args) {
String extSchema = "";
String driver = "com.mysql.cj.jdbc.Driver";
//为了保密url暂时不写了
String url = "";
String user = "";
String password = "";
printTableStructure(driver, url, user, password,extSchema);
}
/**
* 打印所有表结构
*
* @param driver driver
* @param url url
* @param user user
* @param password password
* @throws Exception exception
*/
private static void printTableStructure(String driver, String url, String user, String password,String extSchema){
try{
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
DatabaseMetaData metaData = connection.getMetaData();
ResultSet schemaResultSet = metaData.getCatalogs();
//同步单个库
if(!StringUtils.isEmpty(extSchema)){
generateFile(connection,metaData,extSchema);
}else{
Long start = System.currentTimeMillis();
//CountDownLatch latch = new CountDownLatch(18);
//同步所有库
while(schemaResultSet.next()){
String schema = schemaResultSet.getString("TABLE_CAT");
if("information_schema".equals(schema) ||
"seata".equals(schema) ||
"szcgc".equals(schema) ||
"szcgc_project".equals(schema)){
continue;
}
//pool.execute(() -> {
generateFile(connection,metaData,schema);
//latch.countDown();
//});
}
//pool.shutdown();
/*try{
latch.await();
}catch (InterruptedException exception){
exception.getMessage();
}*/
System.out.println("同步耗时: "+ (System.currentTimeMillis() - start));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
private static void generateFile(Connection connection,DatabaseMetaData metaData,String schema){
try{
// 获取所有表
ResultSet tableResultSet = metaData.getTables(schema, schema, "%", new String[]{"TABLE"});
//poi生成excel
XSSFWorkbook workbook = new XSSFWorkbook();
//生成sheet
XSSFSheet sheetIndex = workbook.createSheet("index");
//设置列宽
setColumnWidth(sheetIndex,1);
//使用线程安全的类
AtomicInteger i = new AtomicInteger(0);
while (tableResultSet.next()) {
try {
String tableName= tableResultSet.getString("TABLE_NAME");
String tableRemark = tableResultSet.getString("REMARKS");
Row row0 = sheetIndex.createRow(i.get());
Cell cell0 = row0.createCell(0);
cell0.setCellValue(tableRemark);
cell0.setCellStyle(indexCellStyle(workbook,"1"));
Cell cell1 = row0.createCell(1);
cell1.setCellValue(tableName);
cell1.setCellStyle(indexCellStyle(workbook,"2"));
//单元格加超链接
CreationHelper createHelper = workbook.getCreationHelper();
XSSFHyperlink hyperlink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.DOCUMENT);
hyperlink.setAddress("#"+tableName+"!A1");
cell1.setHyperlink(hyperlink);
i.incrementAndGet();
//生成表sheet
XSSFSheet sheetTable = workbook.createSheet(tableName);
setDetailColumnWidth(sheetTable);
Row rowTable0 = sheetTable.createRow(0);
rowTable0.setHeight((short)400);
Cell cellTable00 = rowTable0.createCell(0);
cellTable00.setCellValue(tableName);
Cell cellTable01 = rowTable0.createCell(1);
cellTable01.setCellValue(tableRemark);
Row rowTable1 = sheetTable.createRow(1);
rowTable1.setHeight((short)600);
Cell cellTable10 = rowTable1.createCell(0);
cellTable10.setCellValue("键说明");
cellTable10.setCellStyle(detailCellStyle(workbook));
Cell cellTable11 = rowTable1.createCell(1);
cellTable11.setCellValue("字段名称");
cellTable11.setCellStyle(detailCellStyle(workbook));
Cell cellTable12 = rowTable1.createCell(2);
cellTable12.setCellValue("字段类型");
cellTable12.setCellStyle(detailCellStyle(workbook));
Cell cellTable13 = rowTable1.createCell(3);
cellTable13.setCellValue("默认值");
cellTable13.setCellStyle(detailCellStyle(workbook));
Cell cellTable14 = rowTable1.createCell(4);
cellTable14.setCellValue("空值");
cellTable14.setCellStyle(detailCellStyle(workbook));
Cell cellTable15 = rowTable1.createCell(5);
cellTable15.setCellValue("字段描述");
cellTable15.setCellStyle(detailCellStyle(workbook,"1"));
//获取表所有主键
ResultSet pkColumns = connection.getMetaData().getPrimaryKeys(null,null,tableName);
// 获取表字段结构
ResultSet columnResultSet = metaData.getColumns(schema, schema, tableName, "%");
int j = 2;
while (columnResultSet.next()) {
// 字段名称
String columnName = columnResultSet.getString("COLUMN_NAME");
// 数据类型
String columnType = columnResultSet.getString("TYPE_NAME");
// 字段长度
int datasize = columnResultSet.getInt("COLUMN_SIZE");
// 小数部分位数
int digits = columnResultSet.getInt("DECIMAL_DIGITS");
// 是否可为空 1代表可空 0代表不可为空
int nullable = columnResultSet.getInt("NULLABLE");
// 描述
String remarks = columnResultSet.getString("REMARKS");
//默认值
String defValue = columnResultSet.getString("COLUMN_DEF");
// 主键
String pK = "NO";
while(pkColumns.next()){
String pkColumnName = pkColumns.getString("COLUMN_NAME");
if(columnName.equals(pkColumnName)){
pK = "PK";
break;
}
}
Row rowTable = sheetTable.createRow(j);
Cell cellTable0 = rowTable.createCell(0);
cellTable0.setCellValue(pK);
Cell cellTable1 = rowTable.createCell(1);
cellTable1.setCellValue(columnName);
Cell cellTable2 = rowTable.createCell(2);
cellTable2.setCellValue(columnType+"("+datasize+")");
Cell cellTable3 = rowTable.createCell(3);
cellTable3.setCellValue(defValue);
Cell cellTable4 = rowTable.createCell(4);
cellTable4.setCellValue(nullable == 0 ? "NOT NULL":null);
Cell cellTable5 = rowTable.createCell(5);
cellTable5.setCellValue(remarks);
if(!columnResultSet.isLast()){
cellTable0.setCellStyle(columnCellStyle(workbook));
cellTable1.setCellStyle(columnCellStyle(workbook));
cellTable2.setCellStyle(columnCellStyle(workbook));
cellTable3.setCellStyle(columnCellStyle(workbook));
cellTable4.setCellStyle(columnCellStyle(workbook));
cellTable5.setCellStyle(columnCellStyle(workbook,"1"));
}else{
cellTable0.setCellStyle(columnCellStyle(workbook,"2"));
cellTable1.setCellStyle(columnCellStyle(workbook,"2"));
cellTable2.setCellStyle(columnCellStyle(workbook,"2"));
cellTable3.setCellStyle(columnCellStyle(workbook,"2"));
cellTable4.setCellStyle(columnCellStyle(workbook,"2"));
cellTable5.setCellStyle(columnCellStyle(workbook,"3"));
}
j++;
}
pkColumns.close();
columnResultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
OutputStream os = null;
try {
os = new FileOutputStream("d:\\"+schema+".xlsx");
workbook.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//索引sheet样式设置
private static CellStyle indexCellStyle(XSSFWorkbook workbook, String type){
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
//设置字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
if("2".equals(type)){
font.setUnderline((byte) 1);
font.setColor(IndexedColors.BLUE.index);
}
cellStyle.setFont(font);
return cellStyle;
}
//详细表sheet样式设置
private static CellStyle detailCellStyle(XSSFWorkbook workbook){
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFColor color=new XSSFColor(new java.awt.Color(155,194,230),new DefaultIndexedColorMap());
cellStyle.setFillForegroundColor(color);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderTop(BorderStyle.MEDIUM);
cellStyle.setTopBorderColor(IndexedColors.BLUE.index);
//设置字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
cellStyle.setFont(font);
return cellStyle;
}
private static CellStyle detailCellStyle(XSSFWorkbook workbook,String type){
CellStyle cellStyle = detailCellStyle(workbook);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setRightBorderColor(IndexedColors.BLUE.index);
return cellStyle;
}
//设置字段样式
private static CellStyle columnCellStyle(XSSFWorkbook workbook){
CellStyle cellStyle = workbook.createCellStyle();
//设置字体
Font font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
cellStyle.setFont(font);
return cellStyle;
}
private static CellStyle columnCellStyle(XSSFWorkbook workbook,String type){
CellStyle cellStyle = columnCellStyle(workbook);
//右边框
if("1".equals(type)){
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setRightBorderColor(IndexedColors.BLUE.index);
}
//底边框
else if("2".equals(type)){
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBottomBorderColor(IndexedColors.BLUE.index);
}
//表格最后一行需同时设置右边框和底边框
else{
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setRightBorderColor(IndexedColors.BLUE.index);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBottomBorderColor(IndexedColors.BLUE.index);
}
return cellStyle;
}
//设置主页sheet列宽
private static void setColumnWidth(XSSFSheet sheet,int index){
for(int i = 0; i<=index; i++){
sheet.setColumnWidth(i, 30 * 256);
}
}
//设置详情sheet列宽
private static void setDetailColumnWidth(XSSFSheet sheet){
sheet.setColumnWidth(0, 30 * 256);
sheet.setColumnWidth(1, 30 * 256);
sheet.setColumnWidth(2, 15 * 256);
sheet.setColumnWidth(3, 15 * 256);
sheet.setColumnWidth(4, 15 * 256);
sheet.setColumnWidth(5, 30 * 256);
}
//使用静态内部类(在初始化这个内部类的时候,JLS(Java Language Sepcification)会保证这个类的线程安全)
private static class SingleTonHolder{
public final static XSSFWorkbook singleTon = new XSSFWorkbook();
}
public static XSSFWorkbook getInstance(){
return SingleTonHolder.singleTon;
}
}
poi+ResultSet+线程池导出数据库表结构
最新推荐文章于 2023-06-14 10:14:14 发布