Developing Database Applications
[Pages:214]Developing Database Applications
JBuilder? 2005
Borland Software Corporation 100 Enterprise Way Scotts Valley, California 95066-3249
Refer to the file deploy.html located in the redist directory of your JBuilder product for a complete list of files that you can distribute in accordance with the JBuilder License Statement and Limited Warranty.
Borland Software Corporation may have patents and/or pending patent applications covering subject matter in this document. Please refer to the product CD or the About dialog box for the list of applicable patents. The furnishing of this document does not give you any license to these patents.
COPYRIGHT ? 1997?2004 Borland Software Corporation. All rights reserved. All Borland brand and product names are trademarks or registered trademarks of Borland Software Corporation in the United States and other countries. All other marks are the property of their respective owners.
For third-party conditions and disclaimers, see the Release Notes on your JBuilder product CD.
Printed in the U.S.A.
JB2005database 10E13R0804 0405060708-9 8 7 6 5 4 3 2 1 PDF
Contents
Chapter 1
Introduction
1
Chapter summaries . . . . . . . . . . . . . . . . . . 2
Database tutorials. . . . . . . . . . . . . . . . . . . 3
Database samples . . . . . . . . . . . . . . . . . . 3
Related documentation . . . . . . . . . . . . . . . . 4
Documentation conventions . . . . . . . . . . . . . 6
Developer support and resources. . . . . . . . . . . 7
Contacting Borland Developer Support . . . . . . 7
Online resources. . . . . . . . . . . . . . . . . . 7
World Wide Web . . . . . . . . . . . . . . . . . . 8
Borland newsgroups . . . . . . . . . . . . . . . . 8
Usenet newsgroups . . . . . . . . . . . . . . . . 8
Reporting bugs . . . . . . . . . . . . . . . . . . 8
Chapter 2
Understanding JBuilder database
applications
11
Database application architecture . . . . . . . . . . 11
DataExpress components . . . . . . . . . . . . . . 12
Key features and benefits . . . . . . . . . . . . . 13
Overview of the DataExpress components . . . . 14
DataExpress for EJB components . . . . . . . . . . 17
InternetBeans Express . . . . . . . . . . . . . . . . 17
XML database components . . . . . . . . . . . . . . 17
dbSwing . . . . . . . . . . . . . . . . . . . . . . . . 17
Data modules and the Data Modeler . . . . . . . . . 18
Database Pilot . . . . . . . . . . . . . . . . . . . . 18
JDBC Monitor . . . . . . . . . . . . . . . . . . . . . 19
JDataStore and JBuilder . . . . . . . . . . . . . . . 19
When to use JDataStore versus JDBC drivers . . 19
Additional advantages of a JDataStore . . . . . . 20
Using the JDataStore Explorer . . . . . . . . . . 20
JDataStore explorer operations . . . . . . . . 21
InterBase and JBuilder . . . . . . . . . . . . . . . . 21
Chapter 3
Importing and exporting data
from a text file
23
Adding columns to a TableDataSet in the editor . . . 23
Importing formatted data from a text file . . . . . . . 24
Retrieving data from a JDBC data source . . . . . . 24
Exporting data . . . . . . . . . . . . . . . . . . . . 25
Exporting data from a QueryDataSet to a
text file . . . . . . . . . . . . . . . . . . . . . . 25
Saving changes from a TableDataSet to a
SQL table. . . . . . . . . . . . . . . . . . . . . 25
Saving changes loaded from a
TextDataFile to a JDBC data source . . . . . . . 26
Chapter 4
Connecting to a database
27
Connecting to databases . . . . . . . . . . . . . . 28
Adding a Database component to your
application . . . . . . . . . . . . . . . . . . . 28
Setting Database connection properties . . . . . 29
Setting up JDataStore . . . . . . . . . . . . . . . . 31
Setting up InterBase and InterClient. . . . . . . . . 31
Using InterBase and InterClient with JBuilder . . 32
Tips on using sample InterBase tables . . . . . . 32
Adding a JDBC driver to JBuilder . . . . . . . . . . 33
Creating the .library and .config files . . . . . . . 33
Adding the JDBC driver to projects. . . . . . . . 34
Connecting to a database using InterClient
JDBC drivers . . . . . . . . . . . . . . . . . . . . 35
Using the Database component in your
application . . . . . . . . . . . . . . . . . . . . . 37
Prompting for user name and password . . . . . . . 38
Pooling JDBC connections . . . . . . . . . . . . . 38
Optimizing performance of JConnectionPool. . . 40
Logging output . . . . . . . . . . . . . . . . . . 40
Pooling example . . . . . . . . . . . . . . . . . 40
Troubleshooting JDataStore and InterBase
connections . . . . . . . . . . . . . . . . . . . . 43
Common connection error messages . . . . . . 43
Chapter 5 Retrieving data from a data source 45
Querying a database . . . . . . . . . . . . . . . . 46 Setting properties in the query dialog box . . . . 47 The Query page . . . . . . . . . . . . . . . . 47 The Parameters page . . . . . . . . . . . . . 48 Place SQL text in resource bundle . . . . . . 49 Querying a database: Hints & tips . . . . . . . . 51 Enhancing data set performance . . . . . . . 51 Persisting query metadata. . . . . . . . . . . 52 Opening and closing data sets . . . . . . . . 52 Ensuring that a query is updatable . . . . . . 52
Using parameterized queries to obtain data from your database. . . . . . . . . . . . . . . . . 53 Parameterizing a query. . . . . . . . . . . . . . 53 Creating the application . . . . . . . . . . . . 53 Adding a Parameter Row . . . . . . . . . . . 54 Adding a QueryDataSet . . . . . . . . . . . . 54 Add the UI components . . . . . . . . . . . . 55 Parameterized queries: Hints & tips . . . . . . . 57 Using parameters . . . . . . . . . . . . . . . 57 Re-executing the parameterized query with new parameters. . . . . . . . . . . . . 59 Parameterized queries in master-detail relationships . . . . . . . . . . . . . . . . . 59
i
Chapter 6
Using stored procedures
61
Stored procedures: hints & tips . . . . . . . . . . . . 62
Escape sequences, SQL statements, and
server-specific procedure calls . . . . . . . . . 62
Using vendor-specific stored procedures. . . . . . . 63
Using JDataStore stored procedures and
user-defined functions . . . . . . . . . . . . . . 63
Using InterBase stored procedures . . . . . . . . 63
Using parameters with Oracle PL/SQL stored
procedures . . . . . . . . . . . . . . . . . . . . 64
Using Sybase stored procedures . . . . . . . . . 65
Sample application with database-server
specific stored procedures . . . . . . . . . . . . 65
Writing a custom data provider . . . . . . . . . . . . 65
Obtaining metadata . . . . . . . . . . . . . . . . 66
Invoking initData . . . . . . . . . . . . . . . . 66
Obtaining actual data . . . . . . . . . . . . . . . 67
Tips on designing a custom data provider . . . 67
Understanding the provideData() method
in master-detail data sets . . . . . . . . . . . 67
Chapter 7
Working with columns
69
Understanding Column properties and metadata . . 69
Non-metadata Column properties . . . . . . . . . 69
Viewing column information in the column
designer . . . . . . . . . . . . . . . . . . . . . 70
Generate RowIterator Class button . . . . . . . . 71
Using the column designer to persist
metadata . . . . . . . . . . . . . . . . . . . . . 71
Making metadata dynamic using the
column designer . . . . . . . . . . . . . . . . . 72
Viewing column information in the
Database Pilot . . . . . . . . . . . . . . . . . . 72
Optimizing a query . . . . . . . . . . . . . . . . . . 73
Setting column properties . . . . . . . . . . . . . 73
Setting Column properties using
JBuilder's visual design tools . . . . . . . . . 73
Setting properties in code . . . . . . . . . . . 73
Persistent columns . . . . . . . . . . . . . . . . 73
Combining live metadata with persistent
columns . . . . . . . . . . . . . . . . . . . . . 74
Removing persistent columns . . . . . . . . . . . 74
Using persistent columns to add empty
columns to a DataSet. . . . . . . . . . . . . 75
Controlling column order in a DataSet . . . . . . 75
Chapter 8
Saving changes back to your data
source
77
Saving changes from a QueryDataSet . . . . . . . . 78
Adding a button to save changes from a
QueryDataSet . . . . . . . . . . . . . . . . . . 79
Saving changes back to your data source with
a stored procedure . . . . . . . . . . . . . . . . . 80
Saving changes using a QueryResolver . . . . . 80
Coding stored procedures to handle data resolution . . . . . . . . . . . . . . . . . . . . . .81 Saving changes with a ProcedureResolver . . . .81 Example: Using InterBase stored procedures with return parameters . . . . . . . .83
Resolving data from multiple tables . . . . . . . . . .83 Considerations for the type of linkage between tables in the query . . . . . . . . . . .84 Table and column references (aliases) in a query string . . . . . . . . . . . . . . . . . . . .84 Controlling the setting of the column properties . .85 What if a table is not updatable? . . . . . . . . . .85 How can the user specify that a table should never be updated? . . . . . . . . . . . .85
Using DataSets with RMI (streamable data sets) . . .85 Example: Using streamable data sets . . . . . . .85 Using streamable DataSet methods . . . . . . . .86
Customizing the default resolver logic. . . . . . . . .87 Understanding default resolving . . . . . . . . . .87 Adding a QueryResolver component . . . . . .87 Intercepting resolver events. . . . . . . . . . .88 Using resolver events . . . . . . . . . . . . . .89 Writing a custom data resolver . . . . . . . . . . .90 Handling resolver errors . . . . . . . . . . . .90 Resolving master-detail relationships. . . . . .91
Chapter 9
Establishing a master-detail
relationship
93
Defining a master-detail relationship . . . . . . . . .94
Creating an application with a master-detail
relationship . . . . . . . . . . . . . . . . . . . .94
Fetching details . . . . . . . . . . . . . . . . . . . .97
Fetching all details at once . . . . . . . . . . . . .97
Fetching selected detail records on demand. . . .97
Editing data in master-detail data sets . . . . . . . .98
Steps to creating a master-detail relationship . . . . .99
Saving changes in a master-detail relationship . . . 100
Resolving master-detail data sets to a
JDBC data source . . . . . . . . . . . . . . . 100
Chapter 10
Using data modules to simplify
data access
103
Creating a data module using the design tools . . . 104
Create the data module with the wizard . . . . . 104
Add data components to the data module . . . . 104
Adding business logic to the data module . . . . 106
Using a data module . . . . . . . . . . . . . . . 106
Adding a required library to a project . . . . . 106
Referencing a data module in your
application . . . . . . . . . . . . . . . . . . 107
Understanding the Use DataModule
wizard . . . . . . . . . . . . . . . . . . . . 108
Creating data modules using the Data Modeler. . . 109
Creating queries with the Data Modeler . . . . . 109
Opening a URL . . . . . . . . . . . . . . . . 110
Beginning a query. . . . . . . . . . . . . . . 110
ii
Adding a Group By clause . . . . . . . . . . 112 Selecting rows with unique column values . . 113 Adding a Where clause . . . . . . . . . . . . 113 Adding an Order By clause . . . . . . . . . . 114 Editing the query directly . . . . . . . . . . . 114 Testing your query . . . . . . . . . . . . . . 114 Building multiple queries . . . . . . . . . . . 115 Specifying a master-detail relationship . . . . 115 Saving your queries . . . . . . . . . . . . . 116 Generating database applications . . . . . . . . 117 Using a generated data module in
your code . . . . . . . . . . . . . . . . . . 118
Chapter 11 Filtering, sorting, and locating data 121
Retrieving data for the examples . . . . . . . . . . 122 Filtering data . . . . . . . . . . . . . . . . . . . . 124
Adding and removing filters . . . . . . . . . . . 124 Sorting data . . . . . . . . . . . . . . . . . . . . . 127
Sorting data in a JdbTable . . . . . . . . . . . . 127 Sorting data using the JBuilder visual
design tools . . . . . . . . . . . . . . . . . . 128 Understanding sorting and indexing . . . . . 129
Sorting data in code . . . . . . . . . . . . . . . 130 Locating data . . . . . . . . . . . . . . . . . . . . 130
Locating data with a JdbNavField . . . . . . . . 130 Locating data programmatically . . . . . . . . . 132 Locating data using a DataRow . . . . . . . . . 133 Working with locate options . . . . . . . . . . . 133 Locates that handle any data type . . . . . . . . 134 Column order in the DataRow and DataSet . . . 134
Chapter 12
Adding functionality to
database applications
135
Using pick lists and lookups. . . . . . . . . . . . . 136
Data entry with a pick list . . . . . . . . . . . . 136
Adding a pick list field . . . . . . . . . . . . . . 136
Removing a pick list field . . . . . . . . . . . . 137
Create a lookup using a calculated column . . . 138
Create a lookup using the PickListDescriptor
parameters . . . . . . . . . . . . . . . . . . . 140
Using calculated columns . . . . . . . . . . . . . . 142
Create a calculated column in the designer . . . 143
Aggregating data with calculated fields . . . . . 144
Example: Aggregating data with
calculated fields . . . . . . . . . . . . . . . 144
Setting properties in the AggDescriptor . . . . . 147
Creating a custom aggregation event
handler . . . . . . . . . . . . . . . . . . . . . 147
Adding an Edit or Display Pattern for data
formatting . . . . . . . . . . . . . . . . . . . . . 148
Display masks . . . . . . . . . . . . . . . . . . 149
Edit masks . . . . . . . . . . . . . . . . . . . . 149
Using masks for importing and exporting
data . . . . . . . . . . . . . . . . . . . . . . 149
Data type dependent patterns . . . . . . . . . . 150 Patterns for numeric data . . . . . . . . . . . 150 Patterns for date and time data . . . . . . . . 150 Patterns for string data . . . . . . . . . . . . 151 Patterns for boolean data . . . . . . . . . . . 152
Presenting an alternate view of the data . . . . . . 152 Ensuring data persistence . . . . . . . . . . . . . . 153
Making columns persistent . . . . . . . . . . . . 154 Using variant data types . . . . . . . . . . . . . . . 155
Storing Java objects . . . . . . . . . . . . . . . 155
Chapter 13
Using other controls and events
157
Synchronizing visual components . . . . . . . . . . 157
Accessing data and model information from a
UI component . . . . . . . . . . . . . . . . . . . 158
Displaying status information . . . . . . . . . . . . 158
Building an application with a
JdbStatusLabel component . . . . . . . . . . . 158
Running the JdbStatusLabel application . . . . . 159
Handling errors and exceptions . . . . . . . . . . . 159
Overriding default DataSetException
handling on controls . . . . . . . . . . . . . . 160
Chapter 14
Creating a distributed database
application using DataSetData
161
Understanding the sample distributed database
application (using Java RMI and DataSetData) . . 161
Setting up the sample application . . . . . . . . 162
What is going on? . . . . . . . . . . . . . . . 163
Passing metadata by DataSetData . . . . . . 163
Deploying the application on multiple tiers . . 164
Chapter 15
Database administration tasks
165
Exploring database tables and metadata
using the Database Pilot . . . . . . . . . . . . . . 165
Browsing database schema objects . . . . . . . 166
Setting up drivers to access remote and
local databases . . . . . . . . . . . . . . . . . 166
Executing SQL statements . . . . . . . . . . . . 167
Using the Explorer to view and edit table data . . 168
Using the Database Pilot for database
administration tasks . . . . . . . . . . . . . . . . 170
Creating the SQL data source . . . . . . . . . . 170
Populating a SQL table with data using
JBuilder . . . . . . . . . . . . . . . . . . . . . 172
Deleting tables in JBuilder . . . . . . . . . . . . 172
Monitoring database connections . . . . . . . . . . 172
About the JDBC Monitor . . . . . . . . . . . . . 172
Using the JDBC Monitor in a running
application . . . . . . . . . . . . . . . . . . . 174
Adding the MonitorButton to the Palette. . . . 174
Using the MonitorButton Class from code. . . 174
Understanding MonitorButton properties . . . 174
iii
Chapter 16
Tutorial: Importing and exporting
data from a text file
175
Step 1: Creating the project . . . . . . . . . . . . 176
Step 2: Creating the text file . . . . . . . . . . . . 176
Step 3: Generating an application . . . . . . . . . 177
Step 4: Adding DataExpress components to
your application . . . . . . . . . . . . . . . . . . 177
Step 5: Adding dbSwing components to
create a user interface . . . . . . . . . . . . . . 178
Step 6: Adding a JButton Swing component . . . . 180
Step 7: Compiling and running your application . . 181
Step 8: Using patterns for exporting numeric,
date/time, and text fields . . . . . . . . . . . . . 182
Chapter 17
Tutorial: Creating a basic
database application
185
Step 1: Creating the project . . . . . . . . . . . . 187
Step 2: Generating an application . . . . . . . . . 187
Step 3: Adding DataExpress components to
your application . . . . . . . . . . . . . . . . . . 188
Step 4: Designing the columns for the application . . . . . . . . . . . . . . . . . . . . . 190 Adding columns and editing column properties . . . . . . . . . . . . . . . . . . . . 190 Specifying calculations for the calculated columns. . . . . . . . . . . . . . . . . . . . . 191
Step 5: Adding dbSwing components to create a user interface . . . . . . . . . . . . . . . 192
Step 6: Aggregating data with calculated fields . . . 194
Index
197
iv
Figures
2.1 Diagram of a typical database application . . 12 2.2 JDataStore Explorer . . . . . . . . . . . . . 20 4.1 Database component displayed in
structure pane. . . . . . . . . . . . . . . . . 29 4.2 Connection Descriptor dialog box. . . . . . . 30 5.1 Query property editor . . . . . . . . . . . . . 47 5.2 Parameters page . . . . . . . . . . . . . . . 49 5.3 Resource Bundle dialog . . . . . . . . . . . 49 8.1 UI for saving changes from a
QueryDataSet. . . . . . . . . . . . . . . . . 79 10.1 Data Modeler . . . . . . . . . . . . . . . . 109 10.4 Group By page . . . . . . . . . . . . . . . 112 10.5 Where page . . . . . . . . . . . . . . . . . 113 10.6 Order By page . . . . . . . . . . . . . . . 114 10.7 Link Queries dialog box . . . . . . . . . . . 115 10.8 Arrow showing relationship between
queries . . . . . . . . . . . . . . . . . . . 116 10.9 Editor showing code generated by
Data Modeler . . . . . . . . . . . . . . . . 116 10.10 Data Module Application wizard . . . . . . 117 11.1 Running database application . . . . . . . 123 11.2 Application running filters . . . . . . . . . . 126 11.3 Click on column header to sort at runtime . 127
11.4 Sort property editor . . . . . . . . . . . . . 128 11.5 Sorted application at runtime . . . . . . . . 129 11.6 Sample application with JdbNavField . . . . 131 12.1 Lookup application. . . . . . . . . . . . . . 140 12.2 Calculated columns . . . . . . . . . . . . . 144 12.3 Column designer. . . . . . . . . . . . . . . 154 15.1 Database Pilot . . . . . . . . . . . . . . . . 165 15.2 Enter SQL page of the Database Pilot. . . . 168 15.3 JDBC Monitor . . . . . . . . . . . . . . . . 173 15.4 JDBC Monitor with output . . . . . . . . . . 173 16.1 Import/export database application . . . . . 175 16.2 Import/Export application at runtime. . . . . 180 16.3 Exporting data to text file application
at runtime . . . . . . . . . . . . . . . . . . 181 17.1 Basic database application . . . . . . . . . 186 17.2 Query dialog box. . . . . . . . . . . . . . . 189 17.3 queryDataSet1 node expanded . . . . . . . 189 17.4 queryDataSet1 columns in the column
designer . . . . . . . . . . . . . . . . . . . 191 17.5 JdbTable component in the UI designer . . . 193 17.6 Basic database application with
navigation bar and status label . . . . . . . 194 17.7 Agg dialog box . . . . . . . . . . . . . . . . 195
v
Tutorials
Importing and exporting data from a text file . . . . 175 Creating a basic database application . . . . . . . 185
vi
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- java application developer s guide marklogic
- java application programming interface api
- introduction to programming in java
- java programming 4 java application building
- chapter14 graphical user interfaces building java programs
- basic java syntax java programming
- chapter 2 simple java applications people
- developing database applications
- learning computer programming using java with 101 examples
Related searches
- crm applications examples
- essays for college applications examples
- completing job applications worksheets
- hobby lobby applications for employment
- sample job applications printable
- nyc senior housing applications online
- applications of management information systems
- job applications online hiring now
- sears applications online job application
- practice job applications printable
- sample applications to fill out
- employee applications printable