简介
为了使 JDBC 更加易于使用,Spring 在 JDBCAPI 上定义了一个抽象层, 以此建立一个JDBC存取框架.
作为 SpringJDBC 框架的核心, JDBC 模板的设计目的是为不同类型的JDBC操作提供模板方法. 每个模板方法都能控制整个过程,并允许覆盖过程中的特定任务.通过这种
方式,可以在尽可能保留灵活性的情况下,将数据库存取的工作量降到最低.
JdbcTemplate主要提供以下五类方法:
execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;
update方法:update方法用于执行新增、修改、删除等语句;
batchUpdate方法:batchUpdate方法用于执行批处理相关语句;
query方法及queryForXXX方法:用于执行查询相关语句;
call方法:用于执行存储过程、函数相关语句。
基本用法
package com.jdbctemplate;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
/**
* JdbcTemplate的最基本用法
*/
public class JdbcTemplateDemo1 {
@Test
public void test(){
//准备数据源:spring的内置数据源
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3366/myschool");
ds.setUsername("root");
ds.setPassword("******");
//1、创建JdbcTemplate对象
JdbcTemplate jt = new JdbcTemplate();
//给jt设置数据源
jt.setDataSource(ds);
//2、执行操作
jt.execute("insert into account(name,money) values('ddd',2000)");
}
}
导入依赖
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
优化
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">
<!--配置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://localhost:3366/myschool"></property>
<property name="username" value="root"></property>
<property name="password" value="875072"></property>
</bean>
</beans>
@Test
public void test(){
//1、获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
// JdbcTemplate jt = (JdbcTemplate) ac.getBean("jdbcTemplate");
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
jt.execute("insert into account(name,money) values('eee',2000)");
}
CRUD
package com.jdbctemplate;
import com.domain.Account;
import org.junit.Test;
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.List;
/**
* JdbcTemplate的CRUD
*/
public class JdbcTemplateDemo3 {
@Test
public void testSave(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
// jt.execute("insert into account(name,money) values('eee',2000)");
jt.update("insert into account(name,money) values(?,?)","fff",3000);
}
@Test
public void testUpdate(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
jt.update("update account set name = ?, money = ? where id = ?","tom",1111,1);
}
@Test
public void testDelete(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
jt.update("delete from account where id = ?",8);
}
@Test
public void testQuery(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
// List<Account> accountList = jt.query("select * from account where money>?",new AccountRowMapper(),1000);
List<Account> accountList = jt.query("select * from account where money>?",new BeanPropertyRowMapper<Account>(Account.class),1000);
for (Account account : accountList) {
System.out.println(account);
}
}
@Test
public void testQueryById(){
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
List<Account> accountList = jt.query("select * from account where id=?",new BeanPropertyRowMapper<Account>(Account.class),1);
System.out.println(accountList.get(0));
}
@Test
public void testQueryRow(){ //查询返回一行一列,使用聚合函数,但不加group by子句
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
JdbcTemplate jt = ac.getBean("jdbcTemplate", JdbcTemplate.class);
Long count = jt.queryForObject("select count(*) from account where money > ?", Long.class, 1000);
System.out.println(count);
}
}
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;
}
}
Dao中使用
AccountDaoImpl.java
package com.dao.impl;
import com.dao.IAccountDao;
import com.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
public class AccountDaoImpl implements IAccountDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public Account findAccountById(Integer accountId) {
List<Account> accountList = jdbcTemplate.query("select * from account where id = ?",new BeanPropertyRowMapper<Account>(Account.class),accountId);
return accountList.isEmpty()?null:accountList.get(0);
}
public Account findAccountByName(String accountName) {
List<Account> accountList = jdbcTemplate.query("select * from account where name = ?",new BeanPropertyRowMapper<Account>(Account.class),accountName);
if (accountList.isEmpty()) {
return null;
}
if (accountList.size() > 1) {
throw new RuntimeException("结果集不唯一");
}
return accountList.get(0);
}
public void updateAccount(Account account) {
jdbcTemplate.update("update account set name = ?,money = ? where id = ?",account.getName(),account.getMoney(),account.getId());
}
}
JdbcTemplateTest.java
import com.dao.IAccountDao;
import com.dao.impl.AccountDaoImpl;
import com.domain.Account;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class JdbcTemplateTest {
@Test
public void test() {
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDaoImpl accountDao = ac.getBean("accountDao", AccountDaoImpl.class);
Account accountById = accountDao.findAccountById(1);
System.out.println(accountById);
}
}