본문 바로가기
JAVA

[60일차] 자료실 session사용 로그인/로그아웃 만들기/빅데이터활용 페이지나누기

ggosoon 2022. 8. 16. 18:03

교재 p.460

jsp 파일 만들기

 

top.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
    
<%
	String id= (String)session.getAttribute("id"); //login_ok.jsp에서 보낸 id를 변수로 받아오기
	//id가 오브젝트로 넘어오기 때문에 String으로 강제 형변환을 해주어야 하는 것이다 
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style type="text/css">
header{
background-color:black;
color:white;
height:70px;
line-height:70px;
text-align:center;
}
nav{
background-color:gray;
color:white;
height:40px;
line-height:40px;
}
section{
background-color:#d3d3d3;
color:white;
min-height:500px;
}
footer{
background-color:black;
color:white;
height:50px;
line-height:50px;
text-align:center;
font-size:12px;
}
a:link, a:visited{
color:white;
text-decoration:none;
}
</style>
</head>
<body>
<header>
<h2>(과정평가형 정보처리산업기사) 지역구의원투표 프로그램 ver2022-08</h2>
</header>
<nav>
&emsp;
후보조회 &emsp; 
투표하기&emsp;
투표검수조회&emsp;
후보자등수&emsp;

<%if(id=="admin"){ %> <!-- 위에서 받아온 변수 id가 어떻게 받아오는지 알아야함. equal로 if문을작성하면 변수가 null로 들어올때(첫화면실행시) 오류남 -->
후보등록 &emsp;
후보사진등록 &emsp;
<%} %>

<%if(id=="admin"){ %>
<a href=logout.jsp>로그아웃</a> &emsp;
<%}else{ %>
<a href=login.jsp>로그인</a> &emsp;
<%} %>

<a href=index.jsp>홈으로</a>&emsp;
</nav>

bottom.jsp

<%@ page language="java" 
    pageEncoding="UTF-8"%>

    
    <footer>HRDKOREA Copyright &copy2015 All rights reserved. Human Resources Development Service of Korea</footer>
</body>
</html>

index.jsp

<%@ page language="java" 
    pageEncoding="UTF-8"%>
    
<%@ include file="top.jsp" %>

<section>
<br>
<div align=center></div>
<div>

</div>

<br>
</section>

<%@ include file="bottom.jsp" %>

 

login.jsp

<%@ page language="java" 
    pageEncoding="UTF-8"%>
    
<%@ include file="top.jsp" %>

<section>
<br>
<div align=center><h2>관리자 로그인</h2></div>
<div align=center>
<form action=login_ok.jsp method=get >
<table border=1>
<tr>
	<td>아이디</td><td><input type=text name=id></td>
</tr>
<tr>
	<td>비밀번호</td><td><input type=text name=pwd></td>
</tr>
<tr>
	<td colspan=2 align=center><input type=submit value="로그인"></td>
</tr>

</table>
</form>
</div>

<br>
</section>

<%@ include file="bottom.jsp" %>

 

login_ok.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String id= request.getParameter("id");
	String pwd= request.getParameter("pwd");
	
	if(id.equals("admin")){
		if(pwd.equals("1234")){
			System.out.println("로그인 성공");
			session.setAttribute("id", "admin");
			response.sendRedirect("index.jsp");
		}else{
			System.out.println("비밀번호가 일치하지않습니다!");
			response.sendRedirect("error.jsp");
		}
	}else{
		System.out.println("로그인 실패");
		response.sendRedirect("error.jsp");
	}
%>

 

error.jsp

<%@ page language="java" 
    pageEncoding="UTF-8"%>
    
<%@ include file="top.jsp" %>

<section>
<br>
<div align=center></div>
<div align=center><br><br>
<h1>로그인 실패 !!</h1>
</div>

<br>
</section>

<%@ include file="bottom.jsp" %>

 

logout.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	session.invalidate();//세션 다 삭제하기 
	
	response.sendRedirect("index.jsp");
	
%>

 

index.jsp 실행화면
로그인 화면
로그인하게 되면 안보이던  '후보등록, 후보사진등록,로그아웃' 메뉴가 보이게 된다

login_ok.jsp를 bean 으로 바꿔서 만들어보기 

