기존 list.jsp 


<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="java.sql.*,java.text.SimpleDateFormat,java.util.Date"%>

<%
	final int ROWSIZE = 4;
	final int BLOCK = 5;

	int pg = 1;
	
	if(request.getParameter("pg")!=null) {
		pg = Integer.parseInt(request.getParameter("pg"));
	}
	
	int start = (pg*ROWSIZE) - (ROWSIZE-1);
	int end = (pg*ROWSIZE);
	
	int allPage = 0;
	
	int startPage = ((pg-1)/BLOCK*BLOCK)+1;
	int endPage = ((pg-1)/BLOCK*BLOCK)+BLOCK;

%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
 <title>게시판</title>
 </head>
 <body>
 <%
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	String url = "jdbc:odbc:board2";
	String id = "";
	String pass = "";
	int total = 0;
	
	try {
		Connection conn = DriverManager.getConnection(url,id,pass);
		Statement stmt = conn.createStatement();
		Statement stmt1 = conn.createStatement();
		String sql = "";

		String sqlCount = "SELECT COUNT(*) FROM board1";
		ResultSet rs = stmt.executeQuery(sqlCount);
		
		if(rs.next()){
			total = rs.getInt(1);
		}

		int sort=1;
		String sqlSort = "SELECT NUM from board1 order by ref desc, step asc";
		rs = stmt.executeQuery(sqlSort);
	
		
		while(rs.next()){
			int stepNum = rs.getInt(1);
			sql = "UPDATE board1 SET STEP2=" + sort + " where NUM=" +stepNum;
		 	stmt1.executeUpdate(sql);
		 	sort++;
		}
		
		allPage = (int)Math.ceil(total/(double)ROWSIZE);
		
		if(endPage > allPage) {
			endPage = allPage;
		}
		
		out.print("총 게시물 : " + total + "");
		
		String sqlList = "SELECT NUM, USERNAME, TITLE, TIME, HIT, INDENT from board1 where STEP2 >="+start + " and STEP2 <= "+ end +" order by step2 asc";
		rs = stmt.executeQuery(sqlList);
		
%>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
  <tr height="5"><td width="5"></td></tr>
 <tr style="background:url('img/table_mid.gif') repeat-x; text-align:center;">
   <td width="5"><img src="img/table_left.gif" width="5" height="30" /></td>
   <td width="73">번호</td>
   <td width="379">제목</td>
   <td width="73">작성자</td>
   <td width="164">작성일</td>
   <td width="58">조회수</td>
   <td width="7"><img src="img/table_right.gif" width="5" height="30" /></td>
  </tr>
<%
	if(total==0) {
%>
	 		<tr align="center" bgcolor="#FFFFFF" height="30">
	 	   <td colspan="6">등록된 글이 없습니다.</td>
	 	  </tr>
<%
	 	} else {
	 		
		while(rs.next()) {
			int idx = rs.getInt(1);
			String name = rs.getString(2);
			String title = rs.getString(3);
			String time = rs.getString(4);
			int hit = rs.getInt(5);
			int indent = rs.getInt(6);
			
			Date date = new Date();
			SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd"); 
			String year = (String)simpleDate.format(date);
			String yea = time.substring(0,10);
		
%>
<tr height="25" align="center">
	<td>&nbsp;</td>
	<td><%=idx %></td>
	<td align="left">
	<% 
		
	for(int j=0;j<indent;j++){
%>		&nbsp;&nbsp;&nbsp;<%
	}
	if(indent!=0){
%>		<img src='img/reply_icon.gif' />
<%
	}
%>
	<a href="view.jsp?idx=<%=idx%>&pg=<%=pg%>"><%=title %></a>
<%
	if(year.equals(yea)){
%>
			<img src='img/new.jpg' />
<%
		} 
%>
	</td>
	<td align="center"><%=name %></td>
	<td align="center"><%=yea %></td>
	<td align="center"><%=hit %></td>
	<td>&nbsp;</td>
</tr>
  <tr height="1" bgcolor="#D2D2D2"><td colspan="6"></td></tr>
<% 
		}
	} 
	rs.close();
	stmt.close();
	conn.close();
} catch(SQLException e) {
//	out.println( e.toString() );
}
%>
 <tr height="1" bgcolor="#82B5DF"><td colspan="6" width="752"></td></tr>
 </table>
 
