Hi
Where is the best place to make database calls to display lists or search results and how to cache them? How can I avoid making unecessary calls to the database?
Here are a two scenarios:
1. Let's say I have a form where the user has to enter data and has to select data from a list or a combobox. The data in these lists come from various database small tables. What is the best place to make the database calls to extract the data for these lists? I looked at the cd catalog sample that comes with the blue print catalog samples, and the database calls are placed in the getXXX methods of the backing beans. Example:
public class AlbumView
...
public List getTrackList()
throws NamingException, SQLException
{
int currentId = getCurrentAlbum().getId();
if(cachedAlbumId != currentId) {
// If the album id was updated since our last query, query again
this.trackList = null;
this.trackCount = -1;
}
if(this.trackList == null) {
Connection connection = getConnection();
try {
// Only query if we don't have the result cached for this request.
; PreparedStatement s = getConnection().prepareStatement(
"SELECT track, title FROM songs WHERE album_id = ?");
s.setInt(1, currentId);
ResultSet results = s.executeQuery();
this.trackList = new ArrayList();
while(results.next()) {
TrackData track = new TrackData();
track.setTrack(results.getInt("track"));
track.setTitle(results.getString("title"));
this.trackList.add(track);
}
this.cachedAlbumId = currentId;
results.close();
s.close();
}
finally {
connection.close();
}
}
return this.trackList;
}
and in the jsp page we have:
<h:dataTable id="dataTable" border="1" value="#{AlbumView.trackList}"
var="track" rendered="#{AlbumView.trackCount > 0}">
<h:column>
...
The AlbumView is a backing bean:
<managed-bean>
<description>
&n bsp; The backing bean for the album page
</description>
<managed-bean-name>AlbumView</managed-bean-name>
<managed-bean-class>
com.sun.j2ee.blueprints.cdcatalog.AlbumView
</managed-bean-class>
<managed-bean-scope>request</managed-bean-scope>
</managed-bean>
What is not clear to me is the following. Let's say the user enters some invalid input and tries to save the data. Because there is some invalid input, then getTrackList will be called again next time the page is displayed. Right? What can I do in order in order to avoid that? I know the data in the track list doesn't change so when the page is re-displayed I don't want to requery the track list from the database.
Another problem that I see with this code is poor error handling. If the app bombs inside getTrackList probably the web application will jump to the jsp error page. Sometimes this is not desirable
Overall, what is your opinion on getting the database data in these getXXX functions? Please ignore the fact that the jdbc calls are placed in the backing bean.
2. Scenario 2
Searching stuff. Let's say I have a table with employees and I want to find employees by name. Assuming that the whole table with employees is not that big I want to cache the result of the search in memory.
In case the user opens a new search window, is there an easy way to avoid the clash of two search operations. Scenario: user enters criteria 1, clicks the search button, the app displays the search result, the user opens anther browser window, enters criteria 2, clicks search, the app displays the search result of criteria 2, the user moves to the first window, clicks next page, the app should display the second page of the first search.
Any links to articles or sample applications on these topics are more than welcomed.
Thank you,
Where is the best place to make database calls to display lists or search results and how to cache them? How can I avoid making unecessary calls to the database?
Here are a two scenarios:
1. Let's say I have a form where the user has to enter data and has to select data from a list or a combobox. The data in these lists come from various database small tables. What is the best place to make the database calls to extract the data for these lists? I looked at the cd catalog sample that comes with the blue print catalog samples, and the database calls are placed in the getXXX methods of the backing beans. Example:
public class AlbumView
...
public List getTrackList()
throws NamingException, SQLException
{
int currentId = getCurrentAlbum().getId();
if(cachedAlbumId != currentId) {
// If the album id was updated since our last query, query again
this.trackList = null;
this.trackCount = -1;
}
if(this.trackList == null) {
Connection connection = getConnection();
try {
// Only query if we don't have the result cached for this request.
; PreparedStatement s = getConnection().prepareStatement(
"SELECT track, title FROM songs WHERE album_id = ?");
s.setInt(1, currentId);
ResultSet results = s.executeQuery();
this.trackList = new ArrayList();
while(results.next()) {
TrackData track = new TrackData();
track.setTrack(results.getInt("track"));
track.setTitle(results.getString("title"));
this.trackList.add(track);
}
this.cachedAlbumId = currentId;
results.close();
s.close();
}
finally {
connection.close();
}
}
return this.trackList;
}
and in the jsp page we have:
<h:dataTable id="dataTable" border="1" value="#{AlbumView.trackList}"
var="track" rendered="#{AlbumView.trackCount > 0}">
<h:column>
...
The AlbumView is a backing bean:
<managed-bean>
<description>
&n bsp; The backing bean for the album page
</description>
<managed-bean-name>AlbumView</managed-bean-name>
<managed-bean-class>
com.sun.j2ee.blueprints.cdcatalog.AlbumView
</managed-bean-class>
<managed-bean-scope>request</managed-bean-scope>
</managed-bean>
What is not clear to me is the following. Let's say the user enters some invalid input and tries to save the data. Because there is some invalid input, then getTrackList will be called again next time the page is displayed. Right? What can I do in order in order to avoid that? I know the data in the track list doesn't change so when the page is re-displayed I don't want to requery the track list from the database.
Another problem that I see with this code is poor error handling. If the app bombs inside getTrackList probably the web application will jump to the jsp error page. Sometimes this is not desirable
Overall, what is your opinion on getting the database data in these getXXX functions? Please ignore the fact that the jdbc calls are placed in the backing bean.
2. Scenario 2
Searching stuff. Let's say I have a table with employees and I want to find employees by name. Assuming that the whole table with employees is not that big I want to cache the result of the search in memory.
In case the user opens a new search window, is there an easy way to avoid the clash of two search operations. Scenario: user enters criteria 1, clicks the search button, the app displays the search result, the user opens anther browser window, enters criteria 2, clicks search, the app displays the search result of criteria 2, the user moves to the first window, clicks next page, the app should display the second page of the first search.
Any links to articles or sample applications on these topics are more than welcomed.
Thank you,
Find your next car at Yahoo! Canada Autos
