差分ツール

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Pattern;

import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JProgressBar;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.SwingUtilities;
import javax.swing.SwingWorker;
import javax.swing.border.LineBorder;
import javax.swing.event.ChangeEvent;
import javax.swing.event.ChangeListener;

import org.apache.commons.lang3.StringUtils;
import org.supercsv.io.CsvListWriter;
import org.supercsv.io.ICsvListWriter;
import org.supercsv.prefs.CsvPreference;
import org.supercsv.quote.AlwaysQuoteMode;
import org.supercsv.quote.NormalQuoteMode;

import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import com.opencsv.exceptions.CsvValidationException;

import jp.co.hirogin.batch_ikou_dff.error.ImportException;
import jp.co.hirogin.batch_ikou_dff.error.OutputPathException;

public class CsvToSQLiteApp extends JFrame {
	private static final String DB_URL = "jdbc:sqlite:example.db";
	private static final String BEFORE_TABLE = "before";
	private static final String AFTER_TABLE = "after";
	private static final String ADD_UPDATE_TABLE = "add_update";
	private static final String UPDATE_TABLE = "update_target";
	private static final int BATCH_SIZE = 1000;
	private int chunkSize = 10000; // 1万件ごとに分割するための定数
	private JTextField beforeDirPathField;
	private JTextField afterDirPathField;
	private JTextField outputCsvPathField;
	private JCheckBox headerCheckBox;
	/** 追加更新を分ける */
	private JCheckBox sortingCheckBox;
	private JCheckBox outPutHeaderCheckBox;
	private JLabel statusLabel;
	private JTextArea messageArea;
	private JProgressBar progressBar;
	/** 取込ボタン */
	private JButton importButton = new JButton(" 取込 ");
	/** 選択ボタン */
	private JButton selectButton = new JButton("カラム選択");
	/** 実行ボタン */
	private JButton runButton = new JButton(" 実行 ");
	/** クリアボタン */
	private JButton btnClear = new JButton("クリア");
	private JTextField numDiviField;
	private Pattern pattern = Pattern.compile("^[0-9]+$");
	// 出力の囲い文字設定用のフィールドを追加
	private JTextField outputQuoteField;
	// 出力ファイル名設定用のフィールドを追加
	private JTextField outputFileNameField;
	private CsvPreference preference = new CsvPreference.Builder('\"', ',', "\r\n")
			.useQuoteMode(new AlwaysQuoteMode())
			.build();
	/** カラムリスト */
	private List<String> headerList;
	/** カラム選択画面 */
	private CSVHeaderSelector frame;

