一:manifest.json  选择APP模块配置勾选SQLite(数据库) 

uniapp连接本地mysql数据库 uniapp调用数据库_uniapp连接本地mysql数据库

 

二:在根目录新建 文件夹  utils   ,在  utils  里新建 文件  sqlite.mixin.js   和  sqlite_init.mixin.js

sqlite.mixin.js   (sqlite 数据执行语句)

/*
 * @Descripttion: sqlite 方法集合
 * @version: 
 */
let databaseName="bookkeep";//数据库名
let tabName="bills";//数据库表名
/**
 * @Descripttion: 初始化,进行建表操作
 **/
function createTables(){
	// 注意:tabName不能用数字作为表格名的开头
	//账单表
	let tabNamearray=["bills","plans"];
	
	let name="bookkeep";
	let tabName="bills";
	let tableStructure="id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL";
	return new Promise((resolve, reject) => {
	  plus.sqlite.executeSql({
	    name: name,
	    // sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)',
	    sql: `create table if not exists ${tabName}(${tableStructure})`,
		//sql: `DROP TABLE sqlite_sequence`,
	    success(e) {
			console.log(e);
	      resolve(e);
	    },
	    fail(e) {
	      console.log(e)
	      reject(e);
	    }
	  })
	})
}

/**
 * 执行多条sql语句
 * 
 * @databasename 数据库名
 * @tableName    表名数组["table1","table2"]
 * bills //账单表   plans //计划备忘录表 
 **/
function executeSQL(){
    plus.sqlite.executeSql({  
        name: 'first',  
        sql: ['create table if not exists table_A("where" CHAR(110),"location" CHAR(100),"age" INT(11))','create table if not exists table_B("where" CHAR(110),"location" CHAR(100),"age" INT(11));'],  
        success: function(e){  
            console.log('executeSql success!');  
            plus.sqlite.executeSql({  
                name: 'first',  
                sql: ["insert into table_B values('北京','安乐林:',11)","insert into table_B values('天津','风火轮',22);"],  
                success: function(e){  
                    console.log('executeSql success!');  
                },  
                fail: function(e){  
                    console.log('executeSql failed: '+JSON.stringify(e));  
                }  
            });  
        },  
        fail: function(e){  
            console.log('executeSql failed: '+JSON.stringify(e));  
        }  
    });  
}   
 


// 监听数据是否打开
function isOpenDB(name) {
  let dbName = name;
  let dbPath = `_doc/${name}_record.db`;
  //数据库打开了就返回true,否则返回false
  let isopen = plus.sqlite.isOpenDatabase({
    name: dbName,
    path: dbPath
  })
  return isopen
}

// 创建数据库/打开数据库
function openDB(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.openDatabase({
      name: name || 'testData',
      path: `_doc/${name}_record.db`,
      success: function (e) {
        resolve('openDatabase success!')
      },
      fail: function (e) {
        reject('openDatabase failed: ' + JSON.stringify(e))
      }
    });
  })
}

// 查询所有数据库表名
function queryDBTable(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: name,
      sql: "select * FROM sqlite_master where type='table'",
      success(e) {
		 console.log(e) 
        resolve(e);
      },
      fail(e) {
        console.log(e)
        reject(e);
      }
    })
  })
}

// 查询表是否存在
function queryIsTable(name, tabName) {
  plus.sqlite.selectSql({
    name: name,
    sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
    success(e) {
      resolve(e[0].isTable ? true : false);
    },
    fail(e) {
      console.log(e)
      reject(e);
    }
  })
}

// 创建表
function createTable(name, tabName, tableStructure) {
  // 注意:tabName不能用数字作为表格名的开头
  return new Promise((resolve, reject) => {
    plus.sqlite.executeSql({
      name: name,
      // sql: 'create table if not exists dataList("list" INTEGER PRIMARY KEY AUTOINCREMENT,"id" TEXT,"name" TEXT,"gender" TEXT,"avatar" TEXT)',
      sql: `create table if not exists ${tabName}(${tableStructure})`,
      success(e) {
        resolve(e);
      },
      fail(e) {
        console.log(e)
        reject(e);
      }
    })
  })
}

// 查询表是否存在
function isTable(name, tabName) {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: name,
      sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
      success(e) {
        resolve(e[0].isTable ? true : false);
      },
      fail(e) {
        console.log(e)
        reject(e);
      }
    })
  })
}

// 添加数据
function addSaveData(name, tabName, obj) {
  if (obj) {
    let keys = Object.keys(obj)
    let keyStr = keys.toString()
    let valStr = ''
    keys.forEach((item, index) => {
      if (keys.length - 1 == index) {
        valStr += ('"' + obj[item] + '"')
      } else {
        valStr += ('"' + obj[item] + '",')
      }
    })
    // console.log(valStr)
    let sqlStr = `insert into ${tabName}(${keyStr}) values(${valStr})`
    // console.log(sqlStr)
    return new Promise((resolve, reject) => {
      plus.sqlite.executeSql({
        name: name,
        sql: sqlStr,
        success(e) {
			console.log(e);
          resolve(100);
        },
        fail(e) {
          console.log(e)
          reject(e);
        }
      })
    })
  } else {
    return new Promise((resolve, reject) => {
      reject("错误")
    })
  }
}

