[c164f8f] | 1 | import java.io.IOException;
|
---|
| 2 | import java.sql.*;
|
---|
| 3 | import java.time.LocalDateTime;
|
---|
| 4 | import java.util.ArrayList;
|
---|
[1c51912] | 5 | import java.util.HashMap;
|
---|
[c164f8f] | 6 | import java.util.List;
|
---|
[1c51912] | 7 | import java.util.Map;
|
---|
[c164f8f] | 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, " +
|
---|
[53bad7e] | 34 | "numberOfPeople INTEGER, " +
|
---|
[c164f8f] | 35 | "isPriceChanged BOOLEAN DEFAULT 0, " +
|
---|
| 36 | "newPrice REAL DEFAULT 0)"
|
---|
| 37 | );
|
---|
| 38 | PreparedStatement stmt3 = conn.prepareStatement(
|
---|
| 39 | "CREATE TABLE IF NOT EXISTS savedOptions (" +
|
---|
[1c51912] | 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))"
|
---|
[c164f8f] | 55 | )) {
|
---|
[1c51912] | 56 |
|
---|
[c164f8f] | 57 | stmt1.executeUpdate();
|
---|
| 58 | stmt2.executeUpdate();
|
---|
| 59 | stmt3.executeUpdate();
|
---|
[1c51912] | 60 | stmt4.executeUpdate();
|
---|
[c164f8f] | 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);
|
---|
[53bad7e] | 70 | stmt.setString(3, password);
|
---|
[c164f8f] | 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) {
|
---|
[53bad7e] | 83 | // Google login
|
---|
[c164f8f] | 84 | return storedPassword == null;
|
---|
| 85 | }
|
---|
[53bad7e] | 86 | return password.equals(storedPassword);
|
---|
[c164f8f] | 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 | }
|
---|
[1c51912] | 143 | public static boolean saveFavoriteOption(int userId, int detailId) throws SQLException {
|
---|
| 144 | String sql = "INSERT INTO savedOptions (userId, detailId) VALUES (?, ?) ON CONFLICT DO NOTHING";
|
---|
[c164f8f] | 145 | try (Connection conn = getConnection();
|
---|
| 146 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
---|
| 147 | stmt.setInt(1, userId);
|
---|
[1c51912] | 148 | stmt.setInt(2, detailId);
|
---|
[c164f8f] | 149 | return stmt.executeUpdate() > 0;
|
---|
| 150 | }
|
---|
| 151 | }
|
---|
[1c51912] | 152 | public static boolean removeFavoriteOption(int userId, int detailId) throws SQLException {
|
---|
| 153 | String sql = "DELETE FROM savedOptions WHERE userId = ? AND detailId = ?";
|
---|
[c164f8f] | 154 | try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {
|
---|
| 155 | stmt.setInt(1, userId);
|
---|
[1c51912] | 156 | stmt.setInt(2, detailId);
|
---|
[c164f8f] | 157 | return stmt.executeUpdate() > 0;
|
---|
| 158 | }
|
---|
| 159 | }
|
---|
| 160 | public static List<Option> getSavedTripsByUser(int userId) throws SQLException {
|
---|
| 161 | List<Option> savedTrips = new ArrayList<>();
|
---|
[1c51912] | 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 = ?";
|
---|
[c164f8f] | 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();
|
---|
[1c51912] | 172 | option.setId(rs.getInt("detail_id"));
|
---|
[c164f8f] | 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"));
|
---|
[1c51912] | 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"));
|
---|
[c164f8f] | 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 |
|
---|
[0a7426e] | 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 | }
|
---|
[c164f8f] | 222 | }
|
---|
[0a7426e] | 223 |
|
---|
[c164f8f] | 224 | }
|
---|
[0a7426e] | 225 | return options;
|
---|
[c164f8f] | 226 | }
|
---|
[0a7426e] | 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";
|
---|
[c164f8f] | 236 |
|
---|
[0a7426e] | 237 | if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
|
---|
| 238 | sql += " AND o.dateRange = ?";
|
---|
[c164f8f] | 239 | }
|
---|
[0a7426e] | 240 | if (dateFlag) {
|
---|
| 241 | sql += " AND o.dateRange LIKE ?";
|
---|
| 242 | }
|
---|
| 243 | if (numPeople != 0) {
|
---|
| 244 | sql += " AND o.numberOfPeople = ?";
|
---|
| 245 | }
|
---|
| 246 |
|
---|
[c164f8f] | 247 | try (Connection conn = getConnection();
|
---|
| 248 | PreparedStatement stmt = conn.prepareStatement(sql)) {
|
---|
[0a7426e] | 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 |
|
---|
[c164f8f] | 262 | try (ResultSet rs = stmt.executeQuery()) {
|
---|
[0a7426e] | 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);
|
---|
[c164f8f] | 277 | }
|
---|
| 278 | }
|
---|
| 279 | }
|
---|
| 280 |
|
---|
[0a7426e] | 281 | System.out.println("Found " + options.size() + " options");
|
---|
| 282 | return options;
|
---|
| 283 | }
|
---|
[1c51912] | 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;
|
---|
[c164f8f] | 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());
|
---|
[1c51912] | 293 | stmt.setString(5, option.getDateRange());
|
---|
| 294 | stmt.setInt(6,option.getNumPeople());
|
---|
| 295 | stmt.setBoolean(7, option.isPriceChanged());
|
---|
| 296 | stmt.setFloat(8, option.getNewPrice());
|
---|
[c164f8f] | 297 | stmt.executeUpdate();
|
---|
[1c51912] | 298 | try(ResultSet genKey = stmt.getGeneratedKeys()){
|
---|
| 299 | if(genKey.next()){
|
---|
| 300 | id = genKey.getInt(1);
|
---|
| 301 | }
|
---|
| 302 | }
|
---|
[c164f8f] | 303 | } catch (SQLException e) {
|
---|
| 304 | e.printStackTrace();
|
---|
| 305 | }
|
---|
[1c51912] | 306 | return id;
|
---|
[c164f8f] | 307 | }
|
---|
[1c51912] | 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 | }
|
---|
[c164f8f] | 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 | }
|
---|
[0a7426e] | 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 |
|
---|
[c164f8f] | 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"));
|
---|
[53bad7e] | 389 | existingOption.setNumPeople(rs.getInt("numberOfPeople"));
|
---|
[c164f8f] | 390 | return existingOption;
|
---|
| 391 | }
|
---|
| 392 | }
|
---|
| 393 | } catch (SQLException e) {
|
---|
| 394 | e.printStackTrace();
|
---|
| 395 | }
|
---|
| 396 | return null;
|
---|
| 397 | }
|
---|
| 398 |
|
---|
[1c51912] | 399 |
|
---|
[0a7426e] | 400 | public static int getCurrentOptionsCount() throws SQLException {
|
---|
| 401 | String sql = "SELECT COUNT(*) AS optionsCount FROM options";
|
---|
| 402 | try (Connection conn = getConnection();
|
---|
[1c51912] | 403 | PreparedStatement stmt = conn.prepareStatement(sql);
|
---|
| 404 | ResultSet rs = stmt.executeQuery()) {
|
---|
[0a7426e] | 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 | }
|
---|
[1c51912] | 427 | }
|
---|
| 428 | }
|
---|
| 429 | }
|
---|
| 430 |
|
---|
[c164f8f] | 431 | }
|
---|