|
1
37
38 package com.sun.j2ee.blueprints.catalog.dao;
39
40 import java.sql.Connection  ;
41 import java.sql.PreparedStatement ;
42 import java.sql.ResultSet ;
43 import java.sql.SQLException ;
44 import javax.sql.DataSource ;
45 import java.util.Locale ;
46 import java.util.ArrayList ;
47
48
49 import com.sun.j2ee.blueprints.util.tracer.Debug;
50 import com.sun.j2ee.blueprints.util.dao.*;
51 import com.sun.j2ee.blueprints.catalog.*;
52
53
54
60 public class PointbaseCatalogDAO implements CatalogDAO {
61
62 private final static String SELECT_LODGINGS_QUERY_STR = "SELECT "+
63 "lodgingid, name, description, price, location" +" FROM "+ DatabaseNames.LODGING_TABLE +
64 " WHERE location = ? AND locale = ?";
65 private final static String SELECT_LODGING_QUERY_STR = "SELECT "+
66 "lodgingid, name, description, price, location" +" FROM "+ DatabaseNames.LODGING_TABLE +
67 " WHERE lodgingid = ? AND locale = ?";
68 private final static String SELECT_TRANSPORTATIONS_QUERY_STR = "SELECT "+
69 "transportationid, name, description, imageuri, price, origin, destination,carrier,departuretime, arrivaltime, class"+" FROM " + DatabaseNames.TRANSPORTATION_TABLE +
70 " WHERE origin = ? AND destination = ? AND locale = ?";
71 private final static String SELECT_TRANSPORTATION_QUERY_STR = "SELECT "+
72 "transportationid, name, description, imageuri, price, origin, destination,carrier,departuretime, arrivaltime, class"+" FROM " + DatabaseNames.TRANSPORTATION_TABLE +
73 " WHERE transportationid = ? AND locale = ?";
74 private final static String SELECT_ACTIVITIES_QUERY_STR = "SELECT "+
75 "activityid, name, description, price, location "+" FROM " + DatabaseNames.ACTIVITY_TABLE +
76 " WHERE location = ? AND locale = ?";
77 private final static String SELECT_ACTIVITY_QUERY_STR = "SELECT "+
78 "activityid, name, description, price, location "+" FROM " + DatabaseNames.ACTIVITY_TABLE +
79 " WHERE activityid = ? AND locale = ?";
80 private final static String SELECT_ADVENTURE_PACKAGE_QUERY_STR = "SELECT "+
81 "name, description, price, location, lodgingid "+" FROM " + DatabaseNames.PACKAGE_TABLE +
82 " WHERE packageid = ? AND locale = ?";
83 private final static String SELECT_ACTIVITYLIST_QUERY_STR = "SELECT "+
84 "activityid"+" FROM " + DatabaseNames.ACTIVITYLIST_TABLE +
85 " WHERE packageid = ? AND locale = ?";
86
87 public PointbaseCatalogDAO() {
88 }
89
90 public ArrayList getLodgings(String location, Locale locale)
91 throws CatalogDAOException, DAOSystemException {
92
93 PreparedStatement stmt = null;
94 ResultSet result = null;
95 Connection dbConnection = null;
96
97 ArrayList lodgings = new ArrayList ();
98 try {
99 dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
100 stmt = dbConnection.prepareStatement(SELECT_LODGINGS_QUERY_STR);
101 stmt.setString(1, location.trim());
102 stmt.setString(2, locale.toString().trim());
103 result = stmt.executeQuery();
104 if ( !result.next() )
105 throw new CatalogDAOException(
106 "No data found for " + location +" , "+locale.toString());
107 do {
108 int i = 1;
109 String lodgingId = result.getString(i++);
110 String name = result.getString(i++);
111 String description = result.getString(i++);
112 double price = result.getDouble(i++);
113 location = result.getString(i++);
114 lodgings.add(new Lodging(lodgingId , name, description, price, location));
115 }
116 while(result.next());
117
118 return(lodgings );
119 } catch(SQLException se) {
120 throw new DAOSystemException("SQLException while getting " +
121 "lodging details; location = " + location +" and locale = "+locale.toString()+" \n", se);
122 } finally {
123 DAOUtils.closeResultSet(result);
124 DAOUtils.closeStatement(stmt);
125 DAOUtils.closeConnection(dbConnection);
126 }
127 }
128
129
130
131 public Lodging getLodging(String id, Locale locale)
132 throws CatalogDAOException, DAOSystemException {
133
134 PreparedStatement stmt = null;
135 ResultSet result = null;
136 Connection dbConnection = null;
137
138 try {
139 dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
140 stmt = dbConnection.prepareStatement(SELECT_LODGING_QUERY_STR);
141 stmt.setString(1, id.trim());
142 stmt.setString(2, locale.toString().trim());
143 result = stmt.executeQuery();
144 if ( !result.next() )
145 throw new CatalogDAOException(
146 "No data found for " + id +" , "+locale.toString());
147 int i = 1;
148 String lodgingId = result.getString(i++);
149 String name = result.getString(i++);
150 String description = result.getString(i++);
151 double price = result.getDouble(i++);
152 String location = result.getString(i++);
153 return new Lodging(lodgingId , name, description, price, location);
154 } catch(SQLException se) {
155 throw new DAOSystemException("SQLException while getting " +
156 "lodging details; id = " + id +" and locale = "+locale.toString()+" \n", se);
157 } finally {
158 DAOUtils.closeResultSet(result);
159 DAOUtils.closeStatement(stmt);
160 DAOUtils.closeConnection(dbConnection);
161 }
162 }
163
164 public AdventurePackage getAdventurePackage(String packageId, Locale locale)
165 throws CatalogDAOException, DAOSystemException {
166
167 PreparedStatement stmt = null;
168 PreparedStatement stmt2 = null;
169 ResultSet result = null;
170 ResultSet result2 = null;
171 Connection dbConnection = null;
172
173 ArrayList transportations = new ArrayList ();
174 try {
175 dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
176 stmt = dbConnection.prepareStatement(SELECT_ADVENTURE_PACKAGE_QUERY_STR);
177 stmt.setString(1, packageId.trim());
178 stmt.setString(2, locale.toString().trim());
179 result = stmt.executeQuery();
180 if ( !result.next() )
181 throw new CatalogDAOException(
182 "No data found for " + packageId +" , "+locale.toString());
183 int i = 1;
184 String name = result.getString(i++).trim();
185 String description = result.getString(i++).trim();
186 double price = result.getDouble(i++);
187 String location = result.getString(i++).trim();
188 String lodgingId = result.getString(i++).trim();
189 ArrayList activities = new ArrayList ();
190 stmt2 = dbConnection.prepareStatement(SELECT_ACTIVITYLIST_QUERY_STR);
192 stmt2.setString(1, packageId);
193 stmt2.setString(2, locale.toString().trim());
194 result2 = stmt2.executeQuery();
195
196 while (result2.next()) {
197 String activityId = result2.getString(1).trim();
198 activities.add(activityId);
199 }
200 AdventurePackage ap = new AdventurePackage(packageId,
201 name, description, location, lodgingId, price, activities);
202 return(ap);
203 } catch(SQLException se) {
204 throw new DAOSystemException("SQLException while getting " +
205 "AdventurePackage details; origin = " + packageId +", and locale = "+locale.toString()+" \n", se);
206 } finally {
207 DAOUtils.closeResultSet(result);
208 DAOUtils.closeStatement(stmt);
209 DAOUtils.closeResultSet(result2);
210 DAOUtils.closeStatement(stmt2);
211 DAOUtils.closeConnection(dbConnection);
212 }
213 }
214
215 public ArrayList getTransportations(String origin, String destination, Locale locale)
216 throws CatalogDAOException, DAOSystemException {
217
218 PreparedStatement stmt = null;
219 ResultSet result = null;
220 Connection dbConnection = null;
221
222 ArrayList transportations = new ArrayList ();
223 try {
224 dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
225 stmt = dbConnection.prepareStatement(SELECT_TRANSPORTATIONS_QUERY_STR);
226 stmt.setString(1, origin.trim());
227 stmt.setString(2, destination.trim());
228 stmt.setString(3, locale.toString().trim());
229 result = stmt.executeQuery();
230 if ( !result.next() )
231 throw new CatalogDAOException(
232 "No data found for " + origin +" , "+ destination +" , "+locale.toString());
233 do {
234 int i = 1;
235 String transportId = result.getString(i++);
236 String name = result.getString(i++);
237 String description = result.getString(i++);
238 String imageURI = result.getString(i++);
239 double price = result.getDouble(i++);
240 origin = result.getString(i++);
241 destination = result.getString(i++);
242 String carrier = result.getString(i++);
243 String departureTime = result.getString(i++);
244 String arrivalTime = result.getString(i++);
245 String travelClass = result.getString(i++);
246 transportations.add(new Transportation(transportId , name, description, imageURI, price,origin,destination,carrier,departureTime,arrivalTime,travelClass));
247 }
248 while(result.next());
249
250 return(transportations);
251 } catch(SQLException se) {
252 throw new DAOSystemException("SQLException while getting " +
253 "Transportation details; origin = " + origin +" , "+"destination = "+destination+" and locale = "+locale.toString()+" \n", se);
254 } finally {
255 DAOUtils.closeResultSet(result);
256 DAOUtils.closeStatement(stmt);
257 DAOUtils.closeConnection(dbConnection);
258 }
259 }
260
261 public Transportation getTransportation(String id, Locale locale)
262 throws CatalogDAOException, DAOSystemException {
263
264 PreparedStatement stmt = null;
265 ResultSet result = null;
266 Connection dbConnection = null;
267
268 try {
269 dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
270 stmt = dbConnection.prepareStatement(SELECT_TRANSPORTATION_QUERY_STR);
271 stmt.setString(1, id.trim());
272 stmt.setString(2, locale.toString().trim());
273 result = stmt.executeQuery();
274 if ( !result.next() )
275 throw new CatalogDAOException(
276 "No data found for " + id +" , "+locale.toString());
277 int i = 1;
278 String transportId = result.getString(i++);
279 String name = result.getString(i++);
280 String description = result.getString(i++);
281 String imageURI = result.getString(i++);
282 double price = result.getDouble(i++);
283 String origin = result.getString(i++);
284 String destination = result.getString(i++);
285 String carrier = result.getString(i++);
286 String departureTime = result.getString(i++);
287 String arrivalTime = result.getString(i++);
288 String travelClass = result.getString(i++);
289 return new Transportation(transportId , name, description, imageURI,
290 price, origin, destination, carrier, departureTime, arrivalTime, travelClass);
291 } catch(SQLException se) {
292 throw new DAOSystemException("SQLException while getting " +
293 "Transportation details; id = " + id +" and locale = "+locale.toString()+" \n", se);
294 } finally {
295 DAOUtils.closeResultSet(result);
296 DAOUtils.closeStatement(stmt);
297 DAOUtils.closeConnection(dbConnection);
298 }
299 }
300
301
302 public ArrayList getActivities(String location, Locale locale)
303 throws CatalogDAOException, DAOSystemException {
304
305 PreparedStatement stmt = null;
306 ResultSet result = null;
307 Connection dbConnection = null;
308
309 ArrayList activities = new ArrayList ();
310 try {
311 dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
312 stmt = dbConnection.prepareStatement(SELECT_ACTIVITIES_QUERY_STR);
313 stmt.setString(1, location.trim());
314 stmt.setString(2, locale.toString().trim());
315 result = stmt.executeQuery();
316 if ( !result.next() )
317 throw new CatalogDAOException(
318 "No data dound for " + location +" , "+locale.toString());
319 do {
320 int i = 1;
321 String activityId = result.getString(i++);
322 String name = result.getString(i++);
323 String description = result.getString(i++);
324 double price = result.getDouble(i++);
325 location = result.getString(i++);
326 activities.add(new Activity(activityId , name, description, price,location));
327 }
328 while(result.next());
329
330 return(activities);
331 } catch(SQLException se) {
332 throw new DAOSystemException("SQLException while getting " +
333 "Activity details; location = " + location +" and locale = "+locale.toString()+" \n", se);
334 } finally {
335 DAOUtils.closeResultSet(result);
336 DAOUtils.closeStatement(stmt);
337 DAOUtils.closeConnection(dbConnection);
338 }
339 }
340
341
342 public Activity getActivity(String id, Locale locale)
343 throws CatalogDAOException, DAOSystemException {
344
345 PreparedStatement stmt = null;
346 ResultSet result = null;
347 Connection dbConnection = null;
348
349 try {
350 dbConnection = DAOUtils.getDBConnection(JNDINames.CATALOG_DATASOURCE);
351 stmt = dbConnection.prepareStatement(SELECT_ACTIVITY_QUERY_STR);
352 stmt.setString(1, id.trim());
353 stmt.setString(2, locale.toString().trim());
354 result = stmt.executeQuery();
355 if ( !result.next() )
356 throw new CatalogDAOException(
357 "No data dound for " + id +" , "+locale.toString());
358 int i = 1;
359 String activityId = result.getString(i++);
360 String name = result.getString(i++);
361 String description = result.getString(i++);
362 double price = result.getDouble(i++);
363 String location = result.getString(i++);
364 return new Activity(activityId , name, description, price,location);
365 } catch(SQLException se) {
366 throw new DAOSystemException("SQLException while getting " +
367 "Activity details; id = " + id +" and locale = "+locale.toString()+" \n", se);
368 } finally {
369 DAOUtils.closeResultSet(result);
370 DAOUtils.closeStatement(stmt);
371 DAOUtils.closeConnection(dbConnection);
372 }
373 }
374 }
375 |
Popular Tags |
Java API By Example, From Geeks To Geeks.