/*
** 查询数据库数据
* @name     数据库名
* @tabName  表名
* @setData  查询条件{"id":1,"type":"goods"}
* @byName   排序的字段名
* @byType   排序规则  asc升序   desc降序
*/
function selectDataList(name, tabName, setData, byName, byType) {
  let setStr = ''
  let sql = ''
  if (JSON.stringify(setData) !== '{}') {
    let dataKeys = Object.keys(setData)
    dataKeys.forEach((item, index) => {
      console.log(setData[item])
      setStr += (
        `${item}=${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? " and " : ""}`)
    })
    sql = `select * from ${tabName} where ${setStr}`
  } else {
    sql = `select * from ${tabName}`
  }
  if (byName && byType) {
    // desc asc
    sql += ` order by ${byName} ${byType}`
  }
  console.log(sql)
  if (tabName !== undefined) {
    return new Promise((resolve, reject) => {
      plus.sqlite.selectSql({
        name: name,
        sql: sql,
        success(e) {
          resolve(e);
        },
        fail(e) {
          console.log(e)
          reject(e);
        }
      })
    })
  } else {
    return new Promise((resolve, reject) => {
      reject("错误")
    });
  }
}

/*
** 查询数据库数据直接使用语句查询
* @name     数据库名
* @tabName  表名
* @setDatasql  查询条件sql语句
* @byName   排序的字段名
* @byType   排序规则  asc升序   desc降序
*/
function selectDataListsql(name, tabName, setDatasql, byName, byType) {
  let setStr = ''
  let sql = ''
  if (setDatasql!='') {
    sql = `select * from ${tabName} where ${setDatasql}`
  } else {
    sql = `select * from ${tabName}`
  }
  if (byName && byType) {
    // desc asc
    sql += ` order by ${byName} ${byType}`
  }
  console.log(sql)
  if (tabName !== undefined) {
    return new Promise((resolve, reject) => {
      plus.sqlite.selectSql({
        name: name,
        sql: sql,
        success(e) {
          resolve(e);
        },
        fail(e) {
          console.log(e)
          reject(e);
        }
      })
    })
  } else {
    return new Promise((resolve, reject) => {
      reject("错误")
    });
  }
}

/*
** 查询数据库数据直接使用语句查询
* @name     数据库名
* @setDatasql  查询条件纯sql语句
*/
function selectDataListsqlonly(name, setDatasql) {
    return new Promise((resolve, reject) => {
      plus.sqlite.selectSql({
        name: name,
        sql: setDatasql,
        success(e) {
			console.log(e)
          resolve(e);
        },
        fail(e) {
          console.log(e)
          reject(e);
        }
      })
    })
  
}

// 获取数据库分页数据
/**
 * 
 * @param {*} name 
 * @param {*} tabName 
 * @param {*} num 页码
 * @param {*} size 页面大小返回条数
 * @param {*} byName 排序主键字段
 * @param {*} byType 排序类型 desc倒序 / asc正序
 */
async function queryDataList(name, tabName, num, size, byName, byType) {
  let count = 0
	let sql = ''
	let numindex = 0
	await queryCount(name, tabName).then((resNum) => {
		count = Math.ceil(resNum[0].num / size)
	})
	if(((num - 1) * size) == 0) {
	    numindex = 0
	} else {
		numindex = ((num - 1) * size) + 1
	}
	sql = `select * from ${tabName}`
	if(byName && byType) {
		// desc asc
		sql += ` order by ${byName} ${byType}`
	}
	sql += ` limit ${numindex},${size}`
	if (count < num - 1) {
		return new Promise((resolve, reject) => {
			reject("无数据")
		});
	} else {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: name,
				// sql: "select * from userInfo limit 3 offset 3",
				sql:sql ,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	}
}

// 查询表数据总条数
function queryCount(name, tabName) {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
			name: name,
			sql: "select count(*) as num from " + tabName,
			success(e) {
				resolve(e);
			},
			fail(e) {
				reject(e);
			}
		})
  })
}

// 修改(更新)数据
// 示例:UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
// UPDATE 表名 SET 要修改字段 = '修改内容' WHERE 筛选条件 = 6;
/**
 * 
 * @param {*} name 数据库名
 * @param {*} tabName 表名
 * @param {*} setData 设置值 (修改字段 + 修改内容)
 * @param {*} setName 筛选条件
 * @param {*} setVal 筛选值
 * @returns 
 */
function updateSqlData(name, tabName, setData, setName, setVal) {
  if (JSON.stringify(setData) !== '{}') {
    let dataKeys = Object.keys(setData)
		let setStr = ''
		dataKeys.forEach((item, index) => {
      // console.log(item, setData[item])
			setStr += (
				`${item} = ${JSON.stringify(setData[item])}${dataKeys.length - 1 !== index ? "," : ""}`)
    })
    console.log(setStr)
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: name,
				sql: `update ${tabName} set ${setStr} where ${setName} = "${setVal}"`,
				success(e) {
					//resolve(e);
					resolve(100);
				},
				fail(e) {
					console.log(e)
					reject(e);
				}
			})
		})
	} else {
		return new Promise((resolve, reject) => {
			reject("错误")
		});
	}
}

// 关闭数据库
function closeDB(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.closeDatabase({
      name: name,
      success: function (e) {
        resolve('closeDatabase success!');
      },
      fail: function (e) {
        reject('closeDatabase failed: ' + JSON.stringify(e));
      }
    });
  })
}
export{
  databaseName,
  tabName,	  
  createTables,	
  openDB,
  closeDB,
  isOpenDB,
  queryDBTable,
  queryIsTable,
  createTable,
  isTable,
  addSaveData,
  selectDataList,
  selectDataListsql,
  selectDataListsqlonly,
  queryCount,
  updateSqlData,
  queryDataList
}

