Using Java DataBase Connectivity |
Sun ONE Studio 5 provides a JDBC (Java Database Connectivity) module that automates many programming tasks that you use when building client components or applications that interact with a database.
The goal of the Sun ONE Studio 5 JDBC module is to increase your productivity when programming visual forms that contain Swing (Java Foundation Class) components that use JDBC to retrieve and update database tables. You can use this module to assist you in generating simple, two-tiered application architectures.
This chapter describes the following JDBC productivity enhancement tools provided by Sun ONE Studio 5, and begins with a brief description of the steps you follow in creating a JDBC application. The tools include:
This section provides a brief introduction to JDBC programming tasks.
When you perform JDBC programming, you follow these general programming steps:
1. Import relevant classes within your code.
3. Establish a connection with a database.
5. Create try and catch blocks and retrieve exceptions and warnings.
6. Set up and use database tables.
c. Execute Statements to perform persistence operations.
iii. Create an updatable result set (RowSet).
iv. Insert and delete rows programmatically.
d. View changes in a ResultSet by managing the Transaction Isolation Level.
Sun ONE Studio 5 simplifies most of these tasks, generating JDBC code either through your editing of the Sun ONE Studio 5 JDBC JavaBeans component properties or through your use of the JDBC Form Wizard.
While this chapter provides a discussion of JDBC programming in the context of the Sun ONE Studio 5 IDE, it assumes familiarity with the basics of the JDBC programming model. For additional information about JDBC, you can review the following reference materials, grouped by function.
The Java Developer Connection provides an excellent tutorial on JDBC:
http://developer.java.sun.com/developer/onlineTraining/new2java/programming/learn/jdbc.html
In addition, the Java Developer Connection supplies a JDBC Short Course:
http://developer.java.sun.com/developer/onlineTraining/Database/JDBCShortCourse/index.html
Sun has produced a document entitled:
"Duke's Bakery - A JDBC Order Entry Prototype - Part I":
http://developer.java.sun.com/developer/technicalArticles/Database/dukesbakery/
The following index is a reference when starting to program using JDBC:
http://developer.java.sun.com/developer/technicalArticles/Interviews/StartJDBC/index.html
Another document is "Of Java, Databases, and Really Cool Dead Guys":
http://developer.java.sun.com/developer/technicalArticles/Interviews/Databases/index.html
You can find additional information on JDBC within the Sun tutorial:
http://java.sun.com/docs/books/tutorial/index.html
This tutorial also provides some references:
http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html
Before you begin the process of writing JDBC code, you need to understand the database that your application will use. To obtain database information, you can use the Sun ONE Studio 5 Database Explorer.
Using the Sun ONE Studio 5 Database Explorer, you can perform the following tasks:
To learn how to perform these tasks, refer to the Database Explorer Help within the Sun ONE Studio 5 IDE.
Sun ONE Studio 5 provides database connectivity and JDBC code generation tools for visual forms and components, specifically providing two basic types of components that you can use with your JDBC application:
The JDBC tab in the component palette contains icons for a number of JDBC JavaBeans components that you can use to facilitate the interaction of Java Swing components with a database. These components have properties that you customize using the Sun ONE Studio 5 Component Inspector.
A Connection source is a non-visual component that provides a connection to a JDBC compliant database. When you configure the Connection Source, you set:
A Pooled Connection Source component is similar to a Connection Source. However, when you specify the use of a Pooled Connection Source with your application, database connections that are established during application runtime are not closed when the application ceases to use the connection.
Instead, Sun ONE Studio 5 retains the connection in a pool for subsequent use within the runtime application. You can use a Pooled Connection Source when your application performs frequent open and close requests against a database to which it is connected.
A RowSet component represents rows fetched from the database. You can use these components to configure data models for several Swing components.
A RowSet object contains a set of rows from a JDBC result set or another source of tabular data, such as a file or spreadsheet.
Depending on how you implement them in your code, RowSets can be serializable or extensible to non-tabular sources of data.
Because a RowSet object follows the JavaBeans model for properties and event notification, it is a JavaBeans component that can be combined with other components in an application.
RowSets can be either connected or disconnected, depending on their implementation. A disconnected RowSet obtains a connection to a data source to fill itself with data or to propagate changes in data back to the data source, but most of the time it does not have a connection open.
Even when it is disconnected, a RowSet does not require the use of a JDBC driver or the full JDBC API, so its size is small. A disconnected RowSet is an ideal format for sending data over a network to a thin client.
The JDBC Tab makes three different types of row sets available:
You can customize a JDBC RowSet by setting the following properties under the properties tab in the Properties Editor:
The Other Properties Tab for a RowSet enables you to inspect and modify additional properties.
The Event Tab for a RowSet enables you to inspect and modify events associated with RowSets.
The Code Generation Tab enables you to specify pre- and post-processing code related to a rowset.
The JDBC module provides a visual component that provides direct navigation of a RowSet with a pre-built GUI. This component is useful when you need to create prototypical applications and when you want to create data entry applications.
You can customize a Data Navigator by setting the following properties under the properties tab in the Properties Editor of a Data Navigator.
Stored procedures are a group of SQL statements that form a logical unit and perform a specific task. Stored procedures encapsulate operations or queries that execute on a database server. Such procedures, of course, vary in their nature according to the database management system (DBMS) on whose server they execute.
Within the Sun ONE Studio 5 IDE, a stored procedure is a non-visual component that represents a database stored procedure in your JDBC application. You can call a stored procedure in response to an event initiated by a user within an application GUI (such as a button click).
The syntax for a stored procedure is different for each database management system that Sun ONE Studio 5 supports. For example, one database management system might use begin, end, or additional keywords to indicate the beginning and ending of the procedure definition, while a second DBMS might use other keywords to indicate the same parts of the procedure definition.
The JDBC Tutorial provides information on some of the stored procedures you can create for different databases, in addition to information on calling a stored procedure from your JDBC application.
You can customize a stored procedure by setting the following properties under the properties tab in the Properties Editor of a stored procedure. Once you have specified these properties in the property sheet, you can connect stored procedures to any user action.
Use the visual and non-visual components provided in the JDBC module in conjunction with Swing components to create forms that you use to retrieve and manipulate database data.
For example, a number of Swing components (JList, JTable, JComboBox, JButton, JToggleButton, JRadioButton, and JCheckbox) are associated with data models for the data they display. Within the IDE, you use Property Editors and the Component Inspector to customize the data model for these Swing components by specifying the JDBC components with which they interact to access a database. After you have completed specifying the JDBC components, Sun ONE Studio 5 generates the corresponding JDBC code.
The following Swing components have associated data models.:
You can configure these data models to use data from the database.
The most common component to display database tables is JTable. The model can be configured in the property sheet of each Swing component (under the model property).
Components that can display multiple rows, such as JTable or JList, also have the selectionModel property.
JList and JComboBox also have a special kind of model. This model consists of using one column from one RowSet to work with another column from another RowSet to display data, using a SQL join. See below for details.
Text components which have the document property (such as JTextField, JTextArea, JPasswordField, JTextPane, and JEditorPane) can set up this property to use data from the database.
1. For the model property in the JTable's property sheet, open the custom property editor by clicking on the value of the property and then clicking the ellipsis (...) button that appears.
2. Choose the TableEditor mode.
3. In the RowSet field, choose the RowSet to be displayed in the table.
4. Use Fetch columns to load column names into the list.
5. Use the Add, Remove, Edit, Move Up, and Move Down buttons to set the names and order of the columns in the table.
6. Click OK to preserve the changes and close the custom property editor.
1. For the selectionModel property in the component's property sheet, open the custom property editor by clicking on the value of the property and then clicking the ellipsis button (...) that appears.
2. In the RowSet field, choose the RowSet to be displayed in the table or list.
3. Click OK to preserve the changes and close the custom property editor.
1. For the model property in the component's property sheet, open the custom property editor (by clicking on the value of the property and then clicking the ellipsis button (...) that appears).
2. For the Primary RowSet fields, choose the RowSet for the data model to retrieve rows from, and then select one column from the Column drop-down list.
3. If you want, in the Secondary RowSet field, choose the RowSet to display data from (according to a SQL join). Corresponding columns from the primary and secondary RowSet must have the same data type.
4. If the Join check box is checked, a corresponding component displays the result of a database join. If it is unchecked, a corresponding component is used as a code map to set values in the primary rowset.
5. Choose a Data column (join column) and Display column (visible data). Click OK to preserve the changes and close the custom property editor.
|
1. For the model property in the component's property sheet, open the custom property editor (by clicking on the value of the property and then clicking the ellipsis (...) button that appears).
2. Choose the RowSet from which the data is to be fetched.
3. Choose a column; data from this column will be used to decide if the component should be selected.
4. Enter the database value corresponding to a selected component into the Select field and the value of an unselected component into the Unselect field.
5. Click OK to preserve the changes and close the custom property editor.
1. For the document property in the component's property sheet, open the custom property editor by clicking on the value of the property and then clicking the ellipsis button (...) that appears.
2. Choose the RowSet from which the data is to be fetched.
3. Choose a column in which to display the text component.
4. Click OK to preserve the changes and close the custom property editor.
After you have used the Property Editor to customize Swing components in your application, Sun ONE Studio 5 enables you to create a visual form associated with the Swing components that interacts with the database.
|
1. Create a Swing component form using a template provided in the Sun ONE Studio 5 IDE.
2. Add any needed Connection Source (or Pooled Connection Source), RowSet, or Stored Procedure nonvisual components to your form from the Component Palettes.
3. Using the corresponding Property Editor, customize these components for the database entities they represent.
4. Add any visual components you need, including the Data Navigator.
5. Use the corresponding Property Editor to customize the visual components appropriately, referencing the RowSet components you need.
As you specify the Swing components to use with your JDBC application, Sun ONE Studio 5 automatically creates the correct Swing classes to use in your application.
6. Use the Properties Editor for the specified form to indicate exceptions that should be caught during runtime and run the form.
You can use the Sun ONE Studio 5 Component Inspector to modify properties for components you use in your JDBC application. The following components can be found under Non-visual Components in the Component Inspector:
The Data Navigator component and other Swing components are shown according to their position in the container hierarchy.
The JDBC Form Wizard guides you through the creation of a form that can interact with database tables. It provides a substitute for the explicit editing of properties that you would otherwise perform if you used the approach outlined in Using JDBC Components. When you finish running the wizard, you will have a generated application, a file name for the application, and a package.
The following sections illustrate the JDBC Form Wizard, using the sample PointBase Server Database that comes included with the Sun ONE Studio 5 IDE.
When you use the JDBC Form Wizard or when you use the JDBC tab to create a JDBC client application, one of the first tasks you must perform is to establish a connection with the database management system that you want to use.
Typically, the JDBC Form Wizard or Sun ONE Studio 5 connection generates the code that you can use in your JDBC application when you use the Visual Form Editor or the JDBC Form Wizard to create a form. The application uses the form to populate information that it obtains from a database management system.
The second panel of the JDBC Form Wizard lets you establish a connection with a database. You can specify the use of a pooled connection for a DataSource in this panel.
When you need a new connection, you must supply:
Sun ONE Studio 5 provides these parameters to the JDBC application code that it generates.
You can select an existing connection by clicking the Use Existing Connection radio button, and selecting the connection from the drop-down list.
When you select the Next button, Sun ONE Studio 5 calls a method that creates a database connection based on parameters you enter. You use this connection to the database in the same way that you use the wizard to write JDBC application code.
The third panel of the JDBC Form Wizard lets you:
The JDBC Form Wizard lets you execute SQL statements against tables you specify in the Wizard. You use the data from the SQL output to populate visual forms. You can specify SQL statements which, when applied to a specific form, generate the appropriate SQL code. In FIGURE 3, Sun ONE Studio 5 provides a default SQL command to use with the table you have selected.
To avoid conflicts during a transaction, a database management system uses locks. Locks are operative until the application commits the transaction or rolls it back from the database.
Locks are set according to a transaction isolation level. Locks apply to the entire ResultSet that is returned to the application or committed from the application to the database.
Each database management system provides its own default transaction isolation level. Sun ONE Studio 5 lets you choose between the transaction isolation levels within the second panel of the JDBC Form Wizard.
Note - The driver and the data base management system must support the transaction isolation level you use. |
The fourth panel of the JDBC Form Wizard lets you select columns from the database tables to include in the form that is displayed. In this panel, you can specify:
In the example provided, JTable (the most common Swing form) is used. The JTable form displays more than one column of data in the application.
Other Swing component choices include:
In FIGURE 4, the first Column is selected. It can be removed or moved in position.
If you choose JList or JComboBox, only one column can be displayed, and you can choose a column to display from the Name property:
1. Select a value in the Name column.
2. Select a column name from the built-in combo box.
1. Click on the Title field you want to edit. An edit window appears with two tabs.
2. Select the String Value tab to enter the new name as a simple string value.
3. Select Resource Bundle to enter the name using a resource bundle. Enter the name of the bundle into Bundle Field, and select any related keys from the Keys combo box.
4. Select OK to close the edit window.
This panel displays a list of all available tables according to the database connection created on the Connection panel and is enabled only if a view supporting two RowSets (JList of JCheckbox) is selected.
You can use this panel to populate the secondary RowSet of the generated application.
1. Check Use Secondary Rowset.
If you check this rowset, the secondary rowset is used in the generated application.
2. Select either the Tables or Views radio button.
3. Select a type of rowset from the RowSet type combo box.
4. Select a table or view from the list.
5. Check Read-only if you want the corresponding rowset to be read-only.
6. Check Add rowInserted event handler to add a rowInserted event handler to the source code of the generated application.
The handler is called when a new row is inserted and enables the creation of default column values dynamically.
7. Choose a transaction isolation level for the rowset using one of the values in the Transaction isolation combo box.
The default transaction level is READ_COMMITTED.
8. Use the SQL_command text field to prepare SQL to populate the rowset.
By default, Sun ONE Studio 5 generates the text select * from table-name.
9. Select a data column to use with a database join.
Selecting this column will display a different field other than the primary column retrieved; however, it must be of the same data type as the primary column.
The last panel shows a preview of a generated application. Use this panel to complete your generated application. In addition, you can select a package and a file name to create a completed application.
Provide the name of the package under Package and the target file under Target.
You can view the component layout and the layout from the view of the Data Navigator. What you view depends on the Swing form you have chosen to contain the data that is manipulated in your application.
You can compile, run, and debug JDBC applications as if they were any other form. If you need special JDBC drivers, ensure they are in the Sun ONE Studio 5 CLASSPATH, so they will, by default, be available for external compiling, executing, and debugging of JDBC-based forms.
You can run your application external to the IDE by adding paths to these packages into your CLASSPATH:
If a WebRowSet is used in your JDBC application, two more JAR files are required:
Copyright © 2003, Sun Microsystems, Inc. All rights reserved.