使用SpringBoot+jpa连接MySQL实现页面增删改查

SpringBoot+jpa：

本篇文章主要给刚学习SpringBoot的同门们，一个简单的使用SpringBoot+jpa连接MySQL数据库在前端页面实现增删改查操作
，使用的一个开发工具是IDEA

---

前言

这里只是做了一个简单的增删改查功能，如何有心的初学者同胞们可以在
我的源码上做一些分页和对类型的一些添增等各种操作，在我的基础上学下来也可以进行一个借鉴，望对各位同胞们有所帮助，谢谢！

一、创建数据库、并给出数据表与数据类型表

1.数据表
2.数据类型表

二、使用IDEA创建项目

后端代码

一：先选择项目所需要的配置

二：先准备配置文件，检查是否有需求加入的配置（pom.xml）

<?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.1.16.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.uncletj</groupId>
    <artifactId>bookwork</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>bookwork</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-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <dependency>
            <groupId>nz.net.ultraq.thymeleaf</groupId>
            <artifactId>thymeleaf-layout-dialect</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.18</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

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

三：编写application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/billsinfo?useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.jpa.properties.hibernate.dialect =org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.thymeleaf.cache=false

四：然后创建框架所需一些包名：entity、repository，service、controler
（1.entity）
1.1：Bills

package com.uncletj.bookwork.entity;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Getter;
import lombok.Setter;
import org.springframework.format.annotation.DateTimeFormat;
import javax.persistence.*;
import java.io.Serializable;
import java.util.Date;

@Entity
@Getter
@Setter
@Table(name = "bills")
public class Bills implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "title")
    private String title;

    @Column(name = "bill_time")
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date billTime;

    @ManyToOne(targetEntity = BillsType.class)
    @JoinColumn(name ="type_id")
    @JsonIgnore
    private BillsType billsType;

    @Column(name = "price")
    private String price;

    @Column(name = "explai")
    private String explai;
}

1.2：BillsType

package com.uncletj.bookwork.entity;
import lombok.Data;
import javax.persistence.*;

@Entity
@Data
@Table(name = "bill_type")
public class BillsType {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;
}

(2.repository)
2.1：BillsRepository

package com.uncletj.bookwork.repository;
import com.uncletj.bookwork.entity.Bills;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface BillsRepository extends JpaRepository<Bills,Long>, JpaSpecificationExecutor<Bills> {
}

2.2:BillsTypeRepository

package com.uncletj.bookwork.repository;
import com.uncletj.bookwork.entity.BillsType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface BillsTypeRepository extends JpaRepository<BillsType,Long>, JpaSpecificationExecutor<BillsType> {
}

（:3.service）
3.1：BillsService

package com.uncletj.bookwork.service;
import com.uncletj.bookwork.entity.Bills;
import java.util.List;

public interface BillsService {
    public boolean saveOrUpdate(Bills bills);
    public Bills getById(Long id);
    public boolean delete(Long id);
    public Bills getBills(Long id);
    public List<Bills>getAll();
}

3.2.1：impl层

package com.uncletj.bookwork.service.impl;
import com.uncletj.bookwork.entity.Bills;
import com.uncletj.bookwork.repository.BillsRepository;
import com.uncletj.bookwork.service.BillsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BillsServiceImpl implements BillsService {

    @Autowired
    private BillsRepository billsRepository;

    @Override
    public boolean saveOrUpdate(Bills bills) {
        try {
            billsRepository.save(bills);
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return  false;
        }
    }

    @Override
    public Bills getById(Long id) {
      return billsRepository.findById(id).get();
    }

    @Override
    public boolean delete(Long id) {
        try {
            billsRepository.deleteById(id);
            return true;
        }catch (Exception e){
            e.printStackTrace();
            return  false;
        }
    }

    @Override
    public Bills getBills(Long id) {
        return billsRepository.getOne(id);
    }

    @Override
    public List<Bills> getAll() {
        return billsRepository.findAll();
    }
}

2.2：BillTypeService

package com.uncletj.bookwork.service;
import com.uncletj.bookwork.entity.BillsType;
import java.util.List;

public interface BillTypeService {
    public BillsType getById(Long id);
    public List<BillsType>getAll();
}

3.2.1：impl层

package com.uncletj.bookwork.service.impl;
import com.uncletj.bookwork.entity.BillsType;
import com.uncletj.bookwork.repository.BillsTypeRepository;
import com.uncletj.bookwork.service.BillTypeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BillsTypeServiceImpl implements BillTypeService {

    @Autowired
    private BillsTypeRepository billsTypeRepository;
    
    @Override
    public BillsType getById(Long id) {
        return billsTypeRepository.findById(id).get();
    }

    @Override
    public List<BillsType> getAll() {
        return billsTypeRepository.findAll();
    }
}

