· 7 years ago · Dec 03, 2018, 01:22 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 AccountType extends MySQLConnection{
10 //constructor to create account_type
11 public AccountType() throws SQLException{
12 String sql = "CREATE TABLE IF NOT EXISTS account_type "
13 + "(AccTypeId VARCHAR(255), "
14 + "AccTypeDesc VARCHAR(255), "
15 + "PRIMARY KEY ( AccTypeId ))";
16 Connection conn = null;
17 try {
18 //execute query if table added print to console success
19 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
20 Statement stmt = conn.createStatement();
21 stmt.executeUpdate(sql);
22 System.out.println("Table account_type created successfully...");
23 } catch (SQLException e)
24 {
25 System.out.println(e.getMessage());
26 } finally
27 {
28 if(conn != null) {
29 if(!conn.isClosed()){
30 conn.close();
31 }
32 }
33 }
34 } //End of AccountType()
35 //method to validate and add data to account_type table
36 public void addAcctTypeData(int AccTypeID, String AccountTypeDesc) throws SQLException
37 {
38 //validates data being input, print error if not valid input, print success if processed.
39 if (validateAcctTypeData(AccTypeID, AccountTypeDesc)) {
40 String sql = "INSERT INTO account_type VALUES (?,?)";
41 Connection conn = null;
42 try {
43 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
44 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
45
46
47
48 stmt.setInt(1,AccTypeID);
49 stmt.setString(2, AccountTypeDesc);
50 int result = stmt.executeUpdate();
51 if (result == 1) {
52 System.out.println("'" + AccTypeID + "' and '" + AccountTypeDesc + "' 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 }
68 } //end of addAcctTypeData()
69 //method to return true or false if input is valid
70 //print error with field that threw invalid
71 public boolean validateAcctTypeData(int AccTypeID, String AccountTypeDesc) {
72 if (AccTypeID == 10 || AccTypeID == 20 || AccTypeID == 30){
73
74 } else
75 {
76 System.out.println("Something went wrong adding '" + AccTypeID + "' to account_type!");
77 return false;
78 }
79 if (AccountTypeDesc == "Single" || AccountTypeDesc == "Joint" || AccountTypeDesc == "Minor")
80 {
81
82 } else
83 {
84 System.out.println("Something went wrong adding '" + AccountTypeDesc + "' to account_type!");
85 return false;
86 }
87 return true;
88 }
89
90 //update function
91 public void Update(int accTypeID, String accountTypeDesc) throws SQLException {
92
93 String sql = "UPDATE account_type SET AccTypeDesc = ? WHERE AccTypeId = ?";
94 Connection conn = null;
95
96 try
97 {
98 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
99 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
100
101 stmt.setString(1, accountTypeDesc);
102 stmt.setInt(2, accTypeID);
103 int result = stmt.executeUpdate();
104 if (result == 1) {
105 System.out.println("Row: " + accTypeID + " description has been updated to '" + accountTypeDesc + "' successfully...");
106 }
107 else {
108 System.out.println("Something went wrong updating " + accTypeID);
109 }
110 }
111 catch (SQLException e)
112 {
113 System.err.println(e);
114 }
115 finally
116 {
117 if(conn != null) {
118 if(!conn.isClosed()){
119 conn.close();
120 }
121 }
122 }
123
124}
125
126//delete
127public void Delete(int accTypeID) throws SQLException {
128
129 String sql = "DELETE FROM account_type WHERE AccTypeId = ?";
130 Connection conn = null;
131
132 try
133 {
134 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
135 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
136
137 stmt.setInt(1, accTypeID);
138 int result = stmt.executeUpdate();
139
140 if (result == 1) {
141 System.out.println("Account Type: " + accTypeID + " deleted successfully...");
142 } else
143 {
144 System.out.println("Something went wrong deleteing '" + accTypeID + "'");
145 }
146 }
147 catch (SQLException e)
148 {
149 System.err.println(e);
150 }
151 finally
152 {
153 if(conn != null) {
154 if(!conn.isClosed()){
155 conn.close();
156 }
157 }
158 }
159}
160} //END OF CLASS