Transaction Management in Spring - Spring Enterprise Recipes: A Problem Solution Approach - page 160

CREATE TABLE BOOK_STOCK (
ISBN VARCHAR(50) NOT NULL,
STOCK INT NOT NULL,
PRIMARY KEY (ISBN),
CHECK (STOCK >= 0)
);
CREATE TABLE ACCOUNT (
USERNAME VARCHAR(50) NOT NULL,
BALANCE INT NOT NULL,
PRIMARY KEY (USERNAME),
CHECK (BALANCE >= 0)
);
A real-world application of this type would probably feature a price field with a decimal type, but it
makes the programming simpler to follow, so leave it as an int .
The BOOK table stores basic book information such as the name and price, with the topic ISBN as the
primary key. The BOOK_STOCK table keeps track of each book's stock. The stock value is restricted by a
CHECK constraint to be a positive number. Although the CHECK constraint type is defined in SQL-99, not all
database engines support it. At the time of this writing, this limitation is mainly true of MySQL because
Sybase, Derby, HSQL, Oracle, DB2, SQL Server, Access, PostgresSQL, and FireBird all support it. If your
database engine doesn't support CHECK constraints, please consult its documentation for similar
constraint support. Finally, the ACCOUNT table stores customer accounts and their balances. Again, the
balance is restricted to be positive.
The operations of your book shop are defined in the following BookShop interface. For now, there is
only one operation: purchase() .
package com.apress.springenterpriserecipes.bookshop.spring;
public interface BookShop {
public void purchase(String isbn, String username);
}
Because you will implement this interface with JDBC, you create the following JdbcBookShop class.
To better understand the nature of transactions, let's implement this class without the help of Spring's
JDBC support.
package com.apress.springenterpriserecipes.bookshop.spring;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;