5/7/25, 9:43 PM TransactionDb.

java

src/main/java/tham/seven/TransactionDb.java

1 /*
2 * Mei Tham
3 * AP CSA
4 * Mar 5, 2025
5 * 7th Period
6 * Database
7 */
8
9 package tham.seven;
10
11 import java.sql.Connection;
12 import java.sql.DriverManager;
13 import java.sql.PreparedStatement;
14 import java.sql.ResultSet;
15 import java.sql.SQLException;
16 import java.sql.Statement;
17 import java.util.ArrayList;
18 import java.util.List;
19
20 public class TransactionDb {
21 private static final String URL = "jdbc:mysql://localhost:3306/MasterProject";
22 private static final String USER = "root";
23 private static final String PASSWORD = "thamfamily0";
24 private Connection connection;
25
26 public TransactionDb() {
27 try {
28 connection = DriverManager.getConnection(URL, USER, PASSWORD);
29 System.out.println("Connected to database successfully.");
30 } catch (SQLException e) {
31 e.printStackTrace();
32 }
33 }
34
35 public List<Budget> getAllTransactions() {
36 List<Budget> transactions = new ArrayList<>();
localhost:62762/eb320015-fde1-4e72-b120-11eb2abbfe1b/ 1/3
5/7/25, 9:43 PM TransactionDb.java
37 String query = "SELECT * FROM budget"; // Assuming the table name is 'budget', keep it as is for the query
38 try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
39 while (rs.next()) {
40 transactions.add(new Budget(
41 rs.getInt("id"),
42 rs.getString("date"),
43 rs.getDouble("amount"),
44 rs.getString("category"),
45 rs.getString("description"),
46 rs.getBoolean("inBudget")
47 ));
48 }
49 } catch (SQLException e) {
50 e.printStackTrace();
51 }
52 return transactions;
53 }
54
55 public void addTransaction(String date, double amount, String category, String description, boolean inBudget)
{
56 String query = "INSERT INTO budget (date, amount, category, description, inBudget) VALUES (?, ?, ?, ?,
?)";
57 try (PreparedStatement pstmt = connection.prepareStatement(query)) {
58 pstmt.setString(1, date);
59 pstmt.setDouble(2, amount);
60 pstmt.setString(3, category);
61 pstmt.setString(4, description);
62 pstmt.setBoolean(5, inBudget);
63 pstmt.executeUpdate();
64 } catch (SQLException e) {
65 e.printStackTrace();
66 }
67 }
68
69 public void updateTransaction(int id, String date, double amount, String category, String description, boolean
inBudget) {
70 String query = "UPDATE budget SET date = ?, amount = ?, category = ?, description = ?, inBudget = ? WHERE
id = ?";

localhost:62762/eb320015-fde1-4e72-b120-11eb2abbfe1b/ 2/3
5/7/25, 9:43 PM TransactionDb.java
71 try (PreparedStatement pstmt = connection.prepareStatement(query)) {
72 pstmt.setString(1, date);
73 pstmt.setDouble(2, amount);
74 pstmt.setString(3, category);
75 pstmt.setString(4, description);
76 pstmt.setBoolean(5, inBudget);
77 pstmt.setInt(6, id);
78 pstmt.executeUpdate();
79 } catch (SQLException e) {
80 e.printStackTrace();
81 }
82 }
83
84 public void clearAllTransactions­
() {
85 String query = "DELETE FROM budget";
86 try (PreparedStatement pstmt = connection.prepareStatement(query)) {
87 pstmt.executeUpdate();
88 System.out.println("All transactions cleared for new month.");
89 } catch (SQLException e) {
90 e.printStackTrace();
91 }
92 }
93
94
95
96 public void deleteTransaction(int id) {
97 String query = "DELETE FROM budget WHERE id = ?";
98 try (PreparedStatement pstmt = connection.prepareStatement(query)) {
99 pstmt.setInt(1, id);
100 pstmt.executeUpdate();
101 } catch (SQLException e) {
102 e.printStackTrace();
103 }
104 }
105 }
106

localhost:62762/eb320015-fde1-4e72-b120-11eb2abbfe1b/ 3/3