load CSV contents into mysql database via JDBC?

Question

so basically i'm making my program via a console-based menu, yeah? it's a database manager and it's operated by answering with numbers(y'know, enter '1' to enter a record, enter '2' to export records, etc..). so there's no different ways to mess with the parameters, here. but it doesn't seem to be working anyway? here's my code, afterwards i'll tell you what i tried.

public void loadCSV(String table, String filename) {
    String query;

    try (Statement st = con.createStatement()) {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/DrSoheirManager", "root", "ITGS!!!");
        st = con.createStatement();
        query = "load data local infile '/Users/****/NetBeansProjects/IA Draft 3/" +
                filename + "' ignore into table " + table + " columns terminated
                by ',' \n" + " lines terminated by '\\n'";
        /* load data local infile '/users/****/netbeansprojects/ia draft
           3/patients.csv' ignore into table */
        st.execute(query);
    } catch (Exception e) {
        e.printStackTrace();
        st = null;
    }
}

so how the method (should) be used, is basically: loadCSV("patients", patients.csv); where patients is the name of the table where i want to enter the values, and patients.csv is the source of the values i want to enter. this is my first time working with the JDBC so i'm unfamiliar with the methods involved, but i've tried replacing st.execute(query) with st.executeQuery() and st.executeUpdate(), neither worked. beforehand i was getting an error but changing the SQL query a bit seemed to have fixed that. now i'm not getting an error, but the information just isn't being added to the database.

ps: i used the 'ignore' function because i want to be able to import to the database without adding duplicates. have i done this correctly?

if i am violating any rules by posting this please let me know so i can fix my errors. thank you in advance :)

edit: my schema:

and a sample data set:

CREATE TABLE IF NOT EXISTS `Patients` (
`ID` int(11) NOT NULL,
  `fname` text NOT NULL,
  `lname` text NOT NULL,
  `Diagnosis` text NOT NULL,
  `phone` varchar(15) NOT NULL,
  `email` varchar(25) NOT NULL,
  `DOB` date NOT NULL,
  `DOFV` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


INSERT INTO `Patients` (`ID`, `fname`, `lname`, `Diagnosis`, `phone`, `email`, `DOB`, `DOFV`) VALUES
(1, 'hanalei', 'ezz', 'hemophelia', '+1(919)929-6567', 'hana@hanalei.me', '1998-08-22', '2008-03-12'),
(2, 'monika', 'zanesco', 'sickle cell disease', '+1(919)939-4264', 'monika.z@gmail.com', '1998-10-05', '2016-04-13'),
(3, 'bang', 'pham', 'leukemia', '+1(919)243-8937', 'bangp@aol.com', '1998-03-15', '2013-03-03');

Answer 1

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class myTest {

    public static void main(String[] args) {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        String url = "jdbc:mysql://localhost:3306/so_gibberish"; // **** MODIFY db name (my db_name is so_gibberish)
        String user = "dbUser";   // **** MODIFY 
        String password = "password123";    // **** MODIFY 

        try {
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            String sSqlLinux="LOAD DATA INFILE '/Users/nate/patients.txt' INTO TABLE Patients " + 
            " FIELDS TERMINATED BY ',' ENCLOSED BY '\"' " +
            " LINES TERMINATED BY '\\n'";

            String sSqlWindows="LOAD DATA INFILE 'c:/nate/patients.txt' INTO TABLE Patients " + 
            " FIELDS TERMINATED BY ',' ENCLOSED BY '\"' " +
            " LINES TERMINATED BY '\\r\\n'";

            rs = st.executeQuery(sSqlLinux); // run the Linux version
            System.out.println("------------------------------");    

        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(myTest.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(myTest.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}

Code snippet to generate your text file from the supplied insert statement from MySQL Workbench:

select ID,fname,lname,Diagnosis,phone,email,DOB,DOFV
INTO OUTFILE 'c:\\nate\\patients.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM Patients;

select ID,fname,lname,Diagnosis,phone,email,DOB,DOFV
INTO OUTFILE '/Users/nate/patients.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Patients;

Sample CSV:

1,"hanalei","ezz","hemophelia","+1(919)929-6567","hana@hanalei.me","1998-08-22","2008-03-12"
2,"monika","zanesco","sickle cell disease","+1(919)939-4264","monika.z@gmail.com","1998-10-05","2016-04-13"
3,"bang","pham","leukemia","+1(919)243-8937","bangp@aol.com","1998-03-15","2013-03-03"

The Data loaded fine after a truncate (note: tested at the moment on my Windows box). So, mess around with any special concat you need to do in the string for your filename or tablename.

Answer 2

You can also use csv2jdbc to easily import and export data across csv files and tables no matter which database you are using:

Import CSV file from client machine to a database Table

The following statement will create table MY_TABLE with all columns existing in file.csv then import the data to the table.

CSV2J COPY MY_TABLE 
FROM '/tmp/file.csv' 
WITH CSV HEADER CREATE_TABLE DELIMITER ','

Export Select statement to a CSV file

The following statement will extract the select statement to file.csv

CSV2J COPY (
  SELECT 1 AS ID, 10.99 AS AMOUNT, TIMESTAMP '2022-01-31 23:59:58.987' AS DAT_CREATION
  UNION ALL
  SELECT 2 AS ID, 7.50 AS AMOUNT, TIMESTAMP '2023-01-31 21:59:58.987' AS DAT_CREATION
) TO '/tmp/file.csv' WITH CSV HEADER