（4.Controller）
4.1：BillsController（主页面）

package com.uncletj.bookwork.controller;

import com.uncletj.bookwork.entity.Bills;
import com.uncletj.bookwork.service.BillsService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
public class BillsController {

    @Resource
    private BillsService billsService;

    @RequestMapping("/index.html")
    public String index(Model model){
        List<Bills>bills =billsService.getAll();
        model.addAttribute("bills",bills);
        return "index";
    }

    @RequestMapping("/del")
    @ResponseBody
    public Map getDel(Long id){
        boolean del =billsService.delete(id);
        Map map = new HashMap();
        if (del){
            map.put("ok",true);
        }else{
            map.put("ok",false);
        }
        return map;
    }
}

4.2：AddController（添加页面）

package com.uncletj.bookwork.controller;
import com.uncletj.bookwork.entity.Bills;
import com.uncletj.bookwork.entity.BillsType;
import com.uncletj.bookwork.service.BillTypeService;
import com.uncletj.bookwork.service.BillsService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
import java.util.List;

@Controller
public class AddController {

    @Resource
    private BillsService billsService;

    @Resource
    private BillTypeService billTypeService;

    @RequestMapping("/add.html")
    public String addList(Model model){
        List<BillsType>billsTypes =billTypeService.getAll();
        model.addAttribute("billsTypes",billsTypes);
        return "add";
    }

    @PostMapping("/save.html")
    public String save(Bills bills,Model model){
        boolean save =billsService.saveOrUpdate(bills);
        if (save){
            return "redirect:index.html";
        }else{
            List<BillsType>types =billTypeService.getAll();
            model.addAttribute("types",types);
            model.addAttribute("msg","添加失败");
            return "add";
        }
    }
}

4.3：AnendController（修改页面）

package com.uncletj.bookwork.controller;
import com.uncletj.bookwork.entity.Bills;
import com.uncletj.bookwork.entity.BillsType;
import com.uncletj.bookwork.service.BillTypeService;
import com.uncletj.bookwork.service.BillsService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
import java.util.List;

@Controller
public class AnendController {

    @Resource
    private BillsService billsService;

    @Resource
    private BillTypeService billsTypeService;

    @RequestMapping("/amend.html")
    public String amend(Long id, Model model){
        Bills bills1 = billsService.getById(id);
        List<BillsType> billsTypes = billsTypeService.getAll();
        model.addAttribute("bills1",bills1);
        model.addAttribute("bills2",billsTypes);
        return "amend";
    }
    @PostMapping("/amend.html")
    public String amendUpdate(Bills bills,Model model){
        boolean update =billsService.saveOrUpdate(bills);
        if (update){
            return "redirect:index.html";
        }else{
            List<BillsType>types =billsTypeService.getAll();
            model.addAttribute("types",types);
            model.addAttribute("msg","修改失败");
            return "emand";
        }
    }
}

前端页面

（1）主页面（index.html）

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org"
      xmlns:layout="http://www.ultraq.net.nz/thymeleaf/layout">
<head>
    <script src="../static/jquery/jquery-1.8.3.js" th:src="@{/jqeury/jquery-1.8.3.js}" ></script>

    <meta charset="UTF-8">
    <title>Title</title>
    <style type="text/css">
        h2{
            font-family: 楷书;
        }
        table{
            line-height: 40px;
            width: 800px;
        }
        #save{
            margin-top: 30px;
            text-align: center;
        }
    </style>
</head>
<body>
<div>
    <h2 align="center">记账管理</h2>
    <table align="center"border="1" cellpadding="0" cellspacing="0">
        <tr>
            <th>编号</th>
            <th>标题</th>
            <th>记账时间</th>
            <th>类别</th>
            <th>金额</th>
            <th>说明</th>
            <th>操作</th>
        </tr>
        <tr th:each="item : ${bills}" th:id="${item.id}">
            <td th:text="${item.id}">${p.id}</td>
            <td th:text="${item.title}">${p.title}</td>
            <td th:text="${item.billTime}">${p.bill_time}</td>
            <td th:text="${item.getBillsType().getName()}">${p.typeid}</td>
            <td th:text="${item.price}">${p.price}</td>
            <td th:text="${item.explai}">${p.explan}</td>
            <td>
                <a th:href="@{/amend.html(id=${item.id})}" href="/amend.html?id=1">修改</a>
                <a href="javascript:;" th:onclick="|del(${item.id})|">删除</a>
            </td>
        </tr>
    </table>
    <table align="center" id="save">
        <tr>
            <td>
                <a th:href="@{/add.html}" href="amend.html">添加数据</a>
            </td>
        </tr>
    </table>
