背景
某些不会频繁变动，但是数据量可能比较大，查询也比较频繁地数据，例如通讯录等。如果每次都查询服务器的数据库可能造成服务器压力过大，考虑在本地做数据库存储，有更新时从服务器同步数据到本地数据库，用户操作查询则使用本地数据库查询。
方案
-
进入页面时（或者其他适当时机）请求服务器数据，并将数据写入本地数据库。使用sqlite工具，通过sql语句建表，插入等方式。
待解决的问题：json格式的数据如何通过sql语句批量插入数据库 -
有数据更新的时候同步触发本地数据库更新
-
查询时连接本地数据，进行查询
代码
安装sqlite
npm i sqlite3 --save-dev
npm install -g node-gyp
- 新建sqlite.ts文件，编写sqlite的工具函数
import sqlite3 from 'sqlite3';
const sqlite = sqlite3.verbose();
class Sqlite {
constructor() {
this.instance;
this.db = null;
}
// 连接数据库
connect(path) {
return new Promise((resolve, reject) => {
this.db = new sqlite.Database(path, err => {
if (err === null) {
resolve(err);
} else {
reject(err);
}
});
});
}
// 运行sql
run(sql, params) {
return new Promise((resolve, reject) => {
this.db.run(sql, params, err => {
if (err === null) {
resolve(err);
} else {
reject(err);
}
});
});
}
// 运行多条sql
exec(sql) {
return new Promise((resolve, reject) => {
this.db.exec(sql, err => {
if (err === null) {
resolve(err);
} else {
reject(err);
}
});
});
}
// 查询一条数据
get(sql, params) {
return new Promise((resolve, reject) => {
this.db.get(sql, params, (err, data) => {
if (err) {
reject(err);
} else {
resolve(data);
}
});
});
}
// 查询所有数据
all(sql, params) {
return new Promise((resolve, reject) => {
this.db.all(sql, params, (err, data) => {
if (err) {
reject(err);
} else {
resolve(data);
}
});
});
}
// 关闭数据库
close() {
this.db.close();
}
//-----------SQL的一些方法封装---------------
/**
* 建表
* @param tableName
* @param columns
*/
createTable(tableName, columns) {
const columnDefinitions = columns.map(column => `${column.name} ${column.type}`).join(', ');
const query = `CREATE TABLE IF NOT EXISTS ${tableName} (${columnDefinitions})`;
this.db.run(query);
}
/**
* 批量插入
* @param tableName
* @param data
*/
insertData(tableName, data) {
const columns = Object.keys(data).join(', ');
const placeholders = Object.keys(data)
.map(() => '?')
.join(', ');
const values = Object.values(data);
const query = `INSERT OR IGNORE INTO ${tableName} (${columns}) VALUES (${placeholders})`;
this.db.run(query, values);
}
updateData(tableName, data, condition) {
const setClause = Object.keys(data)
.map(column => `${column} = ?`)
.join(', ');
const values = Object.values(data);
const query = `UPDATE ${tableName} SET ${setClause} WHERE ${condition}`;
this.db.run(query, values);
}
deleteData(tableName, condition) {
const query = `DELETE FROM ${tableName} WHERE ${condition}`;
this.db.run(query);
}
/**
* 多参数查询
* @param params
* @returns
*/
executeQuery(params) {
// todo: 后续拓展为可精准查询，可模糊查询
const paramsStr = Object.keys(params)
.filter(item=> !!params[item])
.map(item=>{
return params[item]?`${item} LIKE '%${params[item]}%'`:''
})?.join(' AND ')
const sql = `SELECT * FROM address_book ${paramsStr?`WHERE ${paramsStr}`:''}`
console.log(sql)
return new Promise((resolve, reject) => {
this.db.all(sql, (err, rows) => {
if (err) {
console.error(err);
reject(err)
} else {
resolve(rows)
}
});
})
}
/**
* 获取分页数据
* @param tableName
* @param page
* @param pageSize
* @param condition // const condition = "column_name = 'value'";
* @returns {Promise<unknown>}
*/
getPagedData(tableName, page, pageSize, condition) {
const offset = (page - 1) * pageSize;
const countQuery = `SELECT COUNT(*) as total FROM ${tableName} WHERE ${condition}`;
const dataQuery = `SELECT * FROM ${tableName} WHERE ${condition} LIMIT ? OFFSET ?`;
return new Promise((resolve, reject) => {
this.db.serialize(() => {
this.db.get(countQuery, (err, row) => {
if (err) {
reject(err);
} else {
const total = row.total;
this.db.all(dataQuery, [pageSize, offset], (err, rows) => {
if (err) {
reject(err);
} else {
const totalPages = Math.ceil(total / pageSize);
resolve({ data: rows, total, totalPages });
}
});
}
});
});
});
}
// 单例
static getInstance() {
this.instance = this.instance ? this.instance : new Sqlite();
return this.instance;
}
}
export default Sqlite;
- 新建sqliteUse.ts,使用sqlite
import Sqlite from './sqlite'
import { ipcMain } from 'electron';
import { join } from 'path';
const db = Sqlite.getInstance()
export const init = async() =>{
// 这里前面还会有个生成.db文件的步骤，此处省略
const dbPath = join(__dirname, './database.db')
await db.connect(dbPath)
// 新建通讯录的表
const columns = [
{ name: 'id', type: 'INTEGER PRIMARY KEY AUTOINCREMENT' },
{ name: 'name', type: 'TEXT' },
{ name: 'mobile', type: 'INTEGER' },
{ name: 'phone', type: 'INTEGER' },
{ name: 'address', type: 'TEXT' },
{ name: 'company', type: 'TEXT' }
];
db.createTable('address_book', columns);
// 插入数据: 这个最终以服务器接口返回的为准，这里mock数据
const data = [
{
id:1,
name:'周杰伦',
mobile:'18556781234',
phone:'',
address:'湖南省 长沙市 望城区 罐子岭1号',
company:''
},
{
id:2,
name:'蔡依林',
mobile:'18556781236',
phone:'',
address:'湖南省 长沙市 望城区 罐子岭2号',
company:''
},
]
data.map(item=>{
db.insertData('address_book', item);
})
// 监听数据库事件
ipcMain.handle('sqlite-query',(event,params)=>{
console.log(params)
return new Promise<void>(async(resolve, reject) => {
const res = await db.executeQuery(params)
resolve(res)
})
})
}
//================= 使用案例 ==================================
// // 执行自定义SQL查询
// const query = 'SELECT * FROM users WHERE age > ?';
// const params = [30];
// const res = await db.executeQuery(query, params);
//
// // 创建表
// const columns = [
// { name: 'id', type: 'INTEGER PRIMARY KEY AUTOINCREMENT' },
// { name: 'name', type: 'TEXT' },
// { name: 'age', type: 'INTEGER' }
// ];
// db.createTable('users', columns);
//
// // 插入数据
// const data = { name: 'John Doe', age: 25 };
// db.insertData('users', data);
//
// // 更新数据
// const newData = { age: 30 };
// const condition = 'name = "John Doe"';
// db.updateData('users', newData, condition);
//
// // 删除数据
// const deleteCondition = 'age > 30';
// db.deleteData('users', deleteCondition);
//
// // 关闭数据库连接
// db.close();
//===================== 分页案例 ===============================
// const page = 1;
// const pageSize = 10;
// const condition = "column_name = 'value'";
// db.getPagedData("user", page, pageSize,condition)
// .then(({ data, total, totalPages }) => {
// console.log(data); // 处理分页查询结果
// console.log(total); // 总数
// console.log(totalPages); // 总页数
// })
// .catch(err => {
// console.error(err); // 处理错误
// })
// .finally(() => {
// db.close(); // 关闭数据库连接
// });
//}
- 搜索列表demo：vue3 + antd
<template>
<div class="list-container">
<a-form
layout="inline"
:model="formState"
@finish="handleFinish"
@finishFailed="handleFinishFailed"
>
<a-form-item label="姓名">
<a-input
v-model:value="formState.name"
placeholder="姓名"
>
</a-input>
</a-form-item>
<a-form-item label="手机号">
<a-input
v-model:value="formState.mobile"
placeholder="手机号"
>
</a-input>
</a-form-item>
<a-form-item>
<a-button
type="primary"
html-type="submit"
@click="queryData"
>
查询
</a-button>
</a-form-item>
</a-form>
<a-table
:dataSource="dbData"
:columns="columns"
/>
</div>
</template>
<script lang="ts">
import {defineComponent, ref,toRaw} from 'vue';
import {reactive} from 'vue';
import type {UnwrapRef} from 'vue';
import type {FormProps} from 'ant-design-vue';
export default defineComponent({
// components: {
// Icon,
// },
setup() {
const dbData = ref();
interface FormState {
name: string;
mobile: string;
}
const formState: UnwrapRef<FormState> = reactive({
name: '',
mobile: '',
});
const handleFinish: FormProps['onFinish'] = values => {
console.log(values, formState);
};
const handleFinishFailed: FormProps['onFinishFailed'] = errors => {
console.log(errors);
};
async function queryData() {
try {
const params = toRaw(formState)
dbData.value = await window.electron.ipcRenderer.invoke('sqlite-query', params);
console.log(dbData.value);
} catch (error) {
console.error(error);
}
}
return {
queryData,
dbData,
formState,
handleFinish,
handleFinishFailed,
columns: [
{
title: '姓名',
dataIndex: 'name',
key: 'name',
},
{
title: '手机号',
dataIndex: 'mobile',
key: 'mobile',
},
{
title: '住址',
dataIndex: 'address',
key: 'address',
},
],
};
},
});
</script>
效果预览
