JavaBeans

If a Java beginner, it is enough to understand JavaBeans as the Java classes you create for your project.

User

• SignUpFailException.java
• User.java
• UserDao.java
• UserService.java

SignUpFailException.java

package net.java_school.exception;

public class SignUpFailException extends RuntimeException {

  public SignUpFailException() {
    super();
  }

  public SignUpFailException(String message, Throwable cause,
       boolean enableSuppression, boolean writableStackTrace) {
    super(message, cause, enableSuppression, writableStackTrace);
  }

  public SignUpFailException(String message, Throwable cause) {
    super(message, cause);
  }

  public SignUpFailException(String message) {
    super(message);
  }

  public SignUpFailException(Throwable cause) {
    super(cause);
  }
    
}

Let's use Apache's commons-logging and log4j 2 for logging. See: Log4j 2

Get log4j-api-2.x.x.x.jar and log4-core-2.x.x.x.jar from http://logging.apache.org/log4j/2.x/download.html.

Get commons-logging-1.2.jar from http://apache.mirror.cdnetworks.com/commons/logging/binaries/.

Put these files into WEB-INF/lib.

Create the following commons-logging.properties and log4j2.xml in the src folder.

commons-logging.properties

org.apache.commons.logging.Log = org.apache.commons.logging.impl.Log4JLogger

log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration>
  <Appenders>
    <File name="A1" fileName="A1.log" append="true">
      <PatternLayout pattern="%t %-5p %c{2} - %m%n" />
    </File>
    <Console name="STDOUT" target="SYSTEM_OUT">
      <PatternLayout pattern="%d %-5p [%t] %C{2} (%F:%L) - %m%n" />
    </Console>
  </Appenders>
  <Loggers>
    <Logger name="net.java_school" level="debug">
      <AppenderRef ref="A1" />
    </Logger>
    <Root level="debug">
      <AppenderRef ref="STDOUT" />
    </Root>
  </Loggers>
</Configuration>

UserDao.java

package net.java_school.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import net.java_school.exception.SignUpFailException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class UserDao {
  private Log log = LogFactory.getLog(UserDao.class);
  private static UserDao instance = new UserDao();
  private DataSource ds;
  public static String NEW_LINE = System.getProperty("line.separator");

  public static UserDao getInstance() {
    return instance;
  }
    
  private UserDao() {
    try {
      Context initCtx = new InitialContext();
      Context envCtx = (Context) initCtx.lookup("java:comp/env");
      ds = (DataSource) envCtx.lookup("jdbc/jsppjt");
    } catch (NamingException e) {
      if (log.isDebugEnabled()) {
        log.debug(e.getMessage());
      }
    }
  }
    
  private Connection getConnection() throws SQLException {
    return ds.getConnection();
  }
    
  private void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if (pstmt != null) {
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if (con != null) {
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
    
  //Sign Up
  public void insert(User user) {
    Connection con = null;
    PreparedStatement pstmt = null;
        
    //email,passwd,name,mobile
    String sql = "insert into member values (?, ?, ?, ?)";
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, user.getEmail());
      pstmt.setString(2, user.getPasswd());
      pstmt.setString(3, user.getName());
      pstmt.setString(4, user.getMobile());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
      throw new SignUpFailException("Sign Up Failed!");
    } finally {
      close(null, pstmt, con);
    }
  }
    
  //Login
  public User login(String email, String passwd) {
    User user = null;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "SELECT email, passwd, name, mobile FROM member "
        + "WHERE email = ? AND passwd = ?";
         
         
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, email);
      pstmt.setString(2, passwd);
      rs = pstmt.executeQuery();
             
      if (rs.next()) {
        user = new User();
        user.setEmail(rs.getString("email"));
        user.setPasswd(rs.getString("passwd"));
        user.setName(rs.getString("name"));
        user.setMobile(rs.getString("mobile"));
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }
         
    return user;
  }
    
  //Modify account
  public int update(User user) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "UPDATE member SET name = ?, mobile = ? WHERE email = ? AND passwd = ?";
    int ret = 0;
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, user.getName());
      pstmt.setString(2, user.getMobile());
      pstmt.setString(3, user.getEmail());
      pstmt.setString(4, user.getPasswd());
      ret = pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
      throw new RuntimeException(e);
    } finally {
      close(null, pstmt, con);
    }
        
    return ret;
  }
    
  //Change password
  public int update(String currentPasswd, String newPasswd, String email) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "UPDATE member SET passwd = ? WHERE passwd = ? AND email = ?";
    int ret = 0;
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, newPasswd);
      pstmt.setString(2, currentPasswd);
      pstmt.setString(3, email);
      ret = pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(null, pstmt, con);
    }
        
    return ret;
  }
    
  //Bye
  public int delete(String email, String passwd) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "DELETE member WHERE email = ? AND passwd = ?";
    int ret = 0;
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, email);
      pstmt.setString(2, passwd);
      ret = pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
      throw new RuntimeException(e);
    } finally {
      close(null, pstmt, con);
    }
        
    return ret;
  }
    
  //Search User by email
  public User selectOne(String email) {
    User user = null;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "SELECT email, passwd, name, mobile "
            + "FROM member WHERE email = ?";
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, email);
      rs = pstmt.executeQuery();
            
      if (rs.next()) {
        user = new User();
        user.setEmail(rs.getString("email"));
        user.setPasswd(rs.getString("passwd"));
        user.setName(rs.getString("name"));
        user.setMobile(rs.getString("mobile"));
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }
         
    return user;
  }
        
}

