KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > stockonline > ejb > sql > StockHolding


1 /*
2  * StockOnline: EJB 1.1 Benchmark.
3  *
4  * Copyright Commonwealth Scientific and Industrial Research Organisation (CSIRO - www.csiro.au), Australia 2001, 2002, 2003.
5  *
6  * Contact: Paul.Brebner@csiro.au
7  *
8  * This library is free software; you can redistribute it and/or modify it
9  * under the terms of the GNU Lesser General Public License as published by
10  * the Free Software Foundation; either version 2.1 of the License, or any
11  * later version.
12  *
13  * This library is distributed in the hope that it will be useful, but WITHOUT
14  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
15  * FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License
16  * for more details.
17  *
18  * You should have received a copy of the GNU Lesser General Public License
19  * along with this library; if not, write to the Free Software Foundation,
20  * Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
21  *
22  * Originally developed for the CSIRO Middleware Technology Evaluation (MTE) Project, by
23  * the Software Architectures and Component Technologies Group, CSIRO Mathematical and Information Sciences
24  * Canberra and Sydney, Australia
25  *
26  * www.cmis.csiro.au/sact/
27  * www.cmis.csiro.au/adsat/mte.htm
28  *
29  * Initial developer(s): Shiping Chen, Paul Brebner, Lei Hu, Shuping Ran, Ian Gorton, Anna Liu.
30  * Contributor(s): ______________________.
31  */

32
33
34 //
35
//
36
// History:
37
// 10/08/2001 Shiping Initial coding based on the existing code
38
// 25/11/2001 Shiping Change to avoid application exceptions:
39
// If an account does not have enough holding, still keep going.
40
//
41
//
42
//
43
//
44

