SpringCRUD实现账户表的增删改查(配置文件形式)

SpringCRUD实现账户表的增删改查(配置文件形式)

---

实现步骤

---

                1 独立的实体类 业务层  持久层代码

​                2 持久层技术dbutils(不用mybatis)

​                3 连接池（数据源）dbcp  c3p0

​                4 要求使用junit    测试

​                5  运用xml ioc  实现持久层和业务层解耦

具体实现

1. 注入依赖Maven导入响应的jar文件

   <?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.kgc</groupId>
     <artifactId>springCrud01</artifactId>
     <version>1.0-SNAPSHOT</version>
   
     <name>springCrud01</name>
     <!-- FIXME change it to the project's website -->
     <url>http://www.example.com</url>
   
     <properties>
       <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
       <maven.compiler.source>1.8</maven.compiler.source>
       <maven.compiler.target>1.8</maven.compiler.target>
     </properties>
   
     <dependencies>
       <dependency>
         <groupId>junit</groupId>
         <artifactId>junit</artifactId>
         <version>4.11</version>
         <scope>test</scope>
       </dependency>
       <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>5.1.6</version>
       </dependency>
       <dependency>
         <groupId>c3p0</groupId>
         <artifactId>c3p0</artifactId>
         <version>0.9.1.2</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.0.7.RELEASE</version>
       </dependency>
     </dependencies>

    

2.  数据库 创建表

   create table account
   (
       id    int auto_increment
           primary key,
       name  varchar(20) null,
       money float       null
   );
   
   INSERT INTO spring.account (id, name, money) VALUES (1, '吕蒙', 33333);
   INSERT INTO spring.account (id, name, money) VALUES (2, '马岱', 44);
   INSERT INTO spring.account (id, name, money) VALUES (3, 'shangjinghua', 29382);
   INSERT INTO spring.account (id, name, money) VALUES (4, '吕布', 34098);
   INSERT INTO spring.account (id, name, money) VALUES (9, '周瑜', 51234);
   INSERT INTO spring.account (id, name, money) VALUES (11, '马超', 235478);

    

