springboot 整合JdbcTemplate

JdbcTemplate是Spring提供的一套JDBC模板框架，利用AOP技术来解决直接使用JDBC时大量的重复代码问题。JdbcTemplate虽然没有用Mybatisname灵活，但是也比使用JDBC要灵活的多。

首页先创建一个数据库表：

/*
 Navicat Premium Data Transfer

 Source Server         : docker_mysql5.5
 Source Server Type    : MySQL
 Source Server Version : 50562
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 50562
 File Encoding         : 65001

 Date: 14/01/2020 19:27:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `author` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of books
-- ----------------------------
BEGIN;
INSERT INTO `books` VALUES (1, '三国演义', '罗贯中');
COMMIT;

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.2.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>xyz.fengqing11</groupId>
    <artifactId>hellospringboot</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>hellospringboot</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-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

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

</project>

细心的朋友会发现这里maven管理的mysql驱动是8.0的，但是这并不影响我们连接数据库。

数据库配置

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&amp&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456

这里配置连接数据库必要的url、username、password以及数据库连接池。

创建实体类

package xyz.fengqing11.hellospringboot.pojo;

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

    public int getId() {
        return id;
    }

    public void setId(int 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 + '\'' +
                '}';
    }
}

创建数据库访问层

package xyz.fengqing11.hellospringboot.dao;

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 xyz.fengqing11.hellospringboot.pojo.Book;

import java.util.List;

@Repository
public class BookDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public int addBook(Book book) {
        return jdbcTemplate.update("INSERT INTO books(name,author) values (?,?)",
                book.getName(), book.getAuthor());
    }

    public int updateBook(Book book) {
        return jdbcTemplate.update("UPDATE books SET name=?,author=? WHERE id = ?",
                book.getId(), book.getName(), book.getAuthor());
    }

    public int deleteBookById(Integer id) {
        return jdbcTemplate.update("DElETE FROM books WHERE id=?", id);
    }

    public Book getBookById(Integer id){
        return jdbcTemplate.queryForObject("SELECT * FROM books WHERE id=?",
                new BeanPropertyRowMapper<>(Book.class),id);
    }

    public List<Book> getAllBooks(){
        return jdbcTemplate.query("SELECT * FROM books",
                new BeanPropertyRowMapper<>(Book.class));
    }
}

创建Service和Controller

package xyz.fengqing11.hellospringboot.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import xyz.fengqing11.hellospringboot.dao.BookDao;
import xyz.fengqing11.hellospringboot.pojo.Book;

import java.util.List;

@Service
public class BookService {
    @Autowired
    BookDao bookDao;
    public int addBook(Book book){
        return bookDao.addBook(book);
    }

    public int updateBook(Book book){
        return bookDao.updateBook(book);
    }

    public int deleteBookByid(Integer id){
        return bookDao.deleteBookById(id);
    }

    public Book getBookById(Integer id){
        return bookDao.getBookById(id);
    }

    public List<Book> getAllBooks(){
        return bookDao.getAllBooks();
    }
}

package xyz.fengqing11.hellospringboot.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import xyz.fengqing11.hellospringboot.pojo.Book;
import xyz.fengqing11.hellospringboot.service.BookService;

import java.util.List;

@RestController
public class BookController {
    @Autowired
    BookService bookService;

    @GetMapping("/bookOps")
    public void bookOps() {
        Book b1 = new Book();
        b1.setId(1);
        b1.setName("西厢记");
        b1.setAuthor("王实甫");
        int i = bookService.addBook(b1);
        System.out.println("addBook>>>" + i);


        Book b2 = new Book();
        b2.setId(1);
        b2.setName("朝花夕拾");
        b2.setAuthor("鲁迅");
        int updateBook = bookService.updateBook(b2);
        System.out.println("updateBook>>>" + updateBook);

        Book b3 = bookService.getBookById(1);
        System.out.println("getBookById>>>" + b3);

        int delete = bookService.deleteBookByid(2);
        System.out.println("deleteBookById>>>" + delete);

        List<Book> allBooks = bookService.getAllBooks();
        System.out.println("getAllBooks>>>" + allBooks);

    }
}

完整项目结构：

---

-end-