· 7 years ago · Dec 03, 2018, 01:24 AM
1package mySQLTest;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.util.ArrayList;
10import java.util.Collection;
11
12public class Transaction extends MySQLConnection{
13 //creates table transaction
14 public Transaction() throws SQLException{
15 String sql = "CREATE TABLE IF NOT EXISTS transaction "
16 + "(TransId INTEGER, "
17 + "TranTypeId VARCHAR(255), "
18 + "AcctIdFrom INTEGER, "
19 + "AcctIdTo INTEGER, "
20 + "Amount INTEGER, "
21 + "PRIMARY KEY ( TransId ))";
22
23 Connection conn = null;
24 try {
25 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
26 Statement stmt = conn.createStatement();
27 stmt.executeUpdate(sql);
28 System.out.println("Table transaction created successfully...");
29 } catch (SQLException e)
30 {
31 System.out.println(e.getMessage());
32 } finally
33 {
34 if(conn != null) {
35 if(!conn.isClosed()){
36 conn.close();
37 }
38 }
39 }
40 } //end of Transaction()
41
42 public void addTransactionsData(int transId, String tranTypeId, int AcctIdFrom, int AcctIdTo, int amount) throws SQLException, ClassNotFoundException
43 {
44
45 String sql = "INSERT INTO transaction VALUES (?,?,?,?,?)";
46 // String getFromBalance = "SELECT Balance FROM accounts WHERE AccountId = ?";
47 // String getToBalance = "SELECT Balance FROM accounts WHERE AccountId = ?";
48 // String updateBalance = "UPDATE accounts SET Balance = ? WHERE AccountId = ?";
49
50 Connection conn = null;
51 if (validateAcctTypeData(tranTypeId, AcctIdFrom, AcctIdTo)) {
52
53 try {
54 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
55 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
56 /* PreparedStatement fromBalance = conn.prepareStatement(getFromBalance, Statement.RETURN_GENERATED_KEYS);
57 PreparedStatement toBalance = conn.prepareStatement(getToBalance, Statement.RETURN_GENERATED_KEYS);
58 PreparedStatement updateAccBalance = conn.prepareStatement(updateBalance, Statement.RETURN_GENERATED_KEYS);
59
60
61 fromBalance.setInt(1, AcctIdFrom);
62 toBalance.setInt(1, AcctIdTo);
63 ResultSet rsBalanceFrom = fromBalance.executeQuery();
64 int balanceFrom = rsBalanceFrom.getInt("Balance");
65 System.out.println("From balance: " + balanceFrom);
66 */
67 stmt.setInt(1,transId);
68 stmt.setString(2, tranTypeId);
69 stmt.setInt(3, AcctIdFrom);
70 stmt.setInt(4, AcctIdTo);
71 stmt.setInt(5, amount);
72 int result = stmt.executeUpdate();
73 validateBalance(AcctIdFrom, AcctIdTo, amount);
74 if (validateBalance(AcctIdFrom, AcctIdTo, amount) == false) {
75 return;
76 }
77 if (result == 1) {
78 System.out.println("'" + transId + "', '" + tranTypeId + "', " + AcctIdFrom + "', " + AcctIdTo + "' and '" + amount + "' inserted successfully...");
79 }
80
81
82 } catch (SQLException e)
83 {
84 System.err.println(e);
85 } finally
86 {
87 if(conn != null) {
88 if(!conn.isClosed()){
89 conn.close();
90 }
91 }
92 }
93
94 }
95 } //end of addAccountData()
96
97 public boolean validateAcctTypeData(String tranTypeId, int acctIdFrom, int acctIdTo) throws ClassNotFoundException, SQLException {
98 //validates tranTypeId and AccountId valid inputs
99 if (tranTypeId == "A" || tranTypeId == "C" || tranTypeId == "P" && validateAcctId(acctIdFrom, acctIdTo)){
100 return true;
101 } else {
102 System.out.println("Something went wrong when adding '" + tranTypeId + "' to Transaction");
103 return false;
104 }
105
106 }
107 //method to gather and validate if AccountID exists
108 public Boolean validateAcctId(int acctIdFrom, int acctIdTo) throws ClassNotFoundException, SQLException{
109 ArrayList<Integer> arr = new ArrayList<Integer>(); //create array
110 String returnAccountId = "SELECT AccountID FROM accounts"; //query statement
111 Connection conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
112 PreparedStatement ps = conn.prepareStatement(returnAccountId);
113 ResultSet rs = ps.executeQuery(); //get result set from query
114 while(rs.next()) { //add objects from result set to array
115 arr.add(rs.getInt("AccountID"));
116 }
117 for (int i = 0; i < arr.size(); i++) { //check through arraylist to match against new input
118 if (i == acctIdFrom || i == acctIdTo) {
119 return true;
120 }
121 }
122 System.out.println("One of the accounts was invalid; From: " + acctIdFrom + " To:" + acctIdTo + "...");
123 return false;
124 }
125 //function to update TransID rows
126 public void Update(int TransId, String TranTypeId, int AcctIdFrom, int AcctIdTo, int Amount) throws SQLException {
127
128 String sql = "UPDATE transaction SET TranTypeId = ?, AcctIdFrom = ?, AcctIdTo = ?, Amount = ? WHERE TransId = ?";
129 Connection conn = null;
130
131 try
132 {
133 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
134 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
135
136 stmt.setInt(5, TransId);
137 stmt.setString(1, TranTypeId);
138 stmt.setInt(2, AcctIdFrom);
139 stmt.setInt(3, AcctIdTo);
140 stmt.setInt(4, Amount);
141 int result = stmt.executeUpdate();
142 if (result == 1) {
143 System.out.println("TranId: " + TransId + " has been updated to '" + "'" + TranTypeId + "', '" + AcctIdFrom + "', '" + AcctIdTo + "', '" + Amount +"'...");
144 }
145 else {
146 System.out.println("Something went wrong updating '" + TransId + "'...");
147 }
148 }
149 catch (SQLException e)
150 {
151 System.err.println(e);
152 }
153 finally
154 {
155 if(conn != null) {
156 if(!conn.isClosed()){
157 conn.close();
158 }
159 }
160 }
161
162}
163
164 //function to delete rows
165public void Delete(int transId) throws SQLException {
166
167 String sql = "DELETE FROM transaction WHERE TransId = ?";
168 Connection conn = null;
169
170 try
171 {
172 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
173 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
174
175 stmt.setInt(1, transId);
176 int result = stmt.executeUpdate();
177
178 if (result == 1) {
179 System.out.println("Transaction ID: " + transId + " deleted successfully...");
180 } else
181 {
182 System.out.println("Something went wrong deleteing '" + transId + "'...");
183 }
184 }
185 catch (SQLException e)
186 {
187 System.err.println(e);
188 }
189 finally
190 {
191 if(conn != null) {
192 if(!conn.isClosed()){
193 conn.close();
194 }
195 }
196 }
197}
198
199//method to check if transaction can be completed, updates tables
200 public boolean validateBalance(int AcctIdFrom, int AcctIdTo, int amount) throws SQLException {
201
202 String sqlGetFromBalance = "SELECT Balance FROM accounts WHERE AccountId = ?";
203 String sqlGetToBalance = "SELECT Balance FROM accounts WHERE AccountId = ?";
204 String sqlUpdateBalance = "UPDATE accounts SET Balance = ? WHERE AccountId = ?";
205 Connection conn = null;
206
207
208 try {
209 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
210
211 PreparedStatement fromBalance = conn.prepareStatement(sqlGetFromBalance, Statement.RETURN_GENERATED_KEYS);
212 PreparedStatement toBalance = conn.prepareStatement(sqlGetToBalance, Statement.RETURN_GENERATED_KEYS);
213 PreparedStatement updateTheBalance = conn.prepareStatement(sqlUpdateBalance, Statement.RETURN_GENERATED_KEYS);
214
215 fromBalance.setInt(1, AcctIdFrom);
216 toBalance.setInt(1, AcctIdTo);
217 ResultSet rsBalanceFrom = fromBalance.executeQuery(); //get balance amount is coming from
218 ResultSet rsBalanceTo = toBalance.executeQuery(); //get balance amount is going to
219 //THIS WAS ADDED AFTER ERROR WAS BEING CAUSES. PRINTS FINE.
220 while(rsBalanceFrom.next()) {
221 int balanceFrom = rsBalanceFrom.getInt("Balance");
222 System.out.println("TESTING retrieve balance from =" + balanceFrom);
223 }
224 rsBalanceFrom.next(); //making sure account from will still have positive balance
225 int balanceFrom = rsBalanceFrom.getInt("Balance");
226 int afterFromBalance = balanceFrom - amount;
227 if (afterFromBalance <= 0) {
228 System.out.println("Insufficient funds for transaction...");
229 return false;
230 }
231
232 rsBalanceTo.next();
233 int balanceTo = rsBalanceTo.getInt("Balance"); //updating balances for after transaction
234 int afterToBalance = balanceTo + amount;
235 if (amount > 0) { //making sure transfer amount is not negative
236 updateTheBalance.setInt(1, AcctIdTo); //entering values to update account money is going to
237 updateTheBalance.setInt(2, afterToBalance);
238 int toBalanceUpdate = updateTheBalance.executeUpdate(); //update balance on to account
239 if (toBalanceUpdate == 1) {
240 System.out.println("$" + amount + " has been added to Account ID:" + AcctIdTo + " successfully...");
241 System.out.println("Account " + AcctIdTo + " new balance is $" + afterToBalance);
242 updateTheBalance.setInt(1, AcctIdFrom); //values for account money is coming from
243 updateTheBalance.setInt(2, afterFromBalance);
244 int fromBalanceUpdate = updateTheBalance.executeUpdate(); //update balance on from account
245 if (fromBalanceUpdate == 1) {
246 System.out.println("$" + amount + "has been removed from Accound ID:" + AcctIdFrom + " successfully...");
247 System.out.println("Account " + AcctIdFrom + "new balance is $" + afterFromBalance);
248 }
249 }
250 } else
251 {
252 System.out.println("You cannot transfer a negative amount...");
253 return false;
254 }
255
256
257 }
258 catch (SQLException e)
259 {
260 System.err.println(e);
261 }
262 finally
263 {
264 if(conn != null) {
265 if(!conn.isClosed()){
266 conn.close();
267 }
268 }
269 }
270 return true;
271 }
272
273}//END OF CLASS