主要知识点:
1、使用express新建一个项目
2、在package中添加mysql模块
{
"name": "application-name",
"version": "0.0.1",
"private": true,
"scripts": {
"start": "node app.js"
},
"dependencies": {
"express": "3.4.1",
"jade": "*",
"mysql":">=2.0.0-alpha"
}
}
3、安装mysql模块
npm install
4、新增连接数据库的信息,这里我使用的是连接池
var mysql = require('mysql');
//数据库链接信息.创建数据库连接池。
var pool = mysql.createPool({
host : 'localhost',
port : '3306', //可以不填写,默认为3306
user : 'root',
password : 'root',
database : 'quickstart'
});
5、实现增删该查功能
//链接数据库测试 start
//显示数据
app.get('/', function(req, res) {
pool.getConnection(function(err, connection) {
connection.query('SELECT * from ss_user ', function(err, rows, fields) {
if (err)
throw err;
//res.send('The solution is: ', rows[0]);
//res.send('user id is:'+req.params.id);
res.render('user.jade', {
title : 'User List',
user : rows
});
//console.log('The solution is: ', rows);
});
connection.release();
});
});
//新增测试
app.get('/create', function(req, res) {
res.render('create.jade', {
title : 'Create a new user'
});
});
app.post('/create', function(req, res) {
pool.getConnection(function(err, connection) {
connection.query('insert into ss_user set ?', {
id : req.body.user.id,
login_name : req.body.user.login_name,
name : req.body.user.name,
password : req.body.user.password
}, function(err, fields) {
if (err)
throw err;
//console.log('Insert is success.');
//req.flash('info','User created');
});
connection.release();
res.redirect('/');
});
});
//修改操作。修改指定id的数据
app.get('/update/:id', function(req, res) {
pool.getConnection(function(err, connection) {
connection.query('SELECT * from ss_user where id=?',[req.params.id],function(err, rows, fields) {
if (err)
throw err;
console.log('search is success.');
res.render('create.jade', {
title : 'Update user',
user : rows[0]
});
});
connection.release();
});
});
app.post('/update', function(req, res) {
pool.getConnection(function(err, connection) {
connection.query('update ss_user set ? where id = ?', [{
id : req.body.user.id,
login_name : req.body.user.login_name,
name : req.body.user.name,
password : req.body.user.password
},req.body.user.id], function(err, fields) {
if (err)
throw err;
//console.log('Insert is success.');
});
connection.release();
res.redirect('/');
});
});
//删除操作。删除指定id的数据
app.get('/delete/:id', function(req, res) {
pool.getConnection(function(err, connection) {
connection.query('delete from ss_user where id = ?', [req.params.id], function(err, fields) {
if (err)
throw err;
});
connection.release();
res.redirect('/');
});
});
//链接数据库测试 end
6、新增jade模版文件
user.jade
extends layout
block content
h1= title
mixin users(users)
fieldset
legend Welcome to #{title}
table.mytable
th ID
th Name
th Opt
each user in users
tr
td= user.id
td #{user.login_name}
td
a(href='/delete/#{user.id}', class='btn btn-primary') Delete
a(href='/update/#{user.id}', class='btn btn-primary') Update
a(href='/create', class='btn btn-primary') Add a User
- users = user
mixin users(users)
create.jade
extends layout
block content
h1=title
- userVar = user
- if(userVar)
form(method='post',action='/update')
fieldset
legend=title
div.clearfix
label LoginName
div.input
input(name='user[login_name]',class='xlarge',value=user.login_name)
label Name
div.input
input(name='user[name]',class='xlarge',value=user.name)
label Password
div.input
input(name='user[password]',class='xlarge',value=user.password)
div.actions
input(type='submit',value='Save',class='btn primary')
button(type='reset',class='btn') Cancel
- else
form(method='post',action='/create')
fieldset
legend=title
div.clearfix
label Id
div.input
input(name='user[id]',class='xlarge')
label LoginName
div.input
input(name='user[login_name]',class='xlarge')
label Name
div.input
input(name='user[name]',class='xlarge')
label Password
div.input
input(name='user[password]',class='xlarge')
div.actions
input(type='submit',value='Add',class='btn primary')
button(type='reset',class='btn') Cancel