node连接--MySQL
MySQL驱动器：node-mysql;
MySQL对象关系映射器：node-sequelize;
例子：
- package.json:
{ "name": "shopping-cart-example", "version": "0.0.1", "dependencies": { "express": "2.5.2", "jade": "0.19.0", "mysql": "0.9.5" } } - config.json:
{ "host": "localhost", "password": "123", "database": "cart-example" } - setup.js:
var mysql = require('mysql'); var config = require('./config'); //初始化客户端 delete config.database; var db = mysql.createClient(config); //创建数据库 db.query('CREATE DATABASE IF NOT EXISTS `cart-example`'); db.query('USE `cart-example`'); //创建表 db.query('DROP TABLE IF EXISTS item'); db.query('CREATE TABLE item (' + 'id INT(11) AUTO_INCREMENT,' + 'title VARCHAR(255),' + 'description TEXT,' + 'created DATETIME,' + 'PRIMARY KEY(id))' ); db.query('DROP TABLE IF EXISTS review'); db.query('CREATE TABLE review (' + 'id INT(11) AUTO_INCREMENT,' + 'item_id INT(11),' + 'text TEXT,' + 'stars INT(1),' + 'created DATETIME,' + 'PRIMARY KEY(id))' ); //关闭客户端 db.end(function() { process.exit(); }); - server.js:
var express = require('express'); var mysql = require('mysql'); var config = require('./config'); app = express.createServer(); app.use(express.bodyParser()); app.set('view engine', 'jade'); app.set('views','views'); app.set('view options', {layout: false}); //连接数据集 var db = mysql.createClient(config); //首页路由 app.get('/', function(req, res, next) { db.query('SELECT id, title, description FROM item', function(err, results) { res.render('index', {items: results}); }); }); //创建商品的路由 app.post('/create', function(req, res, next) { //使用?避免字符串拼接，从而避免SQL注入攻击，但需要使用替换数据的第二参数 db.query('INSERT INTO item SET title = ?, description = ?', [req.body.title, req.body.description], function(err, info) { if(err) return next(err); //通过info.insertId来创建商品的id console.log('-item created with id %s', info.insertId); res.redirect('/'); }); }); //查看商品路由 app.get('/item/:id', function(req, res, next) { function getItem (fn) { db.query('SELECT id, title, description FROM item WHERE id= ? LIMIT 1', [req.params.id], function (err, results) { if(err) return next(err); if(!results[0]) return res.send(404); fn(results[0]); }); } function getReviews (item_id, fn) { db.query('SELECT text, stars FROM review WHERE item_id= ?', [item_id], function (err, results) { if(err) return next(err); fn(results); }); } getItem(function (item) { getReviews(item.id, function (reviews) { res.render('item', {item: item, reviews: reviews}); }); }); }); //评价商品的路由 app.post('/item/:id/review', function(req, res, next) { db.query('INSERT INTO review SET item_id = ?, stars = ?, text = ?', [req.params.id, req.body.stars, req.body.text], function(err, info) { if (err) return next(err); console.log('-review created with id %s', info.insertId); res.redirect('/item/' + req.params.id); }); }); app.listen(3000, function() { console.log(' - listeing on http://*:3000'); }); - views/layout.jade:
doctype 5 html head title My shopping cart body h1 My shopping cart #cart block body - views/index.jade:
extends layout block body h2 All items if(items.length) ul each item in items li h3: a(href='/item/#{item.id}')=item.title =item.description else p No items to show h2 Create new item form(action='/create', method='post') p label Title input(type='text', name='title') p label Description textarea(name='description') p button Submit - views/item.jade:
extends layout block body a(href='/') Go back h2= item.title p= item.description h3 User reviews if(reviews.length) each review in reviews .review b #{review.stars} stars p= review.text hr else p No reviews to show. Write one! form(action='/item/#{item.id}/review', method='POST') fieldset legend Create review p label Stars select(name='stars') option 1 option 2 option 3 option 4 option 5 p label Review textarea(name='text') p button(type='submit') Send
sequelize：
上面例子的问题：建表的过程是手动的；表的定义不是项目一部分（单独的setup.js），应用程序无法进行校验；
sequelize通过定义schema和模型同时使用同步特性来创建数据库表（不需要单独建立）；
- 连接sequelzie:
var Sequelize = require('sequelize'); var sequelize = new Sequelize('todo-example', 'root');
Sequelize构造器接受参数包括：
-
- database(String)
- username(String) //必要
- password(String) //可选
- other option(Object) //可选
- host(String)
- port(Number)
- 定义模型和同步：使用sequelize.define方法，第一参数为模型名，第二参数是包含了属性的对象；
var Project = sequelize.define('Project', { title : Sequelize.STRING, description: Sequelize.TEXT, created: Sequelize.DATE });对应的MySql类型：
- Sequelize.STRING //VARCHAR(255)
- Sequelize.BOOLEAN //TINYINT(1)
- Sequelize.TEXT //TEXT
- Sequelize.DATE //DATETIME
- Sequelize.INTEGER //INT
此外还可以传递选项对象： title: {type: Sequelize.STRING, default: 'title'}
- 例子：//注意要先建好数据库 建立数据库
- server.js
var express = require('express'); var Sequelize = require('sequelize'); app = express.createServer(); //注意格式 app.use(express.static(__dirname + '/public')); app.use(express.bodyParser()); app.set('view engine', 'jade'); app.set('views','views'); app.set('view option', {layout: false}); //首页路由 app.get('/', function (req, res, next) { Project.findAll() .success( function (projects) { res.render('index', {projects: projects}); }) .error(next); }); //删除项目路由 app.del('/project/:id', function (req, res, next) { Project.find(Number(req.params.id)).success(function (proj) { proj.destroy() .success(function () { res.rend(200); }) .error(next); }).error(next); }); //创建项目路由 app.post('/projects', function (req, res, next) { Project.build(req.body).save() .success(function (obj) { res.send(obj); }) .error(next); }); //展示指定项目路由 app.get('/project/:id/tasks', function (req, res, next) { Project.find(Number(req.params.id)) .success(function (project) { project.getTasks().on('success', function (tasks) { res.render('tasks', {project: project, tasks: tasks}); }) }) .error(next); }); //为指定项目添加任务 app.post('/project/:id/tasks', function (req, res, next) { res.body.ProjectId = req.params.id; Task.build(req.body).save() .success(function (obj) { res.send(obj); }) .error(next); }); //删除任务路由 app.del('/task/:id', function (req, res, next) { Task.find(Number(req.params.id)).success(function (task) { task.destroy() .success(function () { res.send(200); }) .error(err); }).error(next); }); app.listen(3000, function() { console.log(' - listening on http://*3000'); }); //初始化，处于对结构清晰的考虑，在应用设置后做; var sequelize = new Sequelize('todo-example', 'root','123'); //定义任务模型 var Project = sequelize.define('Project', { title: Sequelize.STRING, description: Sequelize.TEXT, created: Sequelize.DATE }) var Task = sequelize.define('Task', { title: Sequelize.STRING }); //设置联合 Task.belongsTo(Project); Project.hasMany(Task); //同步 sequelize.sync(); - views/layout.jade
doctype 5 html head title TODO list app script(src='/lib/jquery.min.js') script(src='/main.js') body h1 TODO list app #todo block body - views/index.jade
extends layout block body h2 All items if(items.length) ul each item in items li h3: a(href='/item/#{item.id}')=item.title =item.description else p No items to show h2 Create new item form(action='/create', method='post') p label Title input(type='text', name='title') p label Description textarea(name='description') p button Submit - views/tasks.jade
h2 Tasks for project #{project.title} #list ul#tasks-list each task in tasks li span= task.title a.delete(href='/task/#{task.id}') X form#add(action='/project/#{project.id}/tasks', method='POST') input(type= 'text', name= 'title') button Add - public.main.js
$(function () { $('form').submit( function (ev) { ev.preventDefault(); var form = $(this); $.ajax({ url: form.attr('action'), type: 'POST', data: form.serialize(), success: function (obj) { var el = $('<li>'); if ($('#projects-list').length) { el.append($('<a>').attr('href', '/project/' + obj.id + '/tasks').text(obj.title + ' ')) .append($('<a>').attr('href', '/project/' + obj.id).attr('class', 'delete').text('x')); } $('ul').append(el); } }); form.find('input').val(''); //clear input $('ul').delegate('a.delete', 'click', function (ev) { ev.preventDefault(); var li = $(this).closest('li'); $.ajac({ url: $(this).attr('href'), type: 'DELETE', SUCCESS: function () { li.remove(); } }) }) }); });
