Spring整合DbUtils(IOC实战)

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

核心对象

QueryRunner queryRunner = new QueryRunner(DataSource dataSource);

核心方法

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

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项目，导入坐标

 
     
        mysql
        mysql-connector-java 
        5.1.47
     
     
        com.alibaba 
        druid 
        1.1.9 
     
     
        commons-dbutils 
        commons-dbutils 
        1.6 
     
     
        org.springframework 
        spring-context 
        5.1.5.RELEASE 
     
     
        junit 
        junit
        4.12 
    

3. 编写Account实体类

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

4. 编写AccountDao接口和实现类

public interface AccountDao {
    public List 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 findAll() { 
        List list = null; 
        // 编写sql 
        String sql = "select * from account"; 
        try {
            // 执行sql 
            list = queryRunner.query(sql, new BeanListHandler (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.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 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 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

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 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