一、JdbcTemplate 概述
1、JdbcTemplate 概述
它是 spring 框架中提供的一个对象,是对原始 Jdbc API 对象的简单封装。spring 框架为我们提供了很多的操作模板类。
操作关系型数据的: JdbcTemplate
HibernateTemplate
操作 nosql 数据库的: RedisTemplate
操作消息队列的:JmsTemplate
我们今天的主角在 spring-jdbc-5.0.2.RELEASE.jar
中,我们在导包的时候,除了要导入这个 jar 包外,还需要导入一个 spring-tx-5.0.2.RELEASE.jar
(它是和事务相关的)。
2、JdbcTemplate 对象的创建
我们可以参考它的源码,来一探究竟:
public JdbcTemplate() {
}
public JdbcTemplate(DataSource dataSource) {
setDataSource(dataSource);
afterPropertiesSet();
}
public JdbcTemplate(DataSource dataSource, boolean lazyInit) {
setDataSource(dataSource);
setLazyInit(lazyInit);
afterPropertiesSet();
}
除了默认构造函数之外,都需要提供一个数据源。既然有set方法,依据我们之前学过的依赖注入,我们可以在配置文件中配置这些对象。
3、spring 中配置数据源
(1)环境搭建
(2)编写 spring 的配置文件
<?xml version="1.0" encoding="UTF-8"?>
<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">
</beans>
(3)配置数据源
我们之前已经接触过了两个数据源,C3P0 和 DBCP。要想使用这两数据源都需要导入对应的次c3p0.jar 包。
配置 C3P0 数据源
导入 到工程的 lib 目录。在 spring 的配置文件中配置:
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql:///spring_day02"></property>
<property name="user" value="root"></property>
<property name="password" value="1234"></property>
</bean>
配置 DBCP 数据源
导入 到工程的 lib 目录。在 spring 的配置文件中配置:
<!-- 配置数据源 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql:// /spring_day02"></property>
<property name="username" value="root"></property>
<property name="password" value="1234"></property>
</bean>
配置 spring 内置数据源
spring 框架也提供了一个内置数据源,我们也可以使用 spring 的内置数据源,它就在spring-jdbc-5.0.2.REEASE.jar
包中:
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql:///spring_day02"></property>
<property name="username" value="root"></property>
<property name="password" value="1234"></property>
</bean>
(4)将数据库连接的信息配置到属性文件中:
【定义属性文件】
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///spring_day02
jdbc.username=root
jdbc.password=123
【引入外部的属性文件】
一种方式:
<!-- 引入外部属性文件: -->
<bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties"/>
</bean>
另一种方式:
<context:property-placeholder location="classpath:jdbc.properties"/>
二、JdbcTemplate的简单使用
/**
* 1、JdbcTemplate的最基本用法
*/
public class JdbcTemplateDemo1base {
public static void main(String[] args) {
//准备数据源:spring的内置数据源
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/eesy?useSSL=false&serverTimezone=UTC");
ds.setUsername("root");
ds.setPassword("root");
//1、创建JdbcTemplate对象
JdbcTemplate jt = new JdbcTemplate();
//给jt设置数据源
jt.setDataSource(ds);
//2、执行操作
jt.execute("insert into account(name,money) value('sss',1000.0)");
List<Account> accounts = jt.query("select * from account",new BeanPropertyRowMapper<Account>(Account.class));
for(Account account:accounts) {
System.out.println(account);
}
}
}
三、JdbcTemplate_IOC_CRUD操作
1、持久层
/**
* 账户的持久层实现类
*/
public class AccountDaoImpl implements IAccountDao{
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public Account findAccountById(Integer id) {
List<Account> accounts= jdbcTemplate.query("select * from account where id=?",
new BeanPropertyRowMapper<Account>(Account.class),id);
return accounts.isEmpty()?null:accounts.get(0);
}
public Account findAccountByName(String accountName) {
List<Account> accounts= jdbcTemplate.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) {
jdbcTemplate.update("update account set name=?,money=? where id=?",
account.getName(),account.getMoney(),account.getId());
}
}
2、配置文件
bean.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- 配置账户的持久层 -->
<bean id="accountDao" class="com.itheima.dao.impl.AccountDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<!-- 配置JdbcTemplate -->
<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?useSSL=false&serverTimezone=UTC"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
</beans>
pom.xml
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
</dependencies>
3、测试层
/**
* JdbcTemplate的最基本用法
* 其中使用spring的ioc
*/
public class JdbcTemplateDemo2ioc {
public static void main(String[] args) {
//1、读取配置文件
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//IAccountDao accountDao = (IAccountDao) ac.getBean("accountDao");
IAccountDao accountDao = ac.getBean("accountDao",IAccountDao.class);
Account account= accountDao.findAccountById(2);
System.out.println(account);
}
}
4、执行结果
四、JdbcTemplate 的增删改查操作
1、前期准备
创建数据库:
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;
2、在 spring 配置文件中配置 JdbcTemplate
<?xml version="1.0" encoding="UTF-8"?>
<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">
<!-- 配置一个数据库的操作模板:JdbcTemplate -->
<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.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql:///spring_day02"></property>
<property name="username" value="root"></property>
<property name="password" value="1234"></property>
</bean>
</beans>
3、 最基本使用
public class JdbcTemplateDemo2 {
public static void main(String[] args) {
//1.获取 Spring 容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//2.根据 id 获取 bean 对象
JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
//3.执行操作
jt.execute("insert into account(name,money)values('eee',500)");
}
}
4、保存操作
public class JdbcTemplateDemo3 {
public static void main(String[] args) {
//1.获取 Spring 容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//2.根据 id 获取 bean 对象
JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
//3.执行操作
//保存
jt.update("insert into account(name,money)values(?,?)","fff",5000);
}
}
5、更新操作
public class JdbcTemplateDemo3 {
public static void main(String[] args) {
//1.获取 Spring 容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//2.根据 id 获取 bean 对象
JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
//3.执行操作
//修改
jt.update("update account set money = money-? where id = ?",300,6);
}
}
6、删除操作
public class JdbcTemplateDemo3 {
public static void main(String[] args) {
//1.获取 Spring 容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//2.根据 id 获取 bean 对象
JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
//3.执行操作
//删除
jt.update("delete from account where id = ?",6);
}
}
7、查询所有操作
自定义Account得封装策略:
import org.springframework.jdbc.core.RowMapper;
public class JdbcTemplateDemo3 {
public static void main(String[] args) {
//1.获取 Spring 容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//2.根据 id 获取 bean 对象
JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
//3.执行操作
//查询所有
List<Account> accounts = jt.query("select * from account where money > ? ",new AccountRowMapper(), 500);
for(Account o : accounts){
System.out.println(o);
}
}
}
public class AccountRowMapper implements RowMapper<Account>{
@Override
public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
Account account = new Account();
account.setId(rs.getInt("id"));
account.setName(rs.getString("name"));
account.setMoney(rs.getFloat("money"));
return account;
}
}
8、查询补充
Spring自带的封装策略:
import org.springframework.jdbc.core.BeanPropertyRowMapper;
//查询所有
List<Account> accounts = jt.query("select * from account where money>?",new BeanPropertyRowMapper<Account>(Account.class),100f);
for(Account account:accounts) {
System.out.println(account);
}
//查询一个
List<Account> accounts2 = jt.query("select * from account where id>?",new BeanPropertyRowMapper<Account>(Account.class),17);
System.out.println(accounts2.isEmpty()?"没有内容":accounts.get(0));
五、JdbcTemplate_IOC_CRUD操作的优化
将重复的代码提取出来
1、持久层
原始:不继承
/**
* 账户的持久层实现类
*/
public class AccountDaoImpl_not_extends implements IAccountDao{
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public Account findAccountById(Integer id) {
// TODO Auto-generated method stub
List<Account> accounts= jdbcTemplate.query("select * from account where id=?",new BeanPropertyRowMapper<Account>(Account.class),id);
return accounts.isEmpty()?null:accounts.get(0);
}
public Account findAccountByName(String accountName) {
List<Account> accounts= jdbcTemplate.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) {
jdbcTemplate.update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
}
}
修改后:继承
/**
* 此类用于抽取Dao中的重复代码
*/
public class JdbcDaoSupport {
//定义JdbcTemplate让其他类继承该类 调用此方法
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setDataSource(DataSource dataSource) {
if(jdbcTemplate==null) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
}
}
继承实现类
/**
* 账户的持久层实现类
*
*/
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao{
/**
* 继承 :
* 继承了他的setget方法,因此在bean.xml中,配置需要些该类全限定路径。
* 注意:
* 继承JdbcDaoSupport 的方式,只能用于基于 XML的方式,注解用不了。
*/
public Account findAccountById(Integer id) {
// TODO Auto-generated method stub
List<Account> accounts= super.getJdbcTemplate().query("select * from account where id=?",new BeanPropertyRowMapper<Account>(Account.class),id);
return accounts.isEmpty()?null:accounts.get(0);
}
public Account findAccountByName(String accountName) {
List<Account> accounts= super.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) {
super.getJdbcTemplate().update("update account set name=?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
}
}
2、配置文件
bean.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<!-- 配置账户的持久层 -->
<bean id="accountDao" class="com.itheima.dao.impl.AccountDaoImpl">
<!-- <property name="jdbcTemplate" ref="jdbcTemplate"></property> -->
<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?useSSL=false&serverTimezone=UTC"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
</beans>
pom.xml
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
</dependencies>
3、测试层
/**
* JdbcTemplate的最基本用法
* 其中使用spring的ioc
*/
public class JdbcTemplateDemo2ioc {
public static void main(String[] args) {
//1、读取配置文件
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//IAccountDao accountDao = (IAccountDao) ac.getBean("accountDao");
IAccountDao accountDao = ac.getBean("accountDao",IAccountDao.class);
Account account= accountDao.findAccountById(2);
System.out.println(account);
}
}