Retrieving rows from a database table using JDBC

Check out the MySQL syntax for SELECT here.

Here’s an example that gets all the rows from a customer table. You need to create a Statement object from a Connection and invoke executeQuery on it, passing it the SQL SELECT 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);
 
         Collection c = custDAO.getAllRows();
         Iterator iter = c.iterator();
         while (iter.hasNext()) {
            System.out.println(iter.next());
         }
  
         db.close();
      }
      catch(DatabaseException e) {
         e.printStackTrace();
      }
   }
}
 
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 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());
      }
   }
}