springboot+layui实现增删查改

本文描述springboot和layui的结合，采用了springboot内置的jdbc，根据不同需要可以进行修改；分页采用了layui中自带分页格式！

-------------------------------------------------数据管理dao层

package main.springboot.dao;

import main.springboot.bean.PageBean;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Repository;

import java.util.List;

import java.util.Map;

    /**

     * 教师DAO

     * @author 周帅

     */

    @Repository

    public class TeaDao extends PageDao{

        @Autowired

        private JdbcTemplate jdbcTemplate;

        /**

         * 查询所有学生

         * @return

         */

        public List queryTea(String newname){

            String sql = "select * from t_tea where flag=1";

            System.out.println(newname+"   aaaaa");

            if(newname!=null){

                sql +=" and name like '%"+newname+"%'";

            }

            System.out.println(sql);

            return jdbcTemplate.queryForList(sql);

        }

        /**

         * 获取用户信息

         * @param id

         * @return

         */

        public Map getTea(int id){

            String sql = "select * from t_tea where id="+id;

            return jdbcTemplate.queryForMap(sql);

        }

        /**

         * 更新教师信息

         * @param TeaMap

         */

        public void updateTea(Map<String,Object> TeaMap){

            String name = TeaMap.get("name").toString();

            String sex = (String) TeaMap.get("sex");

            String age = (String) TeaMap.get("age");

            System.out.println(sex);

            System.out.println(age);

            String updatesql = "";

            if(name!=null){

                updatesql +="name='"+name+"',";

            }

            if(sex!=null){

                updatesql +="sex="+sex+",";

            }

            if(age!=null){

                updatesql +="age="+age;

            }

            System.out.println(TeaMap.get("id")+"aaaaaaawww");

            if(updatesql.length()>2){

                String sql = "update t_tea set "+updatesql+" where id="+TeaMap.get("id");

                jdbcTemplate.execute(sql);

            }

        }

        /**

         * 添加教师

         * @param TeaMap

         */

        public void addTea(Map<String,Object> TeaMap){

            String sql="insert into t_tea(name,sex,age,flag) values('"+TeaMap.get("name")+"',"+TeaMap.get("sex")+","+TeaMap.get("age")+",'1')";

            jdbcTemplate.execute(sql);

        }

        /**

         * 删除教师

         * @param id

         */

        public void deleteTea(String  id){

            String sql="update t_tea set flag='0' where id in("+id+")";

            jdbcTemplate.execute(sql);

        }

        /**

         * 模糊查询

         * @param pageBean

         * @param where

         * @param orderby

         * @return

         */

        public PageBean mohuquery(PageBean pageBean, String where, String orderby){

            String sql="select * from t_tea where flag=1 " ;

            return this.queryPageByMysql(pageBean, sql, where, orderby);

        }

}

-----------------------------------------------------------pageDao分页查询

package main.springboot.dao;

import java.util.List;

import java.util.Map;

import main.springboot.bean.PageBean;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Repository;

@Repository

public class PageDao {

   @Autowired

   private JdbcTemplate jdbcTemplate;

   /**

    * 分页查询

    * @param sql

    * @param where

    * @param orderby

    * @return

    */

   public PageBean queryPageByMysql(PageBean pageBean,String sql,String where,String orderby){

      int page = pageBean.getPage();

      int rows = pageBean.getRows();

      int startIndex = (page-1) * rows;

      String pageSql = sql+where+orderby+" limit "+startIndex+","+rows;

      System.out.println(pageSql);

      System.out.println("PageBean Sql语句："+pageSql);

      List list = jdbcTemplate.queryForList(pageSql);

      System.out.println("PageBean 查询数据："+list);

      pageBean.setPageData(list);

      int total = this.queryTotal(sql, where);

      pageBean.setTotal(total);

      return pageBean;

   }

   /**

    * 分页查询

    * @param sql

    * @param where

    * @param orderby

    * @return

    */

   public PageBean queryPageByOracle(PageBean pageBean,String sql,String where,String orderby){

      int page = pageBean.getPage();

      int rows = pageBean.getRows();

      int startIndex = (page-1) * rows;

      int endIndex = page * rows;

      String pageSql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM ("+sql+where+orderby+") A WHERE ROWNUM <= "+endIndex+" ) WHERE RN > "+startIndex;

      List list = jdbcTemplate.queryForList(pageSql);

