连接mysql数据库
const mysql = require('mysql');
const db = mysql.createPool({
host:'localhost',
user:'root',
'password':'123456',
database:'blog'
})
db.getConnection((err,conn) => {
if(err) console.log('connect failed')//数据库连接失败
conn.query('SELECT * FROM article_table',(err,data) => {//数据库连接成功
if(err) console.log(err)
res.send(data).end();
})
conn.release()//释放连接池
})
获取所有文章接口
//获取文章
router.post('/article',function(req,res,next){
db.getConnection(function(err,conn){//连接数据库
if(err) console.log('connect failed')//数据库连接失败
conn.query('SELECT * FROM article_table',(err,data) => {//数据库连接成功
if(err) console.log(err)
// console.log(data)
res.send(data).end();
})
conn.release()//释放连接池
})
})
获取文章详情页
//获取文章详情页
router.get('/article/detail',function(req,res,next){
db.getConnection(function(err,conn){//连接数据库
if(err) console.log('connect failed')//数据库连接失败
conn.query(`SELECT * FROM article_table WHERE id=${req.query.id}`,(err,data) => {//数据库连接成功
if(err) console.log(err)
// console.log(data)
res.send(data).end();
})
conn.release()//释放连接池
})
})
- router
{
path: '/index',
name: 'Home',
component: Home
},
{
path: '/articles/detail/:id',//添加路由参数
name: 'Article',
component: Article
},
- index.vue
<router-link
class="read-more"
:to="'/articles/detail/' + item.ID" //获取文章的ID并作为路由参数
:key="item.ID"
tag="li"
v-for="item in articleList">
</router-link>
created(){
Vue.axios.post('/api/article')
.then(res => {
this.articleList = res.data;
})
.catch(err => console.log(err))
},
- detail.vue
data() {
return {
articleDetail: []
}
},
created(){
const artId=this.$route.params.id;//获取路由参数
Vue.axios.get(`/api/article/detail?id=${artId}`)
//获取后台详情api并加上id
.then(res => {
this.articleDetail = res.data[0];
console.log(res.data[0])
})
.catch(err => console.log(err))
}
登录接口
//登陆/验证API 通用
router.post('/login', function(req, res){
var username = req.body.username;//获取表单提交的name属性值
var password = common.md5(req.body.password+common.MD5_SUFFIX);
var resBody = {state:''}//前台根据返回状态验证
db.query(`SELECT * FROM user_table WHERE username='${username}'`,(err, doc) => {
if(err){
res.status(500).send('err').end();
}else{
if(doc.length==0){
resBody.state = 'no admin';
res.send(resBody);
} else{
if(doc[0].password==password){
req.session['admin_id']=doc[0].ID;
resBody.state = 'success';
res.send(doc).end();
}else{
resBody.state = 'pwderr';
res.send(resBody).end();
}
}
}
})
})
- md5
const crypto=require('crypto');
module.exports={
//这东西不能丢
MD5_SUFFIX: 'ergveringnvginbiojncrurugbb*&^5$4$###bregbj哈哈哈哈',
md5: function(str){
var obj=crypto.createHash('md5');
obj.update(str);
return obj.digest('hex');
}
}
- 存在数据库的密码需要先用md5加密
- login.vue
<template>
<div class="login">
<form @submit.prevent="login">
<div class="grid-content login">
<p class="loginTitle">登陆 | Login</p>
<input type="text" v-model="username" placeholder="请输入你的用户名">
<input type="password" v-model="password" placeholder="请输入你的密码">
<button type="submit">登录</button>
<!-- <button type="submit" @click="login">登录</button> -->
</div>
</form>
</div>
</template>
<script>
import Vue from 'vue'
export default {
data() {
return {
username: '',
password: ''
}
},
methods: {
login() {
Vue.axios.post('/api/login',{//把数据传过来
username: this.username,
password: this.password
})
.then((res) => {
const data = res.data;
if(data.state === "pwderr"){
alert('密码错误')
return false
}else if(data.state==='no admin'){
alert('账号不存在')
return false
}else{
alert('success')
let userid = data[0].id;
this.$store.dispatch('setUsername', {name: this.username,id: userid});//添加到vuex中
this.$router.push({path: '/admin/write-blog'})//提交到的路径
}
})
.catch(err => console.log(err))
}
}
}
</script>
删除数据库
//删除数据库数据
router.post('/del',(req,res) => {
var id = req.body.id
db.query(`DELETE FROM article_table WHERE ID='${id}'`,(err,data) => {
if(err){
console.log(err);
res.status(500).send('database err').end();
}else{
res.send('删除成功')
}
})
})
- 数据表格vue
<table class="tab" width="100%" cellpadding="0" cellspacing="0">
<thead>
<tr>
<th width="2%">ID </th>
<th width="15%">标题</th>
<th width="48%">描述</th>
<th width="15%">发表时间</th>
<th width="20%">操作</th>
</tr>
</thead>
<tbody v-for="(item,index) in articleList">
<tr>
<td>{{index+1}}</td>
<td>{{item.title}}</td>
<td>{{item.summary}}</td>
<td>{{item.post_time}}</td>
<td>
<el-button @click="visible = true,mod(item.ID,index)">修改</el-button>
<el-button @click="del(item.ID,index)">删除</el-button>
<!-- <a href="javascript:;" @click="mod(item.ID,index)">修改</a> -->
<!-- <a href="javascript:;" @click="del(item.ID,index)">删除</a> -->
</td>
</tr>
</tbody>
</table>
methods: {
del(db,index) {
if(confirm("确定要清空数据吗?")){
this.articleList.splice(index,1);//删除视图层,并自动刷新页面,数据库中并没有删除
Vue.axios.post('/api/del', { id:db })
.then(data => {
// console.log(db)//当前行id
})
}
},
}
写文章接口
/* 写文章*/
router.post('/edit',(req,res)=>{
var title = req.body.title;
var summary = req.body.summary;
var context = req.body.context;
var post_time = new Date().getTime();//获取当前时间
//处理时间戳
moment.locale('zh-cn');
var today = {};
var _today = moment();
today.year = _today.format('yyyy'); /*现在的年*/
today.date = _today.format('YYYY-MM-DD'); /*现在的时间*/
today.yesterday = _today.subtract(1, 'days').format('YYYY-MM-DD'); /*前一天的时间*/
var postFormatDate = moment(post_time).format('YYYY-MM-DD HH:mm:ss'); /*格式化时间*/
db.query(`INSERT INTO article_table(author,author_src,title,context,post_time,n_like,summary) VALUES("mottoko@163.com","","${title}","${context}","${postFormatDate}","0","${summary}")`,(err,data)=>{
if(err){
console.log(err);
res.status(500).send('数据库错误').end();
}else{
res.json(data.insertId).end();//插入的id
}
})
})
-
获取新插入参数的id用
data.insertId
-
返回给前台要用
res.json
-
前台
<el-tab-pane label="写博客">
<div class="write-blog">
<form @submit.prevent="addblog">
<label for="title">标题:</label><input type="text" id="title" v-model="title" class="int title"><br>
<label for="summary">描述:</label><input type="text" v-model="summary" id="summary" class="int summary"><br>
<mavon-editor v-model="context" :toolbars="toolbars" @keydown="" />
<button type="submit" @click="insertBlog" class="addbtn">添加</button>
</form>
</div>
</el-tab-pane>
methods: {
addblog(){//添加文章在数据库中
addblog() {
Vue.axios.post('/api/edit',{
author: this.author,
title: this.title,
summary: this.summary,
context: this.context,
post_time: this.post_time
})
.then(res => {
alert('发表成功')
//更新视图已经找到方法,不用重新刷新页面了
// location.reload()//实在搞不定把新增数据添加到表格中了,以后有时间再整吧
// this.blogId = res.data //获取新增ID
this.$router.push('/admin/write-blog')
})
.catch(err => console.log(err))
},
},
insertBlog(){//更新视图
var obj={};
var myDate = new Date();
var y = myDate.getFullYear();
var m = myDate.getMonth() + 1;
var d = myDate.getDate();
var h = myDate.getHours();
var min = myDate.getMinutes();
var s = myDate.getSeconds();
if (m >= 1 && m <= 9) {
m = "0" + m;
}
if (d >= 0 && d <= 9) {
d = "0" + d;
}
this.post_time = y + '-' + m + '-' + d + ' ' + h + ':' + min + ':' + s;
obj.author=this.author;
obj.title=this.title;
obj.summary=this.summary;
obj.context=this.context;
obj.post_time=this.post_time;
this.articleList.push(obj);
}
}
错误
-
Expected Array, got Number
想要的是Array,你却给我Number -
mysql [Err] 1064 - You have an error in your SQL syntax;
sql语句出现空格或者不正确的符号 - 注意防止sql注入,后台可以加上
var id = db.escape(req.body.id)
修改页面数据接口
修改数据首要要获取点击的数据,然后在此基础上修改,这里后台我做了两个API,一个是获取当前选中的一行数据接口,一个是修改接口
- 后台:
//当前选中的一行数据的详细
router.post('/mod_data',(req,res) => {
db.query(`SELECT * FROM article_table WHERE id=${req.body.id}`,(err,data)=>{
if(err){
console.log(err);
res.status(500).send('database err').end();
}else if(data.length==0){
res.status(404).send('data not found').end();
}else{
db.query(`SELECT * FROM article_table`,(err,blogdetail)=>{
if(err){
console.log(err);
res.status(500).send('database err').end();
}else{
res.send(data[0])
}
});
}
})
})
//修改页面数据
router.post('/mod',(req,res) => {
var mod_title = req.body.mod_title;
var mod_summary = req.body.mod_summary;
var mod_context = req.body.mod_context
db.query(`UPDATE article_table SET title='${mod_title}',context='${mod_context}',summary='${mod_summary}' WHERE ID='${req.body.id}'`,(err,data)=>{
if(err){
console.log(err);
res.status(500).send('database err').end();
}else{
res.send('修改成功');
}
})
})
- 前台
当点击修改按钮后,弹出下面的dialog,点修改按钮,传入此文章后台id,然后将此id传到后台,后台用,req.body.id接受,就可获取此文章详细信息,然后将此id传出去,修改数据的接口接收,进行修改操作。
v-model是要绑定修改的数据的,但默认是提前出来的数据,所以就让
v-model="mod_summary=mod_data.summary"
<el-button @click="visible = true,mod(item.ID,index)">修改</el-button>
//修改dialog
<div class="mod_dialog" v-show="isshow">
<form @submit.prevent="mod_add">
<el-dialog :visible.sync="visible" title="修改">
<div class="update">
标题:<input type="text" v-model="mod_title=mod_data.title" class="int title"><br>
描述:<textarea v-model="mod_summary=mod_data.summary" cols="30" rows="5" class="int summary"><!-- {{mod_data.summary}} --></textarea><br>
<label class="lab-content">内容:</label><textarea v-model="mod_context = mod_data.context" cols="30" rows="10" class="textarea"><!-- {{mod_data.context}} --></textarea><br>
<input type="submit" value="添加" class="addbtn">
</div>
</el-dialog>
</form>
</div>
methods:{
mod(db,index) {
Vue.axios.post('/api/mod_data', { id:db })
.then(res => {
this.isshow=true;
this.mod_data = res.data
this.currentId = db
console.log(db)
})
},
mod_add() {
Vue.axios.post('/api/mod', {
id:this.currentId,
mod_title: this.mod_title,
mod_summary: this.mod_summary,
mod_context: this.mod_context
})
.then(res => {
alert('修改成功')
this.visible = false
console.log(res.data)
})
}
}
网友评论