Robby Wu 的技術 Blog

node.js 搭載 mysql 之新手教學，請安心服用。

Node.js 可以搭配許多種 DataBase 應用，例如 MongoDB、MySQL、MSSQL等。

本篇介紹 MySQL 整合應用，並以 Express4 做為開發框架，

若對 node.js express 不熟悉，建議可以先看此篇介紹 Node.js Express4。

實作說明

本篇將利用 mysql 實作出（使用者）資料表新增、刪除、修改、搜尋。

mysql 資料表

框架安裝

到專案目錄底下，建立環境並安裝（本篇採用 ejs 樣板設計）。

express -e

npm install

資料庫溝通套件安裝

請留意是否您的環境已經安裝好 mysql 服務，

如果尚未安裝，可以參考 WampServer 或是 XAMPP。

此套件僅為與資料庫溝通，並非包含 MySQL！

上述完成安裝後才可以使用本套件，安裝 mysql

npm install mysql

連線設定

開啟 app.js ，在 var users 下方加入 mysql 引用。

var users = require('./routes/users');

// DataBase 
var mysql = require("mysql");

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "test"
});

con.connect(function(err) {
    if (err) {
        console.log('connecting error');
        return;
    }
    console.log('connecting success');
});

建立 con 連線物件，並利用 connet 進行連線
host：連線主機
user：帳號
password：密碼
database：資料庫

在此 app.use(express ...) 段程式碼下方加入。

app.use(express.static(path.join(__dirname, 'public')));

// db state
app.use(function(req, res, next) {
    req.con = con;
    next();
});

完整 app.js 內容。

var express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

var routes = require('./routes/index');
var users = require('./routes/users');

// DataBase 
var mysql = require("mysql");

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "test"
});

con.connect(function(err) {
    if (err) {
        console.log('connecting error');
        return;
    }
    console.log('connecting success');
});

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

// uncomment after placing your favicon in /public
//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

// db state
app.use(function(req, res, next) {
    req.con = con;
    next();
});

app.use('/', routes);
app.use('/users', users);


// catch 404 and forward to error handler
app.use(function(req, res, next) {
    var err = new Error('Not Found');
    err.status = 404;
    next(err);
});

// error handlers

// development error handler
// will print stacktrace
if (app.get('env') === 'development') {
    app.use(function(err, req, res, next) {
        res.status(err.status || 500);
        res.render('error', {
            message: err.message,
            error: err
        });
    });
}

// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
    res.status(err.status || 500);
    res.render('error', {
        message: err.message,
        error: {}
    });
});


module.exports = app;

SQL 應用

接下來進行簡單的 SELECT 語法取出資料，

編輯 index.js，在 / 根目錄 router 部分，我們將取出資料表內容。

// home page
router.get('/', function(req, res, next) {

    var db = req.con;
    var data = "";

    db.query('SELECT * FROM account', function(err, rows) {
        if (err) {
            console.log(err);
        }
        var data = rows;

        // use index.ejs
        res.render('index', { title: 'Account Information', data: data});
    });

});

建立 var db 賦予 req.con 連線物件資訊
db.query( ) 為進行資料庫存取，返回結果為 err、rows
回傳資料 rows 以陣列格式儲存
在 render 部分，我們將 rows 指定到 data 變數
data: data，此為給予名稱 data，其內容為 data，將於 ejs 樣板部分使用

再來則是編輯首頁樣板 index.ejs。

<h1>Account - List</h1>
    <table>
        <tr>
            <th>id</th>
            <th>userid</th>
            <th>password</th>
            <th>email</th>
        </tr>
        <% for ( var i = 0 ; i < data.length ; i++){ %>
            <tr>
                <td>
                    <%= data[i].id  %>
                </td>
                <td>
                    <%= data[i].userid  %>
                </td>
                <td>
                    <%= data[i].password  %>
                </td>
                <td>
                    <%= data[i].email  %>
                </td>
            </tr>
            <% } %>
    </table>

在 ejs 樣板設計中，利用 <% %> 可執行 javascript 語法，

我們以此方式，將資料集印出。

利用 data.length ，可取得資料筆數
data[i].id：引數 i 為 DataTable 中的列索引，id 為資料表欄位名稱

完成後加點 css 樣式，並執行環境。

npm start

http://localhost:3000/ ，畫面為如下所示。

新增資料

在 index.js 檔案中，加入 /add 部分跳轉至新增資料畫面，並建立一個 userAdd.ejs 樣板。

