JdbcTemplate概述
它是spring 框架中提供的一个对象,是对原始Jdbc API对象的简单封装。spring框架为我们提供了很多的操作模板类。
操作关系型数据的:
JdbcTemplate
HibernateTemplate
操作nosql数据库的:
RedisTemplate
操作消息队列的:
JmsTemplate
环境搭建
pom文件配置如下:
<?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>com.ethan</groupId>
<artifactId>day04_eesy_03jdbcTemplate</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
</dependencies>
</project>
JdbcTemplate对象创建
JdbcTemplate构造方法
public JdbcTemplate() {
}
public JdbcTemplate(DataSource dataSource) {
this.setDataSource(dataSource);
this.afterPropertiesSet();
}
public JdbcTemplate(DataSource dataSource, boolean lazyInit) {
this.setDataSource(dataSource);
this.setLazyInit(lazyInit);
this.afterPropertiesSet();
}
配置spring内置数据源
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/eesy?serverTimezone=GMT%2b8"></property>
<property name="username" value="root"></property>
<property name="password" value="0000"></property>
</bean>
JdbcTemplate的增删改查操作
创建表
创建数据库:
create database spring_day02;
use spring_day02;
创建表:
create table account( id int primary key auto_increment,
name varchar(40),
money float )character set utf8 collate utf8_general_ci;
配置JdbcTemplate
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/eesy?serverTimezone=GMT%2b8"></property>
<property name="username" value="root"></property>
<property name="password" value="0000"></property>
</bean>
</beans>
基本操作
最基本使用
public class JdbcTemplateDemo2 {
public static void main(String[] args) {
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
jt.execute("insert into account(name, money)values('ddd', 3000)");
}
}
保存、更新、删除
jt.update("insert into account(name,money)values(?,?)","fff",5000);
jt.update("update account set money = money-? where id = ?",300,6);
jt.update("delete from account where id = ?",6);
查询
查询所有:
public class JdbcTemplateDemo3 {
public static void main(String[] args) {
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
//查询所有
List<Account> accounts = jt.query("select * from account where money > ?", new AccountRowMapper(), 1000f);
//查询一个
List<Account> accounts2 = jt.query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), 3);
}
}
/**
* 定义Account的封装策略
*/
class AccountRowMapper implements RowMapper<Account> {
/**
* 把结果集中的数据封装到Account中,然后由spring把每个Account加到集合中
* @param resultSet
* @param i
* @return
* @throws SQLException
*/
public Account mapRow(ResultSet resultSet, int i) throws SQLException {
Account account = new Account();
account.setId(resultSet.getInt("id"));
account.setName(resultSet.getString("name"));
account.setMoney(resultSet.getFloat("money"));
return account;
}
}
查询返回一行一列操作:
//查询返回一行一列:使用聚合函数,在不使用group by字句时,都是返回一行一列。最常用的就是分页中获取总记录条数
Integer total = jt.queryForObject("select count(*) from account where money > ? ",Integer.class,500);
在dao中使用JdbcTemplate
Account
实体类如下:
package com.ethan.daomain;
import java.io.Serializable;
/**
* @author Ethan
* @date 2020/1/27 - 9:52
* 账户的实体类
*/
public class Account implements Serializable {
private Integer id;
private String name;
private Float money;
public Integer getId() {
return id;
}
public String getName() {
return name;
}
public Float getMoney() {
return money;
}
public void setId(Integer id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setMoney(Float money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
第一种方式
在dao中定义JdbcTemplate
:
public class AccountDaoImpl2 extends JdbcDaoSupport implements AccountDao {
//如果有多个Dao类,以下为重复代码
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public Account findAccountById(Integer accountId) {
List<Account> accounts = getJdbcTemplate().query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), accountId);
return accounts.isEmpty()? null: accounts.get(0);
}
public Account findAccountByName(String accountName) {
List<Account> accounts = getJdbcTemplate().query("select * from account where name = ?", new BeanPropertyRowMapper<Account>(Account.class), accountName);
if(accounts.isEmpty())
return null;
if(accounts.size() > 1)
throw new RuntimeException("结果集不唯一");
return accounts.get(0);
}
public void updateAccount(Account account) {
getJdbcTemplate().update("update account set name = ?, money = ? where id = ?", account.getName(), account.getMoney(), account.getId());
}
}
配置文件如下:
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<!--配置账户的持久层-->
<bean id="accountDao" class="com.ethan.dao.impl.AccountDaoImpl2">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/eesy?serverTimezone=GMT%2b8"></property>
<property name="username" value="root"></property>
<property name="password" value="0000"></property>
</bean>
</beans>
当我们有很多个dao时,每个dao都有一些重复代码:
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
将公共代码抽取出来,将这些公共代码放到一个类中,然后让每个dao继承这个类:
package com.ethan.dao.impl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
/**
* @author Ethan
* @date 2020/1/27 - 12:33
* 此类用于抽取DAO中的重复代码
* 由于可以在代码上加注解,基于注解配置时采用这种方式
*/
@Repository
public class JdbcDaoSupport {
@Autowired
private JdbcTemplate jdbcTemplate;
private JdbcTemplate createJdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
//set方法注入数据源,判断是否注入了,注入了就创建JdbcTemplate
public void setDataSource(DataSource dataSource) {
if(this.jdbcTemplate == null || dataSource != this.jdbcTemplate.getDataSource()) {
jdbcTemplate = createJdbcTemplate(dataSource);
}
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
}
抽取后的业务层代码:
package com.ethan.dao.impl;
import com.ethan.dao.AccountDao;
import com.ethan.daomain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import java.util.List;
/**
* @author Ethan
* @date 2020/1/27 - 12:20
*
*/
public class AccountDaoImpl2 extends JdbcDaoSupport implements AccountDao {
/* //如果有多个Dao类,以下为重复代码
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}*/
public Account findAccountById(Integer accountId) {
List<Account> accounts = getJdbcTemplate().query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), accountId);
return accounts.isEmpty()? null: accounts.get(0);
}
public Account findAccountByName(String accountName) {
List<Account> accounts = getJdbcTemplate().query("select * from account where name = ?", new BeanPropertyRowMapper<Account>(Account.class), accountName);
if(accounts.isEmpty())
return null;
if(accounts.size() > 1)
throw new RuntimeException("结果集不唯一");
return accounts.get(0);
}
public void updateAccount(Account account) {
getJdbcTemplate().update("update account set name = ?, money = ? where id = ?", account.getName(), account.getMoney(), account.getId());
}
}
这时配置只需要在相关成员变量上加上注解。
第二种方式
可以继承spring提供的JdbcDaoSupport
类:
package com.ethan.dao.impl;
import com.ethan.dao.AccountDao;
import com.ethan.daomain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.util.List;
/**
* @author Ethan
* @date 2020/1/27 - 12:20
*/
public class AccountDaoImpl extends JdbcDaoSupport implements AccountDao {
public Account findAccountById(Integer accountId) {
List<Account> accounts = getJdbcTemplate().query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), accountId);
return accounts.isEmpty()? null: accounts.get(0);
}
public Account findAccountByName(String accountName) {
List<Account> accounts = getJdbcTemplate().query("select * from account where name = ?", new BeanPropertyRowMapper<Account>(Account.class), accountName);
if(accounts.isEmpty())
return null;
if(accounts.size() > 1)
throw new RuntimeException("结果集不唯一");
return accounts.get(0);
}
public void updateAccount(Account account) {
getJdbcTemplate().update("update account set name = ?, money = ? where id = ?", account.getName(), account.getMoney(), account.getId());
}
}
这时无法通过注解的方式配置对象,而采用XML的方式:(前面已经配置了JdbcTemplate
对象和dataSource
对象)
<!--配置账户的持久层-->
<bean id="accountDao" class="com.ethan.dao.impl.AccountDaoImpl2">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
<property name="dataSource" ref="dataSource"></property>
</bean>
对比:
- 第一种在Dao类中定义
JdbcTemplate
的方式,适用于所有配置方式(xml和注解都可以)。 - 第二种让Dao继承
JdbcDaoSupport
的方式,只能用于基于XML的方式,注解用不了。