createSelectSql

package createSelectSql;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

/**
 * 指定したスキーマのselect文を出力する。
 */
public class CreateSelectSql {

    public static void main(String[] args) {

        Connection conn = null;
        ResultSet rs = null;
        CreateSelectSqlProcess process = new CreateSelectSqlProcess();

        try {
            // 出力ファイル生成
            File file = new File(CreateSelectSqlProperty.outputFilePath);
            if (file.exists()) {
                file.delete();
            }
            BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(CreateSelectSqlProperty.outputFilePath), StandardCharsets.UTF_8));

            // 接続
            Properties props = new Properties();
            props.setProperty("user", CreateSelectSqlProperty.user);
            props.setProperty("password", CreateSelectSqlProperty.pass);
            conn = DriverManager.getConnection(CreateSelectSqlProperty.url, props);
            Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            // 対象スキーマのテーブル一覧取得
            List<String> tableList = process.getTableList(st);

            for (String table : tableList) {
                process.outputSql(bw, st, table);
            }

            conn.commit();
            bw.close();

        } catch (SQLException | IOException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

}

package createSelectSql;

import java.io.BufferedWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CreateSelectSqlProcess {

    /**
     * 対象スキーマのテーブル一覧を取得
     *
     * @param st
     * @return
     * @throws SQLException
     */
    public List<String> getTableList(Statement st) throws SQLException {

        List<String> tableList = new ArrayList<>();

        // 対象スキーマのテーブル一覧を検索
        ResultSet rs = st.executeQuery("select tablename from pg_tables where tablename not like 'pg_%' and schemaname = '" + CreateSelectSqlProperty.schema + "';");

        while (rs.next()) {
            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                tableList.add(rs.getString(i));
            }
        }

        return tableList;
    }

    /**
     * selectSQLを出力する
     *
     * @param bw
     * @param table
     * @throws SQLException
     * @throws IOException
     */
    public void outputSql(BufferedWriter bw, Statement st, String table) throws SQLException, IOException {

        String sql = "select ";

        // 対象テーブルのカラム一覧を取得
        ResultSet rs = st.executeQuery("select * from information_schema.columns where table_schema = '" + CreateSelectSqlProperty.schema + "' and table_name = '" + table
                + "' order by ordinal_position;");

        ResultSetMetaData rsmd = rs.getMetaData();

        while (rs.next()) {
            String colName = "";
            String colType = "";

            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                if ("column_name".equals(rsmd.getColumnName(i))) {
                    colName = rs.getString(i);

                } else if ("column_name".equals(rsmd.getColumnName(i))) {
                    colType = rsmd.getColumnTypeName(i);
                }
            }

            // select col1,
            sql += colName + ",";
        }

        // 最後の「,」を削除
        sql = sql.substring(0, sql.length() - 1);

        // select col1,col2,....col10 from [shcema].[table] where 1=1 ;
        sql += " from " + CreateSelectSqlProperty.schema + "." + table + " where 1=1 ;";

        bw.write(sql);
        bw.newLine();
    }

}

package createSelectSql;

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.Properties;

public class CreateSelectSqlProperty {

    /* プロパティファイルパス */
    public static final String PROPERTIES_FILE_PATH = "CreateSelectSql.properties";

    /* プロパティ：key */
    public static final String URL = "url";
    public static final String USER = "user";
    public static final String PASS = "pass";
    public static final String DB = "db";
    public static final String SCHEMA = "schema";
    public static final String OUTPUT_FILE_PATH = "outputFilePath";

    public static String url;
    public static String user;
    public static String pass;
    public static String db;
    public static String schema;
    public static String outputFilePath;

    static {
        Properties properties = new Properties();
        try {
            properties.load(Files.newBufferedReader(Paths.get(PROPERTIES_FILE_PATH), StandardCharsets.UTF_8));
            url = properties.getProperty(URL);
            user = properties.getProperty(USER);
            pass = properties.getProperty(PASS);
            db = properties.getProperty(DB);
            schema = properties.getProperty(SCHEMA);
            outputFilePath = properties.getProperty(OUTPUT_FILE_PATH);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

url=jdbc:postgresql://localhost:5432/postgres
user=postgres
pass=postgres
db=postgres
schema=sample
outputFilePath=select.sql