[微精通:網頁設計Vue + Node.js + MySQL] 5.2.MySQL套件 (上)

接下來,我們將API與資料庫整合在一起,好讓API可以永久的保存資料在資料庫中。此為上半部。

 

image/svg+xml5.2. MySQL 套件 var mysql = require ( ' mysql ' ); var express = require( 'express' ); var api = express(); api.use(express.static( 'html' )); function getConnection () { var connection = mysql . createConnection ( { host : '127.0.0.1' , user : 'root' , password : '1234' , database : 'ZombieDB' } ); return connection ; } function testConnection () { let connection = getConnection (); connection . connect (); console.log( ' 連線成功 ! ' ); connection . end (); console.log( ' 斷線 !' ); } testConnection (); // 略過⼀些程式 // 略過⼀些程式 api. post ( '/api/ getZombies ' , function (req, res) { var result = { data: null , error: null } ; let connection = getConnection (); connection. connect (); connection. query ( ' SELECT * FROM Zombie , function ( err , rs ) { if ( err ){ result. error = err.message ; } else { rs = rs.map (x => { x. isVegetarian = x.isVegetarian == 1 ? true : false return x; }); result. data = rs ; } res.send (JSON.stringify( result )); connection . end (); } ); } ); // 略過⼀些程式 api.listen( 80 , function () { console.log( 'Zombie api listening on port 80!' ); }); // 略過⼀些程式 api.post( '/api/ addZombie ' , express.text(), function (req, res) { var result = { data: null , error: null } ; try { let zombie = JSON.parse ( req.body ); if ( typeof zombie.name != 'string' || zombie.name.trim() == "" ){ result.error = " 請輸入名字且必須為字串。 " ; res.send(JSON.stringify(result)); return ; } if ( typeof zombie.age != 'number' ){ result.error = " 請輸入變異年份且必須為數值。 " ; res.send(JSON.stringify(result)); return ; } if ( typeof zombie.isVegetarian != 'boolean' ){ result.error = " 請輸入是否為素食者且必須為布林值。 " ; res.send(JSON.stringify(result)); return ; } if ( typeof zombie.typeImgSrc != 'string' || [ 'z1.png' , 'z2.png' , 'z3.png' , ‘z4.png' , 'z5.png' ].indexOf(zombie.typeImgSrc) == - 1 ){ result.error = " 請輸入類型圖片,且值必須為 z1.png z2.png z3.png z4.png z5.png " ; res.send(JSON.stringify(result)); return ; } let isVegetarian = zombie . isVegetarian == true ? 1 : 0 ; let connection = getConnection (); connection. connect (); connection. query ( " INSERT INTO Zombie (name, age, isVegetarian, typeImgSrc) VALUES (?, ?, ?, ?) ;” , [ zombie . name , zombie . age , isVegetarian , zombie . typeImgSrc ] , function (err, rs) { if ( err ){ result. error = err.message ; } else { zombie . id = rs . insertID ; result. data = zombie ; } res.send (JSON.stringify( result )); connection . end (); } ); } catch (e){ result.error = e.toString(); res.send(JSON.stringify(result)); } }); // 略過⼀些程式 api.listen( 80 , function () { console.log( 'Zombie api listening on port 80!' ); }); // 略過⼀些程式 api.post( '/api/ updateZombie ' , express.text(), function (req, res) { var result = { data: null , error: null } ; try { let zombie = JSON.parse ( req.body ); if ( typeof zombie.id != 'number' ){ result.error = " 請輸入有效的 id " ; res.send(JSON.stringify(result)); return ; } if ( typeof zombie.name != 'string' || zombie.name.trim() == "" ){ result.error = " 請輸入名字且必須為字串。 " ; res.send(JSON.stringify(result)); return ; } if ( typeof zombie.age != 'number' ){ result.error = " 請輸入變異年份且必須為數值。 " ; res.send(JSON.stringify(result)); return ; } if ( typeof zombie.isVegetarian != 'boolean' ){ result.error = " 請輸入是否為素食者且必須為布林值。 " ; res.send(JSON.stringify(result)); return ; } if ( typeof zombie.typeImgSrc != 'string' || [ 'z1.png' , 'z2.png' , 'z3.png' , 'z4.png' , 'z5.png' ].indexOf(zombie.typeImgSrc) == - 1 ){ result.error = " 請輸入類型圖片,且值必須為 z1.png z2.png z3.png z4.png z5.png " ; res.send(JSON.stringify(result)); return ; } let isVegetarian = zombie . isVegetarian == true ? 1 : 0 ; let connection = getConnection (); connection. connect (); connection. query ( " UPDATE Zombie SET name= ? , age= ? , isVegetarian= ? , typeImgSrc= ? WHERE id= ? ;" , [ zombie . name , zombie . age , isVegetarian , zombie . typeImgSrc , zombie . id ] , function (err, rs) { if ( err ){ result.error = err.message ; } else { if ( rs . affectedRows == 0 ){ result. error = 請輸入有效的 id ' ; } else { result. data = zombie ; } } res.send (JSON.stringify( result )); connection . end (); } ); } catch (e){ result.error = e.toString(); res.send(JSON.stringify(result)); } }); // 略過⼀些程式 api.listen( 80 , function () { console.log( 'Zombie api listening on port 80!' ); }); // 略過⼀些程式 api.post( '/api/ deleteZombie ' , express.text(), function (req, res) { var result = { data: null , error: null } ; try { let zombie = JSON.parse ( req.body ); if ( typeof zombie.id != 'number' ){ result.error = " 請輸入有效的 id " ; res.send(JSON.stringify(result)); return ; } let connection = getConnection (); connection. connect (); connection. query ( " DELETE FROM Zombie WHERE id= ? ;" , [ zombie . id ], function (err, rs) { if ( err ){ result. error = err.message ; } else { if ( rs . affectedRows == 0 ){ result. error = 請輸入有效的 id ' ; } else { result. data = { 'result' : ' ok ' } ; } } res.send (JSON.stringify( result )); connection . end (); } ); } catch (e){ result.error = e.toString(); res.send(JSON.stringify(result)); } }); api.listen( 80 , function () { console.log( 'Zombie api listening on port 80!' ); });