GDJ 24/04/17 (Github, Paging 기능)

- OFFSET 1 rows FETCH NEXT 10 rows ONLY; <-- oracle 페이징 쿼리

CustomerDAO.java 내용 추가 및 코드 수정

/customer/updateCustomerForm.jsp
/customer/loginAction.jsp
/customer/updateCustomerAction.jsp

github organization 공통 레퍼지토리 생성
branch 생성하여 commit, push 확인

/oraclejdbc/GoodsDAO.java

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

public class GoodsDAO {
	// 고객 로그인 후 상품목록 페이지
	// /customer/goodsList.jsp
	// param : void
	// return : Goods(일부 속성)의 배열 -> ArrayList<HashMap<String, Object>>
	public static ArrayList<HashMap<String, Object>> selectGoodsList(
			String category, int startRow, int rowPerPage) throws Exception {
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		
		// Connection
		Connection conn = DBHelper.getConnection();
		String sql = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		if(category != null || category.equals("")) {
			sql = "select"
					+ " goods_no goodsNo,"
					+ " category,"
					+ " goods_title goodsTitle"
					+ " goods_price goodsPrice"
					+ " from goods"
					+ " where category = ?"
					+ " order by goods_no desc"
					+ " offset ? rows fetch next ? rows only";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, category);
			stmt.setInt(2, startRow);
			stmt.setInt(3, rowPerPage);
		} else {
			sql = "select"
					+ " goods_no goodsNo,"
					+ " category,"
					+ " goods_title goodsTitle"
					+ " goods_price goodsPrice"
					+ " from goods"
					+ " order by goods_no desc"
					+ " offset ? rows fetch next ? rows only";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, startRow);
			stmt.setInt(2, rowPerPage);
		}
		rs = stmt.executeQuery();
		
        // 자료구조 변환
        
		// 자원반납
		return list;
	}
}

oraclejdbc/OrdersDAO.java

package dao;

import java.sql.*;
import java.util.*;

public class OrdersDAO {
	// 고객이 자신의 주문을 확인(페이징)
	public static ArrayList<HashMap<String, Object>> selectOrdersListByCustomer(
			String mail, int startRow, int rowPerPage) throws Exception{
		
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		
		Connection conn = DBHelper.getConnection();
		
		String sql = "SELECT o.orders_no ordersNo, o.goods_no goodsNo, g.goods_title goodsTitle FROM orders o INNER JOIN goods g ON o.goods_no = g.goods_no WHERE o.mail = ? ORDER BY o.orders_no DESC OFFSET ? rows FETCH NEXT ? rows ONLY";
		PreparedStatement stmt = conn.prepareStatement(sql);
		
		stmt.setString(1, mail);
		stmt.setInt(2, startRow);
		stmt.setInt(3, rowPerPage);
		ResultSet rs = stmt.executeQuery();
		
		conn.close();
		return list;
	}
	
	// 관리자가 전체주문을 확인(페이징)
	public static ArrayList<HashMap<String, Object>> selectOrdersListAll(
			String mail, int startRow, int rowPerPage) throws Exception{
		
		ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		
		Connection conn = DBHelper.getConnection();
		
		String sql = "SELECT o.orders_no ordersNo, o.goods_no goodsNo, g.goods_title goodsTitle FROM orders o INNER JOIN goods g ON o.goods_no = g.goods_no ORDER BY o.orders_no DESC OFFSET ? rows FETCH NEXT ? rows ONLY";
		PreparedStatement stmt = conn.prepareStatement(sql);
		
		stmt.setInt(1, startRow);
		stmt.setInt(2, rowPerPage);
		ResultSet rs = stmt.executeQuery();
		
		return list;
	}
}