适合初學者-sprungboot+mybatis實作增删改查

springboot初識

（适合初學）springboot+mybatis實作增删改查

前言

開發工具及版本：

window10

idea-2018.2.2

maven-3.54

navicat

效果圖

目錄結構

springboot架構簡介

資料庫

demo資料庫，表名users

pom.xml

通過Maven導入，不知道怎麼配置maven可以自行百度

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>mybits</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybits</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
        <!-- freemarker模闆引擎視圖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>

        <!-- 熱部署，不用重新開機 ，這個在這裡不需要-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>

        <!-- jsp解析器 -->
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
           

對于一個項目，從發起到響應流程

controller層

Controller層：控制層 控制業務邏輯

具體的業務子產品流程的控制，controller層主要調用Service層裡面的接口控制具體的業務流程，控制的配置也要在配置檔案中進行。

package com.example.mybits.Controller;

import com.example.mybits.Model.User;
import com.example.mybits.Service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;

@Controller
//@RestController
public class UserController {

    @Autowired
    private UserService userService;

    //查詢所有
    @RequestMapping("/selectUserAll")
    public ModelAndView selectUserAll(){
        ModelAndView mav = new ModelAndView("selectUserAll");
        mav.addObject("user", userService.selectUserAll());
        return mav;
    }

    //根據id查詢使用者
    @RequestMapping("/selectUserById")
    public ModelAndView selectUserById(Integer id){
        ModelAndView mav = new ModelAndView("selectUserById");
        mav.addObject("user",userService.selectUserById(id));
        return mav;

    }

    //根據number查詢使用者
    @RequestMapping("/selectUserByNumber")
    public ModelAndView selectUserByNumber(String number){
        ModelAndView mav = new ModelAndView("selectUserByNumber");
        mav.addObject("user",userService.selectUserByNumber(number));
        return mav;

    }

    //根據工号查詢使用者（name）
    @RequestMapping("/selectUserByName")
    public ModelAndView selectUserByName(String name){
        ModelAndView mav = new ModelAndView("selectUserByName");
        mav.addObject("user",userService.selectUserByName(name));
        return mav;

    }

    //根據id删除使用者
    @RequestMapping("/deleteUserById")
    public String deleteUser(Integer id){
        userService.deleteUser(id);
        return "redirect:selectUserAll";
    }

    //添加使用者
    @RequestMapping("/addUser")
    public String addUser(User user){
        userService.addUser(user);
        return "redirect:selectUserAll";
    }


    @RequestMapping("/add")
    public ModelAndView addController(User user){
        ModelAndView mav = new ModelAndView();
        mav.setViewName("add");
        return mav;
    }

    //通過id顯示修改内容
    @RequestMapping("/selectOneById")
    public ModelAndView selectOneById(int id){
        ModelAndView mav = new ModelAndView("update");
        mav.addObject("user", userService.selectOneById(id));
        return mav;
    }


    //根據id修改使用者
    @RequestMapping("/updateUserById")
    public String updateUserById(User user){
        userService.updateUser(user);
        return "redirect:selectUserAll";
    }

}

           

dao層（持久層）

Dao層：持久層，主要是和資料庫進行互動

dao層首先胡建立dao接口，接着就可以在配置檔案中定義該接口的實作類；接着就可以在子產品中調用dao的接口進行資料業務的處理，而不用關注此接口的具體實作是哪一個類，dao層的資料源和資料庫實在配置檔案中進行配置的。

UserDao接口：

package com.example.mybits.Dao;

import com.example.mybits.Model.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

//@Repository
@Mapper
public interface UserDao {
    void addUser(User user);
    void deleteUser(Integer id);
    void updateUser(User user);
    List<User> selectUserAll();
    List<User> selectUserById(int id);
    User selectOneById(Integer id);

    List<User> selectUserByNumber(String number);
    List<User> selectUserByName(String name);
}

           

Mapper——userMapper.xml

