C22_dao_board_re

• 댓글 알고리즘 

---

• ref => 글 그룹 (내림차순)
• step => 글 깊이 => 화살표 개수
• level => 같은 그룹내의 정렬 순서(오름차순)

 

• 새글 알고리즘 
  • ref => 마지막 ref +1, step +1 , level = 1 
• 댓글 알고리즘 
  • ref => 부모 ref 
  • step => 부모 step + 1 
  • level => 부모 level + 1 
  • 추가 조건이 발동, 같은 ref 에서 현재 글보다. level 크거나 같으면 같은 글드은 +1을 한다. 

---

• db . 디비 - ref ,step, level 
  • a -  1   1   1
  • b -  2   1   1
  • c -  3   1   1
  • --------------
  • b1-   2   2   2 + 1+1 +1
  • b2-   2   2   2 +1 (추가조건발동)
  • b21- 2   3   3 + 1(추가조건발동)
  • b3-   2   2   2 (추가조건발동)

---

• 뷰 . 화면 . ref ,step, level 
  • c 
  • b
  • =>b3 
  • => b2
  • => => b21 
  • => b1 
  • a

---

•  댓글 알고리즘 

package board;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Random;

public class BoardDAO {
	
	public static BoardDAO instance = new BoardDAO();
	
	Connection conn;
	PreparedStatement pstmt;
	ResultSet rs;
	
	public Connection getConnection() throws Exception {
		String jdbcURL = "jdbc:mysql://localhost:3306/jsp_normal_board_re?serverTimezone=UTC&useSSL=false";
		String dbId = "root";
		String dbPw = "root";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		conn = DriverManager.getConnection(jdbcURL, dbId, dbPw);
		
		return conn;
	}
	
