步骤:
- 建立数据库表
- 引入依赖
- 创建实体类 domain
- 创建dao层 创建dao接口和实现类
- 创建service层 创建service接口和实现类
- 编写配置文件
- 测试类
代码示例:
1. 数据库表
以三列示例 id name money
2. 引入依赖 分别为 sql spring junit dbuntils c3p0
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.cfg</groupId>
<artifactId>spring_day02_crud</artifactId>
<version>1.0-SNAPSHOT</version>
<!--引入依赖-->
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.32</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
</project>
3. 创建实体类(domain包下) Account 及get set
package cn.cfg.domain;
public class Account {
private Integer id;
private String name;
private Float 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;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
4. 创建dao接口和实现类
dao接口
package cn.cfg.dao;
import cn.cfg.domain.Account;
import org.apache.commons.dbutils.QueryRunner;
import java.util.List;
public interface AccountDao{
// 查所有
public List<Account> findAll();
// 根据id查
public Account findById(Integer id);
//添加
public void save(Account account);
//更改
public void update(Account account);
//删除
public void del(Integer id);
}
dao接口实现类 AccountDaoImpl
package cn.cfg.dao.impl;
import cn.cfg.dao.AccountDao;
import cn.cfg.domain.Account;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;
public class AccountDaoImpl implements AccountDao {
QueryRunner queryRunner;
public void setQueryRunner(QueryRunner queryRunner) {
this.queryRunner = queryRunner;
}
public List<Account> findAll() {
String sql="select * from account";
try {
List<Account> list = queryRunner.query(sql, new BeanListHandler<Account>(Account.class));
return list;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//根据id查
public Account findById(Integer id) {
String sql="select * from account where id=?";
try {
Account account = queryRunner.query(sql, new BeanHandler<Account>(Account.class), id);
return account;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//保存
public void save(Account account) {
String sql="insert into account values(null,?,?)";
try {
queryRunner.update(sql,account.getName(),account.getMoney());
} catch (SQLException e) {
e.printStackTrace();
}
}
//根据id更改
public void update(Account account) {
String sql="update account set name=?,money=? where id=?";
try {
queryRunner.update(sql,account.getName(),account.getMoney(),account.getId());
} catch (SQLException e) {
e.printStackTrace();
}
}
//根据id删除
public void del(Integer id) {
String sql="delete from account where id=?";
try {
queryRunner.update(sql,id);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5. 创建service接口和实现类
service接口
package cn.cfg.service;
import cn.cfg.domain.Account;
import java.util.List;
public interface AccountService {
// 查所有
public List<Account> findAll();
// 根据id查
public Account findById(Integer id);
//添加
public void save(Account account);
//更改
public void update(Account account);
//删除
public void del(Integer id);
}
service实现类
package cn.cfg.service.impl;
import cn.cfg.dao.AccountDao;
import cn.cfg.domain.Account;
import cn.cfg.service.AccountService;
import java.util.List;
public class AccountServiceImpl implements AccountService {
AccountDao accountDao;
public void setAccountDao(AccountDao accountDao) {
this.accountDao = accountDao;
}
public List<Account> findAll() {
return accountDao.findAll();
}
public Account findById(Integer id) {
return accountDao.findById(id);
}
public void save(Account account) {
accountDao.save(account);
}
public void update(Account account) {
accountDao.update(account);
}
public void del(Integer id) {
accountDao.del(id);
}
}
6. 编写配置文件
首先jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring
jdbc.user=root
jdbc.password=root
核心配置文件 applationContext.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"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<!--测试类需要调用service 传入dao-->
<bean id="accountService" class="cn.cfg.service.impl.AccountServiceImpl">
<property name="accountDao" ref="accountDao"></property>
</bean>
<!--给accountDao层中的queryRunner dateSource 进行传值 配置 -->
<bean id="accountDao" class="cn.cfg.dao.impl.AccountDaoImpl">
<property name="queryRunner" ref="queryRunner"></property>
</bean>
<!--此处的qureyRunner传送给14行中ref中的queryRunner
下面传值时用构造函数方式而未用set方法
因为在daoImpl中 queryRunner是以构造函数方式存在的
-->
<bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
<constructor-arg type="javax.sql.DataSource" ref="dateSource"></constructor-arg>
</bean>
<!--dateSource传值 引用jdbc.properties中的数据-->
<bean id="dateSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"></property>
<property name="jdbcUrl" value="${jdbc.url}"></property>
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<!--引入jdbc.properties文件-->
<context:property-placeholder location="jdbc.properties"></context:property-placeholder>
</beans>
7. 测试类
package cn.cfg;
import cn.cfg.domain.Account;
import cn.cfg.service.AccountService;
import cn.cfg.service.impl.AccountServiceImpl;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class TestCRUD {
// 查询所有
@Test
public void testFindAll(){
ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("applationContext.xml");
AccountService accountService = ac.getBean("accountService", AccountService.class);
List<Account> all = accountService.findAll();
for (Account aa:all){
System.out.println(aa);
}
}
// 根据id查询
@Test
public void testFindById(){
ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("applationContext.xml");
AccountService accountService = ac.getBean("accountService", AccountService.class);
Account account = accountService.findById(2);
System.out.println(account);
}
// 添加 保存
@Test
public void testSave(){
ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("applationContext.xml");
AccountService accountService = ac.getBean("accountService", AccountService.class);
Account account = new Account();
account.setName("李四");
account.setId(null);
account.setMoney(1999888F);
accountService.save(account);
}
//删除 新添加的李四
@Test
public void testDel(){
ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("applationContext.xml");
AccountServiceImpl accountService = ac.getBean("accountService", AccountServiceImpl.class);
accountService.del(4);
}
}
添加成功效果:
删除成功效果: