一、应用场景:最近个人接到一个项目,其中要求APP在非联网状态下使用,而且每个用户都可以自由操作数据,由于这种特殊性,考虑到用户数量较少且数据量较大,所以最终选择使用websql数据库来实现大量数据的存储。

**WebSQL是前端的一个独立模块,是web存储方式的一种(见下图,F12打开控制台可以看到)。

vue.js连接数据库 前端vue连接数据库_vue.js连接数据库

二、VUE中引入并使用

1、环境搭建,初始化项目(此条不做赘述,相关操作可自行搜索)

2、在src目录下新建WebSql文件夹,并创建WebSql.js,用于引入数据库相关操作

①在main.js中引入websql模块

vue.js连接数据库 前端vue连接数据库_数据库_02

 ②在WebSql.js中定义数据库

vue.js连接数据库 前端vue连接数据库_vue.js连接数据库_03

 

代码如下:

import Vue from 'vue'

const WebSql=function(){
    if(window.openDatabase){
        //openDatabase('数据库名称','版本','数据库描述','数据库大小')
        var db = openDatabase('TC','1.0','taicang',200*1024*1024);
       
        Vue.prototype.$db=db;
        
        if(!db){
            console.log("数据库创建失败!");
        }else{
            console.log('本地数据库创建成功!');
        }
    }else{
        console.log('不支持本地存储!');
    }
};
export default WebSql;

③创建表结构(此处做一个简单的demo,以三级联动为例),代码和效果如下

vue.js连接数据库 前端vue连接数据库_数据_04

//关联本地数据库
          this.$db.transaction(function(tx){
          //创建城镇、村落、组别三张表结构
            tx.executeSql('CREATE TABLE IF NOT EXISTS City (CityID unique  primary key , Name)');
            tx.executeSql('CREATE TABLE IF NOT EXISTS County (CountyID unique  primary key ,CityID, Name)');
            tx.executeSql('CREATE TABLE IF NOT EXISTS GroupList (GroupID unique primary key ,CountryID, Name,IsChoose)');
            
            //如果表中数据为空,则插入数据
            tx.executeSql('SELECT COUNT(*) AS NUM FROM City',[],function(tx,mes){
              if(mes.rows[0].NUM==0){
                console.log("插入城镇数据");
                tx.executeSql("insert into City(CityID,Name)values(1,'城厢镇')");
                tx.executeSql("insert into City(CityID,Name)values(2,'浮桥镇')");
                tx.executeSql("insert into City(CityID,Name)values(3,'璜泾镇')");
                tx.executeSql("insert into City(CityID,Name)values(4,'科教新城')");
                tx.executeSql("insert into City(CityID,Name)values(5,'浏河镇')");
                tx.executeSql("insert into City(CityID,Name)values(6,'娄东街道')");
                tx.executeSql("insert into City(CityID,Name)values(7,'沙溪镇')");
                tx.executeSql("insert into City(CityID,Name)values(8,'双凤镇')");
              }
            },function(tx,err){
              console.log(err);
            });
            tx.executeSql('SELECT COUNT(*) AS NUM FROM County',[],function(tx,mes){
              console.log(mes.rows[0].NUM);
              if(mes.rows[0].NUM==0){
                console.log("插入村落数据");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(1,1,'永丰村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(2,1,'胜泾村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(3,1,'万丰村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(4,1,'电站村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(5,1,'东林村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(6,1,'伟阳社区')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(7,1,'新农村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(8,1,'太丰社区')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(9,2,'茜泾村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(10,2,'马北村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(11,2,'新邵村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(12,2,'牌楼社区')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(13,2,'丁泾村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(14,2,'七丫村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(15,2,'九曲社区')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(16,2,'绿化村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(17,2,'时思村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(18,2,'时思社区')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(19,2,'浪港村')");
                tx.executeSql("insert into County(CountyID,CityID,Name)values(20,2,'老闸社区')");
              }
            },function(tx,err){
              console.log(err);
            });
            tx.executeSql('SELECT COUNT(*) AS NUM FROM GroupList',[],function(tx,mes){
              console.log(mes.rows[0].NUM);
              if(mes.rows[0].NUM==0){
                console.log("插入组别数据");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(1,1,'1号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(2,1,'2号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(3,1,'3号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(4,1,'4号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(5,1,'5号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(6,1,'6号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(7,1,'7号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(8,1,'拆迁剩余','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(9,2,'西薛家泾','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(10,2,'东薛家泾','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(11,2,'盛园小区','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(12,3,'1号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(13,3,'2号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(14,3,'3号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(15,3,'4号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(16,3,'5号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(17,3,'6号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(18,3,'7号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(19,3,'8号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(20,4,'电站花苑','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(21,4,'拆迁剩余','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(22,5,'明星小区','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(23,5,'东林佳苑','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(24,5,'拆迁剩余','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(25,6,'1号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(26,6,'2号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(27,6,'3号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(28,6,'4号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(29,6,'5号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(30,6,'6号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(31,6,'7号','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(32,6,'伟阳小区','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(33,6,'景园、逸园','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(34,6,'城北AB','c')");
                tx.executeSql("insert into GroupList(GroupID,CountryID,Name,IsChoose)values(35,6,'拆迁剩余','c')");
              }
            },function(tx,err){
              console.log(err);
            });
          });

vue.js连接数据库 前端vue连接数据库_VUE_05

④下面做一个简单的页面展示

vue.js连接数据库 前端vue连接数据库_VUE_06

<template>
  <div id="test">
    	<!-- 此处展示城镇列表 -->
      <h2>城镇</h2>
			<div class="fl">
				<p v-for="i in citylist" :key="i.CityID" >{{i.Name}}</p>
			</div>
      <!-- 此处展示村落列表 -->
      <h2>村落</h2>
			<div class="fl">
				<p v-for="i in countrylist" :key="i.CountyID" >{{i.Name}}</p>
			</div>
      <!-- 此处展示组别列表 -->
      <h2>组别</h2>
			<div class="fl">
				<p v-for="i in grouplist" :key="i.GroupID">{{i.Name}}</p>
			</div>
  </div>
</template>
methods: {
          selcity:function(){//查询城镇
            var that=this;//与this区别开
            that.citylist=[];
            this.$db.transaction(function(tx){
              tx.executeSql("select * from City",[],function(tx,res){
                  for(var i=0;i<res.rows.length;i++){
                    that.citylist.push(res.rows[i]);
                  }
                },function(tx,err){
                  console.log(err);
                }
              );
            });		
          },
          selcounty:function(){//查询乡村
            var that=this;//与this区别开
            that.countrylist=[];
            this.$db.transaction(function(tx){
              tx.executeSql("select * from County",[],function(tx,res){
                  for(var i=0;i<res.rows.length;i++){
                    that.countrylist.push(res.rows[i]);
                  }
                },function(tx,err){
                  console.log(err);
                }
              );
            });		
          },
          selgroup:function(){//查询组别
            var that=this;//与this区别开
            that.grouplist=[];
            this.$db.transaction(function(tx){
              tx.executeSql("select * from GroupList",[],function(tx,res){
                  for(var i=0;i<res.rows.length;i++){
                    that.grouplist.push(res.rows[i]);
                  }
                },function(tx,err){
                  console.log(err);
                }
              );
            });		
          }
        }

三、详细代码已上传GitHub,地址如下:

1、https://github.com/736755244/Test 


欢迎留言,一起交流学习!