springboot+jpa+thymeleaf完成数据的增删改查+多条件查询+分页

springboot+jpa+thymeleaf完成数据的增删改查+多条件查询+分页
1.创建实体类，具有外键关系。
部门实体类

@Entity
@Table(name = "dept")
public class Dept {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "deptno")
    private int deptno;
    @Column(name = "dname",nullable = false)
    private String dname;
    @Column(name = "location",nullable = false)
    private String location;

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "deptno=" + deptno +
                ", dname='" + dname + '\'' +
                ", location='" + location + '\'' +
                '}';
    }
}

员工实体类

@Entity
@Table(name = "emp")
public class Emp {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "empno")
    private int empno;
    @Column(name = "ename",nullable = false)
    private String ename;
    @Column(name = "job",nullable = false)
    private String job;
    @Column(name = "hiredate",nullable = false)
    private String hiredate;
    @Column(name = "sal",nullable = false)
    private int sal;
    @Column(name = "comm",nullable = false)
    private int comm;
    //dept与emp的关系，一对多关系，一个部门有多个员工
    @ManyToOne //在多的一方使用ManyToOne多对一
    @JoinColumn(name = "dept")//@JoinColumn指定关联的一对一方法，通常是主键
    //只要获取dept的时候，会自动查询select * from dept where deptno=
    private Dept dept;

    public int getEmpno() {
        return empno;
    }

    public void setEmpno(int empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public String getHiredate() {
        return hiredate;
    }

    public void setHiredate(String hiredate) {
        this.hiredate = hiredate;
    }

    public int getSal() {
        return sal;
    }

    public void setSal(int sal) {
        this.sal = sal;
    }

    public int getComm() {
        return comm;
    }

    public void setComm(int comm) {
        this.comm = comm;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", hiredate='" + hiredate + '\'' +
                ", sal=" + sal +
                ", comm=" + comm +
                ", dept=" + dept +
                '}';
    }
}

2.在pom.xml中导入SpringData JPA的依赖和thymeleaf的依赖和MySQL驱动的依赖。

		<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>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

3.在全局配置文件(application.yml）配置相关文件。

spring:
  datasource:
    username: root
    password:
    url: jdbc:mysql://localhost:3306/jpahomework?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      #更新或者创建数据表结构
      ddl-auto: update
      #控制台显示SQL语句
    show-sql: true
    #thymeleaf的配置
  thymeleaf:
    mode: HTML5
    encoding: utf-8
    servlet:
      content-type: text/html
    cache: false
    prefix: classpath:/templates/

4.创建DeptRepository接口并实现JpaRepository。

public interface DeptRepository extends JpaRepository<Dept, Integer> {
}

5.创建EmpRepository接口并实现JpaRepository。

public interface EmpRepository extends JpaRepository<Emp, Integer>, JpaSpecificationExecutor<Emp> {
}

6.创建DeptService接口。

public interface DeptService {
    public List<Dept> getAlllDept();

}

7.创建EmpService接口。

public interface EmpService {
    public Page<Emp> getAllEmp(int page,int size,String ename,String job,String begintime,String endtime,int deptno);
    public void addEmp(Emp emp);
    public void updateEmp(Emp emp);
    public void deleteEmp(int depto);
    public Emp getEmpById(int empno);
}

8.创建DeptServiceImpl并实现DeptService接口。

@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private DeptRepository deptRepository;
    @Override
    public List<Dept> getAlllDept() {
        return deptRepository.findAll();
    }

}

9.创建EmptServiceImpl并实现EmpService接口。

@Service
public class EmpServiceImpl implements EmpService {
    @Autowired
    private EmpRepository empRepository;
    @Override
    public Page<Emp> getAllEmp(int page,int size,String ename,String job,String begintime,String endtime,int deptno) {
         //Pageable 是Spring Data库中定义的一个接口，用于构造翻页查询，是所有分页相关信息的一个抽象，
         // 通过该接口，我们可以得到和分页相关所有信息（例如pageNumber、pageSize等），这样，Jpa就能够通过pageable参数来得到一个带分页信息的Sql语句。
          Pageable pageable = PageRequest.of(page, size);
          return empRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<Predicate>();

            if (!StringUtils.isEmpty(ename)){
                predicates.add(criteriaBuilder.like(root.get("ename").as(String.class),"%" + ename + "%"));
            }
            if (!StringUtils.isEmpty(job)){
                predicates.add(criteriaBuilder.like(root.get("job"),"%" + job + "%"));
            }
            if (deptno!=0){
                predicates.add(criteriaBuilder.equal(root.get("dept"),deptno));
            }

            if (!StringUtils.isEmpty(begintime)){
                predicates.add(criteriaBuilder.greaterThan(root.get("hiredate"),begintime));
            }

            if (!StringUtils.isEmpty(endtime)){
                predicates.add(criteriaBuilder.lessThan(root.get("hiredate"),endtime));
            }

            return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
        },pageable);
    }

    @Override
    public void addEmp(Emp emp) {
         empRepository.save(emp);
    }

    @Override
    public void updateEmp(Emp emp) {
        empRepository.saveAndFlush(emp);
    }

    @Override
    public void deleteEmp(int depto) {
         empRepository.deleteById(depto);
    }

    @Override
    public Emp getEmpById(int empno) {
        return empRepository.getOne(empno);
    }
}

