Using a PreparedStatement

If you need to execute a certain SQL statement a number of times, you can have it optimized by the underlying DBMS by using a PreparedStatement. A PreparedStatement is called a precompiled SQL statement, but can still be parametrized. In other words, it can accept certain input parameters.

So:

   Statement stmt = connection.createStatement();
   stmt.executeUpdate("INSERT INTO products (description, price) " +
                      "VALUES ('Chateau Meyney, St. Estephe', 18.75, 2)");

will have the same result as:

   PreparedStatement insertStmt = connection.prepareStatement("
                                       "INSERT INTO products (description, price) " +
                                       "VALUES (?, ?, ?)";
   insertStmt.setString(1, "Chateau Meyney, St. Estephe");
   insertStmt.setFloat(2, 18.75f);
   insertStmt.setInt(3, 2);
   insertStmt.executeUpdate();

The latter one does take somewhat more time to set up (precompilation), but if you need to insert a lot of rows, it is beneficial cause you can reuse insertStmt and change its parameters.

executeUpdate returns the number of rows that were affected.

You can do the same thing for SELECT statements with executeQuery. It returns a ResultSet containing the rows that match your query.

If you need to clear the parameters that were set, invoke the method clearParameters.

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();
         ProductDAO prodDAO = new ProductDAO(con);
 
         printCollection(prodDAO.getAllRows()); 
 
         prodDAO.insert(new Product("Chateau Meyney, St. Estephe",       18.75f, 2));
         prodDAO.insert(new Product("Chateau Montrose, St. Estephe",     54.25f, 2));
         prodDAO.insert(new Product("Chateau Gloria, St. Julien",        22.99f, 2));
         prodDAO.insert(new Product("Chateau Beychevelle, St. Julien",   61.63f, 2));
         prodDAO.insert(new Product("Chateau La Tour de Mons, Margeaux", 57.03f, 2));
         prodDAO.insert(new Product("Chateau Brane-Cantenac, Margeaux",  49.92f, 2));
  
         printCollection(prodDAO.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 Product
{
   private String description;
   private float price;
   private int itemsleft;
 
   public Product() { }
 
   public Product(String description, float price, int itemsleft) {
      setDescription(description);
      setPrice(price);
      setItemsleft(itemsleft);
   }
  
   public void setDescription(String description) {
      this.description = description;
   }
 
   public void setPrice(float price) {
      this.price = price;
   }

   public void setItemsleft(int itemsleft) {
      this.itemsleft = itemsleft;
   }
 
   public String getDescription() {
      return description;
   }
 
   public float getPrice() {
      return price;
   }
  
   public int getItemsleft() {
      return itemsleft;
   }
 
   public String toString() {
      return "Product [description=" + getDescription() + ", price=" + getPrice() + 
             ", itemsleft=" + getItemsleft() + "]";
   }
}
 
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 ProductDAO 
{
   Connection connection = null;
   PreparedStatement insertStmt = null;
 
   public ProductDAO(Connection connection) {
      this.connection = connection;
   }
 
   public void insert(Product prod) throws DatabaseException {
      try {
         if (insertStmt == null) {
            insertStmt = connection.prepareStatement(
                            "INSERT INTO products (description, price, itemsleft) " +
                            "VALUES (?, ?, ?)");
         }
 
         insertStmt.setString(1, prod.getDescription());
         insertStmt.setFloat(2, prod.getPrice());
         insertStmt.setInt(3, prod.getItemsleft());
         insertStmt.executeUpdate();  
      }
      catch(SQLException e) {
         throw new DatabaseException(e.getMessage());
      }
   }
 
   public Collection getAllRows() throws DatabaseException {
      try {
         ArrayList al = new ArrayList();
 
         Statement stmt = connection.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM products");
         while (rs.next()) {
            Product prod = new Product();
            prod.setDescription(""+rs.getString("description"));
            prod.setPrice(rs.getFloat("price"));
            prod.setItemsleft(rs.getInt("itemsleft"));
            al.add(prod);
         }
         
         stmt.close();
 
         return al;
      }
      catch(SQLException e) {
         throw new DatabaseException(e.getMessage());
      }
   }
}