JDBC

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import model.Person;

public class UserDb {
    private Connection connection;
    private PreparedStatement statement;
    private Properties properties;

    public UserDb(Properties properties) {
        try {
            Class.forName("org.postgresql.Driver");
            setProperties(properties);
        } catch (Exception e) {
            throw new DbException(e.getMessage(), e);
        }
    }

    public void delete(String email) {
        if (email == null) {
            throw new DbException("Nothing to delete.");
        }
        String sql = "DELETE FROM web3.person WHERE email=?";
        try {
            initializeStatement(sql);
            statement.setString(1, email);
            statement.execute();
        } catch (SQLException e) {
            throw new DbException(e);
        } finally {
            closeConnection();
        }
    }

    public void update(Person person) {
        if (person == null) {
            throw new DbException("Nothing to update.");
        }
        String sql = "UPDATE web3.person SET password=?, name=? WHERE email=?";
        try {
            initializeStatement(sql);
            statement.setString(1, person.getPassword());
            statement.setString(2, person.getName());
            statement.setString(3, person.getEmail());
            statement.execute();
        } catch (SQLException e) {
            throw new DbException(e);
        } finally {
            closeConnection();
        }
    }

    public void add(Person person) {
        if (person == null) {
            throw new DbException("Nothing to add.");
        }
        String sql = "INSERT INTO web3.person (name, email, password) VALUES (?, ?, ?)";
        try {
            initializeStatement(sql);
            statement.setString(1, person.getName());
            statement.setString(2, person.getEmail());
            statement.setString(3, person.getPassword());
            statement.execute();
        } catch (SQLException e) {
            throw new DbException(e);
        } finally {
            closeConnection();
        }
    }

    public Person get(String email) {
        Person person = null;
        try {
            String sql = "SELECT * FROM web3.person WHERE email=?";
            initializeStatement(sql);
            statement.setString(1, email);
            ResultSet result = statement.executeQuery();
            while (result.next()) {
                String name = result.getString("name");
                String password = result.getString("password");
                person = new Person(name, email, password);
            }
        } catch (SQLException e) {
            throw new DbException(e.getMessage(), e);
        } finally {
            closeConnection();
        }
        return person;
    }

    public List<Person> getAll() {
        List<Person> persons = new ArrayList<Person>();
        try {
            String sql = "SELECT * FROM web3.person";
            initializeStatement(sql);
            ResultSet result = statement.executeQuery();
            while (result.next()) {
                String name = result.getString("name");
                String email = result.getString("email");
                String password = result.getString("password");
                Person person = new Person(name, email, password);
                persons.add(person);
            }
        } catch (SQLException e) {
            throw new DbException(e.getMessage(), e);
        } finally {
            closeConnection();
        }
        return persons;
    }

    private void initializeStatement(String sql) throws SQLException {
        String url = getProperties().getProperty("url");
        connection = DriverManager.getConnection(url, getProperties());
        statement = connection.prepareStatement(sql);
    }

    private void closeConnection() {
        try {
            statement.close();
            connection.close();
        } catch (SQLException e) {
            throw new DbException(e.getMessage(), e);
        }
    }

    private Properties getProperties() {
        return properties;
    }

    private void setProperties(Properties properties) {
        if(properties == null){
            throw new DbException("No properties given");
        }
        this.properties = properties;
    }
}