設定したselect文からDBアクセスしてdelete/insert文発行
DataExport
package dataexport;
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.nio.file.Files;
import java.nio.file.Paths;
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;
public class DataExport {
public static void main(String[] args) {
Connection conn = null;
ResultSet rs = null;
DataExportProcess process = new DataExportProcess();
try {
// 出力ファイル生成
File file = new File(PropertiesValue.outputFilePath);
if (file.exists()) {
file.delete();
}
BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(PropertiesValue.outputFilePath), StandardCharsets.UTF_8));
// 接続
Properties props = new Properties();
props.setProperty("user", PropertiesValue.user);
props.setProperty("password", PropertiesValue.pass);
conn = DriverManager.getConnection(PropertiesValue.url, props);
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
// 実行SQL／inputSql.sql(外部ファイル)読み込み
List<String> readLines = Files.readAllLines(Paths.get(PropertiesValue.inputSql), StandardCharsets.UTF_8);
for (String sql : readLines) {
if (sql.trim().isEmpty() == false) {
// sql加工
sql = sql.replace(PropertiesValue.repStrSchema, PropertiesValue.schema) + ";";
// sql実行
rs = st.executeQuery(sql);
// delete文生成
process.createDeleteSql(bw, conn, rs, sql);
// カーソルをResultSetの先端直前に移動
rs.beforeFirst();
// insert文生成
process.createInsertSql(bw, conn, rs, sql);
}
}
// conn.commit();
bw.close();
} catch (SQLException | IOException ex) {
ex.printStackTrace();
} finally {
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
DataExportProcess
package dataexport;
import java.io.BufferedWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;
public class DataExportProcess {
/**
*
* delete文生成
*
* @param bw
* @param conn
* @param rs
* @param sql
* @throws SQLException
* @throws IOException
*/
public void createDeleteSql(BufferedWriter bw, Connection conn, ResultSet rs, String sql) throws SQLException, IOException {
DatabaseMetaData dmd = conn.getMetaData();
ResultSetMetaData rsmd = rs.getMetaData();
List<String> pkList = new ArrayList<String>();
// PKの取得
String schema = sql.split("from ")[1].split(Pattern.quote("."))[0].replace(";", "");
String tableName = sql.split("from ")[1].split(Pattern.quote("."))[1].split(" ")[0].replace(";", "");
ResultSet dbMtrs = dmd.getPrimaryKeys(PropertiesValue.db, schema, tableName);
while (dbMtrs.next()) {
pkList.add(dbMtrs.getString("COLUMN_NAME"));
}
// delete from [schema].[table] where 1=1
String commonSql = "delete from " + PropertiesValue.schema + "." + tableName + " where 1=1 ";
while (rs.next()) {
String sqlLine = commonSql;
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
// PKの場合抽出条件に追加
if (pkList.contains(rsmd.getColumnName(i))) {
// delete from [schema].[table] where 1=1 and [column] = '[colVal]'
sqlLine += " and " + rsmd.getColumnName(i) + " = '" + rs.getString(i) + "'";
}
}
sqlLine += ";";
bw.write(sqlLine);
bw.newLine();
}
}
/**
*
* insert文生成
*
* @param bw
* @param conn
* @param rs
* @param sql
* @throws SQLException
* @throws IOException
*/
public void createInsertSql(BufferedWriter bw, Connection conn, ResultSet rs, String sql) throws SQLException, IOException {
ResultSetMetaData rsmd = rs.getMetaData();
// カラム名取得
String schema = sql.split("from ")[1].split(Pattern.quote("."))[0].replace(";", "");
String tableName = sql.split("from ")[1].split(Pattern.quote("."))[1].split(" ")[0].replace(";", "");
// insert into [schema].[table] (
String commonSql = "insert into " + schema + "." + tableName + " (";
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
// insert into [schema].[table] ([column],
commonSql = commonSql + rsmd.getColumnName(i) + ",";
}
// 最後の","を削除
commonSql = commonSql.substring(0, commonSql.length() - 1);
// insert into [schema].[table] ([column],[column]...[column]) values (
commonSql = commonSql + ") values (";
while (rs.next()) {
String sqlLine = commonSql;
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
// カラムの値/型によって分岐
if (rs.getString(i) == null) {
// null,
sqlLine += rs.getString(i) + ",";
}else if("text".equals(rsmd.getColumnTypeName(i))) {
// '[colVal]',
sqlLine += "'" + rs.getString(i) + "',";
} else {
// '[colVal]',
sqlLine += "'" + rs.getString(i) + "',";
}
}
// 最後の","を削除
sqlLine = sqlLine.substring(0, sqlLine.length() - 1);
sqlLine += ");";
bw.write(sqlLine);
bw.newLine();
}
}
}
PropertiesValue
package dataexport;
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 PropertiesValue {
/* プロパティファイルパス */
public static final String PROPERTIES_FILE_PATH = "DataExport.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 INPUT_SQL = "inputSql";
public static final String OUTPUT_FILE_PATH = "outputFilePath";
public static final String REP_STR_SCHEMA = "repStrSchema";
public static String url;
public static String user;
public static String pass;
public static String db;
public static String schema;
public static String inputSql;
public static String outputFilePath;
public static String repStrSchema;
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);
inputSql = properties.getProperty(INPUT_SQL);
outputFilePath = properties.getProperty(OUTPUT_FILE_PATH);
repStrSchema = properties.getProperty(REP_STR_SCHEMA);
} catch (IOException e) {
e.printStackTrace();
}
}
}
DataExport.properties
url=jdbc:postgresql://localhost:5432/postgres
user=postgres
pass=postgres
db=postgres
schema=sample
inputSql=inputSql.sql
outputFilePath=out.sql
#スキーマ置換用
repStrSchema=【SCHEMA】