Changeset df7f390 for backend/GlobeGuru-backend/src/main/java/DatabaseUtil.java

Timestamp:

01/13/25 14:18:51 (2 days ago)

Author:

Kristijan <kristijanzafirovski26@…>

Branches:

master

Parents:

0a7426e

Message:

Added frontend functionality for changes and refactored code

File:

• backend/GlobeGuru-backend/src/main/java/DatabaseUtil.java (modified) (6 diffs)

backend/GlobeGuru-backend/src/main/java/DatabaseUtil.java

@@ -53 +53 @@
                              "price REAL," +
                              "FOREIGN KEY(optionId) REFERENCES options(id))"
+             );
+             PreparedStatement stmt5 = conn.prepareStatement(
+                     "CREATE TABLE IF NOT EXISTS changeLog(" +
+                             "id INTEGER PRIMARY KEY AUTOINCREMENT," +
+                             "detail_id INTEGER NOT NULL," +
+                             "attribute TEXT NOT NULL," +
+                             "oldValue TEXT," +
+                             "newValue TEXT," +
+                             "FOREIGN KEY(detail_id) REFERENCES optionDetails(id))"
              )) {
 
@@ -59 +68 @@
             stmt3.executeUpdate();
             stmt4.executeUpdate();
+            stmt5.executeUpdate();
         }
     }
@@ -201 +211 @@
         }
     }
-
-
-
     public static List<Option> poolOptionDetails(int id) throws SQLException{
         String sql = "SELECT * FROM optionDetails WHERE optionId = ?";
@@ -283 +290 @@
     }
     public static int saveOptionToDatabase(Option option) {
-        String sql = "INSERT INTO options (link, imgSrc, hotelName, country, dateRange,numberOfPeople, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
+        String sql = "INSERT INTO options (link, imgSrc, hotelName, country, dateRange,numberOfPeople) VALUES (?, ?, ?, ?, ?, ?)";
         int id = 0;
         try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
@@ -293 +300 @@
             stmt.setString(5, option.getDateRange());
             stmt.setInt(6,option.getNumPeople());
-            stmt.setBoolean(7, option.isPriceChanged());
-            stmt.setFloat(8, option.getNewPrice());
             stmt.executeUpdate();
             try(ResultSet genKey = stmt.getGeneratedKeys()){
@@ -331 +336 @@
         }
     }
-
     public static void updateOptionDetails(int detail_id, String type, String board, String amenities, float price) {
-        String sql = "UPDATE optionDetails SET ";
-
-        List<String> updates = new ArrayList<>();
-        if (type != null && !type.isEmpty()) updates.add("type = '" + type + "'");
-        if (board != null && !board.isEmpty()) updates.add("board = '" + board + "'");
-        if (amenities != null && !amenities.isEmpty()) updates.add("amenities = '" + amenities + "'");
-        updates.add("price = " + price);
-
-        sql += String.join(", ", updates);
-        sql += " WHERE id = " + detail_id;
-
-        try (Connection conn = getConnection();
-             PreparedStatement stmt = conn.prepareStatement(sql)) {
-            stmt.executeUpdate();
+        try (Connection conn = getConnection()) {
+            String selectSql = "SELECT * FROM optionDetails WHERE id = ?";
+            PreparedStatement selectStmt = conn.prepareStatement(selectSql);
+            selectStmt.setInt(1, detail_id);
+            ResultSet rs = selectStmt.executeQuery();
+
+            if (rs.next()) {
+                String oldType = rs.getString("type");
+                String oldBoard = rs.getString("board");
+                String oldAmenities = rs.getString("amenities");
+                float oldPrice = rs.getFloat("price");
+
+                insertChangeLog(detail_id, "type", oldType, type);
+                insertChangeLog(detail_id, "board", oldBoard, board);
+                insertChangeLog(detail_id, "amenities", oldAmenities, amenities);
+                insertChangeLog(detail_id, "price", String.valueOf(oldPrice), String.valueOf(price));
+            }
+
+            String sql = "UPDATE optionDetails SET ";
+            List<String> updates = new ArrayList<>();
+            if (type != null && !type.isEmpty()) updates.add("type = '" + type + "'");
+            if (board != null && !board.isEmpty()) updates.add("board = '" + board + "'");
+            if (amenities != null && !amenities.isEmpty()) updates.add("amenities = '" + amenities + "'");
+            if (price > 0) updates.add("price = " + price);
+
+            sql += String.join(", ", updates);
+            sql += " WHERE id = " + detail_id;
+
+            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
+                stmt.executeUpdate();
+            }
         } catch (SQLException e) {
             e.printStackTrace();
         }
     }
-
-    public static void updateOptionInDatabase(Option option) {
-        String sql = "UPDATE options SET link = ?, imgSrc = ?, hotelName = ?, country = ?, price = ?, dateRange = ?, isPriceChanged = ?, newPrice = ? WHERE id = ?";
-        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
-             PreparedStatement stmt = conn.prepareStatement(sql)) {
-            stmt.setString(1, option.getLink());
-            stmt.setString(2, option.getImgSrc());
-            stmt.setString(3, option.getHotelName());
-            stmt.setString(4, option.getCountry());
-            stmt.setFloat(5, option.getPrice());
-            stmt.setString(6, option.getDateRange());
-            stmt.setBoolean(7, option.isPriceChanged());
-            stmt.setFloat(8, option.getNewPrice());
-            stmt.setInt(9, option.getId());
-            stmt.executeUpdate();
-        } catch (SQLException e) {
-            e.printStackTrace();
-        }
-    }
-    public static Option findOption(Option option) {
-        String sql = "SELECT * FROM options WHERE id = ?";
-        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
-             PreparedStatement stmt = conn.prepareStatement(sql)) {
-            stmt.setInt(1, option.getId());
-            try (ResultSet rs = stmt.executeQuery()) {
-                if (rs.next()) {
-                    Option existingOption = new Option();
-                    existingOption.setId(rs.getInt("id"));
-                    existingOption.setLink(rs.getString("link"));
-                    existingOption.setImgSrc(rs.getString("imgSrc"));
-                    existingOption.setHotelName(rs.getString("hotelName"));
-                    existingOption.setCountry(rs.getString("country"));
-                    existingOption.setPrice(rs.getFloat("price"));
-                    existingOption.setDateRange(rs.getString("dateRange"));
-                    existingOption.setPriceChanged(rs.getBoolean("isPriceChanged"));
-                    existingOption.setNewPrice(rs.getInt("newPrice"));
-                    existingOption.setNumPeople(rs.getInt("numberOfPeople"));
-                    return existingOption;
-                }
-            }
-        } catch (SQLException e) {
-            e.printStackTrace();
-        }
-        return null;
-    }
-
-
+    private static void insertChangeLog(int detail_id, String attribute, String oldValue, String newValue) {
+        if (!oldValue.equals(newValue)) {
+            String sql = "INSERT INTO changeLog (detail_id, attribute, oldValue, newValue) VALUES (?, ?, ?, ?)";
+            try (Connection conn = getConnection();
+                 PreparedStatement stmt = conn.prepareStatement(sql)) {
+                stmt.setInt(1, detail_id);
+                stmt.setString(2, attribute);
+                stmt.setString(3, oldValue);
+                stmt.setString(4, newValue);
+                stmt.executeUpdate();
+            } catch (SQLException e) {
+                e.printStackTrace();
+            }
+        }
+    }
     public static int getCurrentOptionsCount() throws SQLException {
         String sql = "SELECT COUNT(*) AS optionsCount FROM options";