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

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

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