📌src/db/JdbcUtil.java
package db;
import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class JdbcUtil {
public static Connection getConnection(){
Connection con=null;
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/testDB";
try {
Class.forName(driver);
con=DriverManager.getConnection(url,"아이디","비밀번호");
con.setAutoCommit(false);
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
public static void close(Connection con){
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(Statement stmt){
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs){
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void commit(Connection con){
try {
con.commit();
System.out.println("commit success");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void rollback(Connection con){
try {
con.rollback();
System.out.println("rollback success");
} catch (Exception e) {
e.printStackTrace();
}
}
}
📌DB로 SQL 구문을 전송하는 클래스
boardDAO 클래스
package dao;
import static db.JdbcUtil.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import vo.BoardBean;
public class BoardDAO {
Connection con;
private static BoardDAO boardDAO; //외부 클래스에서 접근하지 못하도록 private로 지정
private BoardDAO() { //외부 클래스에서 생성자를 사용해서 객체 새로 생성할 없도록
//private로 지정함
}
//싱글톤 패턴
public static BoardDAO getInstance(){
if(boardDAO == null){
boardDAO = new BoardDAO();
}
return boardDAO;
}
//con 연결
public void setConnection(Connection con){
this.con = con;
}
...
}위 코드를 보면 알 수 있듯이 싱글톤 패턴으로 BoardDAO 객체를 생성해서 리턴해주도록 만들었다.
싱글톤 패턴을 포함하여 다른 디자인 패턴을 알고 싶다면 다음 링크를 참고해보면 좋다.
https://yeo-computerclass.tistory.com/91
[디자인 패턴] 싱글톤(Singleton) 패턴
Singleton patter이란 전역 변수를 사용하지 않고 객체를 하나만 생성 하도록 하며, 생성된 객체를 어디에서든지 참조할 수 있도록 하는 패턴이다. 방법은 어플리케이션이 시작될 때 어떤 클래스가
yeo-computerclass.tistory.com
게시판 게시글 개수 구하는 메소드
//게시판 전체 글의 개수 구하기.
public int selectListCount() {
int listCount= 0;
PreparedStatement pstmt = null;
ResultSet rs = null;
String query="select count(*) from board";
try{
pstmt=con.prepareStatement(query);
rs = pstmt.executeQuery();
if(rs.next()){
listCount=rs.getInt(1);
}
}catch(Exception ex){
System.out.println("getListCount 에러: " + ex);
}finally{
close(rs);
close(pstmt);
}
return listCount;
}
게시글 목록 List 추출 메소드
//게시글 목록 보기.
public ArrayList<BoardBean> selectBoardList(int page,int limit){
PreparedStatement pstmt = null;
ResultSet rs = null;
String listQuery="select * from board order by BOARD_RE_REF desc,BOARD_RE_SEQ asc limit ?,10";
ArrayList<BoardBean> boardList = new ArrayList<BoardBean>();
BoardBean board = null;
int startrow=(page-1)*10; //각 page 첫 번째 목록 글 번호
try{
pstmt = con.prepareStatement(listQuery);
pstmt.setInt(1, startrow);
rs = pstmt.executeQuery();
while(rs.next()){
board = new BoardBean();
board.setBOARD_NUM(rs.getInt("BOARD_NUM"));
board.setBOARD_NAME(rs.getString("BOARD_NAME"));
board.setBOARD_SUBJECT(rs.getString("BOARD_SUBJECT"));
board.setBOARD_CONTENT(rs.getString("BOARD_CONTENT"));
board.setBOARD_FILE(rs.getString("BOARD_FILE"));
board.setBOARD_RE_REF(rs.getInt("BOARD_RE_REF"));
board.setBOARD_RE_LEV(rs.getInt("BOARD_RE_LEV"));
board.setBOARD_RE_SEQ(rs.getInt("BOARD_RE_SEQ"));
board.setBOARD_READCOUNT(rs.getInt("BOARD_READCOUNT"));
board.setBOARD_DATE(rs.getDate("BOARD_DATE"));
boardList.add(board);
}
}catch(Exception ex){
System.out.println("getBoardList error : " + ex);
}finally{
close(rs);
close(pstmt);
}
return boardList;
}listQuery 쿼리는 각 페이지에서 변수로 주어지는 limit 만큼 만 글 목록이 나오게 하는 query이다. SQL 구문이 이해가 잘 되지 않는다면 다음 링크를 참고
https://yeo-computerclass.tistory.com/192?category=1221897
[SQL]개수 제한하여 출력(selelct)하게 해주는 limit
SELECT * FROM member; member 테이블의 레코드 수가 몇 천 개 있다고 가정해보자. 이 때,위 쿼리를 실행하면 member 테이블에서 모든 레코드가 SELECT될 것이다. 만약 우리가 모든 레코드가 아닌 50개 정도만
yeo-computerclass.tistory.com
게시글 상세 보기
//게시글 상세 보기.
public BoardBean selectBoard(int board_num){
PreparedStatement pstmt = null;
ResultSet rs = null;
BoardBean boardBean = null;
String query = "select * from board where BOARD_NUM = ?";
try{
pstmt = con.prepareStatement(query);
pstmt.setInt(1, board_num);
rs= pstmt.executeQuery();
if(rs.next()){
boardBean = new BoardBean();
boardBean.setBOARD_NUM(rs.getInt("BOARD_NUM"));
boardBean.setBOARD_NAME(rs.getString("BOARD_NAME"));
boardBean.setBOARD_SUBJECT(rs.getString("BOARD_SUBJECT"));
boardBean.setBOARD_CONTENT(rs.getString("BOARD_CONTENT"));
boardBean.setBOARD_FILE(rs.getString("BOARD_FILE"));
boardBean.setBOARD_RE_REF(rs.getInt("BOARD_RE_REF"));
boardBean.setBOARD_RE_LEV(rs.getInt("BOARD_RE_LEV"));
boardBean.setBOARD_RE_SEQ(rs.getInt("BOARD_RE_SEQ"));
boardBean.setBOARD_READCOUNT(rs.getInt("BOARD_READCOUNT"));
boardBean.setBOARD_DATE(rs.getDate("BOARD_DATE"));
}
}catch(Exception ex){
System.out.println("getDetail error : " + ex);
}finally{
close(rs);
close(pstmt);
}
return boardBean;
}
게시글 등록
//게시글 등록.
public int insertBoard(BoardBean board){
PreparedStatement pstmt = null;
ResultSet rs = null;
int num =0;
String query="";
int insertCount=0;
try{
//새로 글이 등록될 게시글 번호
pstmt=con.prepareStatement("select max(board_num) from board");
rs = pstmt.executeQuery();
if(rs.next())
num =rs.getInt(1)+1;
else
num=1;
query="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,"
+ "BOARD_CONTENT, BOARD_FILE, BOARD_RE_REF,"
+ "BOARD_RE_LEV,BOARD_RE_SEQ,BOARD_READCOUNT,"
+ "BOARD_DATE) values(?,?,?,?,?,?,?,?,?,?,now())";
pstmt = con.prepareStatement(query);
pstmt.setInt(1, num);
pstmt.setString(2, board.getBOARD_NAME());
pstmt.setString(3, board.getBOARD_PASS());
pstmt.setString(4, board.getBOARD_SUBJECT());
pstmt.setString(5, board.getBOARD_CONTENT());
pstmt.setString(6, board.getBOARD_FILE());
pstmt.setInt(7, num);
pstmt.setInt(8, 0);
pstmt.setInt(9, 0);
pstmt.setInt(10, 0);
insertCount=pstmt.executeUpdate();
}catch(Exception ex){
System.out.println("boardInsert 에러 : "+ex);
}finally{
close(rs);
close(pstmt);
}
return insertCount;
}새로 글이 등록될 때 BOARD_NUM과 BOARD_RE_REF에 같은 번호(num)를 부여함으로 원글과 그 글의 답변글이 하나로 묶인다.
게시글 답변
//게시글 답변
public int insertReplyBoard(BoardBean board){
PreparedStatement pstmt = null;
ResultSet rs = null;
String maxBoardNumQuery="select max(board_num) from board";
String query="";
int num=0;
int insertCount=0;
int re_ref=board.getBOARD_RE_REF();
int re_lev=board.getBOARD_RE_LEV();
int re_seq=board.getBOARD_RE_SEQ();
try{
pstmt=con.prepareStatement(maxBoardNumQuery);
rs = pstmt.executeQuery();
if(rs.next())num =rs.getInt(1)+1;
else num=1;
//답변글의 번호가 입력되는 부분
query="update board set BOARD_RE_SEQ=BOARD_RE_SEQ+1 "
+ "where BOARD_RE_REF=? and BOARD_RE_SEQ>?";
pstmt = con.prepareStatement(query);
pstmt.setInt(1,re_ref);
pstmt.setInt(2,re_seq);
int updateCount=pstmt.executeUpdate();
if(updateCount > 0){
commit(con);
}
re_seq = re_seq + 1;
re_lev = re_lev + 1;
query="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,"
+ "BOARD_CONTENT, BOARD_FILE,BOARD_RE_REF,BOARD_RE_LEV,BOARD_RE_SEQ,"
+ "BOARD_READCOUNT,BOARD_DATE) values(?,?,?,?,?,?,?,?,?,?,now())";
pstmt = con.prepareStatement(query);
pstmt.setInt(1, num);
pstmt.setString(2, board.getBOARD_NAME());
pstmt.setString(3, board.getBOARD_PASS());
pstmt.setString(4, board.getBOARD_SUBJECT());
pstmt.setString(5, board.getBOARD_CONTENT());
pstmt.setString(6, ""); //답변에는 파일 업로드 기능 없음.
pstmt.setInt(7, re_ref);
pstmt.setInt(8, re_lev);
pstmt.setInt(9, re_seq);
pstmt.setInt(10, 0);
insertCount = pstmt.executeUpdate();
}catch(SQLException ex){
System.out.println("boardReply error : "+ex);
}finally{
close(rs);
close(pstmt);
}
return insertCount;
}| BOARD_RE_REF | BOARD_RE_SEQ | |
| 1.원글 | 1 | 0 |
| 4.답글3 | 1 | 1 (0+1) |
| 3.답글2 | 1 | 2 (1+1) |
| 2.답글1 | 1 | 3 (2+1) |
이 처럼 답변을 달고자 하는 글과 같은 글 그룹에 속하고, 답변이 달릴 때 마다 출력순서(BOARD_RE_SEQ)를 하나씩 증가시키는 SQL 구문이다.
게시글 수정
//게시글 수정
public int updateBoard(BoardBean board){
int updateCount = 0;
PreparedStatement pstmt = null;
String query="update board set BOARD_SUBJECT=?,BOARD_CONTENT=? where BOARD_NUM=?";
try{
pstmt = con.prepareStatement(query);
pstmt.setString(1, board.getBOARD_SUBJECT());
pstmt.setString(2, board.getBOARD_CONTENT());
pstmt.setInt(3, board.getBOARD_NUM());
updateCount = pstmt.executeUpdate();
}catch(Exception ex){
System.out.println("boardModify 에러 : " + ex);
}finally{
close(pstmt);
}
return updateCount;
}
게시글 삭제
//게시글 삭제
public int deleteBoard(int board_num){
PreparedStatement pstmt = null;
String deleteQuery="delete from board where BOARD_num=?";
int deleteCount=0;
try{
pstmt=con.prepareStatement(deleteQuery);
pstmt.setInt(1, board_num);
deleteCount=pstmt.executeUpdate();
}catch(Exception ex){
System.out.println("boardDelete 에러 : "+ex);
} finally{
close(pstmt);
}
return deleteCount;
}
조회수 증가
//조회수 업데이트.
public int updateReadCount(int board_num){
PreparedStatement pstmt = null;
int updateCount = 0;
String sql="update board set BOARD_READCOUNT = "
+ "BOARD_READCOUNT+1 where BOARD_NUM = "+ board_num;
try{
pstmt=con.prepareStatement(sql);
updateCount = pstmt.executeUpdate();
}catch(SQLException ex){
System.out.println("setReadCountUpdate 에러 : "+ex);
}
finally{
close(pstmt);
}
return updateCount;
}
작성자인지 확인
//글쓴이인지 확인.
public boolean isBoardWriter(int board_num,String pass){
PreparedStatement pstmt = null;
ResultSet rs = null;
String boardQuery="select * from board where BOARD_NUM=?";
boolean isWriter = false;
try{
pstmt=con.prepareStatement(boardQuery);
pstmt.setInt(1, board_num);
rs=pstmt.executeQuery();
rs.next();
if(pass.equals(rs.getString("BOARD_PASS"))){
isWriter = true;
}
}catch(SQLException ex){
System.out.println("isBoardWriter 에러 : "+ex);
}
finally{
close(pstmt);
}
return isWriter;
}