JDBC Helper

JDBC의 동작

1.데이터베이스에 연결
2.실행할 쿼리문을 세팅해서 preparedstatement를 실행
3.목록, 한 건 가져오기일 경우 resultSet에 저장
4.resultSet 출력

DbUtils

package kr.sukbin.java.jdbc.user;

import kr.sukbin.java.jdbc.DataAccessException;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DbUtils {

    DataSource dataSource;

    public DbUtils(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    //추가,수정,삭제를 실행하는 helper 메서드
    public int update(String sql, Object... params) throws DataAccessException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            setParams(ps, params);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    //목록가져오기
    public <T> List<T> list(String sql, ResultSetHandler<T> h, Object... params)
            throws DataAccessException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            setParams(ps, params);
            ResultSet rs = ps.executeQuery();
            List<T> list = new ArrayList<>();
            while (rs.next()) {
                list.add(h.handle(rs));
            }
            return list;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    //한 건 가져오기
    public <T> T get(String sql, ResultSetHandler<T> h, Object... params) throws DataAccessException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            setParams(ps, params);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                T t = h.handle(rs);
                return t;
            }
            return null;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    //preparedStatement에 parameter를 넣는다.
    private void setParams(PreparedStatement ps, Object[] params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; i++)
                ps.setObject(i + 1, params[i]);
        }
    }
}

ResultSetHandler

//ResultSet을 처리하는 functional interface
@FunctionalInterface
public interface ResultSetHandler<T> {
    T handle(ResultSet rs) throws SQLException;
}

UserDaoImpUsingDbUtils

public class UserDaoImpIUsingDbUtils implements UserDao {
    private static final String DB_URL =
            "jdbc:mariadb://localhost:3306/sukbin?user=starboy&password=191014";
    private static final String LIST_USERS =
            "select userId, email, name from user order by userId desc limit ?, ?";
    private static final String ADD_USER =
            "insert user(email, password, name) values(?, sha2(?, 256), ?)";
    private static final String LOGIN =
            "select userId, email, name from user where (email, password) = (?, sha2(?, 256))";
    private static final String GET_USER =
            "select userId, email, name from user where userId = ?";
    private static final String UPDATE_EMAIL =
            "update user set email = ? where userId = ?";
    private static final String UPDATE_PASSWORD =
            "update user set password = sha2(?, 256) where userId = ? and password = sha2(?, 256)";
    private DbUtils dbUtils;

    public UserDaoImpIUsingDbUtils() {
        DataSource dataSource = new MariaDbDataSource(DB_URL);
        dbUtils = new DbUtils(dataSource);
    }

    ResultSetHandler<User> h = (rs) -> {
        User user = new User();
        user.setUserId(rs.getInt("userId"));
        user.setEmail(rs.getString("email"));
        user.setName(rs.getString("name"));
        return user;
    };

    @Override
    public List<User> listUsers(int offset, int count) {
        return dbUtils.list(LIST_USERS, h, offset, count);
    }

    @Override
    public void addUser(User user) {

    }

    @Override
    public User login(String email, String password) {
        return dbUtils.get(LOGIN, h, email, password);
    }

    @Override
    public User getUser(int userId) {
        return dbUtils.get(GET_USER, h, userId);
    }

    @Override
    public int updateEmail(int userId, String email) {
        return 0;
    }

    @Override
    public int updatePassword(int userId, String oldPassword, String newPassword) {
        return 0;
    }
}