<?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.example.mybits.Dao.UserDao">
    <sql id="table">users</sql>
    <resultMap id="BaseResultMap" type="com.example.mybits.Model.User" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="number" property="number" jdbcType="VARCHAR" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="local" property="local" jdbcType="VARCHAR" />
        <result column="ip" property="ip" jdbcType="VARCHAR" />
        <result column="note" property="note" jdbcType="VARCHAR" />
    </resultMap>

    <insert id="addUser" parameterType="com.example.mybits.Model.User" useGeneratedKeys="true" keyProperty="id">
        insert into users (id,number,name,local,ip,note) values (#{id}, #{number}, #{name}, #{local}, #{ip}, #{note})
    </insert>

    <delete id="deleteUser" parameterType="com.example.mybits.Model.User">
        delete from  users where id=#{id}
    </delete>

    <select id="selectUserAll" resultType="com.example.mybits.Model.User">
        select * from users
    </select>

    <select id="selectUserById" resultType="com.example.mybits.Model.User">
        select * from users where id like concat(concat('%',#{id}),'%');
    </select>


    <select id="selectOneById" resultType="com.example.mybits.Model.User">
        select * from users where id like concat(concat('%',#{id}),'%');
    </select>

    <select id="selectUserByNumber" resultType="com.example.mybits.Model.User">
        select * from users where number like concat(concat('%',#{number}),'%');
    </select>

    <select id="selectUserByName" resultType="com.example.mybits.Model.User">
        select * from users where name like concat(concat('%',#{name}),'%');
    </select>

    <update id="updateUser" parameterType="com.example.mybits.Model.User">
        update users set number=#{number}, name=#{name}, local=#{local}, ip=#{ip}, note=#{note} where id=#{id}
    </update>
</mapper>
           

Model層

Mode層也稱為po層和Entity層：實體層，資料庫在項目中的類

主要用于定義與資料庫對象的屬性，提供get/set方法，帶參和無參的構造方法。

User：

package com.example.mybits.Model;


public class User {

  private Integer id;
  private String number;
  private String name;
  private String local;
  private String ip;
  private String note;


  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }


  public String getNumber() {
    return number;
  }

  public void setNumber(String number) {
    this.number = number;
  }


  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }


  public String getLocal() {
    return local;
  }

  public void setLocal(String local) {
    this.local = local;
  }


  public String getIp() {
    return ip;
  }

  public void setIp(String ip) {
    this.ip = ip;
  }


  public String getNote() {
    return note;
  }

  public void setNote(String note) {
    this.note = note;
  }

}

           

Service層

Service層：業務層控制業務

業務子產品的邏輯應用設計，和DAO層一樣都是先設計接口，再建立要實作的類，然後在配置檔案中進行配置其實作的關聯。接下來就可以在service層調用接口進行業務邏輯應用的處理。

好處：封裝Service層的業務邏輯有利于業務邏輯的獨立性和重複利用性。

UserService

package com.example.mybits.Service;

import com.example.mybits.Dao.UserDao;
import com.example.mybits.Model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

    @Autowired
    UserDao userDao;

    public void addUser(User user){
        userDao.addUser(user);
    }
    public void deleteUser(Integer id){
        userDao.deleteUser(id);
    }
    public void updateUser(User user){
        userDao.updateUser(user);
    }

    public List<User> selectUserAll(){
        return userDao.selectUserAll();
    }

    public List<User> selectUserById(int id){
        return userDao.selectUserById(id);
    }
    public User selectOneById(Integer id){
        return userDao.selectOneById(id);
    }

    /**通過名字查詢記錄**/
    public List<User> selectUserByNumber(String number){
        return userDao.selectUserByNumber(new String(number));
    }

    /**通過職稱查詢記錄**/
    public List<User> selectUserByName(String name) {
        return userDao.selectUserByName(new String(name));
    }
}

           

MybitsApplication

package com.example.mybits;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;


@MapperScan("com.example.mybits.Dao")
@SpringBootApplication
public class MybitsApplication extends SpringBootServletInitializer {

    public static void main(String[] args) {
        SpringApplication.run(MybitsApplication.class, args);
    }

    protected SpringApplicationBuilder config(SpringApplicationBuilder applicationBuilder){
        return applicationBuilder.sources(MybitsApplication.class);
    }
}

           

application.yml

application為配置檔案，用 配置檔案去覆寫 SpringBoot 的預設配置，可以在resources 檔案夾下建立 application.yml檔案，這裡就以手動配置 Tomcat 端口去覆寫SpringBoot 預設配置為例，我們知道 Tomcat 預設打開端口是8080。

server:port: 8089

workname: sy
field: 111

spring:
  datasource:
    url: jdbc:mysql://localhost/demo?serverTimezone=GMT%2B8&characterEncoding=utf8&allowMultiQueries=true
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

  mvc:
    view:
      prefix: /WEB-INF/jsp/
      suffix: .jsp

mybatis:
  mapper-locations: classpath:Mapper/*.xml
  type-aliases-package: com.example.mybits.Model


           

前端jsp頁面（首頁）

selectUserAll

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<head>

</head>

<body>

<div>
    <form action="selectUserById"    method="get">
        <div align='center'><input type="text" name="id"><button type="submit">按照工号搜尋</button>

                <form action="selectUserByNumber"  method="get"><div align='center'><input type="text" name="number"><button type="submit">按照姓名搜尋</button></div></form>
                <form action="selectUserByName"  method="get"><div align='center'><input type="text" name="name"><button type="submit">按照職稱搜尋</button></div></form>
            <a href="add"><button type="button">新增</button></a>
        </div>
    </form>
</div>


<table align='center' border='1' cellspacing='0'>

    <thead>
    <tr>
        <th>工号</th>
        <th>姓名</th>
        <th>職稱</th>
        <th>行政區</th>
        <th>IP</th>
        <th>備注</th>
        <th>操作</th>
    </tr>
    </thead>
    <tbody>
    <c:forEach items="${user}" var="user">
        <tr>
            <td>${user.id}</td>
            <td>${user.number}</td>
            <td>${user.name}</td>
            <td>${user.local}</td>
            <td>${user.ip}</td>
            <td>${user.note}</td>
            <td><a href="deleteUserById?id=${user.id}"><button>删除</button></a>
            <a href="selectOneById?id=${user.id}"><button>修改</button></a>
            </td>
        </tr>
    </c:forEach>
    </tbody>
</table>
</body></html>
           

增加頁面

add.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<head>

</head>
<body>

    <div id="section">
        <form action="addUser" method="post">
            <table class="tab" align='center' border='1' cellspacing="0" cellpadding="0" width="500" height="200">
                <tr>
                    <td>姓名:</td>
                    <td><input type="text" name="number" value="${user.number}"></td>
                </tr>
                <tr>
                    <td>職稱:</td>
                    <td><input type="text" name="name" value="${user.name}"></td>
                </tr>
                <tr>
                    <td>行政區:</td>
                    <td><input type="text" name="local" value="${user.local}"></td>
                </tr>
                <tr>
                    <td>IP:</td>
                    <td><input type="text" name="ip" value="${user.ip}"></td>
                </tr>
                <tr>
                    <td>備注:</td>
                    <td><input type="text" name="note" value="${user.note}"></td>
                </tr>
                <tr>
                    <td></td>
                    <td><input type="submit" value="确認新增" ></td>
                    <a href="javascript:history.go(-1)"><button type="button">傳回</button></a>
                </tr>
            </table>
        </form>
    </div>



</body>
</html>


           

查詢頁面

selectUserById(通過id查詢)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<head>

</head>
<body>
<%--<div align='center'><a href="javascript:history.go(-1)"><button>Back</button></a></div>--%>
<table align='center' border='1' cellspacing='0'>

    <thead>
    <tr>
        <th>工号</th>
        <th>姓名</th>
        <th>職稱</th>
        <th>行政區</th>
        <th>IP</th>
        <th>備注</th>
        <th>操作</th>
    </tr>
    </thead>
    <tbody>
    <c:forEach items="${user}" var="user">
        <tr>
            <td>${user.id}</td>
            <td>${user.number}</td>
            <td>${user.name}</td>
            <td>${user.local}</td>
            <td>${user.ip}</td>
            <td>${user.note}</td>
            <td><a href="deleteUserById?id=${user.id}"><button>删除</button></a>
                <a href="selectOneById?id=${user.id}"><button>修改</button></a>
            </td>
        </tr>
    </c:forEach>
    </tbody>
    <a href="javascript:history.go(-1)"><button type="button">傳回</button></a></td>
</table>
</body>
</html>
           

selectUserByName（通過name查詢）

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2021/6/15
  Time: 11:12
  To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<head>

</head>
<body>
<%--<div align='center'><a href="javascript:history.go(-1)"><button>Back</button></a></div>--%>
<table align='center' border='1' cellspacing='0'>

    <thead>
    <tr>
        <th>工号</th>
        <th>姓名</th>
        <th>職稱</th>
        <th>行政區</th>
        <th>IP</th>
        <th>備注</th>
        <th>操作</th>
    </tr>
    </thead>
    <tbody>
    <c:forEach items="${user}" var="user">
        <tr>
            <td>${user.id}</td>
            <td>${user.number}</td>
            <td>${user.name}</td>
            <td>${user.local}</td>
            <td>${user.ip}</td>
            <td>${user.note}</td>
            <td><a href="deleteUserById?id=${user.id}"><button>删除</button></a>
                <a href="selectOneById?id=${user.id}"><button>修改</button></a>
            </td>
        </tr>
    </c:forEach>
    </tbody>
    <a href="javascript:history.go(-1)"><button type="button">傳回</button></a></td>
</table>
</body>
</html>

           

selectUserNumber(通過Number查詢)

<%--
  Created by IntelliJ IDEA.
  User: admin
  Date: 2021/6/15
  Time: 11:12
  To change this template use File | Settings | File Templates.
--%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<head>

</head>
<body>
<%--<div align='center'><a href="javascript:history.go(-1)"><button>Back</button></a></div>--%>
<table align='center' border='1' cellspacing='0'>

    <thead>
    <tr>
        <th>工号</th>
        <th>姓名</th>
        <th>職稱</th>
        <th>行政區</th>
        <th>IP</th>
        <th>備注</th>
        <th>操作</th>
    </tr>
    </thead>
    <tbody>
    <c:forEach items="${user}" var="user">
        <tr>
            <td>${user.id}</td>
            <td>${user.number}</td>
            <td>${user.name}</td>
            <td>${user.local}</td>
            <td>${user.ip}</td>
            <td>${user.note}</td>
            <td><a href="deleteUserById?id=${user.id}"><button>删除</button></a>
                <a href="selectOneById?id=${user.id}"><button>修改</button></a>
            </td>
        </tr>
    </c:forEach>
    </tbody>
    <a href="javascript:history.go(-1)"><button type="button">傳回</button></a></td>
</table>
</body>
</html>

           

修改頁面

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<head>

</head>
<body>

<div id="section">
    <form action="updateUserById" method="post">
        <table class="tab" align='center' border='1' cellspacing="0" cellpadding="0" width="600" height="200">
            <tr>
                <td>工号:</td>
                <td><input type="text" name="id" value="${user.id}"></td>

            </tr>
            <tr>
                <td>姓名:</td>
                <td><input type="text" name="number" value="${user.number}"></td>
            </tr>
            <tr>
                <td>職稱:</td>
                <td><input type="text" name="name" value="${user.name}"></td>
            </tr>
            <tr>
                <td>行政區:</td>
                <td><input type="text" name="local" value="${user.local}"></td>
            </tr>
            <tr>
                <td>IP:</td>
                <td><input type="text" name="ip" value="${user.ip}"></td>
            </tr>
            <tr>
                <td>備注:</td>
                <td><input type="text" name="note" value="${user.note}"></td>
            </tr>
            <tr>
                <td></td>
                <td><input type="submit" value="确認修改" ></td>
                <a href="javascript:history.go(-1)"><button type="button">傳回</button></a></td>
            </tr>
        </table>

    </form>


</div>



</body>
</html>
           

注意

1.項目命名中的mybatis書寫有誤，請自行修改

2.項目中表單命名，number表示名字，name表示職稱，請自行修改

後期将前端頁面用ajax 實作，并使用vue +element_ui實作增删改查