mysql - 如何将 mysql 数据库内容传递到不同的页面？

Question

我正在尝试将我的数据库内容显示到 ejs 网页。但是，当我尝试在页面之间传递内容时遇到了问题。

我有一个 JavaScript 页面 "store.js"服务器正在运行:

store.js-

var express = require('express');
var dbcon = require('./app/db/databaseconnection');
//var path = require('path');
//dbcon.connection;

var app = express();
var router = express.Router();

dbcon.connect();
console.log(dbcon.getproducts());

//var filepath = path.join(__dirname, '../../views/')
var filepath = __dirname + '/views/';
app.set('view engine', 'ejs');
app.use(express.static(__dirname + '/public'));
app.use('/', router);

router.get('/', (request, response) => response.render(filepath + 'index', { page_name: 'home' }));
router.get('/store', (request, response) => response.render(filepath + 'store', { page_name: 'store' }));
router.get('/about', (request, response) => response.render(filepath + 'about', { page_name: 'about' }));
router.get('/contact', (request, response) => response.render(filepath + 'contact', { page_name: 'contact' }));
router.get('/build', (request, response) => response.render(filepath + 'build/build'));
router.get('/learn', (request, response) => response.render(filepath + 'learn/learn'));
app.use('*', (request, response) => response.render(filepath + '404', { page_name: '404' }));
app.listen(3000, () => console.log("Server running at Port 3000"));

然后我有一个包含数据库连接的 JavaScript 页面:

databaseconnection.js-

var mysql = require('mysql');
var connection = mysql.createConnection(conObject = {
    host: "localhost",
    user: "root",
    password: "LOTOS123l",
    database: "dbComputerStore"
});


module.exports = {
    connect: () => 
    { 
        connection.connect((error) => {
            if (error) throw error;
            console.log(conObject.database + " connected!");
        });
    },
    // The display method prints the returned result to the console.
    // Change this to return the result. Maybe a toString() ?
    getproducts: () => {
        /*var result = */connection.query('SELECT * FROM products', (error, result) => { 
            return result;
            //return result;
            // console.log(result);
            // How to get certain properties
            // console.log(result[0].brand);
            // console.log(result[0].series);
            // console.log(result[0].model);
        });
        //return result.result;
    },
    createdb: () => {

    },
    createtable: () => {

    },
    populatetable: () => {

    }
}

所以关于store.js页面我有 console.log(dbcon.getproducts());我希望能够显示 "products" 的数据库内容 table 。然而我总是变得不确定。基本上，我无法让它从数据库连接页面传递到 store.js 页面。

如果我让它工作，我的下一步将是找到一种将产品表显示到 ejs 页面的方法。我已经尝试解决这个问题有一段时间了，因此我们将不胜感激!谢谢!

Answer 1

根据我的评论，您可以通过以下方式修改 databaseconnection.js 模块以使用 connection pool并通过Promises返回查询结果:

注意:代码大部分未经测试

const mysql = require("mysql");

const pool = mysql.createPool({
  connectionLimit: 10, // adjust this according to your needs
  host: "localhost",
  user: "root",
  password: "LOTOS123l",
  database: "dbComputerStore"
});

module.exports = {
  // you don't need the connect method anymore

  getProducts: () => new Promise((resolve, reject) => {
    pool.query("SELECT * FROM products", (error, results, fields) => {
      if (error) {
        reject(error);
      } else {
        resolve(results);
      }
    });
  }),
  // ...
};

然后在store.js中，您可以执行以下操作:

const dbcon = require('./app/db/databaseconnection');

dbcon.getProducts()
  .then(results => {
    console.log(results);
  })
  .catch(err => {
    console.error(err);
  });

// or you could use async/await syntax:

const asyncFn = async () => {
  try {
    const results = await dbcon.getProducts();
    console.log(results);
  } catch (ex) {
    console.error(err);
  }
};
asyncFn();