인터페이스,bean클래스 만들기

package dbpkg;

public interface Login {
	String login(String id, String pwd); 
	

}
package dbpkg;

public class LoginBean implements Login{

	@Override
	public String login(String id, String pwd) {
		System.out.println("id:"+id);
		System.out.println("pwd:"+pwd);
		
		String str="";
		
		if(id.equals("admin")){
			if(pwd.equals("1234")){
				System.out.println("로그인 성공");
				str= "T";
			}else{
				System.out.println("비밀번호가 일치하지않습니다!");
				str="F";
			}
		}else{
			System.out.println("로그인 실패");
			str="F";
		}
		
		
		return str;
	}

}

java에서는 response나 session을 처리할 수 없어서 

그건 따로 login_ok.jsp 에서 처리해준다 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%@ page import="dbpkg.*" %>
<%
	String id= request.getParameter("id");
	String pwd= request.getParameter("pwd");

	Login login= new LoginBean();
	String str= login.login(id, pwd);

	if(str.equals("T")){
		session.setAttribute("id", "admin");
		response.sendRedirect("index.jsp");
	}else{
		response.sendRedirect("error.jsp");
	}
%>

 

여기서 login_ok.jsp 를 아예 없애고 싶으면 서블릿을 만들어야한다 

LoginController 서블릿 하나 만들어서 

package dbpkg;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 * Servlet implementation class LoginController
 */
@WebServlet("/LoginController")
public class LoginController extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LoginController() {
        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());
		
		HttpSession session = request.getSession(); // 서블릿에서 session을 사용하려면 이렇게 객체를 생성해줘야한다 
		
		String id= request.getParameter("id");
		String pwd= request.getParameter("pwd");

		Login login= new LoginBean();
		String str= login.login(id, pwd);

		if(str.equals("T")){
			session.setAttribute("id", "admin");
			response.sendRedirect("index.jsp");
		}else{
			response.sendRedirect("error.jsp");
		}
		
	}

	/**
	 * @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);
	}

}

login.jsp 에 액션부분 바꿔주기 <form action=LoginController method=get >

아래는 서블릿 response.sendRedirect 부분 다르게 작성

package dbpkg;

import java.io.IOException;

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;
import javax.servlet.http.HttpSession;

/**
 * Servlet implementation class LoginController
 */
@WebServlet("/LoginController")
public class LoginController extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LoginController() {
        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());
		
		HttpSession session = request.getSession(); // 서블릿에서 session을 사용하려면 이렇게 객체를 생성해줘야한다 
		
		String id= request.getParameter("id");
		String pwd= request.getParameter("pwd");

		Login login= new LoginBean();
		String str= login.login(id, pwd);
		String url="";

		if(str.equals("T")){
			session.setAttribute("id", "admin");
			url= "index.jsp";
		}else{
			url= "error.jsp";
		}
		
		RequestDispatcher dispatcher= request.getRequestDispatcher(url);
		dispatcher.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);
	}

}

 

서블릿은 jsp 파일을 대체하는 것 ! ui가 하나도 없는 jsp를 서블릿으로 사용하면 하나로 묶어서 사용가능하다

데이터베이스 연동해서 활용해보기 

 

오라클에서 테이블 만들어주고 insert 하기 

create table login(
    id varchar2(13) not null primary key,
    pwd varchar2(15));


insert into login(id,pwd) values('admin','1234');
insert into login(id,pwd) values('ppk','1234');

싱클톤패턴으로 디비커넥션 클래스 만들어주기

package dbpkg;

import java.sql.*;

public class DBConnection {
	Connection con = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;

	String url = "jdbc:oracle:thin:@//localhost:1521/xe";
	String userId = "system";
	String pass = "1234";
	
	private static DBConnection db = new DBConnection();
	
	private DBConnection() { }
	
	public static DBConnection getInstance() {
		return db;
	}
	
