JdbcTemplate
文章目录
1、JdbcTemplate(概念和准备)
1.1、什么是 JdbcTemplate?
Spring 框架对 JDBC 进行封装,使用 JdbcTemplate 方便实现对数据库操作。
1.2、准备工作
(1)引入相关 jar 包
(2)在 spring 配置文件配置数据库连接池
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://10.150.104.5:3306/user_db" />
<property name="username" value="root" />
<property name="password" value="Opfordream@0518" />
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
</bean>
(3)配置 JdbcTemplate 对象,注入 DataSource
<!--创建JdbcTemplate对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入dataSource-->
<property name="dataSource" ref="dataSource"></property>
</bean>
(4)创建 service 类,创建 dao 类,在 dao 注入 jdbcTemplate 对象
配置文件:
<!--开启组件扫描-->
<context:component-scan base-package="spring5"></context:component-scan>
Service:
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import spring5.dao.BookDao;
@Service
public class BookService {
//注入Dao
@Autowired
private BookDao bookDao;
}
Dao:
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class BookDaoImpl {
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
}
2、JdbcTemplate 操作数据库(添加)
mysql> desc t_book;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| user_id | int(20) | NO | PRI | NULL | |
| username | varchar(100) | NO | | NULL | |
| ustatus | varchar(50) | NO | | NULL | |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
(1)对应数据库创建实体类
public class Book {
private String userid;
private String username;
private String ustatus;
public void setUserid(String userid) {
this.userid = userid;
}
public void setUsername(String username) {
this.username = username;
}
public void setUstatus(String ustatus) {
this.ustatus = ustatus;
}
}
(2)编写 service 和 dao
- 在 dao 进行数据库添加操作
- 调用 JdbcTemplate 对象里面 update 方法实现添加操作,有两个参数:
- 第一个参数: sql 语句
- 第二个参数:可变参数,设置 sql 语句值
@Repository
public class BookDaoImpl implements BookDao{
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
//添加的方法
@Override
public void add(Book book) {
//1 创建sql语句
String sql = "insert into t_book values(?,?,?)";
//2 调用方法实现
Object[] args = {book.getUserid(), book.getUsername(), book.getUstatus()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
}
(3)测试代码
public class TestTemplate {
@Test
public void testAdd() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setUserid("1");
book.setUsername("java");
book.setUstatus("a");
bookService.addBook(book);
}
}
mysql> select * from t_book;
+---------+----------+---------+
| user_id | username | ustatus |
+---------+----------+---------+
| 1 | java | a |
+---------+----------+---------+
1 row in set (0.00 sec)
3、JdbcTemplate 操作数据库(修改和删除)
@Override
public void updateBook(Book book) {
//1 创建sql语句
String sql = "update t_book set username=?, ustatus=? where user_id=?";
//2 调用方法实现
Object[] args = {book.getUsername(), book.getUstatus(), book.getUserid()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
@Override
public void deleteBook(String id) {
//1 创建sql语句
String sql = "delete from t_book where user_id=?";
//2 调用方法实现
int update = jdbcTemplate.update(sql, id);
System.out.println(update);
}
测试代码:
@Test
public void testUpdate() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setUserid("1");
book.setUsername("javaupup");
book.setUstatus("at");
bookService.updateBook(book);
}
mysql> select * from t_book;
+---------+----------+---------+
| user_id | username | ustatus |
+---------+----------+---------+
| 1 | javaupup | at |
+---------+----------+---------+
1 row in set (0.00 sec)
@Test
public void testDelete() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
String id = "1";
bookService.deleteBook(id);
}
mysql> select * from t_book;
Empty set (0.00 sec)
4、JdbcTemplate 操作数据库(查询返回某个值)
(1)查询表里面有多少条记录,返回是某个值
mysql> select count(*) from t_book;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
(2)使用 JdbcTemplate 实现查询返回某个值代码
使用 queryForObject 方法,有两个参数:
- 第一个参数: sql 语句
- 第二个参数:返回类型 Class
@Override
public int selectCount() {
//1 创建sql语句
String sql = "select count(*) from t_book;";
//2 调用方法实现
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
测试代码:
@Test
public void testCount() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
int count = bookService.findCount();
System.out.println(count); //0
}
5、JdbcTemplate 操作数据库(查询返回对象)
(1)场景:查询图书详情
(2)JdbcTemplate 实现查询返回对象:使用 queryForObject,有三个参数
- 第一个参数: sql 语句
- 第二个参数: RowMapper 是接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
- 第三个参数: sql 语句值
mysql> select * from t_book;
+---------+----------+---------+
| user_id | username | ustatus |
+---------+----------+---------+
| 1 | javaupup | a |
+---------+----------+---------+
1 row in set (0.00 sec)
@Override
public Book findBookInfo(String id) {
//1 创建sql语句
String sql = "select * from t_book where user_id=?;";
//2 调用方法实现
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
测试代码:
@Test
public void testOne() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = bookService.findOne("1");
System.out.println(book); //Book{userid='null', username='javaupup', ustatus='a'}
}
6、JdbcTemplate 操作数据库(查询返回集合)
(1)场景:查询图书列表分页…
(2)调用 JdbcTemplate 方法实现查询返回集合,使用 query,有三个参数
- 第一个参数: sql 语句
- 第二个参数: RowMapper 是接口,针对返回不同类型数据,使用这个接口里面实现类完成数据封装
- 第三个参数: sql 语句值
mysql> select * from t_book;
+---------+----------+---------+
| user_id | username | ustatus |
+---------+----------+---------+
| 1 | javaupup | a |
| 2 | mysql | b |
+---------+----------+---------+
2 rows in set (0.00 sec)
@Override
public List<Book> findAllBook() {
//1 创建sql语句
String sql = "select * from t_book";
//2 调用方法实现
List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return bookList;
}
测试代码:
@Test
public void testAll() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Book> bookList = bookService.findAll();
System.out.println(bookList);
//[Book{userid='null', username='javaupup', ustatus='a'}, Book{userid='null',username='mysql', ustatus='b'}]
}
7、JdbcTemplate 操作数据库(批量操作)
(1)批量操作:操作表里面多条记录
(2)JdbcTemplate 实现批量添加操作,使用 batchUpdate 方法,有两个参数:
- 第一个参数: sql 语句
- 第二个参数: List 集合,添加多条记录数据
不管是批量添加、修改还是删除,都使用这个方法。
7.1、JdbcTemplate 实现批量添加操作
service 类中:
public void batchAdd(List<Object[]> batchArgs) {
return bookDao.batchAddBook(batchArgs);
}
Dao 类中:
@Override
public void batchAddBook(List<Object[]> batchArgs) {
//1 创建sql语句
String sql = "insert into t_book values(?,?,?)";
//2 调用方法实现
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试代码:
@Test
public void testAddAll() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"3", "javaweb", "c"};
Object[] o2 = {"4", "spring", "d"};
Object[] o3 = {"5", "redis", "e"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchAdd(batchArgs);
// [1, 1, 1]
}
mysql> select * from t_book;
+---------+----------+---------+
| user_id | username | ustatus |
+---------+----------+---------+
| 1 | javaupup | a |
| 2 | mysql | b |
| 3 | javaweb | c |
| 4 | spring | d |
| 5 | redis | e |
+---------+----------+---------+
5 rows in set (0.00 sec)
7.2、JdbcTemplate 实现批量修改操作
@Override
public void batchUpdateBook(List<Object[]> batchArgs) {
//1 创建sql语句
String sql = "update t_book set username=?, ustatus=? where user_id=?";
//2 调用方法实现
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(ints);
}
测试代码:
@Test
public void testUpdateAll() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"javawebLi", "c3", "3"};
Object[] o2 = {"springLi", "d4", "4"};
Object[] o3 = {"redisLi", "e5", "5"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchUpdate(batchArgs);
}
mysql> select * from t_book;
+---------+-----------+---------+
| user_id | username | ustatus |
+---------+-----------+---------+
| 1 | javaupup | a |
| 2 | mysql | b |
| 3 | javawebLi | c3 |
| 4 | springLi | d4 |
| 5 | redisLi | e5 |
+---------+-----------+---------+
5 rows in set (0.00 sec)
7.3、JdbcTemplate 实现批量删除操作
@Override
public void batchDeleteBook(List<Object[]> batchArgs) {
//1 创建sql语句
String sql = "delete from t_book where user_id=?";
//2 调用方法实现
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(ints);
}
测试代码:
@Test
public void testDeleteAll() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"3"};
Object[] o2 = {"4"};
batchArgs.add(o1);
batchArgs.add(o2);
bookService.batchDelete(batchArgs);
}
mysql> select * from t_book;
+---------+----------+---------+
| user_id | username | ustatus |
+---------+----------+---------+
| 1 | javaupup | a |
| 2 | mysql | b |
| 5 | redisLi | e5 |
+---------+----------+---------+
3 rows in set (0.00 sec)
附:所有类的代码
<?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"
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">
<!--开启组件扫描-->
<context:component-scan base-package="spring5"></context:component-scan>
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://10.150.104.5:3306/user_db" />
<property name="username" value="root" />
<property name="password" value="Opfordream@0518" />
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
</bean>
<!--创建JdbcTemplate对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入dataSource-->
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
package spring5.entity;
public class Book {
private String userid;
private String username;
private String ustatus;
public void setUserid(String userid) {
this.userid = userid;
}
public void setUsername(String username) {
this.username = username;
}
public void setUstatus(String ustatus) {
this.ustatus = ustatus;
}
public String getUserid() {
return userid;
}
public String getUsername() {
return username;
}
public String getUstatus() {
return ustatus;
}
@Override
public String toString() {
return "Book{" +
"userid='" + userid + '\'' +
", username='" + username + '\'' +
", ustatus='" + ustatus + '\'' +
'}';
}
}
package spring5.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import spring5.dao.BookDao;
import spring5.entity.Book;
import java.util.List;
@Service
public class BookService {
//注入Dao
@Autowired
private BookDao bookDao;
//添加的方法
public void addBook(Book book) {
bookDao.add(book);
}
//修改的方法
public void updateBook(Book book) {
bookDao.updateBook(book);
}
//删除的方法
public void deleteBook(String id) {
bookDao.deleteBook(id);
}
//查询表记录的方法
public int findCount() {
return bookDao.selectCount();
}
//查询表记录数
public Book findOne(String id) {
return bookDao.findBookInfo(id);
}
//查询返回集合
public List<Book> findAll() {
return bookDao.findAllBook();
}
//批量添加
public void batchAdd(List<Object[]> batchArgs) {
bookDao.batchAddBook(batchArgs);
}
//批量修改
public void batchUpdate(List<Object[]> batchArgs) {
bookDao.batchUpdateBook(batchArgs);
}
//批量删除
public void batchDelete(List<Object[]> batchArgs) {
bookDao.batchDeleteBook(batchArgs);
}
}
package spring5.dao;
import spring5.entity.Book;
import java.util.List;
public interface BookDao {
//添加的方法
void add(Book book);
void updateBook(Book book);
void deleteBook(String id);
int selectCount();
Book findBookInfo(String id);
List<Book> findAllBook();
void batchAddBook(List<Object[]> batchArgs);
void batchUpdateBook(List<Object[]> batchArgs);
void batchDeleteBook(List<Object[]> batchArgs);
}
package spring5.dao;
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 spring5.entity.Book;
import java.util.Arrays;
import java.util.List;
@Repository
public class BookDaoImpl implements BookDao{
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
//添加的方法
@Override
public void add(Book book) {
//1 创建sql语句
String sql = "insert into t_book values(?,?,?)";
//2 调用方法实现
Object[] args = {book.getUserid(), book.getUsername(), book.getUstatus()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
@Override
public void updateBook(Book book) {
//1 创建sql语句
String sql = "update t_book set username=?, ustatus=? where user_id=?";
//2 调用方法实现
Object[] args = {book.getUsername(), book.getUstatus(), book.getUserid()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
@Override
public void deleteBook(String id) {
//1 创建sql语句
String sql = "delete from t_book where user_id=?";
//2 调用方法实现
int update = jdbcTemplate.update(sql, id);
System.out.println(update);
}
@Override
public int selectCount() {
//1 创建sql语句
String sql = "select count(*) from t_book;";
//2 调用方法实现
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
@Override
public Book findBookInfo(String id) {
//1 创建sql语句
String sql = "select * from t_book where user_id=?";
//2 调用方法实现
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
@Override
public List<Book> findAllBook() {
//1 创建sql语句
String sql = "select * from t_book";
//2 调用方法实现
List<Book> bookList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Book>(Book.class));
return bookList;
}
@Override
public void batchAddBook(List<Object[]> batchArgs) {
//1 创建sql语句
String sql = "insert into t_book values(?,?,?)";
//2 调用方法实现
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
@Override
public void batchUpdateBook(List<Object[]> batchArgs) {
//1 创建sql语句
String sql = "update t_book set username=?, ustatus=? where user_id=?";
//2 调用方法实现
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(ints);
}
@Override
public void batchDeleteBook(List<Object[]> batchArgs) {
//1 创建sql语句
String sql = "delete from t_book where user_id=?";
//2 调用方法实现
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(ints);
}
}
package spring5.Test;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import spring5.entity.Book;
import spring5.service.BookService;
import java.util.ArrayList;
import java.util.List;
public class TestTemplate {
@Test
public void testAdd() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setUserid("1");
book.setUsername("java");
book.setUstatus("a");
bookService.addBook(book);
}
@Test
public void testUpdate() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setUserid("1");
book.setUsername("javaupup");
book.setUstatus("at");
bookService.updateBook(book);
}
@Test
public void testDelete() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
String id = "1";
bookService.deleteBook(id);
}
@Test
public void testCount() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
int count = bookService.findCount();
System.out.println(count);
}
@Test
public void testOne() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = bookService.findOne("1");
System.out.println(book);
}
@Test
public void testAll() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Book> bookList = bookService.findAll();
System.out.println(bookList);
}
@Test
public void testAddAll() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"3", "javaweb", "c"};
Object[] o2 = {"4", "spring", "d"};
Object[] o3 = {"5", "redis", "e"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchAdd(batchArgs);
}
@Test
public void testUpdateAll() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"javawebLi", "c3", "3"};
Object[] o2 = {"springLi", "d4", "4"};
Object[] o3 = {"redisLi", "e5", "5"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchUpdate(batchArgs);
}
@Test
public void testDeleteAll() {
ApplicationContext context =
new ClassPathXmlApplicationContext("bean1.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"3"};
Object[] o2 = {"4"};
batchArgs.add(o1);
batchArgs.add(o2);
bookService.batchDelete(batchArgs);
}
}