sqlite_init.mixin.js(初始化sqlite  和 版本更新时更新sqlite)

/*
 * @Descripttion: sqlite 方法集合
 * @version: 
 */
let databaseNameinit="bookkeep";//数据库名
let tabNameinit="bills";//数据库表名
/**
 * @Descripttion: 初始化,进行建表操作
 **/
 
 // 创建数据库/打开数据库
 function openDBinit(name) {
   return new Promise((resolve, reject) => {
     plus.sqlite.openDatabase({
       name: name || 'testData',
       path: `_doc/${name}_record.db`,
       success: function (e) {
         resolve('openDatabase success!')
       },
       fail: function (e) {
         reject('openDatabase failed: ' + JSON.stringify(e))
       }
     });
   })
 }
 
/**
 * 执行多条sql语句
 * 
 * @databasename 数据库名
 * @tableName    表名
 * bills //账单表   plans //计划备忘录表
 **/
async function  executeSQLinit(version){
	let sqlversion=9;//数据库版本,只要对不上就进行更新每次需要更新数据库都要重设此字段
	let sqlsqlcode=100;
	if(sqlversion==version){
		console.log("无需更新");
		let recode={
			"code":sqlsqlcode,
			"version":sqlversion
		};
		return recode;
	}
	let databaseName="bookkeep";//数据库名 
	let tabname=["sqldo","bills","plans"];//表名
	//需要进行更新的数据语句依次从后加入 sqlite不能执行set 操作,只能add
	let sqldata=[
		{"sql":'create table if not exists sqldo(id INTEGER PRIMARY KEY AUTOINCREMENT,num INT NOT NULL DEFAULT 0--执行数据语句号num\n); --数据库更新版本库\n',"num":'1'},
		{"sql":'create table if not exists bills(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL); --账单表\n',"num":'2'},
		{"sql":'create table if not exists plans(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT, --备注\n time VARCHAR(80) NOT NULL, --完成时间\n title TEXT, --名称\n classifi VARCHAR(80) NOT NULL, --分类\n btype TINYINT NOT NULL, --大类别0临时1常规\n uid INT NOT NULL --用户id\n); --计划备忘录\n',"num":'3'},
		
		//{"sql":'insert into sqldo (num) values (4); --完成状态-1未完成1已经完成\n',"num":'4'},
		{"sql":'ALTER TABLE plans ADD COLUMN amount TINYINT DEFAULT -1; --完成状态-1未完成1已经完成\n',"num":'4'},//计划表更新字段
		{"sql":'ALTER TABLE plans ADD COLUMN endtime VARCHAR(80) DEFAULT 0; --结束时间\n',"num":'5'},
		{"sql":'ALTER TABLE plans ADD COLUMN donum TINYINT DEFAULT 0; --完成度0~100\n',"num":'6'},
		{"sql":'ALTER TABLE plans ADD COLUMN deletetime VARCHAR(80) DEFAULT 0; --删除时间0就是未删除\n',"num":'7'},
		{"sql":'ALTER TABLE bills ADD COLUMN deletetime VARCHAR(80) DEFAULT 0; --删除时间0就是未删除\n',"num":'8'},
		//版本1.0.992 常规查询时先查询o 状态,然后再通过时间本年月日进行查询,如果存在的就进行数量统计。不存在,完成就为0,当完成时,点击完成时,就进行完成添加。
		//{"sql":'ALTER TABLE plans ADD COLUMN btypeend TINYINT NOT NULL DEFAULT 0; --常规任务终极状态0:进行时;1:完成终极状态;\n',"num":'9'},
		//版本1.0.993
		{"sql":'ALTER TABLE plans ADD COLUMN plansid INTEGER NOT NULL DEFAULT 0; --常规任务完成时对应的常规任务id,0就代变为常规任务根任务\n',"num":'10'},
		{"sql":'ALTER TABLE plans ADD COLUMN year TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的年,0就代变为默认\n',"num":'11'},
		{"sql":'ALTER TABLE plans ADD COLUMN quarter TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的季度,0就代变为默认\n',"num":'12'},
		{"sql":'ALTER TABLE plans ADD COLUMN month TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的月,0就代变为默认\n',"num":'13'},
		{"sql":'ALTER TABLE plans ADD COLUMN week TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的周,0就代变为默认\n',"num":'14'},
		{"sql":'ALTER TABLE plans ADD COLUMN day TINYINT NOT NULL DEFAULT 0; --常规任务完成时对应的日,0就代变为默认\n',"num":'15'},
		
		{"sql":'ALTER TABLE plans ADD COLUMN btypeend TINYINT NOT NULL DEFAULT 0; --常规任务终极状态0:进行时;1:完成终极状态;\n',"num":'16'},
	];
	//查询数据库版本表是否存在,如果不存在就全部更新
	let istable=await queryIsTableinit(databaseName, "sqldo");
	if(istable){ //先进行判断需要执行哪些语句
		//查询数据库更新的版本
		let setDatasql='select * from sqldo;';
		let sedatalist=await selectDataListsqlonly(databaseName,setDatasql);
		sedatalist.forEach((item, index) =>{ //从数据中循环读取,再进行比对,如果没有的就进行执行语句,并添加到数据库
		    sqldata.forEach((items, indexs) =>{ 
				if(item.num==items.num){
					sqldata.splice(indexs,1);
				}
			});
		});
		if(sqldata.length==0){//空数据没有执行语句
		    console.log("判断完毕,无需更新");
			let recode={
				"code":sqlsqlcode,
				"version":sqlversion
			};
			return recode;
		}
	}
	//开始执行sql语句
	for (var i=0;i<sqldata.length;i++)
	{ 
		let sqlone=sqldata[i].sql;
		let obj={
			"num":sqldata[i].num
		};
		let sqlcode=await queryDBsqloney(databaseName,sqlone);
		if(sqlcode==100){//成功执行添加操作
			let addcode=await addSaveData(databaseName,"sqldo",obj);
			if(addcode==200){
				sqlsqlcode=200;
			}
		}
	}
	let recode={
		"code":sqlsqlcode,
		"version":sqlversion
	};
	return recode;
	
	
	/*
	let sql=[
		'create table if not exists bills(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT,time VARCHAR(80) NOT NULL,amount FLOAT NOT NULL,classifi VARCHAR(80) NOT NULL,btype TINYINT NOT NULL,uid INT NOT NULL); --账单表\n',//账单
		'create table if not exists plans(id INTEGER PRIMARY KEY AUTOINCREMENT,remarks TEXT, --备注\n time VARCHAR(80) NOT NULL, --完成时间\n title TEXT, --名称\n classifi VARCHAR(80) NOT NULL, --分类\n btype TINYINT NOT NULL, --大类别0临时1常规\n uid INT NOT NULL --用户id\n); --计划备忘录\n',
		'ALTER TABLE plans ADD COLUMN amount TINYINT DEFAULT -1; --完成状态-1未完成1已经完成\n',
		'ALTER TABLE plans ADD COLUMN endtime VARCHAR(80) DEFAULT 0; --结束时间\n',
		'ALTER TABLE plans ADD COLUMN donum TINYINT DEFAULT 0; --完成度0~100\n'
	];
	openDBinit(databaseName);
    plus.sqlite.executeSql({  
        name: databaseName,  
        sql: sql,
		 // sql:['DROP TABLE plans;','DROP TABLE plansss;','DROP TABLE planssss;'],//删除表
        success: function(e){  
            console.log('executeSql2222 success!');   
        },  
        fail: function(e){  
            console.log('executeSql failed: '+JSON.stringify(e));  
        }  
    }); 
	 */
}  
 
