node 连接MySQL及其分装, 连接池连接

const mysql = require('mysql')

const config = require('./../../config/config.default')

var connection = mysql.createConnection({
  host: config.database.HOST,
  user: config.database.USERNAME,
  password: config.database.PASSWORD,
  database: config.database.DATABASE
})

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack)
    return
  }
  console.log('Database connection successful, connected as id ' + connection.threadId)
})

//查询
// connection.query(`select * from area`, function(err, results, fields) {
//   if (err) throw err
//   console.log(results[0])
//   console.log(fields[0])
// })



//关闭连接
// connection.end(function(err) {
//   if (err) {
//     console.error('数据库关闭失败' + err)
//   }
//   console.log('数据库关闭')
// })

module.exports = connection

// 分装

const mysql = require('mysql')

const config = require('./../../config/config.default')

module.exports = {
  query: function(sql, params, callback) {
    var connection = mysql.createConnection({
      host: config.database.HOST,
      user: config.database.USERNAME,
      password: config.database.PASSWORD,
      database: config.database.DATABASE
    })
    connection.connect(function(err) {
      if (err) {
        console.error('error connecting: ' + err.stack)
        throw err
      }
      console.log('Database connection successful, connected as id ' + connection.threadId)
      connection.query(sql, params, function(err, results, fields) {
        if (err) throw err
        callback && callback(results, fields)
        connection.end(function(err) {
          if (err) {
            console.error('数据库关闭失败' + err)
            throw err 
          }
          console.log('数据库关闭')
        })
      })    
    })
  },
  // 连接池连接
  queryPool: function(sql, params, callback) {
    var pool = mysql.createPool({
      host: config.database.HOST,
      user: config.database.USERNAME,
      password: config.database.PASSWORD,
      database: config.database.DATABASE
    })
    pool.getConnection(function(err, connection) {
      // 使用连接
      connection.query(sql, params, function(err, results, fields) {
        if (err) throw err
        callback && callback(results, fields)
        connection.release()     
        console.info('连接完成,不再使用,返回到连接池')
      })
    })
  }
}


// 调用
const db = require('./index')


db.query(`select * from area`, [], function(result, fields) {
  console.log('查询结果：')
  console.log(result[0].name)
})

db.queryPool(`select * from area`, [], function(result, fields) {
  console.log('查询结果：')
  console.log(result[0])
})

　　

 

const db = require('../db')
const query = db.queryPoolPromise

/**
 * @author afacode
 * SQL 操作
 * @date 2018.08.15
 */
let findAllUser = (value, col = '*') => {
  let _sql = `select ${col} from user_login where user_id = ?;`
  return query(_sql, value)
}
// 测试
async function user() {
  let user = await findAllUser([22], ['username', 'password'])
  
console.log(user)
}

user()