SpringMVC+MyBatis实现单表增删改查

SpringMVC+MyBatis实现单表增删改查

一. 创建Maven项目

Maven项目的创建及SpringMVC的配置：https://blog.csdn.net/qq_46062119/article/details/120373122?spm=1001.2014.3001.5501

加载完Maven之后，在pom.xml中<dependencies></dependencies>里引入所需依赖：
注：由于 Maven 的传递性，我们不必将所有需要的包全部配置依赖，而是配置最顶端的依赖，其他靠传递性导入。

	<!-- SpringMVC -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>5.3.1</version>
    </dependency>

    <!-- 日志 -->
    <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-classic</artifactId>
      <version>1.2.3</version>
    </dependency>

    <!-- ServletAPI -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
      <scope>provided</scope>
    </dependency>

    <!-- Spring5和Thymeleaf整合包 -->
    <dependency>
      <groupId>org.thymeleaf</groupId>
      <artifactId>thymeleaf-spring5</artifactId>
      <version>3.0.12.RELEASE</version>
    </dependency>

    <!-- mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.6</version>
    </dependency>
    <!-- mysql -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>

二.web.xml基础配置

打开web.xml，配置编码过滤器及SpringMVC前端控制器：

	<!-- 配置编码过滤器，解决中文乱码问题 -->
  <filter>
    <filter-name>CharacterEncodingFilter</filter-name>
    <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
    <init-param>
      <param-name>encoding</param-name>
      <param-value>UTF-8</param-value>
    </init-param>
    <init-param>
      <param-name>forceResponseEncoding</param-name>
      <param-value>true</param-value>
    </init-param>
  </filter>
  <filter-mapping>
    <filter-name>CharacterEncodingFilter</filter-name>
    <url-pattern>/*</url-pattern>
  </filter-mapping>

  <!-- 配置SpringMVC的前端控制器，对浏览器发送的请求统一进行处理 -->
  <servlet>
    <servlet-name>springMVC</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <!-- 通过初始化参数指定SpringMVC配置文件的位置和名称 -->
    <init-param>
      <!-- contextConfigLocation为固定值 -->
      <param-name>contextConfigLocation</param-name>
      <!-- 使用classpath:表示从类路径查找配置文件，例如maven工程中的src/main/resources -->
      <param-value>classpath:springMVC.xml</param-value>
    </init-param>
    <!--
 		作为框架的核心组件，在启动过程中有大量的初始化操作要做
		而这些操作放在第一次请求时才执行会严重影响访问速度
		因此需要通过此标签将启动控制DispatcherServlet的初始化时间提前到服务器启动时
	-->
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>springMVC</servlet-name>
    <!--
        设置springMVC的核心控制器所能处理的请求的请求路径
        /所匹配的请求可以是/login或.html或.js或.css方式的请求路径
        但是/不能匹配.jsp请求路径的请求
    -->
    <url-pattern>/</url-pattern>
  </servlet-mapping>

三.搭建MyBatis框架

1. mybatis框架需与数据库关联，所以在搭建框架之前，需先创建数据库：

create database db_springmvc

-- 创建tb_employee表
create table tb_employee
(
  id int(11) primary key AUTO_INCREMENT,  -- 主键id
  eno varchar(11) NULL,                   -- 员工编号
  name varchar(55) NULL,                  -- 员工姓名
  gender varchar(2) NULL,                 -- 员工性别
  age varchar(12) NULL,                   -- 员工年龄
  email varchar(255) NULL                 -- 员工邮箱
)

2. 在resources文件下新建一个properties文件（dbconfig.properties）建立数据库连接：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_springmvc?characterEncoding=UTF-8
jdbc.username=root
jdbc.password=

3. 在resources新建一个xml文件（mybatis-config.xml）配置mybatis：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <properties resource="dbconfig.properties"></properties>

    <!-- 驼峰命名 -->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <environments default="mysql_developer">
        <environment id="mysql_developer">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
            	<!-- 数据库连接相关配置 ,这里动态获取dbconfig.properties文件中的内容-->
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!-- sql映射文件路径 -->
        <mapper resource="com.jfeng.springmvc_mybatis.dao/EmployeeMapper.xml"/>
    </mappers>
</configuration>

四.创建所需要的相关文件

①：实体类，属性需与数据库字段对应；
②：控制类，用于方法实现与页面跳转；
③：接口，用于编写与所需实现的SQL相关的方法；
④：服务类，用于实现接口方法；
⑤：工具类，用于获取sqlSession实例；
⑥：SQL映射文件，需与接口全类名相同；
⑦：用于html页面存放，文件名需与SpringMVC中的配置对应

编写mapper接口：

package com.jfeng.springmvc_mybatis.dao;

import com.jfeng.springmvc_mybatis.bean.Employee;

import java.util.List;

public interface EmployeeMapper {

    //查询所有员工信息
    public List<Employee> query();

    //根据员工id查询员工信息
    public Employee getEmpById(Integer id);

    //添加员工
    public void add(Employee employee);

    //修改员工信息
    public void edit(Employee employee);

    //删除员工
    public void del(Integer id);

}

编写SQL映射文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jfeng.springmvc_mybatis.dao.EmployeeMapper">

    <!--public List<Employee> query(); -->
    <select id="query" resultType="com.jfeng.springmvc_mybatis.bean.Employee">
        select * from tb_employee
    </select>

    <!-- public Employee getEmpById(Integer id); -->
    <select id="getEmpById" resultType="com.jfeng.springmvc_mybatis.bean.Employee">
        select * from tb_employee where id = #{id}
    </select>

    <!-- public void add(Employee employee); -->
    <insert id="add" useGeneratedKeys="true" keyProperty="id">
        insert into tb_employee(eno,name,gender,age,email) value (#{eno},#{name},#{gender},#{age},#{email})
    </insert>

    <!--public Employee edit(Employee employee);-->
    <update id="edit">
        update tb_employee set eno=#{eno},name=#{name},gender=#{gender},age=#{age},email=#{email}
        where id=#{id}
    </update>

    <!--public void del(Integer id);-->
    <delete id="del">
        delete from tb_employee where id=#{id}
    </delete>
</mapper>

编写工具类，获取sqlSession实例：

package com.jfeng.springmvc_mybatis.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class DButil {

    public SqlSession getopenSession() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSession = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession openSession = sqlSession.openSession();
        return openSession;
    }
}

编写service，实现mapper接口方法：

package com.jfeng.springmvc_mybatis.service;

import com.jfeng.springmvc_mybatis.bean.Employee;
import com.jfeng.springmvc_mybatis.dao.EmployeeMapper;
import com.jfeng.springmvc_mybatis.util.DButil;
import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EmployeeService extends DButil {

    public List<Employee> selectAll() throws Exception {
        SqlSession session = getopenSession();
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            List<Employee> employees = mapper.query();
            return employees;
        } finally {
            session.close();
        }
    }

    public void del(Integer id) throws Exception {
        SqlSession session = getopenSession();
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            mapper.del(id);
            session.commit();
        } finally {
            session.close();
        }
    }

    public void add(Employee employee) throws Exception {
        SqlSession session = getopenSession();
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            mapper.add(employee);
            session.commit();
        } finally {
            session.close();
        }
    }

    public void edit(Employee employee) throws Exception {
        SqlSession session = getopenSession();
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            mapper.edit(employee);
            session.commit();
        } finally {
            session.close();
        }
    }

    public Employee getEmpById(Integer id) throws Exception {
        SqlSession session = getopenSession();
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            return mapper.getEmpById(id);
        } finally {
            session.close();
        }
    }
}

新建前台页面：

编写index.html页面：

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <a th:href="@{/queryAll}">全部员工信息</a>
</body>
</html>

编写showAll.html页面：

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>员工列表</title>
</head>
<body>
    <table border="1" cellspacing="0" cellpadding="0" style="text-align: center">
        <tr >
            <td colspan="6" style="padding-left: 550px;">
                <button th:align="left"><a th:href="@{/insert}">添加</a></button>
            </td>
        </tr>
        <tr>
            <td width="120px">#</td>
            <td width="120px">姓名</td>
            <td width="120px">性别</td>
            <td width="120px">年龄</td>
            <td width="120px">邮箱</td>
            <td width="120px">操作</td>
        </tr>
        <tr th:each="emps:${getemps}">
            <td><span th:text="${emps.eno}"></span></td>
            <td><span th:text="${emps.name}"></span></td>
            <td><span th:text="${emps.gender}==1?'男':'女'"></span></td>
            <td><span th:text="${emps.age}"></span></td>
            <td><span th:text="${emps.email}"></span></td>
            <td>
                <button><a th:href="@{/getEmpById(id=${emps.id})}">修改</a></button>
                <button><a th:href="@{/del(id=${emps.id})}">删除</a></button>
            </td>
        </tr>
    </table>

</body>
</html>

编写addEmps.html页面:

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
  <form th:action="@{/addemps}" method="post">
    <table border="1">
      <tr>
        <td>编号：</td>
        <td><input type="text" name="eno" placeholder="输入编号"></td>
      </tr>
      <tr>
        <td>姓名：</td>
        <td><input type="text" name="name" placeholder="输入姓名"></td>
      </tr>
      <tr>
        <td>性别：</td>
        <td>
          <input type="radio" name="gender" value="1">男&nbsp;&nbsp;
          <input type="radio" name="gender" value="0">女
        </td>
      </tr>
      <tr>
        <td>年龄：</td>
        <td><input type="text" name="age" placeholder="输入年龄"></td>
      </tr>
      <tr>
        <td>邮箱：</td>
        <td><input type="text" name="email" placeholder="输入邮箱"></td>
      </tr>
      <tr>
        <td colspan="2" align="center">
          <input type="submit" value="提交">
        </td>
      </tr>
    </table>
  </form>
</body>
</html>

编写updateEmp.xml页面：

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
  <meta charset="UTF-8">
  <title>Title</title>
</head>
<body>
  <form th:action="@{/edit}" method="post">
    <table border="1">
      <input type="hidden" name="id" th:value="${getEmp.id}">
      <tr>
        <td>编号：</td>
        <td><input type="text" name="eno" th:value="${getEmp.eno}"></td>
      </tr>
      <tr>
        <td>姓名：</td>
        <td><input type="text" name="name" th:value="${getEmp.name}"></td>
      </tr>
      <tr>
        <td>性别：</td>
        <td>
          <input type="radio" name="gender" value="1" th:checked="${getEmp.gender} eq '1'">男&nbsp;&nbsp;
          <input type="radio" name="gender" value="0" th:checked="${getEmp.gender} eq '0'">女
        </td>
      </tr>
      <tr>
        <td>年龄：</td>
        <td><input type="text" name="age" th:value="${getEmp.age}"></td>
      </tr>
      <tr>
        <td>邮箱：</td>
        <td><input type="text" name="email" th:value="${getEmp.email}"></td>
      </tr>
      <tr>
        <td colspan="2" align="center">
          <input type="submit" value="提交">
        </td>
      </tr>
    </table>
  </form>
</body>
</html>

编写Controller，实现所需功能，并进行页面跳转：

package com.jfeng.springmvc_mybatis.controller;

import com.jfeng.springmvc_mybatis.bean.Employee;
import com.jfeng.springmvc_mybatis.service.EmployeeService;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;
import java.util.Map;

@Controller
public class EmployeeController  {

    EmployeeService service = new EmployeeService();

    //实现对首页的访问，在请求控制器中创建处理请求的方法
    @RequestMapping("/")
    public String index() {
        return "index";
    }

    //实现addEmps.html添加员工页面的跳转
    @RequestMapping("/insert")
    public String insert() {
        return "addEmps";
    }

    //实现查询，并返回到前台页面showAll.html
    @RequestMapping("/queryAll")
    public String selectAll(Map map) throws Exception {
        List<Employee> emps = service.selectAll();
        map.put("getemps", emps);
        return "showAll";
    }

    //实现添加，并重定向到查询方法
    @RequestMapping("/addemps")
    public String add(Employee employee) throws Exception {
        service.add(employee);
        return "redirect:/queryAll";
    }

    //实现删除，并重定向到查询方法
    @RequestMapping("/del")
    public String del(@RequestParam(value = "id")Integer id) throws Exception {
        service.del(id);
        return "redirect:/queryAll";
    }

    //实现根据id查询，并返回到前台页面updateEmp.html
    @RequestMapping("/getEmpById")
    public String getEmpById(@RequestParam(value = "id")Integer id, Map map) throws Exception {
        Employee emp = service.getEmpById(id);
        map.put("getEmp", emp);
        return "updateEmp";
    }

    //实现修改，并重定向到查询方法
    @RequestMapping("/edit")
    public String edit(Employee employee) throws Exception {
        service.edit(employee);
        return "redirect:/queryAll";
    }
}

五.配置服务器

IDEA配置本地Tomcat服务器

六.测试

经测试，增删改查均能实现，并且数据库中也有相应改变。