Java读取数据库表信息以及字段信息生成Word文档

参考:https://www.cnblogs.com/wyang0126/p/5039931.html

先上代码

package com.example.test;

import com.lowagie.text.*;
import com.lowagie.text.Font;
import com.lowagie.text.Rectangle;
import com.lowagie.text.pdf.BaseFont;
import com.lowagie.text.rtf.RtfWriter2;
import com.lowagie.text.rtf.style.RtfParagraphStyle;

import java.awt.*;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @Description:
 * @Author:clj
 * @CreateDate:2020/6/28 14:41
 */
public class CreateTableWord {

    public static void main(String[] args){
     //Oracle,MySQL,DB2,SqlServer都可以,只要换成相应的连接信息,并且在pom.xml中引入相应的依赖 String driver
= "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@ip:1521/orcl"; String user = "username"; String pwd = "passdword"; createWord(driver, url, user, pwd); } private static void createWord(String driver, String url, String user, String pwd){ //创建一个word文档,等待填写内容 Document document = new Document(PageSize.A4); try { //内容填写完,输出文件 RtfWriter2.getInstance(document, new FileOutputStream( "E:/AllTable.doc")); //打开 doc document.open(); } catch (FileNotFoundException e) { e.printStackTrace(); } List<String[]> tables = getTableNames(driver, url, user, pwd); //循环遍历输出所有的表的信息 for (String[] tableInfo : tables) { List<Object[]> list = getTableColumns(driver, url, user, pwd, tableInfo[0]); document=docAll(document, tableInfo, list); System.out.println(tableInfo[0]); } //关闭 doc document.close(); } /** * 获取指定库中表名和表注释 * @param driver * @param url * @param user * @param pwd * @return */ public static List<String[]> getTableNames(String driver, String url, String user, String pwd){ Connection conn = null; DatabaseMetaData dbmd = null; List<String[]> tableNames = new ArrayList<>(); try { conn = getConnections(driver, url, user, pwd); dbmd = conn.getMetaData(); ResultSet resultSet = dbmd.getTables(null, getSchema(conn), "%", new String[]{"TABLE"}); while (resultSet.next()) { String[] tableInfo = new String[2]; //表名 String tableName = resultSet.getString("TABLE_NAME"); tableInfo[0] = tableName; //表注释 String tableRemark = resultSet.getString("REMARKS"); tableInfo[1] = tableRemark; tableNames.add(tableInfo); } }catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return tableNames; } /** * 获取指定表的字段信息(包括字段名称,字段类型,字段长度,备注) * @param driver * @param url * @param user * @param pwd * @param tableName * @return */ public static List<Object[]> getTableColumns(String driver, String url, String user, String pwd, String tableName){ List<Object[]> result = new ArrayList(); Connection conn = null; DatabaseMetaData dbmd = null; try { conn = getConnections(driver,url,user,pwd); dbmd = conn.getMetaData(); ResultSet rs = conn.getMetaData().getColumns(null, getSchema(conn),tableName.toUpperCase(), "%"); while(rs.next()){ Object[] objects = new Object[4]; //字段名称 String colName = rs.getString("COLUMN_NAME"); objects[0] = colName; //字段类型 String dbType = rs.getString("TYPE_NAME"); objects[1] = dbType; //字段长度 int columnSize = rs.getInt("COLUMN_SIZE"); objects[2] = columnSize; //备注 String remarks = rs.getString("REMARKS"); if(remarks == null || remarks.equals("")){ remarks = ""; } objects[3] = remarks; result.add(objects); } } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return result; } //获取连接 public static Connection getConnections(String driver,String url,String user,String pwd) throws Exception { Connection conn = null; try { Properties props = new Properties(); props.put("remarksReporting", "true"); props.put("user", user); props.put("password", pwd); Class.forName(driver); conn = DriverManager.getConnection(url, props); } catch (Exception e) { e.printStackTrace(); throw e; } return conn; } //其他数据库不需要这个方法 oracle和db2需要 public static String getSchema(Connection conn) throws Exception { String schema; schema = conn.getMetaData().getUserName(); if ((schema == null) || (schema.length() == 0)) { throw new Exception("ORACLE数据库模式不允许为空"); } return schema.toUpperCase().toString(); } /** * 输出数据库中所有表的信息 * @param document document * @param tableInfo 表名和表注释 * @param list 查询出该表中的信息 * @return */ public static Document docAll(Document document,String[] tableInfo, List<Object[]> list) { try { Paragraph ph = new Paragraph(); Font font = new Font(); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.append(tableInfo[0]); if (tableInfo[1] != null && !"".equals(tableInfo[1])){ stringBuilder.append("(" + tableInfo[1] + ")"); } Paragraph paragraph = new Paragraph(stringBuilder.toString(), RtfParagraphStyle.STYLE_HEADING_1); paragraph.setAlignment(0); document.add(paragraph); Table table = new Table(4); table.setWidth(100); table.setBorderWidth(Rectangle.NO_BORDER); table.setPadding(0); table.setSpacing(0); Cell cell = null; cell = new Cell("字段名称"); cell.setBackgroundColor(Color.LIGHT_GRAY); cell.setHeader(true); table.addCell(cell); cell = new Cell("字段类型"); cell.setBackgroundColor(Color.LIGHT_GRAY); cell.setHeader(true); table.addCell(cell); cell = new Cell("字段长度"); cell.setBackgroundColor(Color.LIGHT_GRAY); cell.setHeader(true); table.addCell(cell); cell = new Cell("备注"); cell.setBackgroundColor(Color.LIGHT_GRAY); cell.setHeader(true); table.addCell(cell); for (int i = 0; i < list.size(); i++) { cell = new Cell(list.get(i)[0].toString()); cell.setUseAscender(true); cell.setHorizontalAlignment(Cell.ALIGN_CENTER); table.addCell(cell); cell = new Cell(list.get(i)[1].toString().toUpperCase()); cell.setUseAscender(true); cell.setHorizontalAlignment(Cell.ALIGN_CENTER); table.addCell(cell); cell = new Cell(list.get(i)[2].toString()); cell.setUseAscender(true); cell.setHorizontalAlignment(Cell.ALIGN_CENTER); table.addCell(cell); cell = new Cell(list.get(i)[3].toString()); cell.setUseAscender(true); cell.setHorizontalAlignment(Cell.ALIGN_CENTER); table.addCell(cell); } document.add(table); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } return document; } }

结果

Java读取数据库表信息以及字段信息生成Word文档

 

 使用到的依赖

<dependency>
            <groupId>javax.persistence</groupId>
            <artifactId>persistence-api</artifactId>
            <version>1.0.2</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.lowagie.text</groupId>
            <artifactId>com.springsource.com.lowagie.text</artifactId>
            <version>2.0.8</version>
        </dependency>

 

Java读取数据库表信息以及字段信息生成Word文档

上一篇:oracle本机忘记密码处理


下一篇:在docker容器创建oracle的表空间遇到问题