php dbutils 使用,commons-dbutils实现增删改查(spring新注解)(示例代码)

1、maven依赖

4.0.0

com.ly.spring

spring04

1.0-SNAPSHOT

jar

org.springframework

spring-context

5.0.2.RELEASE

mysql

mysql-connector-java

5.1.6

commons-dbutils

commons-dbutils

1.6

c3p0

c3p0

0.9.0.2

junit

junit

4.10

test

2、实体类

packagecom.ly.spring.domain;importjava.io.Serializable;public class Account implementsSerializable {privateInteger id;privateInteger uid;private doublemoney;publicInteger getId() {returnid;

}public voidsetId(Integer id) {this.id =id;

}publicInteger getUid() {returnuid;

}public voidsetUid(Integer uid) {this.uid =uid;

}public doublegetMoney() {returnmoney;

}public void setMoney(doublemoney) {this.money =money;

}

@OverridepublicString toString() {return "Account{" +

"id=" + id +

", uid=" + uid +

", money=" + money +

‘}‘;

}

}

3、Service接口

packagecom.ly.spring.service;importcom.ly.spring.domain.Account;importjava.sql.SQLException;importjava.util.List;public interfaceIAccountService {public List findAll() throwsSQLException;public Account findOne(Integer id) throwsSQLException;public void save(Account account) throwsSQLException;public void update(Account account) throwsSQLException;public void delete(Integer id) throwsSQLException;

}

4、Service实现类

packagecom.ly.spring.service.impl;importcom.ly.spring.dao.IAccountDao;importcom.ly.spring.domain.Account;importcom.ly.spring.service.IAccountService;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importjava.sql.SQLException;importjava.util.List;

@Service("accountService")public class AccountServiceImpl implementsIAccountService {

@AutowiredprivateIAccountDao accountDao;public List findAll() throwsSQLException {returnaccountDao.findAll();

}

@Overridepublic Account findOne(Integer id) throwsSQLException {returnaccountDao.findOne(id);

}

@Overridepublic void save(Account account) throwsSQLException {

accountDao.save(account);

}

@Overridepublic void update(Account account) throwsSQLException {

accountDao.update(account);

}

@Overridepublic void delete(Integer id) throwsSQLException {

accountDao.delete(id);

}

}

5、Dao接口

packagecom.ly.spring.dao;importcom.ly.spring.domain.Account;importjava.sql.SQLException;importjava.util.List;public interfaceIAccountDao {public List findAll() throwsSQLException;public Account findOne(Integer id) throwsSQLException;public void save(Account account) throwsSQLException;public void update(Account account) throwsSQLException;public void delete(Integer id) throwsSQLException;

}

6、Dao实现类

packagecom.ly.spring.dao.impl;importcom.ly.spring.dao.IAccountDao;importcom.ly.spring.domain.Account;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.handlers.BeanHandler;importorg.apache.commons.dbutils.handlers.BeanListHandler;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Repository;importjava.sql.SQLException;importjava.util.List;

@Repository("accountDao")public class AccountDaoImpl implementsIAccountDao {

@AutowiredprivateQueryRunner queryRunner;public List findAll() throwsSQLException {return queryRunner.query("select * from account",new BeanListHandler(Account.class));

}

@Overridepublic Account findOne(Integer id) throwsSQLException {return queryRunner.query("select * from account where id = ?",new BeanHandler(Account.class),id);

}

@Overridepublic void save(Account account) throwsSQLException {

queryRunner.update("insert into account(uid,money) values(?,?)",account.getUid(),account.getMoney());

}

@Overridepublic void update(Account account) throwsSQLException {

queryRunner.update("update account set money = ? where id = ?",account.getMoney(),account.getId());

}

@Overridepublic void delete(Integer id) throwsSQLException {

queryRunner.update("delete from account where id = ?",id);

}

}

7、jdbc资源文件

jdbc.url = jdbc:mysql://localhost:3306/db01

jdbc.driver = com.mysql.jdbc.Driver

jdbc.username = root

jdbc.password = root

8、spring主配置类

packagecom.ly.spring.config;import org.springframework.context.annotation.*;//@Configuration：用于标记此类为配置类/*** @Configuration说明：

* 1、若该类为new AnnotationConfigApplicationContext()的入参类型，则可以省略@Configuration注解

* 2、若该类是配置类但不是new AnnotationConfigApplicationContext()的入参类型(JdbcConfig.java)，

* 且引入该配置类的方式是@ComponentScan而非@Import时，不可以省略@Configuration注解

* 3、若是通过@Import引入的该配置类(JdbcConfig.java)，则被引入的配置类可省略@Configuration注解

* 4、@ComponentScan({"com.ly.spring","xx.xxx"})可以配置多个*/@Configuration//@ComponentScan:用于指定spring注解扫描的包

@ComponentScan("com.ly.spring")//@PropertySource:指定外部properties文件

@PropertySource("classpath:db.properties")//@Import:引入另外一个配置类/*** @Import说明

* 1、若需要引入的类通过@ComponentScan可以被扫描到，且该类有@Configuration注解则不需要配置@Import注解引入该配置类

* 2、使用@Import引入配置类时，该配置类可以省略@Configuration注解

* 3、@Import({JdbcConfig.class,xxx.class}) 可以引入多个*/@Import(JdbcConfig.class)public classSpringConfiguration {

}

9、jdbc配置类

packagecom.ly.spring.config;importcom.mchange.v2.c3p0.ComboPooledDataSource;importorg.apache.commons.dbutils.QueryRunner;importorg.springframework.beans.factory.annotation.Qualifier;importorg.springframework.beans.factory.annotation.Value;importorg.springframework.context.annotation.Bean;importorg.springframework.context.annotation.Configuration;importorg.springframework.context.annotation.Scope;importjavax.sql.DataSource;//@Configuration：用于标记此类为配置类