	public Connection getConnection() {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con = DriverManager.getConnection(url, userId, pass);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	public void close1(PreparedStatement stmt, Connection con) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void close2(ResultSet rs, PreparedStatement stmt, Connection con) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

 

로그인빈 수정하기 

package dbpkg;

import java.awt.geom.RectangularShape;
import java.sql.*;

public class LoginBean implements Login{
	private DBConnection dbConn=null;
	private Connection conn= null;
	private PreparedStatement pstmt= null;
	private ResultSet rs=null;
	
	
	public LoginBean() {
		dbConn= DBConnection.getInstance();
	}
	
	@Override
	public String login(String id, String pwd) {
		
		String str= "";
		try {
			conn = dbConn.getConnection();
			String SQL="select * from login where id =?";
			pstmt= conn.prepareStatement(SQL);
			pstmt.setString(1, id);
			rs= pstmt.executeQuery();
			
			if(rs.next()) {
				if(pwd.equals(rs.getString("pwd"))) {
					str="T";
					System.out.println("==>로그인성공");
				}else {
					str="F";
				}
			}else {
				str="F";
			}
			
		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			dbConn.close2(rs, pstmt, conn);
		}
		return str;
	}
}

 

이렇게 하고 실행하면 'admin,1234' 와 'ppk,1234' 둘 다 로그인 가능 

페이지나누기 (빅데이터)

 

오라클에서 테이블 만들기 

create table bigdata(
    idx number(6) not null primary key,
    title nvarchar2(30),
    name nvarchar2(10),
    age number(3),
    etc nvarchar2(50));


create sequence bigidx
increment by 1 start with 10001
maxvalue 999999;

insert into bigdata(idx,title,name,age,etc) values(BIGIDX.NEXTVAL,'빅데이터연습','마이키',17,'외눈박이');

 

인터페이스에 메소드 추가하기 

package dbpkg;

public interface Login {
	String login(String id, String pwd); 
	
	void bigDataInsert();
}

 

빈클래스에서 위에서 추가된 메소드에서 

난수발생시켜서 for문으로돌려 빅데이터값 받아서 추가하기 

package dbpkg;

import java.awt.geom.RectangularShape;
import java.sql.*;

public class LoginBean implements Login{
	private DBConnection dbConn=null;
	private Connection conn= null;
	private PreparedStatement pstmt= null;
	private ResultSet rs=null;
	
	
	public LoginBean() {
		dbConn= DBConnection.getInstance();
	}
	
	@Override
	public String login(String id, String pwd) {
		
		String str= "";
		try {
			conn = dbConn.getConnection();
			String SQL="select * from login where id =?";
			pstmt= conn.prepareStatement(SQL);
			pstmt.setString(1, id);
			rs= pstmt.executeQuery();
			
			if(rs.next()) {
				if(pwd.equals(rs.getString("pwd"))) {
					str="T";
					System.out.println("==>로그인성공");
				}else {
					str="F";
				}
			}else {
				str="F";
			}
			
		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			dbConn.close2(rs, pstmt, conn);
		}
		return str;
	}

	@Override
	public void bigDataInsert() {
		try {	
		conn= dbConn.getConnection();
		
		for(int i=1; i<=123; i++) {
		String [] titleArr= {"인생 공부하기","여행하기","등산하기","운동하기","전산공부하기","세계일주하기","휴식하기"};
		String [] nameArr= {"둘리","설리","마이키","부","또치","도우너","희동이"};
		String [] etcArr= {"ENTJ-지도자형","ISFP-성인군자형","INFJ-예언자형","ISTJ-세상의소금형","ESFP-사교적인유형","ENTJ-지도자형2","ISFP-성인군자형2"};
		
		int titleK=(int)(Math.random()*7);
		int nameK=(int)(Math.random()*7);
		int etcK=(int)(Math.random()*7);
		int ageK=(int)(Math.random()*20)+11; //11부터 20개 
		
		String title= titleArr[titleK];
		String name= nameArr[nameK];
		String etc= etcArr[etcK];
		
		String SQL= "insert into bigdata(idx,title,name,age,etc) values(BIGIDX.NEXTVAL,?,?,?,?)";
		pstmt=conn.prepareStatement(SQL);
		pstmt.setString(1, title);
		pstmt.setString(2, name);
		pstmt.setInt(3, ageK);
		pstmt.setString(4, etc);
		pstmt.executeUpdate();
		}
		
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			dbConn.close1(pstmt, conn);
		}
	}
}

 

인서트컨트롤러 서블릿 만들어서 웹 처리하기 

package dbpkg;

import java.io.IOException;

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 InsertController
 */
@WebServlet("/InsertController")
public class InsertController extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public InsertController() {
        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());
		
		Login service= new LoginBean();
		service.bigDataInsert();
		String url="insert_ok.jsp";
		
		RequestDispatcher dispatcher= request.getRequestDispatcher(url);
		dispatcher.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);
	}

}

 

