2024最新版本 electron + vite + sqlite 开发收藏夹程序【完结】

2024最新版本 electron + vite + sqlite 开发收藏夹程序
2024最新版本 electron + vite + sqlite 开发收藏夹程序【续一】

上篇 2024最新版本 electron + vite + sqlite 开发收藏夹程序【续一】

数据库设计

SQLite的一些操作基本上跟 MySQL 差不多，在这个项目中只需要简单的两张表

表一 fav_list

收藏夹 ID	收藏类目 ID	标题	链接
fav_id	class_id	fav_title	fav_url

表二 fav_class 【不考虑多级类目】

收藏类目 ID	类目名称
class_id	class_name

electron/database.js

在electron目录下创建 database.js

这里偷懒了，直接 runSql 执行 SQL 语句，没有具体到 CRUD

const { app } = require("electron");
const sqlite3 = require("sqlite3");
const path = require("node:path");
const fse = require("fs-extra");
var dbPath;
var db = null;

if (app.isPackaged) {
  dbPath = path.resolve("./resources/storage/data.db");
} else {
  dbPath = path.join(__dirname, "./storage/data.db");
}
fse.ensureFileSync(dbPath);


const SQLiteInit = () => {
  db = new sqlite3.Database(dbPath, (err) => {
    if (err) throw err;
  });
};


const createTable = () => {
  return new Promise((resolve) => {    
    db.serialize(function() {
      db.run(`
      create table if not exists fav_class (
        class_id INTEGER PRIMARY KEY AUTOINCREMENT, 
        class_name text
      );
    `, (err, data) => {
      if (err) throw err;
      resolve(data);
    });
      db.serialize(function() {
        db.run(`
        create table if not exists fav_list (
          fav_id INTEGER PRIMARY KEY AUTOINCREMENT, 
          class_id INTEGER,
          fav_title text,
          fav_url text      
        );
      `, (err, data) => {
        if (err) throw err;
        resolve(data);
      });
     });
      
     });

  })

}
const runSql = (sql) => {
  return new Promise((resolve) => {
    db.all(sql, (err, data) => {
      if (err) throw err;
      resolve(data);
    });
  });
};

module.exports = {
  SQLiteInit,
  createTable,  
  runSql,
};

操作数据库

载入SQLite3并创建表

现在需要在主进程中载入数据库，渲染器进程中进行创建表的操作，我个人理解 electron/main.js 为主进程 ，src/pages/favroite/App.vue 为渲染器进程，所以对这两个文件进行修改

1. electron/main.js

const { app, BrowserWindow, ipcMain} = require('electron')
const path = require('node:path')
const isPackaged = app.isPackaged
const sq3 = require('./database')

process.env['ELECTRON_DISABLE_SECURITY_WARNINGS'] = 'true'

var mainWindow 

app.whenReady().then(() => {
    createMainWindow()  
    app.on("activate", () => {
      if (BrowserWindow.getAllWindows().length === 0) createMainWindow()
    })
  })

app.on('window-all-closed', () => {
  if (process.platform !== 'darwin') {
    app.quit()
  }
})

/**创建主窗口 */
const createMainWindow = () => {
  mainWindow = new BrowserWindow({
    frame:false,    
    fullscreenable:false,
    fullscreen: false,
    maximizable: false,
    shadow: true,
    hasShadow: true,
    resizable: false,
    width: 880,
    height: 500,
    webPreferences:{
      nodeIntegration:true,
      contextIsolation: true,     
      preload: path.join(__dirname, 'preload.js') 
    }
    
  })
  mainWindow.on('ready-to-show', () => {
    mainWindow.show()
  })

  if (!isPackaged){
    mainWindow.loadURL("http://localhost:5173/index/index.html");
  }else{
    mainWindow.loadFile(path.resolve(__dirname, '../build/index/index.html'))
  }

  sq3.SQLiteInit()

}