UserService.java

package net.java_school.user;

public class UserService {
  private UserDao dao = UserDao.getInstance();
    
  public void addUser(User user) {
    dao.insert(user);
  }
    
  public User login(String email, String passwd) {
    return dao.login(email, passwd);
  }
    
  public int editAccount(User user) {
    return dao.update(user);
  }
    
  public int changePasswd(String currentPasswd, String newPasswd, String email) {
    return dao.update(currentPasswd, newPasswd, email);
  }
    
  public void bye(String email, String passwd) {
    dao.delete(email, passwd);
  }

  public User getUser(String email) {
    return dao.selectOne(email);
  }
        
}

Board

• Board.java
• Article.java
• Comment.java
• AttachFile.java
• BoardDao.java
• BoardService.java
• NumbersForPaging.java

Board.java

package net.java_school.board;

public class Board {
  private String boardCd;
  private String boardNm;
  private String boardNm_ko;
    
  public String getBoardCd() {
    return boardCd;
  }
  public void setBoardCd(String boardCd) {
    this.boardCd = boardCd;
  }
  public String getBoardNm() {
    return boardNm;
  }
  public void setBoardNm(String boardNm) {
    this.boardNm = boardNm;
  }
  public String getBoardNm_ko() {
    return boardNm_ko;
  }
  public void setBoardNm_ko(String boardNm_ko) {
    this.boardNm_ko = boardNm_ko;
  }
    
}

Article.java

package net.java_school.board;

import java.util.Date;

public class Article {
  private int articleNo;
  private String boardCd;
  private String title;
  private String content;
  private String email;
  private String name;
  private int hit;
  private Date regdate;
  private int attachFileNum;
  private int commentNum;
    
