Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- public class TestDBSTORE {
- @Test
- public void whenFindAll() {
- DBStore store = new DBStore();
- assertThat(store.findAll(), is(store.findById(1)));
- }
- @Test
- public void whenIsCredential() {
- DBStore store = new DBStore();
- assertThat(store.isCredential("root", "root"), is(true));
- }}
- public class User {
- private int id;
- private String name;
- private String login;
- private String email;
- private Timestamp createDat;
- private String password;
- private int role;
- public User(int id, String name, String login, String email, String createDate, String password, int role) {
- this.id = id;
- this.name = name;
- this.login = login;
- this.email = email;
- this.createDat = Timestamp.valueOf(createDate.replace("T", " ").replace("Z", ""));
- this.password = password;
- this.role = role;
- }
- }
- public class DBStore implements Store, AutoCloseable {
- private static final Logger LOG = LogManager.getLogger(DBStore.class.getName());
- private static final BasicDataSource SOURCE = new BasicDataSource();
- private static final DBStore INSTANCE = new DBStore();
- public DBStore() {
- SOURCE.setDriverClassName("org.postgresql.Driver");
- SOURCE.setUrl("jdbc:postgresql://127.0.0.1:5432/postgres");
- SOURCE.setUsername("postgres");
- SOURCE.setPassword("password");
- SOURCE.setMinIdle(5);
- SOURCE.setMaxIdle(10);
- SOURCE.setMaxOpenPreparedStatements(100);
- createTable();
- if (isEmpty()) {
- addRootUser();
- }
- }
- public static DBStore getInstance() {
- return INSTANCE;
- }
- private void addRootUser() {
- try (Connection connection = SOURCE.getConnection();
- PreparedStatement ps = connection.prepareStatement("insert into clients (name, login, email, createDate, password, role) values (?, ?, ?, ?, ?, ?)");
- ) {
- ps.setString(1, "root");
- ps.setString(2, "root");
- ps.setString(3, "root");
- ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
- ps.setString(5, "root");
- ps.setInt(6, 0);
- ps.executeQuery();
- } catch (SQLException e) {
- LOG.error(e.getMessage(), e);
- }
- }
- private boolean isEmpty() {
- int count = 0;
- boolean result = false;
- try (Connection connection = SOURCE.getConnection();
- PreparedStatement ps = connection.prepareStatement("Select count(*) clients");
- ) {
- ResultSet rs = ps.executeQuery();
- while (rs.next()) {
- count = rs.getInt(1);
- if (count == 0) {
- result = true;
- break;
- }
- }
- } catch (SQLException e) {
- LOG.error(e.getMessage(), e);
- }
- return result;
- }
- private void createTable() {
- try (Connection connection = SOURCE.getConnection()) {
- final PreparedStatement ps = connection.prepareStatement(
- "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)"
- );
- ps.execute();
- } catch (SQLException e) {
- LOG.error(e.getMessage(), e);
- }
- }
- @Override
- public boolean add(User user) {
- boolean result = false;
- try (Connection connection = SOURCE.getConnection();
- PreparedStatement ps = connection.prepareStatement("insert into clients (name, login, email, createDate, password, role) values (?, ?, ?, ?, ?, ?)");
- ) {
- ps.setString(1, user.getName());
- ps.setString(2, user.getLogin());
- ps.setString(3, user.getEmail());
- ps.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
- ps.setString(5, user.getPassword());
- ps.setInt(6, user.getRole());
- result = ps.execute();
- } catch (SQLException e) {
- LOG.error(e.getMessage(), e);
- }
- return result;
- }
- @Override
- public boolean update(int id, User user) {
- boolean result = false;
- try (Connection connection = SOURCE.getConnection();
- PreparedStatement ps = connection.prepareStatement("update clients set name = ?, login = ?, email = ?, password = ?, role = ?, createDate = ? where id = ?");
- ) {
- ps.setString(1, user.getName());
- ps.setString(2, user.getLogin());
- ps.setString(3, user.getEmail());
- ps.setString(4, user.getPassword());
- ps.setInt(5, user.getRole());
- ps.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
- ps.setInt(7, id);
- ps.executeUpdate();
- result = true;
- } catch (SQLException e) {
- LOG.error(e.getMessage(), e);
- }
- return result;
- }
- @Override
- public boolean delete(int id) {
- boolean result = false;
- try (Connection connection = SOURCE.getConnection();
- PreparedStatement ps = connection.prepareStatement("delete from clients where id = ?");
- ) {
- ps.setInt(1, id);
- ps.executeUpdate();
- result = true;
- } catch (SQLException e) {
- LOG.error(e.getMessage(), e);
- }
- return result;
- }
- @Override
- public CopyOnWriteArrayList<User> findAll() {
- CopyOnWriteArrayList<User> list = null;
- try (Connection connection = SOURCE.getConnection();
- PreparedStatement ps = connection.prepareStatement("select * from clients");
- ) {
- ResultSet rs = ps.executeQuery();
- while (rs.next()) {
- list.add(this.getByResultSet(rs));
- }
- } catch (SQLException e) {
- LOG.error(e.getMessage(), e);
- }
- return list;
- }
- private User getByResultSet(ResultSet rs) throws SQLException {
- return new User(
- rs.getInt("id"),
- rs.getString("name"),
- rs.getString("login"),
- rs.getString("email"),
- rs.getString("createdate"),
- rs.getString("password"),
- rs.getInt("role")
- );
- }
- @Override
- public User findById(int id) {
- User user = null;
- try (Connection connection = SOURCE.getConnection();
- PreparedStatement ps = connection.prepareStatement("select * from clients where id = ?");
- ) {
- ps.setInt(1, id);
- ResultSet rs = ps.executeQuery();
- while (rs.next()) {
- user = getByResultSet(rs);
- }
- } catch (SQLException e) {
- LOG.error(e.getMessage(), e);
- }
- return user;
- }
- public int role(String login, String password) {
- int result = -1;
- try (Connection connection = SOURCE.getConnection();
- PreparedStatement ps = connection.prepareStatement("select * from clients where login = ?");
- ) {
- ps.setString(1, login);
- ResultSet rs = ps.executeQuery();
- while (rs.next()) {
- result = (int) rs.getInt("role");
- break;
- }
- } catch (SQLException e) {
- LOG.error(e.getMessage(), e);
- }
- return result;
- }
- public boolean isCredential(String login, String password) {
- boolean result = false;
- for (User user: findAll()) {
- if (user.getLogin().equals(login) && user.getID().equals(password)) {
- result = true;
- break;
- }
- }
- return result;
- }
- @Override
- public void close() throws Exception {
- SOURCE.close();
- }
Add Comment
Please, Sign In to add comment