java - 使用准备好的语句和用户输入更新数据

Question

我需要你的帮助。我不知道为什么我的代码没有更新数据，即使我使用了正确的代码和正确的数据输入。我希望你能帮助我。这是为了我的任务。谢谢!

顺便说一句，这是我的代码:

包分配；

 import java.sql.*;
 import java.util.Scanner;

 public class assignment_prepared {

     public static void main(String[] args) throws SQLException{
         updateTable();
     }
     private static void updateTable() throws SQLException{

         Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/studentdatabase", "root", "password");

         PreparedStatement preparedStatement2 =null;

         String UpdateSQL= "Update studentrecord set lastname = ? where studentid = ?";

         try{
             preparedStatement2 =conn.prepareStatement(UpdateSQL);

             Scanner scan =new Scanner(System.in);

             System.out.println("Updating...");

             System.out.println("Enter StudentId: ");
             int studentid=scan.nextInt();

             System.out.println("Enter Lastname: ");
             String lastname=scan.next();

             preparedStatement2.setInt(1, studentid);
             preparedStatement2.setString(2, lastname);

             preparedStatement2.executeUpdate();

             ResultSet myRs2= preparedStatement2.executeQuery("Select * from studentrecord");

             while(myRs2.next() ){
                 System.out.println(myRs2.getInt("studentid") + " " +myRs2.getString("lastname") + " " +
                         myRs2.getString("firstname") + " " +myRs2.getInt("tfee") + " " +myRs2.getDouble("fee_per_unit") +
                         " " +myRs2.getInt("total_unit") + " " +myRs2.getString("year") + " " +myRs2.getString("course")
                         + " " +myRs2.getString("section"));

             }

         }catch(Exception exc){
             exc.printStackTrace();
         }
     }
 }

输出截图:

Answer 1

您颠倒了参数:

Update studentrecord set lastname = ? where studentid = ?
                                    ↑                   ↑
                                    1                   2

但是你写道:

preparedStatement2.setInt(1, studentid);
preparedStatement2.setString(2, lastname);

什么时候应该是:

preparedStatement2.setString(1, lastname);
preparedStatement2.setInt(2, studentid);

<小时/>

除此之外，您应该将 JDBC 代码与用户提示代码隔离，并且应该使用 try-with-resources，因为您当前正在泄漏资源(严重)。

private static void updateTable() {
    try {
        Scanner scan =new Scanner(System.in);
        System.out.println("Updating...");
        System.out.println("Enter StudentId: ");
        int studentid=scan.nextInt();
        System.out.println("Enter Lastname: ");
        String lastname=scan.next();

        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentdatabase", "root", "password")) {
            updateTable(conn, studentid, lastname);
            listRecords(conn);
        }
    } catch(Exception exc){
        exc.printStackTrace();
    }
}
private static void updateTable(Connection conn, int studentid, String lastname) throws SQLException {
    String updateSQL = "UPDATE studentrecord SET lastname = ? WHERE studentid = ?";
    try (PreparedStatement stmt = conn.prepareStatement(updateSQL)) {
        stmt.setString(1, lastname);
        stmt.setInt(2, studentid);
        stmt.executeUpdate();
    }
}
private static void listRecords(Connection conn) throws SQLException {
    String selectSQL = "SELECT * FROM studentrecord";
    try (
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(selectSQL)
    ) {
        while (rs.next()) {
            System.out.println(rs.getInt("studentid") + " " +
                               rs.getString("lastname") + " " +
                               rs.getString("firstname") + " " +
                               rs.getInt("tfee") + " " +
                               rs.getDouble("fee_per_unit") + " " +
                               rs.getInt("total_unit") + " " +
                               rs.getString("year") + " " +
                               rs.getString("course") + " " +
                               rs.getString("section"));
        }
    }
}