Spring整合DbUtils(IOC实战)
一、什么是DbUtils
DbUtils是Apache的一款用于简化Dao代码的工具类,它底层封装了JDBC技术
核心对象
QueryRunner queryRunner = new QueryRunner(DataSource dataSource);
核心方法
int update(): 执行增删改语句
T query(): 执行查询语句
ResultSetHandler<T> 这是一个接口,主要作用是将数据库返回的记录封装到实体对象中
二、实现
- 准备数据库环境:
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);
- 创建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>
-
编写Account实体类
public class Account { private Integer id; private String name; private Double money; }
-
编写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();
}
}
}
- 编写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);
}
}
- 编写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>
-
编写测试代码
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); } }
-
抽取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>