Updating a data with prepared statement and user input

Question

I need your help. I don't know why my code is not updating a data eventhough I used a right code and a right input of data. I hope you'll help me. It's for my assignment. Thank you!

Btw, here is my code:

package assignment;

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

The screenshot of the output:

Andreas · Accepted Answer · 2016-08-14 16:45:39Z

You inverted the parameters:

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

But you wrote:

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

When it should be:

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

Other than that, you should isolate the JDBC code from the user prompting code, and you should use try-with-resources because you're current leaking resources (badly).

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

Collectives™ on Stack Overflow

Updating a data with prepared statement and user input

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related