Node.js自學筆記 (9/12)：MySQL資料庫處理

Web開發 Node.js
1

若在npm搜尋mysql，會看到mysql與mysql2的套件，其中，mysql套件是官方出的，沒有promise功能，此時會使用bluebirs.js來做promise的功能。mysql的同一批開發人員開發出mysql2，具有promise功能，使用原生方式連線，號稱地表最快的MySQL連線套件。

mysql2提供的範例：

// get the client
const mysql = require('mysql2');
 
// create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test'
});
 
// simple query
connection.query(
  'SELECT * FROM `table` WHERE `name` = "Page" AND `age` > 45',
  function(err, results, fields) {
    console.log(results); // results contains rows returned by server
    console.log(fields); // fields contains extra meta data about results, if available
  }
);
 
// with placeholder
connection.query(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Page', 45],
  function(err, results) {
    console.log(results);
  }
);

Using Prepared Statements

// get the client
const mysql = require('mysql2');
 
// create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test'
});
 
// execute will internally call prepare and query
connection.execute(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Rick C-137', 53],
  function(err, results, fields) {
    console.log(results); // results contains rows returned by server
    console.log(fields); // fields contains extra meta data about results, if available
 
    // If you execute same statement again, it will be picked from a LRU cache
    // which will save query preparation time and give better performance
  }
);

Using connection pools

因為資料庫聯會需要較多資源，所以一般連線後，不隨便斷線，這裡使用連線池的概念，不輕易開啟先連線，使用舊有的連線，所以速度會較快。

// get the client
const mysql = require('mysql2');
 
// Create the connection pool. The pool-specific settings are the defaults
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

實際上，以模組方式來實作

一個專案會有好幾個路由需要連線到資料庫，運用模組概念，可以使用require導入，之前有提到，多處require一個模組，只會載入一次，就可以在多了路由使用。

建立檔案： /src/db_connect2.js

const mysql = require('mysql2');
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '88888888',
    database: 'test',
    waitForConnections: true,
    connectionLimit: 10, // 最大連線數
    queueLimit: 0
});
module.exports = pool.promise(); // 滙出 promise pool
// 這個promise()會把pool物件包成具有promise功能
// 須注意我們所匯出的express是一個已經有primise過的db pool

建立 /src/address_book.js檔案：

const express = require('express');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/list', (req, res) => {
    res.send('ok');
})

module.exports = router;

在index.js新增以下路由：

app.use('/address-book', require(__dirname + '/address_book'));

IMG_0614
IMG_06141763×320 62.6 KB

測試連線：
image

連線成功後，再來測試資料庫讀取。

測試讀取資料庫中的資料

將address_book.js加上SELECT的SQL語法：

const express = require('express');
// 因為看不到外面的express，所以要自己再require一次
const db = require(__dirname + '/db_connect2')
// 這個db物件是已經有primise過的物件

const router = express.Router();
router.get('/list', (req, res) => {
    db.query("SELECT * from address_book LIMIT 1") 
    // 須注意SQL語法的外部要使用雙引號框起來，因為SQL語法中可能會使用單引號
        .then(([result, fileds]) => {
        //為什麼要包成array [result, fileds]，因為在promise的結構裡面，他只能丟一個值出來
        // 所以對於primise的要求，所傳出來的值可以用array或object
            res.json(result);
        })
})

module.exports = router;
// 只有匯出的物件才可被看到，只有匯出的物件才可被使用

測試 http://localhost:3000/address-book/list ：

image
image1039×139 14.5 KB

代表資料有成功讀取。所拿到的資料會是一個Array [] ，裡面有物件 {} 。因為我們在SQL語法中 LIMIT 1 ，所以只會有一個物件被讀取出來。如果改成 LIMIT 2 ，就會讀出2個物件。

PHP的require或 inclide就相當於把對方的檔案內容拷貝一份，貼過來。

Node.js的require則不同，只有匯出的物件可以被使用。一個js所能匯出的物件只有一個。其他的變數、物件都是看不到的，只有匯出的物件才可被看到，只有匯出的物件才可被使用。

如果真的要匯出的東西很多，可以把多個東西以Array包在物件裡面，來做匯出。

測試讀取資料筆數

將 address_book.js 修改為：

const express = require('express');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/list', (req, res) => {
    db.query("SELECT COUNT(*) from address_book")
        .then(([result, fileds]) => {
            res.json(result);
        })
})

module.exports = router;

實際連線 http://localhost:3000/address-book/list ，結果：
image

將 address_book.js 修改為：

const express = require('express');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/list', (req, res) => {
    db.query("SELECT COUNT(*) num from address_book")  // 給予總比數一個數值 num
        .then(([result, fileds]) => {
            res.json(result);
        })
})

module.exports = router;

實際連線 http://localhost:3000/address-book/list ，結果：
image

把拿到的資料作成表格

將 address_book.js 修改為：

const express = require('express');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/list', (req, res) => {
    db.query("SELECT * from address_book LIMIT 10")
    // db.query("SELECT COUNT(*) num from address_book")
        .then(([result, fileds]) => {
            res.json(result);
        })
})

module.exports = router;

實際連線 http://localhost:3000/address-book/list ，結果：

image
image1034×651 110 KB

在 views 新增資料夾 address-book，之後把所有跟address-book相關的網頁模板放到這個資料夾中，在 /views/address-book 新增檔案：list.ejs。然後把之前的範本貼到list.ejs：

<%- include ('parts/html-head') %>
<%- include ('parts/navbar') %>
<div class="container">
    <%= name %>
</div>
<%- include('parts/scripts') %>
<%- include('parts/html-foot') %>

然後到bootstrap官網挑一個table範本：Tables · Bootstrap v4.5 ，選用這個：Tables · Bootstrap v4.5

image
image1214×501 25.3 KB

將list.ejs改為：

<%- include ('../parts/html-head') %>
<%- include ('../parts/navbar') %>
<div class="container">
    <table class="table table-striped">
        <thead>
            <tr>
                <th scope="col">sid</th>
                <th scope="col">Name</th>
                <th scope="col">Email</th>
                <th scope="col">Cell Phone</th>
                <th scope="col">Birthday</th>
                <th scope="col">Address</th>
            </tr>
        </thead>
        <tbody>
            <% for( let i of rows) { %>
            <tr>
                <td><%= i.sid %> </td>
                <td><%= i.name %> </td>
                <td><%= i.email %> </td>
                <td><%= i.mobile %> </td>
                <td><%= i.birthbay %> </td>
                <td><%= i.address %> </td>
            </tr>
            <% } %>
        </tbody>
    </table>
</div>
<%- include('../parts/scripts') %>
<%- include('../parts/html-foot') %>

將address-book.js改為：

const express = require('express');
const db = require(__dirname + '/db_connect2')

const router = express.Router();
router.get('/list', (req, res) => {
    db.query("SELECT * from address_book LIMIT 10")
    // db.query("SELECT COUNT(*) num from address_book")
        .then(([rows]) => {
            res.render('address-book/list', {rows})
            // res.json(result);
        })
})

module.exports = router;

連線到：http://localhost:3000/address-book/list
實測結果：

image
image1684×894 67.3 KB