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

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

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