source: backend/GlobeGuru-backend/src/main/java/DatabaseUtil.java@ cd64b06

Last change on this file since cd64b06 was cd64b06, checked in by Kristijan <kristijanzafirovski26@…>, 5 days ago

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