· 5 years ago · May 10, 2020, 03:58 PM
1package hu.alkfejl.dao;
2
3import hu.alkfejl.model.*;
4
5import java.sql.*;
6import java.util.ArrayList;
7import java.util.List;
8
9
10public class CinemaDAOImp implements CinemaDAO {
11 private static final String CONN_STR = "jdbc:sqlite:cinema.db";
12 private static final String CREATE_MOVIE = "CREATE TABLE IF NOT EXISTS Movie(" +
13 "id integer primary key autoincrement," +
14 "title text," +
15 "length integer," +
16 "rating text," +
17 "director text," +
18 "description text," +
19 "cover text);";
20 private static final String CREATE_ROOM = "CREATE TABLE IF NOT EXISTS Room(" +
21 "id integer primary key autoincrement," +
22 "rows integer not null," +
23 "columns integer not null);";
24 private static final String CREATE_SCREENING = "CREATE TABLE IF NOT EXISTS Screening(" +
25 "id integer primary key autoincrement," +
26 "movieId integer not null," +
27 "datetime text not null," +
28 "room integer not null," +
29 "foreign key(movieId) references Movie(id) ON DELETE CASCADE ON UPDATE CASCADE," +
30 "foreign key (room) references Room(id)ON DELETE CASCADE ON UPDATE CASCADE);";
31 private static final String CREATE_ACTOR = "CREATE TABLE IF NOT EXISTS Actor(" +
32 "movieId not null," +
33 "name text not null," +
34 "primary key(movieId,name));";
35 private static final String CREATE_RESERVATION = "CREATE TABLE IF NOT EXISTS Reservation(" +
36 "id integer primary key autoincrement," +
37 "screeningId integer not null," +
38 "username text not null," +
39 "foreign key(screeningId) references Screening(id) ON DELETE CASCADE ON UPDATE CASCADE," +
40 "foreign key(username) references User(username) ON DELETE CASCADE ON UPDATE CASCADE);";
41 private static final String CREATE_SEAT = "CREATE TABLE IF NOT EXISTS Seat(" +
42 "resId integer not null," +
43 "number integer not null," +
44 "primary key(resId,number)," +
45 "foreign key(resId) references Reservation(id) ON DELETE CASCADE ON UPDATE CASCADE);";
46 private static final String CREATE_USER = "CREATE TABLE IF NOT EXISTS User(" +
47 "username text primary key," +
48 "password text not null" +
49 ");";
50 private static final String FOREIGN_KEYS_ON = "PRAGMA foreign_keys = ON;";
51 private static final String INSERT_MOVIE = "INSERT INTO Movie VALUES(null,?,?,?,?,?,?);";
52 private static final String DELETE_MOVIE = "DELETE FROM Movie where id = ?;";
53 private static final String UPDATE_MOVIE = "UPDATE Movie SET title=?,length=?,rating=?,director=?,description=?,cover=? where id =?;";
54 private static final String SELECT_ALL_MOVIES = "SELECT * FROM Movie;";
55 private static final String SELECT_ALL_SCREENINGS = "SELECT id, movieId, date(datetime), time(datetime),room FROM Screening;";
56 private static final String INSERT_SCREENING = "INSERT INTO Screening VALUES(null,?,?,?);";
57 private static final String DELETE_SCREENING = "DELETE FROM Screening WHERE id=?;";
58 private static final String UPDATE_SCREENING = "UPDATE Screening SET movieId=?,datetime=?,room=? WHERE id=?";
59 private static final String INSERT_ROOM = "INSERT INTO Room VALUES(null,?,?)";
60 private static final String DELETE_ROOM = "DELETE FROM Room WHERE id=?;";
61 private static final String UPDATE_ROOM = "UPDATE Room SET rows = ?,columns = ? WHERE id=?;";
62 private static final String SELECT_ALL_ROOMS = "SELECT * FROM Room;";
63 private static final String INSERT_RESERVATION = "INSERT INTO Reservation VALUES (null,?,?)";
64 private static final String DELETE_RESERVATION = "DELETE FROM Reservation where id=?;";
65 private static final String UPDATE_RESERVATION = "UPDATE Reservation SET screeningId = ?, username = ? WHERE id=?;";
66 private static final String SELECT_ALL_RESERVATIONS = "SELECT * FROM Reservation;";
67 private static final String INSERT_ACTOR = "INSERT INTO Actor VALUES(?,?);";
68 private static final String LAST_MOVIE_ID = "SELECT seq FROM sqlite_sequence WHERE name=\"Movie\";";
69 private static final String LAST_RESERVATION_ID = "SELECT seq FROM sqlite_sequence WHERE name=\"Reservation\";";
70 private static final String SELECT_ACTORS_BY_MOVIE_ID = "SELECT name FROM Actor WHERE movieId=?;";
71 private static final String DELETE_ACTOR = "DELETE FROM Actor WHERE movieId=? AND name=?;";
72 private static final String MOVIE_TITLE_BY_RES_ID = "SELECT title FROM Movie,Reservation,Screening WHERE Screening.id=screeningId AND Movie.id=movieId AND Reservation.id=?;";
73 private static final String SCREENING_DATETIME_BY_RES_ID = "SELECT datetime FROM Reservation,Screening WHERE Screening.id=screeningId AND Reservation.id =?;";
74 private static final String SEATS_BY_RES_ID = "SELECT number FROM Seat,Reservation WHERE Reservation.id=resId AND Reservation.id=?;";
75 private static final String ROOM_BY_RES_ID ="SELECT room FROM Reservation,Screening WHERE Reservation.screeningId=Screening.id AND Reservation.id=?;";
76 private static final String SCREENING_BY_MOVIE_ID = "SELECT * FROM Screening WHERE movieId=?;";
77 private static final String SEATS_BY_SCREENING_ID = "SELECT number FROM Seat,Reservation,Screening WHERE screeningId=Screening.id AND Reservation.id=resId AND screeningId=?;";
78 private static final String DIMENSIONS_BY_SCREENING_ID = "SELECT rows, columns FROM Room, Screening WHERE Screening.room=Room.id AND Screening.id =?;";
79 private static final String SELECT_ALL_USERNAMES = "SELECT username FROM User;";
80 private static final String INSERT_SEAT = "INSERT INTO Seat VALUES(?,?);";
81
82 public CinemaDAOImp() {
83 try {
84 Class.forName("org.sqlite.JDBC");
85 } catch (ClassNotFoundException e) {
86 e.printStackTrace();
87 }
88 initTables();
89 }
90
91 public void initTables() {
92 try (Connection conn = DriverManager.getConnection(CONN_STR); Statement st = conn.createStatement()) {
93 st.executeUpdate(FOREIGN_KEYS_ON);
94 st.executeUpdate(CREATE_MOVIE);
95 st.executeUpdate(CREATE_ROOM);
96 st.executeUpdate(CREATE_ACTOR);
97 st.executeUpdate(CREATE_SCREENING);
98 st.executeUpdate(CREATE_RESERVATION);
99 st.executeUpdate(CREATE_SEAT);
100 st.executeUpdate(CREATE_USER);
101 } catch (SQLException e) {
102 e.printStackTrace();
103 }
104 }
105
106 @Override
107 public boolean addMovie(Movie m) {
108 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(INSERT_MOVIE)) {
109 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
110 ps.setString(1, m.getTitle());
111 ps.setInt(2, m.getLength());
112 ps.setString(3, m.getRating());
113 ps.setString(4, m.getDirector());
114 ps.setString(5, m.getDescription());
115 ps.setString(6, m.getCover());
116 int res = ps.executeUpdate();
117 if (res == 1) {
118 return true;
119 }
120 } catch (SQLException e) {
121 e.printStackTrace();
122 }
123 return false;
124 }
125
126 @Override
127 public boolean deleteMovie(Movie m) {
128 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(DELETE_MOVIE)) {
129 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
130 ps.setInt(1, m.getId());
131 int res = ps.executeUpdate();
132 if (res == 1) {
133 return true;
134 }
135 } catch (SQLException e) {
136 e.printStackTrace();
137 }
138 return false;
139 }
140
141 @Override
142 public boolean updateMovie(int id, Movie m) {
143 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(UPDATE_MOVIE)) {
144 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
145 ps.setString(1, m.getTitle());
146 ps.setInt(2, m.getLength());
147 ps.setString(3, m.getRating());
148 ps.setString(4, m.getDirector());
149 ps.setString(5, m.getDescription());
150 ps.setString(6, m.getCover());
151 ps.setInt(7, id);
152 int res = ps.executeUpdate();
153 if (res == 1) {
154 return true;
155 }
156 } catch (SQLException e) {
157 e.printStackTrace();
158 }
159 return false;
160 }
161
162
163 //TODO: TEST IT MOFO
164 @Override
165 public List<Movie> advSearchMovies(String title, Integer length, String rating, String director) {
166 List<Movie> res = new ArrayList<Movie>();
167 String advSearch = "SELECT * FROM Movie WHERE ";
168 String options = "";
169 if (title != null)
170 options += "title = " + title + ",";
171 if (length != null)
172 options += "length = " + length + ",";
173 if (rating != null)
174 options += "rating = " + rating + ",";
175 if (director != null)
176 options += "director = " + director + ",";
177 if (options.length() == 0) {
178 return res;
179 }
180 else {
181 advSearch += options.substring(0, options.length() - 1);
182 }
183 try (Connection conn = DriverManager.getConnection(CONN_STR);
184 Statement st = conn.createStatement();
185 ResultSet rs = st.executeQuery(advSearch)
186 ) {
187
188 while (rs.next()) {
189 Movie m = new Movie(
190 rs.getInt(1),
191 rs.getString(2),
192 rs.getInt(3),
193 rs.getString(4),
194 rs.getString(5),
195 rs.getString(6),
196 rs.getString(7)
197 );
198 res.add(m);
199 }
200
201 } catch (SQLException e) {
202 e.printStackTrace();
203 }
204
205 return res;
206 }
207
208 @Override
209 public List<Movie> listAllMovies() {
210 List<Movie> res = new ArrayList<Movie>();
211 try (Connection conn = DriverManager.getConnection(CONN_STR);
212 Statement st = conn.createStatement();
213 ResultSet rs = st.executeQuery(SELECT_ALL_MOVIES)
214 ) {
215 while (rs.next()) {
216 Movie m = new Movie(
217 rs.getInt(1),
218 rs.getString(2),
219 rs.getInt(3),
220 rs.getString(4),
221 rs.getString(5),
222 rs.getString(6),
223 rs.getString(7)
224 );
225 res.add(m);
226 }
227
228 } catch (SQLException e) {
229 e.printStackTrace();
230 }
231
232 return res;
233 }
234
235 @Override
236 public Integer getLastMovieId() {
237 try (Connection conn = DriverManager.getConnection(CONN_STR);
238 Statement st = conn.createStatement();
239 ResultSet rs = st.executeQuery(LAST_MOVIE_ID)
240 ) {
241 return rs.getInt(1);
242
243 } catch (SQLException e) {
244 e.printStackTrace();
245 }
246
247 return null;
248 }
249
250 @Override
251 public List<Screening> listScreenings(int movieId) {
252 List<Screening> res = new ArrayList<>();
253 try (Connection conn = DriverManager.getConnection(CONN_STR);
254 Statement st = conn.createStatement();
255 ResultSet rs = st.executeQuery(SELECT_ALL_SCREENINGS)
256 ) {
257 while (rs.next()) {
258 Screening s = new Screening(
259 rs.getInt(1),
260 rs.getInt(2),
261 rs.getString(3),
262 rs.getString(4),
263 rs.getInt(5)
264 );
265 res.add(s);
266 }
267
268 } catch (SQLException e) {
269 e.printStackTrace();
270 }
271
272 return res;
273 }
274
275 @Override
276 public boolean addScreening(Screening s) {
277 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(INSERT_SCREENING)) {
278 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
279 ps.setInt(1, s.getMovieId());
280 ps.setString(2, s.getDate() + "T" + s.getTime() + "+00:00");
281 ps.setInt(3, s.getRoom());
282 int res = ps.executeUpdate();
283 if (res == 1) {
284 return true;
285 }
286 } catch (SQLException e) {
287 e.printStackTrace();
288 }
289 return false;
290 }
291
292 @Override
293 public boolean deleteScreening(Screening s) {
294 try (Connection conn = DriverManager.getConnection(CONN_STR);
295 PreparedStatement ps = conn.prepareStatement(DELETE_SCREENING)) {
296 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
297 ps.setInt(1, s.getId());
298 int res = ps.executeUpdate();
299 if (res == 1) {
300 return true;
301 }
302
303 } catch (SQLException e) {
304 e.printStackTrace();
305 }
306 return false;
307 }
308
309 @Override
310 public boolean updateScreening(int id, Screening s) {
311 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(UPDATE_SCREENING)) {
312 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
313 ps.setInt(1, s.getMovieId());
314 ps.setString(2, s.getTime());
315 ps.setInt(3,s.getRoom());
316 ps.setInt(4,id);
317 int res = ps.executeUpdate();
318 if (res == 1) {
319 return true;
320 }
321 } catch (SQLException e) {
322 e.printStackTrace();
323 }
324 return false;
325 }
326
327 @Override
328 public boolean addRoom(Room r) {
329 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(INSERT_ROOM)) {
330 ps.setInt(1, r.getRows());
331 ps.setInt(2, r.getColumns());
332 int res = ps.executeUpdate();
333 if (res == 1) {
334 return true;
335 }
336 } catch (SQLException e) {
337 e.printStackTrace();
338 }
339 return false;
340 }
341
342 @Override
343 public boolean deleteRoom(Room r) {
344 try (Connection conn = DriverManager.getConnection(CONN_STR);
345 PreparedStatement ps = conn.prepareStatement(DELETE_ROOM)) {
346 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
347 ps.setInt(1, r.getId());
348 int res = ps.executeUpdate();
349 if (res == 1) {
350 return true;
351 }
352
353 } catch (SQLException throwables) {
354 throwables.printStackTrace();
355 }
356 return false;
357 }
358
359 @Override
360 public boolean updateRoom(int id, Room r) {
361 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(UPDATE_ROOM)) {
362 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
363 ps.setInt(1, r.getRows());
364 ps.setInt(2, r.getColumns());
365 ps.setInt(3,id);
366 int res = ps.executeUpdate();
367 if (res == 1) {
368 return true;
369 }
370 } catch (SQLException e) {
371 e.printStackTrace();
372 }
373 return false;
374 }
375
376 @Override
377 public List<Room> listAllRooms() {
378 List<Room> res = new ArrayList<>();
379 try (Connection conn = DriverManager.getConnection(CONN_STR);
380 Statement st = conn.createStatement();
381 ResultSet rs = st.executeQuery(SELECT_ALL_ROOMS)
382 ) {
383 while (rs.next()) {
384 Room r = new Room(
385 rs.getInt(1),
386 rs.getInt(2),
387 rs.getInt(3)
388 );
389 res.add(r);
390 }
391
392 } catch (SQLException e) {
393 e.printStackTrace();
394 }
395
396 return res;
397 }
398
399 @Override
400 public boolean addReservation(Reservation r) {
401 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(INSERT_RESERVATION)) {
402 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
403 ps.setInt(1, r.getScreeningId());
404 ps.setString(2, r.getUsername());
405 int res = ps.executeUpdate();
406 if (res == 1) {
407 return true;
408 }
409 } catch (SQLException e) {
410 e.printStackTrace();
411 }
412 return false;
413 }
414
415 @Override
416 public boolean deleteReservation(Reservation r) {
417 try (Connection conn = DriverManager.getConnection(CONN_STR);
418 PreparedStatement ps = conn.prepareStatement(DELETE_RESERVATION)) {
419 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
420 ps.setInt(1, r.getId());
421 int res = ps.executeUpdate();
422 if (res == 1) {
423 return true;
424 }
425
426 } catch (SQLException throwables) {
427 throwables.printStackTrace();
428 }
429 return false;
430 }
431
432 @Override
433 public boolean updateReservation(int id, Reservation r) {
434 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(UPDATE_RESERVATION)) {
435 conn.createStatement().executeUpdate(FOREIGN_KEYS_ON);
436 ps.setInt(1, r.getScreeningId());
437 ps.setString(2, r.getUsername());
438 int res = ps.executeUpdate();
439 if (res == 1) {
440 return true;
441 }
442 } catch (SQLException e) {
443 e.printStackTrace();
444 }
445 return false;
446 }
447
448 @Override
449 public Integer getLastReservationId() {
450 try (Connection conn = DriverManager.getConnection(CONN_STR);
451 Statement st = conn.createStatement();
452 ResultSet rs = st.executeQuery(LAST_RESERVATION_ID)
453 ) {
454 return rs.getInt(1);
455
456 } catch (SQLException e) {
457 e.printStackTrace();
458 }
459
460 return null;
461 }
462
463 @Override
464 public List<Reservation> listAllReservations() {
465 List<Reservation> res = new ArrayList<>();
466 try (Connection conn = DriverManager.getConnection(CONN_STR);
467 Statement st = conn.createStatement();
468 ResultSet rs = st.executeQuery(SELECT_ALL_RESERVATIONS)
469 ) {
470 while (rs.next()) {
471 Reservation r = new Reservation(
472 rs.getInt(1),
473 rs.getInt(2),
474 rs.getString(3)
475 );
476 res.add(r);
477 }
478
479 } catch (SQLException e) {
480 e.printStackTrace();
481 }
482
483 return res;
484 }
485
486 @Override
487 public List<Reservation> advSearchReservations(String username, Integer screeningId) {
488 List<Reservation> res = new ArrayList<>();
489 String advSearch = "SELECT * FROM Reservation WHERE ";
490 String options = "";
491 if (username != null)
492 options += "username = " + username + ",";
493 if (screeningId != null)
494 options += "screeningId = " + screeningId + ",";
495 if (options.length() == 0) {
496 return res;
497 }
498 advSearch += options.substring(0, options.length() - 1);
499 try (Connection conn = DriverManager.getConnection(CONN_STR);
500 Statement st = conn.createStatement();
501 ResultSet rs = st.executeQuery(advSearch)
502 ) {
503
504 while (rs.next()) {
505 Reservation r = new Reservation(
506 rs.getInt(1),
507 rs.getInt(2),
508 rs.getString(3)
509 );
510 res.add(r);
511 }
512
513 } catch (SQLException e) {
514 e.printStackTrace();
515 }
516
517 return res;
518 }
519
520 @Override
521 public boolean addActor(Actor a) {
522 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(INSERT_ACTOR)) {
523 ps.setInt(1, a.getMovieId());
524 ps.setString(2, a.getName());
525 int res = ps.executeUpdate();
526 if (res == 1) {
527 return true;
528 }
529 } catch (SQLException e) {
530 e.printStackTrace();
531 }
532 return false;
533 }
534
535 @Override
536 public boolean deleteActor(Actor a) {
537 try (Connection conn = DriverManager.getConnection(CONN_STR);
538 PreparedStatement ps = conn.prepareStatement(DELETE_ACTOR)) {
539 ps.setInt(1, a.getMovieId());
540 ps.setString(2,a.getName());
541 int res = ps.executeUpdate();
542 if (res == 1) {
543 return true;
544 }
545
546 } catch (SQLException e) {
547 e.printStackTrace();
548 }
549 return false;
550 }
551
552 @Override
553 public List<User> listAllUsers() {
554 //TODO: body
555 return null;
556 }
557
558 @Override
559 public List<String> listAllUsernames() {
560 List<String> res = new ArrayList<>();
561 try (Connection conn = DriverManager.getConnection(CONN_STR);
562 Statement st = conn.createStatement();
563 ResultSet rs = st.executeQuery(SELECT_ALL_USERNAMES)
564 ) {
565 while (rs.next()) {
566 res.add(rs.getString(1));
567 }
568
569 } catch (SQLException e) {
570 e.printStackTrace();
571 }
572
573 return res;
574 }
575
576
577 @Override
578 public List<String> listActorNamesOfMovie(Integer movieId){
579 List<String> res = new ArrayList<>();
580 try (Connection conn = DriverManager.getConnection(CONN_STR);
581 PreparedStatement ps = conn.prepareStatement(SELECT_ACTORS_BY_MOVIE_ID)
582 ) {
583 ps.setInt(1,movieId);
584 try(ResultSet rs = ps.executeQuery()){
585 while(rs.next()){
586 res.add(rs.getString(1));
587 }
588 }
589 } catch (SQLException e) {
590 e.printStackTrace();
591 }
592 return res;
593 }
594
595 @Override
596 public String getMovieTitleByReservationId(Integer reservationId) {
597 String res = null;
598 try (Connection conn = DriverManager.getConnection(CONN_STR);
599 PreparedStatement ps = conn.prepareStatement(MOVIE_TITLE_BY_RES_ID)
600 ) {
601 ps.setInt(1,reservationId);
602 try(ResultSet rs = ps.executeQuery()){
603 res = rs.getString(1);
604 }
605 } catch (SQLException e) {
606 e.printStackTrace();
607 }
608 return res;
609 }
610
611 @Override
612 public String getScreeningDateTimeByReservationId(Integer reservationId) {
613 String res = null;
614 try (Connection conn = DriverManager.getConnection(CONN_STR);
615 PreparedStatement ps = conn.prepareStatement(SCREENING_DATETIME_BY_RES_ID)
616 ) {
617 ps.setInt(1,reservationId);
618 try(ResultSet rs = ps.executeQuery()){
619 res = rs.getString(1);
620 }
621 } catch (SQLException e) {
622 e.printStackTrace();
623 }
624 return res;
625 }
626
627 @Override
628 public List<Integer> getSeatsByReservationId(Integer reservationId) {
629 List<Integer> res = new ArrayList<>();
630 try (Connection conn = DriverManager.getConnection(CONN_STR);
631 PreparedStatement ps = conn.prepareStatement(SEATS_BY_RES_ID)
632 ) {
633 ps.setInt(1,reservationId);
634 try(ResultSet rs = ps.executeQuery()){
635 while(rs.next()) {
636 res.add(rs.getInt(1));
637 }
638 }
639 } catch (SQLException e) {
640 e.printStackTrace();
641 }
642 return res;
643 }
644
645 @Override
646 public Integer getRoomByReservationId(Integer reservationId) {
647 Integer res = null;
648 try (Connection conn = DriverManager.getConnection(CONN_STR);
649 PreparedStatement ps = conn.prepareStatement(ROOM_BY_RES_ID);
650 ) {
651 ps.setInt(1,reservationId);
652 try(ResultSet rs = ps.executeQuery()){
653 res = rs.getInt(1);
654 }
655 } catch (SQLException e) {
656 e.printStackTrace();
657 }
658 return res;
659 }
660
661 @Override
662 public List<Screening> getScreeningsByMovieId(Integer movieId) {
663 List<Screening> res = new ArrayList<>();
664 try (Connection conn = DriverManager.getConnection(CONN_STR);
665 PreparedStatement ps = conn.prepareStatement(SCREENING_BY_MOVIE_ID)
666 ) {
667 ps.setInt(1,movieId);
668 try(ResultSet rs = ps.executeQuery()){
669 while(rs.next()) {
670 res.add(new Screening(
671 rs.getInt(1),
672 rs.getInt(2),
673 rs.getString(3).split("T")[0],
674 rs.getString(3).split("T")[1],
675 rs.getInt(4)
676 ));
677 }
678 }
679 } catch (SQLException e) {
680 e.printStackTrace();
681 }
682 return res;
683 }
684
685 @Override
686 public List<Integer> getSeatsByScreeningId(Integer screeningId) {
687 List<Integer> res = new ArrayList<>();
688 try (Connection conn = DriverManager.getConnection(CONN_STR);
689 PreparedStatement ps = conn.prepareStatement(SEATS_BY_SCREENING_ID)
690 ) {
691 ps.setInt(1,screeningId);
692 try(ResultSet rs = ps.executeQuery()){
693 while(rs.next()) {
694 res.add(rs.getInt(1));
695 }
696 }
697 } catch (SQLException e) {
698 e.printStackTrace();
699 }
700 return res;
701 }
702
703 @Override
704 public List<Integer> getRoomDimensionsByScreeningId(Integer screeningId) {
705 List<Integer> res = new ArrayList<>();
706 try (Connection conn = DriverManager.getConnection(CONN_STR);
707 PreparedStatement ps = conn.prepareStatement(DIMENSIONS_BY_SCREENING_ID)
708 ) {
709 ps.setInt(1,screeningId);
710 try(ResultSet rs = ps.executeQuery()){
711 res.add(rs.getInt(1));
712 res.add(rs.getInt(2));
713 }
714 } catch (SQLException e) {
715 e.printStackTrace();
716 }
717 return res;
718 }
719
720 @Override
721 public boolean addSeat(Seat s) {
722 try (Connection conn = DriverManager.getConnection(CONN_STR); PreparedStatement ps = conn.prepareStatement(INSERT_SEAT)) {
723 ps.setInt(1, s.getResId());
724 ps.setInt(2, s.getNumber());
725 int res = ps.executeUpdate();
726 if (res == 1) {
727 return true;
728 }
729 } catch (SQLException e) {
730 e.printStackTrace();
731 }
732 return false;
733 }
734}