从ejs文件连接到mysql

Question

我是node.js新手，我正在努力学习如何从ejs文件连接mysql数据库。我试图搜索示例代码，但是代码不起作用。有人能帮我查一下吗。谢谢。

function loaddata() {

          var sql = require("mysql");
          var con = mysql.createConnection({});
          con.connect(function (err) {
              if (err) {
                  console.log('Error connecting to Db');
                  return;
              }
              console.log('Connection established');
          });
          con.query('update students set name="sus" where email="smn14@mail.aub.edu"', function (err, rows) {
              if (err) throw err;

              console.log('Data received from Db:\n');
              console.log(rows);
          });
          con.end(function (err) {
              // The connection is terminated gracefully
              // Ensures all previously enqueued queries are still
              // before sending a COM_QUIT packet to the MySQL server.
          });


      }

Answer 1

创建连接最差。

 var mysql      = require('mysql'); 
 var connection = mysql.createConnection({   
     host : 'localhost',
     user : 'me', 
     password : 'secret',   
     database : 'my_db' 
 });     
 connection.connect();     
 connection.query('SELECT 1 + 1 AS solution', function(err, rows,
 fields) {   
     if (err) throw err;     
     console.log('The solution is: ', rows[0].solution); });     
 connection.end();

从这个示例中，您可以了解以下内容： 在连接上调用的每个方法都按顺序排队和执行。关闭连接是使用end()完成的，这确保在将退出数据包发送到 mysql服务器。

文档

Answer 2

我现在了解了服务器/客户端的过程。这是有意义的，否则您将能够看到存储在Client.js中的数据库密码。:-)

但是，有一种方法适合我。客户端调用javascript-函数并向服务器发送消息。服务器接收到此消息并启动数据库查询。通过socket.io将结果发送到所有客户端

在file.ejs中的客户端

<script type='text/javascript'>

  let socket = io.connect();

  function getSql(userId) {
    socket.emit('start-new-sql-querie',{
        userId: userId
    });
  }

  socket.on('new-sql-result', function (data){ // listen for the new sql result
      console.log(data.userStatus); // foo something with the new data
  })

</script>

<button onclick="getSql(1)">Test sql query</button>

服务器端的数据库connection.js

const connection = {
    connectionLimit: 10,
    host: "localhost",
    user: "Abc",
    password: "1234",
    database: "d001",
    multipleStatements: true
};

module.exports = connection;

服务器端的yourapp.js

const express = require('express'); 
const port = process.env.PORT || 1234;
const app = express();
const server = require('http').createServer(app);
const mysql = require('mysql2');
const config = require('./routes/connection'); // SQL-Connection
const pool = mysql.createPool(config);

let io = require('socket.io')(server);

io.sockets.on('connection', function(socket) {

  socket.on('start-new-sql-querie', function(data) { // listen from the clients

    let user_id = data.userId;
    sql_test.getConnection((error, connection) => { // Connect to sql database
      console.log("user_id: ", user_id)
      connection.query(`SELECT * FROM user WHERE id='${user_id}'`, (err, result) => {
        socket.emit('new-sql-result',{ // send sql result-status to all clients
          userStatus: result.result[0].status
        })
      })
      connection.release();
    })

  });

})