Java > Open Source Codes > edu > rice > rubis > beans > QueryBeanPostgreSQL


1 package edu.rice.rubis.beans;
2
3 import java.rmi.RemoteException ;
4 import java.sql.Connection ;
5 import java.sql.PreparedStatement ;
6 import java.sql.ResultSet ;
7 import java.sql.SQLException ;
8 import java.util.Properties ;
9 import java.util.Vector ;
10 import javax.ejb.SessionBean ;
11 import javax.ejb.SessionContext ;
12 import javax.ejb.FinderException ;
13 import javax.ejb.ObjectNotFoundException ;
14 import javax.ejb.CreateException ;
15 import javax.ejb.RemoveException ;
16 import javax.ejb.EJBException ;
17 import javax.naming.Context ;
18 import javax.naming.InitialContext ;
19 import javax.sql.DataSource ;
20 import java.io.Serializable ;
21
22 /**
23  * PostgreSQL version:
24  * QueryBean is a stateless session bean used to perform requests
25  * on the RUBiS database as described in rubis.sql.
26  * @author <a HREF="mailto:cecchet@rice.edu">Emmanuel Cecchet</a> and <a HREF="mailto:julie.marguerite@inrialpes.fr">Julie Marguerite</a>
27  * @version 1.1
28  */
29
30 public class QueryBean implements SessionBean
31 {
32   protected SessionContext sessionContext;
33   protected DataSource dataSource = null;
34   
35
36   /**
37    * Get all the items that match a specific category and that are still
38    * to sell (auction end date is not passed). You must select the starting
39    * row and number of rows to fetch from the database to get only a limited
40    *number of items.
41    * For example, returns 25 Books.
42    *
43    * @param categoryId id of the category you are looking for
44    * @param regionId id of the region you are looking for
45    * @param startingRow row where result starts (0 if beginning)
46    * @param nbOfRows number of rows to get
47    *
48    * @return Vector of items primary keys
49    * @since 1.1
50    */
51   public Vector getCurrentItemsInCategory(Integer categoryId, int startingRow, int nbOfRows) throws RemoteException
52   {
53     Connection conn = null;
54     PreparedStatement stmt = null;
55     Vector v = new Vector (nbOfRows);
56
57     try
58     {
59       conn = dataSource.getConnection();
60       stmt = conn.prepareStatement("SELECT items.id FROM items WHERE items.category=? AND end_date>=NOW() ORDER BY items.end_date ASC LIMIT ?,?");
61       stmt.setInt(1, categoryId.intValue());
62       stmt.setInt(2, nbOfRows); // PostgreSQL
63 stmt.setInt(3, startingRow);
64       ResultSet rs = stmt.executeQuery();
65       // Build the vector of primary keys
66 while (rs.next())
67       {
68         ItemPK iPK = new ItemPK(new Integer (rs.getInt("id")));
69         v.addElement((Object )iPK);
70       };
71     }
72     catch (SQLException e)
73     {
74       throw new RemoteException ("Failed to executeQuery " +e);
75     }
76     finally
77     {
78       try
79       {
80         if (stmt != null) stmt.close(); // close statement
81 if (conn != null) conn.close(); // release connection
82 }
83       catch (Exception ignore)
84       {
85       }
86     }
87     return v;
88   }
89
90
91   /**
92    * Get all the items that match a specific category and region and
93    * that are still to sell (auction end date is not passed). You must
94    * select the starting row and number of rows to fetch from the database
95    * to get only a limited number of items.
96    * For example, returns 25 Books to sell in Houston.
97    *
98    * @param categoryId id of the category you are looking for
99    * @param regionId id of the region you are looking for
100    * @param startingRow row where result starts (0 if beginning)
101    * @param nbOfRows number of rows to get
102    *
103    * @return Vector of items primary keys
104    * @since 1.1
105    */
106   public Vector getCurrentItemsInCategoryAndRegion(Integer categoryId, Integer regionId, int startingRow, int nbOfRows) throws RemoteException
107   {
108     Connection conn = null;
109     PreparedStatement stmt = null;
110     Vector v = new Vector (nbOfRows);
111
112     try
113     {
114       conn = dataSource.getConnection();
115       stmt = conn.prepareStatement("SELECT items.id FROM items,users WHERE items.category=? AND items.seller=users.id AND users.region=? AND end_date>=NOW() ORDER BY items.end_date ASC LIMIT ?,?");
116       stmt.setInt(1, categoryId.intValue());
117       stmt.setInt(2, regionId.intValue());
118       stmt.setInt(3, nbOfRows); // PostgreSQL
119 stmt.setInt(4, startingRow);
120       ResultSet rs = stmt.executeQuery();
121
122       // Build the vector of primary keys
123 while (rs.next())
124       {
125         ItemPK iPK = new ItemPK(new Integer (rs.getInt("id")));
126         v.addElement((Object )iPK);
127       };
128     }
129     catch (SQLException e)
130     {
131       throw new RemoteException ("Failed to executeQuery " +e);
132     }
133     finally
134     {
135       try
136       {
137         if (stmt != null) stmt.close(); // close statement
138 if (conn != null) conn.close(); // release connection
139 }
140       catch (Exception ignore)
141       {
142       }
143     }
144     return v;
145   }
146
147
148   /**
149    * Get the maximum bid (winning bid) for an item.
150    *
151    * @param itemId item id
152    *
153    * @return maximum bid or 0 if no bid
154    * @exception RemoteException if an error occurs
155    * @since 1.0
156    */
157   public float getItemMaxBid(Integer itemId) throws RemoteException
158   {
159     Connection conn = null;
160     PreparedStatement stmt = null;
161     float maxBid = 0;
162
163     try
164     {
165       conn = dataSource.getConnection();
166       stmt = conn.prepareStatement("SELECT MAX(bid) AS bid FROM bids WHERE item_id=?");
167       stmt.setInt(1, itemId.intValue());
168       ResultSet rs = stmt.executeQuery();
169
170       // Get the max
171 if (rs.next())
172         maxBid = rs.getFloat("bid");
173     }
174     catch (SQLException e)
175     {
176       throw new RemoteException ("Failed to executeQuery " +e);
177     }
178     finally
179     {
180       try
181       {
182         if (stmt != null) stmt.close(); // close statement
183 if (conn != null) conn.close(); // release connection
184 }
185       catch (Exception ignore)
186       {
187       }
188     }
189     return maxBid;
190   }
191
192
193   /**
194    * Get the first <i>maxToCollect</i> bids for an item sorted from the
195    * maximum to the minimum.
196    *
197    * @param maxToCollect number of bids to collect
198    * @param itemId item id
199    *
200    * @return Vector of bids primary keys (can be less than maxToCollect)
201    * @exception RemoteException if an error occurs
202    * @since 1.0
203    */
204   public Vector getItemQtyMaxBid(int maxToCollect, Integer itemId) throws RemoteException
205   {
206     Connection conn = null;
207     PreparedStatement stmt = null;
208     Vector v = new Vector ();
209
210     try
211     {
212       conn = dataSource.getConnection();
213       stmt = conn.prepareStatement("SELECT id FROM bids WHERE item_id=? ORDER BY bid DESC LIMIT ?");
214       stmt.setInt(1, itemId.intValue());
215       stmt.setInt(2, maxToCollect);
216       ResultSet rs = stmt.executeQuery();
217
218       // Build the vector of primary keys
219 while (rs.next())
220       {
221         BidPK bPK = new BidPK(new Integer (rs.getInt("id")));
222         v.addElement((Object )bPK);
223       };
224     }
225     catch (SQLException e)
226     {
227       throw new RemoteException ("Failed to executeQuery " +e);
228     }
229     finally
230     {
231       try
232       {
233         if (stmt != null) stmt.close(); // close statement
234 if (conn != null) conn.close(); // release connection
235 }
236       catch (Exception ignore)
237       {
238       }
239     }
240     return v;
241   }
242
243
244   /**
245    * Get the number of bids for an item.
246    *
247    * @param itemId item id
248    *
249    * @return number of bids or 0 if no bid
250    * @exception RemoteException if an error occurs
251    * @since 1.0
252    */
253   public int getItemNbOfBids(Integer itemId) throws RemoteException
254   {
255     Connection conn = null;
256     PreparedStatement stmt = null;
257     int nbOfBid = 0;
258
259     try
260     {
261       conn = dataSource.getConnection();
262       stmt = conn.prepareStatement("SELECT COUNT(*) AS bid FROM bids WHERE item_id=?");
263       stmt.setInt(1, itemId.intValue());
264       ResultSet rs = stmt.executeQuery();
265
266       // Get the max
267 if (rs.next())
268         nbOfBid = rs.getInt("bid");
269     }
270     catch (SQLException e)
271     {
272       throw new RemoteException ("Failed to executeQuery " +e);
273     }
274     finally
275     {
276       try
277       {
278         if (stmt != null) stmt.close(); // close statement
279 if (conn != null) conn.close(); // release connection
280 }
281       catch (Exception ignore)
282       {
283       }
284     }
285     return nbOfBid;
286   }
287
288
289   /**
290    * Get the bid history for an item sorted from the last bid to the
291    * first bid (oldest one).
292    *
293    * @param itemId item id
294    *
295    * @return Vector of bids primary keys or null if no bids
296    * @exception RemoteException if an error occurs
297    * @since 1.0
298    */
299   public Vector getItemBidHistory(Integer itemId) throws RemoteException
300   {
301     Connection conn = null;
302     PreparedStatement stmt = null;
303     Vector v = new Vector ();
304
305     try
306     {
307       conn = dataSource.getConnection();
308       stmt = conn.prepareStatement("SELECT id FROM bids WHERE item_id=? ORDER BY date DESC");
309       stmt.setInt(1, itemId.intValue());
310       ResultSet rs = stmt.executeQuery();
311
312       // Build the vector of primary keys
313 while (rs.next())
314       {
315         BidPK bPK = new BidPK(new Integer (rs.getInt("id")));
316         v.addElement((Object )bPK);
317       };
318     }
319     catch (SQLException e)
320     {
321       throw new RemoteException ("Failed to executeQuery " +e);
322     }
323     finally
324     {
325       try
326       {
327         if (stmt != null) stmt.close(); // close statement
328 if (conn != null) conn.close(); // release connection
329 }
330       catch (Exception ignore)
331       {
332       }
333     }
334     return v;
335   }
336
337
338   /**
339    * Get all the items the user won in the last 30 days.
340    *
341    * @param userId user id
342    *
343    * @return Vector of items primary keys (can be less than maxToCollect)
344    * @exception RemoteException if an error occurs
345    * @since 1.0
346    */
347   public Vector getUserWonItems(Integer userId) throws RemoteException
348   {
349     Connection conn = null;
350     PreparedStatement stmt = null;
351     Vector v = new Vector ();
352
353     try
354     {
355       conn = dataSource.getConnection();
356       stmt = conn.prepareStatement("SELECT MAX(bid),item_id FROM bids, items WHERE bids.user_id=? AND bids.item_id=items.id AND TO_DAYS(NOW()) - TO_DAYS(items.end_date) < 30 GROUP BY item_id");
357       stmt.setInt(1, userId.intValue());
358       ResultSet rs = stmt.executeQuery();
359
360       // Build the vector of primary keys
361 while (rs.next())
362       {
363         ItemPK iPK = new ItemPK(new Integer (rs.getInt("bids.item_id")));
364         v.addElement((Object )iPK);
365       };
366     }
367     catch (SQLException e)
368     {
369       throw new RemoteException ("Failed to executeQuery " +e);
370     }
371     finally
372     {
373       try
374       {
375         if (stmt != null) stmt.close(); // close statement
376 if (conn != null) conn.close(); // release connection
377 }
378       catch (Exception ignore)
379       {
380       }
381     }
382     return v;
383   }
384
385
386   /**
387    * Get all the maximum bids for each item the user has bid on in the last 30 days.
388    *
389    * @param userId user id
390    *
391    * @return Vector of bids primary keys (can be less than maxToCollect)
392    * @exception RemoteException if an error occurs
393    */
394   public Vector getUserBids(Integer userId) throws RemoteException
395   {
396     Connection conn = null;
397     PreparedStatement stmt = null;
398     Vector v = new Vector ();
399
400     try
401     {
402       conn = dataSource.getConnection();
403       stmt = conn.prepareStatement("SELECT MAX(bid),bids.id FROM bids,items WHERE user_id=? AND bids.item_id=items.id AND items.end_date>=NOW() GROUP BY item_id");
404       stmt.setInt(1, userId.intValue());
405       ResultSet rs = stmt.executeQuery();
406
407       // Build the vector of primary keys
408 while (rs.next())
409       {
410         BidPK bPK = new BidPK(new Integer (rs.getInt("bids.id")));
411         v.addElement((Object )bPK);
412       };
413     }
414     catch (SQLException e)
415     {
416       throw new RemoteException ("Failed to executeQuery " +e);
417     }
418     finally
419     {
420       try
421       {
422         if (stmt != null) stmt.close(); // close statement
423 if (conn != null) conn.close(); // release connection
424 }
425       catch (Exception ignore)
426       {
427       }
428     }
429     return v;
430   }
431
432
433   // ======================== EJB related methods ============================
434
435   /**
436    * This method is empty for a stateless session bean
437    */
438   public void ejbCreate() throws CreateException , RemoteException
439   {
440   }
441
442   /** This method is empty for a stateless session bean */
443   public void ejbActivate() throws RemoteException {}
444   /** This method is empty for a stateless session bean */
445   public void ejbPassivate() throws RemoteException {}
446   /** This method is empty for a stateless session bean */
447   public void ejbRemove() throws RemoteException {}
448
449
450   /**
451    * Sets the associated session context. The container calls this method
452    * after the instance creation. This method is called with no transaction context.
453    * We also retrieve the Home interfaces of all RUBiS's beans.
454    *
455    * @param sessionContext - A SessionContext interface for the instance.
456    * @exception RemoteException - Thrown if the instance could not perform the function
457    * requested by the container because of a system-level error.
458    */
459   public void setSessionContext(SessionContext sessionContext) throws RemoteException
460   {
461     this.sessionContext = sessionContext;
462     if (dataSource == null)
463     {
464       // Finds DataSource from JNDI
465 Context initialContext = null;
466       try
467       {
468         initialContext = new InitialContext ();
469         dataSource = (DataSource )initialContext.lookup("java:comp/env/jdbc/rubis");
470       }
471       catch (Exception e)
472       {
473         throw new RemoteException ("Cannot get JNDI InitialContext");
474       }
475     }
476   }
477
478 }
479

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags