Java Reference
In-Depth Information
Figure 8.5 The relationships between the various tables involved in searching
for a list of products
which database structures are involved with accomplishing this. Figure 8.5 shows
the tables that we will be working with in our examination of Dynamic
SQL
.
Now let's define which table structures are involved. Three database tables are
involved in fulfilling our requirements (figure 8.5). We are using the Product table
from our previous example. The two new tables introduced in this example are
named Category and Item. Category is a simple table that defines the category that
the product belongs in. The Item table defines the various product permutations.
8.4.3
Writing the SQL in static format
Now that we have defined the inputs and outputs, we can write the
SQL
using sim-
ple static
SQL
that we can run in a query tool. Listing 8.11 shows a static
SQL
state-
ment that we will start with and make into a dynamic statement.
Listing 8.11
Static SQL mock-up
SELECT
p.PRODUCTID AS PRODUCTID,
p.NAME AS NAME,
p.DESCRIPTION AS DESCRIPTION,
p.IMAGE AS IMAGE,
p.CATEGORYID AS CATEGORYID
FROM Product p
INNER JOIN Category c ON
c.categoryId=p.categoryId
INNER JOIN Item i ON
i.productId = p.productId