  public int getArticleNo() {
    return articleNo;
  }
  public void setArticleNo(int articleNo) {
    this.articleNo = articleNo;
  }
  public String getBoardCd() {
    return boardCd;
  }
  public void setBoardCd(String boardCd) {
    this.boardCd = boardCd;
  }
  public String getTitle() {
    return title;
  }
  public void setTitle(String title) {
    this.title = title;
  }
  public String getContent() {
    return content;
  }
  public void setContent(String content) {
    this.content = content;
  }
  public String getEmail() {
    return email;
  }
  public void setEmail(String email) {
    this.email = email;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  public int getHit() {
    return hit;
  }
  public void setHit(int hit) {
    this.hit = hit;
  }
  public Date getRegdate() {
    return regdate;
  }
  public void setRegdate(Date regdate) {
    this.regdate = regdate;
  }
  public int getAttachFileNum() {
    return attachFileNum;
  }
  public void setAttachFileNum(int attachFileNum) {
    this.attachFileNum = attachFileNum;
  }
  public int getCommentNum() {
    return commentNum;
  }
  public void setCommentNum(int commentNum) {
    this.commentNum = commentNum;
  }
    
}

Comment.java

package net.java_school.board;

import java.util.Date;

public class Comment {
  private int commentNo;
  private int articleNo;
  private String email;
  private String name;
  private String memo;
  private Date regdate;
    
  public int getCommentNo() {
    return commentNo;
  }
  public void setCommentNo(int commentNo) {
    this.commentNo = commentNo;
  }
  public int getArticleNo() {
    return articleNo;
  }
  public void setArticleNo(int articleNo) {
    this.articleNo = articleNo;
  }
  public String getEmail() {
    return email;
  }
  public void setEmail(String email) {
    this.email = email;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }   
  public String getMemo() {
    return memo;
  }
  public void setMemo(String memo) {
    this.memo = memo;
  }
  public Date getRegdate() {
    return regdate;
  }
  public void setRegdate(Date regdate) {
    this.regdate = regdate;
  }
    
}

AttachFile.java

package net.java_school.board;

public class AttachFile {
  private int attachFileNo;
  private String filename;    
  private String filetype;    
  private long filesize;    
  private int articleNo;
  private String email;
    
  public int getAttachFileNo() {
    return attachFileNo;
  }
  public void setAttachFileNo(int attachFileNo) {
    this.attachFileNo = attachFileNo;
  }
  public String getFilename() {
    return filename;
  }
  public void setFilename(String filename) {
    this.filename = filename;
  }
  public String getFiletype() {
    return filetype;
  }
  public void setFiletype(String filetype) {
    this.filetype = filetype;
  }
  public long getFilesize() {
    return filesize;
  }
  public void setFilesize(long filesize) {
    this.filesize = filesize;
  }
  public int getArticleNo() {
    return articleNo;
  }
  public void setArticleNo(int articleNo) {
    this.articleNo = articleNo;
  }
  public String getEmail() {
    return email;
  }
  public void setEmail(String email) {
    this.email = email;
  }
    
}

BoardDao.java

package net.java_school.board;
package net.java_school.board;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class BoardDao {
  private Log log = LogFactory.getLog(BoardDao.class);
  private static BoardDao instance = new BoardDao();
  private DataSource ds;
  public static String NEW_LINE = System.getProperty("line.separator");
    
  public static BoardDao getInstance() {
    return instance;
  }
    
  private BoardDao() {
    try {
      Context initCtx = new InitialContext();
      Context envCtx = (Context) initCtx.lookup("java:comp/env");
      ds = (DataSource) envCtx.lookup("jdbc/jsppjt");
    } catch (NamingException e) {
      if (log.isDebugEnabled()) {
        log.debug(e.getMessage());
      }
    }
  }
    
  private Connection getConnection() throws SQLException {
    return ds.getConnection();
  }
    
  private void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if (pstmt != null) {
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if (con != null) {
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
    
  //List
  public List<Article> selectListOfArticles(
      String boardCd, 
      String searchWord, 
      int startRownum, 
      int endRownum) {
        
    List<Article> articleList = new ArrayList<Article>();
        
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT articleno, title, regdate, hit, attachfileNum, commentNum FROM ");
    sb.append("( ");
    sb.append("     SELECT rownum R, A.* FROM ");
    sb.append("     ( ");
    sb.append("     SELECT ");
    sb.append("         a.articleno,"); 
    sb.append("         a.title,");
    sb.append("         a.regdate,");
    sb.append("         a.hit,");
    sb.append("         COUNT(DISTINCT(f.attachfileno)) attachfileNum,");
    sb.append("         COUNT(DISTINCT(c.commentno)) commentNum ");
    sb.append("     FROM article a LEFT JOIN attachfile f ON a.articleno = f.articleno ");
    sb.append("          LEFT JOIN comments c ON a.articleno = c.articleno ");
    sb.append("     WHERE ");
    sb.append("         a.boardcd = ? ");
    if (searchWord != null && !searchWord.equals("")) { 
      sb.append("         AND (title LIKE '%" + searchWord + "%' OR DBMS_LOB.INSTR(content, '" + searchWord + "') > 0) ");
    }
    sb.append("     GROUP BY a.articleno, a.title, a.regdate, a.hit ORDER BY articleno DESC ");
    sb.append("     ) A ");
    sb.append(") ");
    sb.append("WHERE R BETWEEN ? AND ?");
        
    final String sql = sb.toString();
      
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, boardCd);
      pstmt.setInt(2, startRownum);
      pstmt.setInt(3, endRownum);
      rs = pstmt.executeQuery();
            
      while(rs.next()) {
        Article article = new Article();
        int articleNo = rs.getInt("articleno");
        String title = rs.getString("title");
        Date regdate = rs.getDate("regdate");
        int hit = rs.getInt("hit");
        int attachfileNum = rs.getInt("attachfileNum");
        int commentNum = rs.getInt("commentNum");
        article.setArticleNo(articleNo);
        article.setTitle(title);
        article.setRegdate(regdate);
        article.setHit(hit);
        article.setAttachFileNum(attachfileNum);
        article.setCommentNum(commentNum);
        articleList.add(article);
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }

    return articleList;
  }
    
  //Total count
  public int selectCountOfArticles(String boardCd, String searchWord) {
    int totalRecord = 0;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
        
    String sql = "SELECT count(*) FROM article WHERE boardcd=? ";
    if (searchWord != null && !searchWord.equals("")) {
      sql = sql + "AND (title LIKE '%" + searchWord + "%' OR DBMS_LOB.INSTR(content, '" + searchWord + "') > 0) ";
    } 
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, boardCd);
      rs = pstmt.executeQuery();
      rs.next();
      totalRecord = rs.getInt(1);
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }
        
    return totalRecord;
  }

