CREATE TABLE "ITEM"　(
  "ITEM_ID" NUMBER NOT NULL,
  "ITEM_NAME" VARCHAR2 (16),
  "START_ITEM_UNIT_PRICE" NUMBER,
  "ITEM_AMOUNT" NUMBER,
  "DISCOUNT_TYPE" NUMBER,
  CONSTRAINT "ITEM_PK1" PRIMARY KEY ("ITEM_ID")
);

CREATE TABLE "BID"　(
  "BID_ID" NUMBER NOT NULL,
  "USER_NAME" VARCHAR2 (16),
  "BID_DATE" DATE,
  "ITEM_ID" NUMBER,
  "BID_UNIT_PRICE" NUMBER,
  "BID_AMOUNT" NUMBER,
  CONSTRAINT "BID_PK1" PRIMARY KEY ("BID_ID"),
  CONSTRAINT "BID_FK" FOREIGN KEY ("ITEM_ID") REFERENCES "ITEM" ("ITEM_ID")
);

CREATE TABLE "SALE"　(
  "SALE_ID" NUMBER NOT NULL,
  "ITEM_ID" NUMBER,
  "SALE_UNIT_PRICE" NUMBER,
  "SALE_AMOUNT" NUMBER,
  "SALE_DATE" DATE,
  CONSTRAINT "SALE_PK1" PRIMARY KEY ("SALE_ID"),
  CONSTRAINT "SALE_FK" FOREIGN KEY ("ITEM_ID") REFERENCES "ITEM" ("ITEM_ID")
);

// BiddingService.java
// ----- 省略ALE =
        "insert -----
public class BiddingService {
    // SQL文
    private static final String FIND_HIGHEST_BID =
        "select bid_id, user_name, bid_date, item_id,
		bid_unit_price, bid_amount " +
        "from bid where bid_unit_price = 
		(select max(bid_unit_price) from bid where item_id = ?)";
    private static final String MAKE_SALE =
        "select bid.bid_id, bid.item_id,
		 bid.bid_unit_price, bid.bid_amount " +
        "item.discount_type" +
        "from bid, item where bid_unit_price =
		 (select max(bid_unit_price) from bid where item_id = ?)";
    private static final String INSERT_S into sale
	 (sale_id, item_id, sale_unit_price, sale_amount, sale_date) " +
        "values (?, ?, ?, ?, ?)";

    // 定数
    private static final String SALE_TABLE = "sale";
    private static final String SALE_PK = "sale_id";

    // アイテムの最高入札額をBIDテーブルから取得
    public int findHighestBidPrice(int itemId) throws AuctionException {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection();
            pstmt = con.prepareStatement(FIND_HIGHEST_BID);
            pstmt.setInt(1, itemId);
            rs = pstmt.executeQuery();
            rs.next();
            int price = rs.getInt(5);
            return price;
        } catch (SQLException se) {
            throw new AuctionException(se);
        } finally {
            DBUtil.cleanUp(con, pstmt, rs);
        }
    }

    // 最高額の入札に割引き金額を適用し確定（SALEテーブルに挿入）
    public void makeSale(int itemId) throws AuctionException {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection();
            pstmt = con.prepareStatement(MAKE_SALE);
            rs = pstmt.executeQuery();
            int bidId = rs.getInt(1);
            int bidUnitPrice = rs.getInt(3);
            int bidAmount = rs.getInt(4);
            int discountType = rs.getInt(5);　　// 割引きタイプの取得
            int saleUnitPrice = 0;　// 割引き適用後の金額

            switch (discountType) {
            case 1:
                //Type1のアルゴリズムを適用しsaleUnitPriceの算出
                //SALEを作成し、テーブルにインサート
                // 10個以上5%引き、20個以上10%引き
                if (bidAmount >= 10) {
                    saleUnitPrice = new Double
(bidUnitPrice * 0.95).intValue();
                } else if (bidAmount >= 20) {
                    saleUnitPrice =  new Double
(bidUnitPrice * 0.90).intValue();
                }
                break;
            case 2:
                //Type2のアルゴリズムを適用し、適用しsaleUnitPriceの算出
                // ----- 省略 -----
                break;
            case 3:
                //Type3のアルゴリズムを適用し、適用しsaleUnitPriceの算出
                // ----- 省略 -----
                break;
            default:
                //割引なしのアルゴリズム ----- 省略 -----
            }

            pstmt = con.prepareStatement(INSERT_SALE);
            pstmt.setInt(1, DBUtil.getNextId(SALE_TABLE,
			 SALE_PK)); //主キーの生成
            pstmt.setInt(2, itemId);
            pstmt.setInt(3, saleUnitPrice);
            pstmt.setInt(4, bidAmount);
            pstmt.setDate(5, new Date(System.currentTimeMillis()));
            pstmt.execute();
        } catch (SQLException se) {
            new AuctionException(se);
        } finally {
            DBUtil.cleanUp(con, pstmt, rs);
        }
    }
}

