SpringBoot整合持久化层——JDBC

SpringBoot中对JdbcTemplate的使用提供了自动化配置类JdbcTemplateAutoConfiguration。
开发者要想使用JdbcTemplate，只需要提供JdbcTemplate的依赖和DataSource依赖即可。

1、新建项目，pom.xml添加依赖

		<!--JdbcTemplate-->
		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!--数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>
        <!--连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.9</version>
        </dependency>

在pom.xml中添加spring-jdbc、数据库驱动和数据库连接池3个依赖。

2、建数据库springboot，库中建表Book

3、在application.properties中配置数据库基本信息

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot?serverTimezone=GMT%2B8&amp
spring.datasource.username=root
spring.datasource.password=admin

4、Pojo+Dao+Service+Controller

（1）Pojo

public class Book {

    private Integer id;
    private String name;
    private String author;
    private double price;
    private Date publicationDate;

    public Book() {
    }

    public Book(Integer id, String name, String author, double price, Date publicationDate) {
    	this.id=id;
        this.name = name;
        this.author = author;
        this.price = price;
        this.publicationDate = publicationDate;
    }
	
	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;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(Float price) {
        this.price = price;
    }

    public Date getPublicationDate() {
        return publicationDate;
    }

    public void setPublicationDate(Date publicationDate) {
        this.publicationDate = publicationDate;
    }
}

（2）Dao层

//BookDao.java
@Repository
public class BookDao {
    @Autowired
    JdbcTemplate jdbcTemplate;
    public int addBook(Book book) {
        return jdbcTemplate.update("INSERT INTO Book(id,name,author,price,publicationDate) VALUES (?,?,?,?,?)",
                book.geiId(), book.getName(), book.getAuthor(), book.getPrice(), book.getPublicationDate());
    }
    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));
    }
}

● 创建BookDao，注入JdbcTemplate。由于已经添加了spring-jdbc相关的依赖，JdbcTemplate会被自动注册到Spring容器中，因此这里可以直接注入JdbcTemplate使用。
● update/batchUpdate：完成增删改；query/queryForObject：查询；execute：可以用来执行所有的方法；
● 在执行查询操作时，需要有一个RowMapper将查询出来的列和实体类中的属性一一对应起来。如果列名和属性名相同，可以直接使用BeanPropertyRowMapper；如果列名和属性名不同，就需要开发者自己实现RowMapper接口，将列和实体名一一对应起来。

（3）Service层

//BookService.java
@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();
    }
}

（4）Controller层

//BookController.java
@RestController
public class BookController {
    @Autowired
    BookService bookService;
    @GetMapping("/addbook")
    public void addBook() {
        Book book1 = new Book();
        book1.setId(1);
        book1.setName("西游记");
        book1.setAuthor("吴承恩");
        book1.setPrice((float) 40.0);
        book1.setPublicationDate(new Date());
        Book book2 = new Book();
        book2.setId(2);
        book2.setName("三国演义");
        book2.setAuthor("罗贯中");
        book2.setPrice((float) 45.0);
        book2.setPublicationDate(new Date());*/
        bookService.addBook(book1);
        bookService.addBook(book2);
        Book b3 = bookService.getBookById(1);
        System.out.println("getBookById>>>"+b3);
        List<Book> allBooks = bookService.getAllBooks();
        System.out.println("getAllBooks>>>"+allBooks);
    }
}

5、浏览器访问

浏览器中访问http://localhost:8080/{context-path}/addbook即可。