Joins

Lookup tables and working with Arrays of 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 two design scenarios based on the second Join Tables 2 Example. In this example, we are dealing with four tables: employees, department, access, and the link table emp_access. 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, department, access, and emp_access

This design pattern is possible where you have the parent table employees referencing the other two child tables: department and access, but in different ways. Both department and access tables are lookup tables, which means you should never write to them. They would be regarded as READ ONLY tables.

We will first look at the way the parent table employees is referencing table department. Table employees has a field named dept which is of type INT and points to the Primary Key field in table department. This design pattern is typical when you want each employee record to be assigned to only one department record. employees.dept can only contain one value, therefore, it would have one id value pointing to only one record in table department.

What about a one-to-many relationship? We handle that with the way we connect the parent table employees with table access. There is a table named emp_access in between table employees and table access. emp_access is often referred to as a "link table". emp_access is what makes it possible to record the fact that an employee record can point to many access record options.

By designing our database this way we can then address the manner in which we display our Create and Edit forms on the client side. See IMAGE 2.

IMAGE 2
DataTables create or edit form example

As you can see, we have a select drop down box for the user to choose one department option. Below that, however, we have multiple checkboxes for the Access field. The user can select any combination of checkboxes and save all those options to the database under the one field named Access.

The following snippet of code is how we program our Java Server Page. As you can see, it is heavily commented since it is one of the more challenging join scenarios. If you read the comments in the JSP page, you can follow the authors thought process and things will become more apparent.

We create two separate Joins for each of the child tables: department and access. We create the Fields we require from each child table. Join1 is special in that we re-reference a field we defined for the parent table employees by calling field2.setSubstituteField(field3). It is important to understand that field2 (employees.dept) will be excluded from the output on the JSON string going to the client side. Instead, join1 is going to create an object for us named department and that is what will be sent in the JSON string to the client side. What we have here is a substitution. Hence the name of the method setSubstituteField(...). Now what is going to happen is that whenever we create a new record or edit an existing one, the parameter data:[department][id] will be sent to the server side, NOT "data[employees][dept]". Since we've used the method setSubstituteField() on field2, JED now knows to go looking for the parameter data:[department][id] to set the value for field2 for an Insert or Update transaction. Note that join1 itself and its fields are being set to READ ONLY mode by calling method setCanWrite(false). This is to ensure nothing gets written to the department lookup table during an Insert or Update transaction.

The next important aspect of join1 is setting the join using method setJoin(...). It takes two parameters. In this case, we pass the value "dept" as its first parameter and the value "id" as its second paramter. Knowing the pattern of the join condition we wind up with the statement:

    SELECT `employees`.`id`, `department`.`id`, `department`.`name` FROM `employees` JOIN `department` ON `employees`.`dept`=`department`.`id` ORDER BY `employees`.`id`;

Like join1, join2 must be set to READ ONLY mode. We ensure we call method setCanWrite(false) on both the Join and its fields. The setJoin(...) method call for join2 is different from the one we used for join1. This time we use a version of the method that requires two String[] arrays as parameters. When creating the String[] arrays it is important to understand that the second element of each array should be in reference to a field in the link table. Let us take a second look at our link table named "emp_access". It has two fields: emp_id and access_id. Each of these reference the Primary Key fields in tables employees and access. We want our SQL statement to read:

    SELECT `employees`.`id`, `department`.`id`, `department`.`name` FROM `employees` JOIN `emp_access` ON `employees`.`id`=`emp_access`.`emp_id` JOIN `access` ON `access`.`id` = `emp_access`.`access_id` ORDER BY `employees`.`id`;

To ensure the join conditions are correct we follow the pattern of [tablename].[fieldname] = [link tablename].[link fieldname]. We use this to our advantage when creating the String[] arrays as we call method

join2.setJoin(new String[]{"id","emp_id"}, new String[]{"id","access_id"}, "emp_access");

The first parameter of method setJoin() focuses on the parent table employees as it joins with the link table. The second parameter of method setJoin() focuses on the child table access as it joins with the link table. The third parameter of this method call is how we tell JED that we are working with the Link Table named "emp_access".

Now that the Joins are completed, we require some extra code to send back with the JSON string to tell DataTables what all the options are for both the Department field and the Access field. You will notice that at the top of our JSP page we created two classes named Options1 and Options2. Inside each of these are ArrayLists name "dept" and "access" respectively. Our code proceeds to populate these ArrayLists by performing a simple lookup on the database by calling method editor.runSelectQuery(...). This is a convenience method. You could have easily called a method directly on the Database object named db.executeSelect(...) to achieve the same affect. We then manipulate the JSON string output by calling a special method named editor.appendJSONData(json1, json2). This method literally appends the second json2 string onto the json1 string.

When JED returns a JSON string to the client side it will include one object for the department and one array of objects for access for every record similar to this:

"department":{"id":"4","name":"Marketing"},"access":[{"id":"3","name":"Desktop"},{"id":"1","name":"Printer"}]

In addition, if you look at the tail end of the JSON string you'll find all the options for fields Department and Access:

"dept":[{"value":"1","label":"IT"},{"value":"2","label":"Sales"},{"value":"3","label":"Pre-Sales"},{"value":"4","label":"Marketing"},
{"value":"5","label":"Senior Management"}, {"value":"6","label":"Accounts"},{"value":"7","label":"Support"}],"access":[{"value":"1","label":"Printer"},{"value":"2","label":"Servers"},{"value":"3","label":"Desktop"},
{"value":"4","label":"VMs"},{"value":"5","label":"Web-site"},{"value":"6","label":"Accounts"}]

This concludes the Joins tutorial.