java - 如何使用 java 将数据从 CSV 文件加载到表中？

Question

这个问题已经有答案了:

What is a NullPointerException, and how do I fix it? (12 个回答)

已关闭 4 年前。

我有一个名为 Customer_Info.csv 的文件，其中包含如下示例数据:

CUST_ID FIRST_NAME LAST_NAME USER_TYPE REPORTS_TO_ID 电子邮件 FIRST_TIME_LOGGED_IN FIRST_TIME_LOGIN_DATE ADDRESS_LINE1 ADDRESS_LINE2 城市 州 邮政编码

10001 MICHELLE VILLEGAS3 储蓄 SRINATH MICHELLE.VILLEGAS3@NOBODY.COM Y 07-10-18 1050 WEST FIFTH STREET AZUSA IND 917023308

这是我需要存储数据的表。我以字符串格式存储所有内容，因为它只是一个临时表。

Create Table Customer_Info_ST
 (
 Cust_ID varchar(5),
 First_Name varchar(15),
 Last_Name varchar(15),
 User_Type varchar(10),
 Report_To_Id varchar(10),
 Email varchar(30),
 First_Time_Logged_In varchar(1),
 First_Time_Login_Date varchar(11),
 Address_Line1 varchar(30),
 Address_Line2 varchar(30),
 City varchar(15),
 State varchar(3),
 Zip varchar(12)
 );

我编写了以下代码将其存储在表中。

public class CustInfoDataLoader {

static String sql, tableName;
private static PreparedStatement pstmt;
private static Statement stmt;
private static final String filePath = "Customer_Info.csv";

public static void main(String[] args) throws SQLException, FileNotFoundException, IOException, ParseException {

    readCsv("Customer_info_ST"); //Line 24 in my code 
    //readCsvUsingLoad();
}

private static void readCsv(String tableName) throws FileNotFoundException, IOException, SQLException, ParseException {
    CSVReader csvreader = null;

    try{
        Reader reader = Files.newBufferedReader(Paths.get(filePath));
        csvreader = new CSVReaderBuilder(reader).withSkipLines(1).build();
        sql = "insert into ? values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        ManageDBResource.createConnectionToDB();
        pstmt = ManageDBResource.conn.prepareStatement(sql);


        String[] rowData = null;

        while((rowData = csvreader.readNext()) != null) {

            pstmt.setString(1, tableName);
            int i = 2;
            String state = rowData[11];
            for (String data : rowData) {

                if(i == 9 && data != null && state != "IND") {
                    java.text.SimpleDateFormat source = new java.text.SimpleDateFormat("MM-dd-yyyy");
                    java.util.Date date = source.parse(data);
                    java.text.SimpleDateFormat target = new java.text.SimpleDateFormat("yyyy-MM-dd");
                    data = target.format(date).toString();
                }
                pstmt.setString(i++,data);
                //System.out.print(data + " ");
            }
            int result = pstmt.executeUpdate();
            //System.out.println();
            if(result == 1) {
                System.out.println("Data loaded Successfully.");

            }
        }
    }
    catch (Exception e) {
        System.out.println(e.getMessage());
    }
    finally {
        pstmt.close();   //Line 69 in my code file
        csvreader.close();
    }

}

我收到以下错误:

Exception in thread "main" java.lang.NullPointerException
at CustInfoDataLoader.readCsv(CustInfoDataLoader.java:69)
at CustInfoDataLoader.main(CustInfoDataLoader.java:24)

Answer 1

您可以在executeUpdate()之后关闭PreparedStatement吗？

就像上面的例子中提到的:

String sql = " INSERT INTO TABLE_(name,email,phone,id) VALUES(?,?,?,?) ";


try { 
    BufferedReader bReader = new BufferedReader(new 
    FileReader("1000rows.csv"));
    String line = ""; 
    while ((line = bReader.readLine()) != null) {
        try {

            if (line != null) 
            {
                String[] array = line.split(",+");
                for(String result:array)
                {
                    System.out.println(result);
//Create prepared Statement here and set them and execute them
            PreparedStatement ps = yourConnecionObject.createPreparedStatement(sql);
             ps.setString(1,str[0]);
             ps.setString(2,str[1]);
             ps.setString(3,str[2]);
             ps.setString(4,strp[3])
             ps.excuteUpdate();
             ps. close()
//Assuming that your line from file after split will follow that sequence

                }
            } 
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        finally
        {
           if (bReader == null) 
            {
                bReader.close();
            }
        }
    }
} catch (FileNotFoundException ex) {
    ex.printStackTrace();
}