@Configurationpublic classJdbcConfig {//@Value：用于读取资源文件中指定key对用的值

@Value("${jdbc.url}")privateString jdbcUrl;

@Value("${jdbc.driver}")privateString jdbcDriver;

@Value("${jdbc.username}")privateString username;

@Value("${jdbc.password}")privateString password;//@Bean用于在spring容器中创建方法返回值类型的bean,创建的bean默认id为方法名//@Bean配置了name属性时即指定了创建的bean对应的id

@Bean//@Scope：指定创建的bean的作用范围，默认是单例的

@Scope("singleton")//当方法中有参数且没有@Qualifier注解指定bean的id时，会自动从spring容器中按照@Autowired注解的方式去注入bean//当方法中有参数且有@Qualifier注解指定bean的id时，会根据指定的id去spring容器中寻找对应的bean

public QueryRunner getQueryRunner(@Qualifier("dataSource") DataSource dataSource) {return newQueryRunner(dataSource);

}//name指定创建的bean在spring容器中的id

@Bean(name = "dataSource")publicDataSource getDataSource() {try{

ComboPooledDataSource comboPooledDataSource= newComboPooledDataSource();

comboPooledDataSource.setDriverClass(jdbcDriver);

comboPooledDataSource.setJdbcUrl(jdbcUrl);

comboPooledDataSource.setUser(username);

comboPooledDataSource.setPassword(password);returncomboPooledDataSource;

}catch(Exception e) {throw newRuntimeException(e);

}

}

}

10、测试类

packagecom.ly.spring.test;importcom.ly.spring.config.JdbcConfig;importcom.ly.spring.config.SpringConfiguration;importcom.ly.spring.domain.Account;importcom.ly.spring.service.IAccountService;importorg.apache.commons.dbutils.QueryRunner;importorg.junit.Before;importorg.junit.Test;importorg.springframework.context.annotation.AnnotationConfigApplicationContext;importjavax.sql.DataSource;importjava.sql.SQLException;importjava.util.List;public classMainTest {privateAnnotationConfigApplicationContext context;privateIAccountService accountService;

@Beforepublic voidinit() {//通过注解获取spring容器//可以同时指定多个配置类//context = new AnnotationConfigApplicationContext(SpringConfiguration.class, JdbcConfig.class);

context = new AnnotationConfigApplicationContext(SpringConfiguration.class);

accountService= context.getBean("accountService",IAccountService.class);

}

@Testpublic voidtestScope() {

DataSource dataSource1= context.getBean("dataSource", DataSource.class);

DataSource dataSource2= context.getBean("dataSource", DataSource.class);

System.out.println(dataSource1==dataSource2);

QueryRunner queryRunner1= context.getBean("getQueryRunner", QueryRunner.class);

QueryRunner queryRunner2= context.getBean("getQueryRunner", QueryRunner.class);

System.out.println(queryRunner1);

System.out.println(queryRunner1==queryRunner2);

}

@Testpublic void findAll() throwsSQLException {

List accounts =accountService.findAll();

System.out.println(accounts);

}

@Testpublic void findOne() throwsSQLException {

Account account= accountService.findOne(3);

System.out.println(account);

}

@Testpublic void save() throwsSQLException {

Account account= newAccount();

account.setUid(52);

account.setMoney(6000);

accountService.save(account);

}

@Testpublic void update() throwsSQLException {

Account account= newAccount();

account.setId(5);

account.setMoney(100000);

accountService.update(account);

}

@Testpublic void delete() throwsSQLException {

accountService.delete(5);

}

}

11、补充spring整合junit，修改如下：

11.1、引入spring-test的jar包

org.springframework

spring-test

5.0.2.RELEASE

test

11.2、在测试类中使用@RunWith注解替换junit的main方法

11.3、在测试类中使用@ContextConfiguration注解指定spring配置文件的位置或者spring配置类

packagecom.ly.spring.test;importcom.ly.spring.config.SpringConfiguration;importcom.ly.spring.domain.Account;importcom.ly.spring.service.IAccountService;importorg.junit.Test;importorg.junit.runner.RunWith;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.test.context.ContextConfiguration;importorg.springframework.test.context.junit4.SpringJUnit4ClassRunner;importjava.sql.SQLException;importjava.util.List;//@RunWith:用于替换junit的main方法

@RunWith(SpringJUnit4ClassRunner.class)//@ContextConfiguration：用于指定spring配置文件的位置或者spring配置类/*** @ContextConfiguration说明

* 1、locations用于指定spring配置文件的位置locations = "classpath:bean.xml"

* 2、classes用于指定spring配置类*/@ContextConfiguration(classes= SpringConfiguration.class)public classMainTest {

@AutowiredprivateIAccountService accountService;

@Testpublic void findAll() throwsSQLException {

List accounts =accountService.findAll();

System.out.println(accounts);

}

@Testpublic void findOne() throwsSQLException {

Account account= accountService.findOne(3);

System.out.println(account);

}

@Testpublic void save() throwsSQLException {

Account account= newAccount();

account.setUid(52);

account.setMoney(6000);

accountService.save(account);

}

@Testpublic void update() throwsSQLException {

Account account= newAccount();

account.setId(5);

account.setMoney(100000);

accountService.update(account);

}

@Testpublic void delete() throwsSQLException {

accountService.delete(5);

}

}

11.4、SpringJUnit4ClassRunner requires JUnit 4.12 or higher报错解决

需升级junit版本4.12及以上

junit

junit

4.12

test