top.jsp 에서 <a href="InsertController" >데이터입력</a> &emsp;  링크 걸어주고 

insert_ok.jsp 에 저장성공 화면 만들고 

<%@ page language="java" 
    pageEncoding="UTF-8"%>
    
<%@ include file="top.jsp" %>

<section>
<br>
<div align=center></div>
<div align=center><br><br>
<h1>저장성공 !!</h1>
</div>

<br>
</section>

<%@ include file="bottom.jsp" %>

 

index.jsp에서 실행시키면

데이터 입력 눌러주면

 

값이 들어가게 되고 오라클에서 확인해보면

247개 값 들어간것 확인할 수 있다

위에 title, name 잘못들어가서 이후에 수정해줌 ㅠ 

 

목록보기 만들기 

top.jsp 만들어주기

 

list.jsp

<%@ page language="java" 
    pageEncoding="UTF-8"%>
    
<%@ include file="top.jsp" %>

<%@ page import="dbpkg.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>

<%
	DBConnection DBCconn =DBConnection.getInstance();
	Connection conn= DBCconn.getConnection();
	String SQL= "select* from bigdata";
	PreparedStatement pstmt= conn.prepareStatement(SQL);
	ResultSet rs=pstmt.executeQuery();	
%>

<section>
<br>
<div align=center><h2>목록보기</h2></div>
<div align=center>
<table border=1>
<tr>
	<td>번호</td><td>제목</td><td>이름</td><td>나이</td><td>특이사항</td>
</tr>

<% while(rs.next()){ %>
<tr>
	<td><%=rs.getString("idx") %></td>
	<td><%=rs.getString("title") %></td>
	<td><%=rs.getString("name") %></td>
	<td><%=rs.getString("age") %></td>
	<td><%=rs.getString("etc") %></td>
</tr>
<%} %>

</table>
</div>

<br>
</section>

<%@ include file="bottom.jsp" %>

목록보기 누르면 확인가능

 

rownum는 1부터 시작이다 

쿼리문 

select rownum as rnum, K.* from
(select idx,title,name,age,etc from bigdata) K 
where rownum <= 10;

* 한번 delete하고해서 오라클에서는 idx 246부터 나옴 ㅠ

 

두번째 오라클 쿼리 

select Rownum, Q.* from
(
select rownum as rnum, K.* from
(select idx,title,name,age,etc from bigdata) K 
where rownum <= 10) Q 
where rnum >= 1;

 

11부터 20까지 실행 해보기

이름 설정해서 검색하기 

 

내림차순 정렬해서 보기 

select Rownum, Q.* from(
select rownum as rnum, K.* from
(select idx,title,name,age,etc from bigdata where name like '%마이키%' order by idx desc) K 
where rownum <= 20) Q 
where rnum >= 11;

 

상위 15개만 출력해보기 

<%@ page language="java" 
    pageEncoding="UTF-8"%>
    
<%@ include file="top.jsp" %>

<%@ page import="dbpkg.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>

<%
	DBConnection DBCconn =DBConnection.getInstance();
	Connection conn= DBCconn.getConnection();
	 String SQL = "select  rownum, Q.*   " ; 
     SQL = SQL + " from  " ; 
     SQL = SQL + "   (  " ; 
     SQL = SQL + "      select  rownum  as rnum, K.*  " ; 
     SQL = SQL + "      from   " ; 
     SQL = SQL + "      ( select idx,title,name,age,etc from bigdata  order  by  idx  desc ) K   " ; 
     SQL = SQL + "     where  rownum <= 15   " ; 
     SQL = SQL + "     )Q  " ; 
     SQL = SQL + "  where rnum >= 1  " ; 

					
	PreparedStatement pstmt= conn.prepareStatement(SQL);
	ResultSet rs=pstmt.executeQuery();	