/**主窗口最小化 */
ipcMain.on('minWindow', (e, data) => {
  mainWindow.minimize()
})
/**关闭主窗口 */
ipcMain.on('closeWindow', (e, data) => {
  mainWindow.close()
})

/**创建普通子窗口 */
const createOtherWindow = (data) => {  
  otherWindow = new BrowserWindow({
      height: data.height || 640,
      width: data.width || 480,
      center: true,
      frame:false,
      fullscreen:false,
      fullscreenable: false,
      closable: true,
      resizable: false,
      maximizable: false,
      webPreferences: {
        nodeIntegration: true,
        webSecurity: false,
        webviewTag: true,
        enableRemoteModule: true,
        nodeIntegrationInWorker: true,      
        nodeIntegrationInSubFrames: true,
        preload: path.join(__dirname, 'preload.js')      
      }
    
  })
  otherWindow.on('ready-to-show', () => {
    otherWindow.show()
  })

  if (!isPackaged){
    otherWindow.loadURL("http://localhost:5173/" + data.name +"/index.html");
  }else{
    otherWindow.loadFile(path.resolve(__dirname, '../build/' + data.name + '/index.html'))
  }

}

/**建立普通子窗口 */
ipcMain.on('createOtherWindow', (e, data) => {
  createOtherWindow(data)
})
/**关闭普通子窗口 */
ipcMain.on('closeOtherWindow', () => {
  otherWindow.close()
})

/**创建数据表 */
ipcMain.on('createTable', () => {
  sq3.createTable()
})

/**执行Sql */
ipcMain.on('runSql', async (e, sql) => {
  let res = await sq3.runSql(sql)  
  e.returnValue = res
})

2. src/pages/favroite/App.vue

ipcRenderer.send(‘createTable’) ，通过ipc

<script setup>
import { ref, onMounted, onBeforeMount } from "vue";

import Aside from "./components/Aside.vue";
import Content from "./components/Content.vue";
import Header from "./components/Header.vue";

const ipcRenderer = window.electron.ipcRenderer;

const classList = ref([]);
const selectedId = ref(1);
const handleClassClick = (id) => {
  selectedId.value = id;
};

const getClassList = async () => {
  classList.value = await ipcRenderer.sendSync(
    "runSql",
    "select * from fav_class"
  ).data;
};

onBeforeMount(() => {
  ipcRenderer.sendSync("createTable");
  getClassList();
});
</script>
<template>
  <t-layout>
    <t-header>
      <Header></Header>
    </t-header>
    <t-layout>
      <t-aside>
        <Aside
          :classList="classList"
          :defaultValue="selectedId"
          @class-click="handleClassClick"
        ></Aside>
      </t-aside>
      <t-content>
        <Content :selectedId="selectedId" :classList="classList"></Content>
      </t-content>
    </t-layout>
  </t-layout>
</template>

添加必要组件

新建 src/pages/favroite/Header.vue

操作窗口关闭

<script setup>
const ipcRenderer = window.electron.ipcRenderer
const closeOtherWindow = () => {
    ipcRenderer.send('closeOtherWindow')
}
</script>
<template>    
    <t-row justify="space-between" align="middle" style="padding: var(--td-size-3);background:var(--td-brand-color-7);color:var(--td-font-white-1);cursor: pointer;">
        <t-col>我的收藏</t-col>
        <t-col title="关闭" @click="closeOtherWindow"><t-icon name="close" /></t-col>
        
    </t-row>
</template>

新建 src/pages/favroite/Aside.vue

显示收藏分类

<script setup>
import { ref } from "vue";
const props = defineProps({
  classList: Object,
});
const ipcRenderer = window.electron.ipcRenderer;
const emit = defineEmits(["class-click"]);

const handleClick = (id, index) => {
  isactive.value = index;
  emit("class-click", id);
};

const isactive = ref(0);