</div>

<script layout:fragment="js" type="text/javascript" >
    function del(id){
        if(confirm("确定要删除该条信息吗？")){
            $.ajax({
                url : "/del",
                type : "DELETE",
                data : {id: id},
                dataType: "json",
                success:function(data){
                    if(data.ok==true){
                        alert("删除成功");
                        $("#"+id).remove();
                    }else{
                        alert("删除失败");
                    }
                }
            })
        }
    }
</script>
</body>
</html>

（2：添加页面）

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org"
      xmlns:layout="http://www.ultraq.net.nz/thymeleaf/layout">
<head>
    <meta charset="UTF-8">
    <title>添加数据</title>
</head>
<body>
<div>
<h2 align="center">添加记录</h2>
<form method="post" action="/save.html" th:action="@{/save.html}" th:method="post">
    <table align="center"border="1" cellpadding="0" cellspacing="0" width="300">
        <tr>
            <th>标题:</th>
            <th><input type="text" name="title"/></th>
        </tr>
        <tr>
            <th>记账时间:</th>
            <th><input type="date" name="billTime"/></th>
        </tr>
        <tr>
            <th>类别:</th>
            <th>
                <select name="billsType.id" id="chcDueId" class="form-control"  required="required" >
                    <option th:each="bll:${billsTypes}" th:value="${bll.id}" th:text="${bll.name}"></option>
                </select>
            </th>
        </tr>
        <tr>
            <th>金额:</th>
            <th><input type="text" name="price"/></th>
        </tr>
        <tr>
            <th>说明:</th>
            <th><input type="text" name="explai"/></th>
        </tr>
        <tr>
            <th colspan="2">
                <input type="submit" value="提交" id="save">
                <input type="button" value="取消" id="back">
            </th>
        </tr>
    </table>
</form>
</div>
<script type="text/javascript">
    $(function () {
        $("#back").on("click",function () {
            window.history.back();
        })
    })
</script>
</body>
</html>

（3：修改页面）

<!DOCTYPE html>
<html lang="en"  xmlns:th="http://www.thymeleaf.org"
      xmlns:layout="http://www.ultraq.net.nz/thymeleaf/layout">
<head>
    <meta charset="UTF-8">
    <title>修改记账</title>
</head>
<body>
<h2 align="center">修改账单</h2>
<form method="post" action="/index.html" th:action="@{/amend.html}" th:method="post">
    <input type="hidden" name="id" th:value="${bills1.id}">
<table align="center"border="1" cellpadding="0" cellspacing="0" width="300">
    <tr>
        <th>标题:</th>
        <th><input type="text" name="title" th:value="${bills1.title}"/></th>
    </tr>
    <tr>
        <th>记账时间:</th>
        <th><input type="date" name="billTime" th:value="${bills1.billTime}"/></th>
    </tr>
    <tr>
        <th>类别:</th>
        <th>
            <select name="billsType.id" >
                <option th:each="bll:${bills2}" th:value="${bll.id}" th:text="${bll.name}" th:selected="${bills1.billsType.id==bll.id}"></option>
            </select>
        </th>
    </tr>
    <tr>
        <th>金额:</th>
        <th><input type="text" name="price" th:value="${bills1.price}"/></th>
    </tr>
    <tr>
        <th>说明:</th>
        <th><input type="text" name="explai" th:value="${bills1.explai}"/></th>
    </tr>
    <tr>
        <th colspan="2">
            <input type="submit" value="提交">
            <input type="button" value="取消">
        </th>
    </tr>
</table>
</form>
</body>
</html>

效果图

学习内容：

—SpringBoot+jpa
说明：SpringBoot+jpa的应用一般在于中小型项目中，主要一个好处在于它不需求编写sql语句，给开发者的效率也会提高很多，但是缺点是你不能控制sql，所以当数据库中表多的时候不建议使用

开发时间：

开：2020：08 ：23 13 : 00
终：2020: 08 ：23 14 ：00

目的：

学习使用SpringBoot+jpa在中小型项目中进行一个应用

总结技术点

一：SpringBoot+jpa
二：MySQL
开发工具：IDEA
使用jdk版本为：jdk1.8