spring之JdbcTemplate使用 (4)

1 概念

Spring框架对JDBC进行了封装，使用JdbcTemplate方便实现对数据库的操作。

2 准备工作

导入相关jar包的Maven坐标：

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.2.7.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aspects</artifactId>
    <version>5.2.7.RELEASE</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.23</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.2.7.RELEASE</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
    <scope>test</scope>
</dependency>

sql语句：

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `price` double NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

Book.java

package top.open1024.spring.domain;

import java.io.Serializable;

/**
 * 图书
 */
public class Book implements Serializable {

    /**
     * 主键
     */
    private Long id;

    /**
     * 书名
     */
    private String name;

    /**
     * 价格
     */
    private Double price;

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Double getPrice() {
        return price;
    }

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

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

db.properties

jdbc.url=jdbc:mysql://192.168.1.107:33060/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=123456

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns="http://www.springframework.org/schema/beans"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">

    <!-- 开启注解扫描 -->
    <context:component-scan base-package="top.open1024.spring"></context:component-scan>

    <!-- 导入数据库连接信息 -->
    <context:property-placeholder location="db.properties"></context:property-placeholder>

    <!-- 配置数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driverClass}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <!-- 配置jdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

3 JdbcTemplate的CRUD

BookDao.java

package top.open1024.spring.dao;

import top.open1024.spring.domain.Book;

import java.util.List;

public interface BookDao {
    /**
     * 新增
     *
     * @param book
     */
    void add(Book book);

    /**
     * 更新
     *
     * @param book
     */
    void update(Book book);

    /**
     * 删除
     *
     * @param id
     */
    void delete(Long id);

    /**
     * 查询个数
     *
     * @return
     */
    Long count();

    /**
     * 查询所有信息
     *
     * @return
     */
    List<Book> findAll();

    /**
     * 根据主键查询信息
     *
     * @param id
     * @return
     */
    Book findOne(Long id);

}

BookDaoImpl.java

package top.open1024.spring.dao.impl;

import top.open1024.spring.dao.BookDao;
import top.open1024.spring.domain.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 BookDaoImpl implements BookDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void add(Book book) {
        jdbcTemplate.update(" insert into book (name,price) values (?,?) ", book.getName(), book.getPrice());
    }

    @Override
    public void update(Book book) {
        jdbcTemplate.update(" update book set name =?,price =? where id = ? ", book.getName(), book.getPrice(), book.getId());
    }

    @Override
    public void delete(Long id) {
        jdbcTemplate.update(" delete from book where id =? ", id);
    }

    @Override
    public Long count() {
        return jdbcTemplate.queryForObject(" select count(*) from book ", Long.class);
    }

    @Override
    public List<Book> findAll() {
        return jdbcTemplate.query(" select * from book ", new BeanPropertyRowMapper<>(Book.class));
    }

    @Override
    public Book findOne(Long id) {
        return jdbcTemplate.queryForObject(" select * from book where id = ? ", new BeanPropertyRowMapper<>(Book.class), id);
    }
}

BookService.java

package top.open1024.spring.service;

import top.open1024.spring.domain.Book;

import java.util.List;

public interface BookService {

    /**
     * 新增图书
     *
     * @param book
     */
    void addBook(Book book);

    /**
     * 更新图书
     *
     * @param book
     */
    void updateBook(Book book);


    /**
     * 删除图书
     *
     * @param id
     */
    void deleteBook(Long id);

    /**
     * 查询数据库中有多少本书
     *
     * @return
     */
    Long count();


    /**
     * 查询图书信息
     *
     * @param id
     * @return
     */
    Book view(Long id);

    /**
     * 查询所有图书信息
     *
     * @return
     */
    List<Book> findAllBookList();


}

BookServiceImpl.java

package top.open1024.spring.service.impl;

import top.open1024.spring.dao.BookDao;
import top.open1024.spring.domain.Book;
import top.open1024.spring.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BookServiceImpl implements BookService {

    @Autowired
    private BookDao bookDao;

    @Override
    public void addBook(Book book) {
        bookDao.add(book);
    }

    @Override
    public void updateBook(Book book) {
        bookDao.update(book);
    }

    @Override
    public void deleteBook(Long id) {
        bookDao.delete(id);
    }

    @Override
    public Long count() {
        return bookDao.count();
    }

    @Override
    public Book view(Long id) {
        return bookDao.findOne(id);
    }

    @Override
    public List<Book> findAllBookList() {
        return bookDao.findAll();
    }
}

测试：

package top.open1024.spring;

import top.open1024.spring.domain.Book;
import top.open1024.spring.service.BookService;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

public class Spring5Test {
    private ApplicationContext context = null;
    private BookService bookService = null;

    @Before
    public void before() {
        context = new ClassPathXmlApplicationContext("applicationContext.xml");
        bookService = context.getBean(BookService.class);
    }

    @Test
    public void testAdd() {
        Book book = new Book();
        book.setName("西游记");
        book.setPrice(20.5);

        bookService.addBook(book);
    }

    @Test
    public void testUpdate() {
        Book book = new Book();
        book.setId(1L);
        book.setName("西游记");
        book.setPrice(50.0);

        bookService.updateBook(book);
    }

    @Test
    public void testDelete() {
        bookService.deleteBook(1L);
    }

    @Test
    public void testCount() {
        Long count = bookService.count();
        System.out.println("count = " + count);
    }

    @Test
    public void testView(){
        Book book = bookService.view(1L);
        System.out.println("book = " + book);
    }


    @Test
    public void testFindAllBookList(){
        List<Book> allBookList = bookService.findAllBookList();

        System.out.println("allBookList = " + allBookList);
    }   
}

目录CONTENT

spring之JdbcTemplate使用 (4)

1 概念

2 准备工作

3 JdbcTemplate的CRUD

评论区