Node系列学习之MySQL复盘

这是我参与更文挑战的第12天，活动详情查看：更文挑战

花了两天时间简单学习了一下Node中如何使用MySQL,可以看下我的两篇文章: Node系列学习之MySQL Node系列学习之MySQL(二) , 今天回家第一天, 早起+舟车劳顿+做饭, 比较累, 学不动了~ 所以今天先简单复盘一下这两天的学习！

文件分析

conf/db.js

存储环境变量与SQL链接数据库的相关配置

const env = process.env.NODE_ENV // 获取环境参数

let MYSQL_CONF

if (env == 'dev') {
  MYSQL_CONF = {
    host: 'localhost',
    user: 'root',
    password: '111',
    port: '3306',
    database: 'myblog'
  }
}
if (env == 'production') {
  MYSQL_CONF = {
    host: 'localhost',
    user: 'root',
    password: '111',
    port: '3306',
    database: 'myblog'
  }
}

module.exports = {
  MYSQL_CONF
}

controller

目录下的文件与 router 目录下的路由文件遥相对应, 只关心数据,从数据库拿到数据后返回, 不作任何处理

crotroller/blog.js

const {exec} = require('../db/mysql.js')

const getList = (author, keyword) => {
  
  let sql = `
    select * from blogs where 1=1 
  `
  if (author) {
    sql += `and author='${author}' `
  }
  if(keyword) {
    sql += `and title like '%${keyword}%' `
  }
  sql += `order by createtime desc;`
  // 返回的是个Promise
  return exec(sql)
  
}
// 博客详情
const getDetail = id => {
  const sql = `select * from blogs where id='${id}' `
  return exec(sql).then(rows => {
    return rows[0]
  })
}
// 新建博客
const newBlog = (blogData = {}) => {
  const title = blogData.title
  const content = blogData.content
  const author = blogData.author
  const createTime = Date.now()
  const sql = `
    insert into blogs (title,content,createtime,author) values ('${title}','${content}',${createTime}, '${author}');
  `
  return exec(sql).then(insertData => {
    // console.log('insertData',insertData);
    return {
      id: insertData.insertId
    }
  })
}
const updateBlog = (id,blogData= {}) => {
  const title= blogData.title
  const content = blogData.content
  const sql = `
    update blogs set title='${title}', content='${content}' where id=${id}; 
  `
  return exec(sql).then(updateData => {
    // console.log('updateData', updateData);
    if(updateData.affectedRows > 0) {
      return true
    }
    return false
  })

}
const delBlog = (id,author) => {
  const sql = `
    delete from blogs where id='${id}' and author='${author}';
  `
  return exec(sql).then(delData => {
    if(delData.affectedRows > 0) {
      return true
    }
    return false
  })
}
module.exports = {
  getList,
  getDetail,
  newBlog,
  updateBlog,
  delBlog
}

controller/user.js

const {exec} = require('../db/mysql.js')
const loginCheck = (username, password) => {
  const sql = `
    select username, realname from users where username='${username}' and password='${password}';
  `
  return exec(sql).then(rows => {
    return rows[0] || {}
  })
}
module.exports = {
  loginCheck
}

db/mysql.js

mysql的封装函数, 返回Promise, 将数据库返回的结果带入.

const mysql = require('mysql')
const { MYSQL_CONF } = require('../conf/db')
// 创建链接对象
const con = mysql.createConnection(MYSQL_CONF)

// 开始连接
con.connect()

// 同意执行 sql 的函数

function exec(sql) {
  return new Promise((resolve, reject) => {
    con.query(sql, (err, result) => {
      if (err) {
        reject(err)
        return
      }
      resolve(result)
    })
  })
}
module.exports = {
  exec
}

model

model/resModel.js

接口回复的统一模板, 有成功返回模板和失败返回模板

class BaseModel {
  constructor(data,message) {
    if(typeof data == 'string') {
      this.message = data
      data = null
      message = null
    }
    if(data) {
      this.data = data
    }
    if(message) {
      this.message = message
    }
  }
}

class SuccessModel extends BaseModel {
  constructor(data,message) {
    super(data, message)
    this.errno = 0
  }
}
class ErrorModel extends BaseModel {
  constructor(data,message) {
    super(data,message)
    this.errno = -1
  }
}
module.exports = {
  SuccessModel,
  ErrorModel
}

router

对路由接口处理, 通过引入 controller 下同名文件暴露的相关方法来获取数据,并对数据进行处理与最终返回

