试图在Netbeans中的Patient表中添加多个Patient

Question

我有一个增强的for循环，它循环遍历我的Patients数组。

在这个循环中，我有一个insert语句，用于插入患者的号码、姓名、地址和电话号码。

但是，当数组中有多个患者时，之前的患者将被重写到数据库中。有什么方法可以让我到达表的下一行，这样我就不会重写之前的所有条目了?

这是我正在使用的方法。

public void databaseSave( ArrayList <Patient> pList )
    {
    try
    {
        String name = "Shaun";
        String pass = "Shaun";
        String host = "jdbc:derby://localhost:1527/DentistDatabase";
        Connection con = DriverManager.getConnection(host, name, pass);
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        //Statement stmt = con.createStatement();
        System.out.println("Before the delete");

        String query = "DELETE "
                    +  "FROM SHAUN.PATIENT";

        System.out.println("After the delete");

        stmt.executeUpdate(query);

        String select = "SELECT * FROM SHAUN.PATIENT";
        ResultSet result = stmt.executeQuery(select);

        System.out.println("Before loop");
        for ( Patient p: pList )
        {
            patientInsertSQL = "Insert Into SHAUN.PATIENT VALUES (" + p.getPatientNum() + ", '"
            + p.getPatientName() + "', '" + p.getPatientAddress() + "', '"
            + p.getPatientPhone() + "')";

            System.out.println("In the loop!");
        }

        int res = stmt.executeUpdate(patientInsertSQL);
        System.out.println(res);

        stmt.close();
        result.close();
        con.commit();
        System.out.println("After Loop and close");
    }
    catch (SQLException err)
    {
        System.out.print(err.getMessage());
    }
}

Answer 1

您必须在每次迭代中执行查询或使用SQL批处理插入。

for ( Patient p: pList )
        {
            patientInsertSQL = "Insert Into SHAUN.PATIENT VALUES (" + p.getPatientNum() + ", '"+ p.getPatientName() + "', '" + p.getPatientAddress() + "', '"
            + p.getPatientPhone() + "')";
        int res = stmt.executeUpdate(patientInsertSQL);
}

SQL Batch Insert:

for(Patient p:pList) {
PatientInsertSQL = "Insert into patient Values(x,y,z)";
stmnt.addBatch(query);
}
stmnt.executeBatch();

BTW，为了避免SQL注入使用PreparedStatement而不是Statement

Answer 2

语句int res = stmt.executeUpdate(patientInsertSQL);应该在for循环中