const form = ref(null);
const formData = ref({
  class_name: "",
});
const FORM_RULES = {
  class_name: [{ required: true, message: "标题必填" }],
};

const onSubmit = ({ validateResult, firstError }) => {
  if (validateResult === true) {
    ipcRenderer.sendSync(
      "runSql",
      `INSERT INTO fav_class (class_name) VALUES('${formData._value.class_name}')`
    );
  } else {
    MessagePlugin.warning(firstError);
  }
};

const deleteClass = (id) => {
  ipcRenderer.sendSync(
    "runSql",
    `delete from fav_class where class_id = ${id}`
  );
};
</script>

<template>
  <t-list :split="true">
    <t-list-item
      v-for="(item, index) in classList"
      :key="index"
      @click="handleClick(item.class_id, index)"
      :class="{ actived: index == isactive }"
    >
      {{ item.class_name }}
      <template #action>
        <t-popconfirm
          theme="default"
          content="是否删除"
          @confirm="deleteClass(item.class_id)"
        >
          <t-icon name="delete" />
        </t-popconfirm>
      </template>
    </t-list-item>

    <template #footer>
      <t-form
        ref="form"
        :rules="FORM_RULES"
        :data="formData"
        @submit="onSubmit"
        style="height: 120px"
      >
        <t-form-item
          label="分类名称"
          name="class_name"
          label-align="top"
          layout="inline"
        >
          <t-space>
            <t-input
              style="width: 120px"
              v-model="formData.class_name"
              placeholder="请输入分类名称"
            ></t-input>
            <t-button ghost theme="primary" type="submit" variant="dashed">
              <t-icon name="add"></t-icon>
            </t-button>
          </t-space>
        </t-form-item>
      </t-form>
    </template>
  </t-list>
</template>

<style scoped lang="less">
.actived {
  background-color: var(--td-gray-color-2);
  color: var(--td-brand-color) !important;
}

.t-list-item:hover {
  cursor: pointer;
  background: var(--td-gray-color-1);
  color: var(--td-font-gray-2);
}
</style>

src/pages/favroite/Content.vue

显示收藏夹列表

<script setup>
import { ref, onMounted, computed } from "vue";
import axios from "axios";
import cheerio, { load } from "cheerio";
const ipcRenderer = window.electron.ipcRenderer;

const props = defineProps(["selectedId", "classList"]);

const fav_list = ref();
const total = ref(0);
const loading = ref(false);

const insertData = async (data) => {
  await ipcRenderer.sendSync(
    "runSql",
    `INSERT INTO fav_list (fav_url, fav_title, class_id) VALUES('${data.fav_url}', '${data.fav_title}', '${data.class_id}')`
  );
  getData();
};

const updateData = async (data) => {
  let sql =
    "update favroite set fav_type = " +
    data.fav_type +
    " where fav_id = " +
    data.fav_id;
  await ipcRenderer.sendSync("runSql", sql);
  getData();
};

const deleteData = async (data) => {
  await ipcRenderer.send(
    "runSql",
    `delete from fav_list where fav_id = ${data.fav_id}`
  );
  getData();
};

const batchDelData = async (e) => {
  if (selectedRowKeys.value.join(",").length === 0) {
    return MessagePlugin.warning("未选择任何数据!");
  }
  await ipcRenderer.send(
    "runSql",
    `delete from fav_list where fav_id in ( ${selectedRowKeys.value.join(
      ","
    )} )`
  );
  getData();
};

const getData = async () => {
  loading.value = true;
  const result = await ipcRenderer.sendSync(
    "runSql",
    `select * from fav_list where class_id = ${props.selectedId}`
  ).data;
  total.value = result.length;
  fav_list.value = result;
  const timer = setTimeout(() => {
    loading.value = false;
    clearTimeout(timer);
  }, 1000);
};

