Spring-CRUD

共计 9136 个字符，预计需要花费 23 分钟才能阅读完成。

1、开发需求

1.1、需求分析

实现 spring 数据库中的管理员表（manager）的 CRUD（增加、删除、修改、查询）操作

1.2、管理员表

1.3、架构需求

Spring、Spring-ioc、DBUtils、C3P0

1.4、新建项目

新建 maven 项目 spring003，导包

<?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>com.tyschool</groupId>
    <artifactId>spring003</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.13.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>5.2.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>5.2.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>5.2.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.3</version>
        </dependency>
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.6</version>
        </dependency>
    </dependencies>
</project>

2、项目初始化 - 上

2.1、创建 applicationContext.xml 文件

2.1.1、配置数据源

<!-- 配置数据源 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!-- 连接数据库的必备信息 -->
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://192.168.1.123:3306/spring?serverTimezone=UTC"></property>
        <property name="user" value="root"></property>
        <property name="password" value="Root12345"></property>
</bean>

2.1.2、配置 QueryRunner

<!-- 配置 QueryRunner-->
<bean id="runner" class="org.apache.commons.dbutils.QueryRunner" scope="prototype">
        <!-- 注入数据源 -->
        <constructor-arg name="ds" ref="dataSource"></constructor-arg>
</bean>

注意：

QueryRunner：SQL 语句的操作对象，可以设置查询结果集的封装策略，线程安全。

2.2、创建 JavaBean

import java.io.Serializable;
import java.util.Date;
 
public class Manager implements Serializable {private int mid;
    private String uname;
    private String pword;
    private Date zcsj;
 
    public int getMid() {return mid;
    }
 
    public void setMid(int mid) {this.mid = mid;
    }
 
    public String getUname() {return uname;
    }
 
    public void setUname(String uname) {this.uname = uname;
    }
 
    public String getPword() {return pword;
    }
 
    public void setPword(String pword) {this.pword = pword;
    }
 
    public Date getZcsj() {return zcsj;
    }
 
    public void setZcsj(Date zcsj) {this.zcsj = zcsj;
    }
 
    @Override
    public String toString() {return "Manager{" +
                "mid=" + mid +
                ", uname='" + uname + '\'' +
                ", pword='" + pword + '\'' +
                ", zcsj=" + zcsj +
                '}';
    }
}

3、项目初始化 - 中

3.1、创建持久层

IManagerDao.java

import com.tyschool.spring003.javabean.Manager;
 
import java.util.List;
 
public interface IManagerDao {List<Manager> findAll();
    Manager findById(int mid);
    int addManager(Manager m);
    int updateManager(Manager m);
    int deleteManager(int mid);
    int allCount();
    List<Manager> findByUname(String uname);
}

ManagerDaoImpl.java

import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.spring003.manager.dao.IManagerDao;
 
import java.util.List;
 
public class ManagerDaoImpl implements IManagerDao {public List<Manager> findAll() {return null;
    }
 
    public Manager findById(int mid) {return null;
    }
 
    public int addManager(Manager m) {return 0;
    }
 
    public int updateManager(Manager m) {return 0;
    }
 
    public int deleteManager(int mid) {return 0;
    }
 
    public int allCount() {return 0;
    }
 
    public List<Manager> findByUname(String uname) {return null;
    }
}

3.2、创建业务层

IManagerService.java

import com.tyschool.spring003.javabean.Manager;
import java.util.List;
 
public interface IManagerService {List<Manager> findAll();
    Manager findById(int mid);
    int addManager(Manager m);
    int updateManager(Manager m);
    int deleteManager(int mid);
    int allCount();
    List<Manager> findByUname(String uname);
}

ManagerServiceImpl.java

import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.manager.service.IManagerService;
 
import java.util.List;
 
public class ManagerServiceImpl implements IManagerService {public List<Manager> findAll() {return null;
    }
 
    public Manager findById(int mid) {return null;
    }
 
    public int addManager(Manager m) {return 0;
    }
 
    public int updateManager(Manager m) {return 0;
    }
 
    public int deleteManager(int mid) {return 0;
    }
 
    public int allCount() {return 0;
    }
 
    public List<Manager> findByUname(String uname) {return null;
    }
}

3.3、配置 applicationContext.xml

<!-- 配置 DAO -->
<bean id="managerDaoImpl" class="com.tyschool.spring003.manager.dao.impl.ManagerDaoImpl" >
        <property name="runner" ref="runner"></property>
</bean>
<!-- 配置 Service -->
<bean id="managerServiceImpl" class="com.tyschool.spring003.manager.service.impl.ManagerServiceImpl">
        <property name="managerDaoImpl" ref="managerDaoImpl"></property>
</bean>

4、项目初始化 - 下

4.1、修改 ManagerServiceImpl.java 文件

