nodejs下的CRUD，使用mysql

2019独角兽企业重金招聘Python工程师标准>>>

先上源码：http://git.oschina.net/jingyuansuifeng/nodejsDemo.git，欢迎大家猛烈fork。

这里样式使用了bootstrap.min.css

主要知识点：

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

7、测试结果

转载于:https://my.oschina.net/robinjiang/blog/169823