Spring（七）JdbcTemplate

傷城~ 2021-06-22 15:37 458阅读 0赞

### 目录 ###

*  Spring中的JdbcTemplate
 *   *  1. 概述
     *  2. 入门案例
     *  3. JdbcTemplate在Spring中的IoC
     *  4. JdbcTemplate的CRUD操作
     *  5. 基于DAO的jdbcTemplate
     *  6. 使用JdbcDaoSupport抽取重复代码

# Spring中的JdbcTemplate #

## 1. 概述 ##

它是 spring 框架中提供的一个对象，是对原始 Jdbc API 对象的简单封装。spring 框架为我们提供了很多 的操作模板类。  
操作关系型数据的： JdbcTemplate HibernateTemplate  
操作 nosql 数据库的： RedisTemplate  
操作消息队列的： JmsTemplate  
我们今天的主角在 spring-jdbc-5.0.2.RELEASE.jar 中，我们在导包的时候，除了要导入这个 jar 包 外，还需要导入一个 spring-tx-5.0.2.RELEASE.jar（它是和事务相关的）。

<dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
    </dependencies>

## 2. 入门案例 ##

我们准备一张表 account：  
![在这里插入图片描述][20200514102204375.png]  
创建实体类：

package com.veeja.domain;
    
    import java.io.Serializable;
    
    /** * 账户的实体类 * * @Author veeja * 2020/5/14 10:22 */
    public class Account implements Serializable { 
        private Integer id;
        private String name;
        private float money;
    
        @Override
        public String toString() { 
            return "Account{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", money=" + money +
                    '}';
        }
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getName() { 
            return name;
        }
    
        public void setName(String name) { 
            this.name = name;
        }
    
        public float getMoney() { 
            return money;
        }
    
        public void setMoney(float money) { 
            this.money = money;
        }
    }

**演示基本的用法：**

/** * 演示最基本的用法 * * @Author veeja * 2020/5/14 10:24 */
    public class JbbsTemplateDemo1 { 
        public static void main(String[] args) { 
            // 准备数据源：spring的内置数据源
            DriverManagerDataSource dataSource = new DriverManagerDataSource();
            dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
            dataSource.setUrl("jdbc:mysql://localhost:3306/springdatabase?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC");
            dataSource.setUsername("root");
            dataSource.setPassword("0000");
    
    
            // 1.创建对象
            JdbcTemplate jt = new JdbcTemplate();
            // 给jt设置数据源
            jt.setDataSource(dataSource);
            // 2.执行操作
            jt.execute("insert into account(name,money) values ('veeja',666)");
        }
    }

操作成功了。  
![在这里插入图片描述][20200514103352472.png]  
接下来我们就对上面的代码进行改造，首先是IOC改造。

## 3. JdbcTemplate在Spring中的IoC ##

bean.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">
        
        <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.cj.jdbc.Driver"></property>
            <property name="url" value="jdbc:mysql://localhost:3306/springdatabase?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC"></property>
            <property name="username" value="root"></property>
            <property name="password" value="0000"></property>
    
        </bean>
    </beans>

public static void main(String[] args) { 
        // 1.获取容器
        ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
        // 2.获取对象
        JdbcTemplate jdbcTemplate = ac.getBean("jdbcTemplate", JdbcTemplate.class);
        // 3.执行操作
        jdbcTemplate.execute("insert into account(name,money) values ('777',777)");
    }

也是可以的：  
![在这里插入图片描述][20200514105026417.png]

## 4. JdbcTemplate的CRUD操作 ##

示例代码：

package com.veeja.jdbctemplate;
    
    import com.veeja.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.List;
    
    /** * JdbcTemplate的CRUD操作 * * @Author veeja * 2020/5/14 10:24 */
    public class JdbcTemplateDemo3 { 
        public static void main(String[] args) { 
            // 1.获取容器
            ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
            // 2.获取对象
            JdbcTemplate jdbcTemplate = ac.getBean("jdbcTemplate", JdbcTemplate.class);
            // 3.执行操作
            // 保存
            jdbcTemplate.update("insert into account (name,money)values(?,?)", "8888", 8888f);
    
            // 更新
            jdbcTemplate.update("update account set name=?,money=? where id=?", "88888", 88888f, 8);
    
            // 删除
            jdbcTemplate.update("delete from account where id =?", 8);
    
            // 查询所有
            List<Account> accounts = jdbcTemplate.query("select * from account where money > ?", new AccountRowMapper(), 500f);
            for (Account account : accounts) { 
                System.out.println(account);
            }
            // 查询所有的第二种方式
            List<Account> accounts2 = jdbcTemplate.query("select * from account where money > ?", new BeanPropertyRowMapper<Account>(Account.class), 500f);
            for (Account account : accounts2) { 
                System.out.println(account);
            }
    
            // 查询一个
            List<Account> accounts3 = jdbcTemplate.query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), 1);
            System.out.println(accounts3.isEmpty() ? "没有内容" : accounts.get(0));
    
            // 查询返回一行一列（使用聚合函数，但是不加group by子句）
            Long count = jdbcTemplate.queryForObject("select count(*) from account where money>?", Long.class, 500f);
            System.out.println(count);
    
    
        }
    }
    
    /** * 定义Account的封装策略 */
    class AccountRowMapper implements RowMapper<Account> { 
        /** * 把结果集中的数据封装到Account中，然后spring把每个Account加到集合中 * * @param resultSet * @param i * @return * @throws SQLException */
        @Override
        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;
        }
    }

