论坛 › nodejs › easyui+nodejs+sqlserver增删改查实现

xywuyiba8 发表于 2017-2-22 12:41:26

easyui+nodejs+sqlserver增删改查实现

　　用到的模块或者技术：

　　Express: http://www.expressjs.com.cn/4x/api.html#express
　　Easyui: http://www.jeasyui.com/documentation/index.php#
　　express-session：https://www.npmjs.com/package/express-session#resave
　　node-mssql: http://csdoc.org/
　　Redis: http://redis.io/
　　art-Template：http://www.jq22.com/jquery-info1097(一种高性能js模板引擎)
　　connect-redis：https://www.npmjs.com/package/connect-redis(用来将服务端session同步至分布式redis缓存)

　　先来看下整体效果：

　　图1 登录页面

　　图2 用户管理主界面

　　图3 系统设置-密码修改

　　图4 系统设置-退出系统
　　1、项目结构

　　routes下user.js作为控制器负责请求分发处理，views下存放user文件夹存放user模块全部视图html资源，系统入口为login.html文件，进行登录操作。
　　2、数据库模块
　　数据库连接使用node-mssql实现sqlserver数据库连接，对增删改查做了简单封装，没有统一整理显得比较凌乱，详细代码如下：



var node_mssql = require('node-mssql');
var express = require('express');
/* add configuration to query object */
var queryObj = new node_mssql.Query({
host: 'localhost',
port: 1433,
username: 'sa',
password: 'sa123456',
db: 'mydatabase'
});
var insert = function (data, insertTable,callback) {
queryObj.table(insertTable);
queryObj.data(data);
queryObj.insert(function (results) {
callback(results);
}, function (err, sql) {
if (err) {
console.log(err);
}
});
};
var list = function (successCallback, option, listTable) {
queryObj.table(listTable);
queryObj.where(option);
queryObj.select(function (results) {
successCallback(results);
}, function (err, sql) {
if (err)
console.log(err);
});
};
var update = function (data, option, updateTable,successCallback) {
queryObj.table(updateTable);
queryObj.data(data);
queryObj.where(option);
queryObj.update(function (results) {
successCallback(results);
}, function (err, sql) {
if (err)
console.log(err);
//console.log(sql);
    });
};

var del = function (ids, table,successCallback,failedCallback) {
queryObj.query("delete from " + table + " where id in ( " + ids + " )",successCallback,failedCallback);
};
var list_sql = function(sql,successCallback){
queryObj.query(sql, successCallback,function(err){
console.log(err);
});
};
var total = function(sql,callback){
queryObj.query(sql,callback,function(err){
console.log(err);
});
};
var findUserById = function(sql,successCallback,failedCallback){
queryObj.query(sql,successCallback,failedCallback);
};
exports.list = list;
exports.list_sql = list_sql;
exports.insert = insert;
exports.update = update;
exports.del = del;
exports.total = total;
exports.findUserById = findUserById;
　　3、修改默认引擎
　　修改默认引擎为html而非ejs，修改操作如下，具体可查询官网说明：



// view engine setup
var template = require('art-template');
template.config('base', '');
template.config('extname', '.html');
app.engine('.html', template.__express);
app.set('view engine', 'html');
app.set('views', __dirname + '/views');
　　4、connect-redis的使用



var express = require('express');
var session = require("express-session");
var cookieParser = require('cookie-parser');
var RedisStore = require("connect-redis")(session);   //用于将session保存至redis中
var app = express();
app.use(session({
store: new RedisStore({
host: 'localhost',
port: 6379
}),
secret: '1234567890QWERTY'
}));
　　在app.js中加入该设置后，在js代码中通过req.session.key= value，可以直接保存key:value的数值对保存至session从而直接同步至redis中，方便分布式下实现session会话的统一管理不丢失。
　　5、登录处理



