|
1
32
33
34
45 package stockonline.ejb.sql;
46
47 import java.sql.*;
48 import javax.sql.DataSource  ;
49 import java.util.*;
50
51 import stockonline.util.Holding;
52
53 public class StockHolding implements java.io.Serializable
54 {
55 final static boolean verbose = false;
56 final static String className = "StockHolding";
57
58 private int stock_id;
59 private int stock_amount;
60
61 public StockHolding () {}
62
63
68 public static Collection getHoldingList (Connection conn, int accountID, int start_stockID)
69 throws Exception
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 > ?";
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
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 (ex.toString());
108 }
109 finally
110 {
111 if(rs!=null) rs.close();
112 if(pstmt!=null) pstmt.close();
113 }
114 }
115
116
122 public static void updateForBuy (Connection conn, int accountID, int stockID, int amount)
123 throws Exception
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 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()) {
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 {
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 (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
183 public static void updateForSell (Connection conn, int accountID, int stockID, int amount)
184 throws Exception
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 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 amount = currentHolding;
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 (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 |
Java API By Example, From Geeks To Geeks.