JDBC GS Update with executeUpdate.

How do we perform SQL Update with the executeUpdate() method contained in a JDBC-Statement object.

1. First you need to Create a Database Connection.
2. Then create a regular Statement object with the method, createStatement().
3. The next step is to write your SQL Update statement and give this string as a parameter to the method:

   Method in Statement interface	Description
   int executeUpdate(String sql)	Executes the given SQL statement, and returns the update count.

   Here is an example of using this method to Update a database table:

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.logging.Level;
   import java.util.logging.Logger;
   
   public class SQLUpdate {
     public static void main(String[] args) {
     Connection conn = null;
       Statement stmt = null;
       try {
         // Register a driver for the MySQL database
         Class.forName("com.mysql.jdbc.Driver");
         // Create a url for accessing the MySQL
         // database CarDB
         String url = "jdbc:mysql://localhost:3306/CarDB";
         // user and password to access the database
         String username = "root";
         String password = "root";
         // User the DriverManager to get a Connection to the database
         conn = DriverManager.getConnection(url, username, password);
         // Create a  Statement 
         // that is non-updateable, non-scrollable conserning ResultSet
         stmt = conn.createStatement();
         // Calculate NewPrice for all cars using executeUpdate
         String query = "Update CarPark Set newPrice=dayPrice*0.8";
         int result = stmt.executeUpdate(query);
         if (result > 0) {
           System.out.println("New price for all cars are calculated");
         }
         // Create a  ResultSet with a query that returns
         // all the columns  from the dB table Carpark
         ResultSet resultSet = stmt.executeQuery("select * from Carpark ");
         // As cursor is at the before first row position
         // we use the next() method to
         // test and read the first row in the ResultSet.
         if (resultSet.next()) {
           // Then we use a loop to retrieve rows and column data
           // and creates a html coded table output
           System.out.println("<table border='1' >");
           do {
             System.out.println("<tr>");
             System.out.print("<td>" + resultSet.getString("regNo") + "</td>");
             System.out.print("<td>" + resultSet.getString("cartype") + "</td>");
             System.out.println("<td>" + resultSet.getInt("model") + "</td>");
             System.out.println("<td>" + resultSet.getDouble("dayPrice") + "</td>");
             System.out.println("<td>" + resultSet.getDouble("newPrice") + "</td>");
             System.out.println("</tr>");
           } while (resultSet.next());
           System.out.println("</table>");
         }
   
       } catch (ClassNotFoundException ex) {
         Logger.getLogger(SQLUpdate.class.getName()).
                 log(Level.SEVERE, null, ex);
       } catch (SQLException e) {
         e.printStackTrace();
       } finally {
         try {
           // Close the Statement, which also close the ResultSet
           stmt.close();
           conn.close();
         } catch (Exception xe) {
           xe.printStackTrace();
         }
       }
     }
   }

   You should use the close() method in the Statement object when you do not need it anymore.

   You should also use the close() method in the Connection object when you do not need it anymore.

   You can download this example here (needed tools can be found in the right menu on this page).

   If we run this application the result should be:

   As pure output

   Translated by a Browser

   <table border='1' > <tr> <td>AD23443</td><td>BMW</td><td>2001</td> <td>300.0</td> <td>240.0</td> </tr> <tr> <td>AX72345</td><td>AUDI</td><td>2003</td> <td>350.0</td> <td>280.0</td> </tr> <tr> <td>CE23473</td><td>AUDI</td><td>2001</td> <td>400.0</td> <td>320.0</td> </tr> <tr> <td>DE12345</td><td>AUDI</td><td>2003</td> <td>250.0</td> <td>200.0</td> </tr> <tr> <td>DE23453</td><td>FORD</td><td>2002</td> <td>375.0</td> <td>300.0</td> </tr> <tr> <td>DE34562</td><td>OPEL</td><td>2001</td> <td>340.0</td> <td>272.0</td> </tr> <tr> <td>RE23456</td><td>FORD</td><td>2005</td> <td>350.0</td> <td>280.0</td> </tr> </table>

   AD23443 BMW 2001 300.0 240.0 AX72345 AUDI 2003 350.0 280.0 CE23473 AUDI 2001 400.0 320.0 DE12345 AUDI 2003 250.0 200.0 DE23453 FORD 2002 375.0 300.0 DE34562 OPEL 2001 340.0 272.0 RE23456 FORD 2005 350.0 280.0