· 7 years ago · Jan 16, 2019, 03:18 PM
1public class TestDBSTORE {
2
3@Test
4public void whenFindAll() {
5 DBStore store = new DBStore();
6 assertThat(store.findAll(), is(store.findById(1)));
7}
8
9@Test
10public void whenIsCredential() {
11 DBStore store = new DBStore();
12 assertThat(store.isCredential("root", "root"), is(true));
13}}
14
15
16public class User {
17private int id;
18private String name;
19private String login;
20private String email;
21private Timestamp createDat;
22private String password;
23private int role;
24public User(int id, String name, String login, String email, String createDate, String password, int role) {
25 this.id = id;
26 this.name = name;
27 this.login = login;
28 this.email = email;
29 this.createDat = Timestamp.valueOf(createDate.replace("T", " ").replace("Z", ""));
30 this.password = password;
31 this.role = role;
32}
33}
34
35
36
37public class DBStore implements Store, AutoCloseable {
38private static final Logger LOG = LogManager.getLogger(DBStore.class.getName());
39private static final BasicDataSource SOURCE = new BasicDataSource();
40private static final DBStore INSTANCE = new DBStore();
41
42public DBStore() {
43 SOURCE.setDriverClassName("org.postgresql.Driver");
44 SOURCE.setUrl("jdbc:postgresql://127.0.0.1:5432/postgres");
45 SOURCE.setUsername("postgres");
46 SOURCE.setPassword("password");
47 SOURCE.setMinIdle(5);
48 SOURCE.setMaxIdle(10);
49 SOURCE.setMaxOpenPreparedStatements(100);
50 createTable();
51 if (isEmpty()) {
52 addRootUser();
53 }
54}
55
56public static DBStore getInstance() {
57 return INSTANCE;
58}
59
60private void addRootUser() {
61 try (Connection connection = SOURCE.getConnection();
62 PreparedStatement ps = connection.prepareStatement("insert into clients (name, login, email, createDate, password, role) values (?, ?, ?, ?, ?, ?)");
63 ) {
64 ps.setString(1, "root");
65 ps.setString(2, "root");
66 ps.setString(3, "root");
67 ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
68 ps.setString(5, "root");
69 ps.setInt(6, 0);
70 ps.executeQuery();
71 } catch (SQLException e) {
72 LOG.error(e.getMessage(), e);
73 }
74}
75
76private boolean isEmpty() {
77 int count = 0;
78 boolean result = false;
79 try (Connection connection = SOURCE.getConnection();
80 PreparedStatement ps = connection.prepareStatement("Select count(*) clients");
81 ) {
82 ResultSet rs = ps.executeQuery();
83 while (rs.next()) {
84 count = rs.getInt(1);
85 if (count == 0) {
86 result = true;
87 break;
88 }
89 }
90 } catch (SQLException e) {
91 LOG.error(e.getMessage(), e);
92 }
93 return result;
94}
95
96
97private void createTable() {
98 try (Connection connection = SOURCE.getConnection()) {
99 final PreparedStatement ps = connection.prepareStatement(
100 "create table if not exists clients(id serial primary key, name character(2000), login character(2000), email character(2000), createDate timestamp, password character(2000), role integer)"
101 );
102 ps.execute();
103 } catch (SQLException e) {
104 LOG.error(e.getMessage(), e);
105 }
106}
107
108@Override
109public boolean add(User user) {
110 boolean result = false;
111 try (Connection connection = SOURCE.getConnection();
112 PreparedStatement ps = connection.prepareStatement("insert into clients (name, login, email, createDate, password, role) values (?, ?, ?, ?, ?, ?)");
113 ) {
114 ps.setString(1, user.getName());
115 ps.setString(2, user.getLogin());
116 ps.setString(3, user.getEmail());
117 ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
118 ps.setString(5, user.getPassword());
119 ps.setInt(6, user.getRole());
120 result = ps.execute();
121 } catch (SQLException e) {
122 LOG.error(e.getMessage(), e);
123 }
124 return result;
125}
126
127@Override
128public boolean update(int id, User user) {
129 boolean result = false;
130 try (Connection connection = SOURCE.getConnection();
131 PreparedStatement ps = connection.prepareStatement("update clients set name = ?, login = ?, email = ?, password = ?, role = ?, createDate = ? where id = ?");
132 ) {
133 ps.setString(1, user.getName());
134 ps.setString(2, user.getLogin());
135 ps.setString(3, user.getEmail());
136 ps.setString(4, user.getPassword());
137 ps.setInt(5, user.getRole());
138 ps.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
139 ps.setInt(7, id);
140 ps.executeUpdate();
141 result = true;
142 } catch (SQLException e) {
143 LOG.error(e.getMessage(), e);
144 }
145 return result;
146}
147
148@Override
149public boolean delete(int id) {
150 boolean result = false;
151 try (Connection connection = SOURCE.getConnection();
152 PreparedStatement ps = connection.prepareStatement("delete from clients where id = ?");
153 ) {
154 ps.setInt(1, id);
155 ps.executeUpdate();
156 result = true;
157 } catch (SQLException e) {
158 LOG.error(e.getMessage(), e);
159 }
160 return result;
161}
162
163@Override
164public CopyOnWriteArrayList<User> findAll() {
165 CopyOnWriteArrayList<User> list = null;
166 try (Connection connection = SOURCE.getConnection();
167 PreparedStatement ps = connection.prepareStatement("select * from clients");
168 ) {
169 ResultSet rs = ps.executeQuery();
170 while (rs.next()) {
171 list.add(this.getByResultSet(rs));
172 }
173 } catch (SQLException e) {
174 LOG.error(e.getMessage(), e);
175 }
176 return list;
177}
178
179private User getByResultSet(ResultSet rs) throws SQLException {
180 return new User(
181 rs.getInt("id"),
182 rs.getString("name"),
183 rs.getString("login"),
184 rs.getString("email"),
185 rs.getString("createdate"),
186 rs.getString("password"),
187 rs.getInt("role")
188 );
189}
190
191@Override
192public User findById(int id) {
193 User user = null;
194 try (Connection connection = SOURCE.getConnection();
195 PreparedStatement ps = connection.prepareStatement("select * from clients where id = ?");
196 ) {
197 ps.setInt(1, id);
198 ResultSet rs = ps.executeQuery();
199 while (rs.next()) {
200 user = getByResultSet(rs);
201 }
202 } catch (SQLException e) {
203 LOG.error(e.getMessage(), e);
204 }
205 return user;
206}
207
208public int role(String login, String password) {
209 int result = -1;
210 try (Connection connection = SOURCE.getConnection();
211 PreparedStatement ps = connection.prepareStatement("select * from clients where login = ?");
212 ) {
213 ps.setString(1, login);
214 ResultSet rs = ps.executeQuery();
215 while (rs.next()) {
216 result = (int) rs.getInt("role");
217 break;
218 }
219 } catch (SQLException e) {
220 LOG.error(e.getMessage(), e);
221 }
222 return result;
223}
224
225public boolean isCredential(String login, String password) {
226 boolean result = false;
227 for (User user: findAll()) {
228 if (user.getLogin().equals(login) && user.getID().equals(password)) {
229 result = true;
230 break;
231 }
232 }
233 return result;
234}
235
236@Override
237public void close() throws Exception {
238 SOURCE.close();
239}