zhengzf0 2020-05-28
本教程源码请访问:tutorial_demo
在《Spring04_IOC和DI实战》当中,我们使用XML配置的方式结合Apache Commons DbUtils实现单表的CRUD操作,这篇教程我们使用纯注解方式结合Apache Commons DbUtils实现单表的CRUD操作。
实现单表的CRUD
DROP DATABASE IF EXISTS springlearn; CREATE DATABASE springlearn; USE springlearn; DROP TABLE IF EXISTS account; CREATE TABLE account ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(40) DEFAULT NULL, money float DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4; INSERT INTO account VALUES (‘1‘, ‘aaa‘, ‘1000‘); INSERT INTO account VALUES (‘2‘, ‘bbb‘, ‘1000‘); INSERT INTO account VALUES (‘3‘, ‘ccc‘, ‘1000‘); INSERT INTO account VALUES (‘5‘, ‘cc‘, ‘10000‘); INSERT INTO account VALUES (‘6‘, ‘abc‘, ‘10000‘); INSERT INTO account VALUES (‘7‘, ‘abc‘, ‘10000‘);
<?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>org.codeaction</groupId> <artifactId>account</artifactId> <version>1.0-SNAPSHOT</version> <packaging>jar</packaging> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>5.2.6.RELEASE</version> </dependency> <dependency> <groupId>commons-dbutils</groupId> <artifactId>commons-dbutils</artifactId> <version>1.4</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>c3p0</groupId> <artifactId>c3p0</artifactId> <version>0.9.1.2</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13</version> <scope>test</scope> </dependency> </dependencies> </project>
package org.codeaction.domain; import java.io.Serializable; public class Account implements Serializable { 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 + ‘}‘; } }
package org.codeaction.dao; import org.codeaction.domain.Account; import java.util.List; public interface IAccountDao { List<Account> findAll(); Account findById(Integer id); void save(Account account); void update(Account account); void delete(Integer id); }
package org.codeaction.dao.impl; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.codeaction.dao.IAccountDao; import org.codeaction.domain.Account; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import java.sql.SQLException; import java.util.List; @Repository("accountDao") public class AccountDaoImpl implements IAccountDao { @Autowired private QueryRunner queryRunner; public void setQueryRunner(QueryRunner queryRunner) { this.queryRunner = queryRunner; } @Override public List<Account> findAll() { List<Account> list = null; try { list = queryRunner.query("select * from account", new BeanListHandler<Account>(Account.class)); } catch (SQLException e) { e.printStackTrace(); } return list; } @Override public Account findById(Integer id) { Account account = null; try { account = queryRunner.query("select * from account where id = ?", new BeanHandler<Account>(Account.class), id); } catch (SQLException e) { e.printStackTrace(); } return account; } @Override public void save(Account account) { Object[] params = {account.getName(), account.getMoney()}; try { queryRunner.update("insert into account(name, money) values(?, ?)", params); } catch (SQLException e) { e.printStackTrace(); } } @Override public void update(Account account) { Object[] params = {account.getName(), account.getMoney(), account.getId()}; try { queryRunner.update("update account set name=?, money=? where id=?", params); } catch (SQLException e) { e.printStackTrace(); } } @Override public void delete(Integer id) { Object[] params = {id}; try { queryRunner.update("delete from account where id=?", id); } catch (SQLException e) { e.printStackTrace(); } } }
package org.codeaction.service; import org.codeaction.domain.Account; import java.util.List; public interface IAccountService { List<Account> findAll(); Account findById(Integer id); void save(Account account); void update(Account account); void delete(Integer id); }
package org.codeaction.service.impl; import org.codeaction.dao.IAccountDao; import org.codeaction.domain.Account; import org.codeaction.service.IAccountService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service("accountService") public class AccountServiceImpl implements IAccountService { @Autowired private IAccountDao accountDao; public void setAccountDao(IAccountDao 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); } }
这个配置类用来配置QueryRunner。
package org.codeaction.config; import com.mchange.v2.c3p0.ComboPooledDataSource; import org.apache.commons.dbutils.QueryRunner; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.sql.DataSource; @Configuration public class JdbcConfig { @Value("${jdbc.driverClass}") private String driverClass; @Value("${jdbc.jdbcUrl}") private String jdbcUrl; @Value("${jdbc.user}") private String user; @Value("${jdbc.password}") private String password; @Bean("queryRunner") public QueryRunner queryRunner(@Qualifier("dataSource") DataSource dataSource) { return new QueryRunner(dataSource); } @Bean("dataSource") public DataSource dataSource() { ComboPooledDataSource dataSource = new ComboPooledDataSource(); try { dataSource.setDriverClass(driverClass); dataSource.setJdbcUrl(jdbcUrl); dataSource.setUser(user); dataSource.setPassword(password); } catch (Exception e) { e.printStackTrace(); } return dataSource; } }
作用:
package org.codeaction.config; import org.springframework.context.annotation.ComponentScan; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Import; import org.springframework.context.annotation.PropertySource; @Configuration @ComponentScan(basePackages = "org.codeaction") @PropertySource("classpath:jdbc.properties") @Import(JdbcConfig.class) public class MyConfig { }
这个文件在resource目录下,文件名:jdbc.properties
jdbc.driverClass=com.mysql.jdbc.Driver jdbc.jdbcUrl=jdbc:mysql://localhost:3306/springlearn jdbc.user=root jdbc.password=123456
package org.codeaction.test; import org.codeaction.config.MyConfig; import org.codeaction.domain.Account; import org.codeaction.service.IAccountService; import org.codeaction.service.impl.AccountServiceImpl; import org.junit.Before; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.AnnotationConfigApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import java.util.List; public class MyTest { private IAccountService accountService; @Before public void init() { ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class); accountService = context.getBean("accountService", AccountServiceImpl.class); } @Test public void testFindAll() { List<Account> accounts = accountService.findAll(); for (Account account : accounts) { System.out.println(account); } } @Test public void testFindById() { Account account = accountService.findById(3); System.out.println(account); } @Test public void testSave() { Account account = new Account(); account.setName("abc"); account.setMoney(10000F); accountService.save(account); System.out.println(account); } @Test public void testDelete() { accountService.delete(4); } @Test public void testUpdate() { Account account = new Account(); account.setId(5); account.setName("ab111111111c111"); account.setMoney(10000F); accountService.update(account); } }
运行测试方法进行测试。
以上就是纯注解方式结合Apache Commons DbUtils实现单表的CRUD操作。
在上面的测试类中,有如下的代码
@Before public void init() { ApplicationContext context = new AnnotationConfigApplicationContext(MyConfig.class); accountService = context.getBean("accountService", AccountServiceImpl.class); }
这段代码用来创建Spring容器,然后从Spring容器中获取Service对象。
在实际开发中,对于测试人员来讲可能不了解Spring,也不知道如何创建Spring容器,这段代码对测试人员来讲并不友好,有没有什么方式能在测试类中注入Service对象呢?针对上述问题,我们对项目进行进行如下修改。
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>5.2.6.RELEASE</version> </dependency>
这个是Spring整合Junit的Jar包坐标。
package org.codeaction.test; import org.codeaction.config.MyConfig; import org.codeaction.domain.Account; import org.codeaction.service.IAccountService; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import java.util.List; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(classes = MyConfig.class) public class MyTest { @Autowired private IAccountService accountService; @Test public void testFindAll() { List<Account> accounts = accountService.findAll(); for (Account account : accounts) { System.out.println(account); } } @Test public void testFindById() { Account account = accountService.findById(3); System.out.println(account); } @Test public void testSave() { Account account = new Account(); account.setName("abc"); account.setMoney(10000F); accountService.save(account); System.out.println(account); } @Test public void testDelete() { accountService.delete(4); } @Test public void testUpdate() { Account account = new Account(); account.setId(5); account.setName("ab111111111c111"); account.setMoney(10000F); accountService.update(account); } }
@RunWith:这个注解用来替换的原来的运行器。
@ContextConfiguration
作用:指定Spring配置文件的位置。
属性:
运行测试方法进行测试。