  //Insert new board article
  public void insert(Article article, AttachFile attachFile) {
    Connection con = null;
    PreparedStatement pstmt = null;
        
    String sql = "INSERT INTO article " +
    "(articleno, boardcd, title, content, email, hit, regdate) " +
    "VALUES " +
    "(SEQ_ARTICLE.nextval, ?, ?, ?, ?, 0, sysdate)";
        
    int chk = 0;
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, article.getBoardCd());
      pstmt.setString(2, article.getTitle());
      pstmt.setString(3, article.getContent());
      pstmt.setString(4, article.getEmail());
      chk = pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
        
    try {
      if (chk > 0 && attachFile != null) {
        sql = "INSERT INTO attachfile " +
        "(attachfileno, filename, filetype, filesize, articleno, email) " +
        "VALUES " +
        "(SEQ_ATTACHFILE.nextval, ?, ?, ?, SEQ_ARTICLE.currval, ?)";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, attachFile.getFilename());
        pstmt.setString(2, attachFile.getFiletype());
        pstmt.setLong(3, attachFile.getFilesize());
        pstmt.setString(4, attachFile.getEmail());
        pstmt.executeUpdate();
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(null, pstmt, con);
    }
        
  }
    
  //Edit a board article
  public void update(Article article, AttachFile attachFile) {
    String sql = "UPDATE article SET title=?, content=? WHERE articleno=?";
        
    Connection con = null;
    PreparedStatement pstmt = null;
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, article.getTitle());
      pstmt.setString(2, article.getContent());
      pstmt.setInt(3, article.getArticleNo());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    
    try {
      if (attachFile != null) {
        sql = "INSERT INTO attachfile "
                + "(attachfileno, filename, filetype, filesize, articleno, email) "
                + "VALUES (SEQ_ATTACHFILE.nextval, ?, ?, ?, ?, ?)";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, attachFile.getFilename());
        pstmt.setString(2, attachFile.getFiletype());
        pstmt.setLong(3, attachFile.getFilesize());
        pstmt.setInt(4, attachFile.getArticleNo());
        pstmt.setString(5, attachFile.getEmail());
        pstmt.executeUpdate();
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(null, pstmt, con);
    }

  }
    
  //Delete a board article
  public void delete(int articleNo) {
    Connection con = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt2 = null;
    PreparedStatement pstmt3 = null;
        
    String sql = "DELETE FROM comments WHERE articleno = ?";
        
    try {
      con = getConnection();
      con.setAutoCommit(false);
      pstmt1 = con.prepareStatement(sql);
      pstmt1.setInt(1, articleNo);
      pstmt1.executeUpdate();
            
      sql = "DELETE FROM attachfile WHERE articleno = ?";
      pstmt2 = con.prepareStatement(sql);
      pstmt2.setInt(1, articleNo);
      pstmt2.executeUpdate();
            
      sql = "DELETE FROM article WHERE articleno = ?";
      pstmt3 = con.prepareStatement(sql);
      pstmt3.setInt(1, articleNo);
      pstmt3.executeUpdate();
      con.commit();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
            
      try {
        con.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
      }
    } finally {
      if (pstmt1 != null) {
        try {
          pstmt1.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (pstmt2 != null) {
        try {
          pstmt2.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      close(null, pstmt3, con);
    }   
        
  }
    
  //Increase the read count
  public void updateHitPlusOne(int articleNo) {
    Connection con = null;
    PreparedStatement pstmt = null;
    String sql = "UPDATE article SET hit = hit + 1 WHERE articleno = ?";
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, articleNo);
      pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState");
        msg.append(" : ");
        msg.append(e.getSQLState());
        msg.append(NEW_LINE);
        msg.append("Message");
        msg.append(" : ");
        msg.append(e.getMessage());
        msg.append(NEW_LINE);
        msg.append("Oracle Error Code");
        msg.append(" : ");
        msg.append(e.getErrorCode());
        msg.append(NEW_LINE);
        msg.append("sql");
        msg.append(" : ");
        msg.append(sql);
        msg.append(NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(null, pstmt, con);
    }
  }
    
  //Fetch board article by articleNo
  public Article selectOne(int articleNo) {
    Article article = null;
    final String sql = "SELECT "
              + "articleno, title, content, a.email, NVL(name,'Anonymous') name, hit, regdate "
              + "FROM "
              + "article a LEFT JOIN member m ON a.email = m.email "
              + "WHERE "
              + "articleno = ?";
             
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
         
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, articleNo);
      rs = pstmt.executeQuery();
             
      if (rs.next()) {
        article = new Article();
        article.setArticleNo(rs.getInt("articleno"));
        article.setTitle(rs.getString("title"));
        article.setContent(rs.getString("content"));
        article.setEmail(rs.getString("email"));
        article.setName(rs.getString("name"));
        article.setHit(rs.getInt("hit"));
        article.setRegdate(rs.getDate("regdate"));
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }
    return article;
    
  }
    
  //Next Article
  public Article selectNextOne(int articleNo, String boardCd, String searchWord) {
    Article article = null;
    String sql = "SELECT articleno, title " +
        "FROM " +
            "(SELECT rownum r,a.* " +
            "FROM " +
            "(SELECT articleno, title "
            + "FROM article "
            + "WHERE boardCd = ? AND articleno > ? ";
             
    if (searchWord != null && !searchWord.equals("")) {
      sql = sql + "AND (title LIKE '%" + searchWord + "%' OR DBMS_LOB.INSTR(content, '" + searchWord + "') > 0) ";
    } 
             
    sql = sql + "ORDER BY articleno) a) WHERE r = 1";
         
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
             
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, boardCd);
      pstmt.setInt(2, articleNo);
      rs = pstmt.executeQuery();

      if (rs.next()) {
        article = new Article();
        article.setArticleNo(rs.getInt("articleno"));
        article.setTitle(rs.getString("title"));
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }
         
    return article;

  }
    
  //Prev Article
  public Article selectPrevOne(int articleNo, String boardCd, String searchWord) {
    Article article = null;
    String sql = "SELECT articleno, title " +
        "FROM " +
        "(SELECT rownum r,a.* " +
            "FROM " +
            "(SELECT articleno, title "
            + "FROM article "
            + "WHERE boardCd = ? AND articleno < ? ";
         
    if (searchWord != null && !searchWord.equals("")) {
      sql = sql + "AND (title LIKE '%" + searchWord + "%' OR DBMS_LOB.INSTR(content, '" + searchWord + "') > 0) ";
    } 
         
    sql = sql + "ORDER BY articleno DESC) a) WHERE r = 1";
         
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
             
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, boardCd);
      pstmt.setInt(2, articleNo);
      rs = pstmt.executeQuery();
             
      if (rs.next()) {
        article = new Article();
        article.setArticleNo(rs.getInt("articleno"));
        article.setTitle(rs.getString("title"));
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }
         
    return article;

  }
    
  //List of AttachFiles
  public List<AttachFile> selectListOfAttachFiles(int articleNo) {
    List<AttachFile> attachFileList = new ArrayList<AttachFile>();
    String sql = "SELECT attachfileno, filename, filetype, filesize, articleno, email " +
      "FROM attachfile WHERE articleno=? ORDER BY attachfileno ASC";
     
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
         
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, articleNo);
      rs = pstmt.executeQuery();
             
      while(rs.next()) {
        AttachFile attachFile = new AttachFile();
        int attachFileNo = rs.getInt("attachfileno");
        String filename = rs.getString("filename");
        String filetype = rs.getString("filetype");
        long filesize = rs.getLong("filesize");
        String email = rs.getString("email");
        attachFile.setAttachFileNo(attachFileNo);
        attachFile.setFilename(filename);
        attachFile.setFiletype(filetype);
        attachFile.setFilesize(filesize);
        attachFile.setEmail(email);
        attachFileList.add(attachFile);
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);  
    }
         
    return attachFileList;

  }
    
  //Delete the attach file.
  public void deleteFile(int attachFileNo) {
    String sql = "DELETE FROM attachfile WHERE attachfileno=?";
         
    Connection con = null;
    PreparedStatement pstmt = null;
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, attachFileNo);
      pstmt.executeQuery();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(null, pstmt, con);    
    }

  }
    
  //Fetch board name by boardcode
  public String selectOneBoardName(String boardCd) {
    String boardNm_ko = null;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "SELECT boardnm_ko FROM board WHERE boardcd = ?";
        
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, boardCd);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        boardNm_ko = rs.getString("boardnm_ko");
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }
            
    return boardNm_ko;
    
  }
    
  //Add comment
  public void insertComment(Comment comment) {
    String sql = "INSERT INTO comments (commentno, articleno, email, memo, regdate) "
      + "VALUES (SEQ_COMMENTS.nextval, ?, ?, ?, sysdate)";
                 
    Connection con = null;
    PreparedStatement pstmt = null;
             
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, comment.getArticleNo());
      pstmt.setString(2, comment.getEmail());
      pstmt.setString(3, comment.getMemo());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(null, pstmt, con);
    }
        
  }
    
  //Edit the comment
  public void updateComment(Comment comment) {
    String sql = "UPDATE comments SET memo = ? WHERE commentno = ?";
    Connection con = null;
    PreparedStatement pstmt = null;
         
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, comment.getMemo());
      pstmt.setInt(2, comment.getCommentNo());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(null, pstmt, con);
    }
  }
    
  //Delete the comment
  public void deleteComment(int commentNo) {
    String sql = "DELETE FROM comments WHERE commentno = ?";
         
    Connection con = null;
    PreparedStatement pstmt = null;
         
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, commentNo);
      pstmt.executeUpdate();
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(null, pstmt, con);
    }
    
  }
    
  //List of comments
  public List<Comment> selectListOfComments(int articleNo) {
    List<Comment> commentList = new ArrayList<Comment>();

    String sql = "SELECT "
                  + "commentno, articleno, c.email, NVL(name,'Anonymous') name, memo, regdate "
              + "FROM "
                  + "comments c LEFT JOIN member m ON c.email = m.email "
              + "WHERE "
                  + "articleno = ? "
              + "ORDER BY commentno DESC"; 
     
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
         
    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, articleNo);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        Comment comment = new Comment();
                 
        comment.setCommentNo(rs.getInt("commentno"));
        comment.setArticleNo(rs.getInt("articleno"));
        comment.setEmail(rs.getString("email"));
        comment.setName(rs.getString("name"));
        comment.setMemo(rs.getString("memo"));
        comment.setRegdate(rs.getTimestamp("regdate"));
                
        commentList.add(comment);
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(null, pstmt, con);
    }
        
    return commentList;
  }

  //Fetch the attach file by attachFileNo  
  public AttachFile selectOneAttachFile(int attachFileNo) {
    AttachFile attachFile = null;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    //attachfileno,filename,filetype,filesize,articleno,email
    String sql = "SELECT attachfileno, filename, filetype, filesize, articleno, email "
          + "FROM attachFile WHERE attachfileno = ?";

    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, attachFileNo);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        attachFile = new AttachFile();
        attachFile.setAttachFileNo(rs.getInt("attachfileno"));
        attachFile.setFilename(rs.getString("filename"));
        attachFile.setFiletype(rs.getString("filetype"));
        attachFile.setFilesize(rs.getLong("filesize"));
        attachFile.setArticleNo(rs.getInt("articleno"));
        attachFile.setEmail(rs.getString("email"));
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }
        
    return attachFile;
  }

  //Fetch the comment by commentNo
  public Comment selectOneComment(int commentNo) {
    Comment comment = null;
        
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
        
    //commentno,articleno,email,memo,regdate
    String sql = "SELECT commentno, articleno, email, memo, regdate "
          + "FROM comments WHERE commentno = ?";

    try {
      con = getConnection();
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, commentNo);
      rs = pstmt.executeQuery();
            
      if (rs.next()) {
        comment = new Comment();
        comment.setCommentNo(rs.getInt("commentno"));
        comment.setArticleNo(rs.getInt("articleno"));
        comment.setEmail(rs.getString("email"));
        comment.setMemo(rs.getString("memo"));
        comment.setRegdate(rs.getDate("regdate"));
      }
    } catch (SQLException e) {
      if (log.isDebugEnabled()) {
        StringBuilder msg = new StringBuilder();
        msg.append("SQLState : " + e.getSQLState() + NEW_LINE);
        msg.append("Message : " + e.getMessage() + NEW_LINE);
        msg.append("Oracle Error Code : " + e.getErrorCode() + NEW_LINE);
        msg.append("sql : " + sql + NEW_LINE);
        log.debug(msg);
      }
    } finally {
      close(rs, pstmt, con);
    }
        
    return comment;
  }

}

