java怎么生成sql脚本文件-编程学习网

这篇文章将为大家详细讲解有关java怎么生成sql脚本文件，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

生成 SQL 脚本文件

1. 使用命令行工具

java -jar hsqldb.jar --sql <database_name> > <output_file>.sql

注：hsqldb.jar 是 HSQLDB 数据库引擎的 JAR 文件。

2. 使用 Java API

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;

public class GenerateSqlScript {

    public static void main(String[] args) {
        // 数据库连接信息
        String url = "jdbc:hsqldb:mem:test";
        String user = "SA";
        String password = "";

        Connection conn = null;
        DatabaseMetaData dbmd = null;
        BufferedWriter writer = null;

        try {
            // 建立数据库连接
            conn = DriverManager.getConnection(url, user, password);

            // 获取数据库元数据
            dbmd = conn.getMetaData();

            // 打开文件输出流
            File file = new File("test.sql");
            writer = new BufferedWriter(new FileWriter(file));

            // 生成 SQL 脚本
            writer.write("-- 生成时间：" + new java.util.Date() + "
");
            writer.write("
");
            writer.write("-- 表
");
            for (ResultSet rs = dbmd.getTables(null, null, "%", new String[] {"TABLE"}); rs.next(); ) {
                String tableName = rs.getString("TABLE_NAME");
                writer.write("CREATE TABLE " + tableName + " (
");
                for (ResultSet colRs = dbmd.getColumns(null, null, tableName, "%"); colRs.next(); ) {
                    String columnName = colRs.getString("COLUMN_NAME");
                    String columnType = colRs.getString("TYPE_NAME");
                    writer.write("	" + columnName + " " + columnType + ",
");
                }
                writer.write(");
");
                writer.write("
");
            }

            writer.write("-- 索引
");
            for (ResultSet rs = dbmd.getIndexInfo(null, null, "%", false, false); rs.next(); ) {
                String indexName = rs.getString("INDEX_NAME");
                String tableName = rs.getString("TABLE_NAME");
                String columnName = rs.getString("COLUMN_NAME");
                writer.write("CREATE INDEX " + indexName + " ON " + tableName + " (" + columnName + ");
");
                writer.write("
");
            }

            writer.write("-- 触发器
");
            for (ResultSet rs = dbmd.getTriggers(null, null, "%", new String[] {"TRIGGER"}); rs.next(); ) {
                String triggerName = rs.getString("TRIGGER_NAME");
                String tableName = rs.getString("TABLE_NAME");
                writer.write("CREATE TRIGGER " + triggerName + " ON " + tableName + " FOR " + rs.getString("TRIGGER_TYPE") + " AS
");
                writer.write(rs.getString("TRIGGER_BODY") + ";
");
                writer.write("
");
            }

            // 关闭资源
            writer.close();
            conn.close();
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (writer != null) {
                    writer.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException | IOException e) {
                e.printStackTrace();
            }
        }
    }
}

3. 使用第三方库