| 1 | import java.io.IOException;
|
|---|
| 2 | import java.sql.*;
|
|---|
| 3 | import java.time.LocalDateTime;
|
|---|
| 4 | import java.util.ArrayList;
|
|---|
| 5 | import java.util.HashMap;
|
|---|
| 6 | import java.util.List;
|
|---|
| 7 | import java.util.Map;
|
|---|
| 8 |
|
|---|
| 9 | public class DatabaseUtil {
|
|---|
| 10 |
|
|---|
| 11 | private static final String DB_URL = "jdbc:sqlite:globe_guru.db";
|
|---|
| 12 |
|
|---|
| 13 | public static Connection getConnection() throws SQLException {
|
|---|
| 14 | return DriverManager.getConnection(DB_URL);
|
|---|
| 15 | }
|
|---|
| 16 | public static void initializeDatabase() throws SQLException {
|
|---|
| 17 | try (Connection conn = getConnection();
|
|---|
| 18 | PreparedStatement stmt1 = conn.prepareStatement(
|
|---|
| 19 | "CREATE TABLE IF NOT EXISTS users (" +
|
|---|
| 20 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
|
|---|
| 21 | "username TEXT NOT NULL UNIQUE, " +
|
|---|
| 22 | "email TEXT NOT NULL UNIQUE, " +
|
|---|
| 23 | "password TEXT, " +
|
|---|
| 24 | "isAdmin BOOLEAN NOT NULL DEFAULT FALSE)"
|
|---|
| 25 | );
|
|---|
| 26 | PreparedStatement stmt2 = conn.prepareStatement(
|
|---|
| 27 | "CREATE TABLE IF NOT EXISTS options (" +
|
|---|
| 28 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
|
|---|
| 29 | "link TEXT, " +
|
|---|
| 30 | "imgSrc TEXT, " +
|
|---|
| 31 | "hotelName TEXT, " +
|
|---|
| 32 | "country TEXT, " +
|
|---|
| 33 | "dateRange TEXT, " +
|
|---|
| 34 | "numberOfPeople INTEGER, " +
|
|---|
| 35 | "isPriceChanged BOOLEAN DEFAULT 0, " +
|
|---|
| 36 | "newPrice REAL DEFAULT 0)"
|
|---|
| 37 | );
|
|---|
| 38 | PreparedStatement stmt3 = conn.prepareStatement(
|
|---|
| 39 | "CREATE TABLE IF NOT EXISTS savedOptions (" +
|
|---|
| 40 | "userId INTEGER," +
|
|---|
| 41 | "detailId INTEGER," +
|
|---|
| 42 | "FOREIGN KEY(userId) REFERENCES users(id)," +
|
|---|
| 43 | "FOREIGN KEY(detailId) REFERENCES optionDetails(id)," +
|
|---|
| 44 | "UNIQUE(userId, detailId))"
|
|---|
| 45 | );
|
|---|
| 46 | PreparedStatement stmt4 = conn.prepareStatement(
|
|---|
| 47 | "CREATE TABLE IF NOT EXISTS optionDetails (" +
|
|---|
| 48 | "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
|
|---|
| 49 | "optionId INTEGER NOT NULL, " +
|
|---|
| 50 | "type TEXT, " +
|
|---|
| 51 | "board TEXT, " +
|
|---|
| 52 | "amenities TEXT, " +
|
|---|
| 53 | "price REAL," +
|
|---|
| 54 | "FOREIGN KEY(optionId) REFERENCES options(id))"
|
|---|
| 55 | )) {
|
|---|
| 56 |
|
|---|
| 57 | stmt1.executeUpdate();
|
|---|
| 58 | stmt2.executeUpdate();
|
|---|
| 59 | stmt3.executeUpdate();
|
|---|
| 60 | stmt4.executeUpdate();
|
|---|
| 61 | }
|
|---|
| 62 | }
|
|---|
| 63 |
|
|---|
| 64 | public static boolean registerUser(String username, String email, String password) throws SQLException {
|
|---|
| 65 | String sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
|
|---|
| 66 | try (Connection conn = getConnection();
|
|---|
| 67 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 68 | stmt.setString(1, username);
|
|---|
| 69 | stmt.setString(2, email);
|
|---|
| 70 | stmt.setString(3, password);
|
|---|
| 71 | return stmt.executeUpdate() > 0;
|
|---|
| 72 | }
|
|---|
| 73 | }
|
|---|
| 74 | public static boolean authenticateUser(String email, String password) throws SQLException {
|
|---|
| 75 | String sql = "SELECT password FROM users WHERE email = ?";
|
|---|
| 76 | try (Connection conn = getConnection();
|
|---|
| 77 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 78 | stmt.setString(1, email);
|
|---|
| 79 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 80 | if (rs.next()) {
|
|---|
| 81 | String storedPassword = rs.getString("password");
|
|---|
| 82 | if (password == null) {
|
|---|
| 83 | // Google login
|
|---|
| 84 | return storedPassword == null;
|
|---|
| 85 | }
|
|---|
| 86 | return password.equals(storedPassword);
|
|---|
| 87 | }
|
|---|
| 88 | }
|
|---|
| 89 | }
|
|---|
| 90 | return false;
|
|---|
| 91 | }
|
|---|
| 92 | public static boolean deleteUser(int userId) throws SQLException {
|
|---|
| 93 | String selectSql = "SELECT userId FROM users WHERE userId = ?";
|
|---|
| 94 | String deleteSql = "DELETE FROM users WHERE userId = ?";
|
|---|
| 95 | String deleteFavoritesSql = "DELETE FROM savedOptions WHERE userId = ?";
|
|---|
| 96 |
|
|---|
| 97 | try (Connection conn = getConnection();
|
|---|
| 98 | PreparedStatement selectStmt = conn.prepareStatement(selectSql);
|
|---|
| 99 | PreparedStatement deleteStmt = conn.prepareStatement(deleteSql);
|
|---|
| 100 | PreparedStatement deleteFavoritesStmt = conn.prepareStatement(deleteFavoritesSql)) {
|
|---|
| 101 |
|
|---|
| 102 | selectStmt.setInt(1, userId);
|
|---|
| 103 | try (ResultSet rs = selectStmt.executeQuery()) {
|
|---|
| 104 | if (rs.next()) {
|
|---|
| 105 | deleteStmt.setInt(1, userId);
|
|---|
| 106 | int rowsAffected = deleteStmt.executeUpdate();
|
|---|
| 107 |
|
|---|
| 108 | deleteFavoritesStmt.setInt(1, userId);
|
|---|
| 109 | deleteFavoritesStmt.executeUpdate();
|
|---|
| 110 |
|
|---|
| 111 | return rowsAffected > 0;
|
|---|
| 112 | } else {
|
|---|
| 113 | return false;
|
|---|
| 114 | }
|
|---|
| 115 | }
|
|---|
| 116 | }
|
|---|
| 117 | }
|
|---|
| 118 | public static boolean userExists(String email) throws SQLException {
|
|---|
| 119 | String query = "SELECT COUNT(*) FROM users WHERE email = ?";
|
|---|
| 120 | try (Connection connection = getConnection();
|
|---|
| 121 | PreparedStatement statement = connection.prepareStatement(query)) {
|
|---|
| 122 | statement.setString(1, email);
|
|---|
| 123 | ResultSet resultSet = statement.executeQuery();
|
|---|
| 124 | if (resultSet.next()) {
|
|---|
| 125 | return resultSet.getInt(1) > 0;
|
|---|
| 126 | }
|
|---|
| 127 | }
|
|---|
| 128 | return false;
|
|---|
| 129 | }
|
|---|
| 130 | public static boolean isAdmin(String email) throws SQLException {
|
|---|
| 131 | String selectSql = "SELECT isAdmin FROM users WHERE email = ?";
|
|---|
| 132 | try (Connection conn = getConnection();
|
|---|
| 133 | PreparedStatement stmt = conn.prepareStatement(selectSql)) {
|
|---|
| 134 | stmt.setString(1, email);
|
|---|
| 135 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 136 | if (rs.next()) {
|
|---|
| 137 | return rs.getBoolean("isAdmin");
|
|---|
| 138 | }
|
|---|
| 139 | }
|
|---|
| 140 | }
|
|---|
| 141 | return false;
|
|---|
| 142 | }
|
|---|
| 143 | public static boolean saveFavoriteOption(int userId, int detailId) throws SQLException {
|
|---|
| 144 | String sql = "INSERT INTO savedOptions (userId, detailId) VALUES (?, ?) ON CONFLICT DO NOTHING";
|
|---|
| 145 | try (Connection conn = getConnection();
|
|---|
| 146 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 147 | stmt.setInt(1, userId);
|
|---|
| 148 | stmt.setInt(2, detailId);
|
|---|
| 149 | return stmt.executeUpdate() > 0;
|
|---|
| 150 | }
|
|---|
| 151 | }
|
|---|
| 152 | public static boolean removeFavoriteOption(int userId, int detailId) throws SQLException {
|
|---|
| 153 | String sql = "DELETE FROM savedOptions WHERE userId = ? AND detailId = ?";
|
|---|
| 154 | try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 155 | stmt.setInt(1, userId);
|
|---|
| 156 | stmt.setInt(2, detailId);
|
|---|
| 157 | return stmt.executeUpdate() > 0;
|
|---|
| 158 | }
|
|---|
| 159 | }
|
|---|
| 160 | public static List<Option> getSavedTripsByUser(int userId) throws SQLException {
|
|---|
| 161 | List<Option> savedTrips = new ArrayList<>();
|
|---|
| 162 | String sql = "SELECT od.id AS detail_id, o.*, od.* FROM savedOptions so " +
|
|---|
| 163 | "JOIN optionDetails od ON so.detailId = od.id " +
|
|---|
| 164 | "JOIN options o ON od.optionId = o.id " +
|
|---|
| 165 | "WHERE so.userId = ?";
|
|---|
| 166 | try (Connection conn = getConnection();
|
|---|
| 167 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 168 | stmt.setInt(1, userId);
|
|---|
| 169 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 170 | while (rs.next()) {
|
|---|
| 171 | Option option = new Option();
|
|---|
| 172 | option.setId(rs.getInt("detail_id"));
|
|---|
| 173 | option.setLink(rs.getString("link"));
|
|---|
| 174 | option.setImgSrc(rs.getString("imgSrc"));
|
|---|
| 175 | option.setHotelName(rs.getString("hotelName"));
|
|---|
| 176 | option.setCountry(rs.getString("country"));
|
|---|
| 177 | option.setDateRange(rs.getString("dateRange"));
|
|---|
| 178 | option.setNumPeople(rs.getInt("numberOfPeople"));
|
|---|
| 179 | option.setType(rs.getString("type"));
|
|---|
| 180 | option.setBoard(rs.getString("board"));
|
|---|
| 181 | option.setAmenities(rs.getString("amenities"));
|
|---|
| 182 | option.setPrice(rs.getFloat("price"));
|
|---|
| 183 | savedTrips.add(option);
|
|---|
| 184 | }
|
|---|
| 185 | }
|
|---|
| 186 | }
|
|---|
| 187 | return savedTrips;
|
|---|
| 188 | }
|
|---|
| 189 | public static int getUserIdByEmail(String email) throws SQLException {
|
|---|
| 190 | String sql = "SELECT id FROM users WHERE email = ?";
|
|---|
| 191 | try (Connection conn = getConnection();
|
|---|
| 192 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 193 | stmt.setString(1, email);
|
|---|
| 194 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 195 | if (rs.next()) {
|
|---|
| 196 | return rs.getInt("id");
|
|---|
| 197 | } else {
|
|---|
| 198 | throw new SQLException("User not found");
|
|---|
| 199 | }
|
|---|
| 200 | }
|
|---|
| 201 | }
|
|---|
| 202 | }
|
|---|
| 203 |
|
|---|
| 204 |
|
|---|
| 205 |
|
|---|
| 206 | public static List<Option> poolOptionDetails(int id) throws SQLException{
|
|---|
| 207 | String sql = "SELECT * FROM optionDetails WHERE optionId = ?";
|
|---|
| 208 | List<Option> options = new ArrayList<>();
|
|---|
| 209 | try(Connection conn = getConnection();
|
|---|
| 210 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 211 | stmt.setInt(1,id);
|
|---|
| 212 | try(ResultSet rs = stmt.executeQuery()){
|
|---|
| 213 | while (rs.next()){
|
|---|
| 214 | Option o = new Option();
|
|---|
| 215 | o.setAmenities(rs.getString("amenities"));
|
|---|
| 216 | o.setType(rs.getString("type"));
|
|---|
| 217 | o.setBoard(rs.getString("board"));
|
|---|
| 218 | o.setPrice(rs.getFloat("price"));
|
|---|
| 219 | o.setId(id);
|
|---|
| 220 | o.setDetail_id(rs.getInt("id"));
|
|---|
| 221 | }
|
|---|
| 222 | }
|
|---|
| 223 |
|
|---|
| 224 | }
|
|---|
| 225 | return options;
|
|---|
| 226 | }
|
|---|
| 227 | public static List<Option> queryOptions(String destination, String dateQuery, int numPeople, boolean dateFlag) throws SQLException {
|
|---|
| 228 | List<Option> options = new ArrayList<>();
|
|---|
| 229 | String sql = "SELECT o.*, od.id AS detail_id, od.type, od.board, od.amenities, od.price AS detail_price " +
|
|---|
| 230 | "FROM options o " +
|
|---|
| 231 | "LEFT JOIN optionDetails od ON o.id = od.optionId " +
|
|---|
| 232 | "WHERE (o.country LIKE ? OR o.hotelName LIKE ?) " +
|
|---|
| 233 | "AND od.type IS NOT NULL " +
|
|---|
| 234 | "AND od.board IS NOT NULL " +
|
|---|
| 235 | "AND od.price > 0";
|
|---|
| 236 |
|
|---|
| 237 | if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
|
|---|
| 238 | sql += " AND o.dateRange = ?";
|
|---|
| 239 | }
|
|---|
| 240 | if (dateFlag) {
|
|---|
| 241 | sql += " AND o.dateRange LIKE ?";
|
|---|
| 242 | }
|
|---|
| 243 | if (numPeople != 0) {
|
|---|
| 244 | sql += " AND o.numberOfPeople = ?";
|
|---|
| 245 | }
|
|---|
| 246 |
|
|---|
| 247 | try (Connection conn = getConnection();
|
|---|
| 248 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 249 | stmt.setString(1, "%" + destination + "%");
|
|---|
| 250 | stmt.setString(2, "%" + destination + "%");
|
|---|
| 251 | int paramIndex = 3;
|
|---|
| 252 | if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
|
|---|
| 253 | stmt.setString(paramIndex++, dateQuery);
|
|---|
| 254 | }
|
|---|
| 255 | if (dateFlag) {
|
|---|
| 256 | stmt.setString(paramIndex++, dateQuery + "%");
|
|---|
| 257 | }
|
|---|
| 258 | if (numPeople != 0) {
|
|---|
| 259 | stmt.setInt(paramIndex, numPeople);
|
|---|
| 260 | }
|
|---|
| 261 |
|
|---|
| 262 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 263 | while (rs.next()) {
|
|---|
| 264 | Option option = new Option();
|
|---|
| 265 | option.setId(rs.getInt("detail_id"));
|
|---|
| 266 | option.setLink(rs.getString("link"));
|
|---|
| 267 | option.setImgSrc(rs.getString("imgSrc"));
|
|---|
| 268 | option.setHotelName(rs.getString("hotelName"));
|
|---|
| 269 | option.setCountry(rs.getString("country"));
|
|---|
| 270 | option.setDateRange(rs.getString("dateRange"));
|
|---|
| 271 | option.setNumPeople(rs.getInt("numberOfPeople"));
|
|---|
| 272 | option.setType(rs.getString("type"));
|
|---|
| 273 | option.setBoard(rs.getString("board"));
|
|---|
| 274 | option.setAmenities(rs.getString("amenities"));
|
|---|
| 275 | option.setPrice(rs.getFloat("detail_price"));
|
|---|
| 276 | options.add(option);
|
|---|
| 277 | }
|
|---|
| 278 | }
|
|---|
| 279 | }
|
|---|
| 280 |
|
|---|
| 281 | System.out.println("Found " + options.size() + " options");
|
|---|
| 282 | return options;
|
|---|
| 283 | }
|
|---|
| 284 | public static int saveOptionToDatabase(Option option) {
|
|---|
| 285 | String sql = "INSERT INTO options (link, imgSrc, hotelName, country, dateRange,numberOfPeople, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
|
|---|
| 286 | int id = 0;
|
|---|
| 287 | try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
|
|---|
| 288 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 289 | stmt.setString(1, option.getLink());
|
|---|
| 290 | stmt.setString(2, option.getImgSrc());
|
|---|
| 291 | stmt.setString(3, option.getHotelName());
|
|---|
| 292 | stmt.setString(4, option.getCountry());
|
|---|
| 293 | stmt.setString(5, option.getDateRange());
|
|---|
| 294 | stmt.setInt(6,option.getNumPeople());
|
|---|
| 295 | stmt.setBoolean(7, option.isPriceChanged());
|
|---|
| 296 | stmt.setFloat(8, option.getNewPrice());
|
|---|
| 297 | stmt.executeUpdate();
|
|---|
| 298 | try(ResultSet genKey = stmt.getGeneratedKeys()){
|
|---|
| 299 | if(genKey.next()){
|
|---|
| 300 | id = genKey.getInt(1);
|
|---|
| 301 | }
|
|---|
| 302 | }
|
|---|
| 303 | } catch (SQLException e) {
|
|---|
| 304 | e.printStackTrace();
|
|---|
| 305 | }
|
|---|
| 306 | return id;
|
|---|
| 307 | }
|
|---|
| 308 | public static void saveOptionDetails(int id, String type, String board, String amenity, float price) {
|
|---|
| 309 | String sql = "INSERT INTO optionDetails (optionId, type, board, amenities, price) VALUES (?, ?, ?, ?, ?)";
|
|---|
| 310 |
|
|---|
| 311 | try (Connection conn = getConnection();
|
|---|
| 312 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 313 | stmt.setInt(1, id);
|
|---|
| 314 | stmt.setString(2, type);
|
|---|
| 315 | stmt.setString(3, board);
|
|---|
| 316 | stmt.setString(4, amenity);
|
|---|
| 317 | stmt.setFloat(5, price);
|
|---|
| 318 | stmt.executeUpdate();
|
|---|
| 319 | } catch (SQLException e) {
|
|---|
| 320 | e.printStackTrace();
|
|---|
| 321 | }
|
|---|
| 322 | }
|
|---|
| 323 | public static void dropOptions() throws SQLException {
|
|---|
| 324 | String sql = "DROP TABLE options";
|
|---|
| 325 | try (
|
|---|
| 326 | Connection conn = getConnection();
|
|---|
| 327 | PreparedStatement stmt = conn.prepareStatement(sql)){
|
|---|
| 328 | stmt.executeUpdate();
|
|---|
| 329 | initializeDatabase();
|
|---|
| 330 |
|
|---|
| 331 | }
|
|---|
| 332 | }
|
|---|
| 333 |
|
|---|
| 334 | public static void updateOptionDetails(int detail_id, String type, String board, String amenities, float price) {
|
|---|
| 335 | String sql = "UPDATE optionDetails SET ";
|
|---|
| 336 |
|
|---|
| 337 | List<String> updates = new ArrayList<>();
|
|---|
| 338 | if (type != null && !type.isEmpty()) updates.add("type = '" + type + "'");
|
|---|
| 339 | if (board != null && !board.isEmpty()) updates.add("board = '" + board + "'");
|
|---|
| 340 | if (amenities != null && !amenities.isEmpty()) updates.add("amenities = '" + amenities + "'");
|
|---|
| 341 | updates.add("price = " + price);
|
|---|
| 342 |
|
|---|
| 343 | sql += String.join(", ", updates);
|
|---|
| 344 | sql += " WHERE id = " + detail_id;
|
|---|
| 345 |
|
|---|
| 346 | try (Connection conn = getConnection();
|
|---|
| 347 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 348 | stmt.executeUpdate();
|
|---|
| 349 | } catch (SQLException e) {
|
|---|
| 350 | e.printStackTrace();
|
|---|
| 351 | }
|
|---|
| 352 | }
|
|---|
| 353 |
|
|---|
| 354 | public static void updateOptionInDatabase(Option option) {
|
|---|
| 355 | String sql = "UPDATE options SET link = ?, imgSrc = ?, hotelName = ?, country = ?, price = ?, dateRange = ?, isPriceChanged = ?, newPrice = ? WHERE id = ?";
|
|---|
| 356 | try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
|
|---|
| 357 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 358 | stmt.setString(1, option.getLink());
|
|---|
| 359 | stmt.setString(2, option.getImgSrc());
|
|---|
| 360 | stmt.setString(3, option.getHotelName());
|
|---|
| 361 | stmt.setString(4, option.getCountry());
|
|---|
| 362 | stmt.setFloat(5, option.getPrice());
|
|---|
| 363 | stmt.setString(6, option.getDateRange());
|
|---|
| 364 | stmt.setBoolean(7, option.isPriceChanged());
|
|---|
| 365 | stmt.setFloat(8, option.getNewPrice());
|
|---|
| 366 | stmt.setInt(9, option.getId());
|
|---|
| 367 | stmt.executeUpdate();
|
|---|
| 368 | } catch (SQLException e) {
|
|---|
| 369 | e.printStackTrace();
|
|---|
| 370 | }
|
|---|
| 371 | }
|
|---|
| 372 | public static Option findOption(Option option) {
|
|---|
| 373 | String sql = "SELECT * FROM options WHERE id = ?";
|
|---|
| 374 | try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
|
|---|
| 375 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 376 | stmt.setInt(1, option.getId());
|
|---|
| 377 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 378 | if (rs.next()) {
|
|---|
| 379 | Option existingOption = new Option();
|
|---|
| 380 | existingOption.setId(rs.getInt("id"));
|
|---|
| 381 | existingOption.setLink(rs.getString("link"));
|
|---|
| 382 | existingOption.setImgSrc(rs.getString("imgSrc"));
|
|---|
| 383 | existingOption.setHotelName(rs.getString("hotelName"));
|
|---|
| 384 | existingOption.setCountry(rs.getString("country"));
|
|---|
| 385 | existingOption.setPrice(rs.getFloat("price"));
|
|---|
| 386 | existingOption.setDateRange(rs.getString("dateRange"));
|
|---|
| 387 | existingOption.setPriceChanged(rs.getBoolean("isPriceChanged"));
|
|---|
| 388 | existingOption.setNewPrice(rs.getInt("newPrice"));
|
|---|
| 389 | existingOption.setNumPeople(rs.getInt("numberOfPeople"));
|
|---|
| 390 | return existingOption;
|
|---|
| 391 | }
|
|---|
| 392 | }
|
|---|
| 393 | } catch (SQLException e) {
|
|---|
| 394 | e.printStackTrace();
|
|---|
| 395 | }
|
|---|
| 396 | return null;
|
|---|
| 397 | }
|
|---|
| 398 |
|
|---|
| 399 |
|
|---|
| 400 | public static int getCurrentOptionsCount() throws SQLException {
|
|---|
| 401 | String sql = "SELECT COUNT(*) AS optionsCount FROM options";
|
|---|
| 402 | try (Connection conn = getConnection();
|
|---|
| 403 | PreparedStatement stmt = conn.prepareStatement(sql);
|
|---|
| 404 | ResultSet rs = stmt.executeQuery()) {
|
|---|
| 405 | if (rs.next()) {
|
|---|
| 406 | return rs.getInt("optionsCount");
|
|---|
| 407 | } else {
|
|---|
| 408 | return 0;
|
|---|
| 409 | }
|
|---|
| 410 | }
|
|---|
| 411 | }
|
|---|
| 412 | public static int getChangedOptionsCountSinceLastUpdate() throws SQLException, IOException {
|
|---|
| 413 | LocalDateTime lastUpdateTime = Server.getLastUpdateTime();
|
|---|
| 414 | if (lastUpdateTime == null) {
|
|---|
| 415 | return 0;
|
|---|
| 416 | }
|
|---|
| 417 | String sql = "SELECT COUNT(*) AS changedOptionsCount FROM options WHERE lastModified > ?";
|
|---|
| 418 | try (Connection conn = getConnection();
|
|---|
| 419 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
|---|
| 420 | stmt.setTimestamp(1, Timestamp.valueOf(lastUpdateTime));
|
|---|
| 421 | try (ResultSet rs = stmt.executeQuery()) {
|
|---|
| 422 | if (rs.next()) {
|
|---|
| 423 | return rs.getInt("changedOptionsCount");
|
|---|
| 424 | } else {
|
|---|
| 425 | return 0;
|
|---|
| 426 | }
|
|---|
| 427 | }
|
|---|
| 428 | }
|
|---|
| 429 | }
|
|---|
| 430 |
|
|---|
| 431 | }
|
|---|