<table width="100%" cellpadding="0" cellspacing="0" border="0">
  <tr><td colspan="4" height="5"></td></tr>
  <tr>
	<td align="center">
		<%
			if(pg>BLOCK) {
		%>
			[<a href="list.jsp?pg=1">◀◀</a>]
			[<a href="list.jsp?pg=<%=startPage-1%>"></a>]
		<%
			}
		%>
		
		<%
			for(int i=startPage; i<= endPage; i++){
				if(i==pg){
		%>
					<u><b>[<%=i %>]</b></u>
		<%
				}else{
		%>
					[<a href="list.jsp?pg=<%=i %>"><%=i %></a>]
		<%
				}
			}
		%>
		
		<%
			if(endPage<allPage){
		%>
			[<a href="list.jsp?pg=<%=endPage+1%>"></a>]
			[<a href="list.jsp?pg=<%=allPage%>">▶▶</a>]
		<%
			}
		%>
		</td>
		</tr>
		  <tr align="center">
   <td><input type=button value="글쓰기" OnClick="window.location='write.jsp'"></td>
  </tr>
 </table>
 </body

</html>



이제 데이터베이스 관련부분을 DAO에서 가져오도록 하겠습니다.


list.jsp


<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="board.*" %>
<%@ page import="java.util.*" %>

<jsp:useBean id="dao" class="board.DAO"/>

<%	
	int total = dao.count();
	ArrayList<VO> alist = dao.getMemberList();
	int size = alist.size();
	int size2 = size;
	
	final int ROWSIZE = 4;
	final int BLOCK = 5;
	int indent = 0;

	int pg = 1;
	
	if(request.getParameter("pg")!=null) {
		pg = Integer.parseInt(request.getParameter("pg"));
	}
	
	int end = (pg*ROWSIZE);
	
	int allPage = 0;

	int startPage = ((pg-1)/BLOCK*BLOCK)+1;
	int endPage = ((pg-1)/BLOCK*BLOCK)+BLOCK;
	
	allPage = (int)Math.ceil(total/(double)ROWSIZE);
	
	if(endPage > allPage) {
		endPage = allPage;
	}
	
	size2 -=end;
	if(size2 < 0) {
		end = size;
	}
	
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
 <head>
 <title>게시판</title>
 </head>
 <body>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
  <tr height="5"><td width="5"></td></tr>
 <tr style="background:url('img/table_mid.gif') repeat-x; text-align:center;">
   <td width="5"><img src="img/table_left.gif" width="5" height="30" /></td>
   <td width="73">번호</td>
   <td width="379">제목</td>
   <td width="73">작성자</td>
   <td width="164">작성일</td>
   <td width="58">조회수</td>
   <td width="7"><img src="img/table_right.gif" width="5" height="30" /></td>
  </tr>
<%
	if(total==0) {
%>
	 		<tr align="center" bgcolor="#FFFFFF" height="30">
	 	   <td colspan="6">등록된 글이 없습니다.</td>
	 	  </tr>
	 <%
	 	} else {
	 		for(int i=ROWSIZE*(pg-1); i<end;i++){
				VO vo = alist.get(i);
				indent = vo.getIndent();
				int idx = vo.getNum();
%>
	<tr height="25" align="center">
	<td align="center">&nbsp;</td>
	<td align="center"><%=idx%></td>
	<td align="left"><% 
		
		for(int j=0;j<indent;j++){
		%> &nbsp;&nbsp;&nbsp;<%
		}
		if(indent!=0){
			%><img src='img/reply_icon.gif' /><%
		}
	%>
	<a href="view.jsp?idx=<%=idx%>&pg=<%=pg%>"><%=vo.getTitle() %></a><%
		if(vo.isDayNew()){
			%>
			<img src='img/new.jpg' />
			<%
		}
	%></td>
   <td align="center"><%=vo.getName()%></td>
   <td align="center"><%=vo.getTime() %></td>
   <td align="center"><%=vo.getHit() %></td>
   <td align="center">&nbsp;</td>
  <tr height="1" bgcolor="#D2D2D2"><td colspan="6"></td></tr>
<% }} %>
 <tr height="1" bgcolor="#82B5DF"><td colspan="6" width="752"></td></tr>
</table>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
  <tr><td colspan="4" height="5"></td></tr>
  <tr>
	<td align="center">
		<%
			if(pg>BLOCK) {
		%>
			[<a href="list.jsp?pg=1">◀◀</a>]
			[<a href="list.jsp?pg=<%=startPage-1%>"></a>]
		<%
			}
		%>
		
		<%
			for(int i=startPage; i<= endPage; i++){
				if(i==pg){
		%>
					<u><b>[<%=i %>]</b></u>
		<%
				}else{
		%>
					[<a href="list.jsp?pg=<%=i %>"><%=i %></a>]
		<%
				}
			}
		%>
		
		<%
			if(endPage<allPage){
		%>
			[<a href="list.jsp?pg=<%=endPage+1%>"></a>]
			[<a href="list.jsp?pg=<%=allPage%>">▶▶</a>]
		<%
			}
		%>
		</td>
		</tr>
	<tr align="center">
   <td><input type=button value="글쓰기" OnClick="window.location='write.jsp'"></td>
  </tr>
 </table>
 </body>
