一、什么是 JdbcTemplate
Spring 框架对 JDBC 进行了封装,使用 JdbcTemplate 方便实现对数据库的操作。
二、准备工作
2.1 pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>sping-aop</artifactId>
<version>1.0-SNAPSHOT</version>
<name>sping-aop</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>net.sourceforge.cglib</groupId>
<artifactId>com.springsource.net.sf.cglib</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>aopalliance</groupId>
<artifactId>aopalliance</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.6.8</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.2.6.RELEASE</version>
</dependency>
</dependencies>
</project>
2.2 Spring 配置文件相关配置
(1)开启组件扫描
(2)配置数据库连接池
(3)配置 JdbcTemplate 对象,注入 DataSource
<?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:aop="http://www.springframework.org/schema/aop"
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/aop https://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
<!--开启组件扫描-->
<context:component-scan base-package="aop"></context:component-scan>
<!--数据库连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="url" value="jdbc:mysql://localhost:3306/test?serverTimezone=GMT&useSSL=false&useUnicode=true&characterEncoding=utf8"></property>
<property name="driver" value="com.mysql.cj.jdbc.Driver"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
<!--JdbcTemplate 对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入dataSource-->
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
三、JdbcTemplate 的具体使用
3.1 增删改查
调用 JdbcTemplate 对象里面的 update 方法实现增删改操作
update(String sql, Object...args)
有两个参数
- 第一个参数:SQL 语句
- 第二个参数:可变参数,设置 SQL 语句值
1、插入操作
package com.example.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public void save(){
String sql = "insert into user (name, password) values ('zhangsan', '123')";
jdbcTemplate.update(sql);
}
public void save2(){
String sql = "insert into user (name, password) values (?, ?)";
Object[] args = {"lisi","123456"};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
}
测试方法:
package com.example.dao;
import org.testng.annotations.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestJdbcTemplate {
@Test
public void test(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
dao.save();
}
@Test
public void test2(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
dao.save2();
}
}
两种新增操作均可以实现
2、更新操作
dao:
public void update(){
String sql = "update user set name = ? where id = ?";
Object[] args = {"wangwu", 1};
jdbcTemplate.update(sql, args);
}
测试代码:
@Test
public void test3(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
dao.update();
}
结果:
3、删除操作
dao:
public void delete(){
String sql = "delete from user where id = ?";
jdbcTemplate.update(sql, 3);
}
测试代码:
@Test
public void test4(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
dao.delete();
}
结果:
4、查询
queryForObject(String sql, Class<T> requiredType)
有两个参数:
- 第一个参数:SQL 语句
- 第二个参数:返回类型 Class
4.1 查询表中有多少条记录,返回某个值或者查询某个字段值
dao:
public int selectCount(){
String sql = "select count(*) from user";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
测试代码:
@Test
public void test5(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
int count = dao.selectCount();
System.out.println(count);
}
4.2 查询返回对象
JdbcTemplate 实现查询返回对象
queryForObject(String sql, RowMapper<T> rowMapper, object...args)
有三个参数:
- 第一个参数:SQL 语句
- 第二个参数:RowMapper 是接口,针对返回不同类型的数据,使用这个接口里面实现类完成数据封装
- 第三个参数:SQL 语句值
User
package com.example.dao;
public class User {
private Integer id;
private String name;
private String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", password='" + password + '\'' +
'}';
}
}
dao:
public User queryUserById(){
String sql = "select * from user where id = ?";
User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), 4);
return user;
}
测试方法:
@Test
public void test6(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
User user = dao.queryUserById();
System.out.println(user);
}
测试结果:
User{id=4, name='lisi', password='123456'}
4.3 查询返回集合
调用 JdbcTemplate 方法实现查询返回集合
query(String sql, RowMapper<T> rowMapper, object...args)
有三个参数:
- 第一个参数:SQL 语句
- 第二个参数:RowMapper 是接口,针对返回不同类型的数据,使用这个接口里面实现类完成数据封装
- 第三个参数:SQL 语句值
dao:
public List<User> queryUserList(){
String sql = "select * from user";
List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
return userList;
}
测试方法:
@Test
public void test7(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
List<User> list = dao.queryUserList();
for (User user: list) {
System.out.println(user);
}
}
输出结果:
User{id=1, name='wangwu', password='123'}
User{id=2, name='zhangsan', password='123'}
User{id=4, name='lisi', password='123456'}
3.2 批量操作
1、批量添加
batchUpdate(String sql, List<Object> batchArgs)
有两个参数:
- 第一个参数:SQL 语句
- 第二个参数:List 集合,添加多条记录数据
dao:
// 批量添加
public void batchAddUser(List<Object[]> batchArgs){
String sql = "insert into user (name, password) values (?, ?)";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试代码:
@Test
public void test8(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
List<Object[]> batchArgs = new ArrayList<>();
Object[] user1 = {"chenliu", "456"};
Object[] user2 = {"maqi", "456444"};
Object[] user3 = {"zhaojiu", "45678"};
batchArgs.add(user1);
batchArgs.add(user2);
batchArgs.add(user3);
dao.batchAddUser(batchArgs);
}
测试结果:
[1, 1, 1]
2、批量修改
dao:
public void batchUpdateUser(List<Object[]> batchArgs){
String sql = "update user set name = ? where id = ?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试代码:
@Test
public void test9(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
List<Object[]> batchArgs = new ArrayList<>();
Object[] user1 = {"zhangzhang", "5"};
Object[] user2 = {"mama", "6"};
Object[] user3 = {"lili", "7"};
batchArgs.add(user1);
batchArgs.add(user2);
batchArgs.add(user3);
dao.batchUpdateUser(batchArgs);
}
测试结果:
[1, 1, 1]
3、批量删除
dao:
public void batchDeleteUser(List<Object[]> batchArgs){
String sql = "delete from user where id = ?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试方法:
@Test
public void test10(){
ApplicationContext context = new ClassPathXmlApplicationContext("spring-aop.xml");
UserDao dao = (UserDao) context.getBean("userDao");
List<Object[]> batchArgs = new ArrayList<>();
Object[] user1 = {"5"};
Object[] user2 = {"6"};
Object[] user3 = {"7"};
batchArgs.add(user1);
batchArgs.add(user2);
batchArgs.add(user3);
dao.batchDeleteUser(batchArgs);
}
测试结果:
[1, 1, 1]