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

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

pred-finalna
  • Property mode set to 100644
File size: 14.7 KB
RevLine 
[c164f8f]1import java.io.IOException;
2import java.sql.*;
3import java.time.LocalDateTime;
4import java.util.ArrayList;
5import java.util.List;
6
7public class DatabaseUtil {
8
9 private static final String DB_URL = "jdbc:sqlite:globe_guru.db";
10
11 public static Connection getConnection() throws SQLException {
12 return DriverManager.getConnection(DB_URL);
13 }
14 public static void initializeDatabase() throws SQLException {
15 try (Connection conn = getConnection();
16 PreparedStatement stmt1 = conn.prepareStatement(
17 "CREATE TABLE IF NOT EXISTS users (" +
18 "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
19 "username TEXT NOT NULL UNIQUE, " +
20 "email TEXT NOT NULL UNIQUE, " +
21 "password TEXT, " +
22 "isAdmin BOOLEAN NOT NULL DEFAULT FALSE)"
23 );
24 PreparedStatement stmt2 = conn.prepareStatement(
25 "CREATE TABLE IF NOT EXISTS options (" +
26 "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
27 "link TEXT, " +
28 "imgSrc TEXT, " +
29 "hotelName TEXT, " +
30 "country TEXT, " +
31 "price REAL, " +
32 "dateRange TEXT, " +
33 "isPriceChanged BOOLEAN DEFAULT 0, " +
34 "newPrice REAL DEFAULT 0)"
35 );
36
37 PreparedStatement stmt3 = conn.prepareStatement(
38 "CREATE TABLE IF NOT EXISTS savedOptions (" +
39 "userId INTEGER, " +
40 "optionId INTEGER, " +
41 "FOREIGN KEY(userId) REFERENCES users(id), " +
42 "FOREIGN KEY(optionId) REFERENCES options(id), " +
43 "UNIQUE(userId, optionId))"
44 )) {
45 stmt1.executeUpdate();
46 stmt2.executeUpdate();
47 stmt3.executeUpdate();
48 }
49 }
50
51 public static boolean registerUser(String username, String email, String password) throws SQLException {
52 String sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
53 try (Connection conn = getConnection();
54 PreparedStatement stmt = conn.prepareStatement(sql)) {
55 stmt.setString(1, username);
56 stmt.setString(2, email);
57 stmt.setString(3, password); // Store hashed
58 return stmt.executeUpdate() > 0;
59 }
60 }
61
62 public static boolean authenticateUser(String email, String password) throws SQLException {
63 String sql = "SELECT password FROM users WHERE email = ?";
64 try (Connection conn = getConnection();
65 PreparedStatement stmt = conn.prepareStatement(sql)) {
66 stmt.setString(1, email);
67 try (ResultSet rs = stmt.executeQuery()) {
68 if (rs.next()) {
69 String storedPassword = rs.getString("password");
70 if (password == null) {
71 // Assume this is a Google login
72 return storedPassword == null;
73 }
74 return password.equals(storedPassword); // Check hashed password
75 }
76 }
77 }
78 return false;
79 }
80
81 public static boolean deleteUser(int userId) throws SQLException {
82 String selectSql = "SELECT userId FROM users WHERE userId = ?";
83 String deleteSql = "DELETE FROM users WHERE userId = ?";
84 String deleteFavoritesSql = "DELETE FROM savedOptions WHERE userId = ?";
85
86 try (Connection conn = getConnection();
87 PreparedStatement selectStmt = conn.prepareStatement(selectSql);
88 PreparedStatement deleteStmt = conn.prepareStatement(deleteSql);
89 PreparedStatement deleteFavoritesStmt = conn.prepareStatement(deleteFavoritesSql)) {
90
91 selectStmt.setInt(1, userId);
92 try (ResultSet rs = selectStmt.executeQuery()) {
93 if (rs.next()) {
94 // User exists, delete the user and their favourite options
95 deleteStmt.setInt(1, userId);
96 int rowsAffected = deleteStmt.executeUpdate();
97
98 deleteFavoritesStmt.setInt(1, userId);
99 deleteFavoritesStmt.executeUpdate();
100
101 return rowsAffected > 0;
102 } else {
103 // User does not exist
104 return false;
105 }
106 }
107 }
108 }
109
110 public static boolean userExists(String email) throws SQLException {
111 String query = "SELECT COUNT(*) FROM users WHERE email = ?";
112 try (Connection connection = getConnection();
113 PreparedStatement statement = connection.prepareStatement(query)) {
114 statement.setString(1, email);
115 ResultSet resultSet = statement.executeQuery();
116 if (resultSet.next()) {
117 return resultSet.getInt(1) > 0;
118 }
119 }
120 return false;
121 }
122
123 public static boolean isAdmin(String email) throws SQLException {
124 String selectSql = "SELECT isAdmin FROM users WHERE email = ?";
125 try (Connection conn = getConnection();
126 PreparedStatement stmt = conn.prepareStatement(selectSql)) {
127 stmt.setString(1, email);
128 try (ResultSet rs = stmt.executeQuery()) {
129 if (rs.next()) {
130 return rs.getBoolean("isAdmin");
131 }
132 }
133 }
134 return false;
135 }
136
137 public static List<Option> queryOptions(String destination, String dateQuery, boolean dateFlag) throws SQLException {
138 List<Option> options = new ArrayList<>();
139 String sql = "SELECT * FROM options WHERE (country LIKE ? OR hotelName LIKE ?)";
140 System.out.println(dateQuery);
141 if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
142 sql += (" AND dateRange = ?");
143 } //append date
144 if (dateFlag) { //search only from dates
145 sql += "AND dateRange LIKE ?";
146 }
147 System.out.println("Searching for dest:" + destination + "\n" + sql);
148 try (Connection conn = getConnection();
149 PreparedStatement stmt = conn.prepareStatement(sql)) {
150 stmt.setString(1, "%" + destination + "%");
151 stmt.setString(2, "%" + destination + "%");
152 if (dateQuery != null && !dateQuery.isEmpty() && !dateFlag) {
153 stmt.setString(3, dateQuery);
154 }
155 if (dateFlag) {
156 stmt.setString(3, dateQuery + "%");
157 }
158 // Execute query
159 try (ResultSet rs = stmt.executeQuery()) {
160 while (rs.next()) {
161 Option option = new Option();
162 option.setId(rs.getInt("id"));
163 option.setLink(rs.getString("link"));
164 option.setImgSrc(rs.getString("imgSrc"));
165 option.setHotelName(rs.getString("hotelName"));
166 option.setCountry(rs.getString("country"));
167 option.setPrice(rs.getFloat("price"));
168 option.setDateRange(rs.getString("dateRange"));
169 options.add(option);
170 }
171 }
172 }
173 System.out.println("Found " + options.size());
174 return options;
175 }
176
177 public static boolean saveFavoriteOption(int userId, int optionId) throws SQLException {
178 String sql = "INSERT INTO savedOptions (userId, optionId) VALUES (?, ?) ON CONFLICT DO NOTHING";
179 try (Connection conn = getConnection();
180 PreparedStatement stmt = conn.prepareStatement(sql)) {
181 stmt.setInt(1, userId);
182 stmt.setInt(2, optionId);
183 return stmt.executeUpdate() > 0;
184 }
185 }
186
187 //TODO add frontend
188 public static boolean removeFavoriteOption(int userId, int optionId) throws SQLException {
189 String sql = "DELETE FROM savedOptions WHERE userId = ? AND optionId = ?";
190 try (Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {
191 stmt.setInt(1, userId);
192 stmt.setInt(2, optionId);
193 return stmt.executeUpdate() > 0;
194 }
195 }
196
197 public static List<Option> getSavedTripsByUser(int userId) throws SQLException {
198 List<Option> savedTrips = new ArrayList<>();
199 String sql = "SELECT options.* FROM savedOptions JOIN options ON savedOptions.optionId = options.id WHERE savedOptions.userId = ?";
200 try (Connection conn = getConnection();
201 PreparedStatement stmt = conn.prepareStatement(sql)) {
202 stmt.setInt(1, userId);
203 try (ResultSet rs = stmt.executeQuery()) {
204 while (rs.next()) {
205 Option option = new Option();
206 option.setId(rs.getInt("id"));
207 option.setLink(rs.getString("link"));
208 option.setImgSrc(rs.getString("imgSrc"));
209 option.setHotelName(rs.getString("hotelName"));
210 option.setCountry(rs.getString("country"));
211 option.setPrice(rs.getFloat("price"));
212 option.setDateRange(rs.getString("dateRange"));
213 option.setPriceChanged(rs.getBoolean("isPriceChanged"));
214 option.setNewPrice(rs.getInt("newPrice"));
215 savedTrips.add(option);
216 }
217 }
218 }
219 return savedTrips;
220 }
221
222
223 public static int getUserIdByEmail(String email) throws SQLException {
224 String sql = "SELECT id FROM users WHERE email = ?";
225 try (Connection conn = getConnection();
226 PreparedStatement stmt = conn.prepareStatement(sql)) {
227 stmt.setString(1, email);
228 try (ResultSet rs = stmt.executeQuery()) {
229 if (rs.next()) {
230 return rs.getInt("id");
231 } else {
232 throw new SQLException("User not found");
233 }
234 }
235 }
236 }
237
238 public static int getCurrentOptionsCount() throws SQLException {
239 String sql = "SELECT COUNT(*) AS optionsCount FROM options";
240 try (Connection conn = getConnection();
241 PreparedStatement stmt = conn.prepareStatement(sql);
242 ResultSet rs = stmt.executeQuery()) {
243 if (rs.next()) {
244 return rs.getInt("optionsCount");
245 } else {
246 return 0;
247 }
248 }
249 }
250
251 public static int getChangedOptionsCountSinceLastUpdate() throws SQLException, IOException {
252 LocalDateTime lastUpdateTime = Server.getLastUpdateTime();
253 if (lastUpdateTime == null) {
254 return 0;
255 }
256 String sql = "SELECT COUNT(*) AS changedOptionsCount FROM options WHERE lastModified > ?";
257 try (Connection conn = getConnection();
258 PreparedStatement stmt = conn.prepareStatement(sql)) {
259 stmt.setTimestamp(1, Timestamp.valueOf(lastUpdateTime));
260 try (ResultSet rs = stmt.executeQuery()) {
261 if (rs.next()) {
262 return rs.getInt("changedOptionsCount");
263 } else {
264 return 0;
265 }
266 }
267 }
268 }
269
270 public static void saveOptionToDatabase(Option option) {
271 String sql = "INSERT INTO options (link, imgSrc, hotelName, country, price, dateRange, isPriceChanged, newPrice) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
272 try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
273 PreparedStatement stmt = conn.prepareStatement(sql)) {
274 stmt.setString(1, option.getLink());
275 stmt.setString(2, option.getImgSrc());
276 stmt.setString(3, option.getHotelName());
277 stmt.setString(4, option.getCountry());
278 stmt.setFloat(5, option.getPrice());
279 stmt.setString(6, option.getDateRange());
280 stmt.setBoolean(7, option.isPriceChanged());
281 stmt.setFloat(8, option.getNewPrice());
282 stmt.executeUpdate();
283 } catch (SQLException e) {
284 e.printStackTrace();
285 }
286 }
287
288
289 public static void dropOptions() throws SQLException {
290 String sql = "DROP TABLE options";
291 try (
292 Connection conn = getConnection();
293 PreparedStatement stmt = conn.prepareStatement(sql)){
294 stmt.executeUpdate();
295 //Remake the options DB
296 initializeDatabase();
297
298 }
299 }
300 public static void updateOptionInDatabase(Option option) {
301 String sql = "UPDATE options SET link = ?, imgSrc = ?, hotelName = ?, country = ?, price = ?, dateRange = ?, isPriceChanged = ?, newPrice = ? WHERE id = ?";
302 try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
303 PreparedStatement stmt = conn.prepareStatement(sql)) {
304 stmt.setString(1, option.getLink());
305 stmt.setString(2, option.getImgSrc());
306 stmt.setString(3, option.getHotelName());
307 stmt.setString(4, option.getCountry());
308 stmt.setFloat(5, option.getPrice());
309 stmt.setString(6, option.getDateRange());
310 stmt.setBoolean(7, option.isPriceChanged());
311 stmt.setFloat(8, option.getNewPrice());
312 stmt.setInt(9, option.getId());
313 stmt.executeUpdate();
314 } catch (SQLException e) {
315 e.printStackTrace();
316 }
317 }
318
319 public static Option findOption(Option option) {
320 String sql = "SELECT * FROM options WHERE id = ?";
321 try (Connection conn = DriverManager.getConnection("jdbc:sqlite:globe_guru.db");
322 PreparedStatement stmt = conn.prepareStatement(sql)) {
323 stmt.setInt(1, option.getId());
324 try (ResultSet rs = stmt.executeQuery()) {
325 if (rs.next()) {
326 Option existingOption = new Option();
327 existingOption.setId(rs.getInt("id"));
328 existingOption.setLink(rs.getString("link"));
329 existingOption.setImgSrc(rs.getString("imgSrc"));
330 existingOption.setHotelName(rs.getString("hotelName"));
331 existingOption.setCountry(rs.getString("country"));
332 existingOption.setPrice(rs.getFloat("price"));
333 existingOption.setDateRange(rs.getString("dateRange"));
334 existingOption.setPriceChanged(rs.getBoolean("isPriceChanged"));
335 existingOption.setNewPrice(rs.getInt("newPrice"));
336 return existingOption;
337 }
338 }
339 } catch (SQLException e) {
340 e.printStackTrace();
341 }
342 return null;
343 }
344
345}
Note: See TracBrowser for help on using the repository browser.