NodeJS+Express實現簡單的Web增刪改查

 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+"&registNum="+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      }