[JSP]MVC게시판 만들기_01

게시판 만들기

가장 기초적인 게시판의 형태를 만들어 보도록 하자.

---

다음과 같이 게시판 목록을 만들고 게시글 제목을 눌렀을 때 해당 내용을 볼 수 있는 페이지로 이동한다. 

 

게시판을 만들기 위해서는

1. JDBC(Java DataBase Connection) : Database에 자료를 저장하고

2. JSP(Java Server Page): Server을 통해 자료 요청에 응답할 수 있어야 한다. 

---

 

1-1. DateBase table 생성 

DROP SEQUENCE MYSEQ;
DROP TABLE MYBOARD;

CREATE SEQUENCE MYSEQ;

CREATE TABLE MYBOARD(
	MYNO NUMBER PRIMARY KEY,
	MYNAME VARCHAR2(500) NOT NULL,
	MYTITLE VARCHAR2(2000) NOT NULL,
	MYCONTENT VARCHAR2(4000) NOT NULL,
	MYDATE DATE NOT NULL
);

INSERT INTO MYBOARD
VALUES(MYSEQ.NEXTVAL, '관리자', '테스트 글', 'TEST1234테스트',SYSDATE);

SELECT MYNO, MYNAME, MYTITLE, MYCONTENT, MYDATE
FROM MYBOARD
ORDER BY MYNO DESC;

 

1-2. java

(MVC에 대한 자세한 설명은 다시 정리할 예정!)

Dto(Data Transfer object) = VO(Value Object) : 데이터베이스에 row값을 저장

※ DB컬럼과 Dto의 변수명은 통일

package com.my.dto;

import java.util.Date;

public class MyBoardDto {
	
	private int myno;
	private String myname;
	private String mytitle;
	private String mycontent;
	private Date mydate;
	
	public MyBoardDto() {
		
	}
	
	public MyBoardDto(int myno, String myname, String mytitle, String mycontent, Date mydate) {
		super();
		this.myno = myno;
		this.myname = myname;
		this.mytitle = mytitle;
		this.mycontent = mycontent;
		this.mydate = mydate;
	}
	
	public int getMyno() {
		return myno;
	}
	public void setMyno(int myno) {
		this.myno = myno;
	}
	
	public String getMyname() {
		return myname;
	}
	public void setMyname(String myname) {
		this.myname = myname;
	}
	
	public String getMytitle() {
		return mytitle;
	}
	public void setMytitle(String mytitle) {
		this.mytitle = mytitle;
	}
	
	public String getMycontent() {
		return mycontent;
	}
	public void setMycontent(String mycontent) {
		this.mycontent = mycontent;
	}
	
	public Date getMydate() {
		return mydate;
	}
	public void setMydate(Date mydate) {
		this.mydate = mydate;
	}

<▼driver 연결: ojdbc6.jar 파일을 WEB-INF에 꼭 담아준다! > 
- statement: 매개변수가 없는 sql을 실행, 단순 출력이나 조회할 때 사용
- preparedStatement: 매개변수가 필요한 sql문을 먼저 컴파일하고 값을 나중에 set해주어 사용
- executeQuery: select문에 사용, resultSet과 사용
- executedUpdate: 수정, 삭제, 삽입할 때 사용하고 실행에 성공한 row값을 int로 반환받는다

package com.my.dao;

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

import com.my.dto.MyBoardDto;

	

public class MyBoardDao {
	
	//전체출력
	public List<MyBoardDto> selectList(){
		//1. drive 연결
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("1.driver 연결");
		} catch (ClassNotFoundException e) {
			System.out.println("[error]1. ");
			e.printStackTrace();
		}
		
		//2. 계정연결
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String user = "kh";
		String password = "kh";
		
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(url, user, password);
			System.out.println("2. 계정 연결");
		} catch (SQLException e) {
			System.out.println("[error] 2.");
			e.printStackTrace();
		}
		
