01: package com.mockrunner.example.jdbc;
02:
03: import java.sql.Connection;
04: import java.sql.ResultSet;
05: import java.sql.SQLException;
06: import java.sql.Statement;
07: import java.util.ArrayList;
08: import java.util.List;
09:
10: /**
11: * This example simulates the order of some books. It iterates through
12: * a <code>List</code> of ISBN numbers. If the current quantity is at least
13: * one, it reduces the quantity and adds the corresponding ISBN number to
14: * the result <code>List</code>.
15: *
16: * This example uses one table <i>books</i> with at least the columns
17: * <i>isbn</i> and <i>quantity</i>.
18: */
19: public class Bookstore {
20: public static List order(Connection connection, List isbnNumbers)
21: throws SQLException {
22: ArrayList resultList = new ArrayList();
23: Statement statement = null;
24: ResultSet result = null;
25: try {
26: connection.setAutoCommit(false);
27: StringBuffer query = new StringBuffer(
28: "select isbn, quantity from books where (");
29: for (int ii = 0; ii < isbnNumbers.size(); ii++) {
30: query.append("isbn='" + isbnNumbers.get(ii) + "'");
31: if (ii < isbnNumbers.size() - 1) {
32: query.append(" or ");
33: }
34: }
35: query.append(")");
36: statement = connection.createStatement(
37: ResultSet.TYPE_FORWARD_ONLY,
38: ResultSet.CONCUR_UPDATABLE);
39: result = statement.executeQuery(query.toString());
40: while (result.next()) {
41: int quantity = result.getInt("quantity");
42: if (quantity > 0) {
43: result.updateInt("quantity", quantity - 1);
44: result.updateRow();
45: resultList.add(result.getString("isbn"));
46: }
47: }
48: connection.commit();
49: } catch (Exception exc) {
50: connection.rollback();
51: }
52: if (null != result)
53: result.close();
54: if (null != statement)
55: statement.close();
56: return resultList;
57: }
58: }
|