【koa2】对接 mysql 实现增删改查

1. 效果图

2. 对接 mysql 实现增删改查

1. 连接 mysql 数据库

   // db/mysql.js
   
   const mysql = require('mysql');
   
   const MYSQL_CONF = {
     host: 'localhost',
     user: 'xxxx',
     password: '111111',
     database: 'cuthair',
   };
   
   const con = mysql.createConnection(MYSQL_CONF);
   
   con.connect();
   
   function exec(sql) {
     const promise = new Promise((resolve, reject) => {
       con.query(sql, (err, res) => {
         if (err) {
           reject(err);
           return;
         }
         resolve(res);
       });
     });
     return promise;
   }
   
   module.exports = exec;
   
   

2. 操作 mysql 数据库

   // controller/role.js
   
   const exec = require('../db/mysql');
   
   const list = async (role, limit, page) => {
     let sql = `select * from roles where 1=1`;
     if (role) {
       sql += ` and role='${role}'`;
     }
     sql += ` order by id desc`;
     // 分页查询
     if (limit && page){
     	sql += ` limit ${(page - 1) * 10}, ${limit}`;
     }
   
     const result = exec(sql);
     return result;
   };
   
   const add = async (role, permissions) => {
     const sql = `insert into roles (role, permissions) values ('${role}', '${permissions}')`;
     const result = exec(sql);
     return result;
   };
   
   const upt = async (id, role, permissions) => {
     const sql = `update roles set role='${role}', permissions='${permissions}' where id='${id}'`;
     const result = exec(sql);
     return result;
   };
   
   const del = async (id) => {
     const sql = `delete from roles where id='${id}'`;
     const result = exec(sql);
     return result;
   };
   
   module.exports = {
     list,
     add,
     upt,
     del,
   };
   
   

3. 实现路由

   // routes/role.js
   
   const router = require('koa-router')();
   const { list, add, upt, del } = require('../controller/roles');
   
   router.prefix('/api/roles');
   
   router.get('/list', async (ctx, next) => {
     const role = ctx.query.role;
     const limit = ctx.query.limit;
   const page = ctx.query.page;
   
     if (!ctx.session.username) return;
   
     let data = await list(role, limit, page);
     const data2 = await list(); // 获取总数量 total
     if (data.length == 0) {
       ctx.body = {
         code: 400,
         msg: '查询失败',
       };
       return;
     }
     data.forEach((ele) => {
       ele.permissions = JSON.parse(ele.permissions);
     });
     ctx.body = {
       data,
       total: data2.length,
     };
   });
   
   router.post('/add', async (ctx, next) => {
     const { role, permissions } = ctx.request.body;
   
     if (!ctx.session.username) return;
   
     const data = await add(role, permissions);
   
     if (data.affectedRows > 0) {
       ctx.body = {
         code: 200,
         msg: '添加成功',
       };
       return;
     }
   });
   
   router.put('/upt', async (ctx, next) => {
     const { id, role, permissions } = ctx.request.body;
   
     if (!ctx.session.username) return;
   
     const data = await upt(id, role, permissions);
   
     if (data.affectedRows > 0) {
       ctx.body = {
         code: 200,
         msg: '更新成功',
       };
       return;
     }
   });
   
   router.delete('/del', async (ctx, next) => {
     const id = ctx.query.id;
   
     if (!ctx.session.username) return;
   
     const data = await del(id);
   
     if (data.affectedRows > 0) {
       ctx.body = {
         code: 200,
         msg: '删除成功',
       };
       return;
     }
   });
   
   module.exports = router;
   
   

4. 注册路由

   // app.js
   
   const roles = require("./routes/roles");
   app.use(roles.routes(), roles.allowedMethods());