Layui如何連接MySQL數(shù)據(jù)庫并操作CRUD？其實(shí)要解決這個(gè)問題也不難，這篇文章給出了相對應(yīng)的分析和解答，下面我們一起來看看解決方法吧。
創(chuàng)新互聯(lián)是一家專注于成都網(wǎng)站制作、做網(wǎng)站與策劃設(shè)計(jì),安徽網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)10余年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:安徽等地區(qū)。安徽做網(wǎng)站價(jià)格咨詢:028-86922220
一、使用layui框架實(shí)現(xiàn)權(quán)限登陸顯示樹形菜單
1、首先引用一下layui框架所需的包
2、創(chuàng)建二星權(quán)限顯現(xiàn)樹形菜單所需要的表和所需要操作的書本類型表
用戶表t_xm_user
樹形菜單展現(xiàn)所需的權(quán)限表 t_book_menu
中間權(quán)限表t_usermenuid
書本類型表t_book_category2
準(zhǔn)備下面代碼中所需要的包工具包:
https://pan.baidu.com/s/1XnwIoJQUDyw0cJads5Pddw
在項(xiàng)目中配置與mysql數(shù)據(jù)庫連接
3、權(quán)限登陸
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);
} /**
* 通過中間表查詢登陸用戶所對應(yīng)的權(quán)限
* @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所需數(shù)據(jù)后端程序員開發(fā)完畢
* @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(); //數(shù)據(jù)格式轉(zhuǎn)換
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、樹形菜單顯示
對樹形菜單的格式進(jìn)行描述 寫一個(gè)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類中使用了遞歸來轉(zhuǎn)換josn格式，因?yàn)閘ayui只能識別這種格式
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;/**
* 樹形權(quán)限管理類
* @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;
} /**
* 查詢子節(jié)點(diǎn)
* @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表的數(shù)據(jù)
* @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表的數(shù)據(jù)不符合easyui樹形展示的數(shù)據(jù)格式
* 需要轉(zhuǎn)換成easyui所能識別的數(shù)據(jù)格式
* @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框架實(shí)現(xiàn)增，刪，改，查
dao層，從數(shù)據(jù)庫中拿出數(shù)據(jù)并且對其進(jìn)行增，刪，改，查操作
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組件提交所需數(shù)據(jù)后端程序員處理完畢
* @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框架規(guī)劃顯示的頁面
userManage.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
書記類別管理
userManage.js
處理從后臺傳來的數(shù)據(jù)，并且根據(jù)前端代碼進(jìn)行展示
//執(zhí)行渲染layui.use(['table','layer','form'],function(){
var data=document.getElementById("ht").value;
var table =layui.table;
var layer=layui.layer;
var form = layui.form; /*展示數(shù)據(jù)表格 */
table.render({
elem:'#test'//表格id
,url:data+'/booktypeaction.action?methodName=select'//所對應(yīng)調(diào)用的接口
,method:'post' //提交方式 ,cols:[[ /*根據(jù)數(shù)據(jù)庫的表格所對應(yīng)的名稱 */
{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: '當(dāng)前時(shí)間',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');//書籍類別名稱 根據(jù) id來取值 console.log(booktypename.val()); // 執(zhí)行重載
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, //點(diǎn)擊遮罩關(guān)閉層
area: ['80%', '80%'],
content: $('#box1'),
btn: ['確定', '取消'],
yes: function (index, layero) {//確定執(zhí)行函數(shù) console.log(layero); //執(zhí)行添加方法
$.getJSON(data+"/booktypeaction.action?methodName=add", {
booktypename: $("#book_category_name").val(), //書本類型名稱 }, function (data) { /*根據(jù)后臺返回的參數(shù)來進(jìn)行判斷 */
if (data==1) {
layer.alert('添加成功', {icon: 1, title: '提示'}, function (i) {
layer.close(i);
layer.close(index);//關(guān)閉彈出層
$("#booktype")[0].reset()//重置form })
table.reload('testReload', {//重載表格 page: {
curr: 1 // 重新從第 1 頁開始 }
})
} else if(data==2){
layer.msg('添加失敗,請勿重復(fù)添加書本類別名稱')
}
})
}, cancel: function (index, layero) {//取消
$("#booktype")[0].reset()//重置form 根據(jù)id layer.close(index)
}
});
}
}
$('.layui-form .layui-btn').on('click', function () {
var type = $(this).data('type');
active[type] ? active[type].call(this) : '';
});
/*表格 行內(nèi)操作(編輯 以及 刪除 按鈕操作) */
table.on('tool(test)', function(obj){
var data = obj.data; //獲得當(dāng)前行數(shù)據(jù)
var urlex=document.getElementById("ht").value;
var tr=obj.tr//活動當(dāng)前行tr 的 DOM對象 console.log(data);
var layEvent = obj.event; //獲得 lay-event 對應(yīng)的值（也可以是表頭的 event 參數(shù)對應(yīng)的值）
if(layEvent === 'del'){ //刪除
layer.confirm('確定刪除嗎？',{title:'刪除'}, function(index){ //向服務(wù)端發(fā)送刪除指令og
$.getJSON(urlex+'/booktypeaction.action?methodName=del',{book_category_id:data.book_category_id}, function(ret){
layer.close(index);//關(guān)閉彈窗
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){ //根據(jù)后臺返回的參數(shù)，來進(jìn)行判斷
if(data>0){
layer.alert('編輯成功',{icon:1,title:'提示'},function(i){
layer.close(i);
layer.close(index);//關(guān)閉彈出層
$("#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數(shù)據(jù)庫并操作CRUD的方法，看完之后是否有所收獲呢？如果想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊，感謝各位的閱讀。
