uniapp sqlite方法封装

var dbName = 'xxx' // 数据库名称
var dbPath = '_doc/xxx.db' // 数据库地址,推荐以下划线为开头   _doc/xxx.db

// 判断数据库是否打开
const isOpen = (plus: any) => {
  // 数据库打开了就返回 true,否则返回 false
  var open = plus.sqlite.isOpenDatabase({
    name: dbName, // 数据库名称
    path: dbPath, // 数据库地址
  })
  return open
}

// 创建数据库 或 有该数据库就打开
const openSqlite = (plus: any) => {
  return new Promise((resolve, reject) => {
    // 打开数据库
    plus.sqlite.openDatabase({
      name: dbName,
      path: dbPath,
      success(e) {
        resolve(e) // 成功回调
      },
      fail(e) {
        reject(e) // 失败回调
      },
    })
  })
}
// 关闭数据库
const closeSqlite = (plus: any) => {
  return new Promise((resolve, reject) => {
    plus.sqlite.closeDatabase({
      name: dbName,
      success(e) {
        resolve(e)
      },
      fail(e) {
        reject(e)
      },
    })
  })
}

///原生的sql操作
const SqlExecute = (sql: string, plus: any) => {
  return SqlSelect(sql, plus)
}
//执行原生的select语句
const SqlSelect = (sql: string, plus: any) => {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: dbName,
      sql,
      success(e) {
        resolve(e)
      },
      fail(e) {
        reject(e)
      },
    })
  })
}
//创建表字段
const JsCreateTableType = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  if (dbTable) {
    data = Object.entries(data)
      .map((item: any) => {
        var typeName = ''
        switch (item[1].constructor) {
          case Number:
            if (Math.floor(item[1]) == item[1]) {
              typeName = 'INT'
            } else {
              typeName = 'DOUBLE'
            }
            break
          case String:
            typeName = 'TEXT'
            break
          case Boolean:
            typeName = 'BOOLEAN'
            break
          case Date:
            typeName = 'DATETIME'
            break
        }
        return item[0] + ' ' + typeName
      })
      .join(',')

    sql = `CREATE TABLE IF NOT EXISTS ${dbTable}("id" INTEGER PRIMARY KEY AUTOINCREMENT,${data})`
    return SqlExecute(sql, plus)
  }
}

//新建
const JsInsertTableData = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  if (data) {
    var condition = <any>[]
    var sqlValue = <any>[]
    Object.entries(data).forEach((item) => {
      condition.push(`'${item[0]}'`)
      if (item[1] != undefined) {
        if (typeof item[1] == 'string') {
          sqlValue.push(`'${item[1]}'`)
        } else {
          sqlValue.push(item[1])
        }
      }
    })
    condition = condition.join(',')
    sqlValue = sqlValue.join(',')
    sql = `INSERT INTO ${dbTable} (${condition}) VALUES(${sqlValue})`
    return SqlExecute(sql, plus)
  }
}
//查询
const JsSelectTableData = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  var condition = <any>[]
  if (data) {
    Object.entries(data).forEach((item) => {
      if (item[1] != undefined) {
        if (typeof item[1] == 'string') {
          condition.push(`${item[0]} = '${item[1]}'`)
        } else {
          condition.push(`${item[0]} = ${item[1]}`)
        }
      }
    })
    condition = condition.join(' AND ')
    sql = `SELECT * FROM ${dbTable} WHERE ${condition}`
  } else {
    sql = `SELECT * FROM ${dbTable}`
  }
  return SqlSelect(sql, plus)
}
//编辑
const JsUpdate = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  var condition = <any>[]
  if (data) {
    Object.entries(data).forEach((item) => {
      if (item[1] != undefined && item[0] != 'id') {
        if (typeof item[1] == 'string') {
          condition.push(`${item[0]} = '${item[1]}'`)
        } else {
          condition.push(`${item[0]} = ${item[1]}`)
        }
      }
    })
    condition = condition.join(',')
    sql = `UPDATE ${dbTable} SET ${condition} where id = ${data.id}`
    return SqlExecute(sql, plus)
  }
}
// 删除
const JsDeleteTableData = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  if (data) {
    var condition = <any>[]
    Object.entries(data).forEach((item) => {
      if (item[1] != undefined) {
        if (typeof item[1] == 'string') {
          condition.push(`${item[0]} = '${item[1]}'`)
        } else {
          condition.push(`${item[0]} = ${item[1]}`)
        }
      }
    })
    condition = condition.join(' AND ')
    sql = `DELETE FROM ${dbTable} WHERE ${condition}`
    return SqlExecute(sql, plus)
  }
}

export default {
  isOpen,
  openSqlite,
  closeSqlite,
  JsCreateTableType,
  JsInsertTableData,
  JsSelectTableData,
  JsUpdate,
  JsDeleteTableData,
}

var dbName = 'leadinno' // 数据库名称
var dbPath = '_doc/leadinno.db' // 数据库地址,推荐以下划线为开头   _doc/xxx.db

// 判断数据库是否打开
const isOpen = (plus: any) => {
  // 数据库打开了就返回 true,否则返回 false
  var open = plus.sqlite.isOpenDatabase({
    name: dbName, // 数据库名称
    path: dbPath, // 数据库地址
  })
  return open
}

