Recently I've found time again to work on actual tutorials. I don't have anything written yet, but I have something resembling a plan, which you can find here: http://wiki.netbeans.org/PlanGuiBuilderDocImprovements.

Over the next few weeks, I'll be blogging about creating a Swing desktop application with database connectivity. These postings will essentially serve as a rough sneak preview of a full-fledged tutorial on the subject that I'll later post to netbeans.org. The tutorial will go beyond simple database connectivity and show things such as one-to-many and many-to-one relationships as well as how to bind database tables to a variety of GUI components. We'll use a MySQL database that has tables for client info, order info, and countries. There will be a one-to-many relationship between the client and order tables. There will be a many-to-one relationship between client and countries tables. Along the way, I'll be looking at any feedback that comes through and do my best to respond to it, whether in quick responses, in separate articles, or by modifying the final tutorial. Chances are that I'll also tweak the structure along the way as I find better ways of doing things.

To start off, I'll provide an SQL script that provides a beginning database structure:

CREATE TABLE CLIENTS (
    ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    FIRST_NAME VARCHAR(20),
    SURNAME VARCHAR(30),
        ADDRESS VARCHAR(30),
    CITY VARCHAR(30),
    STATE_ VARCHAR(30),
    ZIP INTEGER,
    COUNTRY_ID INTEGER,
    PHONE INTEGER
);

    CREATE TABLE EMAIL_ADDRESSES (
    CLIENT_ID INTEGER NOT NULL,
    ADDRESS VARCHAR(50) NOT NULL PRIMARY KEY,
    FORMAT INTEGER,
    FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS(ID)
    );
    
CREATE TABLE ORDERS (
    ID INTEGER NOT NULL AUTO_INCREMENT,
    CLIENT_ID INTEGER NOT NULL,
    PRODUCT VARCHAR(50) NOT NULL,
    AMOUNT INTEGER NOT NULL,
    PRIMARY KEY(ID),
    FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS(ID)
);

CREATE TABLE PRODUCTS (
    MODEL VARCHAR(50) NOT NULL PRIMARY KEY,
    PRICE DECIMAL NOT NULL
);

CREATE TABLE COUNTRIES (
    COUNTRY_ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    COUNTRY VARCHAR(30) 
);

ALTER TABLE CLIENTS    
ADD CONSTRAINT COUNTRIES_FK Foreign Key (COUNTRY_ID)
   REFERENCES COUNTRIES (COUNTRY_ID);

A few notes on the structure:

  • I use AUTO_INCREMENT in some of the tables so that there is a unique identifier for each row in those tables. For this feature to work properly within the application, you need to add the@GeneratedValue(strategy=GenerationType.IDENTITYannotation for that column in the table's entity class. See http://weblogs.java.net/blog/pkeegan/archive/2007/12/index.htmlfor some more context.
  • The foreign key in the ORDERS table is there to link each order record with a client. In the application's UI, all ORDER records are displayed for the selected CLIENT.
  • The foreign key in the CLIENTS table points to a COUNTRIES table. We will use this relationship in the application to enable the user to select a client's country from a combo box.
  • EMAIL_ADDRESSES is a separate table with a foreign key linking it to the CLIENTS table. This is in attempt to keep one of the entry dialogs looking as much as possible like http://www.netbeans.org/kb/60/java/quickstart-gui.html, where it is possible to enter multiple email addresses person. The motivation is so that this new tutorial can build on that previous one (or a similar version of it), but very likely I will delete that table in the end and merely have an EMAIL column in the CLIENTS table.

After you have created the above database and have connected to it from IDE (see Connecting to a MySQL Database), you can go ahead and create the initial application skeleton by following these steps:

  1. Choose File | New Project.
  2. Select the Java category and the Java Desktop Application template.
  3. In the Name and Location page of the wizard, select the Database Application skeleton.
  4. In the Master Table page of the wizard, select the connection to the just-created database. Then select the clients table, and then move ID from Columns to Include to Available Columns. clientrecordproject1.png
  5. In the Detail Options page, click the Table radio button and select the orders table from the combo box.
  6. clientrecordproject2.png
  7. Click Finish to exit the wizard.
  8. Choose Run | Run Main Project to see the main application window. clientrecord-runwithoutdata.png

So that's the start of the application. The next steps include:

  • Adding@GeneratedValue(strategy=GenerationType.IDENTITYannotations (as noted above) to the identity columns in theClient.java and Countries.java entity classes.
  • Creating an entity class for the COUNTRIES table and modifying the CLIENTS entity class to handle the relation.
  • Customizing the generated JTables (e.g. changing column headings, making the columns read-only). You can get a head start on this by right-clicking the JTable and choosing Table Contents.
  • Creating separate dialogs for the entry of client and order records.

I'll cover those topics and more starting early next week. I welcome all feedback!