Patrick Keegan's Blog

Some CRUD Master/Detail Details

I haven't had a chance to write a full tutorial on creating a true master/detail view using the NetBeans GUI Builder. Naturally, lots of questions have arisen about it in the meantime, particularly since the IDE's Java Desktop Application template shows things that pretty much beg those questions.

For example, if you follow the Building a Java Desktop Database Application, you will encounter something curious in page 4 of the wizard for the Java Desktop Application template.

You have the option to display your "detail" as text fields or as a table. But the table option is greyed out. Why is that?

The answer is that table vs. text field is not the only difference here. If you select "Textfields", the generated text fields represent columns from the master table. If you select "Table", the a JTable is generated to represent columns from a different database table, which is related to the master table through a foreign key. Since the car database example only has a single table, the "Table" option here is irrelevant.

To show how the "Table" option works, let's create a project based on a database that contains two tables. One table will hold people's names. The other table will hold email addresses. "Normalizing" the database in this manner enables the database to scale more efficiently. We can add multiple email addresses for each person without setting a high character limit for the email address column. And we don't need to have any application logic to parse out multiple email addresses that are stored in the same record.

So let's get started. First we'll set up the database:

1. Choose Tools > Java DB Database > Start Server. (If this option is not available to you, see Setting Up the Database section of the Building a Java Desktop Database Application tutorial.)

   Within a few seconds, you should see a message in the Output window indicating that the database server has started.

2. Choose Tools > Java DB Database > Create Database. In the Create Java DB Database dialog box:
   1. Fill in CONTACTS2008 as the database name. For both User Name and Password, enter nbuser.
   2. For both User Name and Password, enter nbuser.
   3. Click OK.
   
3. In the Services window, once right-click the node for your database connection (CONTACTS2008) and choose Connect as shown in the image below.

4. In the Services window, once again right-click the CONTACTS2008 connection, and choose Execute Command.

   

   A new tab called SQL Command should appear in the Source Editor.

5. In the SQL Command tab, paste in the following SQL script:

      CREATE TABLE CONTACTS2008
       (
       ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
       FIRST_NAME VARCHAR(50),
       LAST_NAME VARCHAR(50),
       TITLE VARCHAR(50),
       NICKNAME VARCHAR(50)
       );
   
       ALTER TABLE CONTACTS2008
          ADD CONSTRAINT CONTACTS2008_PK Primary Key (
         ID);
   
       CREATE TABLE EMAIL_ADDRESSES2008
       (
       ID INTEGER NOT NULL ,
       ADDRESS VARCHAR(50) NOT NULL
       );
       
       ALTER TABLE EMAIL_ADDRESSES2008
          ADD CONSTRAINT EMAIL_ADDRESSES2008_PK Primary Key (
         ADDRESS);
   
   ALTER TABLE EMAIL_ADDRESSES2008
      ADD CONSTRAINT CONTACTS2008_FK Foreign Key (ID)
      REFERENCES CONTACTS2008 (ID);
      
   INSERT INTO CONTACTS2008 (FIRST_NAME, LAST_NAME, TITLE, NICKNAME)
          VALUES  	('Jake', 'Roberts', 'Mr.', '"The Snake"'), 
                  	('Andre', 'Giant', 'Mr.', '"The Giant"');   
      
   
   INSERT INTO EMAIL_ADDRESSES2008 (ID, ADDRESS)
          VALUES  	(1, 'snake@ifpwafcad.com'),
                   (2, 'bobo@ifpwafcad.com'),
                   (1, 'jake@ifpwafcad.com'),
                   (2, 'haha@ifpwafcad.com');
                   
   

6. Click the (Run SQL) button in the toolbar of the editor.

   Several lines should appear in the Output window that indicate that the script was run successfully.

   This script creates two database tables and populates each with a few rows of data. The CONTACTS2008 table contains names of people (with columns for first name, last name, title, and nickname). The ID column is "GENERATED ALWAYS AS IDENTITY", meaning that every time a new row is created in that table, a new record number is automatically generated for that row. We do this to ensure that each record is unique, which enables us to reference unique records from other tables.

   The EMAIL_ADDRESSES2008 table is used to store email addresses for the names in the CONTACTS2008 table. A foreign key is set on the ID column of the EMAIL_ADDRESSES2008 table to reference the ID column in the CONTACTS2008 table so that each email address is associated with a specific person.

Now that the database is set up, we can use the New Project wizard to help us generate the Master/Detail form.

1. Choose File > New Project.
2. In the New Project wizard's Choose Project page, select the Java category and the Java Desktop Application template.

3. In the Name and Location page:
   1. Type ContactMasterDetail as the project name.
   2. Select Database Application as the application shell.
   
4. In the Master Table page, select the CONTACTS2008 connection.

   The CONTACTS2008 table should be selected by default, and 5 database column names should be listed in the Columns To Include list.

