Step-by-step guide on how to import CSV data into an SQLite database and export it back to a CSV file using Java with JavaFX in NetBeans IDE

 

Step 1: Set up the project

1. Launch NetBeans IDE and create a new JavaFX project.
2. Name your project and specify a directory to save it.

Step 2: Add Dependencies

1. Right-click on your project in the Project window and select “Properties”.
2. In the Project Properties dialog, navigate to the “Libraries” category.
3. Click on the “Add Dependency” button and search for “sqlite-jdbc” library.
4. Select the “sqlite-jdbc” library from the search results and click “Add JAR/Folder”.
5. Click “OK” to close the Project Properties dialog.

Step 3: Design the User Interface

1. Open the Main.fxml file from the “Projects” window.
2. Drag and drop the necessary UI components, such as buttons, labels, and file choosers, onto the FXML design canvas to create your desired UI layout.

Step 4: Connect UI Components with Controller

1. Create a new Java class, let’s call it MainController, by right-clicking on your project package and selecting “New > Java Class”.
2. In the MainController class, add the necessary imports:

Java

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.stage.FileChooser;

Declare @FXML-annotated fields for the UI components you want to access from the controller, such as buttons and labels:

@FXML
private Button importButton;

@FXML
private Button exportButton;

@FXML
private Label statusLabel;

Implement the event handlers for the buttons. For example, the importButton can have the following event handler:

@FXML
private void handleImportButton(ActionEvent event) {
    FileChooser fileChooser = new FileChooser();
    fileChooser.setTitle("Select CSV File");
    fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("CSV Files", "*.csv"));
    File selectedFile = fileChooser.showOpenDialog(importButton.getScene().getWindow());
    
    if (selectedFile != null) {
        importData(selectedFile);
        statusLabel.setText("Data imported successfully!");
    }
}

Implement the importData method to read the CSV file and insert the data into the SQLite database:

private void importData(File file) {
    String url = "jdbc:sqlite:database.db";
    String sql = "INSERT INTO your_table_name (column1, column2, ...) VALUES (?, ?, ...)";
    
    try (Connection conn = DriverManager.getConnection(url);
         PreparedStatement pstmt = conn.prepareStatement(sql);
         BufferedReader br = new BufferedReader(new FileReader(file))) {
        
        String line;
        while ((line = br.readLine()) != null) {
            String[] data = line.split(",");
            
            // Set the values for the prepared statement based on the CSV columns
            pstmt.setString(1, data[0]);
            pstmt.setString(2, data[1]);
            // ...
            
            pstmt.executeUpdate();
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

Similarly, implement the event handler for the exportButton

@FXML
private void handleExportButton(ActionEvent event) {
    FileChooser fileChooser = new FileChooser();
    fileChooser.setTitle("Save CSV File");
    fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("CSV Files", "*.csv"));
    File selectedFile = fileChooser.showSaveDialog(exportButton.getScene().getWindow());
    
    if (selectedFile != null) {
        exportData(selectedFile);
        statusLabel.setText("Data exported successfully!");
    }
}

Implement the exportData method to retrieve data from the SQLite database and write it to a CSV file:

private void exportData(File file) {
    String url = "jdbc:sqlite:database.db";
    String sql = "SELECT * FROM your_table_name";
    
    try (Connection conn = DriverManager.getConnection(url);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        
        // Open a FileWriter and BufferedWriter to write to the CSV file
        try (FileWriter fw = new FileWriter(file);
             BufferedWriter bw = new BufferedWriter(fw)) {
            
            // Write the column headers
            bw.write("column1,column2,...");
            bw.newLine();
            
            // Write the data rows
            while (rs.next()) {
                bw.write(rs.getString("column1") + ",");
                bw.write(rs.getString("column2") + ",");
                // ...
                bw.newLine();
            }
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}