	public CsvToSQLiteApp() {
		setTitle("CSV to SQLite App");
		setBounds(0, 100, 640, 750);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setLayout(new FlowLayout());
		setResizable(false);
		GridBagLayout layout = new GridBagLayout();
		GridBagConstraints gbc = new GridBagConstraints();

		//比較元ディレクトリパス の設定
		JLabel beforeDirPathLabel = new JLabel("比較元ディレクトリパス : ");
		beforeDirPathField = new JTextField(35);
		beforeDirPathField.setBorder(BorderFactory.createCompoundBorder(new LineBorder(Color.BLACK),
				BorderFactory.createEmptyBorder(1, 0, 1, 0)));
		JButton selectBeforeDirButton = new JButton("選択");
		selectBeforeDirButton.setPreferredSize(new Dimension(60, 20));
		JPanel p = new JPanel();
		p.setLayout(layout);
		p.setPreferredSize(new Dimension(620, 40));
		p.setLayout(new FlowLayout(FlowLayout.LEFT));
		p.setBorder(BorderFactory.createCompoundBorder(new LineBorder(new Color(220, 220, 220), 2, true),
				BorderFactory.createEmptyBorder(1, 0, 1, 0)));

		gbc.gridx = 0;
		gbc.gridy = 0;
		p.add(beforeDirPathLabel, gbc);

		gbc.gridx = 1;
		p.add(beforeDirPathField, gbc);

		gbc.gridx = 2;
		p.add(selectBeforeDirButton, gbc);
		getContentPane().add(p, BorderLayout.PAGE_START);

		JLabel afterDirPathLabel = new JLabel("比較先ディレクトリパス : ");
		afterDirPathField = new JTextField(35);
		afterDirPathField.setBorder(BorderFactory.createCompoundBorder(new LineBorder(Color.BLACK),
				BorderFactory.createEmptyBorder(1, 0, 1, 0)));
		JButton selectAfterDirButton = new JButton("選択");
		selectAfterDirButton.setPreferredSize(new Dimension(60, 20));

		JPanel p2 = new JPanel();
		p2.setLayout(layout);
		p2.setPreferredSize(new Dimension(620, 40));
		p2.setLayout(new FlowLayout(FlowLayout.LEFT));
		p2.setBorder(BorderFactory.createCompoundBorder(new LineBorder(new Color(220, 220, 220), 2, true),
				BorderFactory.createEmptyBorder(1, 0, 1, 0)));

		gbc.gridx = 0;
		gbc.gridy = 0;
		p2.add(afterDirPathLabel, gbc);

		gbc.gridx = 1;
		p2.add(afterDirPathField, gbc);

		gbc.gridx = 2;
		p2.add(selectAfterDirButton, gbc);
		getContentPane().add(p2, BorderLayout.PAGE_START);

		//ヘッダーチェックBOX
		headerCheckBox = new JCheckBox("取込ファイルの1行目をヘッダー行とする");
		headerCheckBox.setSelected(true);

		JPanel checkP = new JPanel();
		checkP.setLayout(layout);
		checkP.setPreferredSize(new Dimension(620, 40));
		checkP.setLayout(new FlowLayout(FlowLayout.LEFT));
		gbc.gridx = 1;
		checkP.add(headerCheckBox, gbc);
		getContentPane().add(checkP, BorderLayout.PAGE_START);

		JPanel buttonI = new JPanel();
		buttonI.setBorder(BorderFactory.createEmptyBorder(0, 10, 20, 20));
		buttonI.setLayout(new GridBagLayout());
		buttonI.setPreferredSize(new Dimension(620, 40));
		buttonI.setLayout(new FlowLayout(FlowLayout.CENTER));
		buttonI.add(importButton, gbc);
		getContentPane().add(buttonI, BorderLayout.PAGE_START);

		JLabel outputCsvPathLabel = new JLabel("出力先ディレクトリパス : ");
		outputCsvPathField = new JTextField(35);
		JButton diffButton = new JButton("選択");
		diffButton.setPreferredSize(new Dimension(60, 20));

		JPanel p3 = new JPanel();
		p3.setLayout(layout);

		p3.setPreferredSize(new Dimension(620, 40));
		p3.setLayout(new FlowLayout(FlowLayout.LEFT));
		p3.setBorder(BorderFactory.createCompoundBorder(new LineBorder(new Color(220, 220, 220), 2, true),
				BorderFactory.createEmptyBorder(1, 0, 1, 0)));

		gbc.gridx = 0;
		gbc.gridy = 0;
		p3.add(outputCsvPathLabel, gbc);

		gbc.gridx = 1;
		p3.add(outputCsvPathField, gbc);

		gbc.gridx = 2;
		p3.add(diffButton, gbc);
		getContentPane().add(p3, BorderLayout.PAGE_START);

		JPanel outLine = new JPanel();
		outLine.setLayout(layout);
		outLine.setLayout(new FlowLayout(FlowLayout.LEFT));
		outLine.setBorder(BorderFactory.createCompoundBorder(new LineBorder(new Color(220, 220, 220), 2, true),
				BorderFactory.createEmptyBorder(0, 0, 0, 0)));

		// 出力の囲い文字設定用のコンポーネントを追加
		JLabel outputQuoteLabel = new JLabel("出力の囲い文字:");
		outputQuoteLabel.setBorder(BorderFactory.createEmptyBorder(2, 0, 0, 2));
		outputQuoteField = new JTextField(10);
		outputQuoteField.setPreferredSize(new Dimension(20, 20));
		outputQuoteField.setText("\""); // デフォルト値を設定
		JPanel p4 = new JPanel();

		p4.setPreferredSize(new Dimension(298, 40));
		p4.setLayout(new FlowLayout(FlowLayout.LEFT));
		p4.setBorder(BorderFactory.createEmptyBorder(2, 0, 0, 2));
		p4.add(outputQuoteLabel, gbc);
		p4.add(outputQuoteField, gbc);
		getContentPane().add(p4, BorderLayout.PAGE_START);

		// 出力ファイル名設定用のコンポーネントを追加
		JLabel outputFileNameLabel = new JLabel("出力ファイル名:");
		outputFileNameField = new JTextField(15);
		outputFileNameField.setPreferredSize(new Dimension(20, 20));
		outputFileNameField.setText("output");
		JPanel p5 = new JPanel();
		p5.setPreferredSize(new Dimension(297, 40));
		p5.setLayout(new FlowLayout(FlowLayout.LEFT));
		p5.setBorder(BorderFactory.createEmptyBorder(2, 2, 0, 2));
		p5.add(outputFileNameLabel, gbc);
		p5.add(outputFileNameField, gbc);
		getContentPane().add(outLine, BorderLayout.PAGE_START);
		outLine.add(p4);
		outLine.add(p5);

		//分割数
		JPanel n5 = new JPanel();
		n5.setPreferredSize(new Dimension(620, 40));
		n5.setLayout(new FlowLayout(FlowLayout.LEFT));
		n5.setBorder(BorderFactory.createCompoundBorder(new LineBorder(new Color(220, 220, 220), 2, true),
				BorderFactory.createEmptyBorder(1, 0, 1, 0)));

		JLabel numLabel = new JLabel("分割件数:");
		numDiviField = new JTextField(14);
		numDiviField.setPreferredSize(new Dimension(20, 20));
		numDiviField.setText("10000");
		n5.add(numLabel, gbc);
		n5.add(numDiviField, gbc);
		getContentPane().add(n5, BorderLayout.PAGE_START);

		outPutHeaderCheckBox = new JCheckBox("出力ファイルにヘッダーを出力する");
		outPutHeaderCheckBox.setSelected(true);
		JPanel checkP2 = new JPanel();
		checkP2.setLayout(layout);
		checkP2.setPreferredSize(new Dimension(620, 40));
		checkP2.setLayout(new FlowLayout(FlowLayout.LEFT));
		checkP2.add(outPutHeaderCheckBox, gbc);
		sortingCheckBox = new JCheckBox("追加更新を分ける");
		sortingCheckBox.setSelected(false);
		sortingCheckBox.setVisible(false);
		checkP2.add(sortingCheckBox, gbc);
		JPanel spacer = new JPanel();
		spacer.setPreferredSize(new Dimension(140, 40));
		checkP2.add(spacer);
		selectButton.setVisible(false);
		checkP2.add(selectButton, gbc);
		getContentPane().add(checkP2, BorderLayout.PAGE_START);

		statusLabel = new JLabel("進捗状況:");
		progressBar = new JProgressBar();
		progressBar.setStringPainted(true);
		progressBar.setPreferredSize(new Dimension(580, 20));
		JPanel barP = new JPanel();
		barP.setPreferredSize(new Dimension(600, 50));
		barP.setLayout(new FlowLayout(FlowLayout.LEFT));
		barP.add(statusLabel, gbc);
		barP.add(progressBar, gbc);
		getContentPane().add(barP, BorderLayout.PAGE_START);

		//メッセージエリア
		JPanel msgP = new JPanel();
		msgP.setPreferredSize(new Dimension(620, 200));
		msgP.setBorder(BorderFactory.createEmptyBorder(0, 0, 0, 0));
		msgP.setLayout(new FlowLayout(FlowLayout.LEFT));
		messageArea = new JTextArea(600, 50);
		messageArea.setEditable(false);
		JLabel msgLabel = new JLabel("メッセージ:");
		JScrollPane messageScrollPane = new JScrollPane(messageArea);
		messageScrollPane.setPreferredSize(new Dimension(600, 150));
		msgP.add(msgLabel, gbc);
		msgP.add(messageScrollPane, gbc);
		getContentPane().add(msgP, BorderLayout.PAGE_START);

		JPanel buttonP = new JPanel();
		buttonP.setBorder(BorderFactory.createEmptyBorder(0, 10, 20, 20));
		buttonP.setLayout(new GridBagLayout());
		buttonP.setPreferredSize(new Dimension(620, 40));
		buttonP.setLayout(new FlowLayout(FlowLayout.RIGHT));
		runButton.setMargin(new Insets(0, 10, 0, 10));
		buttonP.add(runButton, gbc);

		btnClear.setMargin(new Insets(0, 10, 0, 10));
		buttonP.add(btnClear, gbc);
		getContentPane().add(buttonP, BorderLayout.PAGE_START);

		/** 比較元ディレクトリ選択ボタンのアクション */
		selectBeforeDirButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				JFileChooser fileChooser = new JFileChooser();
				fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
				int result = fileChooser.showOpenDialog(null);
				if (result == JFileChooser.APPROVE_OPTION) {
					File selectedDir = fileChooser.getSelectedFile();
					beforeDirPathField.setText(selectedDir.getAbsolutePath());
				}
			}
		});

		/** 比較先ディレクトリ選択ボタンのアクション */
		selectAfterDirButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				JFileChooser fileChooser = new JFileChooser();
				fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
				int result = fileChooser.showOpenDialog(null);
				if (result == JFileChooser.APPROVE_OPTION) {
					File selectedDir = fileChooser.getSelectedFile();
					afterDirPathField.setText(selectedDir.getAbsolutePath());
				}
			}
		});

		/** 出力先ディレクトリ選択ボタンのアクション */
		diffButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				JFileChooser fileChooser = new JFileChooser();
				fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
				int result = fileChooser.showOpenDialog(null);
				if (result == JFileChooser.APPROVE_OPTION) {
					File selectedDir = fileChooser.getSelectedFile();
					outputCsvPathField.setText(selectedDir.getAbsolutePath());
				}
			}
		});

		/** クリアボタンのアクション */
		btnClear.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				messageArea.setText("");
				updateProgress(0);
			}
		});

		/** 実行ボタンのアクション */
		runButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				String beforeDirPath = beforeDirPathField.getText();
				String afterDirPath = afterDirPathField.getText();
				String outputCsvPath = outputCsvPathField.getText();
				boolean hasHeader = headerCheckBox.isSelected();
				boolean sortingFlag = sortingCheckBox.isSelected();
				String outputQuote = outputQuoteField.getText();
				String outputFileName = outputFileNameField.getText();
				String inputChunkSize = numDiviField.getText();
				List<String> selectList = !Objects.isNull(frame) ? frame.getSelectedList() : new ArrayList<String>();
				List<String> columns = new ArrayList<>();
				for (String header : selectList) {
					columns.add("\"" + header.replaceAll("\"", "\"\"") + "\"");
				}

				if (StringUtils.isNotEmpty(inputChunkSize) && pattern.matcher(inputChunkSize).matches()) {
					chunkSize = Integer.valueOf(inputChunkSize);
				}

				if (StringUtils.isNotEmpty(outputQuote)) {
					preference = new CsvPreference.Builder(outputQuote.charAt(0), ',', "\r\n")
							.useQuoteMode(new AlwaysQuoteMode())
							.build();
				} else {
					preference = new CsvPreference.Builder('\"', ',', "\r\n")
							.useQuoteMode(new NormalQuoteMode())
							.build();
				}

				SwingWorker<Void, Integer> worker = new SwingWorker<Void, Integer>() {
					@Override
					protected Void doInBackground() throws Exception {
						updateProgress(0);

						// データベースファイルの削除
						File dbFile = new File("example.db");
						if (dbFile.exists()) {
							dbFile.delete();
						}

						try (Connection conn = DriverManager.getConnection(DB_URL)) {
							importCsvToTable(conn, beforeDirPath, "before", hasHeader);
							publish(33);
							importCsvToTable(conn, afterDirPath, "after", hasHeader);
							publish(50);
							createIndexes(conn, "before");
							createIndexes(conn, "after");
							publish(66);
							creatAddUpdateTable(conn);
							//差分比較
							exportDiffToCsv(conn, "before", "after", outputCsvPath, outputFileName);
							//追加更新を分ける場合
							if (sortingFlag) {
								if (selectList.isEmpty()) {
									JOptionPane.showMessageDialog(null, "追加更新を分ける場合キー項目を選択してください");
									return null;
								}
								exportDiffDelToAddUpdateCsv(conn, columns, "after", "before", outputCsvPath,
										outputFileName);
							} else {
								//追加更新比較
								exportDiffDelToCsv(conn, "after", "before", outputCsvPath, outputFileName);
							}
							publish(100);
						} catch (SQLException ex) {
							ex.printStackTrace();
							appendMessage("CSVファイルの処理中にエラーが発生しました: " + ex.getMessage());
							JOptionPane.showMessageDialog(null, "CSVファイルの処理中にエラーが発生しました: " + ex.getMessage());
						} catch (OutputPathException e) {
							JOptionPane.showMessageDialog(null, "出力先が入力されていません");
						}
						return null;
					}

					@Override
					protected void process(List<Integer> chunks) {
						int latestProgress = chunks.get(chunks.size() - 1);
						updateProgress(latestProgress);
					}

					@Override
					protected void done() {
						appendMessage("処理完了");
					}
				};

				worker.execute();
			}
		});

		/** 取込ボタンのアクション */
		importButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				String beforeDirPath = beforeDirPathField.getText();
				String afterDirPath = afterDirPathField.getText();
				boolean hasHeader = headerCheckBox.isSelected();
				String outputQuote = outputQuoteField.getText();
				String inputChunkSize = numDiviField.getText();
				if (StringUtils.isNotEmpty(inputChunkSize) && pattern.matcher(inputChunkSize).matches()) {
					chunkSize = Integer.valueOf(inputChunkSize);
				}

				if (StringUtils.isNotEmpty(outputQuote)) {
					preference = new CsvPreference.Builder(outputQuote.charAt(0), ',', "\r\n")
							.useQuoteMode(new AlwaysQuoteMode())
							.build();
				} else {
					preference = new CsvPreference.Builder('\"', ',', "\r\n")
							.useQuoteMode(new NormalQuoteMode())
							.build();
				}

				SwingWorker<Void, Integer> worker = new SwingWorker<Void, Integer>() {
					@Override
					protected Void doInBackground() throws Exception {
						updateProgress(0);

						// データベースファイルの削除
						File dbFile = new File("example.db");
						if (dbFile.exists()) {
							dbFile.delete();
						}

						try (Connection conn = DriverManager.getConnection(DB_URL)) {
							headerList = new ArrayList<>();
							importCsvToTable(conn, beforeDirPath, "before", hasHeader);
							publish(50);
							importCsvToTable(conn, afterDirPath, "after", hasHeader);
							publish(66);
							createIndexes(conn, "before");
							createIndexes(conn, "after");
							creatAddUpdateTable(conn);
							sortingCheckBox.setVisible(true);
							publish(100);
						} catch (ImportException | SQLException ex) {
							ex.printStackTrace();
							appendMessage("CSVファイルの処理中にエラーが発生しました: " + ex.getMessage());
							JOptionPane.showMessageDialog(null, "CSVファイルの処理中にエラーが発生しました: " + ex.getMessage());
						} catch (Exception e) {
							e.printStackTrace();
						}
						return null;
					}

					@Override
					protected void process(List<Integer> chunks) {
						int latestProgress = chunks.get(chunks.size() - 1);
						updateProgress(latestProgress);
					}

					@Override
					protected void done() {
						appendMessage("処理完了");
					}
				};

				worker.execute();
			}
		});

		/** 選択ボタンのアクション */
		selectButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				SwingWorker<Void, Integer> worker = new SwingWorker<Void, Integer>() {

					@Override
					protected Void doInBackground() throws Exception {
						if (StringUtils.isEmpty(beforeDirPathField.getText())
								|| StringUtils.isEmpty(afterDirPathField.getText())) {
							JOptionPane.showMessageDialog(null, "取込が実行されていません");
							return null;
						}

						frame = new CSVHeaderSelector(headerList);

						frame.setVisible(true);
						return null;
					}

					@Override
					protected void process(List<Integer> chunks) {
						int latestProgress = chunks.get(chunks.size() - 1);
						updateProgress(latestProgress);
					}

					@Override
					protected void done() {
						appendMessage("処理完了");
					}
				};
				worker.execute();
			}
		});

		/**比較対象のカラムを選択するチェックボックスを選択した場合に実行されるイベント*/
		sortingCheckBox.getModel().addChangeListener(new ChangeListener() {
			@Override
			public void stateChanged(ChangeEvent e) {
				boolean isSelected = sortingCheckBox.isSelected();
				selectButton.setVisible(isSelected);
			}
		});
	}

	private void importCsvToTable(Connection conn, String csvDirPath, String tableName, boolean hasHeader)
			throws SQLException {
		File folder = new File(csvDirPath);
		File[] listOfFiles = folder.listFiles((dir, name) -> name.toUpperCase().endsWith(".CSV"));

		if (listOfFiles == null) {
			JOptionPane.showMessageDialog(null, "指定されたディレクトリにCSVファイルが見つかりません: " + csvDirPath);
			throw new ImportException("ディレクトリパスが見つかりませんでした");
		}

		conn.setAutoCommit(false);

		try (Statement stmt = conn.createStatement()) {
			stmt.execute("DROP TABLE IF EXISTS " + tableName);
		}

		for (File file : listOfFiles) {
			try (CSVReader csvReader = new CSVReaderBuilder(
					new InputStreamReader(new FileInputStream(file), Charset.forName("MS932")))
							.withSkipLines(0)
							.build()) {
				String[] headers = hasHeader ? csvReader.readNext() : generateDefaultHeaders(csvReader.peek());
				if (headers == null) {
					continue;
				}

				while (headers[0].startsWith("#")) {
					headers = csvReader.readNext();
					if (headers == null) {
						break;
					}
				}
				headerList = Arrays.asList(headers);
				createTable(conn, tableName, headers);

				String insertSQL = generateInsertSQL(tableName, headers);
				try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
					String[] row;
					int count = 0;
					while ((row = csvReader.readNext()) != null) {
						if (row[0].startsWith("#")) {
							continue;
						}
						for (int i = 0; i < row.length; i++) {
							pstmt.setString(i + 1, row[i]);
						}
						pstmt.addBatch();
						if (++count % BATCH_SIZE == 0) {
							pstmt.executeBatch();
							conn.commit();
						}
					}
					pstmt.executeBatch(); // 残りのバッチを実行
					conn.commit();
				}
				appendMessage("CSVファイルをインポートしました: " + file.getName());
			} catch (IOException | CsvValidationException | SQLException e) {
				conn.rollback();
				e.printStackTrace();
				appendMessage("ファイルの処理中にエラーが発生しました " + file.getName() + ": " + e.getMessage());
			}
		}

		conn.setAutoCommit(true);
	}

	/** 追加更新用テーブルを作成する*/
	private void creatAddUpdateTable(Connection conn)
			throws SQLException {

		conn.setAutoCommit(false);

		//追加更新用のテーブルを作成する
		String sql = String.format("SELECT * FROM %s EXCEPT SELECT * FROM %s", AFTER_TABLE, BEFORE_TABLE);
		List<Map<String, String>> dffList = new ArrayList<>();
		try (Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql)) {
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			String[] headerRow = new String[columnCount];
			for (int i = 1; i <= columnCount; i++) {
				headerRow[i - 1] = metaData.getColumnName(i);
			}
			while (rs.next()) {
				Map<String, String> resultMap = new HashMap<>();
				for (int i = 1; i <= columnCount; i++) {
					String colname = metaData.getColumnName(i);
					resultMap.put(colname, rs.getString(colname));
				}
				dffList.add(resultMap);
			}
			stmt.execute("DROP TABLE IF EXISTS " + ADD_UPDATE_TABLE);
			createTable(conn, ADD_UPDATE_TABLE, headerRow);
			createIndexes(conn, ADD_UPDATE_TABLE);
			conn.commit();
			String insertSQL = generateInsertSQL(ADD_UPDATE_TABLE, headerRow);
			try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
				int count = 0;
				for (Map<String, String> resultMap : dffList) {
					for (int i = 1; i <= columnCount; i++) {
						String colname = headerRow[i - 1];
						pstmt.setString(i, resultMap.get(colname));
					}
					pstmt.addBatch();
					if (++count % BATCH_SIZE == 0) {
						pstmt.executeBatch();
					}
				}
				pstmt.executeBatch();
				conn.commit();
			}
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
			appendMessage("比較中にエラーが発生しました " + ": " + e.getMessage());
		}
		conn.commit();
		conn.setAutoCommit(true);
	}

	private String[] generateDefaultHeaders(String[] firstRow) {
		String[] headers = new String[firstRow.length];
		for (int i = 0; i < firstRow.length; i++) {
			headers[i] = "column" + (i + 1);
		}
		return headers;
	}

	private void createTable(Connection conn, String tableName, String[] headers) throws SQLException {
		List<String> columns = new ArrayList<>();
		for (String header : headers) {
			columns.add("\"" + header.replaceAll("\"", "\"\"") + "\" TEXT");
		}
		String sql = String.format("CREATE TABLE IF NOT EXISTS %s (%s)", tableName, String.join(",", columns));
		try (Statement stmt = conn.createStatement()) {
			stmt.execute(sql);
		}
	}

	private String generateInsertSQL(String tableName, String[] headers) {
		String[] columns = Arrays.stream(headers)
				.map(header -> "\"" + header.replaceAll("\"", "\"\"") + "\"")
				.toArray(String[]::new);
		String placeholders = String.join(",", Arrays.stream(headers).map(header -> "?").toArray(String[]::new));
		return String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, String.join(",", columns), placeholders);
	}

	private void createIndexes(Connection conn, String tableName) throws SQLException {
		try (Statement stmt = conn.createStatement()) {
			String createIndexSQL = String.format("CREATE INDEX IF NOT EXISTS idx_%s_all ON %s (%s)",
					tableName, tableName, String.join(",", getColumnNames(conn, tableName)));
			stmt.execute(createIndexSQL);
		}
	}

	private String[] getColumnNames(Connection conn, String tableName) throws SQLException {
		List<String> columnNames = new ArrayList<>();
		try (Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("PRAGMA table_info(" + tableName + ")")) {
			while (rs.next()) {
				columnNames.add("\"" + rs.getString("name").replaceAll("\"", "\"\"") + "\"");
			}
		}
		return columnNames.toArray(new String[0]);
	}

	private void exportDiffToCsv(Connection conn, String tableA, String tableB, String outputDirPath,
			String outputFileName)
			throws SQLException, IOException {
		String sql = String.format("SELECT * FROM %s EXCEPT SELECT * FROM %s", tableA, tableB);
		ICsvListWriter writer = null;
		try (Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql)) {

			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();

			int fileCount = 1;
			int rowCount = 0;
			if (StringUtils.isEmpty(outputDirPath)) {
				throw new OutputPathException();
			}
			File outputDir = new File(outputDirPath);
			if (!outputDir.exists()) {
				outputDir.mkdirs();
			}
			String outputFilePath = String.format("%s/%s_削除対象_%03d.csv", outputDirPath, outputFileName, fileCount);
			writer = new CsvListWriter(new BufferedWriter(
					new OutputStreamWriter(new FileOutputStream(outputFilePath), Charset.forName("MS932"))),
					preference);
			//出力ファイルにヘッダーを出力するにチェックがある場合は実行する
			if (outPutHeaderCheckBox.isSelected()) {
				String[] headerRow = new String[columnCount];
				for (int i = 1; i <= columnCount; i++) {
					headerRow[i - 1] = metaData.getColumnName(i);
				}
				writer.write(headerRow);
			}
			while (rs.next()) {
				String[] row = new String[columnCount];
				for (int i = 1; i <= columnCount; i++) {
					row[i - 1] = rs.getString(i);
				}
				writer.write(row);

				rowCount++;
				if (rowCount % chunkSize == 0) {
					writer.close();
					fileCount++;
					outputFilePath = String.format("%s/%s_削除対象_%03d.csv", outputDirPath, outputFileName, fileCount);
					writer = new CsvListWriter(new BufferedWriter(
							new OutputStreamWriter(new FileOutputStream(outputFilePath), Charset.forName("MS932"))),
							preference);
					if (outPutHeaderCheckBox.isSelected()) {
						String[] headerRow = new String[columnCount];
						for (int i = 1; i <= columnCount; i++) {
							headerRow[i - 1] = metaData.getColumnName(i);
						}
						writer.write(headerRow);
					}
				}
			}
			appendMessage("差分をCSVにエクスポートしました: " + outputDirPath);
		} finally {
			if (Objects.nonNull(writer)) {
				writer.close();
			}
		}
	}

	private void exportDiffDelToCsv(Connection conn, String tableA, String tableB, String outputDirPath,
			String outputFileName)
			throws SQLException, IOException {
		String sql = String.format("SELECT * FROM %s EXCEPT SELECT * FROM %s", tableA, tableB);
		ICsvListWriter writer = null;
		try (Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql)) {

			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();

			int fileCount = 1;
			int rowCount = 0;

			File outputDir = new File(outputDirPath);
			if (!outputDir.exists()) {
				outputDir.mkdirs();
			}

			String outputFilePath = String.format("%s/%s_追加更新対象_%03d.csv", outputDirPath, outputFileName, fileCount);
			writer = new CsvListWriter(new BufferedWriter(
					new OutputStreamWriter(new FileOutputStream(outputFilePath), Charset.forName("MS932"))),
					preference);
			//出力ファイルにヘッダーを出力するにチェックがある場合は実行する
			if (outPutHeaderCheckBox.isSelected()) {
				String[] headerRow = new String[columnCount];
				for (int i = 1; i <= columnCount; i++) {
					headerRow[i - 1] = metaData.getColumnName(i);
				}
				writer.write(headerRow);
			}

			while (rs.next()) {
				String[] row = new String[columnCount];
				for (int i = 1; i <= columnCount; i++) {
					row[i - 1] = rs.getString(i);
				}
				writer.write(row);

				rowCount++;
				if (rowCount % chunkSize == 0) {
					writer.close();
					fileCount++;
					outputFilePath = String.format("%s/%s_追加更新対象_%03d.csv", outputDirPath, outputFileName, fileCount);
					writer = new CsvListWriter(new BufferedWriter(
							new OutputStreamWriter(new FileOutputStream(outputFilePath), Charset.forName("MS932"))),
							preference);
					if (outPutHeaderCheckBox.isSelected()) {
						String[] headerRow = new String[columnCount];
						for (int i = 1; i <= columnCount; i++) {
							headerRow[i - 1] = metaData.getColumnName(i);
						}
						writer.write(headerRow);
					}
				}
			}
			appendMessage("差分をCSVにエクスポートしました: " + outputDirPath);
		} finally {
			if (Objects.nonNull(writer)) {
				writer.close();
			}
		}
	}

	private void exportDiffDelToAddUpdateCsv(Connection conn, List<String> selectList, String tableA, String tableB,
			String outputDirPath,
			String outputFileName)
			throws SQLException, IOException {
		conn.setAutoCommit(false);
		//旧テーブルからキー項目を取得する
		List<String> keyItemSql = new ArrayList<>();
		try (Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT " + String.join(",", selectList) + " FROM " + BEFORE_TABLE)) {
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();

			//追加更新用テーブルから更新対象取得するSQLを作成
			while (rs.next()) {
				StringBuilder sb = new StringBuilder("SELECT * FROM " + ADD_UPDATE_TABLE + " WHERE ");
				for (int i = 1; i <= columnCount; i++) {
					String colName = "\"" + metaData.getColumnName(i).replaceAll("\"", "\"\"") + "\"";
					if (i == 1) {
						sb.append(colName);
						sb.append(" = ");
						sb.append("'" + rs.getString(metaData.getColumnName(i)) + "'");
					} else {
						sb.append(" AND ");
						sb.append(colName);
						sb.append(" = ");
						sb.append("'" + rs.getString(metaData.getColumnName(i)) + "'");
					}
				}
				keyItemSql.add(sb.toString());
			}

			//更新用テーブルに挿入するデータを取得
			int count = 0;
			String insertSQL = "";
			String[] headerRow = null;
			List<Map<String, String>> updateTargetList = new ArrayList<>();
			for (String keysSql : keyItemSql) {
				count++;
				try (ResultSet upRs = stmt.executeQuery(keysSql)) {
					ResultSetMetaData upMetaData = upRs.getMetaData();
					int colNum = upMetaData.getColumnCount();
					//初回のみ実行
					if (count == 1) {
						headerRow = new String[colNum];
						for (int i = 1; i <= colNum; i++) {
							String colName = metaData.getColumnName(i);
							headerRow[i - 1] = colName;
						}
						//更新用テーブルを作成
						stmt.execute("DROP TABLE IF EXISTS " + UPDATE_TABLE);
						createTable(conn, UPDATE_TABLE, headerRow);
						createIndexes(conn, UPDATE_TABLE);
						insertSQL = generateInsertSQL(UPDATE_TABLE, headerRow);
					}

					//比較更新テーブルから更新用データを取得する
					while (upRs.next()) {
						Map<String, String> resultMap = new LinkedHashMap<>();
						for (int i = 1; i <= colNum; i++) {
							resultMap.put(metaData.getColumnName(i), upRs.getString(metaData.getColumnName(i)));
						}
						updateTargetList.add(resultMap);
					}
				} catch (Exception e) {
					conn.rollback();
					e.printStackTrace();
					appendMessage("更新対象テーブル作成中にエラーが発生しました " + ": " + e.getMessage());
					return;
				}
			}
			//更新用データを挿入する
			try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
				for (Map<String, String> resultMap : updateTargetList) {
					for (int i = 1; i <= resultMap.size(); i++) {
						String colname = headerRow[i - 1];
						pstmt.setString(i, resultMap.get(colname));
					}
					pstmt.addBatch();
					if (++count % BATCH_SIZE == 0) {
						pstmt.executeBatch();
					}
				}
				pstmt.executeBatch();
				conn.commit();
			} catch (Exception e) {
				conn.rollback();
				e.printStackTrace();
				appendMessage("更新用データ作成中にエラーが発生しました " + ": " + e.getMessage());
			}
		}

		String updateTargetSql = "SELECT * FROM update_target";
		String insertTargertSql = "SELECT * FROM add_update EXCEPT SELECT * FROM update_target";
		try {
			doOutput(conn, updateTargetSql, outputDirPath, outputFileName, "更新対象");
			doOutput(conn, insertTargertSql, outputDirPath, outputFileName, "追加対象");
			appendMessage("差分をCSVにエクスポートしました: " + outputDirPath);
		} catch (Exception e) {

		}
	}

	/** 
	 * 出力処理を実行する
	 * @param conn コネクション
	 * @param sql  実行SQL
	 * @param outputDirPath 出力先のパス
	 * @param outputFileName 出力ファイル名
	 * @param outputFileName 出力ファイル種類（追加、更新、削除）
	 * */
	private void doOutput(Connection conn, String sql, String outputDirPath, String outputFileName,
			String outputFileType)
			throws SQLException, IOException {
		ICsvListWriter writer = null;
		try (Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql)) {

			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();

			int fileCount = 1;
			int rowCount = 0;

			File outputDir = new File(outputDirPath);
			if (!outputDir.exists()) {
				outputDir.mkdirs();
			}

			String outputFilePath = String.format("%s/%s_%s_%03d.csv", outputDirPath, outputFileName, outputFileType,
					fileCount);
			writer = new CsvListWriter(new BufferedWriter(
					new OutputStreamWriter(new FileOutputStream(outputFilePath), Charset.forName("MS932"))),
					preference);
			//出力ファイルにヘッダーを出力するにチェックがある場合は実行する
			if (outPutHeaderCheckBox.isSelected()) {
				String[] headerRow = new String[columnCount];
				for (int i = 1; i <= columnCount; i++) {
					headerRow[i - 1] = metaData.getColumnName(i);
				}
				writer.write(headerRow);
			}

			while (rs.next()) {
				String[] row = new String[columnCount];
				for (int i = 1; i <= columnCount; i++) {
					row[i - 1] = rs.getString(i);
				}
				writer.write(row);

				rowCount++;
				if (rowCount % chunkSize == 0) {
					writer.close();
					fileCount++;
					outputFilePath = String.format("%s/%s_%s_%03d.csv", outputDirPath, outputFileName, outputFileType,
							fileCount);
					writer = new CsvListWriter(new BufferedWriter(
							new OutputStreamWriter(new FileOutputStream(outputFilePath), Charset.forName("MS932"))),
							preference);
					if (outPutHeaderCheckBox.isSelected()) {
						String[] headerRow = new String[columnCount];
						for (int i = 1; i <= columnCount; i++) {
							headerRow[i - 1] = metaData.getColumnName(i);
						}
						writer.write(headerRow);
					}
				}
			}
		} finally {
			if (Objects.nonNull(writer)) {
				writer.close();
			}
		}
	}

	private synchronized void updateProgress(int percent) {
		progressBar.setValue(percent);
	}

	private synchronized void appendMessage(String message) {
		messageArea.append(message + "\n");
	}

	public static void main(String[] args) {
		SwingUtilities.invokeLater(() -> new CsvToSQLiteApp().setVisible(true));
	}
}

