Changeset 1c51912 for backend/GlobeGuru-backend/src/main/java/DatabaseUtil.java

Timestamp:

01/10/25 19:07:51 (5 days ago)

Author:

Kristijan <kristijanzafirovski26@…>

Branches:

master

Children:

cd64b06

Parents:

53bad7e

Message:

Added details for magelan

File:

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

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

@@ -3 +3 @@
 import java.time.LocalDateTime;
 import java.util.ArrayList;
+import java.util.HashMap;
 import java.util.List;
+import java.util.Map;
 
 public class DatabaseUtil {
@@ -29 +31 @@
                              "hotelName TEXT, " +
                              "country TEXT, " +
-                             "price REAL, " +
                              "dateRange TEXT, " +
                              "numberOfPeople INTEGER, " +
@@ -35 +36 @@
                              "newPrice REAL DEFAULT 0)"
              );
-
              PreparedStatement stmt3 = conn.prepareStatement(
                      "CREATE TABLE IF NOT EXISTS savedOptions (" +
-                             "userId INTEGER, " +
-                             "optionId INTEGER, " +
-                             "FOREIGN KEY(userId) REFERENCES users(id), " +
-                             "FOREIGN KEY(optionId) REFERENCES options(id), " +
-                             "UNIQUE(userId, optionId))"
+                             "userId INTEGER," +
+                             "detailId INTEGER," +
+                             "FOREIGN KEY(userId) REFERENCES users(id)," +
+                             "FOREIGN KEY(detailId) REFERENCES optionDetails(id)," +
+                             "UNIQUE(userId, detailId))"
+             );
+             PreparedStatement stmt4 = conn.prepareStatement(
+                     "CREATE TABLE IF NOT EXISTS optionDetails (" +
+                             "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
+                             "optionId INTEGER NOT NULL, " +
+                             "type TEXT, " +
+                             "board TEXT, " +
+                             "amenities TEXT, " +
+                             "price REAL," +
+                             "FOREIGN KEY(optionId) REFERENCES options(id))"
              )) {
+
             stmt1.executeUpdate();
             stmt2.executeUpdate();
             stmt3.executeUpdate();
+            stmt4.executeUpdate();
         }
     }
@@ -136 +148 @@
     public static List<Option> queryOptions(String destination, String dateQuery, int numPeople, boolean dateFlag) throws SQLException {
         List<Option> options = new ArrayList<>();
-        String sql = "SELECT * FROM options WHERE (country LIKE ? OR hotelName LIKE ?)";
-        System.out.println(dateQuery);
+        String sql = "SELECT o.*, od.id AS detail_id, od.type, od.board, od.amenities, od.price AS detail_price " +
+                "FROM options o " +
+                "LEFT JOIN optionDetails od ON o.id = od.optionId " +
+                "WHERE (o.country LIKE ? OR o.hotelName LIKE ?)";
+
         if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
-            sql += (" AND dateRange = ?");
-        } //append date
-        if (dateFlag) {   //search only from dates
-            sql += " AND dateRange LIKE ?";
-        }
-        if(numPeople != 0) { //with number of people
-            sql += " AND numberOfPeople = ?";
-        }
-
-        System.out.println("Searching for dest:" + destination + "\n" + sql);
+            sql += " AND o.dateRange = ?";
+        }
+        if (dateFlag) {
+            sql += " AND o.dateRange LIKE ?";
+        }
+        if (numPeople != 0) {
+            sql += " AND o.numberOfPeople = ?";
+        }
+
         try (Connection conn = getConnection();
              PreparedStatement stmt = conn.prepareStatement(sql)) {
             stmt.setString(1, "%" + destination + "%");
             stmt.setString(2, "%" + destination + "%");
+            int paramIndex = 3;
             if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
-                stmt.setString(3, dateQuery);
+                stmt.setString(paramIndex++, dateQuery);
             }
             if (dateFlag) {
-                stmt.setString(3, dateQuery + "%");
-            }
-            if(numPeople != 0) {
-                stmt.setInt(4, numPeople);
-            }
+                stmt.setString(paramIndex++, dateQuery + "%");
+            }
+            if (numPeople != 0) {
+                stmt.setInt(paramIndex, numPeople);
+            }
+
             try (ResultSet rs = stmt.executeQuery()) {
                 while (rs.next()) {
                     Option option = new Option();
-                    option.setId(rs.getInt("id"));
+                    option.setId(rs.getInt("detail_id"));
                     option.setLink(rs.getString("link"));
                     option.setImgSrc(rs.getString("imgSrc"));
                     option.setHotelName(rs.getString("hotelName"));
                     option.setCountry(rs.getString("country"));
-                    option.setPrice(rs.getFloat("price"));
                     option.setDateRange(rs.getString("dateRange"));
                     option.setNumPeople(rs.getInt("numberOfPeople"));
+                    option.setType(rs.getString("type"));
+                    option.setBoard(rs.getString("board"));
+                    option.setAmenities(rs.getString("amenities"));
+                    option.setPrice(rs.getFloat("detail_price"));
                     options.add(option);
                 }
             }
         }
-        System.out.println("Found " + options.size());
+
+        System.out.println("Found " + options.size() + " options");
         return options;
     }
 
