Koa中使用MySQL数据库实现增删改查
查看更多资源1. 简介：使用 koa 框架、vue.js 与 mysql 数据库，搭建一个简单的任务管理器demo，实现任务列表的展示、分页查询、增删改查。2. 安装：npm install koa koa-router koa-static-cache koa-bodyparser mysql2 --save3. 图示：4. app.js 代码：(as...
·
1. 简介：
使用 koa 框架、vue.js 与 mysql 数据库，搭建一个简单的任务管理器demo，实现任务列表的展示、分页查询、增删改查。
2. 安装：
npm install koa koa-router koa-static-cache koa-bodyparser mysql2 --save3. 图示：
4. app.js 代码：
(async function () {
const Koa = require('koa');
const Static = require('koa-static-cache');
const Router = require('koa-router');
const Bodyparser = require('koa-bodyparser');
const fs = require('fs');
const mysql = require('mysql2/promise'); // 1. 加载模块
const app = new Koa();
const router = new Router();
app.use(Bodyparser());
app.use(Static('./static', { // 配置静态资源
prefix: '/static',
gzip: true
}));
const connection = await mysql.createConnection({ // 2. 连接 mysql 数据库
host: '127.0.0.1',
user: 'root',
password: '123456',
database: 'test' // 数据库名
});
router.get('/', ctx => {
const content = fs.readFileSync('./static/index.html');
ctx.body = content.toString();
});
router.get('/todos', async ctx => {
/*
* (1) 查询及排序
*/
// 语法：SELECT 字段名 FROM 表名 ORDER BY 字段名 DESC
// ASC：升序，默认; DESC：降序
const [data] = await connection.query("SELECT id,title,done FROM todos ORDER BY done DESC, id DESC");
// 接受前端 分页请求参数
let page = ctx.query.page || 1;
page = Number(page);
let prepage = ctx.query.prepage || 4;
prepage = Number(prepage);
let type = ctx.query.type;
let where = '';
if (type) {
where = 'WHERE done=' + type;
}
// 查询总的记录条数
const sql = `SELECT id,title,done FROM todos ${where}`;
const [todosAll] = await connection.query(sql);
// 总的数据量 / 每页显示条数，注意：小数
const pages = Math.ceil(todosAll.length / prepage);
/*
* (2) 分页查询
*/
// 语法：LIMIT 5 OFFSET 3 查询5条，偏移3条
// 语法 where ??=? 其中 ?? 表示字段或表名，? 表示值
const sql2 = `SELECT id,title,done FROM todos ${where} LIMIT ? OFFSET ?`;
const [todos] = await connection.query(sql2, [prepage, (page - 1) * prepage]);
ctx.body = {
code: 0,
data: {
page,
prepage,
pages,
todos
}
}
});
router.post('/add', async ctx => {
const title = ctx.request.body.title || '';
if (title == '') {
ctx.body = {
code: 1,
data: 'title不能为空'
}
return;
}
/*
* (3) 添加数据
*/
const [rs] = await connection.query("INSERT INTO todos (title, done) VALUES ('" + title + "', 0)");
if (rs.affectedRows > 0) {
ctx.body = {
code: 0,
data: '添加成功'
}
} else {
ctx.body = {
code: 2,
data: '添加失败'
}
}
});
router.post('/toggle', async ctx => {
let id = Number(ctx.request.body.id) || 0;
let todo = Number(ctx.request.body.todo) || 0;
/*
* (4) 更新数据
*/
let sql = "UPDATE todos SET ??=? WHERE ??=?";
let [rs] = await connection.query(sql, ['done', todo, 'id', id]);
if (rs.affectedRows > 0) {
ctx.body = {
code: 0,
data: '修改成功'
}
} else {
ctx.body = {
code: 2,
data: '修改失败'
}
}
});
router.post('/remove', async ctx => {
let id = Number(ctx.request.body.id) || 0;
/*
* (5) 删除数据
*/
let sql = "DELETE FROM todos WHERE ??=?";
let [rs] = await connection.query(sql, ['id', id]);
if (rs.affectedRows > 0) {
ctx.body = {
code: 0,
data: '删除成功'
}
} else {
ctx.body = {
code: 2,
data: '删除失败'
}
}
});
app.use(router.routes());
app.listen(8081);
})();5. index.html 代码：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
<script src="https://cdn.jsdelivr.net/npm/vue@2.5.17/dist/vue.js"></script>
<style>
li {
line-height: 40px;
}
.done span {
text-decoration: line-through;
color: #cccccc;
}
.pages {
margin: 10px;
}
.pages a {
margin: 15px;
}
.pages a.active {
color: red;
}
.footer {
margin: 10px;
}
.footer button.active {
background: yellow;
}
</style>
</head>
<body>
<div id="app">
<h1>App</h1>
<div>
<input type="text" v-model="newValue" />
<button @click="add">添加新任务</button>
</div>
<hr />
<ul>
<li v-for="todo of todos" :key="todo.id" :class="{done: todo.done}">
<input type="checkbox" :checked="todo.done" @click.prevent="toggle(todo.id, !todo.done)" />
<span>#{{todo.id}} - {{todo.title}}</span>
<button @click="remove(todo.id)">删除</button>
</li>
</ul>
<div class="pages">
<a href="" :class="{active: n == page}" @click.prevent="changePage(n)" v-for="n of pages">{{n}}</a>
</div>
<div class="footer">
<button :class="{active: type===''}" @click="changeType('')">所有</button>
<button :class="{active: type===1}" @click="changeType(1)">已完成</button>
<button :class="{active: type===0}" @click="changeType(0)">未完成</button>
</div>
</div>
<script>
new Vue({
el: '#app',
data: {
newValue: '',
page: 1,
prepage: 4,
pages: 0,
type: '',
todos: []
},
created() {
this.getData();
},
methods: {
getData() {
fetch('/todos?type='+ this.type +'&page=' + this.page + '&prepage=' + this.prepage).then(res => {
return res.json();
}).then( data => {
this.todos = data.data.todos;
this.pages = data.data.pages;
} );
},
changePage(n) {
this.page = n;
this.getData();
},
changeType(m) {
this.type = m;
this.getData();
},
add() {
fetch('/add', {
method: 'post',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify( {title: this.newValue} )
}).then(res => {
return res.json();
}).then( data => {
if (!data.code) {
this.getData();
}
} )
this.newValue = '';
},
toggle(id, todo) {
fetch('/toggle', {
method: 'post',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify( {id, todo: Number(todo)} )
}).then(res => {
return res.json();
}).then(data => {
if (!data.code) {
this.getData();
}
});
},
remove(id) {
fetch('/remove', {
method: 'post',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify( {id} )
}).then(res => {
return res.json();
}).then(data => {
if (!data.code) {
this.getData();
}
});
}
}
})
</script>
</body>
</html>
前往低代码交流专区
更多推荐
4
0- 0
已为社区贡献9条内容
所有评论(0)