Spring - 事务 - 用户、书、书库存 - 案例

简介

需求

数据库

创建库、account表

mysql -uroot -proot --default-character-set=gbk
drop database if exists spring ; 
create database spring charset utf8  ; 
use spring ;
create table account (
	username  varchar(20) not null,
	balance int not null
) ; 
insert into account value(
	'AA' , 
	160
) ; 
select * from account ;

创建 book 表

drop table if exists book ;
create table book (
	isbn char(4) primary key ,
	bookname varchar(100) not null, 
	price int not null
);
insert into book values(
	'1001',
	'Java',
	100
),(
	'1002',
	'Oracle',
	70
);
select * from book; 

创建库存表 book_stack

drop table if exists book_stack ; /* 开始写错名字。。 */
create table book_stock (
	isbn char(4) primary key  ,
	stock int not null
);
insert into book_stock values (
	'1002',
	8
) ,(
	'1001' , 
	4
);
select * from book_stock;

配置文件

Dao层

applicationContext-bookshop.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

<!-- 扫描的包 -->
<context:component-scan base-package="vedio.dao"></context:component-scan> 
<context:component-scan base-package="vedio.service"></context:component-scan> 


<!-- 数据库配置 资源文件 -->
<context:property-placeholder location="classpath:vedio/db_bookshop.properties"/>

<!-- dataSource -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
	<property name="user" value="${jdbc.user}"></property>
	<property name="password" value="${jdbc.password}"></property>
	<property name="driverClass" value="${jdbc.driverClass}"></property>
	<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
	
	<property name="initialPoolSize" value="${jdbc.initialPoolSize}"></property>
	<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
</bean>

<!-- jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
	<property name="dataSource" ref="dataSource"></property>
</bean>


<!-- 配置事务管理器 -->
<!-- DataSourceTransactionManager -->
<!-- 为transactionManager 添加 dataSource -->
<!-- 加入ts命名空间 -->
<!-- tx:annotation-driven id = ↑ -->
<!-- 添加注解 @Transactional -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 启动事务注解(注解驱动) -->
<tx:annotation-driven transaction-manager="transactionManager"/>

</beans>

db_bookshop.properties

jdbc.user=root
jdbc.password=root
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://127.0.0.1:3306/spring?characterEncoding=utf8

jdbc.initialPoolSize=5
jdbc.maxPoolSize=10

BookShopServiceImp.java

package vedio.service.imp;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import vedio.dao.BookShopDao;
import vedio.service.BookShopService;

@Service
public class BookShopServiceImp implements BookShopService {

	@Autowired
	private BookShopDao bookShopDao ; 
	
	/** * 添加事务注解 */
	@Transactional
	@Override
	public void purchaseBook(String username, String isbn) {
		//1. 书单价
		int price = bookShopDao.findBookPriceByIsbn(isbn);
		
		//2.书库存-1
		bookShopDao.updateBookStockDownByIsbn(isbn);
		
		//3. 用户余额-price
		bookShopDao.updateUserAccount(username, price);
		
	}

}

BookShopDaoImp.java

package vedio.dao.imp;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import vedio.dao.BookShopDao;
import vedio.dao.BookStackException;
import vedio.dao.UserBalanceException;

@Repository
public class BookShopDaoImp implements BookShopDao{

	@Autowired
	private JdbcTemplate jdbcTemplate ;  
	
	/*==================== book =======================*/
	
	@Override
	public int findBookPriceByIsbn(String isbn) {
		String sql = "select price from book where isbn = ? " ; 
		return jdbcTemplate.queryForObject(sql, Integer.class , isbn);
	}
	
	@Override
	public String findBookNameByIsbn(String isbn) {
		String sql = "select bookname from book where isbn = ?" ; 
		return jdbcTemplate.queryForObject(sql, String.class, isbn);
	}
	
	
	/*==================== book_stock =================*/
	@Override
	public int findBookStockByIsbn(String isbn) {
		String sql = "select stock from book_stock where isbn = ? " ; 
		return jdbcTemplate.queryForObject(sql, Integer.class , isbn);
	}

	@Override
	public void updateBookStockDownByIsbn(String isbn) {
		int stock = findBookStockByIsbn(isbn);
		if(stock==0) {
			throw new BookStackException("库存为零.."+"isbn="+isbn);
		}
		String sql = "update book_stock set stock=stock-1 where isbn = ? " ; 
		jdbcTemplate.update(sql ,isbn) ; 
	}
	@Override
	public void updateBookStockUpByIsbn(String isbn) {
		String sql = "update book_stock set stock=stock+1 where isbn = ? " ; 
		jdbcTemplate.update(sql ,isbn) ; 
		
	}
	
