application.properties:
##数据源配置
spring.datasource.url=jdbc:mysql://localhost:3306/zytj?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=admin
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
##配置方言
spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect
##运行时输出jpa执行的sql语句
spring.jpa.show-sql=true
##spring-boot-starter-data-jpa自动映射创建表动作 配置: 有表更新,无表创建
spring.jpa.hibernate.ddl-auto=update
entity:
package com.jlb.zytj.model;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import org.springframework.format.annotation.DateTimeFormat;
import lombok.Data;
@Data
@Entity
@Table
@EntityListeners(AuditingEntityListener.class)
public class Member {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;//主键
@NotNull
@Column(name = "cardno",nullable = false)
private String cardNo;//会员卡号
@NotNull
@Column(nullable = false)
private String name;//姓名
private String sex;//性别
@Column(name = "category",columnDefinition = "tinyint default 0")
private int category=0;//身份,0表示学生，1表示老师
@Column(unique = true,length = 18)
private String card;//身份证号，唯一，用来保证每个用户只能有一个会员卡
private String department;//系
private String profession;//专业
private String grade;//年级
@NotNull
@Column(nullable = false,length = 11)
private String phone;//电话
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@CreatedDate
private Date regdate;//入会时间
@Column(name="status",columnDefinition="tinyint default 0")
private int status=0;//状态，0表示没有使用任何资源，其他状态填写资源id
private String remark;//备注
}
dao:
package com.jlb.zytj.dao;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.jlb.zytj.model.Member;
public interface IMemberDao extends JpaRepository<Member, Integer>,JpaSpecificationExecutor<Member>{
}
Iservice接口:
package com.jlb.zytj.service;
import java.util.Map;
import com.jlb.zytj.dto.MemberDto;
public interface IMemberService {
Map<String, Object> findByCondition(MemberDto dto);
}
servies:
package com.jlb.zytj.service;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.transaction.Transactional;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import com.jlb.zytj.dao.IMemberDao;
import com.jlb.zytj.dto.MemberDto;
import com.jlb.zytj.model.Member;
@Service
@Transactional
public class MemberService implements IMemberService{
@Resource
private IMemberDao memberdao;
@Override
public Map<String, Object> findByCondition(MemberDto dto) {
Map<String,Object> map = new HashMap<String,Object>();
Pageable pageable = PageRequest.of(dto.getPage()-1, dto.getSize(), Sort.by(Direction.DESC, "regdate"));
Page<Member> page = memberdao.findAll(getWhereClause(dto), pageable);
map.put("code", 0);
map.put("count", memberdao.count(getWhereClause(dto)));
map.put("msg", "查询成功");
map.put("data",page.getContent());
return map;
}
private Specification<Member> getWhereClause(MemberDto dto){
return new Specification<Member>() {
@Override
public Predicate toPredicate(Root<Member> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
List<Predicate> list=new ArrayList<Predicate>();
if(dto.getCategory()==0) {//学生
if(dto.getDepartment()!=null && ""!=dto.getDepartment()) {//系
list.add(criteriaBuilder.equal(root.<String>get("department"), dto.getDepartment()));
}
if(dto.getProfession()!=null && ""!=dto.getProfession()) {//专业
list.add(criteriaBuilder.equal(root.<String>get("profession"), dto.getProfession()));
}
if(dto.getGrade()!=null && ""!=dto.getGrade()) {//年级
list.add(criteriaBuilder.equal(root.<String>get("grade"), dto.getGrade()));
}
}
if(dto.getName()!=null && ""!=dto.getName()) {//姓名模糊查询
list.add(criteriaBuilder.like(root.<String>get("name"), "%"+dto.getName()+"%"));
}
if(dto.getCardNo()!=null && ""!=dto.getCardNo()) {//会员卡号
list.add(criteriaBuilder.equal(root.get("card_no").as(String.class), dto.getCardNo()));
}
if(dto.getPhone()!=null && ""!=dto.getPhone()) {//手机号
list.add(criteriaBuilder.equal(root.get("phone").as(String.class), dto.getPhone()));
}
if(dto.getStartTime()!=null) {//入会时间
list.add(criteriaBuilder.greaterThanOrEqualTo(root.get("regdate").as(Date.class), dto.getStartTime()));
}
if(dto.getEndTime()!=null) {//入会时间
list.add(criteriaBuilder.lessThanOrEqualTo(root.get("regdate").as(Date.class), dto.getEndTime()));
}
if(dto.getStatus()>0) {
list.add(criteriaBuilder.lt(root.<Integer>get("status"), dto.getStatus()));
}
Predicate[] pre = new Predicate[list.size()];
return query.where(list.toArray(pre)).getRestriction();
}
};
}
}
controller:
package com.jlb.zytj.controller;
import java.util.HashMap;
import java.util.Map;
import javax.annotation.Resource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.jlb.zytj.dao.IMemberDao;
import com.jlb.zytj.dto.MemberDto;
import com.jlb.zytj.service.IMemberService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
@RestController
@Api(tags = "会员查询")
@RequestMapping("/member")
public class MemberController {
@Resource
private IMemberService memberservice;
@Resource
private IMemberDao memberdao;
@ApiOperation(value = "会员查询",notes = "会员查询")
@PostMapping("/findByCondition")
public Map<String, Object> findByCondition(MemberDto dto){
return memberservice.findByCondition(dto);
}
}
前台页面(引用的模板):
<title>简单用法 - 数据表格</title>
<div class="layui-card layadmin-header">
<div class="layui-breadcrumb" lay-filter="breadcrumb">
<a lay-href="">主页</a>
<a><cite>组件</cite></a>
<a><cite>数据表格</cite></a>
<a><cite>简单用法</cite></a>
</div>
</div>
<div class="layui-fluid">
<div class="layui-row layui-col-space15">
<div class="layui-col-md12">
<div class="layui-card">
<div class="layui-card-header">简单用法</div>
<div class="layui-card-body">
<table class="layui-hide" id="test-table-simple"></table>
</div>
</div>
</div>
</div>
</div>
<script>
layui.use(['admin', 'table'], function(){
var table = layui.table;
table.render({
elem: '#test-table-simple'
,method:'post'
,page:true
,url: '../member/findByCondition'
,cellMinWidth: 80 //全局定义常规单元格的最小宽度，layui 2.2.1 新增
,cols: [[
{field:'id', width:80, title: 'ID', sort: true}
,{field:'card', width:80, title: '身份证'}
,{field:'cardno', width:80, title: '会员卡号', sort: true}
,{field:'category', width:80, title: '职业'}
,{field:'department', title: '部门', minWidth: 150}
,{field:'grade', width:80, title: '年级', sort: true}
,{field:'name', width:80, title: '会员名', sort: true}
,{field:'phone', width:80, title: '手机'}
,{field:'profession', width:135, title: '专业', sort: true}
,{field:'regdate', width:80, title: '日期', sort: true}
,{field:'remark', width:80, title: '备注'}
,{field:'sex', width:135, title: '性别', sort: true}
,{field:'status', width:135, title: '状态', sort: true}
]]
});
});
</script>
报的错误:
java.lang.IllegalArgumentException: Page size must not be less than one!
at org.springframework.data.domain.AbstractPageRequest.<init>(AbstractPageRequest.java:50) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE]
at org.springframework.data.domain.PageRequest.<init>(PageRequest.java:71) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE]
at org.springframework.data.domain.PageRequest.of(PageRequest.java:96) ~[spring-data-commons-2.0.9.RELEASE.jar:2.0.9.RELEASE]
at com.jlb.zytj.service.MemberService.findByCondition(MemberService.java:37) ~[classes/:na]