45 package stockonline.ejb.sql;
46
47 import java.sql.*;
48 import javax.sql.DataSource JavaDoc;
49 import java.util.*;
50
51 import stockonline.util.Holding;
52
53 public class StockHolding implements java.io.Serializable JavaDoc
54 {
55     final static boolean verbose = false;
56     final static String JavaDoc className = "StockHolding";
57
58     private int stock_id;
59     private int stock_amount;
60
61     public StockHolding () {}
62
63     /** To get holding list for an account
64     * @param conn The connection to the database
65     * @param accountID The account iditifier
66     * @param start_stockID The stockID from which the list starts, default is 0
67     */

68     public static Collection getHoldingList (Connection conn, int accountID, int start_stockID)
69     throws Exception JavaDoc
70     {
71         if(verbose) System.out.println(className + ".getHoldingList(" + accountID + "," + start_stockID + ") called");
72
73         PreparedStatement pstmt = null;
74         ResultSet rs = null;
75         int rowNum = 20;
76
77         try
78         {
79            // String sql = "SELECT stock_id, amount FROM StockHolding WHERE sub_accno = ? AND stock_id > ? AND rownum <= ?";
80
String JavaDoc sql = "SELECT stock_id, amount FROM StockHolding WHERE sub_accno = ? AND stock_id > ?";
81             if(verbose) System.out.println(sql);
82
83             pstmt = conn.prepareStatement(sql);
84             pstmt.setInt(1, accountID);
85             pstmt.setInt(2, start_stockID);
86             // pstmt.setInt(3, rowNum);
87

88             rs = pstmt.executeQuery();
89
90             ArrayList list = new ArrayList();
91                 for(int i=0; rs.next() && i<rowNum; i++)
92             {
93                 Holding holding = new Holding();
94
95                     holding.stock_id = rs.getInt(1);
96                     holding.amount = rs.getInt(2);
97                 if(verbose) System.out.println("stock_id = " + holding.stock_id + " amount = " + holding.amount);
98
99                     list.add( holding );
100                 }
101
102             return list;
103         }
104         catch(SQLException ex)
105         {
106             System.err.println("Exception in StockHolding.getHoldingList(): " + ex.getMessage());
107             throw new Exception JavaDoc(ex.toString());
108         }
109         finally
110         {
111             if(rs!=null) rs.close();
112             if(pstmt!=null) pstmt.close();
113         }
114         }
115
116     /** To update the stock holding for buy transactions
117     * @param conn The connection to the database
118     * @param accountID The account iditifier
119     * @param stockID The stock iditifier
120     * @param amount The amount to buy
121     */

122     public static void updateForBuy (Connection conn, int accountID, int stockID, int amount)
123     throws Exception JavaDoc
124     {
125         if(verbose) System.out.println(className + ".updateForBuy(" + accountID + "," + stockID + "," + amount + ") called");
126  
127         PreparedStatement pstmt = null;
128         PreparedStatement pstmt1 = null;
129         ResultSet rs = null;
130
131         try
132         {
133             String JavaDoc sql = "SELECT amount FROM StockHolding WHERE sub_accno = ? AND stock_id = ?";
134             if(verbose) System.out.println(sql);
135
136             pstmt = conn.prepareStatement(sql);
137             pstmt.setInt(1, accountID);
138             pstmt.setInt(2, stockID);
139             rs = pstmt.executeQuery();
140
141             if (rs.next()) // if the account holds the stock, then update the record
142
{
143                 sql = "UPDATE StockHolding SET amount = ? WHERE sub_accno = ? AND stock_id = ?";
144                 if(verbose) System.out.println(sql);
145
146                     pstmt1 = conn.prepareStatement(sql);
147                 pstmt1.setInt(1, rs.getInt(1) + amount);
148                 pstmt1.setInt(2, accountID);
149                 pstmt1.setInt(3, stockID);
150                 pstmt1.executeUpdate();
151             }
152             else // if the account does NOT hold the stock, then create a new record
153
{
154                 sql = "INSERT INTO StockHolding VALUES (?,?,?)";
155                 if(verbose) System.out.println(sql);
156
157                 pstmt1 = conn.prepareStatement(sql);
158                 pstmt1.setInt(1, accountID);
159                 pstmt1.setInt(2, stockID);
160                 pstmt1.setInt(3, amount);
161                 pstmt1.executeUpdate();
162             }
163         }
164         catch(SQLException ex)
165         {
166             System.err.println("Exception in StockHolding.updateForBuy(): " + ex.getMessage());
167             throw new Exception JavaDoc(ex.toString());
168         }
169         finally
170         {
171             if(rs!=null) rs.close();
172             if(pstmt!=null) pstmt.close();
173             if(pstmt1!=null) pstmt1.close();
174         }
175     }
176
177     /** To update the stock holding for sell transactions
178     * @param conn The connection to the database
179     * @param accountID The account iditifier
180     * @param stockID The stock iditifier
181     * @param amount The amount to sell
182     */

183     public static void updateForSell (Connection conn, int accountID, int stockID, int amount)
184     throws Exception JavaDoc
185     {
186         if(verbose) System.out.println(className + ".updateForSell(" + accountID + "," + stockID + "," + amount + ") called");
187  
188         PreparedStatement pstmt = null;
189         PreparedStatement pstmt1 = null;
190         ResultSet rs = null;
191
192         try
193         {
194             String JavaDoc sql = "SELECT amount FROM StockHolding WHERE sub_accno = ? AND stock_id = ?";
195             if(verbose) System.out.println(sql);
196
197             pstmt = conn.prepareStatement(sql);
198             pstmt.setInt(1, accountID);
199             pstmt.setInt(2, stockID);
200             rs = pstmt.executeQuery();
201
202             if (!rs.next())
203                 throw new SQLException("StockHolding.updateForSell(): The account does not hold the stock to sell");
204
205             int currentHolding = rs.getInt(1);
206             if(verbose) System.out.println("currentHolding = " + currentHolding);
207
208             if(currentHolding < amount)
209                 // throw new SQLException("StockHolding.updateForSell(): The account does not hold enough the stock to sell");
210
amount = currentHolding; // To sell the current holding amount
211

212             sql = "UPDATE StockHolding SET amount = ? WHERE sub_accno = ? AND stock_id = ?";
213             if(verbose) System.out.println(sql);
214
215                 pstmt1 = conn.prepareStatement(sql);
216             pstmt1.setInt(1, currentHolding - amount);
217             pstmt1.setInt(2, accountID);
218             pstmt1.setInt(3, stockID);
219             pstmt1.executeUpdate();
220         }
221         catch(SQLException ex)
222         {
223             System.err.println("Exception in StockHolding.updateForSell(): " + ex.getMessage());
224             throw new Exception JavaDoc(ex.toString());
225         }
226         finally
227         {
228             if(rs!=null) rs.close();
229             if(pstmt!=null) pstmt.close();
230             if(pstmt1!=null) pstmt1.close();
231         }
232     }
233 }
Popular Tags