/*
** 查询数据库数据直接使用语句查询
* @name     数据库名
* @setDatasql  查询条件纯sql语句
*/
function selectDataListsqlonly(name, setDatasql) {
    return new Promise((resolve, reject) => {
      plus.sqlite.selectSql({
        name: name,
        sql: setDatasql,
        success(e) {
          resolve(e);
        },
        fail(e) {
          reject(e);
        }
      })
    })
  
} 
//执行数据库语句 
function queryDBsqloney(databaseName,sql){
	return new Promise((resolve, reject) => {
	plus.sqlite.executeSql({
	    name: databaseName,  
	    sql: sql,
		 // sql:['DROP TABLE plans;','DROP TABLE plansss;','DROP TABLE planssss;'],//删除表
	    success: function(e){  
	        resolve(100);   
	    },  
	    fail: function(e){  
	        console.log('executeSql failed: '+JSON.stringify(e));  
			reject(200);
	    }  
	});
	})
}
//执行数据添加
function addSaveData(name, tabName, obj) {
  if (obj) {
    let keys = Object.keys(obj)
    let keyStr = keys.toString()
    let valStr = ''
    keys.forEach((item, index) => {
      if (keys.length - 1 == index) {
        valStr += ('"' + obj[item] + '"')
      } else {
        valStr += ('"' + obj[item] + '",')
      }
    })
    // console.log(valStr)
    let sqlStr = `insert into ${tabName}(${keyStr}) values(${valStr})`
    // console.log(sqlStr)
    return new Promise((resolve, reject) => {
      plus.sqlite.executeSql({
        name: name,
        sql: sqlStr,
        success(e) {
			//return 100;
          resolve(100);
        },
        fail(e) {
			r//eturn 200;
          console.log(e)
          reject(200);
        }
      })
    })
  } else {
	  //return 200;
	  
    return new Promise((resolve, reject) => {
      reject("错误")
    })
	
  }
}




// 查询所有数据库表名
function queryDBTableinit(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: name,
      sql: "select * FROM sqlite_master where type='table'",
      success(e) {
		 console.log(e) 
        resolve(e);
      },
      fail(e) {
        console.log(e)
        reject(e);
      }
    })
  })
}

// 查询表是否存在
function queryIsTableinit(name, tabName) {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: name,
      sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${tabName}'`,
      success(e) {
        resolve(e[0].isTable ? true : false);
      },
      fail(e) {
        console.log(e)
        reject(e);
      }
    })
  })
}

// 关闭数据库
function closeDBinit(name) {
  return new Promise((resolve, reject) => {
    plus.sqlite.closeDatabase({
      name: name,
      success: function (e) {
        resolve('closeDatabase success!');
      },
      fail: function (e) {
        reject('closeDatabase failed: ' + JSON.stringify(e));
      }
    });
  })
}
export{
  databaseNameinit,
  tabNameinit,	  
  executeSQLinit,
  openDBinit,
  queryDBTableinit,
  queryIsTableinit,
  closeDBinit
}