router.post("/user/login", function (req, res) {
var username = req.param("username");
var password = req.param("password");
var obj = {"username": username, "password": password};
dbConn.list(function (results) {
if (results == "") {
res.render("login", {"err": "用户名或密码错误"});
} else {
dbConn.findUserById("select * from dbo.t_user where id = " + results.id, function(recordset){
req.session.user = recordset;
console.log(recordset);
res.render("user/userMain",{"currentUser":recordset});
},function(err,sql){
console.log(err);
});
}
},obj, "dbo.t_user");
});
　　判断results是否为空，如果为空则表示用户名或者密码错误（前台已进行非空校验），直接跳转至login界面，显示：用户名或密码错误；
　　如果results非空，一是将用户信息存至session，二是将用户信息render至跳转页面，这样直接在页面中可以显示当前用户登录信息。
　　6、url控制拦截



app.use('/', user);
app.use(function (req, res, next) {
var url = req.originalUrl;
console.log("############" + url);
if (url != "/" && !req.session.user) {
return res.redirect("/");
}
next();
});
　　在业务拦截处理后面加入以上代码片段进行url拦截，防止非法登录访问。如果请求的url非根目录并且当且用户没有session(表示未登录访问)，直接redirect至登录页面。
　　7、user.js请求处理模块



/**
* Created by Administrator on 2015/8/28.
*/
var express = require('express');
var dbConn = require("./dbConn.js");
var router = express.Router();
/* GET home page. */
//路由分发登录请求
router.get('/', function (req, res, next) {
res.render('login');
});
//登录处理
router.post("/user/login", function (req, res) {
var username = req.param("username");
var password = req.param("password");
var obj = {"username": username, "password": password};
dbConn.list(function (results) {
if (results == "") {
res.render("login", {"err": "用户名或密码错误"});
} else {
dbConn.findUserById("select * from dbo.t_user where id = " + results.id, function(recordset){
req.session.user = recordset;
console.log(recordset);
res.render("user/userMain",{"currentUser":recordset});
},function(err,sql){
console.log(err);
});
}
},obj, "dbo.t_user");
});
router.get("/user/userManage.html", function (req, res) {
res.render("user/userManage", {"msg": "hello userManage"});
});
//查询
router.post("/user/list", function (req, res) {
console.dir(req.session.user);
var count = 0;
var page = req.param("page");
var rows = req.param("rows");
var username = req.body.username;
if(typeof(username) == "undefined"){
username = "";
}
console.log(page + ">" + rows + ">" + username);
var options = {page:page,rows:rows,username:username};
var jsonArray,sql;
if (options != "") {
if (options.username != "") {
//sql = "select top(" +rows+ ") * from dbo.t_user where username like '%"+options.username+"%' and id not in(select top(" + (page-1) * rows + ") id from dbo.t_user ) ";    /*最后一页查询数据有误，还需改进*/
sql = "select top(" +rows+ ") * from (select ROW_NUMBER() over( order by id) as row,* from dbo.t_user where username like '%"+options.username+"%' ) a where a.row > " + (page-1)*rows;
console.log(sql);
dbConn.list_sql(sql,function(recordset){
dbConn.list_sql("select * from dbo.t_user where username like '%" + options.username + "%'",function(result){
jsonArray = {rows:recordset,total:result.length};
res.json(jsonArray);
});
});
}
if (options.username == "") {
sql = "select top(" +rows+ ") * from dbo.t_user where id not in(select top(" + (page-1) * rows + ") id from dbo.t_user )";
//sql = "select top(" +rows+ ") * from (select ROW_NUMBER() over( order by id) as row,* from dbo.t_user) a where a.row > " + (page-1)*rows;
            console.log(sql);
dbConn.list_sql(sql,function(recordset){
dbConn.list_sql("select * from dbo.t_user where username like '%" + options.username + "%'",function(result){
jsonArray = {rows:recordset,total:result.length};
res.json(jsonArray);
});
});
}
}
});
//添加或者修改
router.post("/user/add/:id(\\d+)",function(req,res){
var obj = req.body;
dbConn.update(obj,{id:req.params.id},"dbo.t_user",function(results){
res.json({success:true});
});
});
router.post("/user/add",function(req,res){
var obj = req.body;
dbConn.insert(obj,"dbo.t_user",function(results){
res.json({success:true});
});
});

