· 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//creates transaction_type table and holds function to add data
9public class TransactionType extends MySQLConnection{
10 //constructor for transaction type
11 public TransactionType() throws SQLException {
12 //sql query to be executes
13 String sql = "CREATE TABLE IF NOT EXISTS transaction_type "
14 + "(TranTypeId VARCHAR(255), "
15 + "TransactionDisc VARCHAR(255), "
16 + "PRIMARY KEY ( TranTypeId ))";
17
18 Connection conn = null;
19
20 //executes query to create trasnaction_type table and send message to console if successful
21 try {
22 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
23 Statement stmt = conn.createStatement();
24 stmt.executeUpdate(sql);
25 System.out.println("Table transaction_type created successfully...");
26 } catch (SQLException e)
27 {
28 System.out.println(e.getMessage());
29 } finally
30 {
31 if(conn != null) {
32 if(!conn.isClosed()){
33 conn.close();
34 }
35 }
36 }
37 } //END OF TRANSACTIONTYPE()
38
39 //validates then adds data to transaction_type table
40 public void addTransTypeData(String tranTypeID, String transactionDesc) throws SQLException
41 {
42 if (validateTransType(tranTypeID)) { //checks if validateTransType is true
43
44 String sql = "INSERT INTO transaction_type VALUES (?,?)";
45 Connection conn = null;
46
47 try {
48
49 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
50 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
51
52 stmt.setString(1,tranTypeID);
53 stmt.setString(2, transactionDesc);
54 //executes query to add rows to transaction_type
55 int result = stmt.executeUpdate();
56 if (result == 1) {
57 //prints to console if added successfully
58 System.out.println("'" + tranTypeID + "' and '" + transactionDesc + "' inserted successfully...");
59 }
60
61 } catch (SQLException e)
62 {
63
64 System.err.println(e);
65
66 } finally
67 {
68 if(conn != null) {
69 if(!conn.isClosed()){
70 conn.close();
71 }
72 }
73 }
74
75
76 }
77 }
78
79 //validates data input
80 public boolean validateTransType(String tranTypeID) {
81 //checks if tranTypeID is equal to A C or P else it runs false
82 if (tranTypeID == "A" || tranTypeID == "C" || tranTypeID == "P"){
83
84 } else
85 {
86 System.out.println("Something went wrong adding '" + tranTypeID + "' to transaction_type!");
87 return false;
88 }
89
90 return true;
91 }
92 //function to update rows
93 public void Update(String tranTypeID, String transactionDesc) throws SQLException {
94
95 String sql = "UPDATE transaction_type SET tranTypeID = ? WHERE tranTypeId = ?";
96 Connection conn = null;
97
98 try
99 {
100 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
101 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
102
103 stmt.setString(1, transactionDesc);
104 stmt.setString(2, tranTypeID);
105 int result = stmt.executeUpdate();
106 if (result == 1) {
107 System.out.println("Row: " + tranTypeID + ", description has been updated to '" + transactionDesc + "' successfully...");
108 }
109 else {
110 System.out.println("Something went wrong updating " + tranTypeID);
111 }
112 }
113 catch (SQLException e)
114 {
115 System.err.println(e);
116 }
117 finally
118 {
119 if(conn != null) {
120 if(!conn.isClosed()){
121 conn.close();
122 }
123 }
124 }
125
126 }
127 //function to delete rows
128 public void Delete(String tranTypeID) throws SQLException {
129
130 String sql = "DELETE FROM transaction_type WHERE tranTypeId = ?";
131 Connection conn = null;
132
133 try
134 {
135 conn = DriverManager.getConnection(CONNECTION_ACCT, USER_NAME, PASS_WORD);
136 PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
137
138 stmt.setString(1, tranTypeID);
139 int result = stmt.executeUpdate();
140
141 if (result == 1) {
142 System.out.println("Transaction Type: " + tranTypeID + " deleted successfully...");
143 } else
144 {
145 System.out.println("Something went wrong deleteing '" + tranTypeID + "'");
146 }
147 }
148 catch (SQLException e)
149 {
150 System.err.println(e);
151 }
152 finally
153 {
154 if(conn != null) {
155 if(!conn.isClosed()){
156 conn.close();
157 }
158 }
159 }
160 }
161
162}//END OF CLASS