/*
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());
); //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));
){
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
){
){
){
auto}