	/*==================== account ====================*/

	@Override
	public void updateUserAccount(String username, int price) {
		int balance = findUserBalanceByUsername(username);
		if(balance < price) {
			throw new UserBalanceException("用户余额不足..."+ "username="+username);
		}
		
		
		String sql = "update account set balance=balance-? where username=?" ; 
		jdbcTemplate.update(sql,price,username) ; 
	}

	@Override
	public int findUserBalanceByUsername(String username) {
		String sql = "select balance from account where username=?" ; 
		return jdbcTemplate.queryForObject(sql,Integer.class,username);
	}





}

Test1 : 基本操作

package video;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import vedio.dao.BookShopDao;

/** * 1. 事务Dao准备 * 2. 事务绑定 * */
public class Test_BookShop {
	
	private ClassPathXmlApplicationContext ctx = null ;
	private JdbcTemplate jdbcTemplate = null ;
	private BookShopDao bookShopDao  = null ; 
	
	{
		ctx = new ClassPathXmlApplicationContext("vedio/applicationContext-bookshop.xml") ;
		jdbcTemplate = ctx.getBean(JdbcTemplate.class) ;
		bookShopDao = ctx.getBean(BookShopDao.class) ; 
	}
	
	
	
	
	
	/** * 测试：stock查询、增、删 */
	@Test
	public void testBookStock() {
		int stock = 0 ; 
		String isbn = "1001";
		//查stock
		System.out.println("现有库存="+bookShopDao.findBookStockByIsbn(isbn)  );
		
		//stock-1
		bookShopDao.updateBookStockDownByIsbn(isbn);
		System.out.println("库存-1="+bookShopDao.findBookStockByIsbn(isbn)  );
		
		//stock+1
		bookShopDao.updateBookStockUpByIsbn(isbn);
		System.out.println("库存+1="+bookShopDao.findBookStockByIsbn(isbn)  );
	}
	
	/** * 测试：用户查询，预付修改 —— 关联book价格查询 */
	@Test
	public void testUpdateUserAccount() {
		String username = "AA";
		System.out.println("现有余额："+bookShopDao.findUserBalanceByUsername(username ));
		String isbn = "1001" ; 
		int price = bookShopDao.findBookPriceByIsbn(isbn) ;
		bookShopDao.updateUserAccount(username, price );
		System.out.println("购书价格："+price);
		System.out.println("扣除后余额："+bookShopDao.findUserBalanceByUsername(username ));
	}

	/** * 测试：查询price */
	@Test
	public void testFindBookPriceByIsbn() {
		//这里，用int 基本类型会报错 
		int price = bookShopDao.findBookPriceByIsbn("1001"); //100
		System.out.println(price);
	}
	
	/** * 测试dataSource连接数据库 * @throws SQLException */
	@Test
	public void testJdbcTemplateConnection() throws SQLException {
		DataSource dataSource = ctx.getBean(DataSource.class);
		Connection conn = dataSource.getConnection();
		System.out.println(conn);
	}

}

Test2 : 基本事务

package video;

import javax.jws.soap.SOAPBinding;

import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import vedio.dao.BookShopDao;
import vedio.service.BookShopService;

public class Test_Transaction {
	private ClassPathXmlApplicationContext ctx = null ;
	private BookShopDao bookShopDao = null ;
	private BookShopService bookShopService = null ; 
	{
		ctx = new ClassPathXmlApplicationContext(
				"classpath:vedio/applicationContext-bookshop.xml") ;
		bookShopDao = ctx.getBean(BookShopDao.class) ; 
		bookShopService = ctx.getBean(BookShopService.class) ; 
	}
	
	@Test
	public void testPurchaseBook() {
		String username  = "AA" ; 
		String isbn = "1001" ; 
		
		
		
		showStatus(username, isbn);
		try {
			bookShopService.purchaseBook(username, isbn);
		}catch (Exception e) {
			System.out.println(e);
		}
		showStatus(username, isbn);
	}
	
	public void showStatus(String username , String isbn) {
		String name = bookShopDao.findBookNameByIsbn(isbn);
		int price = bookShopDao.findBookPriceByIsbn(isbn); 
		int stock = bookShopDao.findBookStockByIsbn(isbn);
		int balance = bookShopDao.findUserBalanceByUsername(username);
		System.out.println("-------------");
		System.out.println("isbn"+isbn);
		System.out.println("name="+ name);
		System.out.println("price="+price);
		System.out.println("stock="+stock);
		System.out.println("username:"+ username+ ",balance:"+balance);
	}
}