%>

<section>
<br>
<div align=center><h2>목록보기</h2></div>
<div align=center>
<table border=1>
<tr>
	<td>Rownum</td><td>rnum</td>
	<td>번호</td><td>제목</td><td>이름</td><td>나이</td><td>특이사항</td>
</tr>

<% while(rs.next()){ %>
<tr>
	<td><%=rs.getString("rownum") %></td>
	<td><%=rs.getString("rnum") %></td>
	<td><%=rs.getString("idx") %></td>
	<td><%=rs.getString("title") %></td>
	<td><%=rs.getString("name") %></td>
	<td><%=rs.getString("age") %></td>
	<td><%=rs.getString("etc") %></td>
</tr>
<%} %>

</table>
</div>

<br>
</section>

<%@ include file="bottom.jsp" %>

 

 

 

설정 값(숫자) ? 로 받아보기 

<%@ page language="java" 
    pageEncoding="UTF-8"%>
    
<%@ include file="top.jsp" %>

<%@ page import="dbpkg.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>

<%
	int start ;
	if(request.getParameter("start")==null){
		start=1;
	}else{
		start= Integer.parseInt(request.getParameter("start"));
	}

	DBConnection DBCconn =DBConnection.getInstance();
	Connection conn= DBCconn.getConnection();
	 String SQL = "select  rownum, Q.*   " ; 
     SQL = SQL + " from  " ; 
     SQL = SQL + "   (  " ; 
     SQL = SQL + "      select  rownum  as rnum, K.*  " ; 
     SQL = SQL + "      from   " ; 
     SQL = SQL + "      ( select idx,title,name,age,etc from bigdata  order  by  idx  desc ) K   " ; 
     SQL = SQL + "     where  rownum <= ?   " ; 
     SQL = SQL + "     )Q  " ; 
     SQL = SQL + "  where rnum >= ?  " ; 

					
	PreparedStatement pstmt= conn.prepareStatement(SQL);
	pstmt.setInt(1,start+14);
	pstmt.setInt(2,start);
	ResultSet rs=pstmt.executeQuery();	
%>

<section>
<br>
<div align=center><h2>목록보기</h2></div>
<div align=center>
<table border=1>
<tr>
	<td>Rownum</td><td>rnum</td>
	<td>번호</td><td>제목</td><td>이름</td><td>나이</td><td>특이사항</td>
</tr>

<% while(rs.next()){ %>
<tr>
	<td><%=rs.getString("rownum") %></td>
	<td><%=rs.getString("rnum") %></td>
	<td><%=rs.getString("idx") %></td>
	<td><%=rs.getString("title") %></td>
	<td><%=rs.getString("name") %></td>
	<td><%=rs.getString("age") %></td>
	<td><%=rs.getString("etc") %></td>
</tr>
<%} %>

</table>
</div>

<br>
</section>

<%@ include file="bottom.jsp" %>

 

전체레코드수, 전체페이지수, 현재페이지수 찍어보기

 

<%@ page language="java" 
    pageEncoding="UTF-8"%>
    
<%@ include file="top.jsp" %>

<%@ page import="dbpkg.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>

<%
	int pageSize=15;
	
	int start ;
	if(request.getParameter("start")==null){
		start=1;
	}else{
		start= Integer.parseInt(request.getParameter("start"));
	}
	
	int nowPage=start / pageSize + 1 ;
	
	DBConnection DBCconn =DBConnection.getInstance();
	Connection conn= DBCconn.getConnection();
	
	String SQL_tc= "select count(*) tc from bigdata";
	PreparedStatement pstmt_tc= conn.prepareStatement(SQL_tc);
	ResultSet rs_tc=pstmt_tc.executeQuery();	
	rs_tc.next();
	int tc= rs_tc.getInt("tc");
	
	int pageCount = (int)(Math.ceil((double)tc/ pageSize));


	 String SQL = "select  rownum, Q.*   " ; 
     SQL = SQL + " from  " ; 
     SQL = SQL + "   (  " ; 
     SQL = SQL + "      select  rownum  as rnum, K.*  " ; 
     SQL = SQL + "      from   " ; 
     SQL = SQL + "      ( select idx,title,name,age,etc from bigdata  order  by  idx  desc ) K   " ; 
     SQL = SQL + "     where  rownum <= ?   " ; 
     SQL = SQL + "     )Q  " ; 
     SQL = SQL + "  where rnum >= ?  " ; 

					
	PreparedStatement pstmt= conn.prepareStatement(SQL);
	pstmt.setInt(1,start+pageSize-1);
	pstmt.setInt(2,start);
	ResultSet rs=pstmt.executeQuery();	
