Java and SQLite – Exporting Data

Sometimes it can be useful to export data from a database, so that it can be analysed, or, to import in to another computer system. CSV, or Comma Separated Value files, are one such file format that allows for both of these scenarios.

Below is an example of how Java can be used to export data to a CSV file called ‘personexport.csv’, from an SQLite database table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting and importing data. For this to work the Apache Commons CSV dependency needs to be used, along with the SQLite JDBC Driver used previously.

Firstly, the CSV file path and name are set, along with the path to the database and a check is made to see if the database actually exists. A further check is then made to see if the path for the CSV file exists. If it does, a connection to the database is established and a query is executed to extract the data from the database. The CSV file is then opened and the table headers, along with the rows of data are added to it. Finally, confirmation of a successful export is provided. A ‘try-catch’ block is also used to catch any errors that may occur.

import java.io.BufferedWriter;
import java.io.File;
import java.io.IOException;
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 org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.QuoteMode;

public class DemoSQLite {

    public static void main(String[] args) {

        // File path and name.
        File filePath = new File("c:/demo");
        String fileName = filePath.toString() + "\\personexport.csv";

        // Database and connection variables.
        File database = new File("C://Demo//testDB.db");
        Connection connect = null;

        // Check if database file exists.
        if (!database.isFile()) {

            // Confirm incorrect database location and stop program execution.
            System.out.println("Error locating database.");
            System.exit(0);

        }

        try {

            // Connect to database.
            connect = DriverManager.getConnection("jdbc:sqlite:" + database.getPath());

        } catch (SQLException e) {

            // Confirm unsuccessful connection and stop program execution.
            System.out.println("Database connection unsuccessful.");
            System.exit(0);

        }

        // Check to see if the file path exists.
        if (filePath.isDirectory()) {

            // SQL to select data from the person table.
            String sqlSelect =
                    "SELECT id, firstname, lastname, title, dob " +
                    "FROM person " +
                    "ORDER BY id";

            try {

                // Execute query.
                Statement statement  = connect.createStatement();
                ResultSet results    = statement.executeQuery(sqlSelect);

                // Open CSV file.
                BufferedWriter writer = Files.newBufferedWriter(Paths.get(fileName));

                // Add table headers to CSV file.
                CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT
                        .withHeader(results.getMetaData()).withQuoteMode(QuoteMode.ALL));

                // Add data rows to CSV file.
                while (results.next()) {

                    csvPrinter.printRecord(
                            results.getInt(1),
                            results.getString(2),
                            results.getString(3),
                            results.getString(4),
                            results.getString(5));

                }

                // Close CSV file.
                csvPrinter.flush();
                csvPrinter.close();

                // Message stating export successful.
                System.out.println("Data export successful.");

            } catch (SQLException e) {

                // Message stating export unsuccessful.
                System.out.println("Data export unsuccessful.");
                System.exit(0);

            } catch (IOException e) {

                // Message stating export unsuccessful.
                System.out.println("Data export unsuccessful.");
                System.exit(0);

            }

        } else {

            // Display a message stating file path does not exist and exit.
            System.out.println("File path does not exist.");
            System.exit(0);

        }

    }

}

The CSV file produced contains the following data.

“id”,”firstname”,”lastname”,”title”,”dob”
“1”,”Bob”,”Smith”,”Mr”,”1980-01-20″
“3”,”Fred”,”Bloggs”,”Mr”,”1975-05-07″
“4”,”Alan”,”White”,”Mr”,”1989-03-20″
“5”,”Fiona”,”Bloggs”,”Mrs”,”1985-05-19″
“6”,”Zoe”,”Davis”,”Miss”,”1979-07-11″
“7”,”Tom”,”Ingram”,”Mr”,”1971-10-04″
“8”,”Karen”,”Thomas”,”Mrs”,”1969-03-08″
“9”,”Samantha”,”Yates”,”Miss”,”1995-08-27″