利用Java程序导出Oracle库中所有表结构数据

最新推荐文章于 2025-03-13 20:00:00 发布

乘风御浪云帆之上

最新推荐文章于 2025-03-13 20:00:00 发布

阅读量2k

点赞数 3

分类专栏： java工具文章标签： java oracle excel

本文链接：https://blog.youkuaiyun.com/eieiei438/article/details/106260260

版权

java工具专栏收录该内容

12 篇文章

订阅专栏

利用Java程序导出Oracle库中所有表结构数据

一度为快

实现

pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <artifactId>deal_data</artifactId>
    <groupId>com.peng</groupId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!--不引入ojdbc7项目编译通过，执行数据库那部分会报错-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc7</artifactId>
            <version>12.1.0.2</version>
        </dependency>


        <!--poi选择3.2之前的，否则会报错【Region引入不进来】-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.1-FINAL</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.10</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>6</source>
                    <target>6</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

核心java文件

ConnectionOracle.java

import java.sql.*;

public class ConnectionOracle {
    String sd = "oracle.jdbc.driver.OracleDriver";
    String sc = "jdbc:oracle:thin:@IP地址:端口:数据库名";
    String userName = "账号";
    String password = "密码";

    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    public ConnectionOracle() {
        try {
            Class.forName(sd);
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }
    }

    public ResultSet executeQuery(String sql) throws SQLException {
        con = DriverManager.getConnection(sc, userName, password);
        Statement stmt = con.createStatement();
        rs = stmt.executeQuery(sql);
        return rs;
    }

    public void executeUpdate(String sql) throws SQLException {
        con = DriverManager.getConnection(sc, userName, password);
        Statement stmt = con.createStatement();
        stmt.executeUpdate(sql);
    }

    public void close() throws SQLException {
        if (rs != null)
            rs.close();
        if (stmt != null)
            stmt.close();
        if (con != null)
            con.close();
    }
}

DataToExcel.java

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;


public class DataToExcel {

    public static void main(String[] args) {
        String result = "";
        List listAll = new ArrayList();
        System.out.println("正在读取数据库中所有的表");
        try {
            List tableList = getTableList();
            System.out.println("数据库表读取完成");
            for (int i = 0; i < tableList.size(); i++) {
                String[] strings = (String[]) tableList.get(i);
                String tableName = strings[0].toString();
                List list = new ArrayList();
                list.add(tableName);
                list.add(getStructOfTable(tableName));
                System.out.println("正在生成表" + tableName + "的结构");
                listAll.add(list);
            }
            result = TableStructInfoToExcel(listAll, "D:");
            System.out.println("数据库中表结构导入已完成");
        } catch (Exception e) {
            e.printStackTrace();
            File file = new File(e.getMessage().toString());
            if (file.exists()) {
                file.delete();
            }
        }
        System.out.println(result);
    }


    /**
     * 获取数据库中所有的表
     */
    public static List getTableList() {
        String sql = "select object_name From user_objects Where object_type='TABLE'";
        return getResult(sql, 1);
    }

    public static List getStructOfTable(String tableName) {
        String sql = "SELECT u.column_name,u.data_type,u.data_length,u.data_precision,u.data_Scale,u.nullable,u.data_default,c.comments FROM user_tab_columns u,user_col_comments c" +

                " WHERE u.table_name='" + tableName + "' and u.table_name=c.table_name and c.column_name=u.column_name";
        return getResult(sql, 8);
    }

    /**
     * 获取结果的公用类
     */

