NodeJs+Express实现简单的Web增删改查
前一段时间，公司组织了一次NodeJs的技术分享，自己有幸去听了听，第一次接触NodeJs，后来经过自己学习和探索，完成了一个很简单的Web演示项目，在这里和初学者做以分享,开发工具：WebStorm 10.04+MySql5.6;框架：Node+ExpressNode;版本：v4.2.3;视图渲染：jade模板引擎。
项目代码结构：
比较核心和关键的当然就是node开源的插件库，都可以npm命令快速的安装和卸载，这一点很像Maven，提供我们express框架和mysql的连接库，项目中这些代码库默认都在node_modules目录下；其次就是node在web项目开发中比较重要的路由中间件了，其主要功能是提供请求路由映射，有点像struts2框架的作用；再者就是views目录下存放我们需要的web展示模板，这里使用jade模板引擎；public目录存放web中用到的静态资源文件；最后为了方便代码的管理，将与数据库交互的代码单独放在dao目录下，下面分别贴出几个比较重要的源代码：
1 var mysql=require("mysql"); 2 var localConnection=mysql.createConnection({ 3 host:"localhost", 4 user:"root", 5 password:"123456", 6 database:"test" 7 }); 8 9 10 exports.queryCount=function(queryParam,callback){ 11 12 var sql='select count(1) as total from company where 1=1'; 13 if(queryParam.name){ 14 sql=sql+' and name like ?'; 15 } 16 if(queryParam.registNum){ 17 sql=sql+' and registNum like ?'; 18 } 19 var param=[queryParam.name,queryParam.registNum]; 20 localConnection.query(sql,param,callback); 21 } 22 exports.queryAll=function(queryParam,callback){ 23 var sql='select * from company where 1=1'; 24 var param=new Array(); 25 if(queryParam.name){ 26 sql=sql+' and name like ?'; 27 param[0]=queryParam.name; 28 } 29 if(queryParam.registNum){ 30 sql=sql+' and registNum like ?'; 31 param[1]=queryParam.registNum; 32 } 33 sql=sql+' limit ?,?'; 34 param[2]=(queryParam.pageIndex-1)*queryParam.pageSize; 35 param[3]=queryParam.pageSize; 36 localConnection.query(sql,param,callback); 37 } 38 exports.delete=function(id,callback){ 39 var sql="delete from company where id=?"; 40 var param=[id]; 41 localConnection.query(sql,param,callback); 42 } 43 exports.save=function(company,callback){ 44 var saveSql="insert into company(id,name,address,registNum) values(?,?,?,?)"; 45 var args=new Array(); 46 args[0]=company.id; 47 args[1]=company.name; 48 args[2]=company.address; 49 args[3]=company.registNum; 50 localConnection.query(saveSql,args,callback); 51 } 52 exports.detail=function(id,callback){ 53 var sql="select * from company where id=?" 54 var param=[id]; 55 localConnection.query(sql,param,callback); 56 } 57 exports.update=function(company,callback){ 58 var updateSql="update company set name=?,address=?,registNum=? where id=?"; 59 var param=[company.name,company.address,company.registNum,company.id]; 60 localConnection.query(updateSql,param,callback); 61 }
1 var express = require('express'); 2 var dao=require('../dao/dao'); 3 var router = express.Router(); 4 5 /* GET users listing. */ 6 router._list=function(req, res, next){ 7 var name=(req.body.name||req.query.name); 8 if(!name){ 9 name=""; 10 }else{ 11 name=name.replace(/\t/g,""); 12 } 13 var registNum=(req.body.registNum||req.query.registNum); 14 if(!registNum){ 15 registNum=""; 16 }else{ 17 registNum=registNum.replace(/\t/g,""); 18 } 19 var pageIndex=req.params.pageIndex; 20 if(!pageIndex) pageIndex=1; 21 var queryParam={}; 22 queryParam.pageIndex=pageIndex; 23 queryParam.pageSize=15; 24 queryParam.name='%'+name+'%'; 25 queryParam.registNum='%'+registNum+'%'; 26 dao.queryCount(queryParam,function(err,count){ 27 if(!err){ 28 dao.queryAll(queryParam,function(err,list){ 29 if(!err){ 30 var num=count[0].total; 31 var totalPage=Math.ceil(num/15); 32 res.render('company/list',{dataList:list,totalCount:num,totalPage:totalPage,pageIndex:pageIndex,name:name,registNum:registNum,title:'首页'}); 33 } 34 }) 35 36 } 37 }); 38 } 39 router.delete=function(req,res,next){ 40 var id=req.params.id; 41 var pageIndex=req.params.pageIndex; 42 var name=req.body.name; 43 var registNum=req.body.registNum; 44 dao.delete(id,function(err,result){ 45 if(err){ 46 return next(result); 47 } 48 res.redirect('/company/list/'+pageIndex+"?name="+name+"®istNum="+registNum); 49 }) 50 } 51 52 router.save=function(req,res,next){ 53 var company={}; 54 var date=new Date(); 55 company.id=Date.parse(date); 56 company.name=req.body.name; 57 company.address=req.body.address; 58 company.registNum=date.getFullYear()+""+date.getMonth()+""+date.getDay(); 59 dao.save(company,function(err,result){ 60 if(err){ 61 return next(result); 62 } 63 res.redirect('/company/list/1'); 64 }) 65 } 66 router.detail=function(req,res,next){ 67 var id=req.params.id; 68 dao.detail(id,function(err,r){ 69 if(err) throw err; 70 r[0].success=true; 71 console.log(r[0]); 72 res.send(r[0]); 73 }) 74 } 75 router.update=function(req,res,next){ 76 var company={id:req.body.id,name:req.body.name,address:req.body.address,registNum:req.body.registNum}; 77 dao.update(company,function(err,r){ 78 if(err) throw err; 79 res.redirect('/company/list/1') 80 }) 81 } 82 module.exports = router;
1 var express = require('express'); 2 var path = require('path'); 3 var favicon = require('serve-favicon'); 4 var logger = require('morgan'); 5 var cookieParser = require('cookie-parser'); 6 var bodyParser = require('body-parser'); 7 8 var routes = require('./routes/index'); 9 var users = require('./routes/users'); 10 11 var app = express(); 12 13 // view engine setup 14 app.set('views', path.join(__dirname, 'views')); 15 app.set('view engine', 'jade'); 16 17 // uncomment after placing your favicon in /public 18 //app.use(favicon(path.join(__dirname, 'public', 'favicon.ico'))); 19 app.use(logger('dev')); 20 app.use(bodyParser.json()); 21 app.use(bodyParser.urlencoded({ extended: false })); 22 app.use(cookieParser()); 23 app.use(express.static(path.join(__dirname, 'public'))); 24 app.use(favicon(__dirname + '/public/favicon.ico')); 25 26 app.use('/', routes); 27 app.use('/company/list/:pageIndex', users._list); 28 app.use('/company/delete/:id/:pageIndex',users.delete); 29 app.use('/company/save',users.save); 30 app.use('/company/detail/:id',users.detail); 31 app.use('/company/update',users.update); 32 33 34 35 // catch 404 and forward to error handler 36 app.use(function(req, res, next) { 37 var err = new Error('Not Found'); 38 err.status = 404; 39 next(err); 40 }); 41 42 // error handlers 43 44 // development error handler 45 // will print stacktrace 46 if (app.get('env') === 'development') { 47 app.use(function(err, req, res, next) { 48 res.status(err.status || 500); 49 res.render('error', { 50 message: err.message, 51 error: err 52 }); 53 }); 54 } 55 56 // production error handler 57 // no stacktraces leaked to user 58 app.use(function(err, req, res, next) { 59 res.status(err.status || 500); 60 res.render('error', { 61 message: err.message, 62 error: {} 63 }); 64 }); 65 66 67 module.exports = app;
1 include ../layout 2 body 3 form(id='searchForm',role='form',action='/company/list',method='post') 4 .row 5 .panel.panel-default 6 .panel-body 7 .row.form-group.form-group-sm 8 .col-sm-4 9 .input-group 10 span.input-group-addon='公司名称' 11 .input-group.col-sm-12 12 input(name='name',placeholder='请输入公司名称',class='form-control' value='#{name}') 13 .col-sm-4 14 .input-group 15 span.input-group-addon='注册工商号' 16 .input-group.col-sm-12 17 input(name='registNum',placeholder='请输入公司注册工商号',class='form-control' value='#{registNum}') 18 .col-sm-2 19 button(type='button',class='btn btn-primary btn-sm',id='qryBtn') 查询 20 button(type='button',class='btn btn-primary btn-sm',id='resetBtn', style='margin-left:10px') 重置 21 .row 22 .panel.panel-default 23 .panel-heading 24 .btn-group 25 a.btn.btn-default.btn-xs.new-btn='新建' 26 table.table.table-bordered.table-hover.table-striped.table-condensed 27 thead 28 tr 29 th='编号' 30 th='公司名称' 31 th='公司地址' 32 th='注册工商号' 33 th='操作' 34 tbody 35 each rowData,index in dataList 36 tr 37 td=rowData.id 38 td=rowData.name 39 td=rowData.address 40 td=rowData.registNum 41 td 42 a(id='#{rowData.id}').btn.btn-default.btn-xs.btn-del='删除' 43 a(id='#{rowData.id}').btn.btn-default.btn-xs.btn-update='修改' 44 include ../pageInfo 45 46 script(type='text/javascript'). 47 $(".new-btn").click(function(){ 48 var opt={id:"",name:"",address:"",registNum:"",title:"新建注册公司",url:"/company/save"}; 49 createCommonWin(opt); 50 }); 51 $(".btn-update").click(function(){ 52 var id=$(this).attr("id"); 53 $.ajax({ 54 type:"GET", 55 async:false, 56 dataType:"json", 57 url:"/company/detail/"+id+"", 58 success:function(data){ 59 if(data.success){ 60 var opt={id:id,name:data.name,address:data.address,registNum:data.registNum,title:"修改注册公司",url:"/company/update"}; 61 createCommonWin(opt); 62 } 63 } 64 }); 65 }); 66 $("#qryBtn").click(function(){ 67 var queryUrl=$('form').attr('action'); 68 $("form").attr('action',queryUrl+'/'+parseInt($('span.nowPage').html())); 69 $("form").submit(); 70 }); 71 $("#resetBtn").click(function(){ 72 $("form input").val(""); 73 }); 74 $('.btn-del').click(function(){ 75 var id=$(this).attr('id'); 76 var pageIndex=$("li").find("span.nowPage").html(); 77 bootbox.confirm({ 78 buttons: { 79 confirm: { 80 label: '确定', 81 className: 'btn-myStyle' 82 }, 83 cancel: { 84 label: '取消', 85 className: 'btn-default' 86 } 87 }, 88 message: '删除要删除本条记录？', 89 callback: function (result) { 90 if (result) { 91 $("form").attr('action', '/company/delete/'+id+'/'+pageIndex); 92 $("form").submit(); 93 } 94 }, 95 title: "确认提示" 96 }); 97 }); 98 function createCommonWin(opt){ 99 bootbox.dialog({ 100 message: "<div class='row'>" + 101 "<div class='row form-group form-group-sm' style='text-align: center'>" + 102 "<form class='form-horizontal' id='createForm' role='form' method='post'>" + 103 '<div class="form-group form-group-sm" style="margin-left:20px"> ' + 104 '<input type="hidden" value="' + opt.id + '" name="id" id="id">' + 105 '<input type="hidden" value="' + opt.registNum + '" name="registNum" id="registNum">' + 106 '<label class="col-sm-2 control-label"><span style="color:red;font-weight: bolder">*</span>公司名称</label> ' + 107 '<div class="col-sm-4"><input id="name" type="text" name="name" value="'+opt.name+'" class="form-control input-md">' + 108 '</div></br></br> ' + 109 '<div class="form-group form-group-sm"> ' + 110 '<label class="col-sm-2 control-label" style="margin-left:10px;"><span style="color:#ff0000;font-weight: bolder">*</span>公司地址</label> ' + 111 '<div class="col-sm-4"><input style="width:180px" id="address" type="text" name="address" value="'+opt.address+'" class="form-control input-md">' + 112 '</div></br></br> ' + 113 "</form>" + 114 "</div>" + 115 "</div>", 116 title: opt.title, 117 buttons: { 118 Cancel: { 119 label: "取消", 120 className: "btn-default", 121 callback: function () { 122 return true; 123 } 124 }, 125 OK: { 126 label: "保存", 127 className: "btn-primary", 128 callback: function () { 129 var subForm = $("#createForm"); 130 if (subForm.find("#name").val() == "") { 131 alert("公司名称不能为空!"); 132 return false; 133 } else if (subForm.find("#address").val() == "") { 134 alert("公司地址不能为空!"); 135 return false; 136 } else { 137 subForm.attr("action", opt.url); 138 subForm.submit(); 139 return true; 140 } 141 } 142 } 143 } 144 }); 145 }
最后运行的界面：
