JSP - Student관리프로그램(2) - 회원가입, 로그인, 게시판

JSP - Student관리프로그램(2) - 게시판

class : BoardDAO, BoardDTO

jsp : list, content, updateForm, updatePro, deleteForm, deletePro, writeForm, writePro

 

BOARD테이블 생성

 - 번호, 작성자, 이메일, 제목, 비번, 작성일, 조회수, 내용, ip

 - 시퀀스 - num(게시물 번호 자동증가)

 

게시판 table

create table board
(num number primary key,
writer varchar2(10) not null,
email varchar2(30),
subject varchar2(50) not null,
passwd varchar2(12) not null,
reg_date varchar2(10) not null,
readcount number default 0,
content varchar2(4000) not null,
ip varchar2(20) not null);

create sequence board_seq;

 

BoardDTO

package my.board;

public class BoardDTO {
	private int num;
	private String writer;
	private String email;
	private String subject;
	private String passwd;
	private String reg_date;
	private int readcount;
	private String content;
	private String ip;
	
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getWriter() {
		return writer;
	}
	public void setWriter(String witer) {
		this.writer = witer;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getSubject() {
		return subject;
	}
	public void setSubject(String subject) {
		this.subject = subject;
	}
	public String getPasswd() {
		return passwd;
	}
	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}
	public String getReg_date() {
		return reg_date;
	}
	public void setReg_date(String reg_date) {
		this.reg_date = reg_date;
	}
	public int getReadcount() {
		return readcount;
	}
	public void setReadcount(int readcount) {
		this.readcount = readcount;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getIp() {
		return ip;
	}
	public void setIp(String ip) {
		this.ip = ip;
	}
	
	
	
}

 

BoardDAO : 기능 리스트, 삽입, 조회수증가, 삭제, 수정

package my.board;

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

public class BoardDAO {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	String url, user, pass;
	
	public BoardDAO() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}
		url = "jdbc:oracle:thin:@localhost:1521/XEPDB1";
		user = "arang";
		pass = "arang";
	}
	
	protected List<BoardDTO> makeList(ResultSet rs) throws SQLException {
		List<BoardDTO> list = new ArrayList<>();
		while(rs.next()) {
			BoardDTO dto = new BoardDTO();
			dto.setNum(rs.getInt("num"));
			dto.setWriter(rs.getString("writer"));
			dto.setEmail(rs.getString("email"));
			dto.setSubject(rs.getString("subject"));
			dto.setPasswd(rs.getString("passwd"));
			dto.setReg_date(rs.getString("reg_date"));
			dto.setReadcount(rs.getInt("readcount"));
			dto.setContent(rs.getString("content"));
			dto.setIp(rs.getString("ip"));
			list.add(dto);
		}
		return list;
	}
	
	public List<BoardDTO> listBoard() throws SQLException {
		try {
			conn = DriverManager.getConnection(url, user, pass);
			String sql = "select * from boardex order by num desc";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			List<BoardDTO> list = makeList(rs);
			return list;
		}finally {
			if (rs != null) rs.close();
			if (pstmt != null) pstmt.close();
			if (conn != null) conn.close();
		}
	}
	
	public int insertBoard(BoardDTO dto) throws SQLException{
		try {
			conn = DriverManager.getConnection(url, user, pass);
			String sql = "insert into boardex values(board_seq.nextval, ?,?,?,?,sysdate,0,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getWriter());
			pstmt.setString(2, dto.getEmail());
			pstmt.setString(3, dto.getSubject());
			pstmt.setString(4, dto.getPasswd());
			pstmt.setString(5, dto.getContent());
			pstmt.setString(6, dto.getIp());
			int res = pstmt.executeUpdate();
			return res;
		}finally {
			if (pstmt != null) pstmt.close();
			if (conn != null) conn.close();
		}
	}
	
	
	protected void plusReadcount(int num)throws SQLException{
		try {
			conn = DriverManager.getConnection(url, user, pass);
			String sql = "update boardex set readcount = readcount + 1 where num = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			pstmt.executeUpdate();
		}finally {
			if(pstmt != null) pstmt.close();
			if(conn != null) conn.close();
		}
	}
	
	public BoardDTO getBoard(int num, String mode) throws SQLException {
		try {
			if (mode.equals("content")) {
				plusReadcount(num);
			}
			conn = DriverManager.getConnection(url, user, pass);
			String sql = "select * from boardex where num = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			List<BoardDTO> list = makeList(rs);
			return list.get(0);
		}finally {
			if (rs != null) rs.close();
			if (pstmt != null) pstmt.close();
			if (conn != null) conn.close();
		}
	}
	
	public int deleteBoard(int num, String passwd) throws SQLException {
		try {
			BoardDTO dto = getBoard(num, "password");
			if (dto.getPasswd().equals(passwd)) {
				conn = DriverManager.getConnection(url, user, pass);
				String sql = "delete from boardex where num = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, num);
				int res = pstmt.executeUpdate();
				return res;
			}else {
				return -1;
			}
		}finally {
			if (pstmt != null) pstmt.close();
			if (conn != null) conn.close();
		}
	}
	
	public int updateBoard(BoardDTO dto) throws SQLException {
		try {
			BoardDTO dto2 = getBoard(dto.getNum(), "password");
			if (dto2.getPasswd().equals(dto.getPasswd())) {
				conn = DriverManager.getConnection(url, user, pass);
				String sql = "update boardex set writer=?, subject=?, "
									+ "email=?, content=? where num = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, dto.getWriter());
				pstmt.setString(2, dto.getSubject());
				pstmt.setString(3, dto.getEmail());
				pstmt.setString(4, dto.getContent());
				pstmt.setInt(5, dto.getNum());
				int res = pstmt.executeUpdate();
				return res;
			}else {
				return -1;
			}
		}finally {
			if (pstmt != null) pstmt.close();
			if (conn != null) conn.close();
		}
	}
	
	
}

------------------------------------------------------------------------------------------------------------------------------------------------

index

memberSsn : 회원가입

회원목록 : 수정, 삭제

게시판 : 글쓰기, 수정, 삭제, 조회수증가

로그인 : 로그인, 로그인시 세션(ID기억하기), singleton,

 

JSP : 회원가입, 수정, 삭제, 목록, 로그인, ID기억하기, 게시판 글쓰기, 수정, 삭제

https://github.com/designAR/JSP-member-login-board

GitHub - designAR/JSP-member-login-board: JSP로 구현한 회원관리,게시판 프로그램