· 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.SQLException;
7import java.sql.Statement;
8
9public class Accounts extends MySQLConnection{
10 //creates table accounts
11 public Accounts() throws SQLException{
12 String sql = "CREATE TABLE IF NOT EXISTS accounts "
13 + "(AccountId INTEGER, "
14 + "AccTypeId INTEGER, "
15 + "Balance INTEGER, "
16 + "PRIMARY KEY ( AccountID ))";
17
18 Connection conn = null;
19 try {
20 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
21 Statement stmt = conn.createStatement();
22 stmt.executeUpdate(sql);
23 System.out.println("Table accounts created successfully...");
24 } catch (SQLException e)
25 {
26 System.out.println(e.getMessage());
27 } finally
28 {
29 if(conn != null) {
30 if(!conn.isClosed()){
31 conn.close();
32 }
33 }
34 }
35 } //end of Accounts()
36//adds account data, no validation needed
37 public void addAccountsData(int accountId, int accTypeId, int balance) throws SQLException
38 {
39
40 String sql = "INSERT INTO accounts VALUES (?,?,?)";
41 Connection conn = null;
42
43 try {
44 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
45 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
46
47 stmt.setInt(1,accountId);
48 stmt.setInt(2, accTypeId);
49 stmt.setInt(3, balance);
50 int result = stmt.executeUpdate();
51 if (result == 1) {
52 System.out.println("'" + accountId + "', '" + accTypeId + "' and '" + balance + "' inserted successfully...");
53 }
54
55 } catch (SQLException e)
56 {
57 System.err.println(e);
58 } finally
59 {
60 if(conn != null) {
61 if(!conn.isClosed()){
62 conn.close();
63 }
64 }
65 }
66 }
67 //update
68 public void Update(int accountId, int acctTypeId, int balance) throws SQLException {
69
70 String sql = "UPDATE accounts SET AccTypeId = ?, Balance = ? WHERE AccountId = ?";
71 Connection conn = null;
72
73 try
74 {
75 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
76 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
77
78 stmt.setInt(1, acctTypeId);
79 stmt.setInt(2, balance);
80 stmt.setInt(3, accountId);
81 int result = stmt.executeUpdate();
82 if (result == 1) {
83 System.out.println("AccountId: " + accountId + " type and balance has been updated to '" + acctTypeId + "', $" + balance);
84 }
85 else {
86 System.out.println("Something went wrong updating " + accountId);
87 }
88 }
89 catch (SQLException e)
90 {
91 System.err.println(e);
92 }
93 finally
94 {
95 if(conn != null) {
96 if(!conn.isClosed()){
97 conn.close();
98 }
99 }
100 }
101
102}
103//delete
104public void Delete(int accountId) throws SQLException {
105
106 String sql = "DELETE FROM accounts WHERE AccountId = ?";
107 Connection conn = null;
108
109 try
110 {
111 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
112 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
113
114 stmt.setInt(1, accountId);
115 int result = stmt.executeUpdate();
116
117 if (result == 1) {
118 System.out.println("Account: " + accountId + " deleted successfully...");
119 } else
120 {
121 System.out.println("Something went wrong deleteing '" + accountId + "'");
122 }
123 }
124 catch (SQLException e)
125 {
126 System.err.println(e);
127 }
128 finally
129 {
130 if(conn != null) {
131 if(!conn.isClosed()){
132 conn.close();
133 }
134 }
135 }
136}
137}//END OF CLASS