Journal 8: Database Connectivity, Part 1: Working with Java DB (Derby)

Return to: The Learning Curve Journals»

Now that I've worked with components and consumed web services using the Java Studio Creator 2 IDE, I'm ready to learn what I can do with database integration. Mostly, I work with Microsoft SQL Server and MySQL for production and, on occasion, Microsoft Access for development. But for this first part of a two-part article, I'll work with the Java DB (Derby), which is the database bundled with the IDE. In part two I'll look at integrating databases for production use, such as Microsoft SQL Server and MySQL.

Java Studio Creator 2 comes with the Sun Java System Application Server, and this application server includes the Java DB, which is Sun's supported distribution of the open source Apache Derby database. Java DB is a pure Java Relational Database Management System (RDBMS) for client-server networked applications. Built entirely from Java, Java DB contains all the advantages that Java provides—platform independence for one.

Note: The PointBase database was bundled with earlier versions of Java Studio Creator. However, PointBase was not licensed for production use. This, along with other limitations, motivated the upgrade to Java DB with Java Studio Creator 2.

Java Studio Creator also supports various editions of Oracle, Microsoft SQL Server 2000, Sybase, IBM DB2 and MySQL. More details in the next article!

I have been encountering the acronym JDBC often as I learn about Java. The Java Database Connectivity API provides database-independent connectivity among Java applications and a wide range of SQL databases, even access to tabular data sources and flat files. The JDBC API is written in Java and thus allows my database applications to benefit from all the great features of Java. This allows for my applications to maintain security throughout the database transaction, and also for my applications to maintain portability across various platforms (and databases). It's write once, run anywhere technology.

My non-Java applications used ODBC. This API is written in C, and is thus not appropriate for use with Java, as my applications would inherit many of the drawbacks C contains in security and portability. The JDBC API is more advanced than the ODBC API, and includes important features such as Microsoft's more recent UDA (Universal Data Access) database technology, as an example.

More information on JDBC and how it compares to ODBC can be found in this article.

Now I'll start working with the sample databases included with Java Studio Creator, and then try writing a simple database application using the IDE. First I start a new project. Then, I look at the Servers window for a list of the available data sources. These include Jump Start Cycles, Travel and VIR (Vehicle Incident Reporter), as shown in Figure 1.

A side note: The data sources I mention above are actually all contained within one database called "Sample." The Java DB sample database is located in the SunAppServer8/derby folder. Log files and a few utilities programs are also included there.

If the bundled database server isn't already running, I need to start it up and take a look at the tables available in that database. I do this by right-clicking the Bundled Database Server node. (I can also do this from the data pull-down menu). If the database is stopped, the option to stop the database server is grayed out and a red icon appears next to the node, and I start it as described above. Once the bundled database is started, I can see the tables, views, and columns within tables, in the sample databases shown in Figure 1.

I decided to experiment with an application that would pull in data from several tables and display the results. Start up the IDE and follow along.

Here's my goal: I want to produce a report from several database tables—a flight table, a trip table, and an employee table—that displays flights for particular trips and people. I only want to include a certain piece of data in the report, and for the data columns to appear in a particular order, with column headings that will make sense to the user.

First, I create a new application. To do this, I choose New Project from the File menu (or I can click on Create New Project from the Welcome screen that defaults open when Creator starts). Then I choose JSF Web Application from the New Project dialog window that pops open.

Then I go to the Palette window (next to the Servers window by default) and drop a Table component onto the visual designer. This displays a generic table with several columns and rows, as shown in Figure 2.

Since I want the report to focus on the flight data, I then go to the Servers window and open the table node for the Travel database. I drag the FLIGHT table from the Servers window onto this generic table in the visual designer. I make sure that the entire outline of the generic table is highlighted (the outline actually changes to a blue color) before I drop the FLIGHT table onto it. My flight data is now bound to the page's display, and the generic table has changed to show me the columns or fields of the FLIGHT table, as shown in Figure 3.

Try running the application now. You'll see that what you get is the data from this FLIGHT table in a simple HTML table, nicely formatted with shaded rows. But I want to take it further than this?to add the trip and employee data.

To add trip and employee data, I need to use Java Studio Creator's Query Editor.

In the Outline window, I expand on the SessionBean1 node, and notice the flightRowSet entry. (See Figure 4.) This rowset contains the SQL command associated with the table, and was automatically placed into the Session Bean when I dragged the FLIGHT table onto the generic table in the visual designer. This makes it available across my application session. I'll now right-click on it and select Edit SQL Statement—or double-click on it—to open it in the Query Editor.

The Query Editor window is divided into four panels. At the top is a graphical display of the table. Below, the table columns are displayed: the table name, sort order, and so on. The IDE also shows me the SQL query itself. I'll test this query by running it now, without having to build the rest of the application, by right clicking Run Query in the top panel. As shown in Figure 5, all the flight data is displayed in the table in the bottom panel of the window.

Now, to customize my query I'll add the PERSON and TRIP tables to the Query Editor. It's easiest to do this by right-clicking in the top panel and choosing Add Table, and then selecting multiple tables in the dialog box that opens. But I can also drag the tables from the Servers window and drop them onto the topmost panel in the Query Editor. The IDE then adds these tables to the visual display, and also adds their fields to its table display and to the SQL query itself. Since the TRIP table includes foreign keys to the PERSON and FLIGHT tables, the Query Editor shows these connections graphically.

As shown in Figure 6, columns that will be displayed in a query have a check mark next to the column name. By default, when I add a table to the query, all the columns in that table are set to display.

I first tried un-checking these columns in the Query Editor so certain data would not display when I run my application. But this exposed a limitation in the table component that has to do with synchronization between the Query Editor and the Table Layout design-time interface. So for now, leave all columns checked so they display. For further information, see the tech tip titled How to Avoid RowSet Display Synchronization Errors in Java Studio Creator 2.

My remaining task is to change the appearance of the data in the application, particularly the column headings and column order, and to specify which data should appear at run-time. To do this, I go back to the visual designer. (I can easily toggle back and forth between the visual designer and the query editor using the tabs at the top.) I then right-click on my table (being sure that the whole table is outlined) and choose Table Layout.

Here, the Columns tab in the dialog lets me choose the columns I would like displayed, rename column headings and easily move the position of the columns. I make my changes, and then use the Apply button to view the results. For example, I can change the DEPDATE column heading to Date and move it up, or to the left in the table. It's great to be able to see the results as I work.

Then I choose the Options tab, shown in Figure 8, to enable pagination for this application. With paging enabled, the IDE automatically takes care of displaying the specified number of rows of data per page. Some very cool features can be added to my table at run-time. These features include added controls and links that allow me to page through the data, expand the table to include all data, and create complex sorting between various rows by clicking on them. Try experimenting with them in the deployed table.

Now I'm ready to run the application. To do this, I simply select Run Main Project from the Run menu, then wait for the IDE to compile, build, and deploy my application. Then the IDE opens a browser window and I can view the data retrieved by the application, formatted the way I wanted it, as shown in Figure 9.

Now that I've learned how to work with the sample databases included in Java Studio Creator, I'm ready to take a look at producing production-level applications with the Microsoft SQL Server database and the MySQL database. I'll also explore much of the important underlying technology that allows for standardized connections to databases, including how to work with data providers and property bindings.

For more tech tips, articles, and expert advice for developers, visit the Java Studio Creator developer resources on the Sun Developer Network (SDN) at /jscreator/.