23.06.13 화 65일차
JAVA
- 게시판 페이지 나누기
한 페이지에 10개의 레코드 보여주기
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- db 호출시 사용 -->
<%@ page import="java.sql.*" %>
<%@ include file ="DB.jsp" %>
<%@ include file="top.jsp" %>
<%
PreparedStatement pstmt=null;
ResultSet rs = null;
String sql = "select rownum, Q.* " ;
sql = sql + " from " ;
sql = sql + " ( " ;
sql = sql + " select rownum as rnum, K.* " ;
sql = sql + " from " ;
sql = sql + " ( select m_idx, m_sname, m_title, m_cnt from membert order by m_idx desc ) K " ;
sql = sql + " where rownum <= ? " ;
sql = sql + " )Q " ;
sql = sql + " where rnum >= ? " ;
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, 10);
pstmt.setInt(2, 1);
rs = pstmt.executeQuery();
%>
<section>
<br>
<div align=center>
<h2>회원 목록 보기</h2>
<table border=1 width=700px;>
<tr>
<td>순번</td>
<td>이름</td>
<td>제목</td>
<td>조회수</td>
</tr>
<% while (rs.next()){
String m_idx = rs.getString("m_idx");
String m_sname = rs.getString("m_sname");
String m_title = rs.getString("m_title");
String m_cnt = rs.getString("m_cnt");
%>
<tr>
<td><a href=edit.jsp?m_idx=<%=m_idx%>><%=m_idx%></a></td>
<td><%=m_sname%></td>
<td><%=m_title%></td>
<td><%=m_cnt%></td>
</tr>
<%} %>
</table>
</section>
<%@ include file="footer.jsp" %>rownum과 rnum으로 레코드 갯수의 범위를 지정해 지정한 범위 만큼의 레코드 갯수를 확인 할 수 있다.
rownum = 레코드 개수 지정( 보여줄 레코드 갯수 )
rnum = 마지막 레코드 ( 1부터 시작 )
rownum : 1부터 생성해주는 오라클 고유 명령어
int start = 0;
if (request.getParameter("start") == null){
start = 1;
}else{
start = Integer.parseInt(request.getParameter("start"));
}
String ch1= request.getParameter("ch1");
String ch2= request.getParameter("ch2");
PreparedStatement pstmt=null;
ResultSet rs = null;
String sql = "select rownum, Q.* " ;
sql = sql + " from " ;
sql = sql + " ( " ;
sql = sql + " select rownum as rnum, K.* " ;
sql = sql + " from " ;
sql = sql + " ( select m_idx, m_sname, m_title, m_cnt from membert order by m_idx desc ) K " ;
sql = sql + " where rownum <= ? " ;
sql = sql + " )Q " ;
sql = sql + " where rnum >= ? " ;
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, start + 9);
pstmt.setInt(2, start);
rs = pstmt.executeQuery();start 변수로 파라미터 값을 받아와 레코드의 갯수를 정해주는 부분이다.
start가 null일경우 0으로 초기화 시켜주고, 0이 아닐경우 형변환 하여
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- db 호출시 사용 -->
<%@ page import="java.sql.*" %>
<%@ include file ="DB.jsp" %>
<%@ include file="top.jsp" %>
<%
String ch1 = request.getParameter("ch1");
String ch2 = request.getParameter("ch2");
int start = 0;
int pageSize = 15;
if (request.getParameter("start") == null){
start = 1;
}else{
start = Integer.parseInt(request.getParameter("start"));
}
PreparedStatement pstmt=null;
PreparedStatement pstmt2=null;
ResultSet rs = null;
ResultSet rs2 = null;
if (ch1==null || ch2=="" || ch2.equals("null")){
String sql_tc = "select count(*) tc from memberT" ;
pstmt2 = con.prepareStatement(sql_tc);
String sql = "select rownum, Q.* " ;
sql = sql + " from (select rownum as rnum, K.* from " ;
sql = sql + " ( select m_idx, m_sname, m_title, m_cnt from membert order by m_idx desc ) K " ;
sql = sql + " where rownum <= ? )Q " ;
sql = sql + " where rnum >= ? " ;
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, start + pageSize-1);
pstmt.setInt(2, start);
}else if(ch1.equals("m_sname")){
String sql_tc = "select count(*) tc from memberT where m_sname like ?";
pstmt2 = con.prepareStatement(sql_tc);
pstmt2.setString(1, "%" + ch2 + "%");
String sql = "select rownum, Q.* " ;
sql = sql + " from (select rownum as rnum, K.* from " ;
sql = sql + " ( select m_idx, m_sname, m_title, m_cnt from membert where m_sname like ? order by m_idx desc ) K " ;
sql = sql + " where rownum <= ? )Q " ;
sql = sql + " where rnum >= ? " ;
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%" + ch2 + "%");
pstmt.setInt(2, start + pageSize-1);
pstmt.setInt(3, start);
}else if(ch1.equals("m_title")){
String sql_tc = "select count(*) tc from memberT where m_title like ?" ;
pstmt2 = con.prepareStatement(sql_tc);
pstmt2.setString(1, "%" + ch2 + "%");
String sql = "select rownum, Q.* " ;
sql = sql + " from (select rownum as rnum, K.* from " ;
sql = sql + " ( select m_idx, m_sname, m_title, m_cnt from membert where m_title like ? order by m_idx desc ) K " ;
sql = sql + " where rownum <= ? )Q " ;
sql = sql + " where rnum >= ? " ;
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%" + ch2 + "%");
pstmt.setInt(2, start + pageSize-1);
pstmt.setInt(3, start);
}
rs2 = pstmt2.executeQuery();
rs = pstmt.executeQuery();
rs2.next();
int tc = rs2.getInt("tc");
int totalPage = (int) (Math.ceil((double)tc / pageSize));
int nowPage = (start / pageSize) + 1;
int endPage = (totalPage - 1) * pageSize + 1;
%>
<section>
<br>
<div align=center>
<h2>회원 목록 보기(전체 레코드 수 : <%=tc%> 현재페이지 <%=nowPage%> / <%=totalPage %>전체페이지)</h2>
<table border=1 >
<tr>
<td>rownum</td>
<td>rnum</td>
<td>순번</td>
<td>이름</td>
<td>제목</td>
<td>조회수</td>
</tr>
<%
System.out.print(rs);
while (rs.next()){
String rownum = rs.getString("rownum");
String rnum = rs.getString("rnum");
String m_idx = rs.getString("m_idx");
String m_sname = rs.getString("m_sname");
String m_title = rs.getString("m_title");
String m_cnt = rs.getString("m_cnt");
%>
<tr>
<td><%=rownum%></td>
<td><%=rnum%></td>
<td><a href=edit_t1.jsp?m_idx=<%=m_idx%>><%=m_idx%></a></td>
<td><%=m_sname%></td>
<td><%=m_title%></td>
<td><%=m_cnt%></td>
</tr>
<%} %>
</table>
<br>
<%
if (ch2 != null){
ch2 = java.net.URLEncoder.encode(ch2, "UTF-8");
}
if(start > pageSize) {
%>
<a href=list_t1.jsp?ch1=<%=ch1%>&ch2=<%=ch2%>&start=1>처음으로</a> 
<%}else{ %>
처음으로  
<%} %>
<%if(start >= 2) {%>
<a href=list_t1.jsp?start=<%=start-pageSize%>&ch1=<%=ch1%>&ch2=<%=ch2%>>이전</a> 
<%}else{ %>
이전  
<%} %>
<%if(totalPage != nowPage){%>
<a href=list_t1.jsp?start=<%=start+pageSize%>&ch1=<%=ch1%>&ch2=<%=ch2%>>다음</a> 
<%}else{ %>
다음  
<%} %>
<%if(nowPage != totalPage){%>
<a href=list_t1.jsp?start=<%=endPage%>&ch1=<%=ch1%>&ch2=<%=ch2%>>마지막으로</a> 
<%}else{ %>
마지막으로  
<%} %>
<br>
<form>
<select name=ch1>
<option value ="m_sname">이 름</option>
<option value ="m_title">제 목</option>
</select>
<input type=text name=ch2>
<input type=submit value="검색하기">
</form>
</div>
<br>
<br>
</section>
<%@ include file="footer.jsp" %>레코드의 검색을 추가하여 검색 단어 포함시 목록이 보여지도록 설정했고, 처음, 이전, 다음, 마지막 링크를 추가하여 게시판 목록을 넘기도록 설정했다.
- 페이지 넘버 설정
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- db 호출시 사용 -->
<%@ page import="java.sql.*" %>
<%@ include file ="DB.jsp" %>
<%@ include file="top.jsp" %>
<%
String ch1 = request.getParameter("ch1");
String ch2 = request.getParameter("ch2");
int start = 0;
int pageSize = 15;
int pageListSize = 10;
if (request.getParameter("start") == null){
start = 1;
}else{
start = Integer.parseInt(request.getParameter("start"));
}
PreparedStatement pstmt=null;
PreparedStatement pstmt2=null;
ResultSet rs = null;
ResultSet rs2 = null;
if (ch1==null || ch2=="" || ch2.equals("null")){
String sql_tc = "select count(*) tc from memberT" ;
pstmt2 = con.prepareStatement(sql_tc);
String sql = "select rownum, Q.* " ;
sql = sql + " from (select rownum as rnum, K.* from " ;
sql = sql + " ( select m_idx, m_sname, m_title, m_cnt from membert order by m_idx desc ) K " ;
sql = sql + " where rownum <= ? )Q " ;
sql = sql + " where rnum >= ? " ;
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, start + pageSize-1);
pstmt.setInt(2, start);
}else if(ch1.equals("m_sname")){
String sql_tc = "select count(*) tc from memberT where m_sname like ?";
pstmt2 = con.prepareStatement(sql_tc);
pstmt2.setString(1, "%" + ch2 + "%");
String sql = "select rownum, Q.* " ;
sql = sql + " from (select rownum as rnum, K.* from " ;
sql = sql + " ( select m_idx, m_sname, m_title, m_cnt from membert where m_sname like ? order by m_idx desc ) K " ;
sql = sql + " where rownum <= ? )Q " ;
sql = sql + " where rnum >= ? " ;
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%" + ch2 + "%");
pstmt.setInt(2, start + pageSize-1);
pstmt.setInt(3, start);
}else if(ch1.equals("m_title")){
String sql_tc = "select count(*) tc from memberT where m_title like ?" ;
pstmt2 = con.prepareStatement(sql_tc);
pstmt2.setString(1, "%" + ch2 + "%");
String sql = "select rownum, Q.* " ;
sql = sql + " from (select rownum as rnum, K.* from " ;
sql = sql + " ( select m_idx, m_sname, m_title, m_cnt from membert where m_title like ? order by m_idx desc ) K " ;
sql = sql + " where rownum <= ? )Q " ;
sql = sql + " where rnum >= ? " ;
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%" + ch2 + "%");
pstmt.setInt(2, start + pageSize-1);
pstmt.setInt(3, start);
}
rs2 = pstmt2.executeQuery();
rs = pstmt.executeQuery();
rs2.next();
int tc =rs2.getInt("tc");
int totalPage = (int) (Math.ceil((double)tc / pageSize)) ; // 전체페이지
int nowPage = ( start / pageSize ) + 1 ; // 현재 페이지
int listStartpage = (nowPage - 1) / pageListSize * pageListSize + 1;
int listEndpage = listStartpage + pageListSize - 1 ;
int endPage = (totalPage - 1) * pageSize + 1 ;
%>
<section>
<br>
<div align=center>
1. 페이지 사이즈 <%=pageSize%>
2. 페이지 List 사이즈 <%=pageListSize %>
3. 전체 레코드 수 <%=tc%> <br>
4. 총 페이지 수<%=totalPage%>
5. 현재 레코드<%=start%>
6. 현재 페이지 <%=nowPage%> <br>
7. 가로 하단 시작<%=listStartpage %>
8. 가로 하단 끝<%=listEndpage %>
9. 마지막 페이지의 첫번째 레코드 <%=endPage%> <br>
<h2>회원 목록 보기(전체 레코드 수 : 현재페이지 / 전체페이지)</h2>
<table border=1 >
<tr>
<td>rownum</td>
<td>rnum</td>
<td>순번</td>
<td>이름</td>
<td>제목</td>
<td>조회수</td>
</tr>
<% while (rs.next()){
String rownum = rs.getString("rownum");
String rnum = rs.getString("rnum");
String m_idx = rs.getString("m_idx");
String m_sname = rs.getString("m_sname");
String m_title = rs.getString("m_title");
String m_cnt = rs.getString("m_cnt");
%>
<tr>
<td><%=rownum%></td>
<td><%=rnum%></td>
<td><a href=edit_t1.jsp?m_idx=<%=m_idx%>><%=m_idx%></a></td>
<td><%=m_sname%></td>
<td><%=m_title%></td>
<td><%=m_cnt%></td>
</tr>
<%} %>
</table>
<br>
<%
if(listStartpage > pageListSize ) {
start= (listStartpage - 11) * pageSize + 1 ;
%>
<a href=list2.jsp?start=<%=start %>&ch1=<%=ch1%>&ch2=<%=ch2 %>>이전<%=pageListSize%></a>  
<% } else { %>
이전<%=pageListSize%>  
<%} %>
<%
for(int i=listStartpage ; i <= listEndpage ; i++ ){
if (i <= totalPage ){
start = (i-1) * pageSize + 1 ;
%>
<a href=list2.jsp?start=<%=start%>&ch1=<%=ch1%>&ch2=<%=ch2 %>><%=i%></a>
<%
}
} %>
 