//删除
router.post("/user/delete",function(req,res){
var ids = req.param("ids");
console.log(ids);
dbConn.del(ids,"dbo.t_user",function(recordset){
res.json({success:true});
},function(err,sql){
console.log(err);
});
});
router.get("/user/exit",function(req,res){
//清除系统session
req.session.destroy(function(err) {
console.log(err);
});
res.redirect("/");
});
router.post("/user/modifyPass",function(req,res){
var user = req.session.user;
var oldPwd = req.body.oldPwd;
dbConn.list(function(results){
if(results.length == 0){
res.json({success:false});
}else{
var id = user.id;
var data = {password:req.body.rPwd};
//console.log(req.body);
dbConn.update(data,{id:id},"dbo.t_user",function(results){
res.json({success:true});
});
}
},{username:user.username,password:oldPwd},"dbo.t_user");

});
module.exports = router;
　　8、userMain.html用户页面主模块



<!DOCTYPE html>
<html lang="en">
<meta charset="UTF-8">
<title>Full Layout - jQuery EasyUI Demo</title>
<link rel="stylesheet" type="text/css" href="/javascripts/jquery-easyui-1.4.3/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="/javascripts/jquery-easyui-1.4.3/themes/icon.css">
<link rel="stylesheet" type="text/css" href="/javascripts/jquery-easyui-1.4.3/demo/demo.css">
<script type="text/javascript" src="/javascripts/jquery-easyui-1.4.3/jquery.min.js"></script>
<script type="text/javascript" src="/javascripts/jquery-easyui-1.4.3/jquery.easyui.min.js"></script>
<script type="text/javascript" src="/javascripts/jquery-easyui-1.4.3/locale/easyui-lang-zh_CN.js"></script>
<script type="text/javascript" src="/javascripts/disableMove.js"></script>
<script>
var url;
function openTab(text,url,iconCls){
if($("#tabs").tabs("exists",text)){
$("#tabs").tabs("select",text);
}else{
var content = "<iframe frameborder=0 scrolling='auto' style='width:100%;height:100%;' src='/user/"+url+"'></iframe>";
$("#tabs").tabs("add",{
title:text,
iconCls:iconCls,
closable:true,
content:content
});
}
}
function openModifyPassDlg(){
$("#fm").form("reset");
$("#dlg").dialog("open").dialog("setTitle","修改密码");
}
function savePass(){
//save处理
      $("#fm").form("submit",{
url: "/user/modifyPass",
onSubmit:function(){
if($("#oldPwd").val() == ""){
$.messager.alert("系统提示","请输入您的原密码");
return false;
}
if($("#newPwd").val() == ""){
$.messager.alert("系统提示","请输入新密码");
return false;
}
if($("#rPwd").val() == ""){
$.messager.alert("系统提示","请再次确认密码");
return false;
}
if($("#newPwd").val() != $("#rPwd").val()){
$.messager.alert("系统提示","两次输入密码不匹配，请重新输入");
return false;
}
return $(this).form("validate");
},
success:function(result){
var result = eval('('+result+')');
if(result.success){
$.messager.alert("系统提示","密码修改成功，下次重启生效");
$("#dlg").dialog("close");
$("#fm").form("reset");
}else{
$.messager.alert("系统提示","密码修改失败");
return;
}
}
});
}
function userExit(){
$.messager.confirm('确认退出','您确定退出系统？',function(r){
if (r){
window.location.href = "/user/exit";    //跳转
            }
return false;
});
}
function closePassModifyDialog(){
$("#dlg").dialog("close");
$("#fm").form("reset");
}
</script>
</head>
<body class="easyui-layout">
<div region="north" style="height:78px;background-color: #E0ECFF;">
<table style="padding:5px;width:100%;">
<tr>
<td>&nbsp;</td>
</tr>
<tr>
<td valign="bottom" align="right" width="80%">&nbsp; &nbsp;<strong>欢迎&nbsp;&nbsp;{{currentUser.username}}</strong>&nbsp;
&nbsp;&nbsp;&nbsp;
</td>
</tr>
</table>
</div>
<div region="center">
<div class="easyui-tabs" fit="true" id="tabs">
<div title="首页" data-options="iconCls:'icon-home'">
<iframe frameborder=0 scrolling='auto' style='width:100%;height:100%;' src='http://www.cnblogs.com/caiya928/'></iframe>
</div>
</div>
</div>
<div region="west" style="width:200px;" title="导航菜单" split="true">
<div class="easyui-accordion" data-options="fit:true,border:false">
<div title="用户管理" data-options="iconCls:'icon-user',selected:true"
style="padding:5px;">
<a href="javascript:openTab('用户管理','userManage.html','icon-user_comment')"
class="easyui-linkbutton"
data-options="plain:true,iconCls:'icon-user_comment'">用户管理</a>
</div>
<div title="系统设置" data-options="iconCls:'icon-wrench',selected:true"
style="padding:5px;">
<a href="javascript:openModifyPassDlg()" class="easyui-linkbutton"
data-options="plain:true,iconCls:'icon-vcard_key'">密码修改</a></br>
<a href="javascript:userExit()" class="easyui-linkbutton"
data-options="plain:true,iconCls:'icon-2012080412301'">退出系统</a>
</div>
</div>
</div>
<div region="south" style="height:25px;padding:5px;" align="center">
@copyright &nbsp;&nbsp;博客园&nbsp;&nbsp;<a href="http://www.cnblogs.com/caiya928/">http://www.cnblogs.com/caiya928/</a>
</div>
<div id="dlg" class="easyui-dialog"
style="width: 400px;height:300px;padding: 10px 20px" closed="true"
buttons="#dlg-buttons">
<form id="fm" method="post">
<table cellspacing="10px">
<tr>
<td>用户名：</td>
<td><input type="text" id="username" name="username" value="{{currentUser.username}}"
class="easyui-validatebox" readonly="readonly" style="width: 230px"/></td>
</tr>
<tr>
<td>当前密码：</td>
<td><input type="password" id="oldPwd" name="oldPwd"
class="easyui-validatebox" required="true" style="width: 100%;"/></td>
</tr>
<tr>
<td>新密码：</td>
<td><input type="password" id="newPwd"
name="newPwd" class="easyui-validatebox" required="true"
style="width: 100%;"/></td>
</tr>
<tr>
<td>确认密码：</td>
<td>
<input id="rPwd" name="rPwd" type="password" class="easyui-validatebox" data-options=""
required="required"style="width: 100%">
</td>
</tr>
</table>
</form>
</div>
<div id="dlg-buttons">
<a href="javascript:savePass()" class="easyui-linkbutton"
iconCls="icon-ok">保存</a> <a
href="javascript:closePassModifyDialog()"
class="easyui-linkbutton" iconCls="icon-cancel">关闭</a>
</div>
</body>
</html>
　　9、userManage.html用户管理模块详细



