405
No
|
* 페이지 나누기 * 전체 페이지 블록수 1 2 3 4 5 6 7 8 9 10 [다음] [이전] 41 42 43 44 45 46 47 48 49 50 [다음] 현재 페이지가 속한 블록 1페이지 => 몇번째 블록? 1 |
SQL
select *
from (
select A.*, rownum as rn
from (
select empno, ename
from test01
order by empno
) A
)
where rn between #{start} and #{end}
class PageDAO
package common;
public class PageDAO {
//페이지당 게시물수
public static final int PAGE_SCALE = 10;
// 페이지 블록 단위(한 화면에 보여줄 페이지 수)
public static final int BLOCK_SCALE = 10;
private int curPage; //현재 페이지
private int prevPage; //이전 페이지
private int nextPage; //다음 페이지
private int totPage; //전체 페이지 갯수
private int totBlock; //전체 블록 갯수
private int curBlock; //현재 페이지 블록
private int prevBlock; //이전 페이지 블록
private int nextBlock; //다음 페이지 블록
private int pageBegin; //현재 페이지 시작 번호
private int pageEnd; //현재 페이지 끝 번호
private int blockStart; //현재 블록의 시작 번호
private int blockEnd; //현재 블록의 끝 번호
//생성자
public PageDAO(int count, int curPage) {
curBlock = 1;
this.curPage = curPage;
setTotPage(count); //전체 페이지 갯수 설정
setPageRange();
setTotBlock(); // 전체 페이지 블록 갯수 설정
//curPage가 속한 페이지 블록의 시작번호,끝번호계산
setBlockRange();
}
public void setTotBlock(){
//전체페이지갯수 / 페이지블록단위
totBlock = (int)Math.ceil(totPage / BLOCK_SCALE);
}
public void setBlockRange(){
//curPage가 몇번째 페이지블록에 속하는지 계산
curBlock =
(int)Math.ceil((curPage-1)/BLOCK_SCALE)+1;
//현재 페이지블록의 시작,끝번호 설정
blockStart = (curBlock-1)*BLOCK_SCALE+1;
blockEnd=blockStart+BLOCK_SCALE-1;
//마지막 블록이 범위를 초과하지 않도록 처리
if(blockEnd > totPage){
blockEnd = totPage;
}
// [이전]을 눌렀을 때 이동할 페이지
prevPage =
curBlock==1 ? 1 : (curBlock-1)*BLOCK_SCALE;
// [다음]을 눌렀을 때 이동할 페이지
nextPage = curBlock > totBlock ?
(curBlock*BLOCK_SCALE) : (curBlock*BLOCK_SCALE)+1;
// 마지막 페이지가 범위를 초과하지 않도록 처리
if( nextPage >= totPage ) {
nextPage = totPage;
}
}
//curPage(현재 페이지)의 시작번호,끝번호 계산
public void setPageRange(){
//시작번호:(현재페이지 - 1 ) * 페이지당게시물수 + 1
pageBegin = (curPage-1)*PAGE_SCALE+1;
//끝번호:시작번호 + 페이지당게시물수 - 1
pageEnd = pageBegin + PAGE_SCALE - 1;
}
public void setTotPage(int count){
// Math.ceil(숫자) => 올림
// Math.floor(숫자) => 버림
// Math.round(숫자) => 반올림
totPage=(int)Math.ceil(count * 1.0 / PAGE_SCALE);
}
public int getTotBlock() {
return totBlock;
}
public void setTotBlock(int totBlock) {
this.totBlock = totBlock;
}
public int getTotPage() {
return totPage;
}
//getter,setter
public int getCurPage() {
return curPage;
}
public void setCurPage(int curPage) {
this.curPage = curPage;
}
public int getPrevPage() {
return prevPage;
}
public void setPrevPage(int prevPage) {
this.prevPage = prevPage;
}
public int getNextPage() {
return nextPage;
}
public void setNextPage(int nextPage) {
this.nextPage = nextPage;
}
public int getCurBlock() {
return curBlock;
}
public void setCurBlock(int curBlock) {
this.curBlock = curBlock;
}
public int getPrevBlock() {
return prevBlock;
}
public void setPrevBlock(int prevBlock) {
this.prevBlock = prevBlock;
}
public int getNextBlock() {
return nextBlock;
}
public void setNextBlock(int nextBlock) {
this.nextBlock = nextBlock;
}
public int getPageBegin() {
return pageBegin;
}
public void setPageBegin(int pageBegin) {
this.pageBegin = pageBegin;
}
public int getPageEnd() {
return pageEnd;
}
public void setPageEnd(int pageEnd) {
this.pageEnd = pageEnd;
}
public int getBlockStart() {
return blockStart;
}
public void setBlockStart(int blockStart) {
this.blockStart = blockStart;
}
public int getBlockEnd() {
return blockEnd;
}
public void setBlockEnd(int blockEnd) {
this.blockEnd = blockEnd;
}
}
class PageController
package test;
import java.io.IOException;
import java.util.List;
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 common.PageDAO;
import emp.dao.EmpDAO;
import emp.dto.EmpDTO;
@WebServlet("/page_servlet/*")
public class PageController extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String url=request.getRequestURL().toString();
EmpDAO dao=new EmpDAO();
if(url.indexOf("list.do") != -1){
// new PageDAO( 레코드갯수, 현재페이지번호 )
int count=dao.empCount2();
int curPage = 1;
if(request.getParameter("curPage")!=null){
curPage=Integer.parseInt(
request.getParameter("curPage"));
}
System.out.println("현재 페이지:"+curPage);
PageDAO pageDao = new PageDAO(count, curPage);
int start=pageDao.getPageBegin();
int end=pageDao.getPageEnd();
List<EmpDTO> list=dao.empList2(start,end);
request.setAttribute("list", list);
request.setAttribute("page", pageDao);
String page="/sql02/list.jsp";
RequestDispatcher rd=
request.getRequestDispatcher(page);
rd.forward(request, response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
class EmpDAO
package emp.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import config.DB;
import config.MybatisService;
import emp.dto.DeptDTO;
import emp.dto.EmpDTO;
import emp.dto.EmpSalaryDTO;
public class EmpDAO {
public EmpDAO() {
// TODO Auto-generated constructor stub
}
public List<DeptDTO> deptList(){
List<DeptDTO> list=new ArrayList<DeptDTO>();
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
conn = DB.dbConn();
String sql=
"select deptno,dname from dept order by dname";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while( rs.next() ) {
DeptDTO dto=new DeptDTO();
dto.setDeptno(rs.getInt("deptno"));
dto.setDname(rs.getString("dname"));
list.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if( rs != null ) rs.close();
} catch (Exception e2) {
e2.printStackTrace();
}
try {
if( pstmt != null ) pstmt.close();
} catch (Exception e2) {
e2.printStackTrace();
}
try {
if( conn != null ) conn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return list;
}
public List<EmpDTO> empList(String deptno){
List<EmpDTO> items=new ArrayList<EmpDTO>();
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
conn = DB.dbConn();
// String sql="select "
//+" e.empno,e.ename,e.job,d.deptno,d.dname "
//+" , e.sal, e.comm, nvl(sal*12 + comm, sal*12) tot "
//+"from emp e, dept d "
//+"where e.deptno=d.deptno";
String sql=
"select * from emp_dept_v "
+ " where deptno like ? || '%'";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, deptno);
rs = pstmt.executeQuery();
while(rs.next()){
EmpDTO dto=new EmpDTO();
dto.setEmpno(rs.getInt("empno"));
dto.setEname(rs.getString("ename"));
dto.setJob(rs.getString("job"));
dto.setDeptno(rs.getInt("deptno"));
dto.setDname(rs.getString("dname"));
dto.setSal(rs.getInt("sal"));
dto.setComm(rs.getInt("comm"));
dto.setTot(rs.getInt("tot"));
items.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if( rs != null ) rs.close();
} catch (Exception e2) {
e2.printStackTrace();
}
try {
if( pstmt != null ) pstmt.close();
} catch (Exception e2) {
e2.printStackTrace();
}
try {
if( conn != null ) conn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return items;
}
public int empCount2(){
int count=0;
SqlSession session=null;
try {
session
=MybatisService.getFactory().openSession();
count = session.selectOne("emp.empCount2");
} catch (Exception e) {
e.printStackTrace();
} finally {
if ( session != null ) session.close();
}
return count;
// 리턴되는 레코드가 1개 => selectOne()
// 리턴되는 레코드가 2개 이상 => selectList()
// int count=0;
// Connection conn=null;
// PreparedStatement pstmt=null;
// ResultSet rs=null;
// try {
// conn = DB.dbConn();
// String sql="select count(*) from test01";
// pstmt = conn.prepareStatement(sql);
// rs = pstmt.executeQuery();
// if(rs.next()){
// count = rs.getInt(1);
// }
// } catch (Exception e) {
// e.printStackTrace();
// } finally {
// try {
// if( rs != null ) rs.close();
// } catch (Exception e2) {
// e2.printStackTrace();
// }
// try {
// if( pstmt != null ) pstmt.close();
// } catch (Exception e2) {
// e2.printStackTrace();
// }
// try {
// if( conn != null ) conn.close();
// } catch (Exception e2) {
// e2.printStackTrace();
// }
// }
// return count;
}
//SqlSessionFactoryBuilder => SqlSessionFactory
//=> SqlSession (Mybatis에서 sql을 실행시키는 객체)
public List<EmpDTO> empList2(int start, int end){
List<EmpDTO> items=new ArrayList<EmpDTO>();
SqlSession session=
MybatisService.getFactory().openSession();
Map<String,Object> map=
new HashMap<String,Object>();
map.put("start",start);
map.put("end", end);
// selectOne() 레코드가 1개
// selectList("네임스페이스.sql의 아이디", 입력값)
// 입력값은 1개만 허용, map or dto 전달 가능
items = session.selectList("emp.empList2",map);
return items;
// List<EmpDTO> items=new ArrayList<EmpDTO>();
// Connection conn=null;
// PreparedStatement pstmt=null;
// ResultSet rs=null;
// try {
// conn = DB.dbConn();
// String sql=
//"select * "
//+"from ( "
//+" select A.*, rownum as rn "
//+" from ( "
//+" select empno, ename "
//+" from test01 "
//+" order by empno "
//+" ) A "
//+") "
//+"where rn between ? and ?";
// pstmt = conn.prepareStatement(sql);
// pstmt.setInt(1, start);
// pstmt.setInt(2, end);
// rs = pstmt.executeQuery();
// while(rs.next()){
// EmpDTO dto=new EmpDTO();
// dto.setEmpno(rs.getInt("empno"));
// dto.setEname(rs.getString("ename"));
// items.add(dto);
// }
// } catch (Exception e) {
// e.printStackTrace();
// } finally {
// try {
// if( rs != null ) rs.close();
// } catch (Exception e2) {
// e2.printStackTrace();
// }
// try {
// if( pstmt != null ) pstmt.close();
// } catch (Exception e2) {
// e2.printStackTrace();
// }
// try {
// if( conn != null ) conn.close();
// } catch (Exception e2) {
// e2.printStackTrace();
// }
// }
// return items;
}
public EmpSalaryDTO empSalaryList(){
EmpSalaryDTO dto=null;
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
conn = DB.dbConn();
String sql=
"select count(*) 직원수, sum(sal), avg(sal)"
+ ", max(sal), min(sal) from emp";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if( rs.next() ){
//컬럼이름이 없을 경우 컬럼인덱스 사용(1부터 시작)
dto = new EmpSalaryDTO();
dto.setCount(rs.getInt("직원수"));
dto.setSum(rs.getInt(2));
dto.setAvg(rs.getDouble(3));
dto.setMax(rs.getInt(4));
dto.setMin(rs.getInt(5));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if( rs != null ) rs.close();
} catch (Exception e2) {
e2.printStackTrace();
}
try {
if( pstmt != null ) pstmt.close();
} catch (Exception e2) {
e2.printStackTrace();
}
try {
if( conn != null ) conn.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
return dto;
}
public void insert(){
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn = DB.dbConn();
//오토 커밋 옵션을 해제
conn.setAutoCommit(false);
long before=System.currentTimeMillis();
for(int i=1; i<=100000; i++){
String sql=
"insert into emp values (?,?,?)";
pstmt = conn.prepareStatement(sql);
//if(i==50) throw new Exception();
pstmt.setString(1, Integer.toString(i));
pstmt.setString(2, "부서"+i);
pstmt.setString(3, "지역"+i);
pstmt.executeUpdate();
pstmt.close();
}
long after=System.currentTimeMillis();
conn.commit(); //수동 커밋
conn.setAutoCommit(true); //오토 커밋으로 전환
System.out.println("실행시간:"+(after-before));
} catch (Exception e) {
e.printStackTrace();
try {
if( conn != null ) conn.rollback(); //롤백
} catch (Exception e2) {
e2.printStackTrace();
}
} finally {
}
}
public void insert_batch(){
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn = DB.dbConn();
//오토 커밋 옵션을 해제
conn.setAutoCommit(false);
String sql=
"insert into emp values (?,?,?)";
pstmt = conn.prepareStatement(sql);
long before=System.currentTimeMillis();
for(int i=100001; i<=200000; i++){
//if(i==50) throw new Exception();
pstmt.setString(1, Integer.toString(i));
pstmt.setString(2, "부서"+i);
pstmt.setString(3, "지역"+i);
pstmt.addBatch(); //일괄처리작업 예약
}
pstmt.executeBatch(); //일괄처리작업 실행
conn.commit(); //수동 커밋
conn.setAutoCommit(true); //오토 커밋으로 전환
long after=System.currentTimeMillis();
System.out.println("실행시간:"+(after-before));
} catch (Exception e) {
e.printStackTrace();
try {
if( conn != null ) conn.rollback(); //롤백
} catch (Exception e2) {
e2.printStackTrace();
}
} finally {
}
}
}
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%@ include file="../include/header.jsp" %>
<script src="${path}/include/jquery-3.1.1.min.js"></script>
<script>
$(document).ready(function(){
list('1');
});
function list(curPage){
var param = "curPage="+curPage;
$.ajax({
type: "post",
url: "${path}/page_servlet/list.do",
data: param,
success: function(result){
$("#result").html(result);
}
});
}
</script>
</head>
<body>
<!-- WebContent/sql02/index.jsp -->
<h2>페이지 나누기</h2>
<div id="result"></div>
</body>
</html>
list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%@ include file="../include/header.jsp" %>
<script src="${path}/include/jquery-3.1.1.min.js"></script>
<script>
</script>
<style type="text/css">
/* 하이퍼링크 기본 스타일 */
a:link { text-decoration: none; }
/* 방문한 링크 */
a:visited { text-decoration: none; }
/* 마우스 오버 */
a:hover { color:red; text-decoration: underline; }
/* 클릭했을 때 */
a:active { color:yellow; text-decoration: none; }
</style>
</head>
<body>
<table border="1" width="600px">
<tr>
<th>사번</th>
<th>이름</th>
</tr>
<c:forEach var="row" items="${list}">
<tr>
<td>${row.empno}</td>
<td>${row.ename}</td>
</tr>
</c:forEach>
<!-- 페이지 네비게이션 출력 영역 -->
<tr>
<td colspan="2">
<!-- [처음] -->
<c:if test="${page.curPage > 1}">
<a href="#"
onclick="list('1')">[처음]</a>
</c:if>
<!-- [이전] -->
<c:if test="${page.curBlock > 1}">
<a href="#"
onclick="list('${page.prevPage}')">[이전]</a>
</c:if>
<%-- 페이지 갯수 : ${page.totPage} --%>
<!-- var="변수명" begin="시작번호" end="끝번호" -->
<!-- for(int i=1; i<=100; i++) -->
<c:forEach var="num" begin="${page.blockStart}"
end="${page.blockEnd}">
<c:choose>
<c:when test="${num == page.curPage}">
<!-- 현재페이지 -->
<span style="color:red">${num}</span>
</c:when>
<c:otherwise>
<!-- 현재 페이지가 아닐 때 -->
<a href="javascript:list('${num}')">${num}</a>
</c:otherwise>
</c:choose>
</c:forEach>
<!-- [다음] -->
<c:if test="${page.curBlock < page.totBlock}">
<a href="#"
onclick="list('${page.nextPage}')">[다음]</a>
</c:if>
<!-- [마지막] -->
<c:if test="${page.curPage < page.totPage}">
<a href="#"
onclick="list('${page.totPage}')">[마지막]</a>
</c:if>
</td>
</tr>
</table>
</body>
</html>
class Constants
package config;
public class Constants {
public static final int MAX=100;
}
class DB
package config;
import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class DB {
public static Connection dbConn(){
//context.xml에 설정된 dbcp에서 커넥션을 가져옴
DataSource ds=null; //javax.sql
Connection conn=null;
try {
//context.xml을 분석하는 객체
Context ctx=new InitialContext();//javax.naming
// context.xml의 Resource 태그 검색
// ds=
//(DataSource)ctx.lookup("java:comp/env/myDB");
ds=
(DataSource)ctx.lookup("java:comp/env/oraDB");
conn = ds.getConnection(); //커넥션을 할당받음
} catch (Exception e) {
e.printStackTrace();
}
return conn; //커넥션 리턴
}
}
class MybatisService
package config;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MybatisService {
// SqlSessionFactoryBuilder => SqlSessionFactory
// => SqlSession
//SqlSession 객체 생성기
private static SqlSessionFactory factory;
static {
try {
// Java Resources의 src
Reader r = Resources.getResourceAsReader(
"config/sqlMapConfig.xml");
//SqlSessionFactory 생성기
factory = new SqlSessionFactoryBuilder()
.build(r);
r.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static SqlSessionFactory getFactory() {
return factory;
}
}
sqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- xml 지시어 -->
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 알리아스 설정 -->
<typeAliases>
<!-- typeAlias type="전체경로" alias="별칭" -->
<typeAlias type="emp.dto.EmpDTO" alias="e" />
</typeAliases>
<!-- db연결 참조코드 -->
<environments default="">
<environment id="">
<transactionManager type="JDBC" />
<dataSource type="JNDI">
<property name="data_source"
value="java:comp/env/oraDB" />
</dataSource>
</environment>
</environments>
<!-- 실제 sql query -->
<mappers>
<mapper resource="emp/mapper/emp.xml" />
<mapper resource=
"student/mapper/student.xml" />
<mapper resource=
"student/mapper/dept.xml" />
<mapper resource=
"student/mapper/prof.xml" />
<mapper resource=
"student/mapper/lecture.xml" />
<mapper resource=
"memo/mapper/memo.xml" />
<mapper resource=
"board/mapper/board.xml" />
</mappers>
</configuration>
emp.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="emp">
<!-- id="태그의 식별자"
resultType="sql 명령어의 리턴타입(레코드의 자료형)"
샵{변수} => 입력매개변수 -->
<select id="empList2"
resultType="e">
select *
from (
select A.*, rownum as rn
from (
select empno, ename
from test01
order by empno
) A
)
where rn between #{start} and #{end}
</select>
<select id="empCount2" resultType="int">
select count(*) from test01
</select>
</mapper>
댓글 ( 4)
댓글 남기기