知识

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>



标签
知识