import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.manager.dao.IManagerDao;
import com.tyschool.spring003.manager.dao.impl.ManagerDaoImpl;
import com.tyschool.spring003.manager.service.IManagerService;
 
import java.util.List;
 
public class ManagerServiceImpl implements IManagerService {private IManagerDao managerDaoImpl;
 
    public void setManagerDaoImpl(IManagerDao managerDaoImpl) {this.managerDaoImpl = managerDaoImpl;
    }
 
    public List<Manager> findAll() {return managerDaoImpl.findAll();}
 
    public Manager findById(int mid) {return managerDaoImpl.findById(mid);
    }
 
    public int addManager(Manager m) {return managerDaoImpl.addManager(m);
    }
 
    public int updateManager(Manager m) {return managerDaoImpl.updateManager(m);
    }
 
    public int deleteManager(int mid) {return managerDaoImpl.deleteManager(mid);
    }
 
    public int allCount() {return managerDaoImpl.allCount();}
 
    public List<Manager> findByUname(String uname) {return managerDaoImpl.findByUname(uname);
    }
}

4.2、修改 ManagerDaoImpl.java 文件

import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.manager.dao.IManagerDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
 
import java.sql.SQLException;
import java.util.List;
 
public class ManagerDaoImpl implements IManagerDao {private QueryRunner runner;
 
    public void setRunner(QueryRunner runner) {this.runner = runner;
    }
 
    public List<Manager> findAll() {List<Manager> list=null;
        try {list=runner.query("select * from manager",new BeanListHandler<Manager>(Manager.class));
        } catch (SQLException e) {e.printStackTrace();
        }
        return list;
    }
 
    public Manager findById(int mid) {Manager m=null;
        try {m=runner.query("select * from manager where mid=?",new BeanHandler<Manager>(Manager.class),mid);
        } catch (SQLException e) {e.printStackTrace();
        }
        return m;
    }
 
    public int addManager(Manager m) {try {runner.update("insert into manager(uname,pword,zcsj)values(?,?,?)",m.getUname(),m.getPword(),m.getZcsj());
        } catch (SQLException e) {e.printStackTrace();
        }
        return 0;
    }
 
    public int updateManager(Manager m) {try {runner.update("update manager set uname=?,pword=?,zcsj=? where mid=?",m.getUname(),m.getPword(),m.getZcsj(),m.getMid());
        } catch (SQLException e) {e.printStackTrace();
        }
        return 0;
    }
 
    public int deleteManager(int mid) {try {runner.update("delete from manager where mid=?",mid);
        } catch (SQLException e) {e.printStackTrace();
        }
        return 0;
    }
 
public int allCount() {int n=0;
        try {Number number=(Number)runner.query("select count(mid) from manager",new ScalarHandler());
            n=number.intValue();} catch (SQLException e) {e.printStackTrace();
        }
        return n;
}
 
    public List<Manager> findByUname(String uname) {List<Manager> list=null;
        try {list=runner.query("select * from manager where uname like ?",new BeanListHandler<Manager>(Manager.class),uname);
        } catch (SQLException e) {e.printStackTrace();
        }
        return list;
    }
}
 

5、创建测试类

5.1、spring 工厂

BeanFactory.java

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
 
public class BeanFactory {private static ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");;
    private BeanFactory(){ }
    public static Object getBean(String snameBean) {Object iss =  context.getBean(snameBean);
        return iss;
    }
}

5.2、编写测试类

import com.tyschool.spring003.javabean.Manager;
import com.tyschool.spring003.manager.service.IManagerService;
import com.tyschool.spring003.utils.BeanFactory;
import org.junit.Test;
 
import java.util.List;
 
public class STest {private IManagerService ims= (IManagerService) BeanFactory.getBean("managerServiceImpl");
    
    @Test
    public void findAll(){List<Manager> m =ims.findAll();
        for(Manager m1:m){System.out.println(m1);
        }
    }
 
}

6、测试数据库表操作 - 上

6.1、通过 ID 查询管理员信息

@Test
public void findById(){Manager m=ims.findById(1);
        System.out.println(m);
}

6.2、增加管理员信息

@Test
public void addManager(){Manager m=new Manager();
        m.setUname("lihao");
        m.setPword("lihao");
        m.setZcsj(new Date());
        ims.addManager(m);
}

6.3、修改管理员信息

@Test
public void updateManager(){Manager m=ims.findById(1);
        m.setUname("zhangniu");
        ims.updateManager(m);
}

7、测试数据库表操作 - 下

7.1、删除管理员信息

@Test
public void deleteManager(){ims.deleteManager(1);
}

7.2、查询管理员表中的记录数

@Test
public void allCount(){int n=ims.allCount();
        System.out.println(n);
}

7.3、通过管理员名字查询管理员信息

@Test
public void findByUname(){List<Manager> m=ims.findByUname("li%");
        for(Manager m1:m){System.out.println(m1);
        }
}