Spring 用法学习总结（四）之 JdbcTemplate 连接数据库

🐉目录

9 JdbcTemplate

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

相关包：

百度网盘链接

https://pan.baidu.com/s/1Gw1l6VKc-p4gdqDyD626cg?pwd=6666

创建properties配置文件

💥注意：高版本 MySQL 需要将 useSSL 设置为 false

此外，还需将驱动器修改为适合 MySQL 的版本: mysql-connector-java各种版本下载链接https://mvnrepository.com/artifact/mysql/mysql-connector-java

如果仍报错则将 com.mysql.jdbc.Driver 改为 com.mysql.cj.jdbc.Driver

url=jdbc:mysql://localhost:3306/study?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
name=root
password=123456
driverClassName=com.mysql.cj.jdbc.Driver

study 数据库中的 book 表

导入语法：source sql 文件地址，无法插入中文，请修改数据库编码为 utf-8

create table book(
bookid int(10) primary key auto_increment,
bookname varchar(50),
booksale double(7,2));insert into book(bookname,booksale) values
('Spring揭秘',75.5),
('Spring实战',56),
('Spring源码深入解析',72);

创建 xml 文件

com.alibaba.druid.pool.DruidDataSource 用来配置连接池

org.springframework.jdbc.core.JdbcTemplate 用来连接数据库

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xmlns:tx="http://www.springframework.org/schema/tx"xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsdhttp://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop.xsdhttp://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-2.5.xsd"><!--加载配置文件--><context:property-placeholder location="classpath:user.properties" file-encoding="UTF-8"/><!-- 数据库连接池 --><bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"destroy-method="close"><property name="url" value="${url}" /><property name="username" value="${name}" /><property name="password" value="${password}" /><property name="driverClassName" value="${driverClassName}" /></bean><!-- JdbcTemplate 对象 --><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><!--注入 dataSource--><property name="dataSource" ref="dataSource"></property></bean><!-- 组件扫描 --><context:component-scan base-package="springstudy4"></context:component-scan>
</beans>

Book 类存储书籍信息

Dao 是数据库操作，其中 BookDao 为接口并且声明了对数据库操作的方法，BookDaoIml 类实现了 BookDao 接口

Service 是业务操作，可以调用 BookDao 接口中的方法

Test 类进行测试，通过 Service 类调用 BookDao 接口中的方法

创建 BookDao 接口

package springstudy4;import java.util.List;public interface BookDao {//增void add(Book book);//批量添加void batchAddBook(List<Object[]> batchArgs);//删void delete(int id);//批量删除void batchDeleteBook(List<Object[]> batchArgs);//改void update(Book book);//批量修改void batchUpdateBook(List<Object[]> batchArgs);//查int selectCount();//查询返回对象Book findBookInfo(int id);//查询返回集合List<Book> findAllBook();
}

创建 BookService 类

package springstudy4;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class BookService {//注入 dao@Autowiredprivate BookDao bookDao;//添加public void addBook(Book book) {bookDao.add(book);}//批量添加public void batchAdd(List<Object[]> batchArgs) {bookDao.batchAddBook(batchArgs);}//删除public void deleteBook(int id) {bookDao.delete(id);}//批量删除public void batchDelete(List<Object[]> batchArgs) {bookDao.batchDeleteBook(batchArgs);}//修改public void updateBook(Book book) {bookDao.update(book);}//批量添加public void batchUpdate(List<Object[]> batchArgs) {bookDao.batchUpdateBook(batchArgs);}//查找public void findBook(int id) {bookDao.findBookInfo(id);}//查找全部public List<Book> findAll() {return bookDao.findAllBook();}
}

创建 Book 类

package springstudy4;public class Book {private int bookid;private String bookname;private double booksale;public int getBookid() {return bookid;}public void setBookid(int bookid) {this.bookid = bookid;}public String getBookname() {return bookname;}public void setBookname(String bookname) {this.bookname = bookname;}public double getBooksale() {return booksale;}public void setBooksale(double booksale) {this.booksale = booksale;}@Overridepublic String toString() {return "Book{" +"bookid=" + bookid +", bookname='" + bookname + '\'' +", booksale=" + booksale +'}';}
}

