概念
什么是 JdbcTemplate 类?
Spring 框架对 JDBC 进行了封装,使用 JdbcTemplate 类实现对数据库的操作。
配置 JdbcTemplate 对象
-
引入相关 jar 包
-
在 Spring 配置文件中配置数据库连接池对象
spring5Jdbc.xml<!-- 配置 druid 数据库连接池 --> <!-- 加载 druid 配置文件 --> <context:property-placeholder location="classpath:druid.property"/> <!-- 创建数据库连接池对象 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="${druid.driverClassName}"></property> <property name="url" value="${druid.url}"></property> <property name="username" value="${druid.username}"></property> <property name="password" value="${druid.password}"></property> </bean>
druid.property(src 文件夹下)
druid.driverClassName=com.mysql.cj.jdbc.Driver druid.url=jdbc:mysql://localhost:3306/book_spring?serverTimezone=UTC&rewriteBatchedStatements=true druid.username=root druid.password=密码
-
配置 JdbcTemplate 对象,并为 dataSource 属性注入数据库连接池对象
<!-- 创建 JdbcTemplate 对象,并将数据库连接池对象注入到 JdbcTemplate 对象的 dataSource 属性中 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean>
-
开启组件扫描
<!-- 开启组件扫描,创建使用注解生成的对象 --> <context:component-scan base-package="com.mcc.spring5Jdbc"></context:component-scan>
-
创建对应的类并使用注创建对象,这里以 BookDAO.java,BookDAOImpl.java,BookService.java 为例
BookDAO.javapackage com.mcc.spring5Jdbc.dao; public interface BookDAO { }
BookDAOImpl.java
package com.mcc.spring5Jdbc.dao; @Repository public class BookDAOImpl implements BookDAO{ @Autowired//配置文件中已经创建该对象 private JdbcTemplate jdbcTemplate; }
BookService.java
package com.mcc.spring5Jdbc.service; @Service public class BookService { @Autowired//注入 BookDAOImpl 对象 private BookDAO bookDAO; }
JdbcTemplate 操作
方法 | 作用 |
---|---|
update(String sql,Object… args) | 添加、修改、删除 |
queryForObject(String sql,Class<T> requiredType) | 查询某个值 |
queryForObject(String sql,RowMapper<T> rowMapper,Object… args) | 查询某个对象 |
query(String sql,RowMapper<T> rowMapper,Object… args) | 查询某个集合 |
batchUpdate(String sql,List<Object[]> batchArgs) | 批量添加、修改、删除 |
添加
- update(String sql,Object… args)
BookService.java
/**
* 添加
* @param book
* @return
*/
public int addBook(Book book) {
return bookDAO.add(book);
}
BookDAO.java
public interface BookDAO {
int add(Book book);
}
BookDAOImpl.java
@Override
public int add(Book book) {
//使用 JdbcTemplate 中的方法进行添加操作
String sql = "insert into `t_book` values (?, ?, ?)";
Object[] args = {book.getBookId(), book.getBookName(), book.getBookStatus()};
int update = jdbcTemplate.update(sql, args);
return update;
}
测试
@Test
public void testAdd() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book("1", "数据结构与算法", "checked");
int update = bookService.addBook(book);
System.out.println(update);
context.close();
}
修改、删除
- update(String sql,Object… args)
BookService.java
/**
* 修改
* @param book
* @return
*/
public int updateBook(Book book) {
return bookDAO.update(book);
}
/**
* 删除
* @param id
* @return
*/
public int deleteBook(String id) {
return bookDAO.delete(id);
}
BookDAO.java
public interface BookDAO {
int update(Book book);
int delete(String id);
}
BookDAOImpl.java
@Override
public int update(Book book) {
String sql = "update `t_book` set bookname = ?, bookstatus = ? where book_id = ?";
int update = jdbcTemplate.update(sql, book.getBookName(), book.getBookStatus(), book.getBookId());
return update;
}
@Override
public int delete(String id) {
String sql = "delete from `t_book` where book_id = ?";
int update = jdbcTemplate.update(sql, id);
return update;
}
测试
@Test
public void testUpdate() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book("1", "计算机网络", "unchecked");
int update = bookService.updateBook(book);
System.out.println(update);
context.close();
}
@Test
public void testDelete() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
int update = bookService.deleteBook("1");
System.out.println(update);
context.close();
}
查询
查询某个值
- queryForObject(String sql,Class<T> requiredType):requiredType 代表返回类型的 Class,如果返回的是 int,则为 Integer.class
BookService.java
/**
* 查询记录总数
* @return
*/
public int getCount() {
return bookDAO.count();
}
BookDAO.java
public interface BookDAO {
int count();
}
BookDAOImpl.java
@Override
public int count() {
String sql = "select count(*) from `t_book`";
Integer num = jdbcTemplate.queryForObject(sql, Integer.class);
return num;
}
测试
@Test
public void testCount() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
int num = bookService.getCount();
System.out.println(num);
context.close();
}
查询某个对象
- queryForObject(String sql,RowMapper<T> rowMapper,Object… args):RowMapper 是接口,对不同类型的返回数据,使用该接口的不同实现类完成。普通对象使用 new BeanPropertyRowMapper<类>(类.class) 接收。
BookService.java
/**
* 查询某一本图书
* @return
*/
public Book getBook(String id) {
return bookDAO.getBook(id);
}
BookDAO.java
public interface BookDAO {
Book getBook(String id);
}
BookDAOImpl.java
@Override
public Book getBook(String id) {
String sql = "select * from `t_book` where book_id = ?";
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
测试
@Test
public void testGetBook() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = bookService.getBook("2");
System.out.println(book);
context.close();
}
查询某个集合
- query(String sql,RowMapper<T> rowMapper,Object… args):RowMapper 是接口,对返回的集合中保存的不同类型的数据,使用该接口的不同实现类完成。普通对象使用 new BeanPropertyRowMapper<类>(类.class) 接收。
BookService.java
/**
* 查询全部图书
* @return
*/
public List<Book> getBooks(){
return bookDAO.getBooks();
}
BookDAO.java
public interface BookDAO {
List<Book> getBooks();
}
BookDAOImpl.java
@Override
public List<Book> getBooks() {
String sql = "select * from `t_book`";
List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return bookList;
}
测试
@Test
public void testGetBooks() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Book> bookList = bookService.getBooks();
System.out.println(bookList);
context.close();
}
批量操作
- batchUpdate(String sql,List<Object[]> batchArgs)
批量添加
BookService.java
/**
* 批量添加
* @return
*/
public int[] batchAddBooks(List<Object[]> list) {
return bookDAO.batchAdd(list);
}
BookDAO.java
public interface BookDAO {
int[] batchAdd(List<Object[]> list);
}
``
BookDAOImpl.java
@Override
public int[] batchAdd(List<Object[]> list) {
String sql = "insert into `t_book` values (?, ?, ?)";
int[] update = jdbcTemplate.batchUpdate(sql, list);
return update;
}
测试
@Test
public void testBatchAdd() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> list = new ArrayList<Object[]>();
Object[] o1 = {"5","java","checked"};
Object[] o2 = {"6","python","checked"};
Object[] o3 = {"7","C++","checked"};
list.add(o1);
list.add(o2);
list.add(o3);
int[] update = bookService.batchAddBooks(list);
System.out.println(Arrays.toString(update));
context.close();
}
批量修改
BookService.java
/**
* 批量修改
* @param list
* @return
*/
public int[] batchUpdateBooks(List<Object[]> list) {
return bookDAO.batchUpdate(list);
}
BookDAO.java
public interface BookDAO {
int[] batchUpdate(List<Object[]> list);
}
``
BookDAOImpl.java
@Override
public int[] batchUpdate(List<Object[]> list) {
String sql = "update `t_book` set bookname = ?, bookstatus = ? where book_id = ?";
int[] update = jdbcTemplate.batchUpdate(sql, list);
return update;
}
测试
@Test
public void testBatchUpdate() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> list = new ArrayList<Object[]>();
Object[] o1 = {"java","unchecked","5"};
Object[] o2 = {"python","unchecked","6"};
list.add(o1);
list.add(o2);
int[] update = bookService.batchUpdateBooks(list);
System.out.println(Arrays.toString(update));
context.close();
}
批量删除
BookService.java
/**
* 批量删除
* @param list
* @return
*/
public int[] batchDeleteBooks(List<Object[]> list) {
return bookDAO.batchDelete(list);
}
BookDAO.java
public interface BookDAO {
int[] batchDelete(List<Object[]> list);
}
BookDAOImpl.java
@Override
public int[] batchDelete(List<Object[]> list) {
String sql = "delete from `t_book` where `book_id` = ?";
int[] update = jdbcTemplate.batchUpdate(sql, list);
return update;
}
测试
@Test
public void testBatchDelete() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> list = new ArrayList<Object[]>();
Object[] o1 = {"4"};
Object[] o2 = {"7"};
list.add(o1);
list.add(o2);
int[] update = bookService.batchDeleteBooks(list);
System.out.println(Arrays.toString(update));
context.close();
}
完整实现代码
-
spring5Jdbc.xml
<?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:aop="http://www.springframework.org/schema/aop" 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"> <!-- 配置 druid 数据库连接池 --> <!-- 加载 druid 配置文件 --> <context:property-placeholder location="classpath:druid.property"/> <!-- 创建数据库连接池对象 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="${druid.driverClassName}"></property> <property name="url" value="${druid.url}"></property> <property name="username" value="${druid.username}"></property> <property name="password" value="${druid.password}"></property> </bean> <!-- 创建 JdbcTemplate 对象,并将数据库连接池对象注入到 JdbcTemplate 对象的 dataSource 属性中 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 开启组件扫描,创建使用注解生成的对象 --> <context:component-scan base-package="com.mcc.spring5Jdbc"></context:component-scan> </beans>
-
BookService.java
package com.mcc.spring5Jdbc.service; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.mcc.spring5Jdbc.dao.BookDAO; import com.mcc.spring5Jdbc.pojo.Book; @Service public class BookService { @Autowired//注入 BookDAOImpl 对象 private BookDAO bookDAO; /** * 添加 * @param book * @return */ public int addBook(Book book) { return bookDAO.add(book); } /** * 修改 * @param book * @return */ public int updateBook(Book book) { return bookDAO.update(book); } /** * 删除 * @param id * @return */ public int deleteBook(String id) { return bookDAO.delete(id); } /** * 查询记录总数 * @return */ public int getCount() { return bookDAO.count(); } /** * 查询某一本图书 * @return */ public Book getBook(String id) { return bookDAO.getBook(id); } /** * 查询全部图书 * @return */ public List<Book> getBooks(){ return bookDAO.getBooks(); } /** * 批量添加 * @return */ public int[] batchAddBooks(List<Object[]> list) { return bookDAO.batchAdd(list); } /** * 批量修改 * @param list * @return */ public int[] batchUpdateBooks(List<Object[]> list) { return bookDAO.batchUpdate(list); } /** * 批量删除 * @param list * @return */ public int[] batchDeleteBooks(List<Object[]> list) { return bookDAO.batchDelete(list); } }
-
BookDAO.java
package com.mcc.spring5Jdbc.dao; import java.util.List; import com.mcc.spring5Jdbc.pojo.Book; public interface BookDAO { int add(Book book); int update(Book book); int delete(String id); int count(); Book getBook(String id); List<Book> getBooks(); int[] batchAdd(List<Object[]> list); int[] batchUpdate(List<Object[]> list); int[] batchDelete(List<Object[]> list); }
-
BookDAOImpl.java
package com.mcc.spring5Jdbc.dao; import java.util.List; 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 com.mcc.spring5Jdbc.pojo.Book; @Repository public class BookDAOImpl implements BookDAO{ @Autowired//配置文件中已经创建该对象 private JdbcTemplate jdbcTemplate; @Override public int add(Book book) { //使用 JdbcTemplate 中的方法进行添加操作 String sql = "insert into `t_book` values (?, ?, ?)"; Object[] args = {book.getBookId(), book.getBookName(), book.getBookStatus()}; int update = jdbcTemplate.update(sql, args); return update; } @Override public int update(Book book) { String sql = "update `t_book` set bookname = ?, bookstatus = ? where book_id = ?"; int update = jdbcTemplate.update(sql, book.getBookName(), book.getBookStatus(), book.getBookId()); return update; } @Override public int delete(String id) { String sql = "delete from `t_book` where book_id = ?"; int update = jdbcTemplate.update(sql, id); return update; } @Override public int count() { String sql = "select count(*) from `t_book`"; Integer num = jdbcTemplate.queryForObject(sql, Integer.class); return num; } @Override public Book getBook(String id) { String sql = "select * from `t_book` where book_id = ?"; Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id); return book; } @Override public List<Book> getBooks() { String sql = "select * from `t_book`"; List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class)); return bookList; } @Override public int[] batchAdd(List<Object[]> list) { String sql = "insert into `t_book` values (?, ?, ?)"; int[] update = jdbcTemplate.batchUpdate(sql, list); return update; } @Override public int[] batchUpdate(List<Object[]> list) { String sql = "update `t_book` set bookname = ?, bookstatus = ? where book_id = ?"; int[] update = jdbcTemplate.batchUpdate(sql, list); return update; } @Override public int[] batchDelete(List<Object[]> list) { String sql = "delete from `t_book` where `book_id` = ?"; int[] update = jdbcTemplate.batchUpdate(sql, list); return update; } }
-
TestJdbcTemplate.java
package com.mcc.spring5Jdbc.test; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import org.junit.Test; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.mcc.spring5Jdbc.pojo.Book; import com.mcc.spring5Jdbc.service.BookService; public class TestJdbcTemplate { @Test public void testAdd() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); Book book = new Book("1", "数据结构与算法", "checked"); int update = bookService.addBook(book); System.out.println(update); context.close(); } @Test public void testUpdate() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); Book book = new Book("1", "计算机网络", "unchecked"); int update = bookService.updateBook(book); System.out.println(update); context.close(); } @Test public void testDelete() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); int update = bookService.deleteBook("1"); System.out.println(update); context.close(); } @Test public void testCount() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); int num = bookService.getCount(); System.out.println(num); context.close(); } @Test public void testGetBook() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); Book book = bookService.getBook("2"); System.out.println(book); context.close(); } @Test public void testGetBooks() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Book> bookList = bookService.getBooks(); System.out.println(bookList); context.close(); } @Test public void testBatchAdd() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Object[]> list = new ArrayList<Object[]>(); Object[] o1 = {"5","java","checked"}; Object[] o2 = {"6","python","checked"}; Object[] o3 = {"7","C++","checked"}; list.add(o1); list.add(o2); list.add(o3); int[] update = bookService.batchAddBooks(list); System.out.println(Arrays.toString(update)); context.close(); } @Test public void testBatchUpdate() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Object[]> list = new ArrayList<Object[]>(); Object[] o1 = {"java","unchecked","5"}; Object[] o2 = {"python","unchecked","6"}; list.add(o1); list.add(o2); int[] update = bookService.batchUpdateBooks(list); System.out.println(Arrays.toString(update)); context.close(); } @Test public void testBatchDelete() { ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("spring5Jdbc.xml"); BookService bookService = context.getBean("bookService", BookService.class); List<Object[]> list = new ArrayList<Object[]>(); Object[] o1 = {"4"}; Object[] o2 = {"7"}; list.add(o1); list.add(o2); int[] update = bookService.batchDeleteBooks(list); System.out.println(Arrays.toString(update)); context.close(); } }