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目录下，下面分别贴出几个比较重要的源代码：

var mysql=require("mysql");
var localConnection=mysql.createConnection({
    host:"localhost",
    user:"root",
    password:"123456",
    database:"test"
});


exports.queryCount=function(queryParam,callback){

    var sql='select count(1) as total from company where 1=1';
    if(queryParam.name){
        sql=sql+' and name like ?';
    }
    if(queryParam.registNum){
        sql=sql+' and registNum like ?';
    }
    var param=[queryParam.name,queryParam.registNum];
    localConnection.query(sql,param,callback);
}
exports.queryAll=function(queryParam,callback){
    var sql='select * from company where 1=1';
    var param=new Array();
    if(queryParam.name){
        sql=sql+' and name like ?';
        param[0]=queryParam.name;
    }
    if(queryParam.registNum){
        sql=sql+' and registNum like ?';
        param[1]=queryParam.registNum;
    }
    sql=sql+' limit ?,?';
    param[2]=(queryParam.pageIndex-1)*queryParam.pageSize;
    param[3]=queryParam.pageSize;
    localConnection.query(sql,param,callback);
}
exports.delete=function(id,callback){
    var sql="delete from company where id=?";
    var param=[id];
    localConnection.query(sql,param,callback);
}
exports.save=function(company,callback){
    var saveSql="insert into company(id,name,address,registNum) values(?,?,?,?)";
    var args=new Array();
    args[0]=company.id;
    args[1]=company.name;
    args[2]=company.address;
    args[3]=company.registNum;
    localConnection.query(saveSql,args,callback);
}
exports.detail=function(id,callback){
    var sql="select * from company where id=?"
    var param=[id];
    localConnection.query(sql,param,callback);
}
exports.update=function(company,callback){
    var updateSql="update company set name=?,address=?,registNum=? where id=?";
    var param=[company.name,company.address,company.registNum,company.id];
    localConnection.query(updateSql,param,callback);
}

var express = require('express');
var dao=require('../dao/dao');
var router = express.Router();

/* GET users listing. */
router._list=function(req, res, next){
    var name=(req.body.name||req.query.name);
    if(!name){
        name="";
    }else{
        name=name.replace(/	/g,"");
    }
    var registNum=(req.body.registNum||req.query.registNum);
    if(!registNum){
        registNum="";
    }else{
        registNum=registNum.replace(/	/g,"");
    }
    var pageIndex=req.params.pageIndex;
    if(!pageIndex) pageIndex=1;
    var queryParam={};
    queryParam.pageIndex=pageIndex;
    queryParam.pageSize=15;
    queryParam.name='%'+name+'%';
    queryParam.registNum='%'+registNum+'%';
    dao.queryCount(queryParam,function(err,count){
        if(!err){
            dao.queryAll(queryParam,function(err,list){
                if(!err){
                    var num=count[0].total;
                    var totalPage=Math.ceil(num/15);
                    res.render('company/list',{dataList:list,totalCount:num,totalPage:totalPage,pageIndex:pageIndex,name:name,registNum:registNum,title:'首页'});
                }
            })

        }
    });
}
router.delete=function(req,res,next){
    var id=req.params.id;
    var pageIndex=req.params.pageIndex;
    var name=req.body.name;
    var registNum=req.body.registNum;
    dao.delete(id,function(err,result){
        if(err){
           return next(result);
        }
       res.redirect('/company/list/'+pageIndex+"?name="+name+"&registNum="+registNum);
    })
}

router.save=function(req,res,next){
    var company={};
    var date=new Date();
        company.id=Date.parse(date);
        company.name=req.body.name;
        company.address=req.body.address;
        company.registNum=date.getFullYear()+""+date.getMonth()+""+date.getDay();
    dao.save(company,function(err,result){
        if(err){
            return next(result);
        }
        res.redirect('/company/list/1');
    })
}
router.detail=function(req,res,next){
    var id=req.params.id;
    dao.detail(id,function(err,r){
        if(err) throw err;
        r[0].success=true;
        console.log(r[0]);
        res.send(r[0]);
    })
}
router.update=function(req,res,next){
    var company={id:req.body.id,name:req.body.name,address:req.body.address,registNum:req.body.registNum};
    dao.update(company,function(err,r){
        if(err) throw  err;
        res.redirect('/company/list/1')
    })
}
module.exports = router;

var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

var routes = require('./routes/index');
var users = require('./routes/users');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

// uncomment after placing your favicon in /public
//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
app.use(favicon(__dirname + '/public/favicon.ico'));

app.use('/', routes);
app.use('/company/list/:pageIndex', users._list);
app.use('/company/delete/:id/:pageIndex',users.delete);
app.use('/company/save',users.save);
app.use('/company/detail/:id',users.detail);
app.use('/company/update',users.update);



// catch 404 and forward to error handler
app.use(function(req, res, next) {
  var err = new Error('Not Found');
  err.status = 404;
  next(err);
});

// error handlers

// development error handler
// will print stacktrace
if (app.get('env') === 'development') {
  app.use(function(err, req, res, next) {
    res.status(err.status || 500);
    res.render('error', {
      message: err.message,
      error: err
    });
  });
}

// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
  res.status(err.status || 500);
  res.render('error', {
    message: err.message,
    error: {}
  });
});


module.exports = app;

include ../layout
body
    form(id='searchForm',role='form',action='/company/list',method='post')
        .row
            .panel.panel-default
                .panel-body
                   .row.form-group.form-group-sm
                       .col-sm-4
                          .input-group
                              span.input-group-addon='公司名称'
                              .input-group.col-sm-12
                                  input(name='name',placeholder='请输入公司名称',class='form-control' value='#{name}')
                       .col-sm-4
                           .input-group
                               span.input-group-addon='注册工商号'
                               .input-group.col-sm-12
                                   input(name='registNum',placeholder='请输入公司注册工商号',class='form-control' value='#{registNum}')
                   .col-sm-2
                       button(type='button',class='btn btn-primary btn-sm',id='qryBtn') 查询
                       button(type='button',class='btn btn-primary btn-sm',id='resetBtn', style='margin-left:10px') 重置
    .row
        .panel.panel-default
            .panel-heading
                .btn-group
                     a.btn.btn-default.btn-xs.new-btn='新建'
            table.table.table-bordered.table-hover.table-striped.table-condensed
                thead
                    tr
                        th='编号'
                        th='公司名称'
                        th='公司地址'
                        th='注册工商号'
                        th='操作'
                tbody
                    each rowData,index in dataList
                        tr
                            td=rowData.id
                            td=rowData.name
                            td=rowData.address
                            td=rowData.registNum
                            td
                               a(id='#{rowData.id}').btn.btn-default.btn-xs.btn-del='删除'
                               a(id='#{rowData.id}').btn.btn-default.btn-xs.btn-update='修改'
include ../pageInfo

script(type='text/javascript').
     $(".new-btn").click(function(){
          var opt={id:"",name:"",address:"",registNum:"",title:"新建注册公司",url:"/company/save"};
          createCommonWin(opt);
     });
     $(".btn-update").click(function(){
          var id=$(this).attr("id");
         $.ajax({
             type:"GET",
             async:false,
             dataType:"json",
             url:"/company/detail/"+id+"",
             success:function(data){
                 if(data.success){
                     var opt={id:id,name:data.name,address:data.address,registNum:data.registNum,title:"修改注册公司",url:"/company/update"};
                      createCommonWin(opt);
                 }
             }
         });
     });
     $("#qryBtn").click(function(){
         var queryUrl=$('form').attr('action');
           $("form").attr('action',queryUrl+'/'+parseInt($('span.nowPage').html()));
           $("form").submit();
     });
     $("#resetBtn").click(function(){
         $("form input").val("");
     });
     $('.btn-del').click(function(){
         var id=$(this).attr('id');
         var pageIndex=$("li").find("span.nowPage").html();
         bootbox.confirm({
             buttons: {
                 confirm: {
                     label: '确定',
                     className: 'btn-myStyle'
                 },
                 cancel: {
                     label: '取消',
                     className: 'btn-default'
                 }
             },
             message: '删除要删除本条记录？',
             callback: function (result) {
                 if (result) {
                     $("form").attr('action', '/company/delete/'+id+'/'+pageIndex);
                     $("form").submit();
                 }
             },
             title: "确认提示"
         });
     });
     function createCommonWin(opt){
         bootbox.dialog({
             message: "<div class='row'>" +
             "<div class='row form-group form-group-sm' style='text-align: center'>" +
             "<form class='form-horizontal' id='createForm' role='form' method='post'>" +
             '<div class="form-group form-group-sm" style="margin-left:20px"> ' +
             '<input type="hidden" value="' + opt.id + '" name="id" id="id">' +
             '<input type="hidden" value="' + opt.registNum + '" name="registNum" id="registNum">' +
             '<label class="col-sm-2 control-label"><span style="color:red;font-weight: bolder">*</span>公司名称</label> ' +
             '<div class="col-sm-4"><input id="name" type="text"  name="name"  value="'+opt.name+'" class="form-control input-md">' +
             '</div></br></br> ' +
             '<div class="form-group form-group-sm"> ' +
             '<label class="col-sm-2 control-label" style="margin-left:10px;"><span style="color:#ff0000;font-weight: bolder">*</span>公司地址</label> ' +
             '<div class="col-sm-4"><input style="180px" id="address" type="text" name="address"  value="'+opt.address+'" class="form-control input-md">' +
             '</div></br></br> ' +
             "</form>" +
             "</div>" +
             "</div>",
             title: opt.title,
             buttons: {
                 Cancel: {
                     label: "取消",
                     className: "btn-default",
                     callback: function () {
                         return true;
                     }
                 },
                 OK: {
                     label: "保存",
                     className: "btn-primary",
                     callback: function () {
                         var subForm = $("#createForm");
                         if (subForm.find("#name").val() == "") {
                             alert("公司名称不能为空!");
                             return false;
                         } else if (subForm.find("#address").val() == "") {
                             alert("公司地址不能为空!");
                             return false;
                         } else {
                             subForm.attr("action", opt.url);
                             subForm.submit();
                             return true;
                         }
                     }
                 }
             }
         });
     }

最后运行的界面：