		//3. query 준비
		Statement stmt = null;
		ResultSet rs = null;
		String sql = " SELECT MYNO, MYNAME, MYTITLE, MYCONTENT, MYDATE FROM MYBOARD ORDER BY MYNO DESC ";
		
		List<MyBoardDto> list = new ArrayList<MyBoardDto>();
		
		try {
			stmt = con.createStatement();
			System.out.println("3. query 준비");
			
			//4. query 실행 및 리턴
			rs = stmt.executeQuery(sql);
			System.out.println("4. query 실행 및 리턴");
			while(rs.next()) {
				MyBoardDto dto = new MyBoardDto();
				dto.setMyno(rs.getInt(1));
				dto.setMyname(rs.getString(2));
				dto.setMytitle(rs.getString(3));
				dto.setMycontent(rs.getString(4));
				dto.setMydate(rs.getDate(5));
				list.add(dto);
			}
			
		} catch (SQLException e) {
			System.out.println("[error] 3. 4.");
			e.printStackTrace();
		} finally {
			//5. db종료
			try {
				rs.close();
				stmt.close();
				con.close();
				System.out.println("5. db종료");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
		
	}
	
	//선택출력
	public MyBoardDto selectOne(int myno) {
		//1. drive 연결
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("1.driver 연결");
		} catch (ClassNotFoundException e) {
			System.out.println("[error]1. ");
			e.printStackTrace();
		}
		
		//2. 계정연결
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String username = "kh";
		String password = "kh";
		
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(url, username, password);
			System.out.println("2. 계정연결");
		} catch (SQLException e1) {
			System.out.println("[error] 2.");
			e1.printStackTrace();
		}
		
		
		//3. query 준비 
		String sql = " SELECT MYNO, MYNAME, MYTITLE, MYCONTENT, MYDATE FROM MYBOARD WHERE MYNO = ? ";

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		MyBoardDto dto = null;
		System.out.println("3. query 준비" + sql);
		
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, myno);
			pstmt.executeUpdate();
			rs = pstmt.executeQuery();
			System.out.println("4. query 실행 및 리턴");
			
			while(rs.next()) {
				dto = new MyBoardDto();
				dto.setMyno(rs.getInt(1));
				dto.setMyname(rs.getString(2));
				dto.setMytitle(rs.getString(3));
				dto.setMycontent(rs.getString(4));
				dto.setMydate(rs.getDate(5));
			}
			
			
		} catch (SQLException e) {
			System.out.println("[error] 3. 4.");
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		return dto;
	}
	
	//추가
	public int insert(MyBoardDto dto) {
		//1. driver 연결
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		//2. 계정 연결
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String username = "kh";
		String password = "kh";
		
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(url, username, password);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pstmt = null;
		int res = 0;
		
		//3. query 준비
		String sql = " INSERT INTO MYBOARD VALUES(MYSEQ.NEXTVAL, ?, ?, ?, SYSDATE) " ;
		
		
		try {
			//4. query 실행 및 리턴
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getMyname());
			pstmt.setString(2, dto.getMytitle());
			pstmt.setString(3, dto.getMycontent());
			

			res = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				//5. 종료
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			System.out.println("5. db종료");
		}		
		return res;
	}
	
	//수정
	public int update(MyBoardDto dto) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String username = "kh";
		String password = "kh";
		
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(url, username, password);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pstmt = null;
		int res = 0;
		
		String sql = " UPDATE MYBOARD SET MYTITLE = ?, MYCONTENT =? WHERE MYNO = ? ";
		
		try {
			pstmt = con.prepareStatement(sql) ;
			pstmt.setString(1, dto.getMytitle());
			pstmt.setString(2, dto.getMycontent());
			pstmt.setInt(3, dto.getMyno());
			
			res = pstmt.executeUpdate();
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return res;
	}
	
	//삭제
	public int delete(int myno) {

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String username = "kh";
		String password = "kh";
		
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(url, username, password);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pstmt = null;
		int res = 0;
		
		String sql = " DELETE FROM MYBOARD WHERE MYNO = ? ";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, myno);
			
			res = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
		return res;
	}

}