JDBC:Using Joins

Using Joins

Sometimes you need to use two or more tables to get the data you want. For example, suppose the proprietor of The Coffee Break wants a list of the coffees he buys from Acme, Inc. This involves information in the COFFEES table as well as the yet-to-be-created SUPPLIERS table. This is a case where a join is needed. A join is a database operation that relates two or more tables by means of values that they share in common. In our example database, the tables COFFEES and SUPPLIERS both have the column SUP_ID, which can be used to join them.
The names of the suppliers are in the table SUPPLIERS, and the names of the coffees are in the table COFFEES. Because both tables have the column SUP_ID, this column can be used in a join. You need some way to distinguish to which SUP_ID column you are referring. This is done by preceding the column name with the table name, as in "COFFEES.SUP_ID" to indicate that you mean the column SUP_ID in the table COFFEES. The following method, JoinSample.getCoffeesBoughtBySupplier, selects the coffees bought from a particular supplier:

  public static void getCoffeesBoughtBySupplier (String supplierName, Connection con, String dbName)
    throws SQLException {
    Statement stmt = null;
    String query = "SELECT COFFEES.COF_NAME " +
       "FROM COFFEES, SUPPLIERS " +
       "WHERE SUPPLIERS.SUP_NAME LIKE '" + supplierName + "' " +
       "and SUPPLIERS.SUP_ID = COFFEES.SUP_ID";

    try {
      stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery(query);
      System.out.println("Coffees bought from " + supplierName + ": ");
      while (rs.next()) {
          String coffeeName = rs.getString(1);
          System.out.println("     " + coffeeName);
      }
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } finally {
        stmt.close();
    }
  }

If you call this method with the argument supplierName set to Acme, Inc., it will produce the following output:

Coffees bought from Acme, Inc.:
     Colombian
     Colombian_Decaf

The following line retrieves the value from the first column of the ResultSet object:

          String coffeeName = rs.getString(1);

Note that the ResultSet object in this example contains only one column.