一、前言
这个假期有点儿长,撸代码有点儿手生了,就空余时间总结一下这个很经典的BootStrap。计划总结以下几个部分:列表查询、列表导出、列表导入。
注:总结过程中使用的开发环境,开发工具,项目基础结构如下: jdk1.8;tomcat7;maven3.5 ;idea;mysql;springboot;mybatis-plus。
二、背景
我们以学生信息为基础,实现查询学生信息,学生信息列表导出和学生信息列表导入三个功能。
三、数据库建立
四、列表分页查询
4.1根据数据库,建立开发此功能所需要的接口和类,这里不重复叙述,不懂得可以看我初学Spring-boot系列的前几篇文章。
https://blog.csdn.net/weixin_40877388/category_9584708.html
4.2html页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>学生信息</title>
<link rel="stylesheet" href="../static/common/bootstrap-3.3.7-dist/css/bootstrap.min.css">
<link rel="stylesheet" href="../static/common/bootstrap-table/bootstrap-table.css">
</head>
<body>
<div class="wrapper wrapper-content animated fadeInRight"id="tablediv">
<!--列表搜索条件-->
<div class="row">
<div class="col-sm-12">
<div class="ibox float-e-margins">
<div class="ibox-title">
<h5>学生列表筛选条件</h5>
<!--<div class="ibox-tools">
<a class="collapse-link"><i class="fa fa-chevron-up"></i></a>
<a class="close-link"><i class="fa fa-times"></i></a>
</div>-->
</div>
<div class="ibox-content search-query">
<div class="row">
<div class="col-sm-6">
<div class="form-group">
<label class="control-label col-sm-2">学生id:</label>
<div class="input-group col-sm-4">
<input type="text" class="form-control" id="sId" name="sId">
</div>
</div>
</div>
<div class="col-sm-6">
<div class="form-group">
<label class="control-label col-sm-2">学生学号:</label>
<div class="input-group col-sm-4">
<input type="text" class="form-control" id="sNumber" name="sNumber">
</div>
</div>
</div>
<div class="col-sm-6">
<div class="form-group">
<label class="control-label col-sm-2">学生姓名:</label>
<div class="input-group col-sm-4">
<input type="text" class="form-control" id="sName" name="sName">
</div>
</div>
</div>
<div class="col-sm-6">
<div class="form-group">
<label class="control-label col-sm-2">学生年龄:</label>
<div class="input-group col-sm-4">
<input type="text" class="form-control" id="sAge" name="sAge">
</div>
</div>
</div>
</div>
</div>
<!--<div class="hr-line-dashed"></div>-->
<div class="ibox-content search-query">
<div class="row">
<div class="col-sm-2">
<div class="form-group">
<button class="btn btn-primary infoSearchSubmit" type="button" id="studentSearchSubmit">
搜索
</button>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<!--列表内容-->
<div class="row">
<div class="col-sm-12">
<div class="ibox float-e-margins">
<div class="ibox-title">
<h2>学生信息列表</h2>
</div>
<div class="alarm">
<button class="btn btn-danger" id="export" onclick="exportData()">导出Excle</button>
<button class="btn btn-danger" id="import" onclick="importData()">批量导入</button>
</div>
<div class="ibox-content">
<div class="row row-lg">
<div class="col-sm-12">
<div class="example-wrap">
<div class="example" >
<table id="studentTable"></table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<script src="../static/common/jquery-3.2.0.min.js"></script>
<script src="../static/common/bootstrap-3.3.7-dist/js/bootstrap.min.js"></script>
<script src="../static/common/bootstrap-table/bootstrap-table.js"></script>
<script src="../static/common/bootstrap-table/locale/bootstrap-table-zh-CN.min.js"></script>
<script src="../static/student_detail.js"></script>
</body>
</html>
4.3 student_detail.js
关于bootstarp-table的解释
function initExplainTable() {
$('#explainTable').bootstrapTable({
url: ctp + '/system/explain/selectPage',// controller方法的路径
method: 'post',// 请求方式 post 请求
striped: true,// 是否显示行间隔色 true 隔行变色
cache: false,//是否使用缓存,默认为true,所以一般情况下需要设置一下这个属性(*)false 禁用ajax数据缓存
pagination: true,// 表格底部显示分页条
sortable: false,// 是否启用排序 false 不排列
sortOrder: "asc",// 自定义排序方式 asc 升序
sidePagination: 'server',//,//分页方式:client客户端分页,server服务端分页(*)
queryParamsType: 'limit',// 设置为 'limit' 则会发送符合 RESTFul 格式的参数。
queryParams: defaultQueryParams,//传递参数
pageNumber: 1,// 初始化加载第一页 默认第一页
pageSize: 10,// 每页的记录行数(*)
pageList: [10, 50, 100],// 可供选择的每页的行数(*)
clickToSelect: true,//是否启用点击选中行 true 启用点击选中行
uniqueId: 'id',//每一行的唯一标识 一般为主键列
showRefresh: true,//是否显示刷新按钮 true 显示刷新按钮
checkboxHeader: true,
undefinedText: "-",//未定义文本
search: true,//是否显示表格搜索,此搜索是客户端搜索,不会进服务端,严格搜索
searchOnEnterKey: true,
searchTimeOut: 30,
strictSearch: true,//true 严格搜索
showColumns: true,//是否显示所有的列 true 显示所有的列
height: 500, //行高,如果没有设置height属性,表格自动根据记录条数觉得表格高度
showToggle: true,//true 显示详细视图和列表视图的切换按钮
showPaginationSwitch: true,//true 显示页面切换
minimumCountColumns: 2, //最少允许的列数
toolbar: '#toolbar',//样式
idField: 'id',//指定主键列
columns: [{
field: 'explainName',//列字段名
title: '说明名称' //页面上展示的列名
}, {
field: 'explainValue',
title: '说明内容'
},{
title: '操作',
class: 'td-manage',
formatter: function (value, row, index) {
var editBtn = '';
var delBtn = '';
editBtn += '<a class="edit m-l-sm text-warning" title="编辑">';
editBtn += '<i class="glyphicon glyphicon-edit"></i></a>';
delBtn += '<a class="remove m-l-sm text-danger" title="删除">';
delBtn += '<i class="glyphicon glyphicon-remove"></i></a>';
return editBtn + delBtn;
}
}]
});
}
我的代码:
$(function(){
initProjectTable();
})
//初始化学生列表
function initProjectTable(){
//初始化列表
$('#studentTable').bootstrapTable({
url: 'getAllStudents',
method: 'post',
contentType:'application/x-www-form-urlencoded; charset=UTF-8',
striped: true,
cache: false,
pagination: true,
sortable: false,
sortOrder: "asc",
queryParamsType: 'limit',
queryParams: defaultQueryParams,
sidePagination: 'server',
pageNumber: 1,
pageSize: 5,
pageList: [5,10, 50],
clickToSelect: true,
uniqueId: 'sId',
showRefresh: true,
checkboxHeader: true,
undefinedText: "-",
strictSearch: true,
showColumns: true,
showToggle: true,
showPaginationSwitch: true,
toolbar: '#toolbar',
columns: [{
field: 'sId',
title: '学生id'
},{
field: 'sNumber',
title: '学生编号'
},{
field: 'sName',
title: '学生姓名'
},{
field: 'sAge',
title: '学生年龄'
}, {
field: 'sId',
title: '操作',
class: 'td-manage',
formatter: function (value, row, index) {
var editBtn = '';
var delBtn = '';
editBtn += ' <button class="edit btn btn-primary btn-sm" data-toggle="modal" data-target=".bs-example-modal-lg" title="编辑">编辑</button> ';
delBtn += ' <button class="remove btn btn-danger btn-sm" title="删除">删除</button> ';
return editBtn + delBtn;
}
}]
});
}
//表格自定义参数
function defaultQueryParams(params) {
params.pageNumber= this.pageNumber;//当前页码
params.limit = this.pageSize;//查多少条(每页显示数量)
//params.offset=(this.pageNumber-1)*this.pageSize;//sql语句起始索引
return params;
}
4.4我的服务端分页代码
4.4.1 Controller(PageHelper这个工具类我在4.4.5贴出)
package com.xuexue.firstproject.Controllers;
import com.xuexue.firstproject.Beans.Student;
import com.xuexue.firstproject.Services.IStudentService;
import com.xuexue.firstproject.common.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
@RequestMapping("/student")
public class StudentController {
@Autowired
IStudentService iStudentService;
//访问学生列表页面
@RequestMapping("/toStudent")
public String toExport(){
return "student_detail";
}
//查询学生信息列表
@RequestMapping(value = "/getAllStudents",method = RequestMethod.POST)
@ResponseBody
public PageHelper<Student> getAllStudents(Student student, Integer pageNumber, Integer limit){
//System.out.println("offset:"+pageNumber+"limit:"+limit);
return iStudentService.getAllStudents(student,pageNumber,limit);
}
}
4.4.2 Service
接口:
package com.xuexue.firstproject.Services;
import com.baomidou.mybatisplus.service.IService;
import com.xuexue.firstproject.Beans.Student;
import com.xuexue.firstproject.common.PageHelper;
public interface IStudentService extends IService<Student> {
//查询学生信息列表
PageHelper<Student> getAllStudents(Student student,Integer pageNumber, Integer limit);
}
实现:
package com.xuexue.firstproject.Services.imp;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.mapper.Wrapper;
import com.baomidou.mybatisplus.plugins.pagination.Pagination;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.xuexue.firstproject.Beans.Student;
import com.xuexue.firstproject.Daos.StudentMapper;
import com.xuexue.firstproject.Services.IStudentService;
import com.xuexue.firstproject.common.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
@Service
public class StudentService extends ServiceImpl<StudentMapper,Student> implements IStudentService {
@Autowired
StudentMapper studentMapper;
//查询学生信息列表
@Override
public PageHelper<Student> getAllStudents(Student student,Integer pageNumber, Integer limit) {
//实例化一个用于储存查询回来的对象、信息的类
PageHelper<Student> pageHelper = new PageHelper();
//实例化mybatisplus条件构造器,拼接分页查询的条件
Wrapper<Student> wrapper = new EntityWrapper<>();
//根据条件查询学生数量
int total = studentMapper.selectCount(wrapper);
//System.out.println(total);
pageHelper.setTotal(total);
//判断学生数量,如果大于0,进行分页查询,如果小于0,返回空的list集合
if(total>0){
//分页查询;如果分页limit不等于null,且大于0,说明是分页查询,否则是全部查询
if(limit != null && limit > 0){
//实例化mybatis-plus分页工具类
Pagination pagination = new Pagination(pageNumber,limit);
//分页查询学生信息
pageHelper.setRows(studentMapper.selectPage(pagination,wrapper));
}else{
//查询学生信息
pageHelper.setRows(studentMapper.selectList(wrapper));
}
}else{
//返回空的list集合
pageHelper.setRows(new ArrayList<Student>());
}
return pageHelper;
}
}
4.4.3 Dao && Mapper
package com.xuexue.firstproject.Daos;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.xuexue.firstproject.Beans.Student;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface StudentMapper extends BaseMapper<Student> {
}
<?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.xuexue.firstproject.Daos.StudentMapper">
<resultMap id="LxJobInfo" type="com.xuexue.firstproject.Beans.Student" >
<result column="s_id" property="sId" />
<result column="s_number" property="sNumber" />
<result column="s_name" property="sName" />
<result column="s_age" property="sAge" />
</resultMap>
<sql id="Base_Column_List">
t.s_id,
t.s_number,
t.s_name,
t.s_age
</sql>
</mapper>
4.4.4 Bean
package com.xuexue.firstproject.Beans;
import com.baomidou.mybatisplus.activerecord.Model;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import java.io.Serializable;
@TableName("student")
public class Student extends Model<Student> {
@TableId(value = "s_id", type = IdType.AUTO)
private Integer sId;
private String sNumber;
private String sName;
private String sAge;
public Integer getsId() {
return sId;
}
public void setsId(Integer sId) {
this.sId = sId;
}
public String getsNumber() {
return sNumber;
}
public void setsNumber(String sNumber) {
this.sNumber = sNumber;
}
public String getsName() {
return sName;
}
public void setsName(String sName) {
this.sName = sName;
}
public String getsAge() {
return sAge;
}
public void setsAge(String sAge) {
this.sAge = sAge;
}
@Override
protected Serializable pkVal() {
return this.sId;
}
}
4.4.5 返回数据工具类
package com.xuexue.firstproject.common;
import java.util.ArrayList;
import java.util.List;
public class PageHelper<T> {
//实体类集合
private List<T> rows = new ArrayList<T>();
//数据总条数
private int total;
//字符串
private String str;
//对象
private Object obj;
public PageHelper() {
super();
}
public List<T> getRows() {
return rows;
}
public void setRows(List<T> rows) {
this.rows = rows;
}
public int getTotal() {
return total;
}
public void setTotal(int total) {
this.total = total;
}
public String getStr() {
return str;
}
public void setStr(String str) {
this.str = str;
}
public Object getObj() {
return obj;
}
public void setObj(Object obj) {
this.obj = obj;
}
}
至此,整个分页查询的例子就写好了。
但是这个例子中没有写按照条件查询,如果想要按照条件查询,需要将参数传入后台,并在分页部分的代码
Wrapper<Student> wrapper = new EntityWrapper<>();拼接条件就可以做到啦!
详细的写法可以参考mybatis-plus官方文档。https://mp.baomidou.com/
如果没有用mybatis-plus,也可以在mapper.xml文件中进行拼接。下面是个小例子
<select id="selectDesignerNoGroup"
parameterType="com.**.**.dto.GroupMemberQuery"
resultType="com.**.**.model.CustomerExtInfo">
select
bcei.id AS id ,
bcei.customer_id as customerId,
bcei.user_name as userName ,
bcei.user_phone as userPhone,
bcei.user_post as userPost
from baishi_customer_ext_info bcei
<where>
and bcei.user_state = 0
and bcei.customer_id not in (select group_owner_id from group_info where group_state = 0 )
and bcei.customer_id not in (select member_id from group_member_info where member_state = 0 )
and bcei.user_post = 2
<!-- 地区areaId -->
<if test="areaId != null and areaId !=''">
AND bcei.area_id = #{areaId}
</if>
<!-- 名称userName -->
<if test="userName != null and userName !=''">
AND bcei.user_name = #{userName}
</if>
<!-- 电话 -->
<if test="userPhone != null and userPhone !=''">
AND bcei.user_phone = #{userPhone}
</if>
</where>
</select>