JdbcTemplate

实验	内容	目标
实验1	按照id查询图书	了解 queryForObject
实验2	新增一个图书	了解 update
实验3	按照id修改图书库存
实验4	按照id删除图书
实验5	按照username扣减账户余额	准备事务环境
实验6	编写用户购买图书完整方法

SQL文件

创建数据库spring_tx

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名',
  `age` int(0) NULL DEFAULT NULL COMMENT '年龄',
  `balance` decimal(10, 2)  NULL DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES (1, 'zhangsan', 18, 10000.00);
INSERT INTO `account` VALUES (2, 'lisi', 20, 10000.00);
INSERT INTO `account` VALUES (3, 'wangwu', 16, 10000.00);

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '图书id',
  `bookName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '图书名',
  `price` decimal(10, 2) NULL DEFAULT NULL COMMENT '单价',
  `stock` int(0) NULL DEFAULT NULL COMMENT '库存量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, '剑指Java', 100.00, 100);
INSERT INTO `book` VALUES (2, '剑指大数据', 100.00, 100);
INSERT INTO `book` VALUES (3, '剑指Offer', 100.00, 100);

SET FOREIGN_KEY_CHECKS = 1;

POM.xml

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <scope>annotationProcessor</scope>
</dependency>

配置文件

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/spring_tx
    username: root
    password: 123456

Account类

@Data
public class Account {
    private Integer id;
    private String username;
    private Integer age;
    private BigDecimal balance;
}

Book类

@Data
public class Book {
    private Integer id;
    private String bookName;
    private BigDecimal price;
    private Integer stock;
}

AccountDao类

@Component
public class AccountDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    /**
     * 按照username扣减账户余额
     * @param username 用户名
     * @param delta 扣减的金额
     */
    //@Transactional(propagation = Propagation.REQUIRED,timeout = 5)
    public void updateBalanceByUsername(String username, BigDecimal delta) throws InterruptedException {
        String sql = "update account set balance = balance - ? where username = ?";
        // 执行SQL
//        Thread.sleep(4000);
        jdbcTemplate.update(sql, delta, username);
    }
}

BookDao类

@Component
public class BookDao {

    @Autowired
    JdbcTemplate jdbcTemplate;


    /**
     * 按照id查询图书
     * @param id
     * @return
     */
    @Transactional
    public Book getBookById(Integer id) {

        //1、查询图书SQL
        String sql = "select * from book where id  = ?";
        //2、执行查询
        Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Book.class), id);
        return book;
    }



    // REPEATABLE_READ： 可重复读。 快照读。 MySQL默认
    // READ_COMMITTED： 读已提交。 当前读。 Oracle默认
    //@Transactional(isolation = Isolation.REPEATABLE_READ)
    public BigDecimal getBookPrice(Integer id){
        String sql = "select price from book where id=?";
        //BigDecimal decimal1 = jdbcTemplate.queryForObject(sql, BigDecimal.class, id);

        //BigDecimal decimal2 = jdbcTemplate.queryForObject(sql, BigDecimal.class, id);

        //BigDecimal decimal3 = jdbcTemplate.queryForObject(sql, BigDecimal.class, id);

        //BigDecimal decimal4 = jdbcTemplate.queryForObject(sql, BigDecimal.class, id);

        //BigDecimal decimal5 = jdbcTemplate.queryForObject(sql, BigDecimal.class, id);

        return jdbcTemplate.queryForObject(sql,BigDecimal.class,id);
    }


    /**
     * 添加图书
     * @param book
     */
    public void addBook(Book book){
        String sql = "insert into book(bookName,price,stock) values (?,?,?)";
        jdbcTemplate.update(sql,book.getBookName(),book.getPrice(),book.getStock());
    }


    /**
     * 按照图书id修改图书库存
     * @param bookId 图书id
     * @param num  要减几个
     */
    //@Transactional(propagation = Propagation.REQUIRES_NEW)
    public void updateBookStock(Integer bookId,Integer num){
        String sql = "update book set stock=stock-? where id=?";
        jdbcTemplate.update(sql,num,bookId);
//        int i = 10/0;
    }


    /**
     * 按照id删除图书
     * @param id
     */
    public void deleteBook(Integer id){
        String sql = "delete from book where id=?";
        jdbcTemplate.update(sql,id);
    }
}

AccountDaoTest测试类

@SpringBootTest
public class AccountDaoTest {

    @Autowired
    AccountDao accountDao;

    @Autowired
    UserService userService;

    @Autowired
    TransactionManager transactionManager;


    @Test
    void testTM(){
        System.out.println("transactionManager = " + transactionManager);
        System.out.println(transactionManager.getClass());
    }


    //测试结账
    @Test
    void testcheckout() throws Exception {
        userService.checkout("wangwu",3,4);
    }

    @Test
    void testUpdate() throws InterruptedException {
        accountDao.updateBalanceByUsername("zhangsan", new BigDecimal(9.9));
    }
}

BookDaoTest测试类

@SpringBootTest
class BookDaoTest {

    @Autowired
    DataSource dataSource;


    @Autowired
    JdbcTemplate jdbcTemplate; 
    
    @Autowired
    BookDao bookDao;


    @Test
    void testDelete() {
        bookDao.deleteBook(4);
    }


    @Test
    void testUpdate() {
        bookDao.updateBookStock(4,2);
    }

    @Test
    void testAdd() {
        Book book = new Book();
        book.setBookName("西游记");
        book.setPrice(new BigDecimal("19.9"));
        book.setStock(100);
        bookDao.addBook(book);
    }
    
    @Test
    void testQuery(){
        Book bookById = bookDao.getBookById(1);
        System.out.println("bookById = " + bookById);
    }

    @Test
    void contextLoads() throws SQLException {
        // HikariDataSource；
        // DruidDataSource；
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

    }
}