5. Select the Id column and click the < button to move out of the list of columns to be included. (Since the ID is generated, we want to make sure that users don't try to replace the ID with their own.)

6. In the Detail Options page, select the Table radio button.

   By selecting the Table button, the Detail panel in the generated application will use the ADDRESS2008 table.

   The Available Columns list should now display just the ID column, and the Columns To Include list should just show the ADDRESS column. Leave those values as they are and click Finish to exit the wizard.

   

After you click Finish, code for the application is generated. In the Projects window, you can view the various classes that are generated by expanding the ContactMasterDetail > Source Packages > contactmasterdetail node. In the Source Editor, you can see the visual design for the master/Detail view. The top table is the "master" table, which shows columns from the CONTACTS2008 table. The bottom table is for the detail view, which shows the ADDRESS column from the EMAIL_ADDRESSES2008 table. When a row is selected in the master table, the detail table displays all address records that reference the contact that is selected in the master table.

You can run the application (choose Run > Run > Run Main Project) to see the relations between the tables. When you select a contact in the master table, the email addresses for that contact are displayed in the detail table.

However, the application is not quite ready to work yet. You still need to make a few changes to make the creation of new records work correctly.

The first problem is that our use of the "identity" construct for the ID column in the CONTACTS2008 table is not reflected in the entity class that was generated for the table. So if we try to use the application to create a new row in the database, an exception occurs because the application tries to assign a value to the ID column when it should just let it be generated automatically.

Luckily this is easy to fix. You just need to add one line of code to the Contacts2008 entity class:

1. In the Projects window, double-click Contacts2008.java to open the entity class in the Source Editor.
2. On the line after @Id, insert @GeneratedValue(strategy=GenerationType.IDENTITY).

The relevant section of code should look like the following screenshot:

With that problem fixed, we should now be able to use the New button in the running application to add a new record to the master table.

However, there is still a second problem we have to fix. When creating a new address record, the record does not appear immediately. In order to see the new empty record so that you can edit it, you have to select a different contact and then reselect the contact for which you want to add the address. This is a bug that is a result of the IDE's support for beans binding falling out of synch with the beans binding library, which underwent some late bug fixes. This will be fixed in a post-6.0 IDE release, but for now you have to use this workaround:

1. In the Design view of the ContactMasterDetailView class, right-click the bottom table and choose Bind > elements.
2. In the Bind dialog box, select the Advanced tab.
3. Select the Unreadable Source Value checkbox.
4. Click OK.

Now when we run the application again, we should be able to use the New button below the detail view to add new email addresses for our contacts.

That's it for now. I plan to elaborate on this topic more over the next few months (though I'll probably be pretty quiet over the next few weeks as I'm taking some time away from the computer). But in the meantime, please feel free to write in about specific aspects of DB applications you would like me to help you explore.

Comments

• Thanks for this tutorial. It makes the database application that's generated more useful.
  It still feels like this wizard/app is for editing a single table or single master/detail table set. The FrameView includes the menu items, buttons, and the JTables.
  Ideally the JTables would have been on their own JPanel in a separate class so that the generated framework would be setup to easily allow adding additional tables to the application. Or am I missing something here?

  Posted by: jimjonah on December 21, 2007 at 05:20 AM

• It looks like the FrameView component (which extends org.jdesktop.application.View) enables just one "main" component.

  However, you could put two sub-panels within that main panel.

  In fact, I'll write a quick entry showing how to do that.

  Posted by: pkeegan on December 21, 2007 at 12:01 PM

• Great tutorial. However, much more information regarding these new, great tools is a must. Would very much appreciate seeing a tutorial showing how to build a one-to-many form comprised of jtextfields (parent table) and a jtable (child table) without the assistance of a template.

  Posted by: bjenkins01 on December 21, 2007 at 12:13 PM

• bjenkins01, thanks for the feedback. I definitely am planning a one-to-many example, but the reverse of what you say (the parent table would be a table, and the child table would be text fields), since I think of text fields as being more conducive to editing and the detail area being the one where you are more likely to edit. Do you have a scenario in mind that would illustrate further what you're after?

  Posted by: pkeegan on December 21, 2007 at 01:10 PM

• Yes, I see your point with respect to editing existing records. However, I believe that inserting records, both the master and the children, should be entered through a form having jtextfields for the master table and a jtable component for the child table, if the data relationship logically allows for it. Also, an example of how to construct this type of form without using the CRUD template would be extremely informative, and, in my case, help me overcome the impediments that are currently preventing me from building applications, using what appears to be a fabulous tool.

  Posted by: bjenkins01 on December 25, 2007 at 04:39 PM

• Happy New Year Patrick

  Thanks for this - I can now do Master/Detail apps to some extent - thanks to you and Roumen http://blogs.sun.com/roumen/

  Great comment from bjenkins "much more information regarding these new, great tools is a must......."

  I know I've said it before - but Master/Detail Database Applications were easier in 1999 with Delphi5 - and I can't imagine producing any useful application that didn't involve a Relational Database and multiple tables - something's wrong with the developer focus

  Enough gripes - Happy 2008!

  Jim Fergusson
  

  Posted by: waverley on January 03, 2008 at 04:20 AM

• Thanks for this tutorial. I tried it (on Netbeans 6.0.1), but I have an error when saving a new detail record. Though I did not follow an extra step from the the tuto

  Posted by: aragorn on March 13, 2008 at 01:31 PM

• Hey! Great tutorial! However I'm having problems when I add the @GeneratedValue(strategy=GenerationType.IDENTITY) line on the Contacts2008.java. It says "cannot find symbol". I am using Netbeans version 6.1. How do I do this? Thanks!

  Posted by: benhur99ph on May 28, 2008 at 08:26 PM

• Have you done Ctrl-Shift-I to add the import statement?

  Posted by: pkeegan on June 02, 2008 at 10:24 AM

• Hey! Thanks for the reply! Its working now. Sorry, I'm just new at JAVA and learning all of this stuff by myself. Thanks again!

  Posted by: benhur99ph on June 03, 2008 at 11:42 PM

This work is licensed under a Creative Commons License.