NumbersForPaging.java

package net.java_school.commons;

public class NumbersForPaging {
  private int totalPage;
  private int firstPage;
  private int lastPage;
  private int prevBlock;
  private int nextBlock;
  private int listItemNo;
    
  public int getTotalPage() {
    return totalPage;
  }
  public void setTotalPage(int totalPage) {
    this.totalPage = totalPage;
  }
  public int getFirstPage() {
    return firstPage;
  }
  public void setFirstPage(int firstPage) {
    this.firstPage = firstPage;
  }
  public int getLastPage() {
    return lastPage;
  }
  public void setLastPage(int lastPage) {
    this.lastPage = lastPage;
  }
  public int getPrevBlock() {
    return prevBlock;
  }
  public void setPrevBlock(int prevBlock) {
    this.prevBlock = prevBlock;
  }
  public int getNextBlock() {
    return nextBlock;
  }
  public void setNextBlock(int nextBlock) {
    this.nextBlock = nextBlock;
  }
  public int getListItemNo() {
    return listItemNo;
  }
  public void setListItemNo(int listItemNo) {
    this.listItemNo = listItemNo;
  }
    
}

BoardService.java

package net.java_school.board;

import java.util.List;

import net.java_school.commons.NumbersForPaging;

public class BoardService {
  private BoardDao dao = BoardDao.getInstance();
    
