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.

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:

Choose File | New Project.

Select the Java category and the Java Desktop Application template.

In the Name and Location page of the wizard, select the Database Application skeleton.

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.

In the Detail Options page, click the Table radio button and select the orders table from the combo box.

Comments

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

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

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:

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!

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.