<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<link rel="stylesheet" type="text/css" href="/javascripts/jquery-easyui-1.4.3/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="/javascripts/jquery-easyui-1.4.3/themes/icon.css">
<link rel="stylesheet" type="text/css" href="/javascripts/jquery-easyui-1.4.3/demo/demo.css">
<script type="text/javascript" src="/javascripts/jquery-easyui-1.4.3/jquery.min.js"></script>
<script type="text/javascript" src="/javascripts/jquery-easyui-1.4.3/jquery.easyui.min.js"></script>
<script type="text/javascript" src="/javascripts/jquery-easyui-1.4.3/locale/easyui-lang-zh_CN.js"></script>
<script type="text/javascript" src="/javascripts/disableMove.js"></script>
<script type="text/javascript">
var url;
function saveUser(){
$('#fm').form('submit', {
url:url,
onSubmit:function(){
if($("#username").val() == ""){
$.messager.alert("系统提示","请填写用户名");
return false;
}
if($("#password").val() == ""){
$.messager.alert("系统提示","请填写密码");
return false;
}
if($("#sex").combobox("getValue") == ""){
$.messager.alert("系统提示","请选择性别");
return false;
}
if($("#birthday").datebox("getValue") == ""){
$.messager.alert("系统提示","请选择出生日期");
return false;
}
if($("#email").val() == ""){
$.messager.alert("系统提示","请输入您的邮箱");
return false;
}
if($("#address").val() == ""){
$.messager.alert("系统提示","请输入您的地址");
return false;
}
return $(this).form("validate");
},
success: function(result){
var result = eval('('+result+')');
if(result.success){
$.messager.alert("系统提示","保存成功");
$("#fm").form("reset");
$("#dlg").dialog("close");
$("#dg").datagrid("reload");
}else{
$.messager.alert("系统提示","保存失败");
return;
}
}
});
}
function openUserAddDialog(){
$("#fm").form("reset"); //打开之前先清空数据
            $("#dlg").dialog("open").dialog("setTitle","添加用户");
url = "/user/add";
}
function searchUser(){
$("#dg").datagrid("load",{
"username":$("#s_userName").val()
});
}
function deleteUser(){
var selectedRows=$("#dg").datagrid('getSelections');
if(selectedRows.length==0){
$.messager.alert("系统提示","请选择要删除的数据！");
return;
}
var strIds=[];
for(var i=0;i<selectedRows.length;i++){
strIds.push(selectedRows.id);
}
var ids=strIds.join(",");
$.messager.confirm("系统提示","您确认要删除这<font color=red>"+selectedRows.length+"</font>条数据吗？",function(r){
if(r){
$.post("/user/delete",{ids:ids},function(result){//result直接返回Object，所以无需转换为json
if(result.success){
$.messager.alert("系统提示","数据已成功删除！");
$("#dg").datagrid("reload");
}else{
$.messager.alert("系统提示","数据删除失败！");
}
},"json");
}
});
}
function openUserModifyDialog(){
var selectedRows=$("#dg").datagrid('getSelections');
if(selectedRows.length != 1){
$.messager.alert("系统提示","请选择一条数据进行修改");
return;
}
var row = selectedRows;
$("#dlg").dialog("open").dialog("setTitle","修改用户信息");
dispValue(row);
url = "/user/add/"+row.id;
}
function dispValue(row){
$("#username").val(row.username);
$("#password").val(row.password);
$("#sex").combobox("setValue",row.sex);
$("#birthday").datebox("setValue",row.birthday);
$("#email").val(row.email);
$("#address").val(row.address);
}
function closeUserDialog(){
$("#dlg").dialog("close");
$("#fm").form("reset");
}
function formatEmail(value,rowData,rowIndex){
return value.substr(0,10);
}
</script>
<title>用户管理</title>
</head>
<body style="margin: 1px">
<table id="dg" title="用户管理" class="easyui-datagrid" fitColumns="true" pagination="true" rownumbers="true"
url="/user/list" fit="true" toolbar="#tb">
<thead>
<tr>
<th field="cb" checkbox="true" align="center"></th>
<th field="id" width="50" align="center">编号</th>
<th field="username" width="100" align="center">用户名</th>
<th field="sex" width="50" align="center">性别</th>
<th field="birthday" width="100" align="center" formatter="formatEmail">出生日期</th>
<th field="email" width="100" align="center">邮箱</th>
<th field="address" width="100" align="center">收货地址</th>
</tr>
</thead>
</table>
<div id="tb">
<div>
<a href="javascript:openUserAddDialog()" class="easyui-linkbutton" data-options="plain:true,iconCls:'icon-user_add'">添加用户</a>&nbsp;
<a href="javascript:openUserModifyDialog()" class="easyui-linkbutton" data-options="plain:true,iconCls:'icon-user_edit'">修改用户</a>&nbsp;
<a href="javascript:deleteUser()" class="easyui-linkbutton" data-options="plain:true,iconCls:'icon-user_delete'">删除用户</a>
</div>
<div>
&nbsp;
用户名：<input type="text" id="s_userName" size="20" onkeydown="if(event.keyCode == 13) searchUser();">&nbsp;
<a href="javascript:searchUser()" class="easyui-linkbutton" data-options="plain:true,iconCls:'icon-search'">查询</a>
</div>
</div>

