개발자입니다
IT BANK) JSP 17일차 - JSP(회원관리 페이지(SQL)2) 본문
SQL 아이디, 비번 바로 입력하는 방법
sqlplus oracle/oracle
row 개수 세기
SELECT COUNT(*) FROM session_quiz
// memeberInfo.jsp 수정
<%@page import="session_quiz.MemberDAO"%>
<%@page import="session_quiz.MemberDTO"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>memberInfo</title>
</head>
<body>
<%@ include file="/session_quiz/header.jsp" %>
<div align="center">
<h1>회원 목록</h1>
<%
String cp = request.getParameter("currentPage");
int currentPage = 0;
try{
currentPage = Integer.parseInt(cp);
}catch(Exception e){
currentPage = 1;
}
if(currentPage < 1)
currentPage = 1;
// 한 페이지에 출력할 회원 최대수
int pageBlock = 3;
/*
currentPage = 1;
end = currentPage(1) * pageBlock(3);
begin = end(3) - pageBlock(3) + 1;
currentPage = 2;
end = currentPage(2) * pageBlock(3);
begin = end(6) - pageBlock(3) + 1;
*/
int end = currentPage * pageBlock;
int begin = end - pageBlock + 1;
MemberDAO memberDao = new MemberDAO();
ArrayList<MemberDTO> members = memberDao.list(begin, end); // 수정
if(members.isEmpty() == true){
out.print("<h1> 등록된 데이터가 존재하지 않습니다. </h1>");
return;
}else{
%>
<table border=1>
<thead>
<tr>
<th>아이디</th>
<th>이름</th>
<th>전화번호</th>
</tr>
</thead>
<tbody>
<%for(MemberDTO member : members){ %>
<tr>
<td onclick="location.href='userInfo.jsp?id=<%=member.getId()%>'">
<%=member.getId() %>
</td>
<td><%=member.getUserName() %></td>
<td><%=member.getMobile() %></td>
</tr>
<%} %>
</tbody>
</table>
<!-- [이전] 1 2 3 [다음] 링크 활성화 수정 -->
<%
int totalCount = memberDao.count();
int totalPage = totalCount / pageBlock;
if(totalCount % pageBlock > 0)
totalPage++;
if(currentPage <= 1){
out.print("<a href='#'>[이전] </a>");
}else{
int tmp = currentPage - 1;
out.print("<a href='memberInfo.jsp?currentPage="+tmp+"'>[이전] </a>");
}
/*
총 데이터의 수 : 10
한 페이지에 출력할 데이터의 수 : 3
총 페이지 : 10 / 3 = 3, 3 + 1 = 4pages
총 데이터의 수 : 9
한 페이지에 출력할 데이터의 수 : 3
총 페이지 : 9 / 3 = 3, 3pages
총 데이터의 수 : DB에서 조회한 결과를 totalCount 변수에 담아서 사용
한 페이지에 출력할 데이터의 수 : pageBlock(3)
총 페이지의 수
: totalPage = totalCount / pageBlock
: if(totalCount % pageBlock > 0) totalPage++;
*/
// <!-- page별 목록 보기 추가 -->
// <a href="#">[이전] </a>
// <a href="memberInfo.jsp?currentPage=1"> 1 </a>
// <a href="#"> 2 </a>
// <a href="memberInfo.jsp?currentPage=3"> 3 </a>
// <a href="#"> [다음]</a>
// <!-- 추가 종료 -->
// for문으로 작성
for(int i = 1; i <= totalPage; i++){
if(i == currentPage){
out.print("<b> " + i + "</b>");
}else{
out.print("<a href='memberInfo.jsp?currentPage=" + i + "'> " + i + " </a>");
}
}
if(currentPage >= totalPage){
out.print("<a href='#'> [다음] </a>");
}else{
int tmp = currentPage + 1;
out.print("<a href='memberInfo.jsp?currentPage="+tmp+"'> [다음] </a>");
}
%>
<!-- 추가 종료 -->
<%} %>
</div>
<%@ include file="/session_quiz/footer.jsp" %>
</body>
</html>// memberDAO.java 수정
// 수정
public ArrayList<MemberDTO> list(int begin, int end){
// String sql = "SELECT * FROM session_quiz";
String sql = "SELECT A.* FROM (SELECT ROWNUM rn, id, username, mobile FROM session_quiz)A WHERE rn >= ? AND rn <= ?";
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<MemberDTO> members = new ArrayList<>();
try {
ps = con.prepareStatement(sql);
ps.setInt(1, begin);
ps.setInt(2, end);
rs = ps.executeQuery();
while(rs.next()) {
MemberDTO member = new MemberDTO();
member.setId(rs.getString("id"));
member.setUserName(rs.getString("username"));
member.setMobile(rs.getString("mobile"));
members.add(member);
}
} catch(Exception e) {
e.printStackTrace();
}
return members;
}
// 추가
public int count() {
String sql = "SELECT COUNT(*) as cnt FROM session_quiz";
PreparedStatement ps = null;
ResultSet rs = null;
int cnt = 0;
try {
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
if(rs.next()) {
cnt = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
}
return cnt;
}
■ 게시판 생성
-SQL DB 생성
*PRIMARY KEY : 각 행을 식별하는 고유 키. 필수는 아니며 숫자, 문자 등 가능하다.
*not null : 해당 필드는 null로 설정할 수 없다.
*SEQUENCE 생성 후 INSERT시 .nextval 하면 행마다 1씩 증가한 값 넣을 수 있다.
SQL> CREATE TABLE session_quiz_board(
2 num number(10) PRIMARY KEY not null,
3 id varchar2(20) not null,
4 title varchar2(100),
5 content varchar2(300),
6 filename varchar2(200),
7 writetime varchar2(20),
8 hit number(10)
9 );
Table created.
SQL> CREATE SEQUENCE session_board_seq;
Sequence created.
SQL> commit;
Commit complete.
데이터 INSERT 할때 session_board_seq.nextval 입력시 번호 매김
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'admin', '게시글 제목1', '게시글 내용1', 'test.txt', '2022-09-01', 0);
1 row created.
SELECT * FROM session_quiz_board;
NUM ID
---------- ----------------------------------------
TITLE
----------------------------------------------------------------------------------------------------
CONTENT
----------------------------------------------------------------------------------------------------
FILENAME
----------------------------------------------------------------------------------------------------
WRITETIME HIT
---------------------------------------- ----------
1 admin
게시글 제목1
게시글 내용1
NUM ID
---------- ----------------------------------------
TITLE
----------------------------------------------------------------------------------------------------
CONTENT
----------------------------------------------------------------------------------------------------
FILENAME
----------------------------------------------------------------------------------------------------
WRITETIME HIT
---------------------------------------- ----------
test.txt
2022-09-01 0
-quiz
게시판을 코딩하라
SQL에서 다음과 같이 입력해 데이터 추가한다.
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'admin', '게시글 제목1', '게시글 내용1', 'test1.txt', '2022-09-01', 0);
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'admin', '게시글 제목2', '게시글 내용2', 'test2.txt', '2022-09-02', 0);
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'user1', '게시글 제목3', '게시글 내용3', 'user1.txt', '2022-09-03', 0);
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'user1', '게시글 제목4', '게시글 내용4', 'user1.txt', '2022-09-04', 0);
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'user2', '게시글 제목5', '게시글 내용5', 'user2.txt', '2022-09-05', 0);
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'user2', '게시글 제목6', '게시글 내용6', 'user2.txt', '2022-09-06', 0);
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'test1', '게시글 제목7', '게시글 내용7', 'test1.txt', '2022-09-07', 0);
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'test1', '게시글 제목8', '게시글 내용8', 'test1.txt', '2022-09-08', 0);
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'test2', '게시글 제목9', '게시글 내용9', 'test2.txt', '2022-09-09', 0);
INSERT INTO session_quiz_board VALUES(session_board_seq.nextval, 'test2', '게시글 제목10', '게시글 내용10', 'test2.txt', '2022-09-10', 0);// BoardDTO.java
package practice.session_quiz;
public class BoardDTO {
// 게시글번호, 작성작, 제목, 내용, 파일이름, 게시글생성일, 조회수
private int num;
private String id;
private String title;
private String content;
private String fileName;
private String writeTime;
private int hit;
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String getWriteTime() {
return writeTime;
}
public void setWriteTime(String writeTime) {
this.writeTime = writeTime;
}
public int getHit() {
return hit;
}
public void setHit(int hit) {
this.hit = hit;
}
}// BoardDAO.java
package session_quiz;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
public class BoardDAO {
private Connection con;
public BoardDAO() {
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user= "oracle";
String password= "oracle";
try {
Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
}
public void disconnection() {
try {
if(con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public ArrayList<BoardDTO> list(int begin, int end){
// String sql = "SELECT A.* FROM (SELECT ROWNUM rn, num, id, title, writetime, hit FROM session_quiz_board)A WHERE rn >= ? AND rn <= ?";
String sql = "SELECT B.* FROM "
+ "(SELECT ROWNUM rn, A.* FROM "
+ "(SELECT num, id, title, writetime, hit FROM session_quiz_board ORDER BY num DESC)A"
+ ")B "
+ "WHERE rn >= ? AND rn <= ?";
PreparedStatement ps = null;
ResultSet rs = null;
ArrayList<BoardDTO> boards = new ArrayList<>();
try {
ps = con.prepareStatement(sql);
ps.setInt(1, begin);
ps.setInt(2, end);
rs = ps.executeQuery();
while(rs.next()) {
BoardDTO board = new BoardDTO();
board.setNum( rs.getInt("num") );
board.setId(rs.getString("id") );
board.setTitle(rs.getString("title"));
board.setWriteTime(rs.getString("writetime"));
board.setHit(rs.getInt("hit"));
boards.add(board);
}
} catch (Exception e) {
e.printStackTrace();
}
return boards;
}
public int count(){
String sql = "SELECT count(*) as cnt FROM session_quiz_board";
PreparedStatement ps = null;
ResultSet rs = null;
int cnt = 0;
try {
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
if(rs.next()) {
cnt = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
}
return cnt;
}
}
// boardForm.jsp
<%@page import="java.util.ArrayList"%>
<%@page import="session_quiz.BoardDAO"%>
<%@page import="session_quiz.BoardDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>BoardForm</title>
</head>
<body>
<%@ include file="/session_quiz/header.jsp" %>
<%
String cp = request.getParameter("currentPage");
int currentPage = 0;
try{
currentPage = Integer.parseInt(cp);
}catch(Exception e){
currentPage = 1;
}
if(currentPage < 1)
currentPage = 1;
int pageBlock = 3;
int end = currentPage * pageBlock;
int begin = end - pageBlock + 1;
BoardDAO boardDao = new BoardDAO();
ArrayList<BoardDTO> boards = boardDao.list(begin, end);
if(boards.isEmpty() == true){
out.print("<h1> 등록된 데이터가 존재하지 않습니다. </h1>");
return;
}else{
%>
<table border=1 align="center">
<tr>
<th width="50">No.</th>
<th width="250">제목</th>
<th width="100">작성자</th>
<th width="100">작성일</th>
<th width="50">조회수</th>
</tr>
<%for(BoardDTO board : boards){ %>
<tr>
<td><%=board.getNum() %></td>
<td><%=board.getTitle() %></td>
<td><%=board.getId() %></td>
<td><%=board.getWriteTime() %></td>
<td><%=board.getHit() %></td>
</tr>
<%} %>
<tr>
<td colspan=4>
<%
int totalCount = boardDao.count();
boardDao.disconnection();
int totalPage = totalCount / pageBlock;
if(totalCount % pageBlock > 0)
totalPage++;
if(currentPage <= 1){
out.print("<a href='#'>[이전] </a>");
}else{
int tmp = currentPage - 1;
out.print("<a href='boardForm.jsp?currentPage="+tmp+"'>[이전] </a>");
}
for(int i = 1; i <= totalPage; i++){
if(i == currentPage){
out.print("<b> " + i + "</b>");
}else{
out.print("<a href='boardForm.jsp?currentPage=" + i + "'> " + i + " </a>");
}
}
if(currentPage >= totalPage){
out.print("<a href='#'> [다음] </a>");
}else{
int tmp = currentPage + 1;
out.print("<a href='boardForm.jsp?currentPage="+tmp+"'> [다음] </a>");
}
%>
</td>
<td>
<input type="button" value="글쓰기" onclick="location.href='boardWrite.jsp'">
</td>
</tr>
</table>
<%} %>
<%@ include file="/session_quiz/footer.jsp" %>
</body>
</html>
게시글 순서를 최신 글을 가장 앞으로 하도록 한다. (SQL 명령문)
SELECT B.* FROM (SELECT ROWNUM rn, A.* FROM (SELECT num, title FROM session_quiz_board ORDER BY num DESC)A)B WHERE rn >= 1 AND rn <= 3;
RN NUM
---------- ----------
TITLE
----------------------------------------------------------------------------------------------------
1 10
게시글 제목10
2 9
게시글 제목9
3 8
게시글 제목8
DB에서 모든 데이터를 가져와서 ArrayList에 담은 다음에 행번호 순으로 보여주는 방법도 있다. (코딩 연습)
'JSP > IT BANK - JSP' 카테고리의 다른 글
| IT BANK) JSP 19일차 - JSP(회원관리 페이지(SQL)2) (0) | 2022.10.04 |
|---|---|
| IT BANK) JSP 18일차 - JSP(회원관리 페이지(SQL)2) (0) | 2022.09.30 |
| IT BANK) JSP 16일차 - JSP(회원관리 페이지(SQL)2) (0) | 2022.09.28 |
| IT BANK) JSP 15일차 - JSP(회원관리 페이지(SQL)2) (0) | 2022.09.27 |
| IT BANK) JSP 14일차 - JSP(회원관리 페이지(SQL)) (0) | 2022.09.26 |
'JSP/IT BANK - JSP' Related Articles
more
