CODEWITHSUNDEEP
javajdbc
yassoplaya
yassoplaya

Reputation: 67

How do I select a row and update it in Java?

I'm trying to write a BankingApp with database connectivity. Right now, I'm struggling to update the balance of an user. The user inputs the amount he deposits and then the balance is taken from the database and the amount is added onto it, that's at least how I thought.

I will show what I tried.

EDIT: I put a second PreparedStatement and executed the second query with that statement but it is still not working.

public void deposit(){

            System.out.println("How much money do u want to deposit?");
            int val = sc.nextInt();
            try {
                String query = "SELECT Balance FROM accounts.accs where AccountNumber =" + 654321 + "";
                Connection con = DriverManager.getConnection(url,username,password);
                PreparedStatement stmt = con.prepareStatement(query);
                ResultSet rs = stmt.executeQuery(query);
                while (rs.next()){
                    int balance = rs.getInt("Balance");
                    System.out.println("test");
                    int updatedBalance = balance+val;
                    System.out.println("the updatetbalance is" + updatedBalance +"");
                    String query2 = "UPDATE accs" +
                            "SET Balance = " + updatedBalance +"" +
                            "WHERE AccountNumber =" + 654321 +"";
                    PreparedStatement stmt2 = con.prepareStatement(query);
                    stmt2.executeUpdate(query2);
                    con.commit();
                }

The updatedBalance shows correct in the console, but it doesn't update in the database. What am I doing wrong?

Upvotes: 0

Views: 230

Answers (1)

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

First thing: do not use concatenation for inputting parameters. It's prone to SQL injection.

Second: which DBMS are you using? This update can be done with a single query using update-from-select statement. The syntax can differ from database to database, so I'm not going to give you an example of it, but you should definitely consider using this option. You may look up for it in your database documentation.

And finally, here's the code that uses two PreparedStatement that should work for you:

public void deposit() throws SQLException {

    System.out.println("How much money do u want to deposit?");
    int val = sc.nextInt();
    int accountNumber = 654321;

    String query = "SELECT Balance FROM accounts.accs where AccountNumber = ?";
    try( Connection con = DriverManager.getConnection(url,username,password);
         PreparedStatement stmt = con.prepareStatement(query)) {

        stmt.setInt(1, accountNumber);
        ResultSet rs = stmt.executeQuery();

        String query2 = "UPDATE accs " +
                    "SET Balance = ? " +
                    "WHERE AccountNumber = ?";
        try (PreparedStatement stmt2 = con.prepareStatement(query2)) {
            while (rs.next()){
                int balance = rs.getInt("Balance");
                System.out.println("test");
                int updatedBalance = balance+val;
                System.out.println("the updatetbalance is" + updatedBalance +"");

                stmt2.setInt(1, updatedBalance);
                stmt2.setInt(2, accountNumber);
                stmt2.executeUpdate();
            }
        }
    }
}

Upvotes: 1

Related Questions