Layui如何连接mysql数据库并操作CRUD

Layui如何连接MySQL数据库并操作CRUD?其实要解决这个问题也不难,这篇文章给出了相对应的分析和解答,下面我们一起来看看解决方法吧。

创新互联是一家专注于成都网站制作、做网站与策划设计,安徽网站建设哪家好?创新互联做网站,专注于网站建设10余年,网设计领域的专业建站公司;建站业务涵盖:安徽等地区。安徽做网站价格咨询:028-86922220

一、使用layui框架实现权限登陆显示树形菜单

1、首先引用一下layui框架所需的包

 Layui如何连接mysql数据库并操作CRUD

2、创建二星权限显现树形菜单所需要的表和所需要操作的书本类型表

用户表t_xm_user

树形菜单展现所需的权限表 t_book_menu

Layui如何连接mysql数据库并操作CRUD

中间权限表t_usermenuid

Layui如何连接mysql数据库并操作CRUD

书本类型表t_book_category2

准备下面代码中所需要的包工具包:

https://pan.baidu.com/s/1XnwIoJQUDyw0cJads5Pddw

在项目中配置与mysql数据库连接

 Layui如何连接mysql数据库并操作CRUD

3、权限登陆

userDao类

package com.ht.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.ht.daoimpl.IUserDao;
import com.ht.util.JsonBaseDao;
import com.ht.util.JsonUtils;
import com.ht.util.PageBean;
import com.ht.util.StringUtils;public class UserDao extends JsonBaseDao implements IUserDao{    /**
     * 登陆查询用户表
     * @param paMap
     * @param pageBean
     * @return
     * @throws SQLException 
     * @throws IllegalAccessException 
     * @throws InstantiationException 
     */
    public List> list(Map paMap,PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException{
        String sql="select * from t_xm_user where true";
        String user_name=JsonUtils.getParamVal(paMap, "user_name");
        String user_pwd=JsonUtils.getParamVal(paMap, "user_pwd");        if (StringUtils.isNotBlank(user_name)) {
            sql= sql +" and user_name ="+user_name;
        }        if (StringUtils.isNotBlank(user_pwd)) {
            sql= sql +" and user_pwd ="+user_pwd;
        }        return super.executeQuery(sql, pageBean);
    }    /**
     * 通过中间表查询登陆用户所对应的权限
     * @param paMap
     * @param pageBean
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws SQLException     */
    public List> listMenu(String user_id,PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException{
        String sql="select * from t_usermenuid where true";        if (StringUtils.isNotBlank(user_id)) {
            sql= sql +" and user_id ="+user_id;
        }        return super.executeQuery(sql, pageBean);
    }
}

web层

userAction类

package com.ht.web;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.fasterxml.jackson.databind.ObjectMapper;import com.ht.dao.UserDao;import com.zking.framework.ActionSupport;import com.ht.util.PageBean;import com.ht.util.ResponseUtil;import com.ht.util.StringUtils;public class UserAction extends ActionSupport {    private UserDao uesrDao = new UserDao();    public String login(HttpServletRequest req, HttpServletResponse rep) {        try {
            List> list = this.uesrDao.list(req.getParameterMap(), null);            if (list != null && list.size() > 0) {
                List> listmenu = this.uesrDao.listMenu(req.getParameter("user_name"), null);
                StringBuffer sb = new StringBuffer();                for (Map map : listmenu) {
                    sb.append("," + map.get("Menuid"));
                }
                req.getSession().setAttribute("menuhid", sb.substring(1));                return "index";
            }
        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();
        }        return "login";
    }    /**
     * datagrid所需数据后端程序员开发完毕
     * @param req
     * @param resp
     * @return
     */
    public String list(HttpServletRequest req,HttpServletResponse resp){        try {
            PageBean pageBean=new PageBean();
            pageBean.setRequest(req);
            List> list = this.uesrDao.list(req.getParameterMap(), pageBean);
            ObjectMapper om=new ObjectMapper();            //数据格式转换
            Map map=new HashMap<>();
            map.put("total", pageBean.getTotal());
            map.put("rows", list);
            ResponseUtil.write(resp, om.writeValueAsString(map));
        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();
        } 
        return null;
    }
    
}

login.jsp 显示登陆界面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>




    
    
    
    
    
    
    
    
    
    
    
    
    用户登录


    

欢迎后台管理界面平台

  • X X X X

配置xml


        
    

效果如下:

4、树形菜单显示

对树形菜单的格式进行描述  写一个TreeNode 类

package com.ht.entity;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;public class TreeNode {    private String id;    private String name;    private Map attributes = new HashMap<>();    private List children = new ArrayList<>();    public String getId() {        return id;
    }    public void setId(String id) {        this.id = id;
    }    public String getName() {        return name;
    }    public void setName(String name) {        this.name = name;
    }    public Map getAttributes() {        return attributes;
    }    public void setAttributes(Map attributes) {        this.attributes = attributes;
    }    public List getChildren() {        return children;
    }    public void setChildren(List children) {        this.children = children;
    }    public TreeNode(String id, String text, Map attributes, List children) {
        super();        this.id = id;        this.name = name;        this.attributes = attributes;        this.children = children;
    }    public TreeNode() {
        super();
    }

    @Override    public String toString() {        return "TreeNode [id=" + id + ", name=" + name + ", attributes=" + attributes + ", children=" + children + "]";
    }

}

dao层 MenuDao类

在dao类中使用了递归来转换josn格式,因为layui只能识别这种格式

package com.ht.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.ht.daoimpl.IMenuDao;
import com.ht.entity.TreeNode;
import com.ht.util.JsonBaseDao;
import com.ht.util.JsonUtils;
import com.ht.util.PageBean;
import com.ht.util.StringUtils;/**
 * 树形权限管理类
 * @author Administrator
 * */public class MenuDao extends JsonBaseDao implements IMenuDao{    /**
     * 
     * @param map   req.getParameterMap
     * @param pageBean  分页
     * @return
     * @throws Exception 
     */
    public List list(Map map,PageBean pageBean) throws Exception{
        List> listMenu=this.listMenuSef(map, pageBean);
        List treeNodeList=new ArrayList<>();
        menuList2TreeNodeList(listMenu, treeNodeList);        return treeNodeList;
    }    /**
     * 查询子节点
     * @param map
     * @param pageBean
     * @return
     * @throws Exception     */
    public List> listMenuSef(Map map,PageBean pageBean)throws Exception{
        String sql=" select * from t_book_menu where true";
        String id=JsonUtils.getParamVal(map, "menuhid");        if(StringUtils.isNotBlank(id)) {
            sql= sql + " and Menuid in ("+id+")";    
        }        else {  
            sql= sql + " and Menuid =-1";
        }        return super.executeQuery(sql, pageBean);
    }    /**
     * 查询Menu表的数据
     * @param map
     * @param pageBean
     * @return     */
    public List> listMenu(Map map,PageBean pageBean)throws Exception{
        String sql=" select * from t_book_menu where true";
        String id=JsonUtils.getParamVal(map, "id");        if(StringUtils.isNotBlank(id)) {
            sql= sql + " and parentid ="+id;    
        }        else {
            sql= sql + " and parentid = -1";
        }        return super.executeQuery(sql, pageBean);
    }    
    /**
     * {Menuid:1,....[]}
     * ->{id:1,....[]}
     * menu表的数据不符合easyui树形展示的数据格式
     * 需要转换成easyui所能识别的数据格式
     * @param map
     * @param reTreeNode
     * @throws Exception 
     */
    public void menu2TreeNode(Map map, TreeNode treeNode) throws Exception {
        treeNode.setId(map.get("Menuid").toString());
        treeNode.setName(map.get("Menuname").toString());
        treeNode.setAttributes(map);
        Map jspMap=new HashMap<>();
        jspMap.put("id", new String[] {treeNode.getId()});        this.listMenu(jspMap, null);
        List> listMenu=this.listMenu(jspMap, null);
        List treeNodeList=new ArrayList<>();
        menuList2TreeNodeList(listMenu, treeNodeList);
        treeNode.setChildren(treeNodeList);
    }    /**
     * [{Menuid:1,....[]},{Menuid:2,....[]}]
     * ->[{id:1,....[]},{id:2,....[]}]
     * @param mapList
     * @param treeNodeList
     * @throws Exception     */
    public void menuList2TreeNodeList(List> mapList, List treeNodeList)throws Exception {
        TreeNode treeNode =null;        for (Map map : mapList) {
            treeNode =new TreeNode();
            menu2TreeNode(map, treeNode);
            treeNodeList.add(treeNode);
        }
    }
}

web层

MenuAction类

package com.ht.web;import java.io.PrintWriter;import java.sql.SQLException;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.fasterxml.jackson.databind.ObjectMapper;import com.ht.biz.MenuBiz;import com.ht.bizipml.IMenuBiz;import com.ht.dao.MenuDao;import com.ht.entity.TreeNode;import com.zking.framework.ActionSupport;import com.ht.util.ResponseUtil;public class MenuAction extends ActionSupport {    private IMenuBiz menuDao = new MenuBiz();    public String treeMenu(HttpServletRequest req, HttpServletResponse response) throws Exception {
        @SuppressWarnings("unchecked")
        List list = this.menuDao.list(req.getParameterMap(), null);
        ObjectMapper om = new ObjectMapper();
        String jsonStr = om.writeValueAsString(list);
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out=response.getWriter();
        out.println(jsonStr);
        out.flush();
        out.close();        return null;
    }
}

index.jsp  显示树形菜单

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>


    
    
    
    
    
    
     
    




配置xml


        
    

效果图:

Layui如何连接mysql数据库并操作CRUD

二、使用layui框架实现增,删,改,查

dao层,从数据库中拿出数据并且对其进行增,删,改,查操作

package com.ht.dao;import java.sql.SQLException;import java.util.List;import java.util.Map;import com.ht.daoimpl.IBooktypeDao;import com.ht.util.JsonBaseDao;import com.ht.util.JsonUtils;import com.ht.util.PageBean;import com.ht.util.StringUtils;/**
 * 书籍类别管理
 * @author Administrator
 * */public class BooktypeDao extends JsonBaseDao implements IBooktypeDao{    /**
     * 编辑用户信息 查询书本类型表
     * @param paMap
     * @return
     * @throws Exception     */
    public int edit(Map paMap) throws Exception {
        String sql = "update t_book_category2 set book_category_name=? where book_category_id=?";        return super.executeUpdate(sql, new String[] { "book_category_name","book_category_id" }, paMap);
    }    /**
     * 新增 查询书本类型表
     * @param paMap
     * @return
     * @throws Exception     */
    public int add(Map paMap) throws Exception {
        String sql = "INSERT INTO t_book_category2(book_category_name) VALUES(?)";        return super.executeUpdate(sql, new String[] { "book_category_name" }, paMap);
    }    /**
     * 删除 查询书本类型表
     * @param paMap
     * @return
     * @throws Exception     */
    public int remove(Map paMap) throws Exception {
        String sql = "DELETE FROM t_book_category2 WHERE  book_category_id=?";        return super.executeUpdate(sql, new String[] { "book_category_id" }, paMap);
    }    /**
     * 查询的方法
     * @param paMap
     * @param pageBean
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws SQLException     */
    public List> select(Map paMap, PageBean pageBean)            throws InstantiationException, IllegalAccessException, SQLException {
        String sql = "select * from t_book_category2 where true";
        String book_category_name = JsonUtils.getParamVal(paMap, "book_category_name");        if (StringUtils.isNotBlank(book_category_name)) {
            sql = sql + " and book_category_name  like '%" + book_category_name + "%'";
        }        return super.executeQuery(sql, pageBean);
    }
}

web层

BooktypeAction类

package com.ht.web;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.fasterxml.jackson.databind.ObjectMapper;import com.ht.biz.BooktypeBiz;import com.ht.bizipml.IBooktypeBiz;import com.ht.dao.BooktypeDao;import com.zking.framework.ActionSupport;import com.ht.util.PageBean;import com.ht.util.ResponseUtil;public class BooktypeAction extends ActionSupport{    private IBooktypeBiz booktypeDao=new BooktypeBiz();    /**
     * 查询的请求方法
     * @param req
     * @param resp
     * @return
     */
    public String select(HttpServletRequest req,HttpServletResponse resp) {        try {
            PageBean pageBean=new PageBean();
            pageBean.setRequest(req);
            List> list = this.booktypeDao.select(req.getParameterMap(), pageBean);
            ObjectMapper om=new ObjectMapper();
            Map map=new HashMap<>();
            map.put("total", pageBean.getTotal());
            map.put("rows", list);
            ResponseUtil.write(resp, om.writeValueAsString(map));
        } catch (Exception e) {            // TODO: handle exception            e.printStackTrace();
        }        return "index";
        
    }    /**
     * form组件提交所需数据后端程序员处理完毕
     * @param req
     * @param resp
     * @return
     */
    public String edit(HttpServletRequest req,HttpServletResponse resp){        try {            int edit = this.booktypeDao.edit(req.getParameterMap());
            ObjectMapper om=new ObjectMapper();
            ResponseUtil.write(resp, om.writeValueAsString(edit));
        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();
        } 
        return null;
    }    /**
     * 新增的请求方法
     * @param req
     * @param resp
     * @return
     */
    public String add(HttpServletRequest req,HttpServletResponse resp){        try {            int add = this.booktypeDao.add(req.getParameterMap());
            ObjectMapper om=new ObjectMapper();
            ResponseUtil.write(resp, om.writeValueAsString(add));
        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();
        } 
        return null;
    }    /**
     * 删除的请求方法
     * @param req
     * @param resp
     * @return
     */
    public String remove(HttpServletRequest req,HttpServletResponse resp) {        try {            int remove=this.booktypeDao.remove(req.getParameterMap());
            ObjectMapper om=new ObjectMapper();
            ResponseUtil.write(resp, om.writeValueAsString(remove));
        } catch (Exception e) {            // TODO: handle exception            e.printStackTrace();
        }        return null;
    }
}

前台代码,用layui框架规划显示的页面

userManage.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>







书记类别管理


书籍类别管理 分类列表



userManage.js

处理从后台传来的数据,并且根据前端代码进行展示

//执行渲染layui.use(['table','layer','form'],function(){
    var data=document.getElementById("ht").value;
    var table =layui.table;
    var layer=layui.layer;
    var form = layui.form;     /*展示数据表格  */
    table.render({
          elem:'#test'//表格id
        ,url:data+'/booktypeaction.action?methodName=select'//所对应调用的接口
        ,method:'post'        //提交方式        ,cols:[[            /*根据数据库的表格所对应的名称  */
             {field:'book_category_id',height:80, width:300, title: '书籍类别序号', sort: true}
             ,{field:'book_category_name', height:80,width:300, title: '书籍类别名称'}
             ,{field:'createtime',height:80, width:300, title: '当前时间',templet:"

{{layui.util.toDateString(d.createtime, 'yyyy-MM-dd HH:mm:ss')}}

"} ,{field:'right',height:80, width:300, title: '操作', toolbar:'#lineBtns'}//操作栏 ]] ,page:'true'//分页 , id: 'testReload' }); //上方菜单操作栏(查询、以及 增加 按钮 ) var $ = layui.$, active = { //查询 reload: function () { var book_category_name = $('#book_category_name');//书籍类别名称 根据 id来取值 console.log(booktypename.val()); // 执行重载 table.reload('testReload', { page: { curr: 1 // 重新从第 1 页开始 }, where: { key: 'book_category_name', book_category_name: book_category_name.val(), } }); }, add: function () { //添加 layer.open({//弹出框 type: 1, title: '添加书本类别', maxmin: true, shadeClose: true, //点击遮罩关闭层 area: ['80%', '80%'], content: $('#box1'), btn: ['确定', '取消'], yes: function (index, layero) {//确定执行函数 console.log(layero); //执行添加方法 $.getJSON(data+"/booktypeaction.action?methodName=add", { booktypename: $("#book_category_name").val(), //书本类型名称 }, function (data) { /*根据后台返回的参数来进行判断 */ if (data==1) { layer.alert('添加成功', {icon: 1, title: '提示'}, function (i) { layer.close(i); layer.close(index);//关闭弹出层 $("#booktype")[0].reset()//重置form }) table.reload('testReload', {//重载表格 page: { curr: 1 // 重新从第 1 页开始 } }) } else if(data==2){ layer.msg('添加失败,请勿重复添加书本类别名称') } }) }, cancel: function (index, layero) {//取消 $("#booktype")[0].reset()//重置form 根据id layer.close(index) } }); } } $('.layui-form .layui-btn').on('click', function () { var type = $(this).data('type'); active[type] ? active[type].call(this) : ''; }); /*表格 行内操作(编辑 以及 删除 按钮操作) */ table.on('tool(test)', function(obj){ var data = obj.data; //获得当前行数据 var urlex=document.getElementById("ht").value; var tr=obj.tr//活动当前行tr 的 DOM对象 console.log(data); var layEvent = obj.event; //获得 lay-event 对应的值(也可以是表头的 event 参数对应的值) if(layEvent === 'del'){ //删除 layer.confirm('确定删除吗?',{title:'删除'}, function(index){ //向服务端发送删除指令og $.getJSON(urlex+'/booktypeaction.action?methodName=del',{book_category_id:data.book_category_id}, function(ret){ layer.close(index);//关闭弹窗 table.reload('testReload', {//重载表格 page: { curr: 1 // 重新从第 1 页开始 } }) }); layer.close(index); }); } else if(layEvent === 'edit'){ //编辑 layer.open({ type: 1 //Page层类型 ,skin: 'layui-layer-molv' ,area: ['380px', '270px'] ,title: ['编辑书本类别信息','font-size:18px'] ,btn: ['确定', '取消'] ,shadeClose: true ,shade: 0 //遮罩透明度 ,maxmin: true //允许全屏最小化 ,content:$('#bt') //弹窗id ,success:function(layero,index){ $('#book_category_id').val(data.book_category_id); $('#book_category_name').val(data.book_category_name); },yes:function(index,layero){ /* $.ajaxSettings.async = false; */ $.getJSON(urlex+'/booktypeAction.action?methodName=edit',{ book_category_id: $('#book_category_id').val(), book_category_name: $('#book_category_name').val(), book_category_id: data.book_category_id, },function(data){ //根据后台返回的参数,来进行判断 if(data>0){ layer.alert('编辑成功',{icon:1,title:'提示'},function(i){ layer.close(i); layer.close(index);//关闭弹出层 $("#booktype")[0].reset()//重置form }) table.reload('testReload',{//重载表格 page:{ curr:1 } }) } }); } }); } }); });/** * 顶部搜索框 模糊查询 * @returns *//*function dingselect(){ var select = $('#ks').val(); $("#dg").datagrid({ url : $("#txc").val()+'/booktypeaction.action?methodName=select&book_category_name='+select, }) }*//** * 查询增删改方法 * @returns *//*function userManage(){ var url = "booktypeaction.action?methodName="+$("#xm").attr("data"); $('#ff').form('submit', { url:url, success: function(param){ $('#dd').dialog('close'); $('#dg').datagrid('reload'); $('#ff').form('clear'); } }); }*//** * 修改方法 * @returns *//*function edit(){ var row = $('#dg').datagrid('getSelected'); if(row){ $('#ff').form('load', row); $('#dd').dialog('open'); $('#xm').html('编辑'); $('#xm').attr("data","edit"); } }*//** * 新增方法 * @returns *//*function add(){ $('#ff').form('clear'); $('#dd').dialog('open'); $('#xm').html('新增'); $('#xm').attr("data","add"); }*//** * 删除方法 * @returns *//*function remove(){ var data=$('#dg').datagrid('getSelected'); $.ajax({ url : $("#txc").val()+'/booktypeaction.action?methodName=remove&book_category_id='+data.book_category_id, success: function(param){ $('#dg').datagrid('reload'); } }) }*/

配置xml


        
        
    

以上就是Layui连接mysql数据库并操作CRUD的方法,看完之后是否有所收获呢?如果想了解更多相关内容,欢迎关注创新互联行业资讯,感谢各位的阅读。


名称栏目:Layui如何连接mysql数据库并操作CRUD
标题路径:http://scyanting.com/article/jhseoh.html