      pageBean.setPageData(list);

      int total = this.queryTotal(sql, where);

      pageBean.setTotal(total);

      return pageBean;

   }

   /**

    * 查询总数量

    * @param sql

    * @param where

    * @return

    */

   public int queryTotal(String sql,String where){

      String totalSql = " select count(1) as total from ("+sql+where+") a ";

      List list = jdbcTemplate.queryForList(totalSql);

      Map map = (Map)list.get(0);

      return Integer.parseInt(map.get("total").toString());

   }

}

-----------------------------------------------------------业务service层

package main.springboot.action;

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

import main.springboot.bean.PageBean;

import main.springboot.service.TeaService;

import main.springboot.service.UserService;

import main.springboot.utils.RedisUtils;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.data.domain.Page;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

/**

 * 教师控制层

 * @author 周帅

 */

@Controller

public class TeaAction {

    @Autowired

    private RedisUtils redisUtils;

    @Autowired

    private TeaService teaService;

    @Autowired

    private UserService userService;

    private String newname ="";

    /**

     * 查询所有教师

     * @param request

     * @return

     */

    @RequestMapping("/view/system/queryTeaList")

    @ResponseBody

    public String queryRoleList(HttpServletRequest request){

        try {

            List list = teaService.queryTea(newname);

            JSONObject josn = new JSONObject();

            josn.put("code",0);

            josn.put("msg","");

            josn.put("data",JSONArray.toJSON(list));

            return josn.toJSONString();

        } catch (Exception e) {

            e.printStackTrace();

        }

        return null;

    }

    /**

     * 根据ID查询教师信息

     * @param request

     * @param id

     * @return

     */

    @RequestMapping("/view/system/queryById")

    @ResponseBody

    public Map queryById(HttpServletRequest request,@RequestParam int id){

        Map map = null;

        try {

            map = teaService.getTea(id);

        }catch (Exception e){

            e.printStackTrace();

        }

        return map;

    }

    /**

     * 添加教师

     * @param request

     * @param teaMap

     * @return

     */

    @RequestMapping("/view/system/addTea")

    @ResponseBody

    public int addTea(HttpServletRequest request,@RequestParam Map teaMap){

        try {

            teaService.addTea(teaMap);

            return 0;

        }catch (Exception e){

            e.printStackTrace();

        }

        return -1;

    }

    /**

     * 修改教师

     * @param request

     * @param teaMap

     * @return

     */

    @RequestMapping("/view/system/updateTea")

    @ResponseBody

    public int updateTea(HttpServletRequest request,@RequestParam Map teaMap){

        try {

            teaService.updateTea(teaMap);

            return 0;

        }catch (Exception e){

            e.printStackTrace();

        }

        return -1;

    }

    /**

     * 删除教师

     * @param request

     * @param id

     * @return

     */

    @RequestMapping("/view/system/deleteTea")

    @ResponseBody

    public int deleteTea(HttpServletRequest request,@RequestParam String id){

        try {

            teaService.deleteTea(id);

            return 0;

        }catch (Exception e){

            e.printStackTrace();

        }

        return -1;

    }

    /**

     * 图表数据查询

     * @return

     */

    @RequestMapping("/view/system/queryTea2")

    @ResponseBody

    public ResponseEntity<?> queryTea2(){

        System.out.println(newname);

        List list = teaService.queryTea(newname);

        return  new ResponseEntity<List>(list, HttpStatus.OK);

    }

    /**

     * 分页查询

     * @param request

     * @param queryParams

     * @return

     */

    @RequestMapping("/view/system/queryTea")

    @ResponseBody

    public Map queryTea(HttpServletRequest request,@RequestParam Map queryParams){

        Map result = new HashMap();

        try {

            PageBean pageBean = new PageBean();

            pageBean.setPage(Integer.parseInt(queryParams.get("page").toString()));

            pageBean.setRows(Integer.parseInt(queryParams.get("limit").toString()));

            newname = (String) queryParams.get("keyword");

            pageBean.setQueryParams(queryParams);

            pageBean = teaService.mohuquery(pageBean);

            result.put("count", pageBean.getTotal());

            result.put("data", pageBean.getPageData());

            result.put("msg", "");

            result.put("code", 0);

        } catch (Exception e) {

            e.printStackTrace();

            result.put("count", 0);

            result.put("data", new ArrayList());

            result.put("msg", "");

            result.put("code", 500);

        }

        return result;

    }

}