</html>

코드가 상당히 줄어든걸 볼 수 있습니다. 



이미지출처 : 판다의 이상한 블로그(http://ssppmm.tistory.com/)

Posted by 세이나린
,

이번엔 가장 양이 많은 DAO파일을 만들어 보도록하겠습니다.

그러기전에 데이터베이스에서 STEP2 필드를 삭제하도록 합니다. 

전에 만든 게시판에선 jsp파일에 select, insert, update, delete문을 다 사용했지만 

이번에는 DAO파일에 모두 만들어 놓고 필요한것들을 불러와서 사용하는 방식으로 만들어 보도록 하겠습니다.


DAO.java


package board; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; public class DAO { DBConnect dbconnect = null; String sql=""; public DAO() { dbconnect = new DBConnect(); } public int count() { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; ResultSet rs = null; int cnt = 0; try { sql = "SELECT COUNT(*) FROM board1"; pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()) { cnt=rs.getInt(1); } }catch(Exception e) { }finally { DBClose.close(con,pstmt,rs); } return cnt; } public String pasing(String data) { try { data = new String(data.getBytes("8859_1"), "euc-kr"); }catch (Exception e){ } return data; } public ArrayList<VO> getMemberList() { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; ResultSet rs = null; ArrayList<VO> alist = new ArrayList<VO>(); try { sql = "SELECT NUM, USERNAME, TITLE, TIME, HIT, INDENT from board1 order by ref desc, step asc"; pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); while(rs.next()) { VO vo = new VO(); boolean dayNew = false; vo.setNum(rs.getInt(1)); vo.setName(rs.getString(2)); vo.setTitle(rs.getString(3)); Date date = new Date(); SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd"); String year = (String)simpleDate.format(date); String yea = rs.getString(4).substring(0,10); if(year.equals(yea)){ dayNew = true; } vo.setTime(yea); vo.setHit(rs.getInt(5)); vo.setIndent(rs.getInt(6)); vo.setDayNew(dayNew); alist.add(vo); } }catch(Exception e) { }finally { DBClose.close(con,pstmt,rs); } return alist; } public int getMax() { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; ResultSet rs = null; int max = 0; try { sql = "SELECT MAX(NUM) FROM board1"; pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); if(rs.next()) { max=rs.getInt(1); } }catch(Exception e) { }finally { DBClose.close(con,pstmt,rs); } return max; } public void insertWrite(VO vo, int max) { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; try { sql = "INSERT INTO board1(USERNAME,PASSWORD,TITLE,MEMO,REF) VALUES(?,?,?,?,?)"; pstmt = con.prepareStatement(sql); pstmt.setString(1, pasing(vo.getName())); pstmt.setString(2, pasing(vo.getPassword())); pstmt.setString(3, pasing(vo.getTitle())); pstmt.setString(4, pasing(vo.getMemo())); pstmt.setInt(5, max+1); pstmt.execute(); }catch(Exception e) { }finally { DBClose.close(con,pstmt); } } public VO getView(int idx) { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; ResultSet rs = null; VO vo = null; try { sql = "SELECT USERNAME, TITLE, MEMO, TIME, HIT, PASSWORD, REF, INDENT, STEP FROM board1 WHERE NUM=?"; pstmt = con.prepareStatement(sql); pstmt.setInt(1, idx); rs = pstmt.executeQuery(); if(rs.next()) { vo = new VO(); vo.setName(rs.getString(1)); vo.setTitle(rs.getString(2)); vo.setMemo(rs.getString(3)); vo.setTime(rs.getString(4)); vo.setHit(rs.getInt(5)+1); vo.setPassword(rs.getString(6)); vo.setRef(rs.getInt(7)); vo.setIndent(rs.getInt(8)); vo.setStep(rs.getInt(9)); } }catch(Exception e) { }finally { DBClose.close(con,pstmt,rs); } return vo; } public void UpdateHit(int idx) { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; try { sql = "UPDATE board1 SET HIT=HIT+1 where NUM=?"; pstmt = con.prepareStatement(sql); pstmt.setInt(1, idx); pstmt.executeUpdate(); }catch(Exception e) { }finally { DBClose.close(con,pstmt); } } public boolean checkPassword(VO vo, int idx) { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; ResultSet rs = null; boolean ch = false; try { sql = "SELECT NUM FROM board1 where NUM=? and PASSWORD=?"; pstmt = con.prepareStatement(sql); pstmt.setInt(1, idx); pstmt.setString(2, vo.getPassword()); rs = pstmt.executeQuery(); if(rs.next()) { ch = true; } else { ch = false; } }catch(Exception e) { }finally { DBClose.close(con,pstmt,rs); } return ch; } public void delete(int idx) { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; try { sql = "DELETE FROM board1 WHERE NUM=?"; pstmt = con.prepareStatement(sql); pstmt.setInt(1, idx); pstmt.executeUpdate(); }catch(Exception e) { }finally { DBClose.close(con,pstmt); } } public void modify(VO vo, int idx) { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; try { sql = "UPDATE board1 SET TITLE=?, MEMO=? where NUM=?"; pstmt = con.prepareStatement(sql); pstmt.setString(1, pasing(vo.getTitle())); pstmt.setString(2, pasing(vo.getMemo())); pstmt.setInt(3, idx); pstmt.executeUpdate(); }catch(Exception e) { }finally { DBClose.close(con,pstmt); } } public void UpdateStep(int ref, int step) { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; try { sql = "UPDATE board1 SET STEP=STEP+1 where REF=? and STEP>?"; pstmt = con.prepareStatement(sql); pstmt.setInt(1, ref); pstmt.setInt(2, step); pstmt.executeUpdate(); }catch(Exception e) { }finally { DBClose.close(con,pstmt); } } public void insertReply(VO vo, int ref, int indent, int step) { Connection con = dbconnect.getConnection(); PreparedStatement pstmt = null; try { sql = "INSERT INTO board1(USERNAME, PASSWORD, TITLE, MEMO, REF, INDENT, STEP) "+ "VALUES(?,?,?,?,?,?,?)"; pstmt = con.prepareStatement(sql); pstmt.setString(1, pasing(vo.getName())); pstmt.setString(2, pasing(vo.getPassword())); pstmt.setString(3, pasing(vo.getTitle())); pstmt.setString(4, pasing(vo.getMemo())); pstmt.setInt(5, ref); pstmt.setInt(6, indent+1); pstmt.setInt(7, step+1); pstmt.execute(); }catch(Exception e) { }finally { DBClose.close(con,pstmt); } } }

이곳에 특이점은 list.jsp를 위한 ArrayList<T> (제네릭스)를 사용한것과 VO클래스를 접근하는 내용과 pasing을 이용한 한글화 처리가 있겠습니다.

나머지는 이미 사용한 코드이므로 설명은 딱히 필요 없을 것 같습니다. 설명이 필요하신 분은 덧글으로 남겨주세요.





Posted by 세이나린
,

이번엔 열어놓은 값들을 닫는 DBClose 클래스를 만들어 보도록 하겠습니다.


DBClose.java


package board;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBClose {
	public static void close(Connection con, PreparedStatement pstmt, ResultSet rs) {
		try {
			try {
				if(rs!=null) { rs.close(); rs=null;}
			}catch(Exception e) {}
			
			try {
				if(pstmt!=null) { pstmt.close(); pstmt=null; }
			}catch(Exception e) {}
			
			try {
				if(con!=null) { con.close(); con=null; }
			}catch(Exception e) {}
		}catch(Exception e) {}
	}
	
	public static void close(Connection con, PreparedStatement pstmt) {
		try {
			try {
				if(pstmt!=null) { pstmt.close(); pstmt=null; }
			}catch(Exception e) {}
			
			try {
				if(con!=null) { con.close(); con=null; }
			}catch(Exception e) {}
		}catch(Exception e) {}
	}


	public static void close(Connection con, Statement stmt) {
		try {
			try {
				if(stmt!=null) { stmt.close(); stmt=null; }
			}catch(Exception e) {}
		
			try {
				if(con!=null) { con.close(); con=null; }
			}catch(Exception e) {}
		}catch(Exception e) {}
	}
	
	public static void close(Connection con) {
		try {
			try {
				if(con!=null) { con.close(); con=null; }
			}catch(Exception e) {}
		}catch(Exception e) {}
	}
	
	public static void close(PreparedStatement pstmt) {
		try {
			try {
				if(pstmt!=null) { pstmt.close(); pstmt=null; }
			}catch(Exception e) {}
		
		}catch(Exception e) {}
	}
	
	public static void close(Statement stmt) {
		try {
			try {
				if(stmt!=null) { stmt.close(); stmt=null; }
			}catch(Exception e) {}
		
		}catch(Exception e) {}
	}
	
	public static void close(ResultSet rs) {
		try {
			try {
				if(rs!=null) { rs.close(); rs=null; }
			}catch(Exception e) {}

		}catch(Exception e) {}
	}
	
	public static void close(PreparedStatement pstmt, ResultSet rs) {
		try {
			try {
				if(rs!=null) { rs.close(); rs=null; }
			}catch(Exception e) {}
			
			try {
				if(pstmt!=null) { pstmt.close(); pstmt=null; }
			}catch(Exception e) {}

		}catch(Exception e) {}
	} 

}

인자값에 따라 모든 경우의 수에 맞게 close메소드를 오버로딩 시킨 파일입니다.

특별히 설명할 부분은 없을것 같습니다.



Posted by 세이나린
,