SpringBoot整合持久层技术(夜的第五章)-整合JdbcTemplate

2.创建表格

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
  `author` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '作者',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, '三国演义', '罗贯中');
INSERT INTO `book` VALUES (3, '西厢记', '王世普');

SET FOREIGN_KEY_CHECKS = 1;

创建项目引入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.9.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>interceptor_demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>interceptor_demo</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>8.0.11</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.9</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

yml

server:
  port: 8989
spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/master?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123
    type: com.alibaba.druid.pool.DruidDataSource

mybatis:
  mapper-locations: classpath:mapping/**/*.xml
  type-aliases-package: com.example.interceptor_demo.moredatasource.controller

实体类

package com.example.interceptor_demo.entity;

public class Book {
    private Integer id;
    private String name;
    private String author;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", author='" + author + '\'' +
                '}';
    }
}

controller

package com.example.interceptor_demo.controller;

import com.example.interceptor_demo.dao.BookDao;
import com.example.interceptor_demo.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class BookController {
    @Autowired
    BookDao bookDao;

    @GetMapping("/bookOps")
    public void bookOps(){
        Book b1 = new Book();
        b1.setName("西厢记");
        b1.setAuthor("王世普");
        int i = bookDao.addBook(b1);
        System.out.println("addBook====" + i);
        Book b2 = new Book();
        b2.setId(4);
        b2.setName("朝花夕拾");
        b2.setAuthor("鲁迅");
        int updateBook = bookDao.updateBook(b2);
        System.out.println("updateBook====" + updateBook);
        Book b3 = bookDao.getBookById(1);
        System.out.println("getBookById====" + b3);
        int delete = bookDao.deleteBookById(2);
        System.out.println("deleteBookById====" + delete);
        List<Book> allBooks = bookDao.getAllBooks();
        System.out.println("getAllBooks====" + allBooks);
    }

}

为了省事service省去了

package com.example.interceptor_demo.dao;

import com.example.interceptor_demo.entity.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class BookDao {
    /**
     *         <dependency>
     *             <groupId>org.springframework.boot</groupId>
     *             <artifactId>spring-boot-starter-jdbc</artifactId>
     *         </dependency>
     *          我们引入了这个依赖，JdbcTemplate会被自动注册到Spring容器中，因此可以直接注入
     */
    @Autowired
    JdbcTemplate jdbcTemplate;


    /**
     * 总结:
     *   JdbcTemplate ，增删改三种类型的操作主要使用 update batchUpdate 方法来完成。
     *   query ,queryForObject 方法主要用来完成查询功能。
     *   另外，还有 execute 方法可以用来执行任意的SQL
     *   call 方法用来调用存储过程等。
     */

    /**
     * 添加书籍
     * @param book
     * @return
     */
    public int addBook(Book book){
        return jdbcTemplate.update("insert into book(name,author)values(?,?)",book.getName(),book.getAuthor());
    }

    /**
     * 修改书籍
     * @param book
     * @return
     */
    public int updateBook(Book book){
        return jdbcTemplate.update("update book set name = ?,author = ? where id = ?",book.getName(),book.getAuthor(),book.getId());
    }

    /**
     * 删除书籍
     * @param id
     * @return
     */
    public int deleteBookById(Integer id){
        return jdbcTemplate.update("delete from book where id = ?",id);
    }

    /**
     *  查询书籍
     * @param id
     * @return
     */
    public Book getBookById(Integer id){
        return jdbcTemplate.queryForObject("select * from book where id = ?",new BeanPropertyRowMapper<>(Book.class),id);
    }

    /**
     * 查询所有书籍
     * @return
     */
    public List<Book> getAllBooks(){
        return jdbcTemplate.query("select * from book",new BeanPropertyRowMapper<>(Book.class));
    }
}

结果

addBook====1
updateBook====0
getBookById====Book{id=1, name='三国演义', author='罗贯中'}
deleteBookById====1
getAllBooks====[Book{id=1, name='三国演义', author='罗贯中'}, Book{id=3, name='西厢记', author='王世普'}]
MyInterceptor1>>>>>>>>postHandle
MyInterceptor1>>>>>>>>afterCompletion