----------------------------------------------------------------------控制action层

package main.springboot.action;

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

import main.springboot.bean.PageBean;

import main.springboot.service.TeaService;

import main.springboot.service.UserService;

import main.springboot.utils.RedisUtils;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.data.domain.Page;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

/**

 * 教师控制层

 * @author 周帅

 */

@Controller

public class TeaAction {

    @Autowired

    private RedisUtils redisUtils;

    @Autowired

    private TeaService teaService;

    @Autowired

    private UserService userService;

    private String newname ="";

    /**

     * 查询所有教师

     * @param request

     * @return

     */

    @RequestMapping("/view/system/queryTeaList")

    @ResponseBody

    public String queryRoleList(HttpServletRequest request){

        try {

            List list = teaService.queryTea(newname);

            JSONObject josn = new JSONObject();

            josn.put("code",0);

            josn.put("msg","");

            josn.put("data",JSONArray.toJSON(list));

            return josn.toJSONString();

        } catch (Exception e) {

            e.printStackTrace();

        }

        return null;

    }

    /**

     * 根据ID查询教师信息

     * @param request

     * @param id

     * @return

     */

    @RequestMapping("/view/system/queryById")

    @ResponseBody

    public Map queryById(HttpServletRequest request,@RequestParam int id){

        Map map = null;

        try {

            map = teaService.getTea(id);

        }catch (Exception e){

            e.printStackTrace();

        }

        return map;

    }

    /**

     * 添加教师

     * @param request

     * @param teaMap

     * @return

     */

    @RequestMapping("/view/system/addTea")

    @ResponseBody

    public int addTea(HttpServletRequest request,@RequestParam Map teaMap){

        try {

            teaService.addTea(teaMap);

            return 0;

        }catch (Exception e){

            e.printStackTrace();

        }

        return -1;

    }

    /**

     * 修改教师

     * @param request

     * @param teaMap

     * @return

     */

    @RequestMapping("/view/system/updateTea")

    @ResponseBody

    public int updateTea(HttpServletRequest request,@RequestParam Map teaMap){

        try {

            teaService.updateTea(teaMap);

            return 0;

        }catch (Exception e){

            e.printStackTrace();

        }

        return -1;

    }

    /**

     * 删除教师

     * @param request

     * @param id

     * @return

     */

    @RequestMapping("/view/system/deleteTea")

    @ResponseBody

    public int deleteTea(HttpServletRequest request,@RequestParam String id){

        try {

            teaService.deleteTea(id);

            return 0;

        }catch (Exception e){

            e.printStackTrace();

        }

        return -1;

    }

    /**

     * 图表数据查询

     * @return

     */

    @RequestMapping("/view/system/queryTea2")

    @ResponseBody

    public ResponseEntity<?> queryTea2(){

        System.out.println(newname);

        List list = teaService.queryTea(newname);

        return  new ResponseEntity<List>(list, HttpStatus.OK);

    }

    /**

     * 分页查询

     * @param request

     * @param queryParams

     * @return

     */

    @RequestMapping("/view/system/queryTea")

    @ResponseBody

    public Map queryTea(HttpServletRequest request,@RequestParam Map queryParams){

        Map result = new HashMap();

        try {

            PageBean pageBean = new PageBean();

            pageBean.setPage(Integer.parseInt(queryParams.get("page").toString()));

            pageBean.setRows(Integer.parseInt(queryParams.get("limit").toString()));

            newname = (String) queryParams.get("keyword");

            pageBean.setQueryParams(queryParams);

            pageBean = teaService.mohuquery(pageBean);

            result.put("count", pageBean.getTotal());

            result.put("data", pageBean.getPageData());

            result.put("msg", "");

            result.put("code", 0);

        } catch (Exception e) {

            e.printStackTrace();

            result.put("count", 0);

            result.put("data", new ArrayList());

            result.put("msg", "");

            result.put("code", 500);

        }

        return result;

    }

}

------------------------------------------------------------数据页面

<!DOCTYPE html>

<html>