    public static List getResult(String sql, int length) {
        List list = new ArrayList();
        ResultSet rs = null;
        ConnectionOracle c = new ConnectionOracle();
        try {
            rs = c.executeQuery(sql);
            while (rs.next()) {
                String[] string = new String[length];
                for (int i = 1; i < length + 1; i++) {
                    string[i - 1] = rs.getString(i);
                }
                list.add(string);
            }
            c.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 输出对应list中的数据
     */
    public static void showView(List list) {
        for (Iterator iterator = list.iterator(); iterator.hasNext(); ) {
            String[] name = (String[]) iterator.next();
            for (int i = 0; i < name.length; i++) {
                System.out.println(name[i]);
            }
        }
    }

    /**
     * 将数据导入到excel中
     */

    public static String TableStructInfoToExcel(List list, String path) throws Exception {
        String FileName = "";
        FileOutputStream fos = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        HSSFCellStyle style = null;
        HSSFFont font = null;
        int currentRowNum = 0;
        String[] tableFiled = {"column_name", "data_type", "data_length", "data_precision", "data_Scale", "nullable", "data_default", "comments"};
        try {
            FileName = path + "\\" + "表结构.xls";
            fos = new FileOutputStream(FileName);
            //创建新的sheet并设置名称
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet s = wb.createSheet();
            wb.setSheetName(0, "表结构");
            style = wb.createCellStyle();
            font = wb.createFont();
            for (int z = 0; z < list.size(); z++) {
                List listBean = (List) list.get(z);
                //新建一行,再在行上面新建一列
                row = s.createRow(currentRowNum);
                int pad = currentRowNum;
                currentRowNum++;
                //设置样式
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //字体加粗
                style.setFont(font);
                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
                style.setFillForegroundColor((short) 13);// 设置背景色
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
                style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                for (int i = 0; i < tableFiled.length; i++) {
                    cell = row.createCell((short) i);
                    cell.setCellValue("");
                    cell.setCellStyle(style);
                }
                row.getCell((short) 0).setCellValue("数据库表" + listBean.get(0).toString() + "的结构");
                //创建第二行
                row = s.createRow(currentRowNum);
                currentRowNum++;
                for (int i = 0; i < tableFiled.length; i++) {
                    //创建多列并设置每一列的值和宽度
                    cell = row.createCell((short) i);
                    cell.setCellValue(new HSSFRichTextString(tableFiled[i]));
                    s.setColumnWidth((short) i, (short) 5000);
                }
                List list2 = (List) listBean.get(1);
                for (int i = 0; i < list2.size(); i++) {
                    row = s.createRow(currentRowNum);
                    currentRowNum++;
                    String[] strings = (String[]) list2.get(i);
                    for (int j = 0; j < strings.length; j++) {
                        cell = row.createCell((short) j);
                        cell.setCellValue(new HSSFRichTextString(strings[j]));
                    }
                }
                //合并单元格
                s.addMergedRegion(new Region(pad, (short) 0, pad, (short) (tableFiled.length - 1)));
                currentRowNum++;
            }
            wb.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
            fos.close();
            throw new Exception(FileName);
        }
        return FileName;
    }
}

过程遇到的问题及解决方案

依赖poi版本过高会出现“org.apache.poi.hssf.util.Region”报错
- 问题原因
  - 从POI 3.18开始被Deprecated，在3.20版本中被移除了，所以3.20以前的都有
- 解决方案
  - pom配置，降低其版本
```
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.1-FINAL</version>
</dependency>
```
jdbc-oracle开始没有引入执行报错
- 问题原因
  - 由于Oracle授权问题，Maven不提供Oracle JDBC driver，为了在Maven项目中应用Oracle JDBC driver,必须手动添加到本地仓库
- 解决方案
  1. 首先需要到Oracle官网上下载ojdbc的jar包【http://www.oracle.com/technetwork/database/features/jdbc/default-2280470.html】或【https://download.youkuaiyun.com/download/eieiei438/12443411】
  2. 下载ojdbc7-12.1.0.2.jar
  3. 把该jar文件放到自己仓库目录中的“com\oracle\ojdbc7\12.1.0.2”目录下，如果有冲突就把旧的删除（注：无效的jar包大小应该是1KB,正常的大小为3613KB）
  4. pom文件添加依赖
```
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc7</artifactId>
    <version>12.1.0.2</version>
</dependency>
```

其他问题

Settings.xml配置

阿里的

<?xml version="1.0" encoding="UTF-8"?>
<settings
    xmlns="http://maven.apache.org/SETTINGS/1.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/SETTINGS/1.0.0 http://maven.apache.org/xsd/settings-1.0.0.xsd">
    <localRepository>D:\software\repositoryMaven</localRepository>
    <pluginGroups></pluginGroups>
    <proxies></proxies>
    <servers></servers>
    <mirrors>
        <mirror>
            <id>nexus</id>
            <mirrorOf>*</mirrorOf>
            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
        </mirror>
    </mirrors>
    <profiles></profiles>
</settings>