<div class="easyui-dialog" id="dlg" style="width:480px;height:330px;padding:10px 10px;" closed="true" buttons="#dlg-buttons" data-options="" >
<form action="" method="post" id="fm">
<table cellspacing="8px" align="center">
<tr>
<td>用户名：</td>
<td><input type="text" id="username" name="username" class="easyui-validatebox" required="true" style="width: 300px;"/></td>
<td>&nbsp;&nbsp;&nbsp;</td>
</tr>
<tr>
<td>密&nbsp;&nbsp;码：</td>
<td><input type="password" id="password" name="password" class="easyui-validatebox" required="true"/></td>
<td>&nbsp;&nbsp;&nbsp;</td>
</tr>
<tr>
<td>性&nbsp;&nbsp;别：</td>
<td colspan="2">
<select class="easyui-combobox" id="sex" name="sex" style="width:30%" editable="false" panelHeight="auto">
<option value="">请选择性别</option>
<option value="男">男</option>
<option value="女">女</option>
</select>
</td>
</tr>
<tr>
<td>出生日期：</td>
<td><input type="text" id="birthday" name="birthday" class="easyui-datebox" editable="false" required="true"/></td>
<td>&nbsp;&nbsp;&nbsp;</td>
</tr>
<tr>
<td>邮&nbsp;&nbsp;箱：</td>
<td><input type="text" id="email" name="email" class="easyui-validatebox" required="true" data-options="validType:'email'" style="width: 300px;"/></td>
<td>&nbsp;&nbsp;&nbsp;</td>
</tr>
<tr>
<td>收货地址：</td>
<td>
<input type="text" valign="top" id="address" name="address" class="easyui-validatebox" required="true" style="width: 306px;"/>
</td>
</tr>
</table>
</form>
</div>