## 5. 基于DAO的jdbcTemplate ##

**bean.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">
    
        
        <bean id="accountDao" class="com.veeja.dao.impl.AccountDaoImpl">
            <property name="jdbcTemplate" ref="jdbcTemplate"></property>
        </bean>
        
        
        <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.cj.jdbc.Driver"></property>
            <property name="url" value="jdbc:mysql://localhost:3306/springdatabase?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC"></property>
            <property name="username" value="root"></property>
            <property name="password" value="0000"></property>
    
        </bean>
    </beans>

**IAccountDao ：**

/** * 账户的持久层接口 * * @Author veeja * 2020/5/14 13:06 */
    public interface IAccountDao { 
        /** * 根据Id查询账户 * * @param accountId * @return */
        Account findAccountById(Integer accountId);
    
        /** * 根据名称查询账户 * * @param Name * @return */
        Account findAccountByName(String Name);
    
    
        /** * 更新账户信息 * * @param account */
        void updateAccount(Account account);
    }

**AccountDaoImpl ：**

/** * 账户的持久层实现类 * * @Author veeja * 2020/5/14 13:12 */
    public class AccountDaoImpl implements IAccountDao { 
    
        private JdbcTemplate jdbcTemplate;
    
        public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
            this.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), accountName);
            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());
        }
    }

**测试方法：**

/** * JdbcTemplate的CRUD操作 * * @Author veeja * 2020/5/14 10:24 */
    public class JdbcTemplateDemo4 { 
        public static void main(String[] args) { 
            // 1.获取容器
            ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
            // 2.获取对象
            IAccountDao accountDao = ac.getBean("accountDao", IAccountDao.class);
    
            // 根据id查询
            Account account1 = accountDao.findAccountById(1);
            System.out.println(account1);
    
            // 根据名称查询
            Account account2 = accountDao.findAccountByName("bbb");
            System.out.println(account2);
    
            // 更新用户信息
            Account account3 = new Account();
            account3.setId(7);
            account3.setName("testNew");
            account3.setMoney(50000f);
            accountDao.updateAccount(account3);
    
        }
    }

结果：  
![在这里插入图片描述][20200514144034246.png]

## 6. 使用JdbcDaoSupport抽取重复代码 ##

如果我们有很多个Dao的类，里面都会有一段重复的代码，我们应该怎么抽取出来呢？

package com.veeja.dao.impl;
    
    import org.springframework.jdbc.core.JdbcTemplate;
    
    import javax.sql.DataSource;
    
    /** * 此类用于抽取DAO中的重复代码 * * @Author veeja * 2020/5/14 15:59 */
    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);
        }
    }

改造DAO实现类：

package com.veeja.dao.impl;
    
    import com.veeja.dao.IAccountDao;
    import com.veeja.domain.Account;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    import java.util.List;
    
    /** * 账户的持久层实现类 * * @Author veeja * 2020/5/14 13:12 */
    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), accountName);
            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());
        }
    }

bean.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">
    
        
        <bean id="accountDao" class="com.veeja.dao.impl.AccountDaoImpl">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
    
        
        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
            <property name="url" value="jdbc:mysql://localhost:3306/springdatabase?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC"></property>
            <property name="username" value="root"></property>
            <property name="password" value="0000"></property>
        </bean>
    </beans>

其实spring已经为我们提供了这个类，名字就叫JdbcDaoSupport：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70]  
以后我们写DAO的时候直接继承这个类就可以了。

--------------------

End.

[20200514102204375.png]: /images/20210615/a4baed9fce1f48feb2a2b10238a8e03d.png
[20200514103352472.png]: /images/20210615/09abdb8153d840a5b54f71120a33e735.png
[20200514105026417.png]: /images/20210615/30cf117fcede4d29843f357238956442.png
[20200514144034246.png]: /images/20210615/141cd5c74e0c46f4bc1d3caf427fbb9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ1NjUxMjc_size_16_color_FFFFFF_t_70]: /images/20210615/e38adb4f776c49f1883776bb8f55e84b.png