Spring IOC使用DButil实现对数据库的操作

一、DButil、lombok、junit的简单介绍

1.dbutil

dbutil是由阿帕奇提供操作数据库的插件，其核心类为QueryRunner，存在方法 .query() 查询，.update() 增删改；

<!-- dbutil -->
<dependency><groupId>commons-dbutils</groupId><artifactId>commons-dbutils</artifactId><version>1.4</version>
</dependency>

2.lombok插件

功能：对实体类自动、动态生成get、set方法，无参有参构造方法

步骤：

1.idea安装插件(只做一次)

2.添加坐标

<!-- lombok -->
<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.26</version>
</dependency>

3.编写注解

3.junit测试

1.坐标

<dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope>
</dependency>

2.注解(修饰方法)

@Test======>可以运行的方法

@Before====>@Test运行之前

@After=====>@Test运行之后

---

测试类的两种方法：

public class test01 {ClassPathXmlApplicationContext applicationContext =null;IAccountController controller=null;@Beforepublic void before(){applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");controller = (IAccountController)applicationContext.getBean("controller");}@Testpublic void show1(){List<Account> list = controller.findAll();for (int i = 0; i < list.size(); i++) {Account account = list.get(i);System.out.println(account);}}@Afterpublic void after(){applicationContext.close();}
}

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class test02 {@AutowiredIAccountController controller;@Testpublic void show2(){List<Account> all = controller.findAll();for (int i = 0; i < all.size(); i++) {Account account =  all.get(i);System.out.println(account);}}}

---

在配置文件pom.xml中写出相关坐标

<!--Spring-->
<dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>5.3.27</version>
</dependency><!--MySQL-->
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.28</version>
</dependency><!--数据源-->
<dependency><groupId>c3p0</groupId><artifactId>c3p0</artifactId><version>0.9.1.2</version>
</dependency><!-- lombok -->
<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.26</version>
</dependency><!--junit-->
<dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope>
</dependency><!-- spring测试包 -->
<dependency><groupId>org.springframework</groupId><artifactId>spring-test</artifactId><version>5.3.27</version>
</dependency><!-- dbutil -->
<dependency><groupId>commons-dbutils</groupId><artifactId>commons-dbutils</artifactId><version>1.4</version>
</dependency>

二、使用XML配置文件

1.java对象(pojo)

实现get、set方法

public class Account implements Serializable {public int aid;public String aname;public int amoney;public Account(String aname, int amoney) {this.aname = aname;this.amoney = amoney;}public Account(int aid, String aname, int amoney) {this.aid = aid;this.aname = aname;this.amoney = amoney;}public Account() {}@Overridepublic String toString() {return "Account{" +"aid=" + aid +", aname='" + aname + '\'' +", amoney=" + amoney +'}';}public void setAid(int aid) {this.aid = aid;}public void setAname(String aname) {this.aname = aname;}public void setAmoney(int amoney) {this.amoney = amoney;}public int getAid() {return aid;}public String getAname() {return aname;}public int getAmoney() {return amoney;}
}

使用lombok插件会方便很多，不用自己写set\get方法以及构造方法，直接使用注解实现

@NoArgsConstructor
@AllArgsConstructor
@Data
public class Account implements Serializable {public int aid;public String aname;public int amoney;public Account(String aname, int amoney) {this.aname = aname;this.amoney = amoney;}
}

2.控制层(controller)

• 接口中定义了增删改查的方法
• controller层需传入service层的对象，使用xml配置文件时，要对实现传入对象的set方法
• 重写接口的方法

//接口中定义了增删改查的方法
public interface IAccountController {//查询全部账户public List<Account> findAll();//根据姓名查找账户public Account findByname(String name);//添加账户public void add(Account account);//修改账户public void update(Account account);//删除账户public void delete(int aid);}//实现类
public class AccountControllerImp implements  IAccountController{IAccountService service;public void setService(IAccountService service) {this.service = service;}@Overridepublic List<Account> findAll() {return service.findAll();}@Overridepublic Account findByname(String name) {return service.findByname(name);}@Overridepublic void add(Account account) {service.add(account);}@Overridepublic void update(Account account) {service.update(account);}@Overridepublic void delete(int aid) {service.delete(aid);}
}

3.业务层（service）

• 接口中定义了增删改查的方法
• service层需传入dao层的对象，使用xml配置文件时，要对实现传入对象的set方法
• 重写接口的方法

public interface IAccountService {public List<Account> findAll();public Account findByname(String name);public void add(Account account);public void update(Account account);public void delete(int aid);public void transfer(String sourcename,String targetname,int money);
}public class AccountServiceImp  implements  IAccountService{IAccountDao dao;public void setDao(IAccountDao dao) {this.dao = dao;}@Overridepublic List<Account> findAll() {return dao.findAll();}@Overridepublic Account findByname(String name) {return dao.findByname(name);}@Overridepublic void add(Account account) {dao.add(account);}@Overridepublic void update(Account account) {dao.update(account);}@Overridepublic void delete(int aid) {dao.delete(aid);}
}

4.数据访问层（dao）

• 接口中定义了增删改查的方法
• dao层使用dbutil插件，故传入QueryRunner的对象，实现传入对象的set方法
• 重写接口的方法，分别使用了QueryRunner类下的update方法和query方法

public interface IAccountDao {public List<Account> findAll();public Account findByname(String name);public void add(Account account);public void update(Account account);public void delete(int aid);
}

查询使用query方法，创建了BeanListHandler<类>(类.class),BeanHandler<类>(类.class)

增删改使用update方法

public class AccountDaoImp implements IAccountDao{QueryRunner run;public void setRun(QueryRunner run) {this.run = run;}@Overridepublic List<Account> findAll() {try {return run.query("select * from Account",new BeanListHandler<Account>(Account.class));} catch (SQLException throwables) {throwables.printStackTrace();}return  null;}@Overridepublic Account findByaid(int aid) {try {return run.query("select * from Account where aid=?",new BeanHandler<Account>(Account.class),aid);} catch (SQLException throwables) {throwables.printStackTrace();}return null;}@Overridepublic void add(Account account) {try {run.update("insert into Account(aname,amoney) value(?,?)",account.getAname(),account.getAmoney());} catch (SQLException throwables) {throwables.printStackTrace();}}@Overridepublic void update(Account account) {try {run.update("update Account set aname=?,amoney=?  where aid=?",account.getAname(),account.getAmoney(),account.getAid());} catch (SQLException throwables) {throwables.printStackTrace();}}@Overridepublic void delete(int aid) {try {run.update("delete from Account where aid=?",aid);} catch (SQLException throwables) {throwables.printStackTrace();}}
}

5.写配置文件applicationcontext.xml

<!--加载资源文件-->
<context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder><!--注入数据源-->
<!--将数据库的相关配置写在jdbc.properties文件中-->
<bean id="datasourse" class="com.mchange.v2.c3p0.ComboPooledDataSource"><property name="driverClass" value="${msg1}"></property><property name="jdbcUrl" value="${msg2}"></property><property name="user" value="${msg3}"></property><property name="password" value="${msg4}"></property>
</bean><!--注入QueryRunner-->
<bean id="run" class="org.apache.commons.dbutils.QueryRunner"><constructor-arg name="ds" ref="datasourse"></constructor-arg>
</bean><!--注入Dao-->
<bean id="Dao" class="com.apesourse.dao.AccountDaoImp"><property name="run" ref="run"></property>
</bean><!--注入service-->
<bean id="Service" class="com.apesourse.service.AccountServiceImp"><property name="dao" ref="Dao"></property>
</bean><!--注入controller-->
<bean id="controller" class="com.apesourse.controller.AccountControllerImp"><property name="service" ref="Service"></property>
</bean>

三、使用注解和XML文件

1.java对象(pojo)

@NoArgsConstructor
@AllArgsConstructor
@Data
public class Account implements Serializable {public int aid;public String aname;public int amoney;public Account(String aname, int amoney) {this.aname = aname;this.amoney = amoney;}
}

2.控制层(controller)

//接口中定义了增删改查的方法
public interface IAccountController {//查询全部账户public List<Account> findAll();//根据姓名查找账户public Account findByname(String name);//添加账户public void add(Account account);//修改账户public void update(Account account);//删除账户public void delete(int aid);}//实现类
@Controller("controller")
public class AccountControllerImp implements  IAccountController{@AutowiredIAccountService service;@Overridepublic List<Account> findAll() {return service.findAll();}@Overridepublic Account findByaid(int aid) {return service.findByaid(aid);}@Overridepublic void add(Account account) {service.add(account);}@Overridepublic void update(Account account) {service.update(account);}@Overridepublic void delete(int aid) {service.delete(aid);}
}

3.业务层（service）

public interface IAccountService {public List<Account> findAll();public Account findByname(String name);public void add(Account account);public void update(Account account);public void delete(int aid);public void transfer(String sourcename,String targetname,int money);
}@Service("service")
public class AccountServiceImp  implements  IAccountService{@AutowiredIAccountDao dao;@Overridepublic List<Account> findAll() {return dao.findAll();}@Overridepublic Account findByname(String name) {return dao.findByname(name);}@Overridepublic void add(Account account) {dao.add(account);}@Overridepublic void update(Account account) {dao.update(account);}@Overridepublic void delete(int aid) {dao.delete(aid);}
}

4.数据访问层（dao）

public interface IAccountDao {public List<Account> findAll();public Account findByname(String name);public void add(Account account);public void update(Account account);public void delete(int aid);
}

@Repository
public class AccountDaoImp implements IAccountDao{@AutowiredQueryRunner run;@Overridepublic List<Account> findAll() {try {return run.query("select * from Account",new BeanListHandler<Account>(Account.class));} catch (SQLException throwables) {throwables.printStackTrace();}return  null;}@Overridepublic Account findByaid(int aid) {try {return run.query("select * from Account where aid=?",new BeanHandler<Account>(Account.class),aid);} catch (SQLException throwables) {throwables.printStackTrace();}return null;}@Overridepublic void add(Account account) {try {run.update("insert into Account(aname,amoney) value(?,?)",account.getAname(),account.getAmoney());} catch (SQLException throwables) {throwables.printStackTrace();}}@Overridepublic void update(Account account) {try {run.update("update Account set aname=?,amoney=?  where aid=?",account.getAname(),account.getAmoney(),account.getAid());} catch (SQLException throwables) {throwables.printStackTrace();}}@Overridepublic void delete(int aid) {try {run.update("delete from Account where aid=?",aid);} catch (SQLException throwables) {throwables.printStackTrace();}}
}

5.写配置文件applicationcontext.xml

<!--加载资源文件-->
<context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
<!--注入数据源-->
<bean id="datasourse" class="com.mchange.v2.c3p0.ComboPooledDataSource"><property name="driverClass" value="${msg1}"></property><property name="jdbcUrl" value="${msg2}"></property><property name="user" value="${msg3}"></property><property name="password" value="${msg4}"></property>
</bean><!--注入QueryRunner-->
<bean id="run" class="org.apache.commons.dbutils.QueryRunner"><constructor-arg name="ds" ref="datasourse"></constructor-arg>
</bean><!--扫描-->
<context:component-scan base-package="com.apesourse"></context:component-scan>

四、使用注解（annoation）和配置类（Config）

1.配置类config

@Configuration
@PropertySource(value = "classpath:jdbc.properties")
public class DataConfig {@Value("${msg1}")private String driverClass;@Value("${msg2}")private String jdbcUrl;@Value("${msg3}")private String user;@Value("${msg4}")private String password;@Beanpublic DataSource datasourse(){try {ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();comboPooledDataSource.setDriverClass(driverClass);comboPooledDataSource.setJdbcUrl(jdbcUrl);comboPooledDataSource.setUser(user);comboPooledDataSource.setPassword(password);return comboPooledDataSource;} catch (PropertyVetoException e) {e.printStackTrace();}return null;}@Beanpublic QueryRunner queryRunner(){return new QueryRunner(datasourse());}
}

@Configuration
@ComponentScan(basePackages = "com.apesourse")
@Import(DataConfig.class)
public class applicationConfig {
}

2.配置文件只有jdbc.properties，数据库的driver，URL，user，password

3.控制层、业务层、数据访问层的代码同上（使用注解完成）