JdbcTemplate

jwang2019-07-012026-01-23

Spring除了IOC 和 AOP之外，还对Dao层的提供了支持。就算没有框架，也对原生的JDBC提供了支持。它是spring框架中提供的一个对象，是对原始Jdbc API对象的简单封装。spring框架为我们提供了很多的操作模板类。

持久化技术	模版类
JDBC	org.springframework.jdbc.core.JdbcTemplate
Hibrenate	org.springframework.orm.hibernate5.HibernateTemplate
MyBatis	org.mybatis.spring.SqlSessionTemplate

JDBC模版入门案例

创建Maven工程,导入坐标

<dependencies>
    <!--Spring核心容器-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.0.2.RELEASE</version>
    </dependency>
    <!--SpringJdbc-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.0.2.RELEASE</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
    </dependency>
    <!--Spring整合单元测试-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>5.0.2.RELEASE</version>
    </dependency>
    <!--单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
    </dependency>

    <!--druid连接池-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.18</version>
    </dependency>
</dependencies>

Account

package com.jwang.pojo;

import lombok.Data;

/**
 * 包名:com.jwang.pojo
 *
 * @author Leevi
 * 日期2020-08-09  14:55
 */
@Data
public class Account {
    private String name;
    private Integer id;
    private Double money;
}

使用JDBC模版API

//使用JDBC模版保存
public void save(Account account) {
    //1. 创建数据源
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/spring_day03");
    dataSource.setUsername("root");
    dataSource.setPassword("123456");

    //2. 创建JDBC模版
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    jdbcTemplate.setDataSource(dataSource);

    //3.操作数据库
    String sql = "insert into account values(?,?,?)";
    jdbcTemplate.update(sql, null,account.getName(),account.getMoney())

}

使用JDBC模版完成CRUD

增加

String sql = "insert into account values(?,?,?)";
jdbcTemplate.update(sql, null,account.getName(),account.getMoney());

修改

String sql ="update account set name = ? where id = ?";
Object[] objects = {user.getName(), user.getId()};
jdbcTemplate.update(sql,objects);

删除

String sql = "delete from account where id=?";
jdbcTemplate.update(sql, 6);

查询单个值

String sql = "select count(*) from account";
Long n = jdbcTemplate.queryForObject(sql, Long.class);

String sql = "select name from account where id=?";
String name = jdbcTemplate.queryForObject(sql, String.class, 4);
System.out.println(name);

查询单个对象

String sql = "select * from account where id = ?";
User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Account.class),id);

查询集合

String sql = "select * from account";
List<Account> list = List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Account.class));

在dao中使用JdbcTemplate

方式一

这种方式我们采取在dao中定义JdbcTemplate

AccountDaoImpl.java

public class AccountDaoImpl implements AccountDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public Account findAccountById(Integer id) {
		List<Account> list =  jdbcTemplate.query("select * from account where id = ? ",new AccountRowMapper(),id);
		return list.isEmpty()?null:list.get(0);
	}

	@Override
	public Account findAccountByName(String name) {
		List<Account> list =  jdbcTemplate.query("select * from account where name = ? ",new AccountRowMapper(),name);
		if(list.isEmpty()){
			return null;
		}
		if(list.size()>1){
			throw new RuntimeException("结果集不唯一，不是只有一个账户对象");
		}
		return list.get(0);
	}

	@Override
	public void updateAccount(Account account) {
		jdbcTemplate.update("update account set money = ? where id = ? ",account.getMoney(),account.getId());
	}

}

applicationContext.xml

<!-- 配置一个dao -->
<bean id="accountDao" class="com.jwang.dao.impl.AccountDaoImpl">
	<!-- 注入jdbcTemplate -->
	<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.jdbc.Driver"></property>
	<property name="url" value="jdbc:mysql:///spring_day04"></property>
	<property name="username" value="root"></property>
	<property name="password" value="123456"></property>
</bean>

方式二

这种方式我们采取让dao继承JdbcDaoSupport

AccountDaoImpl.java

public class AccountDaoImpl extends JdbcDaoSupport implements AccountDao {

	@Override
	public Account findAccountById(Integer id) {
		//getJdbcTemplate()方法是从父类上继承下来的。
		List<Account> list = getJdbcTemplate().query("select * from account where id = ? ",new AccountRowMapper(),id);
		return list.isEmpty()?null:list.get(0);
	}

	@Override
	public Account findAccountByName(String name) {
		//getJdbcTemplate()方法是从父类上继承下来的。
		List<Account> list =  getJdbcTemplate().query("select * from account where name = ? ",new AccountRowMapper(),name);
		if(list.isEmpty()){
			return null;
		}
		if(list.size()>1){
			throw new RuntimeException("结果集不唯一，不是只有一个账户对象");
		}
		return list.get(0);
	}

	@Override
	public void updateAccount(Account account) {
		//getJdbcTemplate()方法是从父类上继承下来的。
		getJdbcTemplate().update("update account set money = ? where id = ? ",account.getMoney(),account.getId());
	}
}

applicationContext.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">
	
<!-- 配置dao2 -->
<bean id="accountDao" class="com.jwang.dao.impl.AccountDaoImpl">
	<!-- 注入dataSource -->
	<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_day04"></property>
		<property name="username" value="root"></property>
		<property name="password" value="123456"></property>
	</bean>
</beans>