// 创建数据库 或 有该数据库就打开
const openSqlite = (plus: any) => {
  return new Promise((resolve, reject) => {
    // 打开数据库
    plus.sqlite.openDatabase({
      name: dbName,
      path: dbPath,
      success(e) {
        resolve(e) // 成功回调
      },
      fail(e) {
        reject(e) // 失败回调
      },
    })
  })
}
// 关闭数据库
const closeSqlite = (plus: any) => {
  return new Promise((resolve, reject) => {
    plus.sqlite.closeDatabase({
      name: dbName,
      success(e) {
        resolve(e)
      },
      fail(e) {
        reject(e)
      },
    })
  })
}

///原生的sql操作
const SqlExecute = (sql: string, plus: any) => {
  // return new Promise((resolve, reject) => {
  //   plus.sqlite.executeSql({
  //     name: dbName,
  //     sql,
  //     success(e) {
  //       resolve(e)
  //     },
  //     fail(e) {
  //       reject(e)
  //     },
  //   })
  // })
  return SqlSelect(sql, plus)
}
//执行原生的select语句
const SqlSelect = (sql: string, plus: any) => {
  return new Promise((resolve, reject) => {
    plus.sqlite.selectSql({
      name: dbName,
      sql,
      success(e) {
        resolve(e)
      },
      fail(e) {
        reject(e)
      },
    })
  })
}
//通过对象创建数据库,使用对象的数据类型
const JsCreateTableType = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  if (dbTable) {
    data = Object.entries(data)
      .map((item: any) => {
        var typeName = ''
        switch (item[1].constructor) {
          case Number:
            if (Math.floor(item[1]) == item[1]) {
              typeName = 'INT'
            } else {
              typeName = 'DOUBLE'
            }
            break
          case String:
            typeName = 'TEXT'
            break
          case Boolean:
            typeName = 'BOOLEAN'
            break
          case Date:
            typeName = 'DATETIME'
            break
        }
        return item[0] + ' ' + typeName
      })
      .join(',')

    sql = `CREATE TABLE IF NOT EXISTS ${dbTable}("id" INTEGER PRIMARY KEY AUTOINCREMENT,${data})`
    return SqlExecute(sql, plus)
  }
}

//通过对象插入数据
const JsInsertTableData = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  if (data) {
    var condition = <any>[]
    var sqlValue = <any>[]
    Object.entries(data).forEach((item) => {
      condition.push(`'${item[0]}'`)
      if (item[1] != undefined) {
        if (typeof item[1] == 'string') {
          sqlValue.push(`'${item[1]}'`)
        } else {
          sqlValue.push(item[1])
        }
      }
    })
    condition = condition.join(',')
    sqlValue = sqlValue.join(',')
    sql = `INSERT INTO ${dbTable} (${condition}) VALUES(${sqlValue})`
    return SqlExecute(sql, plus)
  }
}
//通过对象选择数据
const JsSelectTableData = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  var condition = <any>[]
  if (data) {
    Object.entries(data).forEach((item) => {
      if (item[1] != undefined) {
        if (typeof item[1] == 'string') {
          condition.push(`${item[0]} = '${item[1]}'`)
        } else {
          condition.push(`${item[0]} = ${item[1]}`)
        }
      }
    })
    condition = condition.join(' AND ')
    sql = `SELECT * FROM ${dbTable} WHERE ${condition}`
  } else {
    sql = `SELECT * FROM ${dbTable}`
  }
  return SqlSelect(sql, plus)
}
//通过对象获取
const JsUpdate = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  var condition = <any>[]
  if (data) {
    Object.entries(data).forEach((item) => {
      if (item[1] != undefined && item[0] != 'id') {
        if (typeof item[1] == 'string') {
          condition.push(`${item[0]} = '${item[1]}'`)
        } else {
          condition.push(`${item[0]} = ${item[1]}`)
        }
      }
    })
    condition = condition.join(',')
    sql = `UPDATE ${dbTable} SET ${condition} where id = ${data.id}`
    return SqlExecute(sql, plus)
  }
}
// 删除表里的数据
const JsDeleteTableData = (dbTable: string, data: any, plus: any) => {
  var sql = ''
  if (data) {
    var condition = <any>[]
    Object.entries(data).forEach((item) => {
      if (item[1] != undefined) {
        if (typeof item[1] == 'string') {
          condition.push(`${item[0]} = '${item[1]}'`)
        } else {
          condition.push(`${item[0]} = ${item[1]}`)
        }
      }
    })
    condition = condition.join(' AND ')
    sql = `DELETE FROM ${dbTable} WHERE ${condition}`
    return SqlExecute(sql, plus)
  }
}

export default {
  isOpen,
  openSqlite,
  closeSqlite,
  JsCreateTableType,
  JsInsertTableData,
  JsSelectTableData,
  JsUpdate,
  JsDeleteTableData,
}

posted @ 2024-02-02 12:13 糯宝宝的爸爸阅读(697) 评论(0) 收藏举报

刷新页面返回顶部

糯宝宝的爸爸

uniapp sqlite方法封装

公告