<%
if(listEndpage < totalPage){
start = listEndpage * pageSize + 1 ;
%>
<a href=list2.jsp?start=<%=start %>&ch1=<%=ch1%>&ch2=<%=ch2 %>>다음<%=pageListSize%></a>  
<% }else{ %>
다음<%=pageListSize%>  
<% } %>
<a href=list2.jsp?start=<%=endPage %>&ch1=<%=ch1%>&ch2=<%=ch2 %>>마지막</a>
<br>
<form>
<select name=ch1>
<option value ="m_sname">이 름</option>
<option value ="m_title">제 목</option>
</select>
<input type=text name=ch2>
<input type=submit value="검색하기">
</form>
</div>
<br>
<br>
</section>
<%@ include file="footer.jsp" %>개인적으로 오늘 배운 내용중에 가장 헷갈렸던 부분이다.
페이지의 넘버를 설정하기위해 범위값을 선언한 변수를 가지고 연산을 해서 범위를 지정해야한다.
하나씩 하나씩 차근차근 결과를 확인하며 진행하는것이 중요한 걸로 보인다.
- MVC(Model-View-Controller) 패턴 게시판 만들기
Board VO
package psd;
public class BoardVO {
private int m_idx;
private String m_sname;
private String m_title;
private int m_cnt;
public int getM_idx() {
return m_idx;
}
public void setM_idx(int m_idx) {
this.m_idx = m_idx;
}
public String getM_sname() {
return m_sname;
}
public void setM_sname(String m_sname) {
this.m_sname = m_sname;
}
public String getM_title() {
return m_title;
}
public void setM_title(String m_title) {
this.m_title = m_title;
}
public int getM_cnt() {
return m_cnt;
}
public void setM_cnt(int m_cnt) {
this.m_cnt = m_cnt;
}
}
----------------------------------------------------------------------
Dao
package psd;
import java.util.*;
public interface Dao {
List<BoardVO>selectAll(BoardVO vo);
}
----------------------------------------------------------------------
Service
package psd;
import java.util.*;
public interface Service {
List<BoardVO>selectAll(BoardVO vo);
}
----------------------------------------------------------------------
DB
package psd;
import java.sql.*;
public class DB {
public Connection getConnection() {
Connection con = null;
try {
String url = "jdbc:oracle:thin:@//localhost:1521/xe";
String userid = "system";
String userpwd = "1234";
Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager.getConnection(url, userid, userpwd);
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
}
----------------------------------------------------------------------
ServiceImpl
package psd;
import java.util.*;
public class ServiceImpl implements Service{
Dao dao = null;
public ServiceImpl() {
dao = new DaoImpl();
}
@Override
public List<BoardVO> selectAll(BoardVO vo) {
// TODO Auto-generated method stub
return dao.selectAll(vo);
}
}
----------------------------------------------------------------------
DaoImpl
package psd;
import java.sql.*;
import java.util.*;
public class DaoImpl implements Dao{
@Override
public List<BoardVO> selectAll(BoardVO vo) {
DB dbconn = new DB();
Connection conn = dbconn.getConnection();
PreparedStatement pstmt = null;
ResultSet rs =null;
List<BoardVO> li = null ;
try {
li = new ArrayList<BoardVO>();
String SQL = "select * from memberT order by m_idx desc" ;
pstmt = conn.prepareStatement(SQL);
rs = pstmt.executeQuery();
BoardVO m = null;
while(rs.next()) {
m = new BoardVO();
m.setM_idx(rs.getInt("m_idx"));
m.setM_sname(rs.getString("m_sname"));
m.setM_title(rs.getString("m_title"));
m.setM_cnt(rs.getInt("m_cnt"));
li.add(m);
System.out.println(m);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
rs.close();
pstmt.close();
conn.close();
}catch (SQLException e) {
e.printStackTrace();
}
}
return li;
}
}
----------------------------------------------------------------------
BoardController
package psd;
import java.io.IOException;
import java.util.*;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class BoardController
*/
@WebServlet("/BoardController")
public class BoardController extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public BoardController() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
// response.getWriter().append("Served at: ").append(request.getContextPath());
Service s = new ServiceImpl();
List<BoardVO> li= s.selectAll(null);
request.setAttribute("li", li);
RequestDispatcher dis = request.getRequestDispatcher("list_t3.jsp");
dis.forward(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
----------------------------------------------------------------------
list_t3.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- db 호출시 사용 -->
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="psd.*"%>
<%@ include file="top.jsp" %>
<%
List<BoardVO> li = (List<BoardVO>) request.getAttribute("li");
%>
<section>
<br>
<div align=center>
<h2>회원 목록 보기</h2>
<table border=1 >
<tr>
<td>rownum</td>
<td>rnum</td>
<td>순번</td>
<td>이름</td>
<td>제목</td>
<td>조회수</td>
</tr>
<tr>
<%for(BoardVO m : li){ %>
<td><%=m.getM_idx() %></td>
<td><%=m.getM_sname() %></td>
<td><%=m.getM_title() %></td>
<td><%=m.getM_cnt() %></td>
<td>값5</td><td>값6</td>
</tr>
<%} %>
</table>
<br>
<br>
</section>
<%@ include file="footer.jsp" %>
BoardController 에서 ServiceImpl과 List의 li 변수로 받아 Dispatcher의 forword로 list_t3 파일에 리턴을 시켜주었다.
Back-end Developer Preparation Students