java - 通过 JDBC 将 CSV 内容加载到 mysql 数据库中？

Question

所以基本上我是通过基于控制台的菜单来制作我的程序，是吗？它是一个数据库管理器，通过回答数字来操作(你知道，输入“1”输入记录，输入“2”导出记录，等等)。所以这里没有不同的方法来弄乱参数。但它似乎无论如何都不起作用？这是我的代码，之后我会告诉你我尝试了什么。

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

所以该方法(应该)如何使用基本上是:loadCSV(“患者”，患者.csv);其中患者是我要在其中输入值的表的名称，患者.csv 是我要输入的值的源。这是我第一次使用 JDBC，所以我不熟悉所涉及的方法，但我尝试用 st.executeQuery() 和 st.executeUpdate() 替换 st.execute(query)，但都不起作用。之前我遇到了一个错误，但是稍微改变一下 SQL 查询似乎已经解决了这个问题。现在我没有收到错误，但信息只是没有添加到数据库中。

ps:我使用了“忽略”功能，因为我希望能够导入到数据库而不添加重复项。我这样做正确吗？

如果我发布此内容违反了任何规则，请告诉我，以便我可以纠正我的错误。预先感谢您:)

编辑:我的架构:

和示例数据集:

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

用于根据 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;

示例 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"

截断后数据加载良好(注意:目前在我的 Windows 机器上进行了测试)。因此，请随意处理您需要在文件名或表名的字符串中执行的任何特殊连接。