👀创建 BookDaoIml 类（重点）👀

BookDaoIml 类定义了 CRUD 方法，通过 JdbcTemplate 模块可以对数据库中的一条或多条数据进行操作

package springstudy4;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.Arrays;
import java.util.List;@Repository
public class BookDaoImpl implements BookDao {//注入 JdbcTemplate@Autowiredprivate JdbcTemplate jdbcTemplate;//增@Overridepublic void add(Book book) {String sql = "insert into book values(?,?,?)";jdbcTemplate.update(sql, book.getBookid(), book.getBookname(), book.getBooksale());}//批量添加@Overridepublic void batchAddBook(List<Object[]> batchArgs) {String sql = "insert into book values(?,?,?)";int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);System.out.println(Arrays.toString(ints));}//删@Overridepublic void delete(int id) {String sql = "delete from book where bookid=?";int update = jdbcTemplate.update(sql, id);System.out.println(update);}//批量删除@Overridepublic void batchDeleteBook(List<Object[]> batchArgs) {String sql = "delete from book where bookid=?";int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);System.out.println(Arrays.toString(ints));}//改@Overridepublic void update(Book book) {String sql = "update book set bookname=?,booksale=? where bookid=?";Object[] args = {book.getBookname(), book.getBooksale(),book.getBookid()};int update = jdbcTemplate.update(sql, args);System.out.println(update);}//批量修改@Overridepublic void batchUpdateBook(List<Object[]> batchArgs) {String sql = "update book set bookname=?,booksale=? where bookid=?";int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);System.out.println(Arrays.toString(ints));}//查@Overridepublic int selectCount() {String sql = "select count(*) from book";Integer count = jdbcTemplate.queryForObject(sql, Integer.class);return count;}//查询返回对象@Overridepublic Book findBookInfo(int id) {String sql = "select * from book where bookid=?";//调用方法Book book = jdbcTemplate.queryForObject(sql, newBeanPropertyRowMapper<Book>(Book.class), id);return book;}//查询返回集合@Overridepublic List<Book> findAllBook() {String sql = "select * from book";//调用方法List<Book> bookList = jdbcTemplate.query(sql, newBeanPropertyRowMapper<Book>(Book.class));return bookList;}
}

创建 Test 类

package springstudy4;import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;import java.util.ArrayList;
import java.util.List;public class Test {public static void main(String[] args) {ApplicationContext context =new ClassPathXmlApplicationContext("bean4.xml");BookService bookService = context.getBean("bookService", BookService.class);Book book = new Book();List<Book> bookList;List<Object[]> batchArgs = new ArrayList<>();//查找所有bookList = bookService.findAll();for(Book i : bookList) {System.out.println(i);}
//        //增加一条
//        book.setBookid(5);
//        book.setBookname("算法导论");
//        book.setBooksale(65);
//        bookService.addBook(book);
//        //批量添加测试
//        batchArgs.clear();
//        Object[] a1 = {6,"java",26};
//        Object[] a2 = {7,"c++", 66};
//        Object[] a3 = {8,"MySQL",62};
//        batchArgs.add(a1);
//        batchArgs.add(a2);
//        batchArgs.add(a3);
//        //调用批量添加
//        bookService.batchAdd(batchArgs);//批量删除batchArgs.clear();Object[] b1 = {6};Object[] b2 = {7};batchArgs.add(b1);batchArgs.add(b2);//调用方法实现批量删除bookService.batchDelete(batchArgs);//批量修改batchArgs.clear();Object[] c1 = {"java", 66.5, 6};Object[] c2 = {"c++", 77.0, 7};Object[] c3 = {"MySQL",88.5, 8};batchArgs.add(c1);batchArgs.add(c2);batchArgs.add(c3);//调用方法实现批量修改bookService.batchUpdate(batchArgs);}
}