Java and MySQL – Importing Data

In order to import data into a MySQL database, it must be in a suitable format. CSV files are one such format. A CSV file, or Comma Separated Values file, is a delimited file that uses commas to separate values. They store tabular data in plain text. The first row in a CSV file often contains headers for each column of data. Each row in the file thereafter is a record of related data. In the example contents of a CSV file below, each row contains information relating to a person, including their first name, last name, title and date of birth in the format YYYY-MM-DD (four digit year, two digit month and two digit day).

"firstname","lastname","title","dob"
"Zoe","Davis","Miss","1979-07-11"
"Tom","Ingram","Mr","1971-10-04"
"Karen","Thomas","Mrs","1969-03-08"
"Samantha","Yates","Miss","1995-08-27"

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

Firstly, a connection to the database is established and a check is made to see if the CSV file actually exists. If successfully found, the file is assigned to a reader object and the rows of data, or records, are assigned to a records object. The records are then processed one by one. An SQL ‘Insert‘ statement is constructed, incorporating the values from the record in the records object. This is used to insert the data into the database. Note that, a conversion process is needed for the date of birth in order for it to be made suitable for inserting in to the database. Finally, feedback is given as to the number of records added to the database.

import java.io.File;
import java.io.Reader;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVRecord;

public class DemoMySQL {

    public static void main(String[] args) {

        // CSV file path.
        File csvFile = new File("C://Demo//personimport.csv");

        // Connection variable.
        Connection connect = null;

        try {

            // Connect to database.
            connect = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/Demo?useSSL=false",
                    "DemoUN", "DemoPW");

        } catch (SQLException e) {

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

        }

        // Check if CSV file exists.
        if (!csvFile.isFile()) {

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

        } else {

            try {

                // Assign CSV file to reader object and extract the records.
                Reader reader = Files.newBufferedReader(Paths.get(csvFile.getPath()));
                Iterable<CSVRecord> records =
                        CSVFormat.RFC4180.withFirstRecordAsHeader().parse(reader);

                // Record count.
                int recordCount = 0;

                // Query and statement.
                String sqlPersonInfo;
                PreparedStatement statement = null;

                // Date conversion variables.
                SimpleDateFormat format;
                java.util.Date dobParsed;
                java.sql.Date dobSQL;

                // Insert person information into the database.
                for (CSVRecord record : records) {

                    // Construct the insert statement.
                    sqlPersonInfo = "INSERT INTO person ";
                    sqlPersonInfo += "(firstname, lastname, title, dob) ";
                    sqlPersonInfo += "VALUES (?, ?, ?, ?) ";

                    // Convert date of birth to SQL format.
                    format = new SimpleDateFormat("yyyy-MM-dd");
                    dobParsed = format.parse(record.get("dob"));
                    dobSQL = new Date(dobParsed.getTime());

                    // Create statement and bind the parameters.
                    statement = connect.prepareStatement(sqlPersonInfo);
                    statement.setString(1, record.get("firstname"));
                    statement.setString(2, record.get("lastname"));
                    statement.setString(3, record.get("title"));
                    statement.setDate(4, dobSQL);

                    // Execute query.
                    statement.executeUpdate();

                    // Increment the record count.
                    recordCount += 1;

                }

                // Provide feedback on the number of records added.
                if (recordCount == 0) {

                    System.out.println("No new person records added.");

                } else if (recordCount == 1) {

                    System.out.println(recordCount + " person record added.");

                } else {

                    System.out.println(recordCount + " person records added.");

                }

                // Close the statement and database connection.
                statement.close();
                connect.close();

            } catch (Exception e) {

                // Confirm error adding person information and quit.
                System.out.println("Error adding person information.");
                System.exit(0);

            }

        }

    }

}

The contents of the ‘person’ table now looks as follows.

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