SQLite In-memory Database That Inserts Data Read From a CSV File

Question

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(); } } 

Answer 1

You can consider the following points:

1. For insertFromCSV() you can set param using loop instend of hard coded pstatement.setString(1, nextRecord[0]); You will get benefited in case of nextRecord array size is change(increase)

2. recordsReceived++ its get increment 3 times check for that and no need to put it in if..else..

3. Inside of while(...) if and else both parts have some code then no need to put negation(!) simply swap your code block for Arrays.asList(nextRecord).contains("")
4. You can use looger framework for logging.
5. Use try-with-resources if using Java 7 or above.