tag:qiita.com,2005:/sato_ken09/feed Qiitaでユーザーsato_ken09による最近の記事 2024-11-27T18:51:22+09:00 https://qiita.com/sato_ken09 tag:qiita.com,2005:PublicArticle/1942009 2024-11-27T18:51:22+09:00 2024-11-28T12:30:59+09:00 https://qiita.com/sato_ken09/items/71cbde6fdba8a25b39e8 import java.awt.BorderLayout; import java.awt.Color; import java.awt.Dimension; import java.awt.Fl… sato_ken09 tag:qiita.com,2005:PublicArticle/1893505 2024-07-30T06:19:02+09:00 2024-07-30T22:42:28+09:00 https://qiita.com/sato_ken09/items/b61e0ac9c29a275d61cc 必要なJREをだけを抽出しEXE化するPOM JREを含む実行可能なJARファイルの作成 Windows用のEXEファイルの生成 カスタムJREの作成と配布用パッケージへの組み込み これらの目標を達… sato_ken09 tag:qiita.com,2005:PublicArticle/1884200 2024-07-09T14:40:59+09:00 2024-07-09T14:40:59+09:00 https://qiita.com/sato_ken09/items/d2ec018288842150e30d まず、JListの代わりにJTableを使用して、カラムの選択と順序の変更を可能にします。 カラムの順序変更のために、JTableをドラッグ＆ドロップ可能にします。 選択されたカラムとその順序を取… sato_ken09 tag:qiita.com,2005:PublicArticle/1880534 2024-07-02T13:37:41+09:00 2024-07-31T18:18:45+09:00 https://qiita.com/sato_ken09/items/88ca620f4b1889802a0b 比較元と比較先のSJISエンコードされたCSVファイルをSQLiteデータベースにインポートし、その差分をSJISエンコードされたCSVファイルとして出力するSwingベースのアプリケーションです。 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.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStreamReader; import java.io.OutputStreamWriter; 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.List; import java.util.Objects; 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 org.apache.commons.lang3.StringUtils; import org.supercsv.io.CsvListWriter; 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; public class CsvToSQLiteApp extends JFrame { private static final String DB_URL = "jdbc:sqlite:example.db"; private static final int BATCH_SIZE = 1000; private static final int CHUNK_SIZE = 10000; // 1万件ごとに分割するための定数 private JTextField beforeDirPathField; private JTextField afterDirPathField; private JTextField outputCsvPathField; private JCheckBox headerCheckBox; private JCheckBox outPutHeaderCheckBox; private JLabel statusLabel; private JTextArea messageArea; private JProgressBar progressBar; private JPanel columnSelectionPanel; private JButton importButton; private List<JCheckBox> columnCheckBoxes = new ArrayList<>(); // 出力の囲い文字設定用のフィールドを追加 private JTextField outputQuoteField; // 出力ファイル名設定用のフィールドを追加 private JTextField outputFileNameField; private CsvPreference preference = new CsvPreference.Builder('\"', ',', "\r\n") .useQuoteMode(new AlwaysQuoteMode()) .build(); public CsvToSQLiteApp() { setTitle("CSV to SQLite App"); setBounds(0, 100, 620, 780); 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); JButton selectBeforeDirButton = new JButton("選択"); selectBeforeDirButton.setPreferredSize(new Dimension(60, 20)); JPanel p = new JPanel(); p.setLayout(layout); p.setPreferredSize(new Dimension(600, 40)); p.setLayout(new FlowLayout(FlowLayout.LEFT)); p.setBorder(new LineBorder(new Color(220, 220, 220), 2, true)); 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); JButton selectAfterDirButton = new JButton("選択"); selectAfterDirButton.setPreferredSize(new Dimension(60, 20)); JPanel p2 = new JPanel(); p2.setLayout(layout); p2.setPreferredSize(new Dimension(600, 40)); p2.setLayout(new FlowLayout(FlowLayout.LEFT)); p2.setBorder(new LineBorder(new Color(220, 220, 220), 2, true)); 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); headerCheckBox = new JCheckBox("取込ファイルの1行目をヘッダー行とする"); headerCheckBox.setSelected(true); JPanel checkP = new JPanel(); checkP.setLayout(layout); checkP.setPreferredSize(new Dimension(600, 40)); checkP.setLayout(new FlowLayout(FlowLayout.LEFT)); checkP.add(headerCheckBox, gbc); getContentPane().add(checkP, 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(600, 40)); p3.setLayout(new FlowLayout(FlowLayout.LEFT)); p3.setBorder(new LineBorder(new Color(220, 220, 220), 2, true)); 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); // 出力の囲い文字設定用のコンポーネントを追加 JLabel outputQuoteLabel = new JLabel("出力の囲い文字:"); 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(new LineBorder(new Color(220, 220, 220), 2, true)); 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(new LineBorder(new Color(220, 220, 220), 2, true)); p5.add(outputFileNameLabel, gbc); p5.add(outputFileNameField, gbc); getContentPane().add(p5, BorderLayout.PAGE_START); outPutHeaderCheckBox = new JCheckBox("出力ファイルにヘッダーを出力する"); outPutHeaderCheckBox.setSelected(true); JPanel checkP2 = new JPanel(); checkP2.setLayout(layout); checkP2.setPreferredSize(new Dimension(600, 40)); checkP2.setLayout(new FlowLayout(FlowLayout.LEFT)); checkP2.add(outPutHeaderCheckBox, 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(600, 200)); msgP.setLayout(new FlowLayout(FlowLayout.LEFT)); messageArea = new JTextArea(580, 50); messageArea.setEditable(false); JLabel msgLabel = new JLabel("メッセージ:"); JScrollPane messageScrollPane = new JScrollPane(messageArea); messageScrollPane.setPreferredSize(new Dimension(580, 150)); msgP.add(msgLabel, gbc); msgP.add(messageScrollPane, gbc); getContentPane().add(msgP, BorderLayout.PAGE_START); importButton = new JButton("実行"); JPanel inputBtnP = new JPanel(); inputBtnP.setLayout(new GridBagLayout()); inputBtnP.setPreferredSize(new Dimension(600, 40)); inputBtnP.setLayout(new FlowLayout(FlowLayout.RIGHT)); inputBtnP.add(importButton, gbc); getContentPane().add(inputBtnP, 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()); } } }); importButton.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(); String outputQuote = outputQuoteField.getText(); String outputFileName = outputFileNameField.getText(); 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(50); importCsvToTable(conn, afterDirPath, "after", hasHeader); publish(66); createIndexes(conn, "before"); createIndexes(conn, "after"); exportDiffToCsv(conn, "before", "after", outputCsvPath, outputFileName); exportDiffDelToCsv(conn, "after", "before", outputCsvPath, outputFileName); publish(100); // カラムリストの更新 updateColumnSelectionPanel(conn); } catch (SQLException ex) { ex.printStackTrace(); appendMessage("CSVファイルの処理中にエラーが発生しました: " + ex.getMessage()); JOptionPane.showMessageDialog(null, "CSVファイルの処理中にエラーが発生しました: " + ex.getMessage()); } 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(); } }); } 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); return; } 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), "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; } } 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 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); CsvListWriter 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 OutputStreamWriter(new FileOutputStream(outputFilePath), "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 % CHUNK_SIZE == 0) { writer.close(); fileCount++; outputFilePath = String.format("%s/%s_比較先変更行_%03d.csv", outputDirPath, outputFileName, fileCount); writer = new CsvListWriter( new OutputStreamWriter(new FileOutputStream(outputFilePath), "MS932"), preference); } } 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); CsvListWriter 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 OutputStreamWriter(new FileOutputStream(outputFilePath), "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 % CHUNK_SIZE == 0) { writer.close(); fileCount++; outputFilePath = String.format("%s/%s_比較元変更行_%03d.csv", outputDirPath, outputFileName, fileCount); writer = new CsvListWriter( new OutputStreamWriter(new FileOutputStream(outputFilePath), "MS932"), preference); } } appendMessage("差分をCSVにエクスポートしました: " + outputDirPath); } finally { if (Objects.nonNull(writer)) { writer.close(); } } } private void updateColumnSelectionPanel(Connection conn) throws SQLException { columnSelectionPanel.removeAll(); columnCheckBoxes.clear(); String query = "PRAGMA table_info(before)"; try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(query)) { GridBagConstraints gbc = new GridBagConstraints(); gbc.fill = GridBagConstraints.HORIZONTAL; gbc.insets = new Insets(5, 5, 5, 5); gbc.gridx = 0; int row = 0; while (rs.next()) { String columnName = rs.getString("name"); JCheckBox checkBox = new JCheckBox(columnName); columnCheckBoxes.add(checkBox); gbc.gridy = row++; columnSelectionPanel.add(checkBox, gbc); } } columnSelectionPanel.revalidate(); columnSelectionPanel.repaint(); } 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)); } } … sato_ken09