10.创建EmpController。编写多条件查询的方法

@Controller
public class EmpController {
    @Autowired
    private EmpService empService;
    @Autowired
    private DeptService deptService;
    @RequestMapping("/getAllEmp")
    public String getAllEmp(Model model, @RequestParam(value = "pageNo",required = true,defaultValue = "1") int pageNo,@RequestParam(value = "size",required = true,defaultValue = "5")int size, @RequestParam(value = "ename",required = false,defaultValue = "") String ename,
       @RequestParam(value = "job",required = false,defaultValue = "")String job,@RequestParam(value = "begintime",required = false,defaultValue = "") String begintime,@RequestParam(value = "endtime",required = false,defaultValue = "") String endtime,@RequestParam(value = "deptno",required = false,defaultValue = "0") int deptno){

        Page<Emp> emps=empService.getAllEmp(pageNo-1, size, ename, job, begintime, endtime, deptno);

        String url="/getAllEmp?ename="+ename+"&job="+job+"&begintime="+begintime+"&endtime="+endtime+"&deptno="+deptno;
        List<Dept> depts=deptService.getAlllDept();
        model.addAttribute("ename",ename);
        model.addAttribute("job",job);
        model.addAttribute("begintime",begintime);
        model.addAttribute("endtime",endtime);
        model.addAttribute("deptno",deptno);
        model.addAttribute("url",url);
        model.addAttribute("emps",emps);
        model.addAttribute("depts",depts);
        return "EmpList";

    }
}

11.在src/main/resources/templates文件夹下创建EmpList.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script type="text/javascript" src="asserts/js/jquery-3.2.1.slim.min.js" th:src="@{/js/jquery-1.11.3.min.js}"></script>
    <script>
        function btn_delete(id){

            var result=confirm("你确定删除吗");
            if(result==true){
                $.ajax({

                    type:"post",
                    url:"/delete?empno="+id,
                    success:function(data){
                         window.location.href="/getAllEmp";

                    }
                })
            }
        }
    </script>
    <link href="asserts/css/bootstrap.min.css" rel="stylesheet" th:href="@{/css/bootstrap.min.css}">

    <!-- Custom styles for this template -->
    <link href="asserts/css/dashboard.css" rel="stylesheet" th:href="@{/css/dashboard.css}">
</head>
<body>
    <form>
        <div>
            员工姓名：<input type="text" value="" name="ename" th:value="${ename}">
            职位：<input type="text" value="" name="job" th:value="${job}">
            入职日期：<input type="date" value="" name="begintime" th:value="${begintime}">--<input type="date" value="" name="endtime" th:value="${endtime}">
            所属部门：<select name="deptno">
                        <option value="0">请选择</option>
                        <!--/*@thymesVar id="depts" type="com.ruanyuan.springboot.pojo.Dept"*/-->
                        <option th:each="dept:${depts}" th:value="${dept.deptno}" th:text="${dept.dname}" th:selected="${deptno==dept.deptno}"></option>
                    </select>
            <input type="submit" value="查询">
            <a href="/getAllEmp">查询所有</a>
        </div>
        <br>
        <a href="/toAdd">添加员工</a>
        <table border="1px solid black">
            <thead>
                <tr>
                    <td>编号</td>
                    <td>姓名</td>
                    <td>职位</td>
                    <td>入职日期</td>
                    <td>薪水</td>
                    <td>奖金</td>
                    <td>所属部门</td>
                    <td>编辑</td>
                </tr>
            </thead>
            <tbody>

            <tr th:each="emp:${emps.content}">
                <!--/*@thymesVar id="empno" type="com.ruanyuan.springboot.pojo.Emp"*/-->
                <td th:text="${emp.empno}"></td>
                <td th:text="${emp.ename}">姓名</td>
                <td th:text="${emp.job}"> 职位</td>
                <td th:text="${emp.hiredate}">入职日期</td>
                <td th:text="${emp.sal}">薪水</td>
                <td th:text="${emp.comm}">奖金</td>
                <td th:text="${emp.dept.dname}">所属部门</td>
                <td>
                    <a class="btn btn-sm btn-primary" th:href="@{/toEdit(empno=${emp.empno})}">编辑</a>
                    <a class="btn btn-sm btn-danger deleteBtn" th:onclick="'btn_delete('+${emp.empno}+')'" >删除</a></td>
                </td>
            </tr>
            </tbody>
        </table>
    </form>
    <div th:replace="page :: page"></div>

</body>
</html>

page.html代码如图：

<!DOCTYPE html>
<html lang="en"  xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <!-- Bootstrap core CSS -->

    <style>
        a:link{
            position: relative;
        }
        a:visited{
            position: relative;
        }
    </style>
