001: package com.mockrunner.example.jdbc;
002:
003: import java.sql.Connection;
004: import java.sql.DriverManager;
005: import java.sql.ResultSet;
006: import java.sql.SQLException;
007: import java.sql.Statement;
008:
009: import javax.servlet.http.HttpServletRequest;
010: import javax.servlet.http.HttpServletResponse;
011:
012: import org.apache.struts.action.Action;
013: import org.apache.struts.action.ActionForm;
014: import org.apache.struts.action.ActionForward;
015: import org.apache.struts.action.ActionMapping;
016: import org.apache.struts.action.ActionMessage;
017: import org.apache.struts.action.ActionMessages;
018:
019: /**
020: * This example simulates the paying of a bill. It checks the id of the customer,
021: * the id of the bill and the amount. If an error occurs the transaction is
022: * rolled back and an <code>ActionError</code> is created.
023: *
024: * This action uses three tables. The table <i>customers</i> has two columns,
025: * <i>id</i> and <i>name</i>. The table <i>openbills</i> has three columns,
026: * <i>id</i>, <i>customerid</i> and <i>amount</i>. The table <i>paidbills</i>
027: * is equivalent to <i>openbills</i>. If a bill is successfully paid, the
028: * action deletes the corresponding row from <i>openbills</i> and inserts
029: * it into <i>paidbills</i>.
030: */
031: public class PayAction extends Action {
032: public ActionForward execute(ActionMapping mapping,
033: ActionForm form, HttpServletRequest request,
034: HttpServletResponse response) throws Exception {
035: PayForm payForm = (PayForm) form;
036: ActionMessages errors = new ActionMessages();
037: Connection connection = initializeDatabase();
038: try {
039: String name = getName(connection, payForm);
040: if (null == name) {
041: createErrorAndRollback(request, connection, errors,
042: "unknown.customer.error");
043: return mapping.findForward("failure");
044: }
045: if (!checkBillIntegrity(request, connection, errors,
046: payForm)) {
047: return mapping.findForward("failure");
048: }
049: markBillAsPaid(connection, payForm);
050: connection.commit();
051: System.out.println(payForm.getAmount()
052: + " paid from customer " + name);
053: } catch (Exception exc) {
054: exc.printStackTrace();
055: createErrorAndRollback(request, connection, errors,
056: "general.database.error");
057: return mapping.findForward("failure");
058: } finally {
059: connection.close();
060: }
061: return mapping.findForward("success");
062: }
063:
064: private Connection initializeDatabase() throws Exception {
065: Connection connection = DriverManager
066: .getConnection("jdbc:mysql://localhost:3306/test");
067: connection.setAutoCommit(false);
068: return connection;
069: }
070:
071: private void createErrorAndRollback(HttpServletRequest request,
072: Connection connection, ActionMessages errors,
073: String errorKey) throws SQLException {
074: ActionMessage error = new ActionMessage(errorKey);
075: errors.add(ActionMessages.GLOBAL_MESSAGE, error);
076: saveErrors(request, errors);
077: connection.rollback();
078: }
079:
080: private String getName(Connection connection, PayForm payForm)
081: throws SQLException {
082: Statement statement = connection.createStatement();
083: ResultSet result = statement
084: .executeQuery("select name from customers where id='"
085: + payForm.getCustomerId() + "'");
086: String name = null;
087: if (result.next()) {
088: name = result.getString("name");
089: }
090: result.close();
091: statement.close();
092: return name;
093: }
094:
095: private void markBillAsPaid(Connection connection, PayForm payForm)
096: throws SQLException {
097: Statement statement = connection.createStatement();
098: statement.executeUpdate("delete from openbills where id='"
099: + payForm.getBillId() + "'");
100: statement.executeUpdate("insert into paidbills values('"
101: + payForm.getBillId() + "','" + payForm.getCustomerId()
102: + "'," + payForm.getAmount() + ")");
103: statement.close();
104: }
105:
106: private boolean checkBillIntegrity(HttpServletRequest request,
107: Connection connection, ActionMessages errors,
108: PayForm payForm) throws SQLException {
109: Statement statement = connection.createStatement();
110: ResultSet result = statement
111: .executeQuery("select * from openbills where id='"
112: + payForm.getBillId() + "'");
113: try {
114: if (false == result.next()) {
115: createErrorAndRollback(request, connection, errors,
116: "unknown.bill.error");
117: return false;
118: }
119: if (!result.getString("customerid").equals(
120: payForm.getCustomerId())) {
121: createErrorAndRollback(request, connection, errors,
122: "wrong.bill.for.customer");
123: return false;
124: }
125: if (result.getDouble("amount") != payForm.getAmount()) {
126: createErrorAndRollback(request, connection, errors,
127: "wrong.amount.for.bill");
128: return false;
129: }
130: } finally {
131: result.close();
132: statement.close();
133: }
134: return true;
135: }
136: }
|