· 7 years ago · Nov 23, 2018, 08:42 PM
1package app.repository;
2
3import app.servlets.RegisterImpl;
4
5import java.sql.*;
6import java.util.ArrayList;
7import java.util.List;
8import java.util.Queue;
9import java.util.Set;
10
11import static java.lang.System.out;
12
13public class DBConnectionImpl implements DBConnection {
14
15 private final String url = "jdbc:h2:E:\\Programowanie\\Java\\MyWebApp/my-local";
16 private final String dbDriver = "oracle.jdbc.Driver";
17
18
19 private Connection connection = DriverManager.getConnection(url, "sa", "");
20 private Statement statement;
21 private ResultSet resultSet;
22
23 public final String regQuery = "insert into users " +
24 " (name,password,mail,country)" +
25 " values (?,?,?,?);";
26
27 public final String loginQuery = "select MAIL, PASSWORD from USERS;";
28
29 public DBConnectionImpl() throws SQLException {
30
31 try (Connection connection = DriverManager.getConnection(url, "sa", "")) {
32
33 try (Statement statement = connection.createStatement()) {
34 statement.execute
35 ("CREATE TABLE IF NOT EXISTS users (" +
36 "id number(6,0) not null AUTO_INCREMENT primary key," +
37 "name varchar2(20) not null," +
38 "password varchar2(12) not null," +
39 "mail varchar2(40) not null," +
40 "country varchar(20));");
41 statement.execute
42
43 ("CREATE TABLE IF NOT EXISTS movies (" +
44 "id number(6,0) not null AUTO_INCREMENT primary key," +
45 "title varchar2(20) not null," +
46 "type varchar2(20) not null," +
47 "lenght number(4,0) not null," +
48 "description varchar2(255) not null," +
49 "image_url varchar2(255));");
50 }
51
52
53 } catch (SQLException a) {
54 a.printStackTrace();
55 }
56
57
58 }
59
60 @Override
61 public long registerUser(User user) {
62
63 try (PreparedStatement preparedStatement = connection.prepareStatement(regQuery, Statement.RETURN_GENERATED_KEYS)) {
64 preparedStatement.setString(1, user.getName());
65 preparedStatement.setString(2, user.getPwd());
66 preparedStatement.setString(3, user.getEmail());
67 preparedStatement.setString(4, user.getCountry());
68
69 int check = preparedStatement.executeUpdate();
70
71 if (check != 1) {
72 throw new IllegalStateException(String.format("Should insert one row. Actually inserted: %d", check));
73 }
74 try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
75 if (!generatedKeys.next()) {
76 throw new IllegalStateException("Query did not return created primary key");
77 }
78
79 out.println("Generated id is = " + generatedKeys.getLong(1));
80
81 return generatedKeys.getLong(1);
82 }
83
84 } catch (SQLException ex) {
85 throw new IllegalStateException("Could not execute query", ex);
86
87 } catch (NullPointerException ex2) {
88 out.println("wywala tutaj na null");
89 ex2.printStackTrace();
90 } catch (Exception bla) {
91 out.println("jakis wyjatek");
92 }
93 return 0;
94 }
95
96
97 /*tryLoginUser return true when parameters exists in data base
98 return false when parameters not exists in database */
99 @Override
100 public boolean tryLoginUser(String mail, String password) {
101 try (PreparedStatement preparedStatement = connection.prepareStatement(loginQuery)) {
102 try (ResultSet resultSet = preparedStatement.executeQuery()) {
103
104 if (resultSet.next())
105 if (resultSet.getString(1).equals(mail) && resultSet.getString(2).equals(password))
106 return true;
107 }
108
109 } catch (SQLException e) {
110 e.printStackTrace();
111 }
112 return false;
113 }
114
115 @Override
116 public boolean checkRegistration(User user) throws SQLException {
117 String QueryReg = "select * from USERS where MAIL = ? or NAME = ?;";
118 try (PreparedStatement preparedStatement = connection.prepareStatement(QueryReg)) {
119 preparedStatement.setString(1, user.getEmail());
120 preparedStatement.setString(2, user.getName());
121 ResultSet resultSet = preparedStatement.executeQuery();
122
123 if (!resultSet.next())
124 return false;
125 }
126
127 return true;
128
129 }
130
131 @Override
132 public List<Movie> movieList() throws SQLException {
133 List<Movie> list = new ArrayList<>();
134 String queryMovieList = "select * from movies";
135 try (PreparedStatement preparedStatement = connection.prepareStatement(queryMovieList)) {
136 ResultSet resultSet = preparedStatement.executeQuery();
137
138 while(resultSet.next()) {
139 Movie movie = new Movie (
140 resultSet.getString("TITLE"),
141 resultSet.getString("TYPE"),
142 resultSet.getInt("LENGHT"),
143 resultSet.getString("DESCRIPTION"));
144 list.add(movie);
145 }}
146 return list;
147 }
148
149 @Override
150 public boolean addMovie(Movie movie) {
151
152 String queryAddMovie = "insert into movies (TITLE, TYPE, LENGHT, DESCRIPTION, AUTHOR) values (?,?,?,?,?) ";
153 try {
154 PreparedStatement preparedStatement = connection.prepareStatement(queryAddMovie);
155 preparedStatement.setString(1, movie.getTitle());
156 preparedStatement.setString(2, movie.getType());
157 preparedStatement.setLong(3, movie.getLenght());
158 preparedStatement.setString(4, movie.getDescription());
159 preparedStatement.setString(5, movie.getAuthor()
160 );
161 int number = preparedStatement.executeUpdate();
162 if (number < 1)
163 return false;
164 if (number == 1) {
165 return true;
166
167
168 }
169
170
171 } catch (SQLException e) {
172 e.printStackTrace();
173 }
174 return false;
175 }
176}