%>

<section>
<br>
<div align=center><h2>목록보기</h2> 전체레코드수: <%=tc %>/전체페이지수: <%=pageCount %>/현재페이지: <%=nowPage %></div>
<div align=center>
<table border=1>
<tr>
	<td>Rownum</td><td>rnum</td>
	<td>번호</td><td>제목</td><td>이름</td><td>나이</td><td>특이사항</td>
</tr>

<% while(rs.next()){ %>
<tr>
	<td><%=rs.getString("rownum") %></td>
	<td><%=rs.getString("rnum") %></td>
	<td><%=rs.getString("idx") %></td>
	<td><%=rs.getString("title") %></td>
	<td><%=rs.getString("name") %></td>
	<td><%=rs.getString("age") %></td>
	<td><%=rs.getString("etc") %></td>
</tr>
<%} %>

</table>
<% if(start==1){%>
이전&emsp;&emsp;
<%}else{ %>
<a href=list.jsp?start=<%=start-pageSize %>> <b>이전</b></a>
<%} %>
<%if(pageCount==nowPage){ %>
&emsp;&emsp;다음
<%}else{ %>
&emsp;&emsp;
<a href=list.jsp?start=<%=start+pageSize %>> <b>다음</b></a>
<%} %>
</div>

<br>
</section>

<%@ include file="bottom.jsp" %>

 

처음으로, 마지막페이지 추가 

<%@ page language="java" 
    pageEncoding="UTF-8"%>
    
<%@ include file="top.jsp" %>

<%@ page import="dbpkg.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>

<%
	int pageSize=15;
	
	int start ;
	if(request.getParameter("start")==null){
		start=1;
	}else{
		start= Integer.parseInt(request.getParameter("start"));
	}
	
	int nowPage=start / pageSize + 1 ;
	
	DBConnection DBCconn =DBConnection.getInstance();
	Connection conn= DBCconn.getConnection();
	
	String SQL_tc= "select count(*) tc from bigdata";
	PreparedStatement pstmt_tc= conn.prepareStatement(SQL_tc);
	ResultSet rs_tc=pstmt_tc.executeQuery();	
	rs_tc.next();
	int tc= rs_tc.getInt("tc");
	
	int pageCount = (int)(Math.ceil((double)tc/ pageSize));


	 String SQL = "select  rownum, Q.*   " ; 
     SQL = SQL + " from  " ; 
     SQL = SQL + "   (  " ; 
     SQL = SQL + "      select  rownum  as rnum, K.*  " ; 
     SQL = SQL + "      from   " ; 
     SQL = SQL + "      ( select idx,title,name,age,etc from bigdata  order  by  idx  desc ) K   " ; 
     SQL = SQL + "     where  rownum <= ?   " ; 
     SQL = SQL + "     )Q  " ; 
     SQL = SQL + "  where rnum >= ?  " ; 

					
	PreparedStatement pstmt= conn.prepareStatement(SQL);
	pstmt.setInt(1,start+pageSize-1);
	pstmt.setInt(2,start);
	ResultSet rs=pstmt.executeQuery();	
%>

<section>
<br>
<div align=center><h2>목록보기</h2> 전체레코드수: <%=tc %>/전체페이지수: <%=pageCount %>/현재페이지: <%=nowPage %></div>
<div align=center>
<table border=1>
<tr>
	<td>Rownum</td><td>rnum</td>
	<td>번호</td><td>제목</td><td>이름</td><td>나이</td><td>특이사항</td>
</tr>

