자바빈즈
자바 초보자는 자바빈즈를 프로젝트를 위해 만들어야 하는 자바 클래스라 이해하면 충분하다.
회원
다음은 화원과 관련된 자바 빈즈 목록이다.
- 회원 가입 실패시 발생시킬 사용자 정의 예외: 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);
}
}
로깅을 위해, Log4j 2와 아파치의 commons-logging를 사용하겠다.
이에 대한 자세한 설명은 다음 링크에서 참조할 수 있다:
Log4j 2
http://logging.apache.org/log4j/2.x/download.html에서 파일을 내려받아 압축을 풀어 log4j-api-2.x.x.x.jar와 log4-core-2.x.x.x.jar 파일을 얻는다. http://apache.mirror.cdnetworks.com/commons/logging/binaries/에서 commons-logging-1.2.jar 파일을 얻는다. 이들 파일을 WEB-INF/lib 폴더에 복사한다.
다음 commons-logging.properties와 log4j2.xml를 src 폴더에 생성한다.
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();
}
}
}
//회원 가입
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("회원 가입 실패");
} finally {
close(null, pstmt, con);
}
}
//로그인
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;
}
//회원정보 수정
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;
}
//비밀번호 변경
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;
}
//탈퇴
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;
}
//이메일로 회원 찾기
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.java
- 게시글: Article.java
- 댓글: Comment.java
- 첨부 파일: AttachFile.java
- 게시판 퍼시스턴스 계층: BoardDao.java
- 게시판 서비스 계층: BoardService.java
- 페이징 처리를 위한 숫자: NumbersForPaing.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; //글 소유자 ID에 해당하는 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;//댓글 작성자 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();
}
}
}
//특정 페이지에 해당하는 게시글 리스트
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;
}
//총게시글수
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;
}
//게시글 삽입
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);
}
}
//게시글 수정
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);
}
}
//게시글 삭제
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);
}
}
//게시글 조회 수 증가
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);
}
}
//P.K 에 해당하는 게시글 조회
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;
}
//다음 글
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;
}
//이전 글
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;
}
//첨부 파일 리스트
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;
}
//첨부 파일 삭제
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);
}
}
//게시판 코드로 게시판 이름 조회
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;
}
//댓글 삽입
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);
}
}
//댓글 수정
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);
}
}
//댓글 삭제
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);
}
}
//댓글 리스트
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;
}
//첨부 파일 찾기
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;
}
//댓글 찾기
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(); //게시판 Dao
//목록
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;
}
}