import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.ArrayList;
import java.util.List;

import javax.swing.DefaultListModel;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JList;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.ListSelectionModel;
import javax.swing.WindowConstants;

public class CSVHeaderSelector extends JDialog  {
	private DefaultListModel<String> headerListModel;
	private JList<String> headerList;
	private DefaultListModel<String> selectedListModel;
	private JList<String> selectedList;
	private List<String> selectedListVal;

	/**
	 * @return 選択項目
	 */
	public List<String> getSelectedList() {
		return this.selectedListVal;
	}

	public CSVHeaderSelector(List<String> headers) {
		setTitle("CSV Header Selector");
		setBounds(0, 200, 640, 320);
		setDefaultCloseOperation(WindowConstants.HIDE_ON_CLOSE);
		setLayout(new BorderLayout());
		selectedListVal = new ArrayList<String>();

		headerListModel = new DefaultListModel<>();
		for (String header : headers) {
			headerListModel.addElement(header);
		}
		headerList = new JList<>(headerListModel);
		headerList.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);
		JScrollPane headerScrollPane = new JScrollPane(headerList);
		headerScrollPane.setPreferredSize(new Dimension(300, 150));

		selectedListModel = new DefaultListModel<>();
		selectedList = new JList<>(selectedListModel);
		JScrollPane selectedScrollPane = new JScrollPane(selectedList);
		selectedScrollPane.setPreferredSize(new Dimension(300, 150));

		JButton selectButton = new JButton("選択");
		selectButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				List<String> selectedHeaders = headerList.getSelectedValuesList();
				for (String header : selectedHeaders) {
					if (!selectedListModel.contains(header)) {
						selectedListVal.add(header);
						selectedListModel.addElement(header);
						headerListModel.removeElement(header);
					}
				}
			}
		});

		JButton removeButton = new JButton("削除");
		removeButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				List<String> selectedHeaders = selectedList.getSelectedValuesList();
				for (String header : selectedHeaders) {
					if (!headerListModel.contains(header)) {
						selectedListVal.remove(selectedListVal.indexOf(header));
						headerListModel.addElement(header);
						selectedListModel.removeElement(header);
					}
				}
			}
		});

		JButton confirmButton = new JButton("確定");
		confirmButton.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
				setVisible(false);
			}
		});
		JPanel buttonPanel = new JPanel();
		buttonPanel.add(selectButton);
		buttonPanel.add(removeButton);
		buttonPanel.add(confirmButton);
		
		add(headerScrollPane, BorderLayout.WEST);
		add(selectedScrollPane, BorderLayout.EAST);
		add(buttonPanel, BorderLayout.SOUTH);
	}
}