java - JDBCexecuteUpdate() 仅插入 1 行

Question

我的代码从文本文件读取数据以填充数据库。该文本文件包含 5 行，其中包括一个空行(第 4 行)。该代码读取文本文件，但仅将最后一行插入数据库中。我还需要代码来跳过读取文本文件中的第一行。

我哪里出错了，有什么帮助吗？我是 Java 编程新手。

这是我的代码:

import java.io.*;
import java.sql.*;

public class Example {
   public static Connection getConnection() throws Exception {
        String driver = "org.apache.derby.jdbc.ClientDriver";
        String url = "jdbc:derby://localhost:1527/Orders";
        String username = "user";
        String password = "password";

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
   } 
   public static void main(String[] args)throws Exception {

       String id ="";
       String firstName ="";
       String lastName ="";
       String street="";
       String city ="";
       String fileName = "src/Person.data"; 
       String line = null;


       PreparedStatement pstmt = null;
       Connection conn = null;

       try {
           FileReader fileReader = 
           new FileReader(fileName);
           BufferedReader bufferedReader = 
           new BufferedReader(fileReader);  


           while((line = bufferedReader.readLine()) != null) {

               if ( line.trim().length() == 0 ) {
                   continue;  // Skip blank lines
               } 
               String[] splited = line.split(",");
               //System.out.println(line);
               id=splited[0];
               firstName=splited[1];
               lastName=splited[2];
               street=splited[3];
               city=splited[4];
               System.out.println(line);
               System.out.println(splited[4]);

               conn = getConnection();
               conn.setAutoCommit(false);

               pstmt = conn.prepareStatement("insert into APP.Person(PERSON_ID, LAST_NAME, FIRST_NAME,STREET,CITY)values (?,?,?,?,?)");     
               pstmt.setString(1, id);
               pstmt.setString(2, firstName);
               pstmt.setString(3, lastName);
               pstmt.setString(4, street);
               pstmt.setString(5, city);
               pstmt.executeUpdate();
               conn.commit();
           }   
           bufferedReader.close();     
       }
       catch(FileNotFoundException ex) {
           System.out.println(
                "Unable to open file '" + 
                fileName + "'");  
        ex.printStackTrace();
       }
       catch(IOException ex) {
            System.out.println(
                "Error reading file '" 
                + fileName + "'");                  
       }

   }   
}

Answer 1

这是因为插入仅针对最后一条记录执行。更好的选择是使用executebatch。首先批量添加所有这些插入脚本，循环完成后只需执行该批处理。

Prepared 语句是在循环内创建的，这是错误的。您需要在循环外创建一个Prepared 语句。

使用 try catch block 如下:

    try {
        FileReader fileReader =
                new FileReader(fileName);
        BufferedReader bufferedReader =
                new BufferedReader(fileReader);
        conn = getConnection();
            conn.setAutoCommit(false);

            pstmt = conn.prepareStatement("insert into APP.Person(PERSON_ID, LAST_NAME, FIRST_NAME,STREET,CITY)values (?,?,?,?,?)");

        while((line = bufferedReader.readLine()) != null) {

            if ( line.trim().length() == 0 ) {
                continue;  // Skip blank lines
            }
            String[] splited = line.split(",");
            //System.out.println(line);
            id=splited[0];
            firstName=splited[1];
            lastName=splited[2];
            street=splited[3];
            city=splited[4];
            System.out.println(line);
            System.out.println(splited[4]);


            pstmt.setString(1, id);
            pstmt.setString(2, firstName);
            pstmt.setString(3, lastName);
            pstmt.setString(4, street);
            pstmt.setString(5, city);
            pstmt.addBatch();

        }
        pstmt.executeBatch();
        conn.commit();
        bufferedReader.close();
    }
    catch(FileNotFoundException ex) {
        System.out.println(
                "Unable to open file '" +
                        fileName + "'");
        ex.printStackTrace();
    }
    catch(IOException ex) {
        System.out.println(
                "Error reading file '"
                        + fileName + "'");
    }