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.

Google Online Preview   Download