const s = computed(async () => {
  loading.value = true;
  const result = await ipcRenderer.sendSync(
    "runSql",
    `select * from fav_list where class_id = ${props.selectedId}`
  ).data;
  total.value = result.length;
  fav_list.value = result;
  const timer = setTimeout(() => {
    loading.value = false;
    clearTimeout(timer);
  }, 1000);
});

const pagination = ref({
  defaultCurrent: 1,
  defaultPageSize: 10,
  pageSizeOptions: [],
  total: total,
});

const columns = [
  { colKey: "any", type: "multiple" },
  { colKey: "fav_title", title: "标题", width: "800", ellipsis: true },
  { colKey: "tools", title: "操作" },
];

const selectedRowKeys = ref([]);

const rehandleSelectChange = (value, ctx) => {
  selectedRowKeys.value = value;
};

const go = (link) => {
  window.electron.shell.openExternal(link);
};

const visible = ref(false);

const handleClick = () => {
  issave.value = false;
  visible.value = true;
  formData.value = ref([]);
  header.value = "新增收藏";
};
const handleClose = () => {
  visible.value = false;
};

const form = ref(null);

const formData = ref({
  fav_title: "",
  fav_url: "",
  class_id: "",
});

const FORM_RULES = {
  fav_title: [{ required: true, message: "标题必填" }],
  fav_url: [{ required: true, message: "网址必填" }],
  class_id: [{ required: true, message: "类型必选" }],
};

const onReset = () => {
  MessagePlugin.success("重置成功");
};

const getTitle = async () => {
  let url = formData._value.fav_url;
  loading.value = true;
  await axios
    .get(url)
    .then((res) => {
      let $ = cheerio.load(res.data);
      formData._value.fav_title = $("title").text();
      loading.value = false;
    })
    .catch((err) => {
      MessagePlugin.warning("获取标题失败，请检查网址是否正确");
      loading.value = false;
    });
};
const onSubmit = ({ validateResult, firstError }) => {
  if (validateResult === true) {
    if (issave.value) {
      updateData({
        fav_title: formData._value.fav_title,
        fav_url: formData._value.fav_url,
        fav_type: formData._value.fav_type,
        fav_id: formData._value.fav_id,
      });
    } else {
      insertData({
        fav_title: formData._value.fav_title,
        fav_url: formData._value.fav_url,
        class_id: formData._value.class_id,
      });
    }

    MessagePlugin.success("提交成功");
    visible.value = false;
  } else {
    console.log("Validate Errors: ", firstError, validateResult);
    MessagePlugin.warning(firstError);
  }
};

const onEnter = (_, { e }) => {
  e.preventDefault();
};

const issave = ref(false);
const edit = (data) => {
  issave.value = true;
  formData._value = data;

  visible.value = true;
  header.value = "编辑收藏";
};

const header = ref("new");

