Apache Jakarta and Beyond: A Java Programmeramp;#039;s Introduction [Electronic resources] نسخه متنی

10.3.1. Defining the Database and Objects

Before using OJB some thought must be given to what information will live in objects, what information will live in the database, and how the two sets of data will be related. In almost all situations the "obvious" connection is the right one; there will Chapter 8 could be mapped to a table with the following definition:

create table artist (
artist_id  int,
name       varchar(100),
unique (artist_id),
primary key (artist_id)
);

The designation of artist_id as unique ensures that no two artists have the same ID. The designation as a primary key allows certain optimizations. In particular, in hsqldb this will automatically create an

index, an internal data structure that can be used to rapidly find data without having to scan through all rows. Not all databases will automatically create such an index; those that don't should also be issued the following command:

create index index_artist_id
on artist(artist_id);

Marking the primary key and creating the index are not necessary to use OJB because OJB is capable of treating a field as a primary key even if it is not marked as such in the database. However, it is good standard practice to mark primary keys and create indexes where appropriate because doing so often leads to dramatic improvements in database performance.

So far no mention has been made of compound data, such as the list of albums available for each artist. There are numerous ways in which such relationships may be defined in the database. The two most common are called

one-to-many relationships and

many-to-many relationships.

In one-to-many relationships one row or item of data is connected to many others, such as one artist with many albums. These are defined with a reference to the parent table in the child, so the definition for the album table would include a reference to the artist table

create table album (
album_id int,
artist_id int,
year_released int,
name varchar(200)
unique (album_id),
primary key (album_id),
foreign key (artist_id) references artist(artist_id)
);
create index index_album_artist_id
on album(artist_id);

Chapter 8. The good news is that this is exactly how OJB handles such relationships. The details will be discussed shortly.

In addition, it may be useful to introduce inverse relationships, also called

back pointers in the beans. Although all previous examples started with an Artist and from there accessed the list of Albums and then TRacks, in a full-fledged application there may be instances where an Album would be obtained first, perhaps by searching for a particular album name. From the album the user might want to get the name of the artist, which in BeanShell notation would be expressed as album.artist.name.

This information is already present in the database in the form of the artist_id in the album table. On the bean side this means it is possible to get an Artist by calling getArtistId() and using the returned ID to ask OJB for the corresponding Artist bean. To make things more convenient, it is worth adding an Artist property to the Album bean and letting OJB populate this property automatically. Note that this is a

one-to-one relationship, because each album has only one artist. Therefore, the Album bean will have only a single Artist rather than an array.

The definitions of the track table and track bean follow the same pattern and so will not be shown here. They can be found on the companion CD-ROM.

Many-to-many relationships are somewhat more complicated than one-to-many. To see this, consider how the database would be extended to track live shows. A logical approach would be to create a new venue table with information such as the name and address of the venue. An artist may have appeared at many venues, and one venue will have hosted many artists, so there is a many-to-many relationship. This information could be stored by placing an artist_id in the venue table and a venue_id in the artist table. This would be terribly inefficient, however, because every time an artist played a new venue, a new entry would need to be made in the artist table identical to the previous entry except for the venue_id. Similar redundancy would arise every time a venue hosted a new artist. So instead of storing the relationship information in the tables a third table, called a

join table, will be introduced. This table will hold a venue_id and artist_id, and as an added bonus a date indicating when the event took place (or will take place, in the more useful case of an upcoming show).

create table venue (
venue_id int,
address1 varchar(50),
address2 varchar(50),
city     varchar(20),
state_province varchar(10),
country        varchar(10),
unique (venue_id),
primary key (venue_id)
);
create table live_show (
venue_id int,
artist_id int,
event_date datetime,
foreign key (venue_id) references venue(venue_id),
foreign key (artist_id) references artist(artist_id)
);
create index show_artist_id
on live_show(artist_id);
create index show_venue_id
on live_show(venue_id);

Chapter 8.

Listing 10.6. The artist bean

package com.awl.toolbook.chapter10;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.io.Serializable;
public class Artist
extends PersistentObject
implements Serializable
{
public Artist() {super();}
private int artistId;
public int getArtistId() {return artistId;}
public void setArtistId(int artistId) {
this.artistId = artistId; }
private String name;
public String getName() {return name;}
public void setName(String name) {this.name = name;}
private List albums = new ArrayList();
public List getAlbums() {return albums; }
public void setAlbums(List albums) {
this.albums = albums;
}
public Album getAlbum(int i) {
return (Album) albums.get(i);
}
public void setAlbum(int i, Album a) {
if(i == albums.size()) {
albums.add(a);
} else {
albums.set(i,a);
}
}
private List venues = new ArrayList();
public List getVenues() {return venues; }
public void setVenues(List venues) {
this.venues = venues;
}
public Venue getVenue(int i) {
return (Venue) venues.get(i);
}
public void setVenue(int i,Venue a) {
if(i == venues.size()) {
venues.add(a);
} else {
venues.set(i,a);
}
}
private List liveShows = new ArrayList();
public List getLiveShows() {return liveShows;}
public void setLiveShows(List liveShows) {
this.liveShows = liveShows;
}
public LiveShow getLiveShow(int i) {
return (LiveShow) liveShows.get(i);
}
public void setLiveShow(int i,LiveShow a) {
if(i == liveShows.size()) {
liveShows.add(a);
} else {
liveShows.set(i,a);
}
}
}