express+mysql实现简单的增删改查
express+mysql实现简单的增删改查
1、首先要确保使用node.js全局安装过express
若未安装
npm install –g express
npm install –g express-generator
确保有一个数据库
2、建立express项目
express mall-project(项目名为mall-project)
修改package.json
在"dependencies"添加"mysql": "latest"依赖
npm i
npm start
3、修改目录如图
4、修改代码
//mysql.js// MySQL数据库联接配置封裝var mysql = { host: '127.0.0.1', user: 'root', password: 'tll456456', database: 'test', port: 3306}; module.exports = mysql;
//handel.js/* 数据增删改查模块封装 req.query 解析GET请求中的参数 包含在路由中每个查询字符串参数属性的对象,如果没有则为{} req.params 包含映射到指定的路线“参数”属性的对象,如果有route/user/:name,那么“name”属性可作为req.params.name req.body通常用来解析POST请求中的数据 +req.query.id 可以将id转为整数 */// 引入mysqlvar mysql = require('mysql');// 引入mysql连接配置var mysqlconfig = require('../config/mysql');// 引入连接池配置var poolextend = require('./poolextend');// 引入SQL模块var sql = require('./sql');// 引入json模块var json = require('./json');// 使用连接池,提升性能var pool = mysql.createPool(poolextend({}, mysqlconfig));var userData = { add: function(req, res, next) { pool.getConnection(function(err, connection) { var param = req.query || req.params; connection.query(sql.insert, [param.id, param.name, param.age], function(err, result) { if (result) { result = 'add' } // 以json形式,把操作结果返回给前台页面 json(res, result); // 释放连接 connection.release(); }); }); }, delete: function(req, res, next) { pool.getConnection(function(err, connection) { var id = +req.query.id; connection.query(sql.delete, id, function(err, result) { if (result.affectedRows > 0) { result = 'delete'; } else { result = undefined; } json(res, result); connection.release(); }); }); }, update: function(req, res, next) { var param = req.body; if (param.name == null || param.age == null || param.id == null) { json(res, undefined); return; } pool.getConnection(function(err, connection) { connection.query(sql.update, [param.name, param.age, +param.id], function(err, result) { if (result.affectedRows > 0) { result = 'update' } else { result = undefined; } json(res, result); connection.release(); }); }); }, queryById: function(req, res, next) { var id = +req.query.id; pool.getConnection(function(err, connection) { connection.query(sql.queryById, id, function(err, result) { if (result != '') { var _result = result; result = { result: 'select', data: _result } } else { result = undefined; } json(res, result); connection.release(); }); }); }, queryAll: function(req, res, next) { pool.getConnection(function(err, connection) { connection.query(sql.queryAll, function(err, result) { if (result != '') { var _result = result; result = { result: 'selectall', data: _result } } else { result = undefined; } json(res, result); connection.release(); }); }); } }; module.exports = userData;
//json.js//封装接送模块var json = function(res, result) { if (typeof result === 'undefined') { res.json({ code: '1', msg: '操作失败' }); } else if (result === 'add') { res.json({ code: '200', msg: '添加成功' }); } else if (result === 'delete') { res.json({ code: '200', msg: '删除成功' }); } else if (result === 'update') { res.json({ code: '200', msg: '更改成功' }); } else if (result.result != 'undefined' && result.result === 'select') { res.json({ code: '200', msg: '查找成功', data: result.data }); } else if (result.result != 'undefined' && result.result === 'selectall') { res.json({ code: '200', msg: '全部查找成功', data: result.data }); } else { res.json(result); } }; module.exports = json;
//poolextent.js// 连接池扩展封装var poolextend = function(target, source, flag) { for (var key in source) { if (source.hasOwnProperty(key)) { flag ? (target[key] = source[key]) : (target[key] === void 0 && (target[key] = source[key])); } } return target; } module.exports = poolextend;
//sql.js// SQL语句封裝var user = { insert:'INSERT INTO user(id, name, age) VALUES(?,?,?)', update:'UPDATE user SET name=?, age=? WHERE id=?', delete: 'DELETE FROM user WHERE id=?', queryById: 'SELECT * FROM user WHERE id=?', queryAll: 'SELECT * FROM user'}; module.exports = user;
//router/index.jsvar express = require('express');var router = express.Router();var user = require('../modules/handle');/* GET users listing. */router.get('/', function(req, res, next) { res.render('index', { title: 'SQL for MySQL' }); }); router.get('/addUser', function(req, res, next) { user.add(req, res, next); }); router.get('/queryAll', function(req, res, next) { user.queryAll(req, res, next); }); router.get('/query', function(req, res, next) { user.queryById(req, res, next); }); router.get('/deleteUser', function(req, res, next) { user.delete(req, res, next); }); router.get('/update', function(req, res, next) { res.render('update'); }); router.post('/updateUser', function(req, res, next) { user.update(req, res, next); }); module.exports = router;
//index.jade extends layout block content h1= title p a(class={active: currentUrl === '/'} href='/queryAll') 数据查询 p a(class={active: currentUrl === '/'} href='/query?id=1') 定向查询 p a(class={active: currentUrl === '/'} href='/addUser?id=0&name=xyz&age=18') 数据插入 p a(class={active: currentUrl === '/'} href='/update') 数据更新 p a(class={active: currentUrl === '/'} href='/deleteUser?id=0') 数据删除 p
//layout.jade doctype html html head title= title link(rel='stylesheet', href='/stylesheets/style.css') body block content
//update.jade extends layout block content h1 更新用户资料 form(method='post', action='updateUser') div.form-row label span ID: input(type='text',name='id') div.form-row label span name: input(type='text',name='name') div.form-row label span age: input(type='text',name='age') div.form-row input(type='submit')
5、重启
npm start