웹 - jdbc - 예제(방명록) - db,vo,dao구성/방명록 조회

 

->CRUD 처리하기

 

1. DB작업

--방명록 DB

-- 일련번호 관리하는 객체(시퀀스)
create sequence seq_visit_idx 

-- 방명록 테이블 생성
create table visit
(	
	idx		int,
	name	varchar2(100)	not null,
	content	varchar2(2000)	not null, 
	pwd		varchar2(100)	not null,
	ip		varchar2(100)	not null,
	regdate	date
)	

-- 기본키		
alter table visit 
	add constraint pk_visit_idx	primary key(idx);
	
-- sample data
insert into visit values(seq_visit_idx.nextVal,
						 '일길동',
						 '내가 1등이다',
						 '1234',
						 '192.168.219.170',
						 sysdate
  						);	
insert into visit values(seq_visit_idx.nextVal,
						 '이길동',
						 '아쉽네 내가 1등할 수 있었는데...',
						 '1234',
						 '192.168.219.54',
						 sysdate
  						);	
  						
select * from visit

 

2. Vo만들기

package db.vo;

public class VisitVo {
	
	int		idx;
	String 	name;
	String	content;
	String	pwd;
	String	ip;
	String	regdate;
	
	public VisitVo() {
		// TODO Auto-generated constructor stub
	}	

	public VisitVo(int idx, String name, String content, String pwd, String ip, String regdate) {
		super();
		this.idx = idx;
		this.name = name;
		this.content = content;
		this.pwd = pwd;
		this.ip = ip;
		this.regdate = regdate;
	}

	public int getIdx() {
		return idx;
	}
	public void setIdx(int idx) {
		this.idx = idx;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public String getIp() {
		return ip;
	}
	public void setIp(String ip) {
		this.ip = ip;
	}
	public String getRegdate() {
		return regdate;
	}
	public void setRegdate(String regdate) {
		this.regdate = regdate;
	}

}

 

3. dao (조회 메서드만)

package dao;

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 db.vo.VisitVo;
import service.DBService;

public class VisitDao {

	// single-ton pattern : 객체 1개만 생성해서 이용하자
	static VisitDao single = null;

	public static VisitDao getInstance() {

		//없으면 생성해라
		if (single == null)
			single = new VisitDao();

		return single;
	}

	// 외부에서 객체생성하지 말아라...
	private VisitDao() {

	}
	
	//목록조회
	public List<VisitVo> selectList() {

		List<VisitVo> list = new ArrayList<VisitVo>();

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		String sql = "select * from visit order by idx desc";

		try {
			//1.Connection 얻어오기
			conn = DBService.getInstance().getConnection();

			//2.PreparedStatement
			pstmt = conn.prepareStatement(sql);

			//3.ResultSet 얻어온다
			rs = pstmt.executeQuery();

			while (rs.next()) {

				//저장객체 생성->레코드에서 읽은 값을 넣는다
				VisitVo vo = new VisitVo();

				//rs가 가리키는 레코드값을 vo에 넣는다
				vo.setIdx(rs.getInt("idx"));
				vo.setName(rs.getString("name"));
				vo.setContent(rs.getString("content"));
				vo.setPwd(rs.getString("pwd"));
				vo.setIp(rs.getString("ip"));
				vo.setRegdate(rs.getString("regdate"));

				//ArrayList에 추가
				list.add(vo);

			} //end:while

		} catch (Exception e) {			
			e.printStackTrace();

		} finally {

			//마무리 작업(열린역순으로 닫기)
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {				
				e.printStackTrace();
			}
		}

		return list;
	} // end - selectList()
}

 

4. 조회 서블릿 생성

package action;

import jakarta.servlet.RequestDispatcher;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * Servlet implementation class VisitListAction
 */
@WebServlet("/visit/list.do")
public class VisitListAction extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		// Dispatcher형식으로 호출
		String forward_page = "visit_list.jsp";
		RequestDispatcher disp = request.getRequestDispatcher(forward_page);
		disp.forward(request, response);

	}
}

 

5. 조회 jsp 생성

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
방명록 보기...
</body>
</html>

 

) 서블릿 실행 후 url로 jsp로 이동하면...