ouchi > Dustbox > ItemSelectDAO的な

アレだったんで修正しました．

package ppp.dao;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
 
import ppp.common.ConnectionManager;
import ppp.exception.AppRuntimeException;
import ppp.exception.AppSQLException;
import ppp.vo.ItemVO;
 
public class ItemSelectDAO extends SelectNoTxDAOBase<ItemVO> {
 
	private static final String SQL_BASE = "SELECT item.item_no AS item_no, item_name, photo_url, unit_price, size, stock, assortment.name AS assortment_name, assortment.description AS assortment_description, category.name AS category_name, (stock - sub.unavailable) AS available FROM item, stock, assortment, category, (SELECT item_no, sum(quantity) AS unavailable FROM order_detail WHERE status_code = '1' GROUP BY item_no) sub WHERE item.item_no = stock.item_no AND item.assortment = assortment.code AND item.category = category.code AND item.item_no = sub.item_no";
	private static final String SQL_ORDER_BY = " ORDER BY category_name, item_no";
 
	private static final String[] WHERE_PHRASE = {
						" AND item_name = ?", " AND category.name = ?"
						};
 
	public ItemSelectDAO(Connection conn) {
		super(conn);
	}
 
	@Override
	protected String makeSql(Object[] params) throws AppSQLException {
 
		if (params == null || params.length > 2) {
			throw new AppSQLException("パラメータ数が不正です");
		}
 
		StringBuilder sb = new StringBuilder(SQL_BASE);
		System.out.println(sb.toString());
		for (int i = 0; i < params.length; i++) {
			if (params[i] != null) {
				sb.append(WHERE_PHRASE[i]);
				System.out.println(sb.toString());
			}
		}
		sb.append(SQL_ORDER_BY);
 
		System.out.println(sb.toString());
		return sb.toString();
	}
 
	@Override
	protected ItemVO makeRowObject(ResultSet rs) throws SQLException {
		ItemVO item = new ItemVO();
		item.setItemNo(rs.getInt("item_no"));
		item.setItemName(rs.getString("item_name"));
		item.setPhotoUrl(rs.getString("photo_url"));
		item.setUnitPrice(rs.getInt("unit_price"));
		item.setSize(rs.getString("size"));
		item.setStock(rs.getInt("stock"));
		item.setAssortmentName(rs.getString("assortment_name"));
		item.setAssortmentDescription(rs.getString("assortment_description"));
		item.setCategoryName(rs.getString("category_name"));
		item.setAvailableStock(rs.getInt("available"));
		return item;
	}
 
	// Test Driver
	public static void main(String[] args) {
		Object[] params = {};
		List<ItemVO> list = null;
		ConnectionManager cm = new ConnectionManager();
		Connection conn = null;
		try {
			conn = cm.getConnection();
			ItemSelectDAO dao = new ItemSelectDAO(conn);
			list = dao.select(params);
		} catch (AppSQLException e) {
			throw new AppRuntimeException("ItemVOリストの取得に失敗しました", e);
		} finally {
			cm.closeConnection();
		}
		if (list != null) {
			System.out.println("抽出結果");
			for (ItemVO item : list) {
				System.out.println(item.toString());
			}
		}
	}
 
}