1.1JdbcTemplate 概述
它是 spring 框架中提供的一个对象,是对原始 Jdbc API 对象的简单封装。spring 框架为我们提供了很多的操作模板类。
操作关系型数据的:
JdbcTemplate
HibernateTemplate
操作 nosql 数据库的:
RedisTemplate
操作消息队列的:
JmsTemplate
我们今天的主角在 spring-jdbc-5.0.2.RELEASE.jar 中,我们在导包的时候,除了要导入这个 jar 包外,还需要导入一个 spring-tx-5.0.2.RELEASE.jar(它是和事务相关的)。
1.2JdbcTemplate 对象的创建
package com.itheima.jdbcTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
/**
* JdbcTemplate的最基本写法
*/
public class JdbcTemplateDemo1 {
public static void main(String[] args) {
//准备数据源:spring内置数据源
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/eesy_spring?characterEncoding=utf-8");
ds.setUsername("root");
ds.setPassword("root");
//1、创建JdbcTemplate对象
JdbcTemplate jt = new JdbcTemplate();
jt.setDataSource(ds);
//2.执行操作
jt.execute("insert into account(name,money) values ('ccc',1000)");
}
}
通过Spring来获取对象
package com.itheima.jdbcTemplate;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
/**
* JdbcTemplate的最基本写法
*/
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.update("insert into account (name,money) values ('eee',500)");
}
}
<?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:property-placeholder location="classpath:jdbc.properties"/>
<bean id="accountDao" class="com.itheima.dao.impl.AccountDaoImpl">
<!-- <property name="jdbcTemplate" ref="jdbcTemplate"/>-->
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 配置JdbcTemplate-->
<!--<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClass}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
</beans>
通过jdbcTamplate实现CRUD
package com.itheima.jdbcTemplate;
import com.itheima.domain.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
public class JdbcTemplateDemo3 {
/**
* 保存操作
* @param args
*/
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);
//删除
// jt.update("delete from account where id = ?",74);
//查询所有
/* List<Account> accounts = jt.query("select * from account " +
"where money > ?",new AccountRowMapper(),500);
for (Account o : accounts) {
System.out.println(o);
}
}*/
List<Account> accounts = jt.query("select * from account " +
"where money > ?",new BeanPropertyRowMapper<Account>(Account.class),500);
for (Account o : accounts) {
System.out.println(o);
}
//查询一个
/*
List<Account> as = jt.query("select * from account where id = ?", new AccountRowMapper(), 2);
System.out.println(as.isEmpty()?"没有结果":as.get(0));
*/
//查询返回一行一列,使用聚合函数,在不适应group by字句时,返回一行一列,
//最常用就是分页获取总记录条数
Integer total = jt.queryForObject("select count(*) from account where money > ?",
Integer.class, 500);
System.out.println(total);
}
public static 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;
}
}
}
z这个地方可以通过两种方式来实现多行数据的查找
这个方法是最常用的,通过spring来封装数据给Account对象
这个方法就是通过自己来手动实现 封装
除了queryForObject,还要queryForList,queryForMap。这写都是通过自己手动来进行选择
通过实现Dao,来实现CRUD,并且,抽取其中重复的代码块
package com.itheima.dao.impl;
import com.itheima.dao.IAccountDao;
import com.itheima.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import javax.management.RuntimeMBeanException;
import java.util.*;
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {
@Override
public Account findAccountById(Integer accountId) {
List<Account> accounts= super.getJdbcTemplate().query("select * from account where id = ? ",new BeanPropertyRowMapper<Account>(Account.class),accountId);
return accounts.isEmpty() ? null : accounts.get(0);
}
@Override
public Account findAccountByName(String accountName) {
List<Account> accounts= super.getJdbcTemplate().query("select * from account where name = ?",new BeanPropertyRowMapper<Account>(Account.class));
if(accounts.isEmpty()){
return null;
}
if(accounts.size() > 1){
throw new RuntimeException("结果集不唯一");
}
return accounts.get(0);
}
@Override
public void updateAccount(Account account) {
super.getJdbcTemplate().update("update account set name=? , money=?,where id=?",account.getName(),account.getMoney(),account.getId());
}
}
package com.itheima.dao.impl;
import com.itheima.dao.IAccountDao;
import com.itheima.domain.Account;
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 java.util.List;
@Repository
public class AccountDaoImpl2 implements IAccountDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public Account findAccountById(Integer accountId) {
List<Account> accounts= jdbcTemplate.query("select * from account where id = ? ",new BeanPropertyRowMapper<Account>(Account.class),accountId);
return accounts.isEmpty() ? null : accounts.get(0);
}
@Override
public Account findAccountByName(String accountName) {
List<Account> accounts= jdbcTemplate.query("select * from account where name = ?",new BeanPropertyRowMapper<Account>(Account.class));
if(accounts.isEmpty()){
return null;
}
if(accounts.size() > 1){
throw new RuntimeException("结果集不唯一");
}
return accounts.get(0);
}
@Override
public void updateAccount(Account account) {
jdbcTemplate.update("update account set name=? , money=?,where id=?",account.getName(),account.getMoney(),account.getId());
}
}
package com.itheima.dao.impl;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
/**
* 此类用于抽取dao中的重复代码
*/
public class JdbcDaoSupport {
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 = createJdbcTemplate(dataSource);
}
}
private JdbcTemplate createJdbcTemplate(DataSource dataSource) {
return new 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: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:property-placeholder location="classpath:jdbc.properties"/>
<bean id="accountDao" class="com.itheima.dao.impl.AccountDaoImpl">
<!-- <property name="jdbcTemplate" ref="jdbcTemplate"/>-->
<!-- 将datasource 注入到dao中-->
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 配置JdbcTemplate-->
<!--<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc.driverClass}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
</beans>
分析:
这里使用了三种方式来抽取重复的代码块
第一种:使用自己配置的JdbcDaoSupport,使用dao实现类 继承JdbcDaoSupport,从而使用父类的方法
得到JdbcTemplate对象。
第二种:使用spring的JdbcDaoSupport类,来继承
第三种:通过注解来获取