三:App.vue 文件引入 初始化文件

<style lang="scss">
	@import "uview-ui/index.scss";
</style>

<script>
import {
		     databaseName,
			 tabName,
		     createTables,
		     openDB,
             closeDB,
             isOpenDB,
             queryDBTable,
             queryIsTable,
             createTable,
             isTable,
             addSaveData,
             selectDataList,
			 selectDataListsqlonly,
             queryCount,
             updateSqlData,
             queryDataList,
			 } from '@/utils/sqlite.mixin.js';

import { 
		     executeSQLinit,
		     queryDBTableinit
			 } from '@/utils/sqlite_init.mixin.js'; //引入初始化文件
			 
import  callCheckVersion from '@/uni_modules/uni-upgrade-center-app/utils/check-update.js';//热更新				 
			 
export default {
	methods: {
		async tocheckversion(){
			let erwe=await callCheckVersion();//检测更新
		},
		async dbsql(){
			let that=this;
			let codeversion=await executeSQLinit(that.vuex_sqlversion);
			if(codeversion.code==100){
				let version=codeversion.version;
				that.$u.vuex('vuex_sqlversion', version);
			}
			console.log(codeversion);
			console.log(await queryDBTableinit(databaseName));
		},
	},
	onLaunch: function() {
		let that=this;
		//检测更新
		that.tocheckversion();
		//检测是数据库是否需要更新
		openDB(databaseName);//打开或新建数据库
		that.dbsql();//执行数据库更新
		console.log('App onLaunch');
	},
	onShow: function() {
		
	},
	onHide: function() {
		console.log('App Hide');
	}
};
</script>

<style>
/* 解决头条小程序组件内引入字体不生效的问题 */
/* #ifdef MP-TOUTIAO */
@font-face {
	font-family: uniicons;
	src: url('/static/uni.ttf');
}
/* #endif */
</style>
<style lang='scss'>
	view,
	scroll-view,
	swiper,
	swiper-item,
	cover-view,
	cover-image,
	icon,
	text,
	rich-text,
	progress,
	button,
	checkbox,
	form,
	input,
	label,
	radio,
	slider,
	switch,
	textarea,
	navigator,
	audio,
	camera,
	image,
	video {
		box-sizing: border-box;
	}
</style>

 四:页面里引用查询SQLITE数据   例如 index.vue   页面引用sqlite 数据

<template>
	<view class="container">
		<view class="h">
			<view class="u-flex user-box u-p-l-30 u-p-r-20 u-p-b-30 bg-color">
				<view class="u-m-r-10 u-flex-1">
					<view class="header-text"> {{year}} {{year_str}}</view>
				</view>
				<view class="u-m-r-10 u-flex-1">
					<view class="header-text">{{income_str}}</view>
				</view>
				<view class="u-m-r-10 u-flex-1">
					<view class="header-text"> {{expenditure_str}}</view>
				</view>
			</view>
			<view class="u-flex user-box u-p-l-30 u-p-r-20 u-p-b-30 bg-color">
				<view class="u-m-r-10 u-flex-1">
					<view class="header-content">
						<view class="header-children" @click="clickDate">
							<view class="header-children header-content-2x">{{month}}</view>
							<view class="header-children">{{month_str}}</view>
							<view class="header-children">
								<u-icon class="header-children header-icon" name="arrow-down-fill" color="#323232" size="30"></u-icon>
							</view>
							
						</view>
						
						
						<view class="header-children header-text u-p-r-20"> {{inexpenditure_str}} </view>
						<view class="header-children header-content-2x"> {{all_mongey}}</view>
						<view class="header-children"> .{{all_mongey_decimal}} </view>
						
						
					</view>
					
				</view>
				<view class="u-m-r-10 u-flex-1">
					<view>
						<view class="header-children header-content-2x"> {{income_int}} </view>
						<view class="header-children"> .{{income_decimal}} </view>
					</view>
				</view>
				<view class="u-m-r-10 u-flex-1">
					<view>
						<view class="header-children header-content-2x"> {{expenditure_int}} </view>
						<view class="header-children"> .{{expenditure_decimal}} </view>
					</view>
				</view>
			</view>
		</view>
		
		<view style="height: 100%">
			<scroll-view class="scroll-content"
			scroll-y="true" 
			
			refresher-enabled="true"
			:refresher-triggered="refresherTriggered"
			@refresherrefresh="refresher()"
			@refresherrestore="refresherrestore()"
		    @refresherabort="refresherabort()">
				<view>
					<view v-for="(item, index) in list" :key="index">
						<view class="u-flex list-box">
							<view class="u-m-r-10 u-flex-1">
								<view class="header-text"> {{item.time}} {{item.week}}</view>
							</view>
							<view class="u-m-r-10 u-flex-1">
								<view class="header-text">{{income_str}} {{item.income}}</view>
							</view>
							<view class="u-m-r-10 u-flex-1">
								<view class="header-text">{{expenditure_str}} {{item.expenditure}}</view>
							</view>
						</view>
						
						<view class="list-box-children" v-for="(item1, index1) in item.list" :key="index1" @click="toDetail(item1.id)">
							<view class="u-flex">
								<image slot="icon" class="box-icon" :src="item1.icon" mode="" :lazy-load="lazy_load"></image>
							</view>
							<view class="box-left">
								{{item1.bill_type}}
							</view>
							<view class="box-desc">
								{{item1.desc}}
							</view>
							<view class="u-flex-1 box-right">
								{{item1.bill_money}}
							</view>
						</view>
					</view>
				</view>
			</scroll-view>
		</view>
		<view v-if="is_show_emoty" :style="{height:scroll_view_height+'px'}">
			<u-empty text="暂无明细" mode="list"></u-empty>
		</view>
		
		<yinrh-menu-float :imgMenu="yinrh_menu_float_imgMenu" :imgLists="yinrh_menu_float_imgLists" @menuClick="floatTouchClick" />
		
		<u-picker mode="time" v-model="picker_show" :params="picker_params" :default-time="picker_time" @confirm="pickerConfirm"></u-picker>
	</view>
