Skip to main content

Input on a New Desktop Java Database Tutorial

Posted by pkeegan on May 13, 2008 at 9:20 AM PDT

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.IDENTITY annotation for that column in the table's entity class. See http://weblogs.java.net/blog/pkeegan/archive/2007/12/index.html for 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.IDENTITY annotations (as noted above) to the identity columns in the Client.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!

Related Topics >>

Comments

Thank you so much for this

Thank you so much for this excellent tutorial.
One problem... when I get to the part where I'm supposed to choose "Table" under "Detail Options", the radio button is grayed-out and cannot be selected. What am I doing wrong?
Thanks
Eric

In this case, the foreign key in the line below is what enables the Table option in the wizard. Do you have something like that? FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS(ID)

Hello, I've just found why your button for the table is grayed (it was the same for me), it is because you don't have foreign key. You should change the engine to INNODB. All your tables to "ALTER TABLE maTable1 ENGINE=INNODB;" Thank you very much for the tutorial !

Problem solved ! Root Cause: MySQL as this excerpt from the documentation shows: "For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it." Somehow my database (and probably a lot of others, too) is defaulting to "MyISAM" storage engine. Solution: Insert "ENGINE = INNODB" between the closing parathesis and the semicolon in the create tables SQL. I don´t know if MySQL defaults to some other behaviour in later editions but of course it would be nice if Netbeans or the application wizard could somehow check whether the DB has the capabilities required.

Hi, I am using (according to PHPMyAdmin) MySQL 5.0.13 and it does not enable the "Table" Button on the "Detail" Option screen either. Would be interesting to know which SQL on the DB-Structure is used by Netbeans to decide when to enable this. Table creation did not produce errors. I am using a DB-Server on a seperate box.

Good point about the referential integrity rules. In the final tutorial, I want to stress the importance of starting with the right database structure and better explain the decisions that have gone into the structure of the tutorial. So I should add cascading deletes, etc. is the right thing to do.

What are the referential integrity rules? Simply definining foreign keys is not enough. When teaching examples it should become a second nature to address these issues. Databases will be accessed by other software than the application. It will happen in some time in the future. Therefore it should never be possible to compromise the integrity of the data by accessing the database. Data integrity should always be enforces by the database, never ever by the application

Great Work Patrick - Just what was needed (by this old Scot at least) Kindest Regards Jim

Hi Patrick: I am glad that you are back and I would like you to take a challenge and make good GUI /Application design with code generation using available templates in NetBeans such as Master/Detail and other forms starting, from SQL script taken out of published example of MedRec:

"Using the Java Persistence API with Spring 2.0",

http://dev2dev.bea.com/pub/a/2006/03/jpa-spring-medrec.html.

The SQL scripts for MedRec is as follow:

-- MedRec - Data Definition Language -- Create Medrec tables CREATE TABLE address ( id INTEGER NOT NULL, street1 VARCHAR(60) NOT NULL, street2 VARCHAR(60), city VARCHAR(60) NOT NULL, state VARCHAR(2) NOT NULL, zip VARCHAR(10) NOT NULL, country VARCHAR(50) NOT NULL, PRIMARY KEY (id)); CREATE TABLE groups ( username VARCHAR(60) NOT NULL, group_name VARCHAR(60) NOT NULL, PRIMARY KEY (username, group_name)); CREATE TABLE medrec_user ( username VARCHAR(45) NOT NULL, password VARCHAR(50) NOT NULL, status VARCHAR(15) NOT NULL, PRIMARY KEY (username)); CREATE TABLE patient ( id INTEGER NOT NULL, first_name VARCHAR(60) NOT NULL, middle_name VARCHAR(60), last_name VARCHAR(60) NOT NULL, dob DATE NOT NULL, gender VARCHAR(6) NOT NULL, ssn VARCHAR(9) NOT NULL, address_id INTEGER NOT NULL, phone VARCHAR(15), email VARCHAR(60) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (address_id) REFERENCES address(id)); CREATE TABLE physician ( id INTEGER NOT NULL, first_name VARCHAR(60) NOT NULL, middle_name VARCHAR(60), last_name VARCHAR(60) NOT NULL, phone VARCHAR(15), email VARCHAR(60), PRIMARY KEY (id)); CREATE TABLE vital_signs ( id INTEGER NOT NULL, temperature VARCHAR(4), blood_pressure VARCHAR(10), pulse VARCHAR(10), weight INTEGER, height INTEGER, PRIMARY KEY (id)); CREATE TABLE record ( id INTEGER NOT NULL, pat_id INTEGER NOT NULL, phys_id INTEGER NOT NULL, record_date DATE NOT NULL, vital_id INTEGER NOT NULL, symptoms VARCHAR(255) NOT NULL, diagnosis VARCHAR(255), notes VARCHAR(255), PRIMARY KEY (id), FOREIGN KEY (pat_id) REFERENCES patient(id), FOREIGN KEY (phys_id) REFERENCES physician(id), FOREIGN KEY (vital_id) REFERENCES vital_signs(id)); CREATE TABLE prescription ( id INTEGER NOT NULL, pat_id INTEGER NOT NULL, date_prescribed DATE NOT NULL, drug VARCHAR(80) NOT NULL, record_id INTEGER NOT NULL, dosage VARCHAR(30) NOT NULL, frequency VARCHAR(30), refills_remaining INTEGER, instructions VARCHAR(255), PRIMARY KEY (id), FOREIGN KEY (pat_id) REFERENCES patient(id), FOREIGN KEY (record_id) REFERENCES record(id)); I hope that this is a simple and at the same time a very close to reality example , that will be very helpful to you and also to the readers of your blog and eventually future tutorial. I will appreciate your response to this challenge. I think also that this will be an excellent example to demonstrate powerfull capabilities of NetBeans 6.1. Thank you!

Uhmmm.. nevermind my last post... I found out how to do it. As it turns out, ulithegrey had already posted the solution... and somehow my eyes slipped to read it... Anyways, thanks again for this tutorial!

Hey! Its me again. I encountered a problem at step 5. At the Details option page, the radio button for the table is grayed out and I cannot select it... help... thanks!

Hi, I would really like if you could write a tutorial about displaying two database tables in the same jTable. For example, a simple query "Select * From Flights, Countries" and display the result of that query in a jTable. I've been searching it in a while and I just couldn't get any information about it, so I'm asking you is it possible? And if it is, how can we do it?? Thank you!

On Mac OS X Leopard with NetBeans 6.1 installed, the Table radio button in step 5 is disabled (grayed out.) How can I enable this? Also, would it be possible to add an image field to either the product table or the client table? I'd like a simple example of how images would work.

Thanks for the feedback, everybody! I'll will try to incorporate info on all of these questions in postings and/or tutorials in the future. wwedel, are you using the same db as the one I provided? If you are using a db table with a foreign key, the table option won't appear. If you are using a db based on the sql script I provided, which db software are you using?

Patrick: This has been a long time coming. Many thanks for making it happen. A couple of things I would like to see in future tutorials are: 1. The ins and outs of creating and using a navigation bar. Note that it has to be table independent. In other words, one nav bar on a form should be all that's necessary to navigate any number of tables on the same form and 2. How best to Implement New/Delete buttons that are not table independent. Again, one of each should be sufficient to affect any number of tables on a form.