You will first need to (if you haven't already) create Sale and Sale_Item tables in your schema and database.

Lecture 6, slides 4 and 5 show you how to link the tables via foreign keys (which you should be doing).

Lecture 8, slide 18 shows you how to implement the generated sale ID using an auto_increment sequence. Use an integer rather than a bigint as shown in the lecture.

Saving a sale is pretty complicated:

• The sale ID is a generated from a sequence, and is also used as a foreign key to link the sale item to the sale which means that the DAO needs to retrieve the generated value.

• Three tables need to be modified. Data needs to be added to both the sale and sale item tables, and the quantities in the product table need to be updated.

• This all needs to happen as part of a single transaction. If any of it breaks we need to roll back the entire transaction to ensure that the database remains in a consistent state.

The SaleDAO interface looks like:

public interface SaleDAO {

    void save(Sale sale);

}

The SaleJdbiDAO looks like:

public interface SaleJdbiDAO extends SaleDAO {

    @SqlUpdate(" INSERT STATEMENT GOES HERE ")
    @GetGeneratedKeys
    Integer insertSale(@BindBean Sale sale);

    @SqlUpdate(" INSERT STATEMENT GOES HERE ")
    void insertSaleItem(@BindBean SaleItem item, @Bind("saleId") Integer saleId);

    @SqlUpdate(" UPDATE STATEMENT GOES HERE ")
    void updateStockLevel(@BindBean SaleItem item);

    @Override
    @Transaction
    default void save(Sale sale) {
        // save current date
        sale.setDate(LocalDateTime.now());

        // set sale status
        sale.setStatus("NEW ORDER");

        // call the insertSale method, and get the generated sale ID.
        Integer saleId = insertSale(sale);
        sale.setSaleId(saleId);

        // loop through the sale's items.
        for (SaleItem item : sale.getItems()) {
            insertSaleItem(item, saleId);
            updateStockLevel(item);
        }

    }
}

We have to break the operation into four methods since there are three SQL statements, and one transaction that needs to wrap everything. The above code is fairly self-evident and well commented.

<> You need to provide the SQL statements for the @SqlUpdate annotations. Don't insert the sale ID in the insertSale method --- it is being generated by the database. The updateStockLevel statement should subtract the quantity purchased from the quantity in stock.

<> Note that you will need to drill into some objects to get the correct field for the parameters. For example, to get the productId from a SaleItem, you would use the following as the parameter in the SQL:

:product.productId

Where product is the name of the field in the SaleItem that contains the product, and productId is the name of the field inside that product that contains the product ID.

You will also need to add a getSaleDAO method to the JdbiDaoFactory class (copy and adapt one of the other getXyzDAO methods.