/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 50519
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50519
File Encoding : 65001
Date: 2018-09-11 19:02:40
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`id` int(33) NOT NULL AUTO_INCREMENT,
`name` text,
`age` text NOT NULL,
`sex` text NOT NULL,
`birth` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('2', 'asdas', '22', '1', '2018-09-11 15:16:28');
INSERT INTO `person` VALUES ('5', '333', '111', '1', '2018-09-11 16:35:37');
INSERT INTO `person` VALUES ('6', 'ttt', '22', '1', '2018-09-11 16:35:45');
INSERT INTO `person` VALUES ('7', '44', '5', '2', '2018-09-11 16:35:54');
INSERT INTO `person` VALUES ('8', '6', '11', '2', '2018-09-11 16:36:06');
INSERT INTO `person` VALUES ('9', 'sss', '3', '1', '2018-09-11 16:36:50');
INSERT INTO `person` VALUES ('10', '1', '11', '1', '2018-09-11 18:57:40');
2. 简历路由routes/users.js
var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var sql = require('../db/sql');
var uuid = require('node-uuid');
var moment = require('moment');
const dbconfig = {
host : '127.0.0.1',
user : 'root',
password : '1234',
port : '3306',
database : 'test'
};
var connection;
function handleDisconnect() {
connection = mysql.createConnection(dbconfig);
connection.connect(function(err) {
if(err) {
console.log("进行断线重连：" + new Date());
setTimeout(handleDisconnect, 2000); //2秒重连一次
return;
}
console.log("连接成功");
});
connection.on('error', function(err) {
console.log('db error', err);
if(err.code === 'PROTOCOL_CONNECTION_LOST') {
handleDisconnect();
} else {
throw err;
}
});
}
function handleData(result){
var data = [],tempData;
tempData = JSON.parse(JSON.stringify(result));
if(tempData && tempData.length>0){
tempData.forEach(function(value,index){
data.push({
id:value.id,
name:value.name,
age:value.age,
sex:value.sex,
birth:moment(value.birth).format('YYYY-MM-DD HH:mm:ss')
});
});
}
return data;
}
/* GET users listing. */
router.get('/queryAll', function(req, res, next) {
handleDisconnect();
connection.query(sql.queryAll,function (err, result) {
if(err){
console.log('[SELECT ERROR] - ',err.message);
return;
}
var data = handleData(result);
res.render('user', {users:data});
console.log(data);
});
//web请求中可以不断连接
connection.end();
});
router.post('/add', function(req, res, next) {
handleDisconnect();
console.log('=================req================');
console.log(req.body)
//id,name,age,sex,birth
connection.query(sql.insert,[req.body.name,req.body.age,req.body.sex,new Date()],function (err, result) {
if(err){
console.log('[INSERT ERROR] - ',err.message);
res.json("添加数据失败");
return;
}
console.log(result);
res.json("添加数据成功");
});
//web请求中可以不断连接
connection.end();
});
router.post('/update', function(req, res, next) {
handleDisconnect();
console.log('=================req================');
console.log(req.body)
//id,name,age,sex,birth
connection.query(sql.updatePerson,[req.body.name,req.body.age,req.body.sex,new Date(),req.body.id],function (err, result) {
if(err){
console.log('[UPDATE ERROR] - ',err.message);
res.json("编辑数据失败");
return;
}
console.log(result);
res.json("编辑数据成功");
});
//web请求中可以不断连接
connection.end();
});
router.post('/delete', function(req, res, next) {
handleDisconnect();
console.log('=================req================');
console.log(req.body)
connection.query(sql.deleteById,[req.body.id],function (err, result) {
if(err){
console.log('[DELETE ERROR] - ',err.message);
res.json("删除数据失败");
return;
}
console.log(result);
res.json("删除数据成功");
});
//web请求中可以不断连接
connection.end();
});
router.get('/search', function(req, res, next) {
handleDisconnect();
console.log('=================req================');
console.log(req.param('name'));
connection.query(sql.getPersonByName,[req.param('name')],function (err, result) {
if(err){
console.log('[查询 ERROR] - ',err.message);
return;
}
var data = handleData(result);
res.render('user', {users:data});
console.log(data);
});
//web请求中可以不断连接
connection.end();
});
module.exports = router;
3. views/建立user.jade
pasting
extends layout
block content
<div>
input(id='edit',type='button',value='编辑')
input(id='delete',type='button',value='删除')
input(id='name',type='text')
input(id='search',type='button',value='查询')
table
thead
tr
- list = ["checkbox","姓名", "年龄", "性别","出生日期"]
each item in list
- if (item == "checkbox")
th
input(type='checkbox')
- else
th= item
tbody
each user in users
tr
td
input(type='checkbox' data-id='#{user.id}')
td(class='name') #{user.name}
td(class='age') #{user.age}
td(class='sex') #{user.sex}
td(class='birth') #{user.birth}
script(src='/javascripts/jquery-3.2.1.js')
script(src='/business/user.js')
p
div
#id
span
p
input(name='id',type='hidden')
label 姓名
input(name='name')
span
p
label 性别
input(name='sex')
span
p
label 年龄
input(name='age')
p
input(id='save',type='button',value='保存')
input(id='add',type='button',value='增加')
<div>
4.public 下建立user.js
$(function(){
$("#add").click(function(){
$.ajax({
type: 'POST',
url: '/users/add',
data: {
name:$('input[name="name"]').val(),
sex:$('input[name="sex"]').val(),
age:$('input[name="age"]').val()
},
dataType: 'json'
}).done(function(res){
console.log(res);
alert(res);
window.location.reload();
}).fail(function(res){
alert(res);
});
});
$("#edit").click(function(){
var $checkebox = $('input[type="checkbox"]:checked');
var checkeboxLength = $checkebox.length;
if(checkeboxLength>1){
alert('不能批量选择')
return;
}
if(checkeboxLength < 0 || checkeboxLength == 0){
alert('请选择要编辑的内容')
return;
}
$('input[name="id"]').val($checkebox.attr('data-id'));
$('input[name="name"]').val($checkebox.parent().next('td[class="name"]').text());
$('input[name="sex"]').val($checkebox.parent().parent().find('td[class="sex"]').text());
$('input[name="age"]').val($checkebox.parent().parent().find('td[class="age"]').text());
});
$("#delete").click(function(){
if($('input[type="checkbox"]:checked').length>1){
alert('不能批量选择')
return;
}else{
$.ajax({
type: 'POST',
url: '/users/delete',
data: {
id:$('input[type="checkbox"]:checked').attr('data-id')
},
dataType: 'json'
}).done(function(res){
console.log(res);
alert(res);
window.location.reload();
}).fail(function(res){
alert(res);
});
}
});
$("#search").click(function(){
window.location.href = '/users/search?name='+$("#name").val()
});
$("#save").click(function(){
$.ajax({
type: 'POST',
url: '/users/update',
data: {
id:$('input[name="id"]').val(),
name:$('input[name="name"]').val(),
sex:$('input[name="sex"]').val(),
age:$('input[name="age"]').val()
},
dataType: 'json'
}).done(function(res){
console.log(res);
alert(res);
window.location.reload();
}).fail(function(res){
alert(res);
});
});
});
4.public下css定义
body {
padding: 50px;
margin: 0 auto;
float:left;
font: 14px "Lucida Grande", Helvetica, Arial, sans-serif;
}
a {
color: #00B7FF;
}
table {
margin: 10px;
font-family: verdana, arial, sans-serif;
font-size: 11px;
color: #333333;
border-width: 1px;
border-color: #999999;
border-collapse: collapse;
}
table th {
background: #b5cfd2 url('cell-blue.jpg');
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #999999;
}
table td {
background: #dcddc0 url('cell-grey.jpg');
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #999999;
}
div{margin: 0 auto}