<head>

    <title></title>

    <meta charset="UTF-8"></meta>

    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"></meta>

    <meta name="viewport" content="width=device-width, initial-scale=1.0"></meta>

    <meta http-equiv="pragma" content="no-cache"></meta>

    <meta http-equiv="cache-control" content="no-cache"></meta>

    <meta http-equiv="expires" content="0"></meta>

    <link rel="stylesheet" href="../../js/layui/css/layui.css" media="all" />

    <link rel="stylesheet" href="//at.alicdn.com/t/font_tnyc012u2rlwstt9.css" media="all" />

    <link rel="stylesheet" href="../../css/role.css" media="all" />

    <style>

        .c1{

            float: left;

        }

    </style>

</head>

<body class="childrenBody">

<blockquote class="layui-elem-quote role_search">

    <div class="layui-inline">

        <div class="layui-input-inline">

            <input id="keyword" type="text" value="" placeholder="请输入关键字" class="layui-input search_input">

        </div>

        <a class="layui-btn search_btn" onclick="queryTea()">查询</a>

    </div>

    <div class="layui-inline">

        <a class="layui-btn layui-btn-normal newsAdd_btn" onclick="addTea('')">添加用户</a>

    </div>

    <div class="layui-inline">

        <a class="layui-btn layui-btn-danger batchDel" onclick="getDatas();">批量删除</a>

    </div>

</blockquote>

<div class="c1">

<table class="layui-hide" id="user" lay-filter="tools"></table>

</div>

<script type="text/html" id="tools">

    <a class="layui-btn layui-btn-xs" lay-event="edit">编辑</a>

    <a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">删除</a>

</script>

<script type="text/javascript" src="../../js/echarts.js"></script>

<div class="c1" id="main"  style="width: 500px;height:500px;"></div>

<script type="text/javascript" src="../../js/layui/layui.js"></script>

<script type="text/javascript" src="../../js/jquery2.1.4.js"></script>

<script type="text/javascript" src="../../js/DateFormat.js"></script>

<script type="text/javascript" src="../../js/view/system/tea.js"></script>

</body>

</html>

--------------------------------------------------------主要的tea.js

var table;

var layer;

layui.use([ 'layer', 'table', 'element' ], function() {

    table = layui.table;

    layer = layui.layer;

    // 执行一个 table 实例

    table.render({

        elem : '#user',

        height:472,

        url : '/view/system/queryTea',

        page :true, // 开启分页

        cols : [ [ // 表头

            {

                fixed : 'left',

                type : 'checkbox'

            }, {

                field : 'id',

                title : 'ID',

                width : 80,

                fixed : 'left'

            }, {

                field : 'name',

                title : '教师名称',

                width : 160

            }, {

                field : 'sex',

                title : '性别',

                width : 80,

                templet : function(d) {

                    if (d.sex == 1) {

                        return '男';

                    } else if (d.sex == 0) {

                        return '女';

                    }

                }

            }, {

                field : 'age',

                title : '年龄',

                width : 220,

            },{

                title : '操作',

                width : 300,

                align : 'center',

                toolbar : '#tools'

            } ] ]

    });

// 监听工具条

    table.on('tool(tools)', function(obj) { // 注：tool是工具条事件名，test是table原始容器的属性

        var data = obj.data // 获得当前行数据

            , layEvent = obj.event; // 获得 lay-event 对应的值

        if ('edit' == layEvent) {

            addTea(data.id)

        } else if ('del' == layEvent) {

            del(data.id);

        }

    });

    showBar();

});

function showBar() {

//生成图表

// 基于准备好的dom，初始化echarts实例

    var myChart = echarts.init(document.getElementById('main'));

    var m=0;

    var n=0;

    $.ajax({

        url:"/view/system/queryTea2",

        success:function(data){

            $.each(data,function (i,obj) {

                if(obj.sex==1){

                    m+=1;

                }else{

                    n+=1;

                }

            });

            // 指定图表的配置项和数据

            var option = {

                title: {

                    text: '男女比例'

                },

                tooltip: {},

                legend: {

                    data:['数量']

                },

                xAxis: {

                    data: ["男","女"]

                },

                yAxis: {},

                series: [{

                    name: '数量',

                    type: 'bar',

                    data: [m,n]

                }]

            };

// 使用刚指定的配置项和数据显示图表。

            myChart.setOption(option);

        }

    });

}

function queryTea(){

    var keyword = $("#keyword").val();

    table.reload('user', {

        where : {

            keyword : keyword

        },

        page : {

            curr : 1

        }

    });

    showBar()

    }

var index;

function addTea(id) {

    index = layer.open({

        type : 2,

        title : "角色信息",

        content : '/view/system/editTea?id=' + id

    });

    layer.full(index);

    queryTea();

}

