Update rows from a database table

Check out the MySQL syntax for UPDATE here.

Here’s an example that modifes a value in the customer table. You need to create a Statement object from a Connection and invoke executeUpdate on it, passing it the SQL UPDATE command. It returns a ResultSet containing the database rows that match your query. You can then iterate over the rows with the next method. The first call to next will position the “cursor” to the first row. You can get a particular column in that row with the getXXX methods, specifying either the column index or the column name. Make sure the database column type and the resulting variable type match, or a proper conversion is done. If types don’t match, a Bad format SQLException is thrown.

Main.java:

import java.util.*;
import java.sql.*;
  
public class Main {
   public static void main(String []args) {
      try {
         Database db = new Database("org.gjt.mm.mysql.Driver",
                                    "jdbc:mysql://192.168.0.1/esus",
                                    "joris",
                                    "mypass");
         Connection con = db.getConnection();
         CustomerDAO custDAO = new CustomerDAO(con);
 
         printCollection(custDAO.getAllRows()); 
 
         Customer cust = custDAO.getCustomer("Joris Van den Bogaert");
         if (cust != null) {
            cust.setEmail("joris_vandenbogaert1@yahoo.com");
            custDAO.updateEmail(cust);
         }
  
         System.out.println();
         printCollection(custDAO.getAllRows()); 
         db.close();
      }
      catch(DatabaseException e) {
         e.printStackTrace();
      }
   }
 
   public static void printCollection(Collection c) {
      Iterator iter = c.iterator();
      while (iter.hasNext()) {
         System.out.println(iter.next());
      }
   }
}
 
class Customer
{
   private String name;
   private String email;
 
   public Customer() { }
 
   public Customer(String name, String email) {
      setName(name);
      setEmail(email);
   }
  
   public void setName(String name) {
      this.name = name;
   }
 
   public void setEmail(String email) {
      this.email = email;
   }
 
   public String getName() {
      return name;
   }
 
   public String getEmail() {
      return email;
   }
 
   public String toString() {
      return "Customer [name=" + getName() + ", email=" + getEmail() + "]";
   }
}
 
class Database
{
   Connection connection = null;
  
   public Database(String driver, String url, String user, String pass) 
                      throws DatabaseException 
   {
      try {
         Class.forName(driver).newInstance();
 
         connection = DriverManager.getConnection(url, user, pass);
      }
      catch(Exception e) {
         throw new DatabaseException(e.getMessage());
      }
   }
 
   public Connection getConnection() {
      return connection;
   }
 
   public void close() throws DatabaseException {
      try {
         connection.close();
      }
      catch(Exception e) {
         throw new DatabaseException(e.getMessage());
      }
   } 
}   
 
class DatabaseException extends Exception {
   public DatabaseException() {
   }
 
   public DatabaseException(String message) {
      super(message);
   }
}
 
class CustomerDAO 
{
   Connection connection = null;
 
   public CustomerDAO(Connection connection) {
      this.connection = connection;
   }
 
   public void updateEmail(Customer cust) throws DatabaseException {
      try {
         Statement stmt = connection.createStatement();
 
         stmt.executeUpdate("UPDATE customers SET email = '" + cust.getEmail() + "' " +
                            "WHERE name = '" + cust.getName() + "'");
         stmt.close();      
      }
      catch(SQLException e) {
         throw new DatabaseException(e.getMessage());
      }
   }
 
   public Customer getCustomer(String name) throws DatabaseException {
      try {
         Statement stmt = connection.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM customers " +
                                          "WHERE name='" + name + "'");
         if (rs.next()) {
            Customer cust = new Customer();
            cust.setName(rs.getString("name"));
            cust.setEmail(rs.getString("email"));
            return cust;
         }
      }
      catch(SQLException e) {
         throw new DatabaseException(e.getMessage());
      }
 
      return null;
   } 
 
   public Collection getAllRows() throws DatabaseException {
      try {
         ArrayList al = new ArrayList();
 
         Statement stmt = connection.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM customers");
         while (rs.next()) {
            Customer cust = new Customer();
            cust.setName(""+rs.getString("name"));
            cust.setEmail(rs.getString("email"));
            al.add(cust);
         }
         
         stmt.close();
 
         return al;
      }
      catch(SQLException e) {
         throw new DatabaseException(e.getMessage());
      }
   }
}

outputs:

Customer [name=Joris Van den Bogaert, email=joris1@esus.com]
Customer [name=Alicia Kolesnikova, email=alicia1@esus.com]

Customer [name=Joris Van den Bogaert, email=joris_vandenbogaert1@yahoo.com]
Customer [name=Alicia Kolesnikova, email=alicia1@esus.com]