CODEWITHSUNDEEP
javasqljdbc
mcquelle
mcquelle

Reputation: 3

Update a record value in java database

I have the following method in a class called savings, am using JDBC database to save and update on java application.

I have SAVINGS table in my database ZaiLab with the following fields, ID,ACCOUNTNUMBER,CUSTOMERNAME,BALANCE,MINMUM)

and the following VALUEs will be entered by the user using JOPtionPane. (acc,Amount);

the application should then check if acco entered by the user exist, if not it should display the appropriate message. "Account does not exist". If exist it should add Amount entered by the user Balance.

The problem is, it says There is no column named: BALANCE.

public void deposit(int acc,int Amount) {
    try {
        String host= "jdbc:derby://localhost:1527/ZaiLab";
        String uname= "siduduzo";
        String upass ="Password01";
        Connection con = DriverManager.getConnection( host, uname, upass );

        Statement stmt = con.createStatement( );

        if(Amount<1000) {
            JOptionPane.showMessageDialog( null,"Sorry! the Minimum you can deposit on a savings account is R1000");          
        } else {
            try (PreparedStatement checkAccountExists = con.prepareStatement("SELECT 1 FROM SAVINGS WHERE ACCOUNTNUMBER = ?")) {
                checkAccountExists.setInt(1, acc);

                try (ResultSet RS = checkAccountExists.executeQuery()) {
                    int currentbal = RS.getInt("BALANCE");
                    int newbalance = currentbal+Amount;

                    if (RS.next()) {
                        String sql = "UPDATE SAVINGS WHERE ACCOUNTNUMBER =" +acc+ "SET BALANCE = "+newbalance;
                        stmt.executeUpdate(sql);
                        JOptionPane.showMessageDialog( null,"you have made a deposit to "+acc);
                    } else {
                        JOptionPane.showMessageDialog( null,"Invalid Account Number");
                    }
                }
            }
        }
    } catch ( SQLException err ) {
        System.out.println( err.getMessage( ) );
    }
}

Upvotes: 0

Views: 9015

Answers (3)

Farah Smadi
Farah Smadi

Reputation: 1

It changes all records in the column to the same record

sql = "SELECT * FROM department INNER JOIN employee ON department.depno=employee.depno  ";
resultSet = statement.executeQuery(sql); 

sql2="update employee set empage=? " ;  
ps=connection.prepareStatement(sql2);
while (resultSet.next()){  
    empage=resultSet.getInt("empage"); 
     if(empage>50){  l= empage-3; 
         //statement.executeUpdate(sql2); 
         ps.setInt(1,l);   
         ps.executeUpdate();
         ps.addBatch();
       }  
}

Upvotes: 0

Parfait
Parfait

Reputation: 107587

Currently your UPDATE query is not valid if your database follows ANSI standards as the WHERE clause must follow the SET clause. Additionally, you need to space out clauses properly as now SET sits against account number. Such syntax can be best achieved with a prepared statement:

UPDATE SAVINGS SET BALANCE = ? WHERE ACCOUNTNUMBER = ?

And as @MarkRotteveel explains, assign currentbal and newbalance after checking for RS.next() to advance cursor in successful resultset query. Consider adjustment of your method's else block:

try (PreparedStatement checkAccountExists = con.prepareStatement(
        "SELECT BALANCE FROM SAVINGS WHERE ACCOUNTNUMBER = ?")) {
     checkAccountExists.setInt(1, acc);

     try (ResultSet RS = checkAccountExists.executeQuery()) {
          if (RS.next()) {
              int currentbal = RS.getInt("BALANCE");
              int newbalance = currentbal + Amount;

              try (PreparedStatement stmt = con.prepareStatement(
                    "UPDATE SAVINGS SET BALANCE = ? WHERE ACCOUNTNUMBER = ?")){ 

                   stmt.setInt(1, newbalance);
                   stmt.setInt(2, acc);

                   stmt.executeUpdate();
                   JOptionPane.showMessageDialog(null, "you have made a deposit to "+acc);
              }

          } else {
              JOptionPane.showMessageDialog(null, "Invalid Account Number");
          }
     }
}

Upvotes: 1

marcothesane
marcothesane

Reputation: 6731

The query you fire goes:

SELECT 1 FROM SAVINGS WHERE ACCOUNTNUMBER = ?

and you populate the parameter marker's host variable with :

checkAccountExists.setInt(1, acc);

Now, if you specified an existing account number, the query above returns the value 1 with whatever default column name the underlying database uses as the column name. It will surely not be BALANCE. So you don't have that column in your result set.

If the SAVINGS table contains the column BALANCE, all you have to do is change the query in the string parameter of con.prepareStatement to :

SELECT BALANCE FROM SAVINGS WHERE ACCOUNTNUMBER = ?

In general: before you test your Java code - always try to connect to the underlying database using a plain SQL client - sqlplus in Oracle, vsql in Vertica, isql in SQL Server, etc. - and run such a query with an example parameter, to see what comes back. Then, you'd have found the error yourself.

Upvotes: 1

Related Questions