router/user.js

const { loginCheck } = require('../controller/user')
const { SuccessModel, ErrorModel } = require('../model/resModel')
const handleUserRouter = (req, res) => {
  const method = req.method

  // 登录
  if (method == 'POST' && req.path == '/api/user/login') {
    const { username, password } = req.body
    const result = loginCheck(username,password)
    return result.then(data => {
      if(data.username) {
        return new SuccessModel()
      }
      return new ErrorModel('登录失败~')
    })
  }
}
module.exports = handleUserRouter

router/blog.js

const { getList, getDetail, newBlog, updateBlog, delBlog } = require('../controller/blog')
const { SuccessModel, ErrorModel } = require('../model/resModel')
const handleBlogRouter = (req, res) => {
  const method = req.method
  const id = req.query.id
  // 获取博客列表
  if (method == 'GET' && req.path == '/api/blog/list') {
    const author = req.query.author || ''
    const keyword = req.query.keyword || ''
    // const listData = getList(author, keyword)
    // return new SuccessModel(listData)
    const result = getList(author,keyword)
    return result.then(listData => {
      return new SuccessModel(listData)
    })
  }
  // 获取博客详情
  if (method == 'GET' && req.path == '/api/blog/detail') {
    // const data = getDetail(id)
    // return new SuccessModel(data)
    const result = getDetail(id)
    return result.then(data => {
      return new SuccessModel(data)
    })
  }
  // 新建一篇博客
  if (method == 'POST' && req.path == '/api/blog/new') {
    // const data = newBlog(req.body)
    // return new SuccessModel(data)
    req.body.author = 'tmier' // 待开发登录完成后再改成真实数据
    const result = newBlog(req.body)
    return result.then(data => {
      return new SuccessModel(data)
    })
  }

  // 更新一篇博客
  if (method == 'POST' && req.path == '/api/blog/update') {
    const result = updateBlog(id, req.body)
    return result.then(val => {
      if(val) {
        return new SuccessModel()
      }
      return new ErrorModel('更新博客失败~')
    })
  }

  // 删除一篇博客
  if (method == 'POST' && req.path == '/api/blog/del') {
    let author = 'tmier'
    const result = delBlog(id,author)
    return result.then(val => {
      if(val) {
        return new SuccessModel()
      }
      return new ErrorModel('删除博客失败~')
    })
  }
}

module.exports = handleBlogRouter

app.js

对接受的req与返回的res进行统一设置与处理, 设置未命中路由

const querystring = require('querystring')
const handleBlogRouter = require('./src/router/blog.js')
const handleUserRouter = require('./src/router/user.js')

// 用于处理 postData
const getPostData = req => {
  return new Promise((resolve, reject) => {
    if (req.method !== 'POST') {
      resolve({})
      return
    }
    // 非json数据类型,忽略并返回{}
    if (req.headers['content-type' !== 'application/json']) {
      resolve({})
      return
    }
    // 正确的
    let postData = ''
    req.on('data', chunk => {
      postData += chunk.toString()
    })
    req.on('end', () => {
      if (!postData) {
        resolve({})
        return
      }
      // 成功返回
      resolve(JSON.parse(postData))
    })
  })
}

const serverHandle = (req, res) => {
  res.setHeader('Content-Type', 'application/json')

  // 获取path
  const url = req.url
  req.path = url.split('?')[0]

  // 解析 query
  req.query = querystring.parse(url.split('?')[1])

  // 处理 postData
  getPostData(req).then(postData => {
    req.body = postData
    // 处理 blog 路由 旧

    // const blogData = handleBlogRouter(req, res)
    // if (blogData) {
    //   res.end(JSON.stringify(blogData))
    //   return
    // }

    // 处理 blog 路由 新
    const blogResult = handleBlogRouter(req, res)
    if (blogResult) {
      blogResult.then(blogData => {
        res.end(JSON.stringify(blogData))
      })
      return
    }

    // 处理 user 路由
    // const userData = handleUserRouter(req, res)
    // if (userData) {
    //   res.end(JSON.stringify(userData))
    //   return
    // }
    const userResult = handleUserRouter(req,res)
    if(userResult) {
      userResult.then(userData => {
        res.end(JSON.stringify(userData))
      })
      return 
    }

    // 未命中路由, 返回404
    res.writeHead(404, { 'Content-Type': 'text/plain' })
    res.write('404 Not Found\n')
    res.end()
  })
}
module.exports = serverHandle