KickJava   Java API By Example, From Geeks To Geeks.

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


1 package edu.rice.rubis.beans;
2
3 import java.rmi.RemoteException JavaDoc;
4 import java.sql.Connection JavaDoc;
5 import java.sql.PreparedStatement JavaDoc;
6 import java.sql.ResultSet JavaDoc;
7 import java.sql.SQLException JavaDoc;
8 import java.util.Properties JavaDoc;
9 import java.util.Vector JavaDoc;
10 import javax.ejb.SessionBean JavaDoc;
11 import javax.ejb.SessionContext JavaDoc;
12 import javax.ejb.FinderException JavaDoc;
13 import javax.ejb.ObjectNotFoundException JavaDoc;
14 import javax.ejb.CreateException JavaDoc;
15 import javax.ejb.RemoveException JavaDoc;
16 import javax.ejb.EJBException JavaDoc;
17 import javax.naming.Context JavaDoc;
18 import javax.naming.InitialContext JavaDoc;
19 import javax.sql.DataSource JavaDoc;
20 import java.io.Serializable JavaDoc;
21
22 /**
23  * MySQL version of Query Bean:
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 JavaDoc
31 {
32   protected SessionContext JavaDoc sessionContext;
33   protected DataSource JavaDoc 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 JavaDoc getCurrentItemsInCategory(Integer JavaDoc categoryId, int startingRow, int nbOfRows) throws RemoteException JavaDoc
52   {
53     Connection JavaDoc conn = null;
54     PreparedStatement JavaDoc stmt = null;
55     Vector JavaDoc v = new Vector JavaDoc(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, startingRow); // MySQL version
63
stmt.setInt(3, nbOfRows); // MySQL version
64
ResultSet JavaDoc rs = stmt.executeQuery();
65       // Build the vector of primary keys
66
while (rs.next())
67       {
68         ItemPK iPK = new ItemPK(new Integer JavaDoc(rs.getInt("id")));
69         v.addElement((Object JavaDoc)iPK);
70       };
71     }
72     catch (SQLException JavaDoc e)
73     {
74       throw new RemoteException JavaDoc("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 JavaDoc 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 JavaDoc getCurrentItemsInCategoryAndRegion(Integer JavaDoc categoryId, Integer JavaDoc regionId, int startingRow, int nbOfRows) throws RemoteException JavaDoc
107   {
108     Connection JavaDoc conn = null;
109     PreparedStatement JavaDoc stmt = null;
110     Vector JavaDoc v = new Vector JavaDoc(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, startingRow); // MySQL version
119
stmt.setInt(4, nbOfRows); // MySQL version
120
ResultSet JavaDoc rs = stmt.executeQuery();
121
122       // Build the vector of primary keys
123
while (rs.next())
124       {
125         ItemPK iPK = new ItemPK(new Integer JavaDoc(rs.getInt("id")));
126         v.addElement((Object JavaDoc)iPK);
127       };
128     }
129     catch (SQLException JavaDoc e)
130     {
131       throw new RemoteException JavaDoc("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 JavaDoc 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 JavaDoc itemId) throws RemoteException JavaDoc
158   {
159     Connection JavaDoc conn = null;
160     PreparedStatement JavaDoc 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 JavaDoc rs = stmt.executeQuery();
169
170       // Get the max
171
if (rs.next())
172         maxBid = rs.getFloat("bid");
173     }
174     catch (SQLException JavaDoc e)
175     {
176       throw new RemoteException JavaDoc("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 JavaDoc 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 JavaDoc getItemQtyMaxBid(int maxToCollect, Integer JavaDoc itemId) throws RemoteException JavaDoc
205   {
206     Connection JavaDoc conn = null;
207     PreparedStatement JavaDoc stmt = null;
208     Vector JavaDoc v = new Vector JavaDoc();
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 JavaDoc rs = stmt.executeQuery();
217
218       // Build the vector of primary keys
219
while (rs.next())
220       {
221         BidPK bPK = new BidPK(new Integer JavaDoc(rs.getInt("id")));
222         v.addElement((Object JavaDoc)bPK);
223       };
224     }
225     catch (SQLException JavaDoc e)
226     {
227       throw new RemoteException JavaDoc("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 JavaDoc 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 JavaDoc itemId) throws RemoteException JavaDoc
254   {
255     Connection JavaDoc conn = null;
256     PreparedStatement JavaDoc 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 JavaDoc rs = stmt.executeQuery();
265
266       // Get the max
267
if (rs.next())
268         nbOfBid = rs.getInt("bid");
269     }
270     catch (SQLException JavaDoc e)
271     {
272       throw new RemoteException JavaDoc("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 JavaDoc 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 JavaDoc getItemBidHistory(Integer JavaDoc itemId) throws RemoteException JavaDoc
300   {
301     Connection JavaDoc conn = null;
302     PreparedStatement JavaDoc stmt = null;
303     Vector JavaDoc v = new Vector JavaDoc();
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 JavaDoc rs = stmt.executeQuery();
311
312       // Build the vector of primary keys
313
while (rs.next())
314       {
315         BidPK bPK = new BidPK(new Integer JavaDoc(rs.getInt("id")));
316         v.addElement((Object JavaDoc)bPK);
317       };
318     }
319     catch (SQLException JavaDoc e)
320     {
321       throw new RemoteException JavaDoc("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 JavaDoc 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 JavaDoc getUserWonItems(Integer JavaDoc userId) throws RemoteException JavaDoc
348   {
349     Connection JavaDoc conn = null;
350     PreparedStatement JavaDoc stmt = null;
351     Vector JavaDoc v = new Vector JavaDoc();
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 JavaDoc rs = stmt.executeQuery();
359
360       // Build the vector of primary keys
361
while (rs.next())
362       {
363         ItemPK iPK = new ItemPK(new Integer JavaDoc(rs.getInt("bids.item_id")));
364         v.addElement((Object JavaDoc)iPK);
365       };
366     }
367     catch (SQLException JavaDoc e)
368     {
369       throw new RemoteException JavaDoc("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 JavaDoc 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 JavaDoc getUserBids(Integer JavaDoc userId) throws RemoteException JavaDoc
395   {
396     Connection JavaDoc conn = null;
397     PreparedStatement JavaDoc stmt = null;
398     Vector JavaDoc v = new Vector JavaDoc();
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 JavaDoc rs = stmt.executeQuery();
406
407       // Build the vector of primary keys
408
while (rs.next())
409       {
410         BidPK bPK = new BidPK(new Integer JavaDoc(rs.getInt("bids.id")));
411         v.addElement((Object JavaDoc)bPK);
412       };
413     }
414     catch (SQLException JavaDoc e)
415     {
416       throw new RemoteException JavaDoc("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 JavaDoc 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 JavaDoc, RemoteException JavaDoc
439   {
440   }
441
442   /** This method is empty for a stateless session bean */
443   public void ejbActivate() throws RemoteException JavaDoc {}
444   /** This method is empty for a stateless session bean */
445   public void ejbPassivate() throws RemoteException JavaDoc {}
446   /** This method is empty for a stateless session bean */
447   public void ejbRemove() throws RemoteException JavaDoc {}
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 JavaDoc sessionContext) throws RemoteException JavaDoc
460   {
461     this.sessionContext = sessionContext;
462     if (dataSource == null)
463     {
464       // Finds DataSource from JNDI
465
Context JavaDoc initialContext = null;
466       try
467       {
468         initialContext = new InitialContext JavaDoc();
469         dataSource = (DataSource JavaDoc)initialContext.lookup("java:comp/env/jdbc/rubis");
470       }
471       catch (Exception JavaDoc e)
472       {
473         throw new RemoteException JavaDoc("Cannot get JNDI InitialContext");
474       }
475     }
476   }
477
478 }
479
Popular Tags