JSP 게시판 만들기 - 목록
페이지 정보
본문
[ 디비 테이블 ]
idx int auto increment
name nvarchar(30)
passowrd nvarchar(30)
title nvarchar(100)
memo text
time datetime now()
hit int
ref int
indent int
step int
[ list.jsp ]
<%@ page contentType="text/html; charset=utf-8" pageEncoding="utf-8" language="java" import="java.sql.*" errorPage="" %>
<%@ page import="java.sql.*,java.text.SimpleDateFormat,java.util.Date"%>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:디비네임";
String id = "디비아이디";
String pass = "디비패스워드";
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>
<html>
<head>
<meta charset="utf-8">
<title>게시판</title>
</head>
<body>
<%
int total = 0;
try {
Connection conn = DriverManager.getConnection(url,id,pass); //DB연결
Statement stmt = conn.createStatement(); //Statement타입의 객체생성
Statement stmt1 = conn.createStatement();
String sql = "";
String sqlCount = "select count(*) from board";// DB내의 자료개수를 찾는 SQL문
ResultSet rs = stmt.executeQuery(sqlCount); // DB실행
if(rs.next()){ // rs.next()의 반환값은 true or false입니다. 찾는결과가 있으면 true
total = rs.getInt(1); // SELECT문의 첫번째 필드 여기선 COUNT(*)
}
int sort=1;
String sqlSort = "select idx from board order by ref desc, step asc";
rs = stmt.executeQuery(sqlSort);
while(rs.next()){
int stepNum = rs.getInt(1);
sql = "update board set step2=" + sort + " where idx=" +stepNum;
stmt1.executeUpdate(sql);
sort++;
}
allPage = (int)Math.ceil(total/(double)ROWSIZE);
if(endPage > allPage) {
endPage = allPage;
}
out.print("총 게시물 : " + total + "개"); // 게시물수 출력
String sqlList = "select idx, name, title, time, hit, indent from board1 where step2 >="+start + " and step2 <= "+ end +" order by step2 asc";
rs = stmt.executeQuery(sqlList); // DB실행
%>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<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) { // total 즉, 자료가 없다면
%>
<tr align="center" bgcolor="#FFFFFF" height="30">
<td colspan="7">등록된 글이 없습니다.</td>
</tr>
<%
} else { // total이 0이 아닌 즉 자료가 1개이상 있다면
while(rs.next()) { // while이라는 반복문으로 자료를 찾습니다. rs.next()는 다음라인으로 커서 이동
int idx = rs.getInt(1); // 1은 첫번째 즉 num값을 idx라는 변수에 대입
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> </td>
<td><%=idx %></td>
<td align="left">
<%
for(int j=0;j<indent;j++){
%> <%
}
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"><%=time %></td>
<td align="center"><%=hit %></td>
<td> </td>
</tr>
<tr height="1" bgcolor="#D2D2D2"><td colspan="7"></td></tr>
<%
}
}
rs.close(); // rs객체 반환
stmt.close(); // stmt객체 반환
conn.close(); // conn객체 반환
} catch(SQLException e) {
out.println( e.toString() ); //에러 날경우 에러출력
}
%>
<tr height="1" bgcolor="#82B5DF"><td colspan="7"></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 colspan="7"><input type=button value="글쓰기"></td>
</tr>
</table>
</body>
</html>
자료출처
http://seinarin.tistory.com/3
idx int auto increment
name nvarchar(30)
passowrd nvarchar(30)
title nvarchar(100)
memo text
time datetime now()
hit int
ref int
indent int
step int
[ list.jsp ]
<%@ page contentType="text/html; charset=utf-8" pageEncoding="utf-8" language="java" import="java.sql.*" errorPage="" %>
<%@ page import="java.sql.*,java.text.SimpleDateFormat,java.util.Date"%>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:디비네임";
String id = "디비아이디";
String pass = "디비패스워드";
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>
<html>
<head>
<meta charset="utf-8">
<title>게시판</title>
</head>
<body>
<%
int total = 0;
try {
Connection conn = DriverManager.getConnection(url,id,pass); //DB연결
Statement stmt = conn.createStatement(); //Statement타입의 객체생성
Statement stmt1 = conn.createStatement();
String sql = "";
String sqlCount = "select count(*) from board";// DB내의 자료개수를 찾는 SQL문
ResultSet rs = stmt.executeQuery(sqlCount); // DB실행
if(rs.next()){ // rs.next()의 반환값은 true or false입니다. 찾는결과가 있으면 true
total = rs.getInt(1); // SELECT문의 첫번째 필드 여기선 COUNT(*)
}
int sort=1;
String sqlSort = "select idx from board order by ref desc, step asc";
rs = stmt.executeQuery(sqlSort);
while(rs.next()){
int stepNum = rs.getInt(1);
sql = "update board set step2=" + sort + " where idx=" +stepNum;
stmt1.executeUpdate(sql);
sort++;
}
allPage = (int)Math.ceil(total/(double)ROWSIZE);
if(endPage > allPage) {
endPage = allPage;
}
out.print("총 게시물 : " + total + "개"); // 게시물수 출력
String sqlList = "select idx, name, title, time, hit, indent from board1 where step2 >="+start + " and step2 <= "+ end +" order by step2 asc";
rs = stmt.executeQuery(sqlList); // DB실행
%>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
<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) { // total 즉, 자료가 없다면
%>
<tr align="center" bgcolor="#FFFFFF" height="30">
<td colspan="7">등록된 글이 없습니다.</td>
</tr>
<%
} else { // total이 0이 아닌 즉 자료가 1개이상 있다면
while(rs.next()) { // while이라는 반복문으로 자료를 찾습니다. rs.next()는 다음라인으로 커서 이동
int idx = rs.getInt(1); // 1은 첫번째 즉 num값을 idx라는 변수에 대입
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> </td>
<td><%=idx %></td>
<td align="left">
<%
for(int j=0;j<indent;j++){
%> <%
}
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"><%=time %></td>
<td align="center"><%=hit %></td>
<td> </td>
</tr>
<tr height="1" bgcolor="#D2D2D2"><td colspan="7"></td></tr>
<%
}
}
rs.close(); // rs객체 반환
stmt.close(); // stmt객체 반환
conn.close(); // conn객체 반환
} catch(SQLException e) {
out.println( e.toString() ); //에러 날경우 에러출력
}
%>
<tr height="1" bgcolor="#82B5DF"><td colspan="7"></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 colspan="7"><input type=button value="글쓰기"></td>
</tr>
</table>
</body>
</html>
자료출처
http://seinarin.tistory.com/3
댓글목록
등록된 댓글이 없습니다.