<% while(rs.next()){ %>
<tr>
	<td><%=rs.getString("rownum") %></td>
	<td><%=rs.getString("rnum") %></td>
	<td><%=rs.getString("idx") %></td>
	<td><%=rs.getString("title") %></td>
	<td><%=rs.getString("name") %></td>
	<td><%=rs.getString("age") %></td>
	<td><%=rs.getString("etc") %></td>
</tr>
<%} %>

</table>
<a href=list.jsp?start=1>처음으로 </a>&emsp;&emsp;
<% if(start==1){%>
이전&emsp;&emsp;
<%}else{ %>
<a href=list.jsp?start=<%=start-pageSize %>> <b>이전</b></a>
<%} %>
<%if(pageCount==nowPage){ %>
&emsp;&emsp;다음
<%}else{ %>
&emsp;&emsp;
<a href=list.jsp?start=<%=start+pageSize %>> <b>다음</b></a>
<%} %>
<%int endpage= (pageCount-1)*pageSize+1; %>
&emsp;&emsp; <a href=list.jsp?start=<%=endpage%>>마지막으로</a>
</div>

<br>
</section>

<%@ include file="bottom.jsp" %>

마지막으로 누른 화면

 

제목, 이름으로 검색하기 창 만들기 

<%@page import="java.net.URLEncoder"%>
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ include file="top.jsp" %>
<section>
<br>
  <div align="center" > <h2>  </h2> </div>
  <div align="center" > 
<%@ page import="dbpkg.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%

  int pageSize=15; 

   
  int start  ;
  if ( request.getParameter("start") == null ) {
	  start = 1 ; 
  } else {
	  start = Integer.parseInt(request.getParameter("start"));
  }

  int nowPage = start / pageSize + 1 ;
  
  DBConnection DBConn = DBConnection.getInstance();
  Connection conn=DBConn.getConnection();
  PreparedStatement pstmt_tc =  null;
  PreparedStatement pstmt =null;  
  
  String ch1 = request.getParameter("ch1");
  String ch2 = request.getParameter("ch2");
  
  if ( ch1 == null || ch2 == null || ch2 =="" || ch1.equals("null") ||  ch2.equals("null") ){
	  String SQL_tc = " select count(*) tc from bigdata ";
	  pstmt_tc = conn.prepareStatement(SQL_tc);
  }else if(ch1.equals("title")) {
	  String SQL_tc = " select count(*) tc from bigdata where title like ? "; 
	  pstmt_tc = conn.prepareStatement(SQL_tc);
	  pstmt_tc.setString(1, "%" +ch2 +"%");
  }else if(ch1.equals("name")){
	  String SQL_tc = " select count(*) tc from bigdata  where name like ? ";
	  pstmt_tc = conn.prepareStatement(SQL_tc);
	  pstmt_tc.setString(1, "%" + ch2 +"%");	  
  }

  ResultSet rc_tc = pstmt_tc.executeQuery();
  rc_tc.next();
  int tc = rc_tc.getInt("tc");
  
  int  totalPage =(int) (Math.ceil((double) tc / pageSize));
  
  if ( ch1 == null || ch2 == null || ch2 =="" || ch1.equals("null") ||  ch2.equals("null") ){

  String SQL = "select  rownum, Q.*   " ; 
         SQL = SQL + " from  " ; 
         SQL = SQL + "   (  " ; 
         SQL = SQL + "      select  rownum  as rnum, K.*  " ; 
         SQL = SQL + "      from   " ; 
         SQL = SQL + "      ( select idx,title,name,age,etc from bigdata  order  by  idx  desc ) K   " ; 
         SQL = SQL + "     where  rownum <= ?   " ; 
         SQL = SQL + "     )Q  " ; 
         SQL = SQL + "  where rnum >= ?  " ; 

  pstmt = conn.prepareStatement(SQL);
  pstmt.setInt(1, start + pageSize - 1);
  pstmt.setInt(2, start);
  } else if(ch1.equals("title")) {
	  String SQL = "select  rownum, Q.*   " ; 
      SQL = SQL + " from  " ; 
      SQL = SQL + "   (  " ; 
      SQL = SQL + "      select  rownum  as rnum, K.*  " ; 
      SQL = SQL + "      from   " ; 
      SQL = SQL + "      ( select idx,title,name,age,etc from bigdata where title like ? order  by  idx  desc ) K   " ; 
      SQL = SQL + "     where  rownum <= ?   " ; 
      SQL = SQL + "     )Q  " ; 
      SQL = SQL + "  where rnum >= ?  " ; 

	pstmt = conn.prepareStatement(SQL);
	pstmt.setString(1,"%" +ch2 +"%" );
	pstmt.setInt(2, start + pageSize - 1);
	pstmt.setInt(3, start); 

  } else if(ch1.equals("name")) {
	  String SQL = "select  rownum, Q.*   " ; 
      SQL = SQL + " from  " ; 
      SQL = SQL + "   (  " ; 
      SQL = SQL + "      select  rownum  as rnum, K.*  " ; 
      SQL = SQL + "      from   " ; 
      SQL = SQL + "      ( select idx,title,name,age,etc from bigdata  where name like ?   order  by  idx  desc ) K   " ; 
      SQL = SQL + "     where  rownum <= ?   " ; 
      SQL = SQL + "     )Q  " ; 
      SQL = SQL + "  where rnum >= ?  " ; 

	pstmt = conn.prepareStatement(SQL);
	pstmt.setString(1,"%" +ch2 +"%" );
	pstmt.setInt(2, start + pageSize - 1);
	pstmt.setInt(3, start); 
  }
  
  ResultSet rs = pstmt.executeQuery();
  %>
  전체레코드 수 : <%=tc %>  // 전체페이지수 <%=totalPage %> / <%=nowPage %> 현재페이지 
  <br> <br>
  <table  border=1>
  <tr>
    <td>Rownum</td><td>rnum</td><td>번호</td><td>제목</td><td>이름</td>
    <td>나이</td><td>특이사항</td>
  </tr> 
  <% 
  while(rs.next()){
  %>
  <tr>
    <td><%=rs.getString("rownum") %></td>
    <td><%=rs.getString("rnum") %></td>
    <td><%=rs.getString("idx") %></td>
    <td><%=rs.getString("title") %></td>
    <td><%=rs.getString("name") %></td>
    <td><%=rs.getString("age") %></td>
    <td><%=rs.getString("etc") %></td>
  </tr> 		
  <%	  
  }  