</head>
<body>
<div class="row" style="width: 1217px;" th:fragment="page">
    <div class="col-md-6" style="width: 200px">
        第[[${emps.getNumber()+1}]]页，共[[${emps.getTotalPages()}]]页，共[[${emps.getTotalElements()}]]条记录
    </div>
    <div class="col-md-6 offset-md-4">
        <nav aria-label="Page navigation example">
            <ul class="pagination pagination-sm" >

                <!--/*@thymesVar id="previousOrFirstPageable" type="com.ruanyuan.springboot.pojo.Emp"*/-->
                <li class="page-item" th:if="${emps.isFirst()!=true}" >
                    <a class="page-link" href="${url}pageNo=${emps.pageNum-1}" th:href="@{${url}(pageNo=${emps.getNumber()+1})}">
                        上一页
                    </a>
                </li>
                <li class="page-item" th:if="${emps.isFirst()==true}" >
                    <span class="page-link" href="#" style="color: gray">
                        上一页
                    </span>
                </li>

                <li class="page-item" th:each="page:${#numbers.sequence(0,emps.getTotalPages()-1)}" th:class="${page==emps.getNumber()}?'page-item active':'page-item'">
                    <a class="page-link" th:href="@{${url}(pageNo=${page+1})}"  th:text="${page+1}"/>
                </li>


                <li class="page-item" th:if="${emps.isLast()!=true}">
                    <a class="page-link" th:href="@{${url}(pageNo=${emps.getNumber()+1})}">
                        下一页
                    </a>
                </li>
                <li class="page-item" th:if="${emps.isLast()==true}">
                    <span class="page-link" href="#" style="color: gray">
                        下一页
                    </span>
                </li>

            </ul>
        </nav>
    </div>
</div>

</body>
</html>

12.在EmpController编写添加修改删除相关方法。

@RequestMapping("/toAdd")
    public String toAdd(Model model){
        List<Dept> depts=deptService.getAlllDept();
        model.addAttribute("depts",depts);
        return "EmpEdit";
    }
    @RequestMapping("/Add")
    public String Add(Emp emp){
        System.out.println(emp);
        empService.addEmp(emp);
        return "redirect:/getAllEmp";
    }
    @RequestMapping("/toEdit")
    public String toEdit(Model model,int empno){
        List<Dept> depts=deptService.getAlllDept();
        model.addAttribute("depts",depts);
        model.addAttribute("emp", empService.getEmpById(empno));
        return "EmpEdit";
    }
    @RequestMapping("/Edit")
    public String Edit(Emp emp){
        empService.updateEmp(emp);
        return "redirect:/getAllEmp";
    }
    @RequestMapping("/delete")
    @ResponseBody
    public void delete(int empno){
        empService.deleteEmp(empno);

    }

13.在src/main/resources/templates文件夹下创建EmpEdit.html

<!DOCTYPE html>
<html lang="en"  xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link href="asserts/css/bootstrap.min.css" rel="stylesheet" th:href="@{/css/bootstrap.min.css}">

    <!-- Custom styles for this template -->
    <link href="asserts/css/dashboard.css" rel="stylesheet" th:href="@{/css/dashboard.css}">
</head>
<body>
<h2 th:text="${emp!=null?'员工修改':'员工添加'}"></h2>
<form th:action="${emp!=null}?@{/Edit}:@{/Add}" th:method="post">

    <input type="hidden" name="empno"th:value="${emp.empno}" th:if="${emp!=null}">
    <div class="form-group">
        <label for="exampleInputEmail1">姓名：</label>
        <input type="text" class="form-control" id="exampleInputEmail1"  th:name="ename" th:value="${emp!=null}?${emp.ename}">
    </div>
    <div class="form-group">
        <label for="exampleInputPassword1">职位：</label>
        <input type="text" class="form-control" id="exampleInputPassword1" th:name="job" th:value="${emp!=null}?${emp.job}">
    </div>

    <div class="form-group">
        <label for="exampleInputPassword1">入职日期：</label>
        <input type="date" class="form-control"  th:name="hiredate" th:value="${emp!=null}?${emp.hiredate}">
    </div>
    <div class="form-group">
        <label for="exampleInputPassword1">薪水：</label>
        <input type="number" class="form-control"  th:name="sal" th:value="${emp!=null}?${emp.sal}">
    </div>
    <div class="form-group">
        <label for="exampleInputPassword1">奖金：</label>
        <input type="number" class="form-control"  th:name="comm" th:value="${emp!=null}?${emp.comm}">
    </div>
    <div class="form-group">
        <label for="exampleFormControlSelect1">所属部门</label>
        <select class="form-control" id="exampleFormControlSelect1" th:name="dept.deptno">
            <option th:each="dept:${depts}" th:value="${dept.deptno}" th:selected="${emp!=null}?${dept.deptno eq emp.dept.deptno}">[[${dept.dname}]]</option>

        </select>
    </div>
    <button type="submit" class="btn btn-default">提交</button>
    <a href="javascript:history.go(-1)" class="back">返回</a>
</form>
</body>
</html>