java读取数据库中word文件_Java读取数据库表信息以及字段信息生成Word文档

packagecom.example.test;import com.lowagie.text.*;importcom.lowagie.text.Font;importcom.lowagie.text.Rectangle;importcom.lowagie.text.pdf.BaseFont;importcom.lowagie.text.rtf.RtfWriter2;importcom.lowagie.text.rtf.style.RtfParagraphStyle;import java.awt.*;importjava.io.FileNotFoundException;importjava.io.FileOutputStream;import java.sql.*;importjava.util.ArrayList;importjava.util.List;importjava.util.Properties;/*** @Description:

* @Author:clj

* @CreateDate:2020/6/28 14:41*/

public classCreateTableWord {public static voidmain(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 voidcreateWord(String driver, String url, String user, String pwd){//创建一个word文档，等待填写内容

Document document = newDocument(PageSize.A4);try{//内容填写完，输出文件

RtfWriter2.getInstance(document, newFileOutputStream("E:/AllTable.doc"));//打开 doc

document.open();

}catch(FileNotFoundException e) {

e.printStackTrace();

}

List tables =getTableNames(driver, url, user, pwd);//循环遍历输出所有的表的信息

for(String[] tableInfo : tables) {

List list = getTableColumns(driver, url, user, pwd, tableInfo[0]);

document=docAll(document, tableInfo, list);

System.out.println(tableInfo[0]);

}//关闭 doc

document.close();

}/*** 获取指定库中表名和表注释

*@paramdriver

*@paramurl

*@paramuser

*@parampwd

*@return

*/

public static ListgetTableNames(String driver, String url, String user, String pwd){

Connection conn= null;

DatabaseMetaData dbmd= null;

List 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();

}

}returntableNames;

}/*** 获取指定表的字段信息(包括字段名称，字段类型，字段长度，备注)

*@paramdriver

*@paramurl

*@paramuser

*@parampwd

*@paramtableName

*@return

*/

public static ListgetTableColumns(String driver, String url, String user, String pwd, String tableName){

List result = newArrayList();

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();

}

}returnresult;

}//获取连接

public static Connection getConnections(String driver,String url,String user,String pwd) throwsException {

Connection conn= null;try{

Properties props= newProperties();

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();throwe;

}returnconn;

}//其他数据库不需要这个方法 oracle和db2需要

public static String getSchema(Connection conn) throwsException {

String schema;

schema=conn.getMetaData().getUserName();if ((schema == null) || (schema.length() == 0)) {throw new Exception("ORACLE数据库模式不允许为空");

}returnschema.toUpperCase().toString();

}/*** 输出数据库中所有表的信息

*@paramdocument document

*@paramtableInfo 表名和表注释

*@paramlist 查询出该表中的信息

*@return

*/

public static Document docAll(Document document,String[] tableInfo, Listlist) {try{

Paragraph ph= newParagraph();

Font font= newFont();

StringBuilder stringBuilder= newStringBuilder();

stringBuilder.append(tableInfo[0]);if (tableInfo[1] != null && !"".equals(tableInfo[1])){

stringBuilder.append("(" + tableInfo[1] + ")");

}

Paragraph paragraph= newParagraph(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();

}returndocument;

}

}