Raw JDBC

날 것 그대로의 JDBC 프로그래밍을 통해서 동작원리를 이해하고 데이터베이스에 데이터를 추가/수정/삭제, 한 건 가져오기, 목록 가져오기를 수행하는 것을 목표로 한다.

User

import lombok.Data;

@Data
public class User {
    private int userId;
    private String email;
    private String password;
    private String name;
}

lombok을 사용하여 정의

UserDao

public interface UserDao {
    List<User> listUsers(int offset, int count);

    void addUser(User user);

    User login(String email, String password);

    User getUser(int userId);


    int updateEmail(int userId, String email);

    int updatePassword(int userId, String oldPassword, String newPassword);
}

interface로 정의하여 이를 재정의함.

UserDaoImpRawJDBC

public class UserDaoImpUsingRawJDBC 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 DataSource dataSource;

    public UserDaoImpUsingRawJDBC() {
        dataSource = new MariaDbDataSource(DB_URL);
    }

    @Override
    public List<User> listUsers(int offset, int count) throws DataAccessException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(LIST_USERS)) {
            ps.setInt(1, offset);
            ps.setInt(2, count);
            ResultSet rs = ps.executeQuery();
            List<User> userList = new ArrayList<>();
            while (rs.next()) {
                User user = new User();
                user.setUserId(rs.getInt("userId"));
                user.setEmail(rs.getString("email"));
                user.setName(rs.getString("name"));
                userList.add(user);
            }
            rs.close();
            return userList;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    @Override
    public void addUser(User user) throws DataAccessException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(ADD_USER)) {
            ps.setString(1, user.getEmail());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getName());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }

    }

    @Override
    public User login(String email, String password) throws DataAccessException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(LOGIN)) {
            ps.setString(1, email);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            User user = null;
            if (rs.next()) {
                user = new User();
                user.setUserId(rs.getInt("userId"));
                user.setEmail(rs.getString("email"));
                user.setName(rs.getString("name"));
            }
            rs.close();
            return user;
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    @Override
    public User getUser(int userId) throws DataAccessException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(GET_USER)) {
            ps.setInt(1, userId);
            ResultSet rs = ps.executeQuery();
            User user = null;
            if (rs.next()) {
                user = new User();
                user.setUserId(rs.getInt("userId"));
                user.setEmail(rs.getString("email"));
                user.setName(rs.getString("name"));
            }
            rs.close();
            return user;

        } catch (SQLException e) {
            throw new DataAccessException(e);
        }

    }

    @Override
    public int updateEmail(int userId, String email) throws DataAccessException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(UPDATE_EMAIL)) {
            ps.setString(1, email);
            ps.setInt(2, userId);
            return ps.executeUpdate();

        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
    }

    @Override
    public int updatePassword(int userId, String oldPassword, String newPassword) throws DataAccessException {
        try (Connection con = dataSource.getConnection();
             PreparedStatement ps = con.prepareStatement(UPDATE_PASSWORD)) {
            ps.setString(1, newPassword);
            ps.setInt(2, userId);
            ps.setString(3, oldPassword);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }

    }
}

Raw JDBC는 datasource를 통해서 데이터베이스에 연결, Preparedstatement에 실행할 쿼리문의 인자를 세팅한 후 실행, 결과값이 있을 경우 executeQuery()로 ResultSet에 저장하고 출력, 쿼리를 실행하는 경우 executeUpdate()로 쿼리를 실행하는 것이다.