[JSP] 페이징

페이징 자체는 회원목록처럼 코드흐름은 비슷한데

계산도 해야하고 회원리스트에 있는 자바스크립트도 있어 생각을 많이 해야되서

수업 내내 많이 힘들었던 페이징. 선생님, 형의 도움으로 만들었다.

 

CodeDAO, CodeDaoOracle 등등 Member와 같은 이름 같은 흐름이다.

 

 

 

CodeVO.java

package com.study.code.vo;

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;

public class CodeVO {
	
	private int commOrd;                                  //순번
	private String commParent;                             //부모 코드
	private String commNm;                                //코드명
	private String commCd;                                  //코드
	
	
	@Override
	public String toString() {
		return ToStringBuilder.reflectionToString(this, ToStringStyle.MULTI_LINE_STYLE);
	}

	public int getCommOrd() {
		return commOrd;
	}

	public void setCommOrd(int commOrd) {
		this.commOrd = commOrd;
	}

	public String getCommParent() {
		return commParent;
	}

	public void setCommParent(String commParent) {
		this.commParent = commParent;
	}

	public String getCommNm() {
		return commNm;
	}

	public void setCommNm(String commNm) {
		this.commNm = commNm;
	}

	public String getCommCd() {
		return commCd;
	}

	public void setCommCd(String commCd) {
		this.commCd = commCd;
	}
}

 

ICodeDao.java

 

package com.study.code.dao;

import java.sql.Connection;
import java.util.List;

import com.study.code.vo.CodeVO;

public interface ICodeDao {
	/**
	 * <b>공통코드에 조회목록을 리턴한다.</b>
	 *  
	 * @param conn
	 * @param code
	 * @return 코드목록 List<CodeVO>
	 * @throws DaoException	
	 */
	public List<CodeVO> getCodeListByParent(Connection conn, String parentCode);
	
}

 

 

ICodeService.java

 

package com.study.code.service;

import java.util.List;

import com.study.code.vo.CodeVO;

public interface ICodeService {
	
	/**
	 * <b>공통코드에 조회목록을 리턴한다.</b>
	 *  
	 * @param parentCode
	 * @return 코드목록 List<CodeVO>
	 */
	List<CodeVO> getCodeListByParent(String parentCode) ;
	
}

 

CodeDaoOracle.java

 

package com.study.code.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.study.code.vo.CodeVO;

public class CodeDaoOracle implements ICodeDao{

	@Override
	public List<CodeVO> getCodeListByParent(Connection conn, String parentCode) {
		//코드VO가 들어있는 List를 반환하겠다.
		// 변수 선언
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		List<CodeVO> list = new ArrayList<CodeVO>();

		sb.append("	SELECT comm_cd 	");
		sb.append("     , comm_nm		"); 
		sb.append("     , comm_parent ");
		sb.append("     , comm_ord 	");
		sb.append("     FROM comm_code ");
		sb.append("     WHERE comm_parent = ? ");
		sb.append("     ORDER BY comm_ord ASC ");
		
		System.out.println(sb.toString());
		
		try {
			// 3. 구문 객체 생성
			pstmt = conn.prepareStatement(sb.toString());
			//바인드변수 
			pstmt.setString(1, parentCode);
			
			rs = pstmt.executeQuery();
			while(rs.next()) { // ResultSet 은 next밖에 못
				CodeVO code = new CodeVO();
				code.setCommCd(rs.getString("comm_cd"));
				code.setCommNm(rs.getString("comm_nm"));
				code.setCommParent(rs.getString("comm_parent"));
				code.setCommOrd(rs.getInt("comm_ord"));
				list.add(code);
			}
			//res = pstmt.executeUpdate();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			// 자원종료
			if (rs != null) try {rs.close();} catch (SQLException e) {}
			if (pstmt != null) try {pstmt.close();} catch (SQLException e) {}
		}
	}
}

 

 

 

CodeServiceImpl.java

 

package com.study.code.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

import com.study.exception.BizNotFoundException;
import com.study.exception.BizPasswordNotMatchedException;

import com.study.code.dao.CodeDaoOracle;
import com.study.code.dao.ICodeDao;
import com.study.code.vo.CodeVO;
;

public class CodeServiceImpl implements ICodeService{
	
