Layui如何连接mysql数据库并操作CRUD
Layui如何连接MySQL数据库并操作CRUD？其实要解决这个问题也不难，这篇文章给出了相对应的分析和解答，下面我们一起来看看解决方法吧。
创新互联是一家专注于成都网站制作、做网站与策划设计,安徽网站建设哪家好?创新互联做网站,专注于网站建设10余年,网设计领域的专业建站公司;建站业务涵盖:安徽等地区。安徽做网站价格咨询:028-86922220
一、使用layui框架实现权限登陆显示树形菜单
1、首先引用一下layui框架所需的包
2、创建二星权限显现树形菜单所需要的表和所需要操作的书本类型表
用户表t_xm_user
树形菜单展现所需的权限表 t_book_menu
中间权限表t_usermenuid
书本类型表t_book_category2
准备下面代码中所需要的包工具包:
https://pan.baidu.com/s/1XnwIoJQUDyw0cJads5Pddw
在项目中配置与mysql数据库连接
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"%>
用户登录
欢迎后台管理界面平台
配置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框架实现增，删，改，查
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
URL地址：http://ybzwz.com/article/jhseoh.html