// Item.java
// ----- 省略 -----
public class Item implements Serializable {
    // フィールド
    private int itemId;
    private String itemName;
    // ----- 省略 -----

    // コンスラクタ
    public Item(int itemId, String itemName, int startItemUnitPrice,
        int itemAmount, int discountType) {
        this.itemId = itemId;
    // ----- 省略 -----
    }

    // アクセッサメソッド
    public int getItemId() {
        return itemId;    }
    // ----- 省略 -----

    // Strategyパターンの利用
    public Sale makeSale(Bid bid) throws AuctionException {
        return bid.makeSale();
    }
}

// Bid.java
// ----- 省略 -----
public class Bid implements Serializable {
    // フィールド
    private int bidId;
    private String userName;
    private Date bidDate;
    private Item item;
    // ----- 省略 -----

    // コンストラクタ
    public Bid(int bidId, String userName, Date bidDate, Item item,
        int bidUnitPrice, int bidAmount) {
        this.bidId = bidId;
        this.userName = userName;
        this.bidDate = bidDate;
        this.item = item;
        // ----- 省略 -----
        // デフォルトのストラテジの設定
        this.saleStrategy = (SaleStrategy)new 
		 DefinedSaleStrategy(getItem().getDiscountType());
    }

    // アクセッサメソッド
    public int getBidId() {
        return bidId;
    }
    public void setBidId(int bidId) {
        this.bidId = bidId;
    }
    // ----- 省略 -----
    public Item getItem() {
        return item;    }
    public void setItemId(Item item) {
        this.item = item;
    }
    // ----- 省略 -----

    // Strategyパターンの変更用メソッド
    public void changeStrategy(SaleStrategy saleStrategy) {
      this.saleStrategy = saleStrategy;
    }
    // Strategyパターンの利用
    public Sale makeSale() throws AuctionException {
        return saleStrategy.makeSale(this);
    }
}

// SaleStrategy.java
// ----- 省略 -----
interface SaleStrategy {
    Sale makeSale(Bid Bid) throws AuctionException;
}

// DefinedSaleStrategy.java
// ----- 省略 -----
public class DefinedSaleStrategy implements SaleStrategy {
    int discountType = 0;
    // コンストラクタ
    public DefinedSaleStrategy(int discountType) {
        this.discountType = discountType;
    }
    // デフォルトの落札確定メソッド
    public Sale makeSale(Bid bid) {
        int saleUnitPrice = 0;
        int bidUnitPrice = bid.getBidUnitPrice();
        int bidAmount = bid.getBidAmount();
        switch (discountType) {
        case 1:
            //Type1のアルゴリズムを適用しSALEを作成
            // 10個以上5%引き、20個以上10%引き
            if (bidAmount >= 10) {
                saleUnitPrice = new Double(bidUnitPrice * 0.95).intValue();
            } else if (bidAmount >= 20) {
                saleUnitPrice = new Double(bidUnitPrice * 0.90).intValue();
            }
            break;
        case 2:
            //Type2のアルゴリズムを適用しSALEを作成
            //例えば5個以上5%引き、10個以上10%引き
            // ----- 省略 -----
            break;
        case 3:
            //Type3のアルゴリズムを適用しSALEを作成
            //例えば3個以上5%引き、5個以上10%引き
            break;
            // ----- 省略 -----
        default:
            //割引なしのアルゴリズムを適用しSALEを作成
        }

        Item item = bid.getItem();

        return new Sale(item, saleUnitPrice, bidAmount,
        new Date(System.currentTimeMillis()));
    }
}

// CustomSaleStrategy.java
// ----- 省略 -----
 public class CustomSaleStrategy implements SaleStrategy {
    double discountRate = 0.0;
    public CustomSaleStrategy(double discountRate){
      this.discountRate = discountRate;
    }

        // カスタムの落札確定メソッド
    public Sale makeSale(Bid bid) {
        int saleUnitPrice = 0;

        int bidUnitPrice = bid.getBidUnitPrice();
        int bidAmount = bid.getBidAmount();
        saleUnitPrice = new Double(bidUnitPrice * discountRate).intValue();
        Item item = bid.getItem();

        return new Sale(item, saleUnitPrice, bidAmount,
        new Date(System.currentTimeMillis()));
    }
}