json版
var express = require('express');
var fs = require("fs");
var app = express();
app.get("/", function(req, res){
res.send("Hello , welcome to index!<br>"+
"<a href='http://localhost:8081/addUser'>增</a><br>"+
"<a href='http://localhost:8081/update/1'>改 +id, 默认1</a><br>"+
"<a href='http://localhost:8081/delUser/1'>删 + id, 默认1</a><br>"+
"<a href='http://localhost:8081/1'>id查询 +id， 默认1</a><br>"+
"<a href='http://localhost:8081/listUsers'>list查询</a><br>"
);
});
// 新增
var user = {
"user4" : {
"name" : "mohit",
"password" : "password4",
"profession" : "teacher",
"id": 4
}
}
app.get('/addUser', function(req, res) {
fs.readFile( __dirname + "/" + "users.json", 'utf8', function (err, data) {
data = JSON.parse(data);
data["user4"] = user["user4"];
console.log( data );
res.end( JSON.stringify(data) );
});
});
// 删除
app.get('/delUser/:id', function (req, res) {
fs.readFile( __dirname + "/" + "users.json", 'utf8', function (err, data) {
data = JSON.parse( data );
delete data["user" + req.params.id];
console.log( data );
res.end( JSON.stringify(data));
});
})
// 修改
var updateUser = {
"user" : {
"name" : "zhangsan",
"password" : "mypassword",
"profession" : "programer",
"id": 0
}
}
app.get('/update/:id', function (req, res) {
fs.readFile( __dirname + "/" + "users.json", 'utf8', function (err, data) {
data = JSON.parse( data );
delete data["user" + req.params.id];
data["user" + req.params.id] = updateUser["user"];
console.log( data );
res.end( JSON.stringify(data) );
});
})
// 列表查询
app.get('/listUsers', function (req, res) {
fs.readFile( __dirname + "/" + "users.json", 'utf8', function (err, data) {
console.log( data );
res.end( data );
});
})
// id查询
// 模拟数据的id和user+id对应
app.get('/:id', function (req, res) {
fs.readFile( __dirname + "/" + "users.json", 'utf8', function (err, data) {
data = JSON.parse( data );
var user = data["user" + req.params.id]
console.log( user );
res.end( JSON.stringify(user));
});
})
var server = app.listen(8081, function () {
console.log("访问地址: http://localhost:8081")
})
users.json
{
"user1" : {
"name" : "mahesh",
"password" : "password1",
"profession" : "teacher",
"id": 1
},
"user2" : {
"name" : "suresh",
"password" : "password2",
"profession" : "librarian",
"id": 2
},
"user3" : {
"name" : "ramesh",
"password" : "password3",
"profession" : "clerk",
"id": 3
}
}
mysql版
var express = require('express');
var fs = require("fs");
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '123456',
port: '3306',
database : 'test'
});
var app = express();
connection.connect();
app.get("/", function(req, res){
res.send("Hello , welcome to index!<br>"+
"<a href='http://localhost:8081/addUser'>增</a><br>"+
"<a href='http://localhost:8081/update/1'>改 +id, 默认1</a><br>"+
"<a href='http://localhost:8081/delUser/1'>删 + id, 默认1</a><br>"+
"<a href='http://localhost:8081/1'>id查询 +id， 默认1</a><br>"+
"<a href='http://localhost:8081/listUsers'>list查询</a><br>"
);
});
// 新增
app.get('/addUser', function(req, res) {
var addSql = "INSERT INTO users(id,name,password,profession) VALUES(?,?,?,?)";
var addSqlParams = [4, "赵六", "password4", "actor"];
connection.query(addSql,addSqlParams,function (err, result) {
if(err){
return res.end('[SELECT ERROR] - '+err.message);
}
if (result.affectedRows == 1) {
res.send("添加成功！");
}
});
});
// 删除
app.get('/delUser/:id', function (req, res) {
var delSql = 'DELETE FROM users where id='+req.params.id;
connection.query(delSql,function (err, result) {
if(err){
console.log('[DELETE ERROR] - ',err.message);
return;
}
if (result.affectedRows == 1) {
res.send("删除成功");
}
});
})
// 修改
app.get('/update/:id', function (req, res) {
var modSql = 'UPDATE users SET name = ?,profession = ? WHERE id = ?';
var modSqlParams = ['老贾', '摆小摊的',req.params.id];
connection.query(modSql,modSqlParams,function (err, result) {
if(err){
return res.end('[SELECT ERROR] - '+err.message);
}
if (result.affectedRows == 1) {
res.send("修改成功");
}
});
})
// 列表查询
app.get('/listUsers', function (req, res) {
var sql = 'SELECT * FROM users';
connection.query(sql,function (err, result) {
if(err){
return res.end('[SELECT ERROR] - '+err.message);
}
res.send( result );
});
})
// id查询
// 模拟数据的id和user+id对应
app.get('/:id', function (req, res) {
var sql = "SELECT * FROM users where id="+req.params.id;
connection.query(sql,function (err, result) {
if(err){
return res.end('[SELECT ERROR] - '+err.message);
}
res.send( result );
});
})
var server = app.listen(8081, function () {
console.log("访问地址: http://localhost:8081")
})
users.sql
/*
Navicat MySQL Data Transfer
Source Server : 127.0.0.1
Source Server Version : 50621
Source Host : localhost
Source Database : TaoYuan
Target Server Version : 50621
File Encoding : utf-8
Date: 05/18/2016 11:44:07 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `users`
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '名字',
`password` varchar(20) NOT NULL DEFAULT '' COMMENT '密码',
`profession` varchar(20) NOT NULL DEFAULT '0' COMMENT '职业',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `users`
-- ----------------------------
BEGIN;
INSERT INTO `users` VALUES ('1', '张三', 'password1', 'teacher'), ('2', '李四', 'password2', 'programer'), ('3', '王五', 'password3', 'librarian');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
