public List<Message> selectList(Connection conn, int firstRow, int endRow) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt =
conn.prepareStatement(
"select message_id, guest_name, password, message from ( "
+ " select rownum rnum, message_id, guest_name, password, message from ( "
+ " select * from guestbook_message m order by m.message_id desc "
+ " ) where rownum <= ? "
+ ") where rnum >= ?");
pstmt.setInt(1, endRow);
pstmt.setInt(2, firstRow);
rs = pstmt.executeQuery();
if (rs.next()) {
List<Message> messageList = new ArrayList<Message>();
do {
messageList.add(super.makeMessageFromResultSet(rs));
} while (rs.next());
return messageList;
} else {
return Collections.emptyList();
}
} finally {
JdbcUtil.close(rs);
JdbcUtil.close(pstmt);
}
}
public void deleteMessage(int messageId, String password)
throws ServiceException, InvalidMessagePasswordException, MessageNotFoundException {
Connection conn = null;
try {
conn = ConnectionProvider.getConnection();
conn.setAutoCommit(false);
MessageDao messageDao = MessageDaoProvider.getInstance().getMessageDao();
Message message = messageDao.select(conn, messageId);
if (message == null) {
throw new MessageNotFoundException("메시지가 없습니다:" + messageId);
}
if (!message.hasPassword()) {
throw new InvalidMessagePasswordException("메시지가 암호를 가지고 있지 않습니다");
}
if (!message.getPassword().equals(password)) {
throw new InvalidMessagePasswordException("메시지 암호가 다릅니다.");
}
messageDao.delete(conn, messageId);
conn.commit();
} catch (SQLException ex) {
JdbcUtil.rollback(conn);
throw new ServiceException("삭제 처리 중 에러가 발생했습니다.:" + ex.getMessage(), ex);
} catch (InvalidMessagePasswordException ex) {
JdbcUtil.rollback(conn);
throw ex;
} catch (MessageNotFoundException ex) {
JdbcUtil.rollback(conn);
throw ex;
} finally {
if (conn != null) {
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
}
JdbcUtil.close(conn);
}
}
}
public int insert(Connection conn, Message message) throws SQLException {
PreparedStatement pstmt = null;
try {
pstmt =
conn.prepareStatement(
"insert into guestbook_message "
+ "(message_id, guest_name, password, message) "
+ "values (message_id_seq.NEXTVAL, ?, ?, ?)");
pstmt.setString(1, message.getGuestName());
pstmt.setString(2, message.getPassword());
pstmt.setString(3, message.getMessage());
return pstmt.executeUpdate();
} finally {
JdbcUtil.close(pstmt);
}
}