3. 实体类

   package cn.kgc.pojo;
   
   public class Account {
       private Integer id;
       private String name;
       private Float money;
   
       public Account() {
       }
   
       public Account(Integer id, String name, Float money) {
           this.id = id;
           this.name = name;
           this.money = 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. 持久层

   package cn.kgc.dao;
   
   import cn.kgc.pojo.Account;
   
   import java.util.List;
   
   public interface AccountDao {
       //查询所有
       List<Account> findAll();
   
       //根据Id查询
       Account findById(Integer id);
   
       //曾
       void insert(Account account);
   
       //改
       void update(Account account);
   
       //删除
       void delete(Integer id);
   }
   

   package cn.kgc.dao.impl;
   
   import cn.kgc.pojo.Account;
   import cn.kgc.dao.AccountDao;
   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;//对象的赋值交给IOC容器
   
       public void setQueryRunner(QueryRunner queryRunner) {
           this.queryRunner = queryRunner;
       }
   
       @Override
       public List<Account> findAll() {
           String sql = "select * from account";
           try {
               List<Account> accountList = queryRunner.query(sql, new BeanListHandler<Account>(Account.class));
               return  accountList;
           } catch (SQLException e) {
               e.printStackTrace();
           }
           return null;
       }
   
       @Override
       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;
       }
   
       @Override
       public void insert(Account account) {
           String sql = "insert into account values(null,?,?)";
           try {
               int update = queryRunner.update(sql, account.getName(), account.getMoney());
               if (update != 0) {
                   System.out.println("插入成功");
               }
           } catch (SQLException e) {
               e.printStackTrace();
           }
   
   
       }
   
       @Override
       public void update(Account account) {
           String sql = "update account set name = ? ,money = ? where id = ?";
           try {
               int update = queryRunner.update(sql, account.getName(), account.getMoney(),account.getId());
               if (update != 0) {
                   System.out.println("修改成功");
               }
           } catch (SQLException e) {
               e.printStackTrace();
           }
   
       }
   
       @Override
       public void delete(Integer id) {
           String sql = "delete from account where id = ?";
           try {
               int update = queryRunner.update(sql, id);
               if (update != 0) {
                   System.out.println("删除成功");
               }
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
   }
   

    

5. 业务层

   package cn.kgc.service;
   
   import cn.kgc.pojo.Account;
   
   import java.util.List;
   
   public interface AccountService {
       //查询所有
       List<Account> findAll();
   
       //根据Id查询
       Account findById(Integer id);
   
       //曾
       void insert(Account account);
   
       //改
       void update(Account account);
   
       //删除
       void delete(Integer id);
   }

   package cn.kgc.service.impl;
   
   import cn.kgc.pojo.Account;
   import cn.kgc.dao.AccountDao;
   import cn.kgc.service.AccountService;
   
   import java.util.List;
   
   public class AccountServiceImpl implements AccountService {
       AccountDao accountDao;//对象的初始化由IOc容器负责,同个set方法
   
       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 insert(Account account) {
           accountDao.insert(account);
       }
   
       @Override
       public void update(Account account) {
           accountDao.update(account);
       }
   
       @Override
       public void delete(Integer id) {
           accountDao.delete(id);
       }
   }
   

    

6. 配置文件
   1. 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">
          <!--导入内部xml文件-->
          <import resource="applicationContext-dao.xml"/>
          <!--创建service对象-->
          <bean id="accountService" class="cn.kgc.service.impl.AccountServiceImpl">
              <!--service对象有个属性AccountDao,该修对象由导入的xml文件具体指向那个实体类-->
              <property name="accountDao" ref="accountDao"/>
          </bean>
      </beans>

       

   2. applicationContext-dao.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">
         <!--创建AccountDao对象,具体指向实现类-->
          <bean id="accountDao" class="cn.kgc.dao.impl.AccountDaoImpl">
              <!--该对象里属性queryRunner赋值,引用类型ref指向-->
              <property name="queryRunner" ref="queryRunner"/>
          </bean>
          <!--创建queryRunner对象-->
          <bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
              <!--为对象属性初始化-->
              <constructor-arg type="javax.sql.DataSource" ref="dataSource"/>
          </bean>
          <!--创建dataSource对象并填写配置信息-->
          <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
              <property name="driverClass" value="${jdbc.driver}"/>
              <property name="jdbcUrl" value="${jdbc.url}"/>
              <property name="user" value="${jdbc.user}"/>
              <property name="password" value="${jdbc.password}"/>
          </bean>
          <!--外界引入propertiex文件-->
          <context:property-placeholder location="classpath:jdbc.properties"/>
      </beans>

       

7. 测试

   import cn.kgc.pojo.Account;
   import cn.kgc.service.impl.AccountServiceImpl;
   import org.junit.Test;
   import org.springframework.context.support.ClassPathXmlApplicationContext;
   
   import java.util.List;
   
   public class TestSpringCrud {
       @Test
       public void testFindAll(){
   //创建IOC容器,参数指定容器的配置文件
           ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
   //通过容器拿到容器内的对象Service
           AccountServiceImpl as = ac.getBean("accountService", AccountServiceImpl.class);
   //Service对象执行方法
           List<Account> accountList = as.findAll();
           for (Account acc : accountList) {
               System.out.println(acc);
           }
       }
       @Test
       public void testFindById(){
           ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
           AccountServiceImpl as = ac.getBean("accountService", AccountServiceImpl.class);
           Account account = as.findById(2);
           System.out.println(account);
       }
       @Test
       public void testInsert(){
           ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
           AccountServiceImpl as = ac.getBean("accountService", AccountServiceImpl.class);
           Account account = new Account();
           account.setName("马超");
           account.setMoney(235478.00f);
          as.insert(account);
          testFindAll();
       }
   
       @Test
       public void testUpdate() {
           ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
           AccountServiceImpl accountService = ac.getBean("accountService", AccountServiceImpl.class);
           Account acc = new Account();
           acc.setMoney(100f);
           acc.setName("鲁肃");
           acc.setId(6);
           accountService.update(acc);
           testFindAll();
       }
       @Test
       public void testDelete(){
           ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
           AccountServiceImpl accountService = ac.getBean("accountService", AccountServiceImpl.class);
           accountService.delete(6);
           testFindAll();
       }
   }