组团学
学习方向
登录/注册

Spring-CRUD

阅读 (839279)

1、开发需求

1.1、需求分析

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

1.2、管理员表

image20200407103938452.png

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);
        }
}
需要 登录 才可以提问哦
头像
：
回复