文章目录
前言
跟随尚硅谷学习Spring5
JdbcTemplate各类操作
一、JdbcTemplate(概念和准备)
- 什么是 JdbcTemplate
- Spring 框架对 JDBC 进行封装,使用 JdbcTemplate 方便实现对数据库操作
- 准备工作
引入相关 jar 包
在 spring 配置文件配置数据库连接池
package com.atguigu.spring5.jdbctemplate.service;
import com.atguigu.spring5.jdbctemplate.dao.BookDao;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class BookService {
// service类里注入dao
@Autowired
private BookDao bookDao;
// 添加的方法
public int addBook(Books books) {
return bookDao.add(books);
}
public int updateBook(Books books) {
return bookDao.update(books);
}
public int deleteBook(Books books) {
return bookDao.delete(books);
}
public int queryBookTableCount() {
return bookDao.queryTableCount();
}
public Books queryBookById(Books books) {
return bookDao.queryById(books);
}
public List<Books> queryBookByName(Books books) {
return bookDao.queryByName(books);
}
public List<Books> queryBookAll() {
return bookDao.queryAll();
}
// 批量添加
public void batchAddBook(List<Object[]> batchArgs) {
bookDao.batchAdd(batchArgs);
}
public void batchUpdateBook(List<Object[]> batchArgs) {
bookDao.batchUpdateBook(batchArgs);
}
// 拓展方法便于查找动态bookId
public int getBookIdByBookName(Books books) {
List<Books> booksList = this.queryBookByName(books);
return booksList.get(0).getBookID();
}
public void batchDeleteBook(List<Object[]> batchArgs) {
bookDao.batchDeleteBook(batchArgs);
}
}
<!--数据库连接池-->
<context:property-placeholder location="classpath:jdbc.properties"/>
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
配置 JdbcTemplate 对象,注入 DataSource
配置文件
<!--配置JdbcTemplate对象,注入DataSource-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
创建 service 类,创建 dao 类,在 dao 注入 jdbcTemplate 对象
配置文件
<!--开启组件扫描-->
<context:component-scan base-package="com.atguigu.spring5.jdbctemplate"/>
Service
package com.atguigu.spring5.jdbctemplate.service;
import com.atguigu.spring5.jdbctemplate.dao.BookDao;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class BookService {
// service类里注入dao
@Autowired
private BookDao bookDao;
}
Dao
ackage com.atguigu.spring5.jdbctemplate.dao;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.util.Arrays;
import java.util.List;
@Repository
public class BookDaoImpl implements BookDao {
// dao类里注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
}
二、JdbcTemplate 操作数据库(添加)
对应数据库创建实体类
package com.atguigu.spring5.jdbctemplate.entity;
public class Books {
private int bookID;
private String bookName;
private int bookCounts;
private String detail;
public Books() {
}
public Books(int bookID, String bookName, int bookCounts, String detail) {
this.bookID = bookID;
this.bookName = bookName;
this.bookCounts = bookCounts;
this.detail = detail;
}
public Books(String bookName) {
this.bookName = bookName;
}
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 int getBookCounts() {
return bookCounts;
}
public void setBookCounts(int bookCounts) {
this.bookCounts = bookCounts;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail;
}
@Override
public String toString() {
return "Books{" +
"bookID=" + bookID +
", bookName='" + bookName + '\'' +
", bookCounts=" + bookCounts +
", detail='" + detail + '\'' +
'}';
}
}
编写 service 和 dao
在 dao 进行数据库添加操作
调用 JdbcTemplate 对象里面 add 方法实现添加操作
update(String sql,0bject. . . args)
有两个参数
第一个参数:sql语句
第二个参数:可变参数,设置sql语句值
package com.atguigu.spring5.jdbctemplate.dao;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.util.Arrays;
import java.util.List;
@Repository
public class BookDaoImpl implements BookDao {
// dao类里注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 增删改:jdbcTemplate.(String sql, Object... args)
*/
// 添加书籍
public int add(Books books) {
// 1. 创建sql语句, ?表示占位符,可被jdbcTemplate参数形式替换
String sql = "INSERT INTO t_books (bookID,bookName, bookCounts, detail) VALUE (?,?,?,?)";
// 2。 调用方法实现,返回影响行数
// 写法1.
// int update = jdbcTemplate.update(sql, books.getBookName(), books.getBookCounts(), books.getDetail());
// 写法2.
Object[] args = {books.getBookName(), books.getBookCounts(), books.getDetail()};
int update = jdbcTemplate.update(sql, args);
System.out.println("数据库INSERT添加操作,受影响的行数为:" + update);
return update;
}
}
测试:
package com.atguigu.sprint5;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import com.atguigu.spring5.jdbctemplate.service.BookService;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestJdbcTemplate {
@Test
public void testJdbcTemplate_a1_INSERT() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Books books = new Books();
books.setBookID(1);
books.setBookName("诗经");
books.setBookCounts(20);
books.setDetail("中国最早的诗歌总集");
bookService.addBook(books);
}
}
三、JdbcTemplate 操作数据库(修改和删除)
对应数据库创建实体类,和添加操作相同的实体类
编写 service 和 dao
在 dao 进行数据库添加操作
调用 JdbcTemplate 对象里面 update 和delete 方法实现添加操作
package com.atguigu.spring5.jdbctemplate.dao;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.util.Arrays;
import java.util.List;
@Repository
public class BookDaoImpl implements BookDao {
// dao类里注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 增删改:jdbcTemplate.(String sql, Object... args)
*/
// 修改书籍
public int update(Books books) {
String sql = "UPDATE t_books SET bookName=?, bookCounts=?, detail=? WHERE bookID=?";
Object[] args = {books.getBookName(), books.getBookCounts(), books.getDetail(), books.getBookID()};
int update = jdbcTemplate.update(sql, args);
System.out.println("数据库UPDATE更新操作,受影响的行数为:" + update);
return update;
}
// 删除书籍
public int delete(Books books) {
String sql = "DELETE FROM t_books WHERE bookID = ?";
Object[] args = {books.getBookID()};
int update = jdbcTemplate.update(sql, args);
System.out.println("数据库DELETE删除操作,受影响的行数为:" + update);
return update;
}
}
测试:
package com.atguigu.sprint5;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import com.atguigu.spring5.jdbctemplate.service.BookService;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestJdbcTemplate {
@Test
public void testJdbcTemplate_a2_Update() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
// 通过书名拿到对应Id的所有书籍列表
Books books = new Books();
books.setBookID(2);
books.setBookName("诗经");
List<Books> booksList = bookService.queryBookByName(books);
// 将第一条记录更新书籍信息
booksList.get(0).setBookName("乾坤大挪移");
booksList.get(0).setBookCounts(7);
booksList.get(0).setDetail("至第七层者实是古往今来第一人");
bookService.updateBook(booksList.get(0));
}
@Test
public void testJdbcTemplate_a3_Delete() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
// 通过书名拿到对应Id的所有书籍列表
Books books = new Books();
books.setBookName("乾坤大挪移");
List<Books> booksList = bookService.queryBookByName(books);
for (Books book: booksList) {
// 删除同名所有书籍
bookService.deleteBook(book);
}
}
}
四、JdbcTemplate 操作数据库(查询返回某个值)
查询表里面有多少条记录,返回是某个值
使用 JdbcTemplate 实现查询返回某个值代码
queryFor0bject(String sql,Class<T> requiredType)
有两个参数
第一个参数:sql语句
第二个参数:返回类型class
对应数据库创建实体类,和添加操作相同的实体类
编写 service 和 dao
在 dao 进行数据库添加操作
调用 JdbcTemplate 对象里面 queryTableCount 方法实现添加操作
package com.atguigu.spring5.jdbctemplate.dao;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.util.Arrays;
import java.util.List;
@Repository
public class BookDaoImpl implements BookDao {
// dao类里注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 查询表记录值:jdbcTemplate.queryForObject(String sql, Class<T> requiredType)
* @return 值
*/
// 查询表记录数
public int queryTableCount() {
String sql = "SELECT COUNT(*) FROM t_books";
int count = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println("数据库SELECT查询操作,queryTableCount():" + count);
return count;
}
}
测试:
package com.atguigu.sprint5;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import com.atguigu.spring5.jdbctemplate.service.BookService;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestJdbcTemplate {
@Test
public void testJdbcTemplate_a4_QueryBookTableCount() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
bookService.queryBookTableCount();
}
}
五、JdbcTemplate 操作数据库(查询返回对象)
查询图书详情
JdbcTemplate 实现查询返回对象
queryFor0bject(String sql,RowMapper<T> rowMapper,0bject.. . args)
有三个参数
第一个参数:sql语句
第二个参数:RowMapper是接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
第三个参数:sql语句值
对应数据库创建实体类,和添加操作相同的实体类
编写 service 和 dao
在 dao 进行数据库添加操作
调用 JdbcTemplate 对象里面 queryById 方法实现添加操作
package com.atguigu.spring5.jdbctemplate.dao;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.util.Arrays;
import java.util.List;
@Repository
public class BookDaoImpl implements BookDao {
// dao类里注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 查询一个书籍对象:jdbcTemplate.queryForObject(String sql, RowMapper<T> rowMapper, Object... args)
* @return 对象
*/
// 通过书籍id查询书籍 - 最多返回一个值(书籍)
public Books queryById(Books books) {
String sql = "SELECT * FROM t_books WHERE bookID=?";
Object[] args = {books.getBookID()};
RowMapper<Books> rowMapper = new BeanPropertyRowMapper<Books>(Books.class);
Books book = jdbcTemplate.queryForObject(sql, rowMapper, args);
System.out.println("数据库SELECT查询操作,queryById()");
return book;
}
}
测试:
package com.atguigu.sprint5;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import com.atguigu.spring5.jdbctemplate.service.BookService;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestJdbcTemplate {
@Test
public void testJdbcTemplate_a5_QueryBookById() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Books books = new Books();
books.setBookID(1);
System.out.println("数据库SELECT查询操作,testJdbcTemplate5_QueryBookById():" + bookService.queryBookById(books));
}
}
六、JdbcTemplate 操作数据库(查询返回集合)
查询图书列表分页
JdbcTemplate 实现查询返回对象
query(String sql,RowMapper<T> rowMapper,Object.. . args)
有三个参数
第一个参数: sql语句
第二个参数:RowMapper是接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
第三个参数: sql语句值
对应数据库创建实体类,和添加操作相同的实体类
编写 service 和 dao
在 dao 进行数据库添加操作
调用 JdbcTemplate 对象里面 queryByName 和 queryAll 方法实现添加操作
package com.atguigu.spring5.jdbctemplate.dao;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.util.Arrays;
import java.util.List;
@Repository
public class BookDaoImpl implements BookDao {
// dao类里注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 查询书籍集合:jdbcTemplate.query(String sql, RowMapper<T> rowMapper, Object... args)
* @return 集合
*/
// 通过书籍name查询书籍 - 返回书籍集合
public List<Books> queryByName(Books books) {
String sql = "SELECT * FROM t_books WHERE bookName=?";
Object[] args = {books.getBookName()};
RowMapper<Books> rowMapper = new BeanPropertyRowMapper<Books>(Books.class);
List<Books> booksList = jdbcTemplate.query(sql, rowMapper, args);
System.out.println("数据库SELECT查询操作,queryByName()");
return booksList;
}
// 查询所有书籍 - 返回书籍集合
public List<Books> queryAll() {
String sql = "SELECT * FROM t_books";
RowMapper<Books> rowMapper = new BeanPropertyRowMapper<Books>(Books.class);
List<Books> booksList = jdbcTemplate.query(sql, rowMapper);
System.out.println("数据库SELECT查询操作,queryByName()");
return booksList;
}
}
测试:
package com.atguigu.sprint5;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import com.atguigu.spring5.jdbctemplate.service.BookService;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestJdbcTemplate {
@Test
public void testJdbcTemplate_a6_QueryBookByName() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Books books = new Books();
books.setBookName("诗经");
System.out.println("数据库SELECT查询操作,testJdbcTemplate6_QueryBookByName():" + bookService.queryBookByName(books));
}
@Test
public void testJdbcTemplate_a7_QueryBookAll() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
System.out.println("数据库SELECT查询操作,testJdbcTemplate7_QueryBookAll():" + bookService.queryBookAll());
}
}
七、JdbcTemplate 操作数据库(批量操作)
操作表里面多条记录
JdbcTemplate 实现批量添加操作
batchUpdate(String sql,List<0bject[> batchArgs)
有两个参数
第一个参数: sql语句
第二个参数:List集合,添加多条记录数据
对应数据库创建实体类,和添加操作相同的实体类
编写 service 和 dao
在 dao 进行数据库添加操作
调用 JdbcTemplate 对象里面 batchAdd 、batchUpdateBook 、batchDeleteBook方法实现添加操作
package com.atguigu.spring5.jdbctemplate.dao;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.util.Arrays;
import java.util.List;
@Repository
public class BookDaoImpl implements BookDao {
// dao类里注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 数据库批量操作
* @param batchArgs
*/
// 批量添加书籍
public void batchAdd(List<Object[]> batchArgs) {
String sql = "INSERT INTO t_books (bookName, bookCounts, detail) VALUE (?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println("数据库INSERT添加的batch操作:" + Arrays.toString(ints));
}
public void batchUpdateBook(List<Object[]> batchArgs) {
String sql = "UPDATE t_books SET bookName=?, bookCounts=?, detail=? WHERE bookID=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println("数据库UPDATE修改的batch操作:" + Arrays.toString(ints));
}
public void batchDeleteBook(List<Object[]> batchArgs) {
String sql = "DELETE FROM t_books WHERE bookID = ?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println("数据库DELETE删除的batch操作:" + Arrays.toString(ints));
}
}
测试:
package com.atguigu.sprint5;
import com.atguigu.spring5.jdbctemplate.entity.Books;
import com.atguigu.spring5.jdbctemplate.service.BookService;
import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;
public class TestJdbcTemplate {
@Test
public void testJdbcTemplate_a8_batchAddBook() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<Object[]>();
Object[] o1 = {"九阳神功", "99", "絕頂之內功心法,威力可能與另一路少林無上神功《易筋經》難分上下"};
Object[] o2 = {"九阴真经", "9", "武林中眾人夢寐以求的至寶,也是金庸小說最絕頂的武功之一"};
Object[] o3 = {"双剑合璧", "22", "一般用来指两个各有特色的人或集体,在配合时能够互相产生极大的辅助作用"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchAddBook(batchArgs);
}
@Test
public void testJdbcTemplate_a9_batchUpdateBook() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
// 通过书名拿到对应Id的所有书籍列表的第一个
List<Object[]> batchArgs = new ArrayList<Object[]>();
Object[] o1 = {"九阳神功", "199", "絕頂之內功心法,威力可能與另一路少林無上神功《易筋經》難分上下", bookService.getBookIdByBookName(new Books("九阳神功"))};
Object[] o2 = {"九阴真经", "19", "武林中眾人夢寐以求的至寶,也是金庸小說最絕頂的武功之一", bookService.getBookIdByBookName(new Books("九阴真经"))};
Object[] o3 = {"双剑合璧", "122", "一般用来指两个各有特色的人或集体,在配合时能够互相产生极大的辅助作用", bookService.getBookIdByBookName(new Books("双剑合璧"))};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchUpdateBook(batchArgs);
}
@Test
public void testJdbcTemplate_b1_batchDeleteBook() {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-jdbctemplate.xml");
BookService bookService = context.getBean("bookService", BookService.class);
// 通过书名拿到对应Id的所有书籍列表的第一个
List<Object[]> batchArgs = new ArrayList<Object[]>();
Object[] o1 = {bookService.getBookIdByBookName(new Books("九阳神功"))};
Object[] o2 = {bookService.getBookIdByBookName(new Books("九阴真经"))};
Object[] o3 = {bookService.getBookIdByBookName(new Books("双剑合璧"))};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchDeleteBook(batchArgs);
}
}