	private ICodeDao codeDao = new CodeDaoOracle();
	private String JDBC_URL = "jdbc:apache:commons:dbcp:study";
	
	@Override
	
	public List<CodeVO> getCodeListByParent(String parentCode) {
		
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(JDBC_URL);
			
			//conn.setAutoCommit(false);
			List<CodeVO> list = codeDao.getCodeListByParent(conn, parentCode);
			
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			try {conn.rollback();} catch (SQLException e2) {}
			throw new RuntimeException(e.getMessage(), e);
		}finally {
			if (conn != null) try {conn.close();} catch (SQLException e) {}
		}	
	}
}

 

만들어 주고 페이징은 getMemberList와 밀접한 연관이 있기 때문에 Member나 Free List에 있는 네임이나 쿼리, 조건을

바꿔줘야 한다.

그리고 getMemberCount도 바꿔줘야 한다.

 

MemberDaoOracle.java (getMemberCount)

 

 

@Override
	public int getMemberCount(Connection conn, MemberSearchVO searchVO) {
		// 변수 선언
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				StringBuilder sb = new StringBuilder();

				// SQL 구문
				sb.append(" SELECT COUNT(*) AS tot         ");
				sb.append(" FROM member         		 ");
				sb.append(" WHERE 1 = 1          ");
				//sb.append(" WHERE bo_del_yn ='N'          ");
				
				//검색은 물음표
				//삭제때문에 쓴 append WHERE 삭제여부가 N 인 것만 출력
				//그렇다면 삭제는 'Y'인건 안보이게 하면 된다.
				
				
				if (StringUtils.isNotBlank(searchVO.getSearchWord())) {
					switch (searchVO.getSearchType()) {
					case "I":
						sb.append(" AND mem_id LIKE '%' || ? || '%'  ");
						break;
					case "M":
						sb.append(" AND mem_name LIKE '%' || ? || '%'  ");
						break;
					case "P":
						sb.append(" AND mem_hp LIKE '%' || ? || '%'  ");
						break;
					default:
						sb.append(" AND mem_id LIKE '%' || ? || '%'  ");
						break;
					}
				}
				// 분류(searchCategory)가 존재하면

				if (StringUtils.isNotBlank(searchVO.getSearchLike())) {
					sb.append(" AND mem_like = ? ");
				}
				if (StringUtils.isNotBlank(searchVO.getSearchJob())) {
					sb.append(" AND mem_job = ? ");
				}
				

				
				
				
				System.out.println(sb.toString());
				try {
					// 3. 구문 객체 생성
					pstmt = conn.prepareStatement(sb.toString());
					
					int idx = 1;
					if (StringUtils.isNotBlank(searchVO.getSearchWord())) {
						pstmt.setString(idx++, searchVO.getSearchWord());
					}
					// 분류(searchCategory)가 존재하면
					
					if (StringUtils.isNotBlank(searchVO.getSearchJob())) {
						pstmt.setString(idx++, searchVO.getSearchJob());
					}
					if (StringUtils.isNotBlank(searchVO.getSearchLike())) {
						pstmt.setString(idx++, searchVO.getSearchLike());
					}
					
					rs = pstmt.executeQuery();
					
					int tot = 0;
					if(rs.next()) {
						
						tot = rs.getInt(1); //레코드 전체 수 int로
					}
					return tot;
				} catch (SQLException e) {
					e.printStackTrace();
					throw new RuntimeException(e.getMessage(), e);
				} finally {
					// 자원종료
					if (rs != null) try {rs.close();} catch (SQLException e) {}
					if (pstmt != null) try {pstmt.close();} catch (SQLException e) {}
				}
			}

 

MemberDaoOracle.java (getMemberList)

 

