The Java TM Tutorial

| Start of Tutorial > Start of Trail > Start of Lesson | Search

Trail: JDBC Database Access
Lesson: JDBC Basics

## 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.

> Before we go any further, we need to create the table `SUPPLIERS` and populate it with values.

> The code below creates the table `SUPPLIERS` :

> >     String createSUPPLIERS = "create table SUPPLIERS " +
> >     			 "(SUP_ID INTEGER, SUP_NAME VARCHAR(40), " +
> >     			 "STREET VARCHAR(40), CITY VARCHAR(20), " +
> >     			 "STATE CHAR(2), ZIP CHAR(5))";
> >     stmt.executeUpdate(createSUPPLIERS);

> The following code inserts rows for three suppliers into `SUPPLIERS` :

> >     stmt.executeUpdate("insert into SUPPLIERS values (101, " +
> >         "'Acme, Inc.', '99 Market Street', 'Groundsville', " + "'CA', '95199'");
> >     stmt.executeUpdate("Insert into SUPPLIERS values (49," +
> >         "'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', " + "'95460'");
> >     stmt.executeUpdate("Insert into SUPPLIERS values (150, " +
> >         "'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', " + "'93966'");

> The following code selects the whole table and lets us see what the table `SUPPLIERS` looks like:

> >     ResultSet rs = stmt.executeQuery("select * from SUPPLIERS");

> The result set will look similar to this:

> SUP_ID | SUP_NAME | STREET | CITY | STATE | ZIP
> ------ | ------------- | ------------- | ----------- | ----- | -----
> 101 | Acme, Inc. | 99 Market Street | Groundsville | CA | 95199
> 49 | Superior Coffee | 1 Party Place | Mendocino | CA | 95460
> 150 | The High Ground | 100 Coffee Lane | Meadows | CA | 93966

> Now that we have the tables `COFFEES` and `SUPPLIERS` , we can proceed with the scenario where the owner wants to get a list of the coffees he buys from a particular supplier. The names of the suppliers are in the table `SUPPLIERS` , and the names of the coffees are in the table `COFFEES` . Since both tables have the column `SUP_ID` , this column can be used in a join. It follows that you need some way to distinguish which `SUP_ID` column you are referring to. 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 code, in which `stmt` is a `Statement` object, will select the coffees bought from Acme, Inc.:

> >     String query = "
> >     SELECT COFFEES.COF_NAME " +
> >        "FROM COFFEES, SUPPLIERS " +
> >        "WHERE SUPPLIERS.SUP_NAME LIKE 'Acme, Inc.'" +
> >        "and SUPPLIERS.SUP_ID = COFFEES.SUP_ID";

> >     ResultSet rs = stmt.executeQuery(query);
> >     System.out.println("Coffees bought from Acme, Inc.: ");
> >     while (rs.next()) {
> >     		String coffeeName = getString("COF_NAME");
> >     		System.out.println("     " + coffeeName);
> >     }

> This will produce the following output:

> >     Coffees bought from Acme, Inc.:
> >          Colombian
> >          Colombian_Decaf

| Start of Tutorial > Start of Trail > Start of Lesson | Search