8. NodeJS连接Mysql

Mike

　　1.mysql模块安装
　　npm install mysql --save
　　2.连接数据库：db_config.js

const mysql = require('mysql')
//创建连接
const conn = mysql.createConnection({
  host:'112.126.63.18',
  user:'lijing',
  password:'lijing123',
  port:'3306',
  database:'zhifu'
})
//建立连接
conn.connect()
let sql = 'select * from user'
//执行sql语句
conn.query(sql,(err,result)=>{
  if(err) throw err
  console.log(result)
})
//关闭连接
conn.end()

　　结果：

3.通过占位符实现传参,query方法第二参数就是会填充sql语句里的？

const mysql = require('mysql')
//创建连接
const conn = mysql.createConnection({
  host:'112.126.63.18',
  user:'lijing',
  password:'lijing123',
  port:'3306',
  database:'zhifu'
})
//建立连接
conn.connect()

let sql = 'select * from user where name = ? and id = ?'
//执行sql语句
conn.query(sql,['fff','1'],(err,result)=>{
  if(err) throw err
  console.log(result)
})
//关闭连接
conn.end()

　　4. mysql连接池与普通连接的区别以及它的使用方法
　　4.1 频繁的创建、关闭连接会减低系统的性能，提高系统的开销

　　
4.2 连接池可以有效的管理连接，达到连接复用的效果

　　
4.3 连接池的使用

const mysql = require('mysql')
//创建连接池
const pool = mysql.createPool({
  host:'112.126.63.18',
  user:'lijing',
  password:'lijing123',
  port:'3306',
  database:'zhifu'
})
//获取连接
pool.getConnection((err, conn) => {
  if (err) throw err
  let sql = 'select * from user'
  //执行sql语句
  conn.query(sql,(err, result) => {
  conn.release()
  if (err) throw err
  console.log(result)
  })
})

　　5.数据库增删改查用户
　　
5.1 数据库配置

let dbOption
dbOption = {
  connectionLimit: 10,
  host:'112.126.63.18',
  user:'lijing',
  password:'lijing123',
  port:'3306',
  database:'zhifu'
}
module.exports = dbOption

5.2 数据库连接，以及query方法封装:config.js

const mysql = require('mysql')
const dbOption = require('../config/db_config')
//创建连接池
const pool = mysql.createPool(dbOption)

function query (sql,params) {
  return new Promise((resolve, reject) => {
  //获取连接
  pool.getConnection((err, conn) => {
    if (err){
    reject(err)
    return
    }
    //执行sql语句
    conn.query(sql, params, (err, result) => {
    conn.release()
    if (err) {
      reject(err)
      return
    }
    resolve(result)
    })
  })
  })
}

module.exports = query

server:

const http = require('http');
const routerModal = require('./router/index')
const url = require('url')
const getPostData = (req) => {
  return new Promise((resolve, reject) => {
  if (req.method !== 'POST') {
    resolve({})
    return
  }
  let postData = '';
  req.on('data', chunk => {
    postData += chunk;
  })
  req.on('end', () => {
    // console.log(postData)
    if(postData){
    resolve(JSON.parse(postData))
    }else{
    resolve({})
    }
  })
  })
}
const server = http.createServer((req, res) => {
  //设置允许跨域的域名，*代表允许任意域名跨域
  res.setHeader("Access-Control-Allow-Origin","http://127.0.0.1:5501");
  res.writeHead(200, { 'content-type': 'application/json;charset=UTF-8' })
  getPostData(req).then((data) => {
  req.body = data
  let result = routerModal(req, res);
  if (result) {
    result.then(resultData =>{
    res.end(JSON.stringify(resultData))
    })  
  } else {
    res.writeHead(404, { 'content-type': 'text/html' })
    res.end('404 not found')
  }
  })
})
server.listen(3000, () => {
  console.log('监听3000端口')
})

5.3 增加：server通过获取的promise结果来获取数据，并且返回结果

controller:引用的query方法
module.exports = {
  async addUser(userObj){
  console.log(userObj);
  let {name,city,sex} = userObj
  let sql = 'insert into user (name,city,sex) values (?,?,?)'
  let resultData = await query(sql,[name,city,sex])
  if(resultData){
    return {
    msg:'新增成功'
    }
  }else{
    return {
    msg:'新增失败'
    }
  }  
  }
}

路由router：
const url = require('url')
const {getUserList,addUser,delectUser,updateUser} = require('../controller/user')
function handleRequest(req,res) {
  if(urlObj.pathname === '/api/addUser'&&req.method === 'POST'){
  let resultData = addUser(req.body);
  console.log(resultData,'index.js')
  return resultData;
  }
}
module.exports = handleRequest

5.4 更新用户接口

controller:引用的query方法
module.exports = {
  async updateUser(id,userObj){
  // console.log(id,userObj);
  let {name,city,sex} = userObj
  let sql = 'update user set name = ?,city = ?,sex = ? where id = ?'
  let resultData = await query(sql,[name,city,sex,id])
  if(resultData.affectedRows > 0){
    return {
    msg:'更新成功'
    }
  }else{
    return {
    msg:'更新失败'
    }
  }
}

路由router：
const url = require('url')
const {getUserList,addUser,delectUser,updateUser} = require('../controller/user')
function handleRequest(req,res) {
  let urlObj = url.parse(req.url,true);
  if(urlObj.pathname === '/api/updateUser'&&req.method === 'POST'){
  let resultData = updateUser(urlObj.query.id,req.body);
  return resultData;
  }
}
module.exports = handleRequest

5.5 删除用户接口

controller:引用的query方法
module.exports = {
  async delectUser(id){
  let sql = 'delete from user where id = ?'
  let resultData = await query(sql,[id])
  if(resultData.affectedRows > 0){
    return {
    msg:'删除成功'
    }
  }else{
    return {
    msg:'删除失败'
    }
  }
  }
}

路由router：
const url = require('url')
const {getUserList,addUser,delectUser,updateUser} = require('../controller/user')
function handleRequest(req,res) {
  let urlObj = url.parse(req.url,true);
  if(urlObj.pathname === '/api/delectUser'&&req.method === 'POST'){
  let resultData = delectUser(urlObj.query.id);
  return resultData;
  }
}
module.exports = handleRequest

5.6 通过sql语句操作数据库实现动态查询

controller:引用的query方法
module.exports = {
  async getUserList(urlParams){
  let {name,city} = urlParams
  let sql = 'select * from user where 1=1 '
  if(name){
    sql += 'and name = ?'
  }
  if(city){
    sql += 'and city = ?'
  }
  let resultData = await query(sql,[name,city])
  return resultData
  }
}

路由router：
const url = require('url')
const {getUserList,addUser,delectUser,updateUser} = require('../controller/user')
function handleRequest(req,res) {
  let urlObj = url.parse(req.url,true);
  if(urlObj.pathname === '/api/getUserList'&&req.method === 'GET'){
  let resultData = getUserList(urlObj.query)
  return resultData;
  }
}
module.exports = handleRequest