</template>

<script>
	import {
		     databaseName,
			 tabName,
		     createTables,
		     openDB,
	         closeDB,
	         isOpenDB,
	         queryDBTable,
	         queryIsTable,
	         createTable,
	         isTable,
	         addSaveData,
	         selectDataList,
			 selectDataListsql,
			 selectDataListsqlonly,
	         queryCount,
	         updateSqlData,
	         queryDataList,
			 } from '@/utils/sqlite.mixin.js';   //引入sqlite 操作文件
		
	export default {
		data() {
			return {
				//悬浮球
				yinrh_menu_float_imgMenu:"/uni_modules/yinrh-menu-float/static/main.png",
				yinrh_menu_float_imgLists:[
					//{src:"pages/chart/index",img:"/uni_modules/yinrh-menu-float/static/menu_chart.png"},//图表功能
					{src:"pages/bill/add",img:"/uni_modules/yinrh-menu-float/static/menu_bookkeeping.png"},
					{src:"pages/bill/list",img:"/uni_modules/yinrh-menu-float/static/menu_bill.png"},
				],
				
				
				picker_params: {
					year: true,
					month: true,
					day: false,
					hour: false,
					minute: false,
					second: false
				},
				picker_show: false,
				picker_time: "",
				
				phone_height: "0", // default:624
				scroll_view_height: "0", // default:546 
				
				refresherTriggered: false,
			    _refresherTriggered: false,
				
				year_str: "年",
				month_str: "月",
				income_str: "收入",
				expenditure_str: "支出",
				inexpenditure_str: "结余",
				
				year: "",
				month: "",
				
				income_sum: "0.00",
				income_int: "0",
				income_decimal: "00",
				
				expenditure_sum: "0.00",
				expenditure_int: "0",
				expenditure_decimal: "00",
				
				all_money_sum:"0.00",
				all_mongey:"0",
				all_mongey_decimal:"00",
				
				
				list : [],
				/*
				list : [
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						},
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						},
						{bill_id: 2,
						bill_money: "+34.00",
						bill_type: "支付宝",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "../../static/type/1.png",
						time: "03-05",
						}
					],
					time: "03月06日",
					week: "星期六",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					},
					{expenditure: "34.00",
					income: "0.00",
					list: [
						{bill_id: 1,
						bill_money: "-34.00",
						bill_type: "衣服",
						desc: "",
						icon: "../../static/type/1.png",
						id: 1,
						img_url: "",
						time: "03-05",
						}
					],
					time: "03月05日",
					week: "星期五",
					}
				],
				*/
				reload: false,
				
				is_show_emoty: false,
				lazy_load: true,
			}
		},
		methods: {
			//悬浮球点击跳转
			floatTouchClick(e){
				uni.$u.route({
					url: e,
					params: {
						name: 'lisa'
					}
				})
			},
			
			clickDate(){
				this.picker_show = true;
			},
			pickerConfirm(e) {
				if (this.picker_params.year) this.year = e.year;
				if (this.picker_params.month) this.month = e.month;
				
				this.picker_time = this.year + "-" + this.month;
				console.log("3434343");
				this.toMonthcount();
				
			},
			refresher(){
				let that=this;
				console.log('下拉刷新');
				
				if(that._refresherTriggered){
					return;
				}
				that.refresherTriggered = true;
				that._refresherTriggered = true;
				setTimeout(() => {
				    that.refresherTriggered = false;
				    that._refresherTriggered = false;
				}, 3000)
			},
			refresherrestore(){
				
				console.log("自定义下拉刷新被复位");
				//this.refresherTriggered = false;
				//this._refresherTriggered = false;
				this.refresherTriggered = 'false'; // 需要重置
				
			},
			refresherabort(){
				
				console.log("自定义下拉刷新被中止");
				//this.refresherTriggered = false;
				//this._refresherTriggered = false;
				
			},
			//总数格式处理
			formatSum(){
				
				
				let all_mongey_sum_arr=this.all_money_sum.split('.');
				this.all_mongey = all_mongey_sum_arr[0];
				this.all_mongey_decimal = all_mongey_sum_arr[1];
				
				let income_sum_arr = this.income_sum.split('.');
				this.income_int = income_sum_arr[0];
				this.income_decimal = income_sum_arr[1];
				
				let expenditure_sum_arr = this.expenditure_sum.split('.');
				this.expenditure_int = expenditure_sum_arr[0];
				this.expenditure_decimal = expenditure_sum_arr[1];
			},
			toDetail(e){
					uni.$u.route('/pages/bill/detail', {
						id: e,
					});
			},
			//查询显示月收入与支出总数查询显示月账单列表不进行分页,每次查询一个月的数据
			async toMonthcount(){    //查询数据 都采取异步 async 
				let that=this;
				//入库
				if(!isOpenDB(databaseName)){
					openDB(databaseName);//打开数据库
				}
				let daydate=that.getDaysInMonth(that.year,that.month);
				let daynum=daydate.length;
				//月第一天
				let start_day=that.year+"-"+that.month+"-01";
				let datetime_start=new Date(start_day.replace(/-/g,'/')).getTime()/1000;
				//月最后一天
				let end_day=that.year+"-"+that.month+"-"+daynum;
				let datetime_end=new Date(end_day.replace(/-/g,'/')).getTime()/1000;
				let setDatasql="select time,group_concat(amount) as amounts,group_concat(id) as id,group_concat(classifi) as classifi, group_concat(btype) as btypes,group_concat(remarks) as remarkss from "+tabName+" where time >= "+datetime_start+ " and " + " time <= " + datetime_end +" and deletetime=0 group by time order by time desc";
				
				let datalistuser=[];
				let datalist=await selectDataListsqlonly(databaseName,setDatasql);
				
				//console.log(daydate);
				
				
				datalist.forEach((item, index) =>{
					/*日期处理*/
					let m = new Date((Number(item.time))*1000).getMonth()+1;//月份
					if(m<10){
						m="0"+m+"月";
					}else{
						m=m+"月";
					}
					let d = new Date((Number(item.time))*1000).getDate();//日期
					if(d<10){
						d="0"+d+"日";
					}else{
						d=d+"日";
					}
					let x = new Date((Number(item.time))*1000).getDay();//星期几
					switch(x)
					{
					    case 0:
					        x="星期天";
					        break;
					    case 1:
					        x="星期一";
					        break;
						case 2:
						    x="星期二";
						    break;
						case 3:
						    x="星期三";
						    break;
						case 4:
						    x="星期四";
						    break;
						case 5:
						    x="星期五";
						    break;
						case 6:
						    x="星期六";
						    break;					
					    default:
					        x="星期天";
					}
					datalistuser[index]={};
					datalistuser[index].expenditure=0;
					datalistuser[index].income=0;
					datalistuser[index].time=m+d;
					datalistuser[index].week=x;
					/*分割字符串*/
					let amountarray=item.amounts.split(",");
					datalistuser[index].amountarray=amountarray;
					let idarray=item.id.split(",");
					datalistuser[index].idarray=idarray;
					let classifiarray=item.classifi.split(",");
					datalistuser[index].classifiarray=classifiarray;
					let btypesarray=item.btypes.split(",");
					datalistuser[index].btypesarray=btypesarray;
					let remarkssarray=item.remarkss.split(",");
					datalistuser[index].remarkssarray=remarkssarray;
				});
				datalistuser.forEach((item, index) =>{
					datalistuser[index].list=[];
					item.idarray.forEach((itemm,indexx)=>{
						/**总收入支出计算**/
						if(item.btypesarray[indexx]==0){
							datalistuser[index].expenditure+=Number(item.amountarray[indexx]);
						}else if(item.btypesarray[indexx]==1){
							datalistuser[index].income+=Number(item.amountarray[indexx]);
						}
						
						datalistuser[index].list[indexx]={};
						datalistuser[index].list[indexx].bill_id=item.idarray[indexx];
						datalistuser[index].list[indexx].bill_money=item.btypesarray[indexx]==0 ? "-"+item.amountarray[indexx] : "+"+item.amountarray[indexx];
						let classifit="食";
						let classifitico="/static/type2/shi.png";
						let classifittico=[
							{"id":"yi","text":"衣","icon":"/static/type2/yi.png"},
							{"id":"shi","text":"食","icon":"/static/type2/shi.png"},
							{"id":"zhu","text":"住","icon":"/static/type2/zhu.png"},
							{"id":"xing","text":"行","icon":"/static/type2/xing.png"},
							{"id":"yiliao","text":"医疗","icon":"/static/type2/yiliao.png"},
							{"id":"waer","text":"娃儿","icon":"/static/type2/ertong.png"},
							{"id":"fumu","text":"父母","icon":"/static/type2/fumu.png"},
							{"id":"xuexi","text":"学习","icon":"/static/type2/xuexi.png"},
							{"id":"yule","text":"娱乐","icon":"/static/type2/yule.png"},
							{"id":"qita","text":"其他","icon":"/static/type2/qita.png"},
							{"id":"yinhangka","text":"银行卡","icon":"/static/type2/yinhangka.png"},
							{"id":"weixin","text":"微信","icon":"/static/type2/weixin.png"},
							{"id":"zhifubao","text":"支付宝","icon":"/static/type2/zhifubao.png"},
							{"id":"xianjin","text":"现金","icon":"/static/type2/xianjin.png"},
							{"id":"xianjin","text":"虚拟币","icon":"/static/type2/xuni.png"},
							{"id":"qita","text":"其他","icon":"/static/type2/qita.png"}
						];
						    
						classifittico.forEach((icoitem,icoindex)=>{
							if(icoitem.id==item.classifiarray[indexx]){
								classifit=icoitem.text;
								classifitico=icoitem.icon;
							}
						});
						datalistuser[index].list[indexx].bill_type=classifit;
						datalistuser[index].list[indexx].desc=item.remarkssarray[indexx];
						datalistuser[index].list[indexx].icon=classifitico;
						datalistuser[index].list[indexx].id=item.idarray[indexx];
						datalistuser[index].list[indexx].img_url="../../static/type/1.png";
						datalistuser[index].list[indexx].time="../../static/type/1.png";
					});
				});
				
				
				//数据最终处理
				that.income_sum=0;
				that.expenditure_sum=0;
				datalistuser.forEach((item, index) =>{
					datalistuser[index].list=datalistuser[index].list.reverse();//数组反转
					
					that.expenditure_sum=Number(datalistuser[index].expenditure)+Number(that.expenditure_sum);
					let expenditure=item.expenditure.toFixed(3);
					datalistuser[index].expenditure="-"+expenditure.substring(0,expenditure.lastIndexOf(".")+3);
					
					that.income_sum=Number(datalistuser[index].income)+Number(that.income_sum);
					let income=item.income.toFixed(3);
					datalistuser[index].income="+"+income.substring(0,income.lastIndexOf(".")+3);
				});
				
				//支出与收入进行处理
				that.all_money_sum=0;
				that.all_money_sum=that.income_sum-that.expenditure_sum;
				if(that.all_money_sum>=0){
					that.all_money_sum=that.all_money_sum.toFixed(3);
					that.all_money_sum="+"+that.all_money_sum.substring(0,that.all_money_sum.lastIndexOf(".")+3);
				}else{
					that.all_money_sum=that.all_money_sum.toFixed(3);
					that.all_money_sum=that.all_money_sum.substring(0,that.all_money_sum.lastIndexOf(".")+3);
				}
				
				
				that.expenditure_sum=that.expenditure_sum.toFixed(3);
				that.expenditure_sum="-"+that.expenditure_sum.substring(0,that.expenditure_sum.lastIndexOf(".")+3);
				that.income_sum=that.income_sum.toFixed(3);
				that.income_sum="+"+that.income_sum.substring(0,that.income_sum.lastIndexOf(".")+3);
				that.formatSum();
				that.list=datalistuser;
				
				//console.log(that.list);
				
			},
			
			getDaysInMonth(year,month){
				month = parseInt(month,10); //parseInt(number,type)这个函数后面如果不跟第2个参数来表示进制的话,默认是10进制。
				var temp = new Date(year,month,0);
				let n = +temp.getDate();
				let dataTimes = [];
				for (var i = 1; i < n + 1; i++) {
				  dataTimes.push(i);
				}
			    return dataTimes //dataTimes.length
			},
			
			
		},
		onShow() {
			console.log("首页显示");
			this.toMonthcount();
		},
		onLoad(options){
			
			// 计算scroll-view高度
			
			let _this = this;
			uni.getSystemInfo({
				success(res) {
					console.log(res);
					
					_this.phone_height = res.windowHeight;
					_this.scroll_view_height = _this.phone_height;
					/*
					let v = uni.createSelectorQuery().select(".h");
					v.boundingClientRect(data=>{
						_this.scroll_view_height = _this.phone_height - data.height;
					}).exec();
					*/
				}
			})
			
			
			let t = new Date().toISOString().slice(0, 10);
			let t_a = t.split("-");
			this.year = t_a[0] || "";
			this.month = t_a[1] || "";
			this.picker_time = this.year + "-" + this.month;
			
		}
	}
