Spring整合DbUtils(IOC实战)

Spring整合DbUtils(IOC实战)

一、什么是DbUtils

DbUtils是Apache的一款用于简化Dao代码的工具类，它底层封装了JDBC技术

核心对象

QueryRunner queryRunner = new QueryRunner(DataSource dataSource);

核心方法

int update():   执行增删改语句
 T query():     执行查询语句
    ResultSetHandler<T>  这是一个接口，主要作用是将数据库返回的记录封装到实体对象中

二、实现

1. 准备数据库环境：

CREATE DATABASE `spring_db`; 
USE `spring_db`; 
CREATE TABLE `account` ( `
id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL, 
`money` double DEFAULT NULL,
PRIMARY KEY (`id`) 
) ; 

insert into `account`(`id`,`name`,`money`) values (1,'tom',1000), (2,'jerry',1000);

2. 创建java项目，导入坐标

<dependencies> 
    <dependency> 
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId> 
        <version>5.1.47</version>
    </dependency> 
    <dependency> 
        <groupId>com.alibaba</groupId> 
        <artifactId>druid</artifactId> 
        <version>1.1.9</version> 
    </dependency> 
    <dependency> 
        <groupId>commons-dbutils</groupId> 
        <artifactId>commons-dbutils</artifactId> 
        <version>1.6</version> 
    </dependency> 
    <dependency> 
        <groupId>org.springframework</groupId> 
        <artifactId>spring-context</artifactId> 
        <version>5.1.5.RELEASE</version> 
    </dependency> 
    <dependency> 
        <groupId>junit</groupId> 
        <artifactId>junit</artifactId>
        <version>4.12</version> 
    </dependency>
</dependencies>

3. 编写Account实体类

   public class Account {
       private Integer id;
       private String name;
       private Double money;
   }
   

4. 编写AccountDao接口和实现类

public interface AccountDao {
    public List<Account> findAll();
    public Account findById(Integer id); 
    public void save(Account account);
    public void update(Account account); 
    public void delete(Integer id); 
}

public class AccountDaoImpl implements AccountDao { 
    private QueryRunner queryRunner; 
    public void setQueryRunner(QueryRunner queryRunner) { 
        this.queryRunner = queryRunner; 
    }
    @Override 
    public List<Account> findAll() { 
        List<Account> list = null; 
        // 编写sql 
        String sql = "select * from account"; 
        try {
            // 执行sql 
            list = queryRunner.query(sql, new BeanListHandler<Account> (Account.class)); 
        } catch (SQLException e) {
            e.printStackTrace(); 
        }
        return list;
    }
    @Override 
    public Account findById(Integer id) { 
        Account account = null; 
        // 编写sql 
        String sql = "select * from account where id = ?";
        try {
            // 执行sql
            account = queryRunner.query(sql, new BeanHandler<Account> (Account.class), id); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        }
        return account; 
    }
    @Override
    public void save(Account account) { 
        // 编写sql 
        String sql = "insert into account values(null,?,?)"; 
        // 执行sql 
        try {
            queryRunner.update(sql, account.getName(), account.getMoney()); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
    }
    @Override
    public void update(Account account) { 
        // 编写sql
        String sql = "update account set name = ?,money = ? where id = ?"; 
        // 执行sql 
        try {               queryRunner.update(sql,account.getName(),account.getMoney(),account.getId()); 
        } catch (SQLException e) { 
            e.printStackTrace();
        } 
    }@Override 
    public void delete(Integer id) {
        // 编写sql 
        String sql = "delete from account where id = ?"; 
        // 执行sql 
        try {
            queryRunner.update(sql, id); 
        } catch (SQLException e) {
            e.printStackTrace(); 
        } 
    } 
}

5. 编写AccountService接口和实现类

public interface AccountService{
    public List<Account> findAll();
    public Account findById(Integer id); 
    public void save(Account account); 
    public void update(Account account); 
    public void delete(Integer id);
}

public class AccountServiceImpl implements AccountService {
    private AccountDao accountDao; 
    public void setAccountDao(AccountDao accountDao) { 
        this.accountDao = accountDao;
    }
    @Override 
    public List<Account> findAll() {
        return accountDao.findAll(); 
    }
    @Override 
    public Account findById(Integer id) {
        return accountDao.findById(id); 
    }
    @Override
    public void save(Account account) { 
        accountDao.save(account);
    }
    @Override 
    public void update(Account account) {
        accountDao.update(account); 
    }
    @Override 
    public void delete(Integer id) { 
        accountDao.delete(id);
    } 
}

6. 编写spring核心配置文件

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"> 
    <!--把数据库连接池交给IOC容器--> 
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> 		          <property name="driverClassName" value="com.mysql.jdbc.Driver"> </property> 
        <property name="url" value="jdbc:mysql://localhost:3306/spring_db"> </property> 
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
    </bean> 
    <!--把QueryRunner交给IOC容器--> 
    <bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner"> 			     <constructor-arg name="ds" ref="dataSource"></constructor-arg> 
    </bean> 
    <!--把AccountDao交给IOC容器--> 
    <bean id="accountDao" class="com.lagou.dao.impl.AccountDaoImpl"> 
        <property name="queryRunner" ref="queryRunner"></property> 
    </bean> 
    <!--把AccountService交给IOC容器--> 
    <bean id="accountService" class="com.lagou.service.impl.AccountServiceImpl"> 			<property name="accountDao" ref="accountDao"></property>
    </bean>
</beans>

7. 编写测试代码

   public class AccountServiceTest {
       ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
       AccountService accountService = applicationContext.getBean(AccountService.class); 
       //测试保存 
       @Test
       public void testSave() {
           Account account = new Account(); 
           account.setName("lucy"); account.setMoney(100d); 
           accountService.save(account); 
       }
     //测试查询 
       @Test
       public void testFindById() { 
           Account account = accountService.findById(3); 
           System.out.println(account); }
       //测试查询所有 
       @Test
       public void testFindAll() {
           List<Account> accountList = accountService.findAll();
           for (Account account : accountList) { 
               System.out.println(account);
           } 
       }
       //测试修改
       @Test 
       public void testUpdate() { 
           Account account = new Account(); account.setId(3); 
           account.setName("jack"); account.setMoney(2000d); 
           accountService.update(account); 
       }
       //测试删除
       @Test 
       public void testDelete() {
           accountService.delete(3);
       } 
   }
   

8. 抽取jdbc配置文件

   applicationContext.xml加载jdbc.properties配置文件获得连接信息

   首先，需要引入context命名空间和约束路径:

   * 命名空间：
   		xmlns:context="http://www.springframework.org/schema/context"
   * 约束路径：
   		http://www.springframework.org/schema/context 			   http://www.springframework.org/schema/context/spring-context.xsd
   

   <context:property-placeholder location="classpath:jdbc.properties"/> 
       <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> 	
           <property name="driverClassName" value="${jdbc.driver}"></property>
           <property name="url" value="${jdbc.url}"></property> 
           <property name="username" value="${jdbc.username}"></property> 
           <property name="password" value="${jdbc.password}"></property> 
       </bean>