  public List<Article> getArticleList(String boardCd, String searchWord, int startRecord, int endRecord) {
    return dao.selectListOfArticles(boardCd, searchWord, startRecord, endRecord);
  }
    
  public int getTotalRecord(String boardCd, String searchWord) {
    return dao.selectCountOfArticles(boardCd, searchWord);
  }

  public void addArticle(Article article, AttachFile attachFile) {
    dao.insert(article, attachFile);
  }

  public void modifyArticle(Article article, AttachFile attachFile) {
    dao.update(article, attachFile);
  }

  public void removeArticle(int articleNo) {
    dao.delete(articleNo);
  }
    
  public void increaseHit(int articleNo) {
    dao.updateHitPlusOne(articleNo);
  }
    
  public Article getArticle(int articleNo) {
    return dao.selectOne(articleNo);
  }
    
  public Article getNextArticle(int articleNo, String boardCd, String searchWord) {
    return dao.selectNextOne(articleNo, boardCd, searchWord);
  }

  public Article getPrevArticle(int articleNo, String boardCd, String searchWord) {
    return dao.selectPrevOne(articleNo, boardCd, searchWord);
  }

  public List<AttachFile> getAttachFileList(int articleNo) {
    return dao.selectListOfAttachFiles(articleNo);
  }

  public AttachFile getAttachFile(int attachFileNo) {
    return dao.selectOneAttachFile(attachFileNo);
  }
    