</script>

<style lang="scss">
	.scroll-content{
		width:100%;
		height:calc(100vh - var(--window-top));//calc()是动态计算函数
	}
	.container{
		background-color: #f7f7f7;
		
		.bg-color{
			background-color: #FDDC2D
		}
		.header-text{
			font-size: 25rpx;
			color: #7a7a7a;
		}
		.header-children{
			display: initial;
		}
		.header-content-2x{
			font-size: 40rpx;
		}
		.header-icon{
			padding: 15rpx;
		}
		
		.list-box{
			padding: 18rpx 18rpx 18rpx 40rpx;
		}
		.list-box-children{
			display: -webkit-box;
			display: -webkit-flex;
			display: flex;
			-webkit-box-orient: horizontal;
			-webkit-box-direction: normal;
			-webkit-flex-direction: row;
			flex-direction: row;
			-webkit-box-align: center;
			-webkit-align-items: center;
			align-items: center;
			position: relative;
			box-sizing: border-box;
			width: 100%;
			padding: 26rpx 32rpx;
			font-size: 28rpx;
			line-height: 50rpx;
			color: #606266;
			background-color: #fff;
			text-align: left;
			
			.box-icon{
				width: 50rpx;
				height: 50rpx;
				margin-right: 35rpx;
			}
			
			.box-left{
				width: auto;
				font-weight: 500;
				font-size: 28rpx;
			}
			
			.box-right{
				overflow: hidden;
				text-align: right;
				vertical-align: middle;
				color: #909399;
				font-size: 26rpx;
			}
			
			.box-desc{
				font-weight: 500;
				width: 300rpx;
				margin-left: 50rpx;
				overflow: hidden;
				text-overflow: ellipsis;
				-ms-text-overflow: ellipsis;
				display: -webkit-box;
				line-clamp: 1;
				-webkit-line-clamp: 1;
				-webkit-box-orient: vertical;
			}
		}
	}
	
	.u-cell-icon {
		width: 36rpx;
		height: 36rpx;
		margin-right: 8rpx;
	}
</style>