<div id="dlg-buttons">
<a href="javascript:saveUser();" class="easyui-linkbutton" iconCls="icon-ok">保存</a>
<a href="javascript:closeUserDialog();" class="easyui-linkbutton" iconCls="icon-cancel">取消</a>
</div>

</body>
</html>
　　10、处理easyui中dialog、window、panel等窗体拖动时超出父窗体不能拖回的问题



/**
* Created by Administrator on 2015/8/31.
*/
var easyuiPanelOnMove = function(left, top) {
var parentObj = $(this).panel('panel').parent();
if (left < 0) {
$(this).window('move', {
left : 1
});
}
if (top < 0) {
$(this).window('move', {
top : 1
});
}
var width = $(this).panel('options').width;
var height = $(this).panel('options').height;
var right = left + width;
var buttom = top + height;
var parentWidth = parentObj.width();
var parentHeight = parentObj.height();
if(parentObj.css("overflow")=="hidden"){
if(left > parentWidth-width){
$(this).window('move', {
"left":parentWidth-width
});
}
if(top > parentHeight-height){
$(this).window('move', {
"top":parentHeight-height
});
}
}
};
$.fn.panel.defaults.onMove = easyuiPanelOnMove;
$.fn.window.defaults.onMove = easyuiPanelOnMove;
$.fn.dialog.defaults.onMove = easyuiPanelOnMove;
　　将以上代码保存为任意js文件，在需要处理的页面中直接引入即可。
　　详细代码设计已完毕，改进的地方还有很多，比如：
　　1、数据库操作代码凌乱，不是很方便后期维护
　　2、没有加入登录时的验证码输入操作，具体可以参考这篇博客https://cnodejs.org/topic/50f90d8edf9e9fcc58a5ee0b

查看完整版本: easyui+nodejs+sqlserver增删改查实现