  public void removeAttachFile(int attachFileNo) {
    dao.deleteFile(attachFileNo);
  }
    
  public String getBoardName(String boardCd) {
    return dao.selectOneBoardName(boardCd);
  }

  public List<Comment> getCommentList(int articleNo) {
    return dao.selectListOfComments(articleNo);
  }

  public Comment getComment(int commentNo) {
    return dao.selectOneComment(commentNo);
  }

  public void addComment(Comment comment) {
    dao.insertComment(comment);
  }

  public void modifyComment(Comment comment) {
    dao.updateComment(comment);
  }
    
  public void removeComment(int commentNo) {
    dao.deleteComment(commentNo);
  }
    
  public NumbersForPaging getNumbersForPaging(int totalRecord, int page, int numPerPage, int pagePerBlock) {
    int totalPage = totalRecord / numPerPage;
    if (totalRecord % numPerPage != 0) totalPage++;
    int totalBlock = totalPage / pagePerBlock;
    if (totalPage % pagePerBlock != 0) totalBlock++;
    int block = page / pagePerBlock;
    if (page % pagePerBlock != 0) block++;
    int firstPage = (block - 1) * pagePerBlock + 1;
    int lastPage = block * pagePerBlock;
    int prevPage = 0;
    if (block > 1) {
      prevPage = firstPage - 1;
    }
    int nextPage = 0;
    if (block < totalBlock) {
      nextPage = lastPage + 1;
    }
    if (block >= totalBlock) {
      lastPage = totalPage;
    }
    int listItemNo = totalRecord - (page - 1) * numPerPage;
        
    NumbersForPaging numbers = new NumbersForPaging();
       
    numbers.setTotalPage(totalPage);
    numbers.setFirstPage(firstPage);
    numbers.setLastPage(lastPage);
    numbers.setPrevBlock(prevPage);
    numbers.setNextBlock(nextPage);
    numbers.setListItemNo(listItemNo);
        
    return numbers;
  }    
}