nodejs+mysql 1，nodejsmysql

nodejs+mysql 1，nodejsmysql
1.設定建立資料庫package.json

{"name":"shopping-cart-example","version":"0.0.1","dependencies":{"express":"4.10.6","express-session":"1.9.3","jade":"1.8.2","body-parser":"1.10.0","mysql":"2.5.4"}}

資料庫配置：

{"host":"localhost","user":"root","database":"cart_example"}

運行資料庫啟動代碼，將完成資料庫的初始建立工作，因為mysql和mongodb不同

var mysql = require('mysql'),config = require('./config');delete config.database;var db = mysql.createConnection(config);//串連資料庫db.on('error',function(){});//console.log(db);db.query('CREATE DATABASE IF NOT EXISTS `cart_example`');//建立資料庫db.query('USE `cart_example`');//使用資料庫db.query('DROP TABLE IF EXITS 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 EXITS 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();});

2.伺服器代碼

<pre name="code" class="javascript">var express = require('express');var session = require('express-session');//之前是包括在express中，現在獨立出來var bodyParser = require('body-parser');//之前是包括在express 中，現在獨立出來var mysql = require('mysql');//mysql 驅動var config = require('./config');var app = express();//app.use(session({secret:'my secret',resave:false,saveUninitialized: true}));app.use(bodyParser.urlencoded({ extended: true }));//只有這裡是true才能正確解析出POST資訊中的成員app.use(bodyParser.json());//處理用戶端傳來的參數，並且儲存在req.body.user中app.use(express.static('views'));//防止用戶端資源的檔案夾app.set('view engine','jade');//設定HTML解析引擎是jadeapp.set('view options',{layout:false});//var db = mysql.createConnection(config);//串連mysql資料庫//app.get('/',function(req,res,next){//首頁getconsole.log('---------------/------------------');db.query('SELECT id,title,description FROM item',function(err,results){//尋找所有物品console.log('------------------------get "/"');console.log(results);res.render('index',{items:results});//物品作為參數傳給首頁});});app.post('/create',function(req,res,next){//建立物品postconsole.log('---------------/create------------------');db.query('INSERT INTO item SET title=?,description=?',//插入一個新建立的物品[req.body.title,req.body.description],function(err,info){//[]中的參數替換'?'，防止SQL注入攻擊if(err)return next(err);console.log(' - item created with id %s', info.insertId);res.redirect('/');//重新置放到首頁，這樣物品會立刻顯示出來});}); app.get('/item/:id',function(req,res,next){//選擇物品的get 路徑console.log('---------------/item/:id------------------');function getItem(fn){//從資料庫中找到物品db.query('SELECT id,title,description FROM item WHERE id=? LIMIT 1',//找到對應id的物品[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){console.log('------------getReviews-------------');console.log('item=')console.log(item);console.log('reviews=');console.log(reviews);res.render('item',{item:item,reviews:reviews});//重新置放頁面，並傳遞物品，和評價});});});app.post('/item/:id/review',function(req,res,next){//評價物品路由console.log('---------------/item/:id/review------------------');db.query('INSERT INTO review SET item_id=?,stars=?,text=?',//插入評價資訊[req.params.id,req.body.stars,req.body.text],function(err,info){console.log(' - review created with id %s',info.insertId);res.redirect('/item/'  + req.params.id);//重新置放到物品頁面，這樣可以顯示評價資訊});});app.listen(3000,function(){console.log(' - listining on http://*:3000');});

3.用戶端檔案a.

doctype htmlhtmlheadtitle My shopping cartbodyh1 My shopping cart#cartblock body

b.index

extends ./layoutblock bodyh2 All itemsif (items.length)uleach item in itemslih3: a(href = "/item/#{item.id}")= item.title= item.descriptionelsep No items to showh2 Create new itemform(action="/create",method="POST")plabel Titleinput(type="text",name="title")plabel Descriptiontextarea(name="description")pbutton Submit

c.item

extends ./layoutblock bodya(href="/") Go backh2= item.titlep= item.descriptionh3 User reviewsif(reviews.length)each review in reviews.reviewb #{review.stars} starsp= review.texthrelsep No reviews to show.Write one!form(action="/item/#{item.id}/review",method="POST")fieldsetlegend Create reviewplabel Starsselect(name="stars")option 1option 2option 3option 4option 5plabel Reviewtextarea(name="text")pbutton(type="submit") Send

4.運行結果

a.資料庫狀態

b.控制台

c.index.html顯示

d.item.html顯示

本執行個體來源為：《了不起的nodejs》