Joins

Working With Objects

Databases are designed in different ways when it comes to Joins. Joins become necessary when you want to extract data from more than one table in the database. This tutorial will focus on one such design scenario based on the first Join Tables 1 Example. In this example, we are dealing with three tables: employees, landlines, and mobile. If you were to view them in a database designer application, they would look like what you see in IMAGE 1.

IMAGE 1
Join diagram of tables: employees, landlines, and mobile

This design pattern is most typical where you have the parent table employees referencing the other two child tables: landlines and mobile. The parent table employees has a Primary Key field of type INT that points to the Foreign Key fields in tables landlines and mobile, both labelled emp_id, also of type INT.

This is undoubtedly the easiest Join case when we program our Java Server Page.

We create two separate Joins for each of the child tables: landlines and mobile. We create the Fields we require from each child table. The important method call is the setJoin(...). In this case it takes two parameters of type String. When setting the join, we want to think about how the SQL statement will look where the two parameters become part of the join condition. In both joins we pass the Primary Key from table employees first and the second parameter is the Foreign Key of the child table. Therefore, we wind up with join1.setJoin(pKey,"emp_id"). Each Join performs its own lookup on the database and the SQL statement will look like this for join1:

    SELECT employees.id, landlines.number FROM employees LEFT JOIN landlines ON employees.id = landlines.emp_id ORDER BY employees.id;

The fields list includes employees.id along with landlines.number. employees.id is inserted as part of the query to aid in internal data manipulation within the Join. If you are well versed in the SQL language, setting the join as we did will now make sense as you view the SQL statement it generates.

When JED returns a JSON string to the client side it will include two objects for every record similar to this:

"landlines":{"number":"555-4321"},"mobile":{"number":"444-0085","type":"cell"}

This concludes the Joins tutorial.