index.ejs，在首頁加入「新增」按鈕

<input type='submit' value='Add' onclick="javascript: location.href='add'">

index.js

當進入 http://localhost:3000/add 頁面時，render userAdd.ejs 樣板。

// add page
router.get('/add', function(req, res, next) {

    // use userAdd.ejs
    res.render('userAdd', { title: 'Add User'});
});

userAdd.ejs

form 部分，我們採用 post 方式，有別於原本 get ，將資料傳遞至 userAdd（由 router 執行）

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title></title>
</head>

<body>
    <form name="addform" action="userAdd" method="post" accept-charset="utf-8" >
        <h1>Account - Add</h1>
        <div>
            <label>userid：</label>
            <input type="text" name="userid" placeholder="userid" />
        </div>
        <div>
            <label>password：</label>
            <input type="password" name="password" placeholder="password">
        </div>
        <div>
            <label>email：</label>
            <input type="text" name="email" placeholder="email">
        </div>
        <div>
            <input type='submit' value='Submit'>
            <input type='reset' value='Reset'>
            <input type='button' value='Back' onclick="javascript: location.href='/'">
        </div>
    </form>
</body>

</html>

接下來為資料頁面 post 後，於 userAdd 進行資料 insert

// add post
router.post('/userAdd', function(req, res, next) {

    var db = req.con;

    var sql = {
        userid: req.body.userid,
        password: req.body.password,
        email: req.body.email
    };

    //console.log(sql);
    var qur = db.query('INSERT INTO account SET ?', sql, function(err, rows) {
        if (err) {
            console.log(err);
        }
        res.setHeader('Content-Type', 'application/json');
        res.redirect('/');
    });

});

post 過來的資料，可利用 req.body.[tag name] 取得
var sql 部分，將以 json 方式組成： userid 為資料表欄位名稱
在 mysql query 部分 " ? " ，可代表單一變數，或以 json格式表示
response 部分，setHeader 以 json 表示
並在完成 insert 後，導回首頁

完成後加點 css 如下結果：

http://localhost:3000/

http://localhost:3000/add

編輯資料

在剛剛首頁 index.ejs 部分，我們追加「編輯」按鈕，並且在點擊時，進入編輯頁面

<td>
　　<input type="button" name="edit" value="Edit" onclick="Edit('<%= data[i].id  %>');" />
</td>

function Edit(id) {
        window.location.href = "/userEdit?id=" + id;
    }

依據 id 值加在網址後面，讓編輯頁面能以 get 方式取得 key

回到 index.js 增加 userEdit，依接收到的 id 值串接 SQL

// edit page
router.get('/userEdit', function(req, res, next) {

    var id = req.query.id;
    var db = req.con;
    var data = "";

    db.query('SELECT * FROM account WHERE id = ?', id, function(err, rows) {
        if (err) {
            console.log(err);
        }

        var data = rows;
        res.render('userEdit', { title: 'Edit Account', data: data });
    });

});

若要取得網址列參數，可使用 req.query.[參數]
render 部分我們將載入 userEdit 樣板（下面步驟將建立此樣板）

建立編輯頁面樣板 userEdit.ejs

<form name="editform" action="userEdit" method="post" accept-charset="utf-8">
        <h1>Account - Edit</h1>
        <input type="hidden" name="id" value="<%=data[0].id  %>" />
        <div>
            <label>userid：</label>
            <input type="text" name="userid" placeholder="userid" value="<%=data[0].userid %>" readonly />
        </div>
        <div>
            <label>password：</label>
            <input type="text" name="password" placeholder="password" value="<%=data[0].password %>" />
        </div>
        <div>
            <label>email：</label>
            <input type="text" name="email" placeholder="email" value="<%=data[0].email %>">
        </div>
        <div>
            <input type='submit' value='Submit'>
            <input type='reset' value='Reset'>
            <input type='button' value='Back' onclick="javascript: window.history.back();">
        </div>
    </form>

form 部分我們使用 post，並傳到 userEdit（下方將新增 userEdit 的 post 事件）
帳號 userid 部分，使用 readonly，畢竟沒有人再改帳號啊～～
補上一些功能按鈕：reset、back

在 index.js 部分，新增 post 事件

router.post('/userEdit', function(req, res, next) {

    var db = req.con;
    var id = req.body.id;

    var sql = {
        userid: req.body.userid,
        password: req.body.password,
        email: req.body.email
    };

    var qur = db.query('UPDATE account SET ? WHERE id = ?', [sql, id], function(err, rows) {
        if (err) {
            console.log(err);
        }

        res.setHeader('Content-Type', 'application/json');
        res.redirect('/');
    });

});

