uniapp-sample-scan/pages/collections/test.vue

<template>
    <view>


        默认打开数据库，并创建表
        <button type="primary" @click="openSqlite">打开数据库</button>
        <button type="primary" @click="createTable">创建表</button>

        <button type="primary" @click="closedb">关闭数据库</button>



        <button type="primary" @click="addProp">给表加字段</button>

        <button type="primary" @click="addData">添加数据</button>
        <button type="primary" @click="addListData">批量添加数据</button>
        <button type="primary" @click="changeListData">批量修改数据</button>


        <button type="primary" @click="getData">查询第一页数据</button>

        <button type="primary" @click="changeData">修改最后一条数据</button>

        <button type="primary" @click="clearData">清空数据</button>
        <button type="primary" @click="deleteTable">删除数据表</button>



        <view>总页数：{{pages}}   总数据条数：{{total}}</view>
        <view></view>
        <view v-for="(item,index) in list" :key="index">
            <span>fid:{{item.fid}}，{{item.title}}，</span>
            <span>{{item.content}}，</span>
            <span>{{item.desc}}</span>
        </view>




    </view>
</template>
<script>
    import { openSqlite,executeSql,closedb,getTable,isTable,getAllField,insertAll,addSql,getPageList,
       selectList,deleteSql,updateSql,selectSql,batchUpdate} from "@/utils/database";
    export default {
        data(){
            return {
                tableName:"ord_storage_order",
                options:{
                    current:1,  // 分页默认为第1页
                    size:10,
                },
                list:[],
                pages:0,
                total:0,
            }
        },
        onUnload(){
            // 关闭数据库
            this.closedb()
        },
        onLoad(){
			const dbPath = plus.io.convertLocalFileSystemURL('_doc/fr_storage.db');
			console.log(dbPath); // 输出类似"/storage/emulated/0/Android/data/com.example.myapp/files/database/test.db"的路径

		

        },
        async onShow(){
            /**
             * 数据库设置名称 在 database.js 文件中
             */
            // console.log("所有表名称",await getTable())
            // 默认打开数据库,并创建表
            await this.openSqlite()
            await this.createTable()
        },
        methods: {
            async deleteTable(){
                try{
                    await executeSql("drop table ord_storage_order")
                    uni.showToast({
                        title:"删除数据表成功",
                        icon:"none"
                    })
                }catch(e){
                    uni.showToast({
                        title:"删除数据表失败",
                        icon:"none"
                    })
                    console.error("删除数据表失败ord_storage_order",e)
                }

            },
            // 创建表
            async createTable(){
                let sql = this.createTableSql_outbound()
                try{
                    let exist = await isTable(this.tableName)
                    console.log("表是否存在",exist)
                    if(!exist){
                        let res = await executeSql(sql)
                        uni.showToast({
                            title:"新增数据表成功",
                            icon:"none"
                        })
                        console.log("新增表ord_storage_order",res)
                    }else{
                        // uni.showToast({
                        //     title:"数据表已存在",
                        //     icon:"none"
                        // })
                    }
                }catch(e){
                    uni.showToast({
                        title:"新增数据表失败",
                        icon:"none"
                    })
                    console.error("新增表报错ord_storage_order",e)
                }
            },
            // 更新表字段
            async addProp(){
                // 更新app时，可以增加字段
                try{
                    let res = await executeSql(`ALTER TABLE ord_storage_order ADD desc varchar(500) DEFAULT '1'`)
                    console.log("增加字段",res)
                    uni.showToast({
                        title:"新增字段成功",
                        icon:"none"
                    })
                }catch(e){
                    uni.showToast({
                        title:"字段已存在，或者未打开数据库，或者数据表不存在",
                        icon:"none"
                    })
                }
            },
            // 关闭数据库
            closedb(){
                try{
                    closedb()
                }catch(e){
                    console.error("关闭数据库，报错",e)
                }

            },
            async openSqlite(){
                // 打开数据库
                try{
                    let b = await openSqlite()
                    uni.showToast({
                        title:"打开数据库成功",
                        icon:"none"
                    })
                }catch(e){
                    console.error("打开数据库，报错",e)
                }
            },
            // 添加数据
            async addData(){
                try{
                    let b = await addSql(this.tableName,{title:"你好，世界！",content:"世界：你好呀!",desc:"嘻嘻"})
                    uni.showToast({
                        title:"添加成功",
                        icon:"none"
                    });
                }catch(e){
                    console.error("添加数据，报错",e)
                }

            },
            // 批量插入数据
            async addListData(){
                try{
                    let list = []
                    for(let i=0;i<3;i++){
                        list.push({title:"你好，世界！",content:"世界：你好呀!",desc:"嘻嘻"})
                    }
                    list.push({title:"你好，世界！"})
                    list.push({title:"你好，世界！",content:"世界：你好呀!",s:1})
                    await insertAll(this.tableName,list)
                    uni.showToast({
                        title:"添加成功",
                        icon:"none"
                    });
                }catch(e){
                    console.error("批量添加数据，报错",e)
                }
            },
            // 批量修改数据
            async changeListData(){

                // 查询最后10条数据，并修改
                let res = await getPageList(this.tableName,{current:1,size:10},{},"fid desc")
                let list = res.data.data.records
                for(let i in list){
                    list[i].title = "批量修改"
                }
                console.log("list",list)

                try{

                    await batchUpdate(this.tableName,list,"fid")
                    // uni.showToast({
                    //     title:"修改成功",
                    //     icon:"none"
                    // });
                }catch(e){
                    console.error("批量修改数据，报错",e)
                }
            },
            // 获取数据
            async getData(){
                try{
                    let where = {}
                    let res = await getPageList(this.tableName,this.options,where,"fid desc")
                    console.log("加载数据",res)
                    this.list = res.data.data.records
                    this.pages = res.data.data.pages
                    this.total = res.data.data.total
                }catch(e){
                    uni.showToast({
                        title:"报错，请查看控制台",
                        icon:"none"
                    });
                    console.error("报错",e)
                }
            },
            // 修改数据
            async changeData(){
                // fid 是自增id
                try{
                    let res = await getPageList(this.tableName,{current:1,size:1},{},"fid desc")
                    console.log("最后一页数据",res.data.data)
                    await updateSql(this.tableName,{title:"修改了数据"},{fid:res.data.data.records[0].fid})
                    uni.showToast({
                        title:"修改成功",
                        icon:"none"
                    });
                }catch(e){
                    uni.showToast({
                        title:"修改报错，请查看控制台",
                        icon:"none"
                    });
                    console.error("修改报错",e)
                }
            },
            async clearData(){
                try{
                    await executeSql("DELETE FROM ord_storage_order")
                    uni.showToast({
                        title:"清除数据成功",
                        icon:"none"
                    });
                }catch(e){
                    uni.showToast({
                        title:"清除数据报错，请查看控制台",
                        icon:"none"
                    });
                    console.error("清除数据报错",e)
                }
            },
            /**
             * 创建表， 仅供参考
             * @returns {string}
             * 因为java后台用的id，所以这里用fid 作为自增id
             */

            createTableSql_outbound(){
                return "CREATE TABLE IF NOT EXISTS `ord_storage_order` (" +
                    "  `id`  int(11) DEFAULT '' ," +
                    "  `fid` INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "  `title` varchar(50) DEFAULT NULL ," +
                    "  `content` varchar(50) DEFAULT NULL ," +
                    "  `updateId` int(11) DEFAULT NULL ," +
                    "  `updateName` varchar(50) DEFAULT NULL ," +
                    "  `checkStatus` int(11) DEFAULT '0' ," +
                    "  `status` int(11) DEFAULT '0' ," +
                    "  `createTime` datetime DEFAULT CURRENT_TIMESTAMP ," +
                    "  `updateTime` datetime DEFAULT NULL default(datetime('now','localtime')) ," +
                    "  `deleted` char(1) DEFAULT '0' ," +
                    "  `tenantId` int(11) NOT NULL DEFAULT '0' ," +
                    "  `handleOption` varchar(500) DEFAULT NULL" +
                    "); "
            },
        }
    }
</script>
<style lang='scss' scoped>
    button{
        margin-bottom:5px;
    }
</style>