vue2+element UI+axios+sqlite连接本地数据库

问题描述：

现在有一个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()"
                  >登&nbsp;&nbsp;入</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