问题描述:
现在有一个vue2+element UI的前端项目,用sqlite简单了搭建一个数据库,但是怎么将他们用js连接呢?
首先npm install sqlite~上网上搜下一大堆。
- 发现了一个开源的项目,使用了Vue.js + Element UI + Express + axios + SQLite3.比较一下它和我的项目的异同:
https://github.com/shawvey/The-Weather-Helper如果github登不上可以git clone https://gitee.com/liu-qiuqi/The-Weather-Helper.git
(o゜▽゜)o☆这里这是gitee里的。
我的项目目录:
Weather-Helper的App.vue和main.js对应我的项目中pages的每个文件夹里的vue和js
main.js:
import Vue from 'vue';
import App from './App';
import router from './router';//忽略
import ElementUI from 'element-ui';
import 'element-ui/lib/theme-default/index.css'; // 默认主题
import SIdentify from './components/page/Identify'; //自定义组件
import "babel-polyfill";
import axios from 'axios';
Vue.use(axios);
Vue.component("SIdentify",SIdentify);
Vue.use(ElementUI);
Vue.use(axios);
new Vue({
router,
render: h => h(App)
}).$mount('#app');
App.vue
<template>
<div id="app">
<router-view></router-view>
</div>
</template>
<style>
@import "../static/css/main.css";
@import "../static/css/color-dark.css";
</style>
/components/page/Login.vue
<template>
<div class="login-wrap">
<div class="ms-title">Login</div>
<div class="ms-login">
<el-form :model="ruleForm" :rules="rules" ref="ruleForm" label-width="0px" class="demo-ruleForm">
<div v-if="errorInfo">
<span>{{errInfo}}</span>
</div>
<el-form-item prop="name">
<el-input v-model="ruleForm.name" placeholder="Username" ></el-input>
</el-form-item>
<el-form-item prop="password">
<el-input type="password" placeholder="Password" v-model="ruleForm.password" @keyup.enter.native="submitForm('ruleForm')"></el-input>
</el-form-item>
<el-form-item prop="validate">
<el-input v-model="ruleForm.validate" class="validate-code" placeholder="" ></el-input>
<div class="code" @click="refreshCode">
<s-identify :identifyCode="identifyCode"></s-identify>
</div>
</el-form-item>
<div class="login-btn">
<el-button type="primary" @click="submitForm('ruleForm')">Submit</el-button>
</div>
<p class="register" @click="handleCommand()">Create an Account</p>
</el-form>
</div>
</div>
</template>
<script>
import axios from 'axios';
export default {
name: 'login',
data() {
return {
identifyCodes: "1234567890",
identifyCode: "",
errorInfo : false,
ruleForm: {
name: '',
password: '',
validate: ''
},
rules: {
name: [
{ required: true, message: 'please enter username', trigger: 'blur' }
],
password: [
{ required: true, message: 'please enter password', trigger: 'blur' }
],
validate: [
{ required: true, message: 'please enter verification code', trigger: 'blur' }
]
}
}
},
mounted() {
this.identifyCode = "";
this.makeCode(this.identifyCodes, 4);
},
methods: {
submitForm(formName) {
// debounceAjax(formName)
const self = this;
self.$refs[formName].validate((valid) => {
if (self.ruleForm.validate != this.identifyCode){
alert('Please enter true verification code!')
}
else if (self.ruleForm.validate== this.identifyCode) {
//发送请求到后端/api/user/login
axios.post('/api/user/login',JSON.stringify(self.ruleForm))
.then((response) => {
console.log(response);
if (response.data == -1) {
self.errorInfo = true;
self.errInfo = 'no such user';
console.log('Invalid username or password.')
} else if (response.data == 0) {
console.log('Invalid username or password.')
self.errorInfo = true;
self.errInfo = 'Invalid username or password.';
} else if (response.status == 200) {
sessionStorage.setItem('ms_userId',self.ruleForm.name);
sessionStorage.setItem('ms_username',self.ruleForm.name);
sessionStorage.setItem('ms_user',JSON.stringify(self.ruleForm));
self.$router.push('/readme');
console.log(JSON.stringify(self.ruleForm));
}
}).then((error) => {
console.log(error);
})
} else {
console.log('error submit!!');
return false;
}
});
},
handleCommand() {
this.$router.push('/register');
},
randomNum(min, max) {
return Math.floor(Math.random() * (max - min) + min);
},
refreshCode() {
this.identifyCode = "";
this.makeCode(this.identifyCodes, 4);
},
makeCode(o, l) {
for (let i = 0; i < l; i++) {
this.identifyCode += this.identifyCodes[
this.randomNum(0, this.identifyCodes.length)
];
}
}
}
}
</script>
service文件夹里
app.js
//引用api文件夹里的userApi.js
const userApi = require('./api/userApi');//重要
const fs = require('fs');
const path = require('path');
const bodyParser = require('body-parser');
const express = require('express');
const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded())
app.use('/api/user', userApi);//重要
//'/api/user'前端发送路径,如前面的'/api/user/login' login是在./api/userApi.js里的一个方法如下
app.listen(3000);
console.log('success listen at port: 3000')
userApi.js
var express = require('express');
var router = express.Router();
var $sql = require('../db/sqlMap');
const sqlite3 = require('sqlite3').verbose();
var jsonWrite = function(res, ret) {
if(typeof ret === 'undefined') {
res.send('err');
} else {
console.log(ret);
res.send(ret);
}
}
var dateStr = function(str) {
return new Date(str.slice(0,7));
}
//search user
router.post('/login', (req, res) => {
let db = new sqlite3.Database('./db/DB.db', (err) => {
if (err) {
console.error(err.message);
}
console.log('Connected to the database.');
});
var sql_name = $sql.user.select_name;
var params = req.body;
var keywords = JSON.parse(Object.keys(params));
console.log(keywords);
if (keywords.name) {
sql_name += "where username ='"+ keywords.name +"'";
}
db.get(sql_name, function(err, result) {
if (err) {
console.log(err);
}
if (result === undefined) {
res.send('-1') //cannot search username,return -1
} else {
if(result.password === keywords.password) {
jsonWrite(res, result);
} else {
res.send('0') //username
}
}
})
db.close();
});
module.exports = router;
因为Weather的配置是由config、build、package.json共同设置的,但是我的这个项目里只有vue.config.js和package.json,所以比较后我将vue.config.js改成了这样:
let pageMethod = require('./src/util/getPages.js');
let pages = {}
pages = pageMethod.pages();
module.exports = {
pluginOptions: {
webpack: {
dir: [
'./webpack'
]
}
},
lintOnSave: false, //禁用eslint
productionSourceMap: false,
// eslint-disable-next-line no-undef
pages,
devServer: {
index: 'Login.html', //默认启动serve 打开page1页面
open: process.platform === 'darwin',
host: '',
port: 8080,
https: false,
hotOnly: false,
//增加部分
proxyTable: {
'/api':{
// target:'http://jsonplaceholder.typicode.com',
target: 'http://127.0.0.1:3000/api/',
changeOrigin:true,
pathRewrite:{
'^/api':''
}
}
},
}
}
根据上面一通照葫芦画瓢后——发现没连上😓
应该是配置文件出现了问题,“前端向什么地址发送请求”怎么配置呢?
我查到有一篇博客讲解了vue.config.js(我的)和webpack(Weather-Helper所用的那种方式的配置文件)的区别:这里👀
发现没有错,就是用proxy,上面是没有改过来把proxy写成了proxyTable,可恶🤢,正确的在下面:
let pageMethod = require('./src/util/getPages.js');
let pages = {}
pages = pageMethod.pages();
module.exports = {
pluginOptions: {
webpack: {
dir: [
'./webpack'
]
}
},
lintOnSave: false, //禁用eslint
productionSourceMap: false,
// eslint-disable-next-line no-undef
pages,
devServer: {
index: 'Login.html', //默认启动serve 打开page1页面
open: process.platform === 'darwin',
host: '',
port: 8080,
https: false,
hotOnly: false,
//增加部分
proxy: {
'/api':{
// target:'http://jsonplaceholder.typicode.com',
target: 'http://127.0.0.1:3000/api/',
changeOrigin:true,
pathRewrite:{
'^/api':''
}
}
},
}
}
又出现了问题~心累:启动项目后报错status:500发现传到后端的数据长这样:(如下req.body)terminal里显示SyntaxError: Unexpected end of JSON input at JSON.parse ()
req.body:
{ ‘{“account”:“001”,“password”:“123456”,“checked”:’: [ ‘’ ] }
解决方案:
简单实现了用户登录总的来说后端是这样配置的:
- 步骤一:将Weather-Helper里的service文件夹复制到项目根目录(src所在目录),用数据库管理工具(可以从这里下载SqliteAdministrator,链接:https://pan.baidu.com/s/1GwrPq9RBt4Rre2SzV-wlUg 提取码:7wll)新建一个数据库User表。将后缀为s3db的数据库文件保存在/service/db里。
- 步骤二:修改/service/app.js
const userApi = require('./api/userApi');//改成自己的API相对路径
const fs = require('fs');
const path = require('path');
const bodyParser = require('body-parser');
const express = require('express');
const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded())
app.use('/api/user', userApi);//改成相应的‘前端发送请求地址’,对应变量名
app.listen(3000);
console.log('success listen at port: 3000')
- 步骤三:修改service/db/sqlMap,改成你需要的sqlite语句。👀这里是sqlite语句教程
var sqlMap = {
user: {
add: 'INSERT INTO User [(account, password, portrait,realname,phone)] VALUES',
select_account: 'SELECT * FROM User',
update_user: 'UPDATE User SET'
}
}
module.exports = sqlMap;
- 步骤四:创建/修改/service/api/xxApi.js
我将userApi改成了这样
var express = require('express');
var router = express.Router();
var $sql = require('../db/sqlMap');
const sqlite3 = require('sqlite3').verbose();
var jsonWrite = function(res, ret) {
if(typeof ret === 'undefined') {
res.send('err');
} else {
console.log(ret);
res.send(ret);
}
}
var dateStr = function(str) {
return new Date(str.slice(0,7));
}
//search user
//尝试了下用户登录
router.post('/login', (req, res) => {
let db = new sqlite3.Database('./db/IIs.s3db', (err) => {//单引号里写自己的数据库文件名
if (err) {
console.error("!!!!")
console.error(err.message);
}
console.log('Connected to the database.');
});
var sql_account = $sql.user.select_account;
var params = req.body;
var keywords = JSON.parse(Object.keys(params));
console.log(keywords);
if (keywords.account) {
sql_account += " where account ='"+ keywords.account +"'";
}
db.get(sql_account, function(err, result) {
if (err) {
console.log(err);
}
if (result === undefined) {
res.send('-1') //cannot search username,return -1
} else {
if(result.password === keywords.password) {
jsonWrite(res, result);
} else {
res.send('0') //username
}
}
})
db.close();
});
module.exports = router;
- 步骤五:修改根目录下的vue.config.js文件,没有的话创建一个(👀参考这里):
let pageMethod = require('./src/util/getPages.js');
let pages = {}
pages = pageMethod.pages();
module.exports = {
pluginOptions: {
webpack: {
dir: [
'./webpack'
]
}
},
lintOnSave: false, //禁用eslint
productionSourceMap: false,
// eslint-disable-next-line no-undef
pages,
devServer: {
index: 'Login.html', //默认启动serve 打开page1页面
open: process.platform === 'darwin',
host: '',
port: 8080,
https: false,
hotOnly: false,
//在devServer里加上下面的这个proxy
proxy: {
'/api':{
// target:'http://jsonplaceholder.typicode.com',
target: 'http://localhost:3000/api/',
changeOrigin:true,
pathRewrite:{
'^/api':''
}
}
},
}
}
如果项目目录和Weather-Helper相似那么配置文件直接参考它的就行啦!
- 在/src/pages/Login/Login.vue里:
<template>
<div id="login">
<div class="login-box">
<div id="normalLoginTab" class="loginForm" style="display: block">
<h2 class="loginbox-title">帐号登录</h2>
<div class="loginUrs" style="height: 302px">
<div class="g-bd">
<!-- 登入表单 -->
<el-form
ref="Loginform"
:model="user"
style="width: 70%; margin: auto"
>
<el-form-item>
<el-input v-model="user.account" placeholder="请输入账号" style="height: 40px; line-height: 40px;" />
</el-form-item>
<el-form-item>
<el-input
v-model="user.password"
placeholder="请输入密码"
show-password
/>
</el-form-item>
<el-form-item style="margin-top: -20px">
<el-checkbox v-model="checked" type="check"
>记住密码</el-checkbox
>
<router-link
to="/"
style="
margin-left: 175px;
text-decoration: none;
color: #42a5eb;
"
>忘记密码?</router-link
>
</el-form-item>
<el-form-item style="margin-top: -30px">
<el-button type="goon" @click="doLogin()"
>登 入</el-button
>
</el-form-item>
</el-form>
</div>
</div>
</div>
</div>
</div>
</template>
<script>
import qs from "qs";
import axios from "axios";
export default {
data() {
return {
user: {
account: "",
password: "",
portrait:"",
realname:"",
phone:""
},
checked: [],
errorInfo : false,
errInfo:"",
rules: {
account: [
{ required: true, message: 'please enter username', trigger: 'blur' }
],
password: [
{ required: true, message: 'please enter password', trigger: 'blur' }
]
}
};
},
methods: {
doLogin() {
// debounceAjax(formName)
const self = this;
if (!self.user.account){
alert('Please enter an account!')
}
else if(!self.user.password){
alert('Please enter a password!')
}
else if(self.user.password&&self.user.account){
var dataa={account:this.user.account,password:this.user.password}
axios.post('/api/user/login',JSON.stringify(dataa))
.then((response) => {
if (response.data == -1) {
self.errorInfo = true;
self.errInfo = 'no such user';
console.log('Invalid username or password.')
} else if (response.data == 0) {
console.log('Invalid username or password.')
self.errorInfo = true;
self.errInfo = 'Invalid username or password.';
} else if (response.status == 200) {
var datab=response.data
sessionStorage.setItem('ms_account',self.user.account);
sessionStorage.setItem('ms_password',self.user.password);
sessionStorage.setItem('ms_user',JSON.stringify(datab));
//self.$router.push('/index');//页面跳转
//alert(sessionStorage.getItem("ms_user"));
}
}).then((error) => {
console.log(error);
})
} else {
console.log('error submit!!');
return false;
}
}
},
};
</script>
<style scoped src="../../style/Login.css">
</style>
登陆成功后将用户信息存入sessionStorage,👀有关sessionStorage