springboot learning - integrate JdbcTemplate

12:59:22, February 1, 2020

The epidemic continues, disrupting many of my plans

It's also good to study at home

Introduction to JdbcTemplate

Spring encapsulates the operation of database deeply on jdbc. With spring's injection function, DataSource can be registered in JdbcTemplate.

The JdbcTemplate is located atMedium. Its fully qualified name is org.springframework.jdbc.core.JdbcTemplate. To use JdbcTemlate, you need anotherThis package includes transaction and exception control

JdbcTemplate mainly provides the following five methods:

Execute method: can be used to execute any SQL statement, generally used to execute DDL statements;
Update method and batchUpdate method: update method is used to execute new, modified, deleted statements; batchUpdate method is used to execute batch related statements;
Query method and queryForXXX method: used to execute query related statements;
call method: used to execute stored procedures and function related statements.

JdbcTemplate is a set of JDBC template framework provided by Spring. It uses AOP technology to solve the problem of a large number of duplicate code when using JDBC directly. Although JdbcTemplate is not as flexible as Mybatis, it is much more convenient than using JDBC directly. The use of JdbcTemplate in SpringBoot provides the automatic configuration class of JdbcTemplateAutoConfiguration.

1. create table

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50625
Source Host           : localhost:3306
Source Database       : chapter05

Target Server Type    : MYSQL
Target Server Version : 50625
File Encoding         : 65001

Date: 2020-02-01 13:39:40
*/

SET FOREIGN_KEY_CHECKS=0;

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

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('1', 'Romance of the Three Kingdoms', 'Luo Guanzhong');
INSERT INTO `book` VALUES ('2', 'The Dream of Red Mansion', 'Cao Xueqin');

2. Create a springboot project and add a dependency:

        <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.9</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

Spring JDBC is provided in spring boot starter JDBC. In addition, database driver dependency and database connection pool dependency are added.

3. Database configuration

Configure the basic database connection information in application.properties:

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql:///chapter05
spring.datasource.username=root
spring.datasource.password=abcdABCD123

4. Create entity class

Create the Book entity class with the following code:

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

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", author='" + 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;
    }
}

5. Create database access layer

Create BookDao with the following code:

@Repository
public class BookDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    //Column name and property name are consistent
    public int addBook(Book book) {
        return jdbcTemplate.update("INSERT INTO book(name,author) VALUES (?,?)",
                book.getName(), book.getAuthor());
    }
    public int updateBook(Book book) {
        return jdbcTemplate.update("UPDATE book SET name=?,author=? WHERE id=?",
                book.getName(), book.getAuthor(), book.getId());
    }
    public int deleteBookById(Integer id) {
        return jdbcTemplate.update("DELETE FROM book WHERE id=?", id);
    }
    public Book getBookById(Integer id) {
        return jdbcTemplate.queryForObject("select * from book where id=?",
                new BeanPropertyRowMapper<>(Book.class), id);
    }
    public List<Book> getAllBooks() {
        return jdbcTemplate.query("select * from book",
                new BeanPropertyRowMapper<>(Book.class));
    }
}

6. Create Sevice and Controller

Create bookservice and BookController as follows:

@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();
    }
}

@RestController
public class BookController {
    @Autowired
    BookService bookService;
    @GetMapping("/bookOps")
    public void bookOps() {
        Book b1 = new Book();
        b1.setId(99);
        b1.setName("Romance of the West Chamber");
        b1.setAuthor("Wang Shipu");
        int i = bookService.addBook(b1);
        System.out.println("addBook>>>" + i);
        Book b2 = new Book();
        b2.setId(1);
        b2.setName("Morning and evening");
        b2.setAuthor("Lu Xun");
        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);
    }
}