@Override
	public List<MemberVO> getMemberList(Connection conn, MemberSearchVO searchVO) {
		// 변수 선언
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		List<MemberVO> list = new ArrayList<MemberVO>();

		// SQL 구문
		//페이징 쿼리 추가
		sb.append("SELECT * ");
		sb.append("FROM ( SELECT rownum AS rnum ");
		sb.append("				, a.* ");
		//a.*  : 어느 테이블의 모든 것
		sb.append("		FROM (        ");
		
		sb.append("SELECT mem_id      ");
		sb.append("     , mem_name    ");
		sb.append("     , mem_pass    ");
		sb.append("     , mem_zip     ");
		sb.append("     , mem_add1    ");
		sb.append("     , mem_add2    ");
		sb.append("     , mem_hp      ");
		sb.append("     , mem_mail    ");
		sb.append("     , mem_job     ");
		sb.append("     , (SELECT comm_nm from comm_code WHERE mem_job = comm_cd ) as mem_job_nm ");
		sb.append("     , (SELECT comm_nm from comm_code WHERE mem_like = comm_cd ) as mem_like_nm ");
		sb.append("     , mem_mileage ");
		sb.append("     , mem_delete  ");
		sb.append("  FROM member      ");
		sb.append("  WHERE 1 = 1 ");
        //조건을 무조건 줘야한다. 밑에 AND이라서 조건이 없으면 오류.
        //그래서 조건이 무조건 참인것을 끼워 넣는다.
		if (StringUtils.isNotBlank(searchVO.getSearchWord())) {
			switch (searchVO.getSearchType()) {
			case "I":
				sb.append(" AND mem_id LIKE '%' || ? || '%'  ");
				break;
			case "M":
				sb.append(" AND mem_name LIKE '%' || ? || '%'  ");
				break;
			case "P":
				sb.append(" AND mem_hp LIKE '%' || ? || '%'  ");
				break;
			default:
				sb.append(" AND mem_hp LIKE '%' || ? || '%'  ");
				break;
			}
		}
		// 분류(searchCategory)가 존재하면

		if (StringUtils.isNotBlank(searchVO.getSearchJob())) {
//			sb.append(" AND mem_id = ? ");
			sb.append(" AND mem_job = ? ");
		}
		if (StringUtils.isNotBlank(searchVO.getSearchLike())) {
//			sb.append(" AND mem_id = ? ");
			sb.append(" AND mem_like = ? ");
		}
		sb.append(" ORDER BY mem_id ");
		sb.append("  			) a " );
		sb.append("  	where rownum <= ?  ");
		sb.append("  ) b  			       ");
		sb.append("  where rnum BETWEEN ? AND ?  ");
		//삭제때문에 쓴 append WHERE 삭제여부가 N 인 것만 출력
		//그렇다면 삭제는 'Y'인건 안보이게 하면 된다.
		System.out.println(sb.toString());
		
		try {
			// 3. 구문 객체 생성
			pstmt = conn.prepareStatement(sb.toString());
			int idx = 1;
			if (StringUtils.isNotBlank(searchVO.getSearchWord())) {
				pstmt.setString(idx++, searchVO.getSearchWord());
			}
			if (StringUtils.isNotBlank(searchVO.getSearchJob())) {
				pstmt.setString(idx++, searchVO.getSearchJob());
			} 
			if (StringUtils.isNotBlank(searchVO.getSearchLike())) {
				pstmt.setString(idx++, searchVO.getSearchLike());
			} 

			// 4. 바인드 변수 설정
			pstmt.setInt(idx++, searchVO.getEndRow());
			pstmt.setInt(idx++, searchVO.getStartRow());
			pstmt.setInt(idx++, searchVO.getEndRow());
			// 5. 구문객체 실행
			rs = pstmt.executeQuery();
			while(rs.next()) { // ResultSet 은 next밖에 못한다.
				MemberVO member = new MemberVO();
				member.setMemId(rs.getString("mem_id"));
				member.setMemName(rs.getString("mem_name"));
				member.setMemZip(rs.getString("mem_zip"));
				member.setMemAdd1(rs.getString("mem_add1"));
				member.setMemAdd2(rs.getString("mem_add2"));
				member.setMemHp(rs.getString("mem_hp"));
				member.setMemMail(rs.getString("mem_mail"));
				member.setMemJob(rs.getString("mem_job"));
				member.setMemJobnm(rs.getString("mem_job_nm"));
				member.setMemLikenm(rs.getString("mem_like_nm"));
				member.setMemLikenm(rs.getString("mem_mileage"));
				member.setMemLikenm(rs.getString("mem_delete"));
				list.add(member);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			// 자원종료
			if (rs != null) try {rs.close();} catch (SQLException e) {}
			if (pstmt != null) try {pstmt.close();} catch (SQLException e) {}
		}
	}