SSM

Spring: JdbcTemplate

张天宇 on 2020-03-13

Spring中的JdbcTemplate。

JdbcTemplate概述

用于和数据库交互,实现对表的CRUD操作,是Spring框架中提供的一个对象,是对原始Jdbc API对象的简单封装。Spring框架为我们提供了很多操作模板类。

  • 操作关系型数据的:
    • JdbcTemplate
    • HibernateTemplate
  • 操作nosql数据库的:
    • RedisTemplate
  • 操作消息队列的:
    • JmsTemplate

创建方式

简单使用

1
2
3
4
5
6
7
8
9
10
11
12
//准备数据,Spring的内置数据源
DriverManagerDataSource ds =new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/test");
ds.setUsername("root");
ds.setPassword("root");
//创建对象
JdbcTemplate jt=new JdbcTemplate();
//给jt设置数据源
jt.setDataSource(ds);
//执行操作
jt.execute("insert into account(name, money) values('eee',1000)");

配置写法

1. bean.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?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:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.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:3306/test"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
</beans>
2. 简单使用
1
2
3
4
5
6
//获取容器
ApplicationContext ac=new ClassPathXmlApplicationContext("bean.xml");
//获取对象
JdbcTemplate jt=ac.getBean("jdbcTemplate",JdbcTemplate.class);
//执行操作
jt.execute("insert into account(name, money) values('fff',1000)");

CRUD操作

  • 保存

    1
    jt.update("insert into account(name,money) values(?,?)","ggg",333);
  • 更新

    1
    jt.update("update account set name=?, money=? where id=?","test",456,8);
  • 删除

    1
    jt.update("delete from account where id=?",8);
  • 查询所有

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    List<Account> accounts=jt.query("select * from account where money > ?", new AccountRowMapper(), 1000f);
    //定义Account的封装策略
    class AccountRowMapper implements RowMapper<Account>{
    //把结果集中的数据封装到Account中,然后由Spring把每个Account加到集合中
    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;
    }
    }
    //直接使用spring提供的封装类
    List<Account> accounts=jt.query("select * from account where money > ?", new BeanPropertyRowMapper<Account>(Account.class), 1000f);
  • 查询一个

    1
    2
    List<Account> accounts=jt.query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), 1);
    System.out.println(accounts.isEmpty()?"没有内容":accounts.get(0));
  • 返回一行一列

    1
    2
    //返回一行一列
    int count = jt.queryForObject("select count(*) from account where money > ?",Integer.class,1000f);

JDBC在Dao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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 name) {
//与唯一属性id的查询不同
List<Account> accounts=jdbcTemplate.query("select * from account where name = ?", new BeanPropertyRowMapper<Account>(Account.class), name);
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());
}
}

bean.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?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:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
https://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">
<!--配置账户的持久层-->
<bean id="accountDao" class="com.ztygalaxy.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.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/test"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
</beans>

使用

1
2
3
4
5
6
//获取容器
ApplicationContext ac=new ClassPathXmlApplicationContext("bean.xml");
//获取对象
IAccountDao accountDao=ac.getBean("accountDao",IAccountDao.class);
Account account =accountDao.findAccountById(2);
System.out.println(account);

JdbcDaoSupport

通过继承JdbcDaoSupport减少代码重复,一旦继承使用注解配置就会比较麻烦。

1
2
3
4
5
6
7
8
//JdbcDaoSupport中已经包含了jdbcTemplate和dataSource的操作
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {
public Account findAccountById(Integer id) {
List<Account> accounts=getJdbcTemplate().query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), id);
return accounts.isEmpty()?null:accounts.get(0);
}
//...
}
1
2
3
4
<!--配置账户的持久层,直接将dataSource注入-->
<bean id="accountDao" class="com.ztygalaxy.dao.impl.AccountDaoImpl">
<property name="dataSource" ref="dataSource"></property>
</bean>