onMounted(() => {});
</script>
<template>
  <t-layout class="custom">
    <t-content
      style="background: #fff; -webkit-app-region: no-drag; padding: 20px"
    >
      <t-space>
        <t-button theme="primary" @click="handleClick">
          <template #icon>
            <t-icon name="bookmark-add"></t-icon>
          </template>
          新增收藏
        </t-button>
        <t-button theme="danger" variant="base" @click="batchDelData">
          <template #icon>
            <t-icon name="delete"></t-icon>
          </template>
          批量删除
        </t-button>
      </t-space>
      <div class="mytable">
        <t-table
          v-if="s"
          :data="fav_list"
          :columns="columns"
          row-key="fav_id"
          size="small"
          :hover="true"
          :bordered="false"
          :pagination="pagination"
          @select-change="rehandleSelectChange"
          :loading="loading"
          :selected-row-keys="selectedRowKeys"
          :select-on-row-click="true"
          :resizable="false"
          lazy-load
        >
          <template #fav_title="{ row }">
            <t-row justify="space-between">
              <t-col>{{ row.fav_title }}</t-col>

              <t-col
                ><t-space>
                  <t-icon
                    class="iconbox"
                    name="edit"
                    @click="
                      edit({
                        fav_id: `${row.fav_id}`,
                        fav_url: `${row.fav_url}`,
                        fav_title: `${row.fav_title}`,
                        fav_type: `${row.fav_type}`,
                      })
                    "
                  ></t-icon>
                </t-space>
              </t-col>
            </t-row>
          </template>
          <template #tools="{ row }">
            <t-space>
              <t-button theme="success" @click="go(row.fav_url)" variant="text">
                <t-icon name="link" />
              </t-button>

              <t-popconfirm
                theme="danger"
                placement="right-bottom"
                content="是否删除"
                @confirm="deleteData(row)"
              >
                <t-button theme="danger" variant="text">
                  <t-icon name="delete" />
                </t-button>
              </t-popconfirm>
            </t-space>
          </template>
        </t-table>
      </div>
    </t-content>

    <t-drawer
      v-model:visible="visible"
      :footer="false"
      :header="header"
      placement="top"
      :on-confirm="handleClose"
      @close="handleClose"
    >
      <t-space direction="vertical" size="large" style="width: 100%">
        <t-form
          ref="form"
          :rules="FORM_RULES"
          :data="formData"
          :colon="true"
          @reset="onReset"
          @submit="onSubmit"
        >
          <t-form-item label="标题" name="fav_title">
            <t-input
              v-model="formData.fav_title"
              placeholder="请输入内容"
              @enter="onEnter"
            ></t-input>
          </t-form-item>

          <t-form-item label="网址" name="fav_url">
            <t-input
              v-model="formData.fav_url"
              placeholder="请输入内容"
              @enter="onEnter"
            ></t-input>
          </t-form-item>
          <t-form-item label="类型" name="fav_type">
            <t-select v-model="formData.class_id">
              <t-option
                v-for="(item, index) in classList"
                :key="index"
                :value="item.class_id"
                :label="item.class_name"
                >{{ item.class_name }}</t-option
              >
            </t-select>
          </t-form-item>
          <t-form-item>
            <t-space size="small">
              <t-button theme="primary" type="submit" :disabled="loading"
                >提交</t-button
              >
              <t-button theme="success" @click="getTitle" :loading="loading"
                >获取标题</t-button
              >
              <t-button theme="default" variant="base" type="reset"
                >重置</t-button
              >
            </t-space>
          </t-form-item>
        </t-form>
      </t-space>
    </t-drawer>
  </t-layout>
</template>

<style lang="less" scoped>
.mytable {
  margin-top: 10px;
}

.iconbox {
  display: none;
  cursor: pointer;
}

.t-table-td--ellipsis:hover {
  .iconbox {
    display: inline;
  }
}

.custom {
  height: 700px;
  overflow: auto;
  &::-webkit-scrollbar {
    width: 6px;
    height: 6px;
  }
  &::-webkit-scrollbar-button {
    display: none;
  }
  &::-webkit-scrollbar-track {
    background: transparent;
  }
  &::-webkit-scrollbar-track-piece {
    background-color: transparent;
  }
  &::-webkit-scrollbar-thumb {
    background: rgba(144, 147, 153, 0.3);
    cursor: pointer;
    border-radius: 4px;
  }
  &::-webkit-scrollbar-corner {
    display: none;
  }
  &::-webkit-resizer {
    display: none;
  }
}
</style>

所有功能已经写完，vscode 终端执行 yarn run electron:dev 看最终效果

写在最后

收藏功能虽然能用，但依然有几个地方有疑问，我目前没有办法和时间去解决，有大佬可以帮看下不？

1. 新建分类和删除分类后，不会即时刷新分类列表

2. 多个组件里面 const ipcRenderer = window.electron.ipcRenderer ，这个能不能写在一个页面里多个组件调用

水平有限，写的很烂，希望能帮助到有需要的朋友

2024.1.8 14:14