%>  
</table>
<br> 
<%
  // 링크 한글 인코딩 하기 
  if (ch2 != null) {
    ch2 = URLEncoder.encode(ch2,"utf-8");
  }
%>
<a href="list.jsp?start=1&ch1=<%=ch1 %>&ch2=<%=ch2 %>" >처음으로 </a>
&emsp;&emsp;
<% if( start >= pageSize + 1 ) { %>
<a href=list.jsp?start=<%=start-pageSize%>&ch1=<%=ch1 %>&ch2=<%=ch2 %>><b>이 전</b></a>
<% } else { %>
<b>이 전</b>
<% } %>
&emsp;&emsp;

<% if(nowPage == totalPage) { %>
<b>다 음</b>
<% } else { %>
<a href=list.jsp?start=<%=start+pageSize%>&ch1=<%=ch1 %>&ch2=<%=ch2 %>><b>다 음</b></a>
<% } %> 

&emsp;&emsp;
<%
  start =(totalPage - 1 )*pageSize +1;
%>
<a href=list.jsp?start=<%=start %>&ch1=<%=ch1 %>&ch2=<%=ch2 %> >마지막으로</a>
</div>
<div align="center" >
  <form action=list.jsp>
      <select name=ch1>
       <option value="title" > 제 목 </option>
       <option value="name" > 이 름 </option>
      </select>
      <input type=text  name=ch2>
      <input type=submit value="검색하기">
  </form>
</div>
 
<br>
</section>

 

이름: 마이키 검색결과

 

 

'JAVA' 카테고리의 다른 글

[66일차] 스프링교재  (0) 2022.08.24
[64일차] 오라클 계층형 답변형 게시판  (0) 2022.08.22
[58일차] 정보처리산업기사 지역구의원투표프로그램 만들기  (0) 2022.08.11
[55일차] 자바로 mysql연동, 이미지 추가해서 자료실 만들기  (0) 2022.08.09
[54일차] mysql연동 form,insert,select,delete,검색창 만들기  (0) 2022.08.05

'JAVA' Related Articles

티스토리툴바