5
\$\begingroup\$

I wrote this program to make a simple in-memory database using SQLite and JDBC. Requirements I was trying to accomplish are below.

  1. It should consume a CSV file, parse the data, and insert it into the in-memory database.
  2. Table name is X and it has 10 columns (named A - J).
  3. If any of the columns in a row of the CSV file are blank, it should be written into a bad-data-<timestamp>.csv file and not inserted into the database.
  4. At the end of the process, write statistics to a log file:
    1. Number of records received
    2. Number of successful records (records allowed to be inserted to database)
    3. Number of failed records (records written to bad-data-<timestamp>.csv file).

The "sample.csv" file can be found here.

This is my first time trying to do anything with databases or OpenCSV so let me know if I can structure it better or anything else. I would really appreciate some constructive criticism.

package com.ms3.dbx;

import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import com.opencsv.CSVWriter;

import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class SQLiteTest {

    private final String url = "jdbc:sqlite::memory:";
    private Connection connection = null;

    private final String csvPath = "/Users/home/eclipse-workspace/dbx/src/main/java/com/ms3/dbx/sample.csv";
    private final String badDataPath = "/Users/home/eclipse-workspace/dbx/src/main/java/com/ms3/dbx/bad-data-";
    private final String badDataExt = ".csv";

    private final DateFormat df = new SimpleDateFormat("yyyyMMddhhmmss");
    private final String badDataFilename = badDataPath + df.format(new Date()) + badDataExt;

    private final String logFilePath = "/Users/home/eclipse-workspace/dbx/src/main/java/com/ms3/dbx/logFile.log";

    private int recordsReceived = 0;
    private int recordsSuccessful = 0;
    private int recordsFailed = 0;

    static
    {
        try
        {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e)
        {
            throw new ExceptionInInitializerError(e);
        }
    }

    // Opens connection to in-memory database
    private void openConnection() throws SQLException
    {
        if (connection == null || connection.isClosed())
        {
            connection = DriverManager.getConnection(url);
        }
    }

    // Closes connection to database
    private void closeConnection() throws SQLException
    {
        connection.close();
    }

    // Creates a table X in database
    private void createTable()
    {
        try
        {
            final Statement statement = connection.createStatement();

            statement.executeUpdate("CREATE TABLE IF NOT EXISTS X"
                    + "(A       TEXT,"
                    + " B       TEXT,"
                    + " C       TEXT,"
                    + " D       TEXT,"
                    + " E       TEXT,"
                    + " F       TEXT,"
                    + " G       TEXT,"
                    + " H       TEXT,"
                    + " I       TEXT,"
                    + " J       TEXT);");

        } catch (SQLException e)
        {
            e.getMessage();
        }
    }

    // Reads data from sample.csv file using OpenCSV
    // If there is a blank column in a row, write it to "bad-data-<timestamp>.csv" file
    // Else insert the row into the database
    // Increment recordsReceived for each row in sample.csv file
    // Increment recordsSuccessful for each row that has every column filled with data
    // Increment recordsFailed for each row that has at least one blank column
    private void insertFromCSV()
    {
        try
        {
            Reader reader = Files.newBufferedReader(Paths.get(csvPath));
            CSVReader csvReader = new CSVReaderBuilder(reader).withSkipLines(1).build();

            Writer writer = Files.newBufferedWriter(Paths.get(badDataFilename));

            CSVWriter csvWriter = new CSVWriter(writer,
                    CSVWriter.DEFAULT_SEPARATOR,
                    CSVWriter.NO_QUOTE_CHARACTER,
                    CSVWriter.DEFAULT_ESCAPE_CHARACTER,
                    CSVWriter.DEFAULT_LINE_END);

            String[] headerRecord = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J"};
            csvWriter.writeNext(headerRecord);

            PreparedStatement pstatement = connection.prepareStatement("INSERT INTO X(A,B,C,D,E,F,G,H,I,J) "
                    + "VALUES(?,?,?,?,?,?,?,?,?,?);");

                String[] nextRecord;
                while ((nextRecord = csvReader.readNext()) != null)
                {
                    recordsReceived++;

                    if (!Arrays.asList(nextRecord).contains(""))
                    {
                        recordsSuccessful++;
                        pstatement.setString(1, nextRecord[0]);
                        pstatement.setString(2, nextRecord[1]);
                        pstatement.setString(3, nextRecord[2]);
                        pstatement.setString(4, nextRecord[3]);
                        pstatement.setString(5, nextRecord[4]);
                        pstatement.setString(6, nextRecord[5]);
                        pstatement.setString(7, nextRecord[6]);
                        pstatement.setString(8, nextRecord[7]);
                        pstatement.setString(9, nextRecord[8]);
                        pstatement.setString(10, nextRecord[9]);
                        pstatement.executeUpdate();
                    } else
                    {
                        recordsFailed++;
                        csvWriter.writeNext(nextRecord);
                    }
                }

                csvWriter.close();

        } catch (SQLException | IOException e)
        {
            e.getMessage();
        }
    }

    // Query the database and print everything to make sure the data is actually being inserted
    private void testDB()
    {
        try
        {
            final Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT * FROM X;");

            ResultSetMetaData rsmd = rs.getMetaData();
            int numColumns = rsmd.getColumnCount();

            while(rs.next())
            {
                for (int i = 1; i <= numColumns; i++)
                {
                    System.out.print(rs.getString(i) + " ");
                }
                System.out.println();
            }
        } catch (SQLException e)
        {
            e.getMessage();
        }
    }

    // Log the received, successful, and failed records in a log file
    private void logStats()
    {
        try
        {
            FileWriter fw = new FileWriter(logFilePath);
            fw.write("Records Received: " + recordsReceived + "\n");
            fw.write("Records Successful: " + recordsSuccessful + "\n");
            fw.write("Records Failed: " + recordsFailed);
            fw.close();
        } catch (IOException e)
        {
            e.getMessage();
        }
    }

    public static void main(String[] args) throws SQLException
    {
        SQLiteTest obj = new SQLiteTest();
        obj.openConnection();
        obj.createTable();
        obj.insertFromCSV();
        obj.testDB();
        obj.logStats();
        obj.closeConnection();
    }
}
\$\endgroup\$
  • \$\begingroup\$ Anyone have any suggestions or critiques? Could really use the help, thanks! \$\endgroup\$ – Jordan Ward Jun 18 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.