3 连接mysql 数据库 进行数据的存储和读取

1下载node 连接模块npm i mysql -s2 在 src 下创建 conf 文件夹 用于存放配置文件 新建db.js通过环境参数的不同进行线上和线下配置const env process.env.NODE_ENV//环境参数 let MYSQL_CONF if (env dev) { MYSQL_CONF { host: localhost, user: root, password: , port: 3306, database: myblog }; } if (env production) { MYSQL_CONF { host: localhost, user: root, password: , port: 3306, database: myblog }; } module.exports { MYSQL_CONF }3创建 db 文件夹 用于存放数据操作文件 msyql.exe//引入msyql模块 const mysql require(mysql); //引入数据库配置 const { MYSQL_CONF } require(../config/db); //创建数据库连接 const con mysql.createConnection(MYSQL_CONF); //连接数据库 con.connect(); // 执行sql代码 function exec (sql) { const promise new Promise((resolve, reject) { // con.query(sql, (err, result) { if (err) { reject(err); return } resolve(result) }) }); return promise; } module.exports { exec };4创建数据库 文件创建 myblog 数据库 创建 两张表CREATE TABLE blogs ( id int(11) NOT NULL AUTO_INCREMENT, title varchar(50) NOT NULL, content longtext NOT NULL, createtime bigint(20) NOT NULL DEFAULT 0, author varchar(20) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT14 DEFAULT CHARSETutf8;CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, username varchar(20) NOT NULL, password varchar(20) NOT NULL, realname varchar(10) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8;修改controller 下的文件blog.jsconst { exec } require(../db/mysql) const getList (author, keyword) { //11 保证语法不报错 let sql select * from blogs where 11 ; if (author) { sql and author${author} } if (keyword) { sql and title like %${keyword}%; } sql order by createtime desc;; 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 is , 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); 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 }user.jsconst { exec } require(../db/mysql) 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 }修改app.jsconst querystring require(querystring) const handleBlogRouter require(./src/router/blog) const handleUserRouter require(./src/router/user) //用于处理 post data const getPostData (req) { const promise new Promise((resolve, reject) { if (req.method ! POST) { resolve({}) return } 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) ) }) }) return promise } 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]) //处理 post Data getPostData(req).then(postData { req.body postData console.log(postData); //处理blog路由 const blogResult handleBlogRouter(req, res) if (blogResult) { blogResult.then(blogData { res.end(JSON.stringify(blogData)) }) return } //处理user路由 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修改路由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 result getList(author, keyword) return result.then(listData { return new SuccessModel(listData) }) } //获取博客详情 if (method GET req.path /api/blog/detail) { const result getDetail(id) return result.then(data { return new SuccessModel(data) }) } //新建一篇博客 if (method POST req.path /api/blog/new) { req.body.author zhangsan 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(更新成功) } else { return new ErrorModel(更新失败) } }) } //删除一篇博客 if (method POST req.path /api/blog/del) { const author zhangsan const result delBlog(id, author); return result.then(val { if (val) { return new SuccessModel(删除成功) } else { return new ErrorModel(删除失败) } }) } } module.exports handleBlogRouterconst { 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