01: package com.mockrunner.example.jdbc;
02:
03: import java.sql.Connection;
04: import java.sql.DriverManager;
05: import java.sql.PreparedStatement;
06: import java.sql.ResultSet;
07: import java.sql.SQLException;
08: import java.sql.Statement;
09:
10: /**
11: * This example class simulates a bank. It can be used to
12: * transfer an amount of money from one account to another.
13: * It uses a table with the name <i>account</i>. The first column
14: * is the account <i>id</i>, the second stores the current <i>balance</i>.
15: * The SQL to create the table is
16: *
17: * <code>create table account(id int not null primary key,balance int not null)</code>.
18: */
19: public class Bank {
20: private Connection connection;
21:
22: public void connect() throws SQLException {
23: disconnect();
24: connection = DriverManager
25: .getConnection("jdbc:mysql://localhost:3306/test");
26: connection.setAutoCommit(false);
27: }
28:
29: public void disconnect() throws SQLException {
30: if (null != connection) {
31: connection.close();
32: connection = null;
33: }
34: }
35:
36: public void transfer(int sourceId, int targetId, int amount)
37: throws SQLException {
38: PreparedStatement preparedStatement = null;
39: try {
40: if (!isValid(sourceId, amount))
41: return;
42: preparedStatement = connection
43: .prepareStatement("update account set balance=balance+? where id=?");
44: preparedStatement.setInt(1, -amount);
45: preparedStatement.setInt(2, sourceId);
46: preparedStatement.executeUpdate();
47: preparedStatement.setInt(1, amount);
48: preparedStatement.setInt(2, targetId);
49: preparedStatement.executeUpdate();
50: connection.commit();
51: } catch (SQLException exc) {
52: connection.rollback();
53: } finally {
54: if (null != preparedStatement)
55: preparedStatement.close();
56: }
57: }
58:
59: private boolean isValid(int sourceId, int amount)
60: throws SQLException {
61: Statement statement = null;
62: ResultSet result = null;
63: try {
64: statement = connection.createStatement();
65: result = statement
66: .executeQuery("select balance from account where id="
67: + sourceId);
68: if (!result.next()) {
69: connection.rollback();
70: return false;
71: }
72: int balance = result.getInt(1);
73: if (balance < amount) {
74: connection.rollback();
75: return false;
76: }
77: return true;
78: } catch (SQLException exc) {
79: connection.rollback();
80: return false;
81: } finally {
82: if (null != result)
83: result.close();
84: if (null != statement)
85: statement.close();
86: }
87: }
88: }
|