function close() {

    layer.close(index);

}

function del(id) {

        layer.open({

            type : 1,

            content : '<div style="padding: 20px 80px;">确定删除记录?</div>',

            btn : [ '确定', '取消' ],

            yes : function(index, layero) {

                $.ajax({

                    url : "/view/system/deleteTea",

                    data : {

                        "id" : id

                    },

                    dataType : "text",

                    success : function(data) {

                        if(data==0){

                            layer.msg("删除成功！");

                            layer.close(index);

                            queryTea();

                        }else{

                            layer.msg("删除失败！");

                        }

                    },

                    error : function() {

                    }

                });

            }

        });

}

/**

 * 获取选中数据

 */

function getDatas(){

    var checkStatus = table.checkStatus('user');

    var data = checkStatus.data;

    var id = "";

    for(var i=0;i<data.length;i++){

        id += data[i].id;

        if(i<data.length-1){

            id += ",";

        }

    }

    if(data.length != 0){

        del(id);

    }

}

-----------------------------------------------------------------添加、修改页面

<!DOCTYPE html>

<head>

    <meta charset="utf-8">

    <title></title>

    <meta name="renderer" content="webkit">

    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">

    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">

    <meta name="apple-mobile-web-app-status-bar-style" content="black">

    <meta name="apple-mobile-web-app-capable" content="yes">

    <meta name="format-detection" content="telephone=no">

    <link rel="stylesheet" href="../../js/layui/css/layui.css" media="all" />

    <link rel="stylesheet" href="../../css/user.css" media="all" />

</head>

<body class="childrenBody">

<form class="layui-form changePwd">

    <input type="hidden" name="id"  id="id" th:value="${id}">

    <div class="layui-form-item">

        <label class="layui-form-label">教师名称</label>

        <div class="layui-input-block">

            <input type="text" name="name" value="" placeholder="教师名称" id="name"  lay-verify="required|name" class="layui-input pwd">

        </div>

    </div>

    <div class="layui-form-item">

        <label class="layui-form-label">教师性别</label>

        <div class="layui-input-block">

            <input type="radio" name="sex" value="0" title="女" checked="">

            <input type="radio" name="sex" value="1" title="男" >

        </div>

    </div>

    <div class="layui-form-item">

        <label class="layui-form-label">年龄</label>

        <div class="layui-input-block">

            <input type="text" name="age" value="" placeholder="年龄"  id="age" class="layui-input pwd">

        </div>

    </div>

    <div class="layui-form-item">

        <div class="layui-input-block">

            <button class="layui-btn" lay-submit="" lay-filter="*">保存</button>

            <button type="button" class="layui-btn layui-btn-primary" onclick="parent.close();">关闭</button>

        </div>

    </div>

</form>

<script type="text/javascript" src="../../js/layui/layui.js"></script>

<script type="text/javascript" src="../../js/jquery2.1.4.js"></script>

<script>

    $(function(){

        var id = $("#id").val();

        if(id != ""){

            $.ajax({

                url: "/view/system/queryById",

                data:{"id":id},

                dataType:"json",

                success: function(data){

                    $("#name").val(data.name);

                    $("#age").val(data.age);

                    $("input[name='sex']").eq(data.sex).attr("checked",'checked');

                },error:function(){

                }

            });

        }

    })

    layui.use(['form','layer' ], function() {

        var form = layui.form;

        var layer = layui.layer;

        // 添加验证规则

        form.verify({

            name : function(value, item) {

                value = value.trim();

                if (value.length < 0) {

                    return "请输入教师名称";

                }

            }

        });

        form.on('submit(*)', function(data) {

            var index = layer.msg('提交中，请稍候',{icon: 16,time:false,shade:0.8});

            var d = data.field;

            var url = "/view/system/addTea";

            if(d.id != ""){

                url = "/view/system/updateTea";

            }

            $.ajax({

                url: url,

                data:d,

                dataType:"text",

                success: function(data){

                    layer.close(index);

                    if(data == 0){

                        layer.msg("保存成功！");

                        parent.queryTea();

                        parent.close();

                    }else{

                        layer.msg("保存失败！");

                    }

                },error:function(){

                    layer.close(index);

                    layer.msg("保存失败！");

                }

            });

            return false;

        });

    });

</script>

</body>

</html>