Skip to main content

Some CRUD Master/Detail Details

Posted by pkeegan on December 19, 2007 at 12:50 PM PST

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.

master-detail-notableoption.png

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.

    master-detail-createdb.png

  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. master-detail-connect.png

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

    master-detail-execute.png

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

  6. 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');
                   
  7. Click the master-detail-sqlrun.png (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. master-detail-javadesktop.png

  4. In the Name and Location page:
    1. Type ContactMasterDetail as the project name.
    2. Select Database Application as the application shell.

    master-detail-dbshell.png

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

  6. 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.)
  7. master-detail-mastertable.png

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

    master-detail-detailoptions.png

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.

master-detail-runbeforefixing.png

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:

master-detail-annotation.png

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.

master-detail-unreadable.png

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

I am working on a project with MySQL database Java ...

I am working on a project with MySQL database Java GUI.

This is my SQL script CREATE TABLE takenservice ( ResvID INT UNSIGNED NOT NULL, ServID INT UNSIGNED DEFAULT 0 NOT NULL, ServUsedDate DATETIME NOT NULL , PayStat VARCHAR(6) DEFAULT Unpaid NOT NULL, ServRecBy VARCHAR(7) NOT NULL, PayRecBy VARCHAR(7), Quantity SMALLINT DEFAULT 1 NOT NULL, PRIMARY KEY (ResvID, ServID, ServUsedDate)); Alter table takenservice add constraint foreign key (ResvId) references reservation (ResvID); Alter table takenservice add constraint foreign key (ResvId) references reservation (ResvID);

I created the entity classes with Netbeans 7.0 and created 4 classes:

Service
Reservation
ServiceTaken
ServiceTakenPK

but I cant enter values to the table and it gives me an error that I am trying to enter null values in as primary keys.

I have found the bug actually in Netbeans forum which is http://netbeans.org/bugzilla/show_bug.cgi?id=105084.

and accordingly I have added the following methods to the class ServiceTaken

public int getResvID() {
return takenservicePK.resvID;
}

public int getServID() {
return takenservicePK.servID;
}

public Date getServUsedDate() {
return takenservicePK.servUsedDate;
upon inserting a new entry in the table I get this error :

Error Code: 1048 Call: INSERT INTO takenservice (PayRecBy, ServRecBy, Quantity, PayStat, ServUsedDate, ResvID, ServID) VALUES (?, ?, ?, ?, ?, ?, ?) bind => [ddd, ddd, 0, ddd, null, null, null]

What I can do to fix this ? Please help me with this I am running out of time =/