post 方法使用 body進行取得 name 物件之內容
update 內容一樣以 json 方式儲存
header 部分：application/json
更新完畢後導回首頁 redirect('/');

完成後...一樣加點 css 給她，然後執行

npm start

http://localhost:3000/

試試修改資料並更新吧~，http://localhost:3000/userEdit?id=1

刪除資料

完成讀取、新增、修改後，接下來是「刪除」資料功能，

我們修改首頁，仿造修改按鈕，追加一枚刪除按鈕

index.ejs

<input type="button" name="delete" value="Delete" onclick="Delete('<%= data[i].id  %>');" />

function Delete(id) {
        var rs = confirm('Confirm to delete?');
        if (rs) {
            window.location.href = "/userDelete?id=" + id;
        }
    }

樣板修改完畢後，在 index.js 裡面追加 router

router.get('/userDelete', function(req, res, next) {

    var id = req.query.id;
    var db = req.con;

    var qur = db.query('DELETE FROM account WHERE id = ?', id, function(err, rows) {
        if (err) {
            console.log(err);
        }
        res.redirect('/');
    });
});

利用 get 方式，透過 req.query.id 將網址列的 id 參數取出
執行 DELETE query
完成後導回首頁 redirect('/');

一樣發揮創意補上 css (被打)，然後執行

npm start

http://localhost:3000/

搜尋資料

最後實作簡單的搜尋功能，在 index.ejs 樣板增加搜尋按鈕

<div class="search">
        <label>UserId：</label>
        <input type="text" name="suserid" value="" placeholder="input the userid">
        <input type="button" name="sSearch" value="Search" onclick="Search();">
    </div>

function Search() {

        var userid = document.getElementsByName('suserid')[0].value;
        window.location.href = "/?user=" + userid;;
    }

因搜尋是在首頁中執行，故我們修改根目錄的 router 事件

// home page
router.get('/', function(req, res, next) {

    var db = req.con;
    var data = "";

    var user = "";
    var user = req.query.user;

    var filter = "";
    if (user) {
        filter = 'WHERE userid = ?';
    }

    db.query('SELECT * FROM account ' + filter, user, function(err, rows) {
        if (err) {
            console.log(err);
        }
        var data = rows;

        // use index.ejs
        res.render('index', { title: 'Account Information', data: data, user: user });
    });

});

若網址參數 user有資料，則 pass 給 var user
如果 user 有資料，則串接篩選字串
在 render 部分，我們補上 user:user（將搜尋條件返回頁面顯示，有點像 ASP.NET 的 PostBack 概念）

因此回到剛剛首頁樣板 index.ejs，我們在搜尋文字欄位，將剛剛取得的 user 放入

<input type="text" name="suserid" value="<%=user  %>" placeholder="input the userid">

如此一來，每次搜尋完後，該欄位資料仍會顯示剛剛的文字

完成後補上奇妙的 css，然後執行

npm start

http://localhost:3000/

本實作範本 + 資料表下載

範例存於小弟的 Github ，不妨給個星星吧 ><

git clone https://github.com/explooosion/Node.js-Express-With-MySQL.git

進入專案

cd Node.js-Express-With-MySQL

安裝套件

npm install

匯入專案資料夾內 account.sql

以上為簡單的 Node.js Express + MySQL

教學範例來源為參考此篇文章：Node.js Express connectivity with PHPMyAdmin

有勘誤之處，不吝指教。ob'_'ov

Node.js - Express + MySQL

實作說明

框架安裝

資料庫溝通套件安裝

連線設定

SQL 應用

新增資料

編輯資料

刪除資料

搜尋資料

本實作範本 + 資料表下載

Git

More from this blog

React.js - 離開 vue 的我，和從前的 react 夥伴往 useState Lazy Initialization 邁進

JavaScript - 我獨自升級 substr

Git - 在工作與私人專案中來回切換 SSH

TypeScript - 在 interface 中理解 Pick, Omit, Partial, Intersection Types

Nginx - 405 not allowed ft. Cloudflare

Command Palette

實作說明

框架安裝

資料庫溝通套件安裝

連線設定

SQL 應用

新增資料

編輯資料

刪除資料

搜尋資料

本實作範本 + 資料表下載

Git

More from this blog