-    public static boolean saveFavoriteOption(int userId, int optionId) throws SQLException {
-        String sql = "INSERT INTO savedOptions (userId, optionId) VALUES (?, ?) ON CONFLICT DO NOTHING";
+
+    public static boolean saveFavoriteOption(int userId, int detailId) throws SQLException {
+        String sql = "INSERT INTO savedOptions (userId, detailId) VALUES (?, ?) ON CONFLICT DO NOTHING";
         try (Connection conn = getConnection();
              PreparedStatement stmt = conn.prepareStatement(sql)) {
             stmt.setInt(1, userId);
-            stmt.setInt(2, optionId);
+            stmt.setInt(2, detailId);
             return stmt.executeUpdate() > 0;
         }
@@ -192 +213 @@
 
 
-    public static boolean removeFavoriteOption(int userId, int optionId) throws SQLException {
-        String sql = "DELETE FROM savedOptions WHERE userId = ? AND optionId = ?";
+
+    public static boolean removeFavoriteOption(int userId, int detailId) throws SQLException {
+        String sql = "DELETE FROM savedOptions WHERE userId = ? AND detailId = ?";
         try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {
             stmt.setInt(1, userId);
-            stmt.setInt(2, optionId);
+            stmt.setInt(2, detailId);
             return stmt.executeUpdate() > 0;
         }
     }
+
 
     public static List<Option> getSavedTripsByUser(int userId) throws SQLException {
         List<Option> savedTrips = new ArrayList<>();
-        String sql = "SELECT options.* FROM savedOptions JOIN options ON savedOptions.optionId = options.id WHERE savedOptions.userId = ?";
+        String sql = "SELECT od.id AS detail_id, o.*, od.* FROM savedOptions so " +
+                "JOIN optionDetails od ON so.detailId = od.id " +
+                "JOIN options o ON od.optionId = o.id " +
+                "WHERE so.userId = ?";
         try (Connection conn = getConnection();
              PreparedStatement stmt = conn.prepareStatement(sql)) {
@@ -210 +236 @@
                 while (rs.next()) {
                     Option option = new Option();
-                    option.setId(rs.getInt("id"));
+                    option.setId(rs.getInt("detail_id"));
                     option.setLink(rs.getString("link"));
                     option.setImgSrc(rs.getString("imgSrc"));
                     option.setHotelName(rs.getString("hotelName"));
                     option.setCountry(rs.getString("country"));
+                    option.setDateRange(rs.getString("dateRange"));
+                    option.setNumPeople(rs.getInt("numberOfPeople"));
+                    option.setType(rs.getString("type"));
+                    option.setBoard(rs.getString("board"));
+                    option.setAmenities(rs.getString("amenities"));
                     option.setPrice(rs.getFloat("price"));
-                    option.setDateRange(rs.getString("dateRange"));
-                    option.setPriceChanged(rs.getBoolean("isPriceChanged"));
-                    option.setNewPrice(rs.getInt("newPrice"));
                     savedTrips.add(option);
                 }
@@ -225 +253 @@
         return savedTrips;
     }
+
 
 
@@ -274 +303 @@
     }
 
-    public static void saveOptionToDatabase(Option option) {
-        String sql = "INSERT INTO options (link, imgSrc, hotelName, country, price, dateRange,numberOfPeople, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
+    public static int saveOptionToDatabase(Option option) {
+        String sql = "INSERT INTO options (link, imgSrc, hotelName, country, dateRange,numberOfPeople, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
+        int id = 0;
         try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
              PreparedStatement stmt = conn.prepareStatement(sql)) {
@@ -282 +312 @@
             stmt.setString(3, option.getHotelName());
             stmt.setString(4, option.getCountry());
-            stmt.setFloat(5, option.getPrice());
-            stmt.setString(6, option.getDateRange());
-            stmt.setInt(7,option.getNumPeople());
-            stmt.setBoolean(8, option.isPriceChanged());
-            stmt.setFloat(9, option.getNewPrice());
+            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()){
+                if(genKey.next()){
+                    id = genKey.getInt(1);
+                }
+            }
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return id;
+    }
+
+    public static void saveOptionDetails(int id, String type, String board, String amenity, float price) {
+        String sql = "INSERT INTO optionDetails (optionId, type, board, amenities, price) VALUES (?, ?, ?, ?, ?)";
+
+        try (Connection conn = getConnection();
+             PreparedStatement stmt = conn.prepareStatement(sql)) {
+            stmt.setInt(1, id);
+            stmt.setString(2, type);
+            stmt.setString(3, board);
+            stmt.setString(4, amenity);
+            stmt.setFloat(5, price);
             stmt.executeUpdate();
         } catch (SQLException e) {
@@ -292 +343 @@
         }
     }
-
 
     public static void dropOptions() throws SQLException {
@@ -350 +400 @@
     }
 
+    public static List<Option> fetchAllOptions() {
+        List<Option> options = new ArrayList<>();
+        String sql = "SELECT * FROM options";
+
+        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
+             PreparedStatement stmt = conn.prepareStatement(sql);
+             ResultSet rs = stmt.executeQuery()) {
+            while (rs.next()) {
+                Option option = new Option();
+                option.setId(rs.getInt("id"));
+                option.setLink(rs.getString("link"));
+                option.setImgSrc(rs.getString("imgSrc"));
+                option.setHotelName(rs.getString("hotelName"));
+                option.setCountry(rs.getString("country"));
+                option.setPrice(rs.getFloat("price"));
+                option.setDateRange(rs.getString("dateRange"));
+                option.setPriceChanged(rs.getBoolean("isPriceChanged"));
+                option.setNewPrice(rs.getInt("newPrice"));
+                option.setNumPeople(rs.getInt("numberOfPeople"));
+                options.add(option);
+            }
+
+        } catch (SQLException e) {
+            e.printStackTrace();
+        }
+        return options;
+    }
+
 }