RE: Newbie questions - database calls and caching data

[Tom Butler]

Chapter 10 within ‘Core Java Server Faces’ (david geary) covers database access – that may be a good place to start, especially if you are using Tomcat since that is the web container used in the examples – this chapter describes connection pooling which is useful if you have multiple users.   Your other questions really get more into design issues, and are subjective based on your specific application.   Here’s the general approach I’m using that involves a search example, however, its straightforward because I do not require database update for this functionality (i.e., search only, no update back to database):   create search form on .jsp (or .jsf) page when search form is submitted, it calls an action on the form bean in this method, I grab a connection to the database (using a method described in chapter 10 from the book above) I have a bean object that represents the fields from the database table I am reading as I read through the database rows, I create a new instance of the bean class, and add the columns from the database to the attributes of the bean class object (using getter/setter methods);  at the same time, after I populate the bean object with the data from the database row, add the bean object to an ArrayList (the arraylist will then contain a bean object for each database row) after I have read all of the rows, I disconnect from the database and then wrap the ArrayList in a ListDataModel object (which worked better for me with the tomahawk t:datatable component);  Note at this point I’m disconnected from the database and can use a java object to work with the data (of course if you need to update the data, you’ll need to modify this solution.)  The last step is that I use a Tomahawk T:datatable to display the ListDataModel;    Note I am newer to JSF/myfaces as well, so I’m not sure if this is a best practice, but this is what has worked for me.   Hope this helps.   Tom   From: Costa Basil [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 1:46 PM To: users@myfaces.apache.org Subject: Newbie questions - database calls and caching data   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, Find your next car at Yahoo! Canada Autos