	public void insertBoard(String writer, String email, String subject, String pw, String content) {
		

		int ref = 1;
		
		try {
			conn = getConnection();
			
			String refSql = "SELECT MAX(board_ref) FROM board";
			pstmt = conn.prepareStatement(refSql);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				ref = rs.getInt(1) + 1;
			}
			
			
			
			String sql = " INSERT INTO board ";
					 sql  += " VALUES(0, ?, ?, ?, ?, ?, ?, 1, 1, 0, now()) ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, writer);
			pstmt.setString(2, email);
			pstmt.setString(3, subject);
			pstmt.setString(4, pw);
			pstmt.setString(5, content);
			pstmt.setInt(6, ref);
			
			pstmt.executeUpdate();
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) { try { conn.close(); } catch (Exception e) {} }
			if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
			if(rs != null) { try { rs.close(); } catch (Exception e) {} }
		}
	}
	
	public int getAllCount() {
			int count = 0;
			try {
				conn = getConnection();
				
				String sql = "SELECT COUNT(*) FROM board";
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			}catch(Exception e) {
				e.printStackTrace();
			}finally {
				if(conn != null) { try { conn.close(); } catch (Exception e) {} }
				if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
				if(rs != null) { try { rs.close(); } catch (Exception e) {} }
			}
		return count;
	}
	
	public ArrayList<Board> getBoardList(int start, int count){
		ArrayList<Board> boardList = new ArrayList<Board>();
		
		try {
			conn = getConnection();
			
			String sql = "SELECT * FROM board ORDER BY board_ref DESC, board_relevel LIMIT ?, ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, start);
			pstmt.setInt(2, count);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				Board board = new Board();
				
				board.setBoard_num(rs.getInt(1));
				board.setBoard_writer(rs.getString(2));
				board.setBoard_email(rs.getString(3));
				board.setBoard_subject(rs.getString(4));
				board.setBoard_pw(rs.getString(5));
				board.setBoard_content(rs.getString(6));
				board.setBoard_ref(rs.getInt(7));
				board.setBoard_restep(rs.getInt(8));
				board.setBoard_relevel(rs.getInt(9));
				board.setBoard_readcount(rs.getInt(10));
				board.setBoard_regdate(rs.getString(11));
				
				boardList.add(board);
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) { try { conn.close(); } catch (Exception e) {} }
			if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
			if(rs != null) { try { rs.close(); } catch (Exception e) {} }
		}
		
		return boardList;
	}
	
	public Board getOneBoard(int num) {
		Board board = new Board();
		
		try {
			conn = getConnection();
			
			String readSql = "UPDATE board SET board_readcount=board_readcount+1 WHERE board_num=?";
			pstmt = conn.prepareStatement(readSql);
			pstmt.setInt(1, num);
			pstmt.executeUpdate();
			
			String sql = "SELECT * FROM board WHERE board_num=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				board.setBoard_num(rs.getInt(1));
				board.setBoard_writer(rs.getString(2));
				board.setBoard_email(rs.getString(3));
				board.setBoard_subject(rs.getString(4));
				board.setBoard_pw(rs.getString(5));
				board.setBoard_content(rs.getString(6));
				board.setBoard_ref(rs.getInt(7));
				board.setBoard_restep(rs.getInt(8));
				board.setBoard_relevel(rs.getInt(9));
				board.setBoard_readcount(rs.getInt(10));
				board.setBoard_regdate(rs.getString(11));
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) { try { conn.close(); } catch (Exception e) {} }
			if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
			if(rs != null) { try { rs.close(); } catch (Exception e) {} }
		}
		
		return board;
	}
	
	public Board getOneUpdateBoard(int num) {
		Board board = new Board();
		
		try {
			conn = getConnection();
			
			String sql = "SELECT * FROM board WHERE board_num=?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				board.setBoard_num(rs.getInt(1));
				board.setBoard_writer(rs.getString(2));
				board.setBoard_email(rs.getString(3));
				board.setBoard_subject(rs.getString(4));
				board.setBoard_pw(rs.getString(5));
				board.setBoard_content(rs.getString(6));
				board.setBoard_ref(rs.getInt(7));
				board.setBoard_restep(rs.getInt(8));
				board.setBoard_relevel(rs.getInt(9));
				board.setBoard_readcount(rs.getInt(10));
				board.setBoard_regdate(rs.getString(11));
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) { try { conn.close(); } catch (Exception e) {} }
			if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
			if(rs != null) { try { rs.close(); } catch (Exception e) {} }
		}
		
		return board;
	}
	
	public String getPw(int num) {
		String pw = "";
		
		try {
			conn = getConnection();
			
			String sql = "SELECT board_pw FROM board WHERE board_num=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				pw = rs.getString(1);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) { try { conn.close(); } catch (Exception e) {} }
			if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
			if(rs != null) { try { rs.close(); } catch (Exception e) {} }
		}
		
		return pw;
	}
	
	public void updateBoard(int num, String subject, String content) {
		try {
			conn = getConnection();

			String sql = "UPDATE board SET board_subject=?, board_content=? WHERE board_num=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, subject);
			pstmt.setString(2, content);
			pstmt.setInt(3, num);

			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) { try { conn.close(); } catch (Exception e) {} }
			if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
		}			
	}
	
	public void deleteBoard(int num) {
		try {
			conn = getConnection();
			
			String sql = "DELETE FROM board WHERE board_num=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) { try { conn.close(); } catch (Exception e) {} }
			if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
		}
	}
	public void replyBoardWrite(String writer,String pw,String email,
			String subject,String  content, int ref, int reStep, int reLevel ) {

		try {
			conn = getConnection();

			String levelSql = "UPDATE board SET board_relevel = board_relevel+1 WHERE board_ref=? AND board_relevel > ? ";
			pstmt = conn.prepareStatement(levelSql);
			pstmt.setInt(1, ref);
			pstmt.setInt(2, reLevel);
			pstmt.executeUpdate();
			
			String sql = " INSERT INTO board "
					   + " VALUES(0, ?, ?, ?, ?, ?, ?, ?, ?, 0, now())";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, writer);
			pstmt.setString(2, email);
			pstmt.setString(3, subject);
			pstmt.setString(4, pw);
			pstmt.setString(5, content);
			pstmt.setInt(6, ref);
			pstmt.setInt(7, reStep + 1);
			pstmt.setInt(8, reLevel + 1);
			
			pstmt.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(conn != null) { try { conn.close(); } catch (Exception e) {} }
			if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
			if(rs != null) { try { rs.close(); } catch (Exception e) {} }
		}
	}
	
	public void addDummy(int count) {
		
		for(int i = 0; i < count; i++) {
			
			int ref = 1;
			
			try {
				conn = getConnection();
				
	
				String refSql = "SELECT MAX(board_ref) FROM board";
				pstmt = conn.prepareStatement(refSql);
				rs = pstmt.executeQuery();
				if(rs.next()) {
					ref = rs.getInt(1) + 1;
				}
				
				Random ran = new Random();
				String sampleAlpabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
				String writer = "";
				String email = "";
				String subject = "";
				String pw = "1234";
				String content = "";
				for(int j = 0; j < 5; j++) {
					int index = ran.nextInt(sampleAlpabet.length());
					writer += sampleAlpabet.charAt(index);
					
					index = ran.nextInt(sampleAlpabet.length());
					email += sampleAlpabet.charAt(index);
					
					index = ran.nextInt(sampleAlpabet.length());
					subject += sampleAlpabet.charAt(index);
					
					index = ran.nextInt(sampleAlpabet.length());
					content += sampleAlpabet.charAt(index);
					
				}
				
				email += "@test.com";
				
				conn = getConnection();			
				String sql = " INSERT INTO board ";
						 sql  += " VALUES(0, ?, ?, ?, ?, ?, ?, 1, 1, 0, now()) ";
						 
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, writer);
				pstmt.setString(2, email);
				pstmt.setString(3, subject);
				pstmt.setString(4, pw);
				pstmt.setString(5, content);
				pstmt.setInt(6, ref);			
				pstmt.executeUpdate();
				
			}catch(Exception e) {
				e.printStackTrace();
			}finally {
				if(conn != null) { try { conn.close(); } catch (Exception e) {} }
				if(pstmt != null) { try { pstmt.close(); } catch (Exception e) {} }
				if(rs != null) { try { rs.close(); } catch (Exception e) {} }
			}
		}
	}
}