flutter-sqflite

sqflite: ^1.3.1+1
工具类

import 'dart:math';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:flutter_zxs/base/base_import.dart';
class DbUtil {
  static Database db;
  static String tableName = 'user';

  // 获取数据库中所有的表
  Future<List> getTables() async {
    if (db == null) {
      return Future.value([]);
    }
    List tables = await db
        .rawQuery('SELECT name FROM sqlite_master WHERE type = "table"');
    List<String> targetList = [];
    tables.forEach((item) {
      targetList.add(item['name']);
    });
    return targetList;
  }
  // 检查数据库中, 表是否完整, 在部份android中, 会出现表丢失的情况
  Future checkTableIsRight() async {
    List<String> expectTables = ['user','tb_history']; //将项目中使用的表的表名添加集合中

    List<String> tables = await getTables();

    for (int i = 0; i < expectTables.length; i++) {
      if (!tables.contains(expectTables[i])) {
        return false;
      }
    }
    return true;
  }
  //初始化数据库
  Future init() async {
    //Get a location using getDatabasesPath
    String databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'data.db');
    print(path);
    try {
      db = await openDatabase(path);
    } catch (e) {
      print("error $e");
    }

    bool tableIsRight = await this.checkTableIsRight();

    if (!tableIsRight) {
      // 关闭上面打开的db，否则无法执行open
      db.close();
      //表不完整
      // Delete the database
      await deleteDatabase(path);

      db = await openDatabase(path, version: 1,
          onCreate: (Database db, int version) async {
            // When creating the db, create the table
            await db.execute(SqlTable.sql_createTable_user);
            await db.execute(SqlTable.sql_createTable_order);
            await db.execute(SqlTable.sql_createTable_history);
            print('db created version is $version');
          }, onOpen: (Database db) async {
            print('new db opened');
          });
    } else {
      print("Opening existing database");
    }
  }
  static Future<int> insertUser(User todo) async {
    int value = await db.insert(tableName, todo.toMap());
    return value;
  }
  static Future<User> getuser(String phone,String pwd) async {
    List<Map> maps = await db.query(tableName,
        where: 'phone = ? and password = ?',
        whereArgs: [phone,pwd]);
    if (maps.length > 0) {
      return User.fromMap(maps.first);
    }
    return null;
  }
  static Future<int> getUserCount() async{
    List<Map> maps = await db.query(tableName);
    return maps.length;
  }
  static Future<int> updateUser(User todo) async {
    return await db.update(tableName, todo.toMap(),
        where: 'id = ?', whereArgs: [todo.tid]);
  }
  static User newUser(String phone,String pwd){
      var user = User();
      user.phone = phone;
      user.pwd = pwd;
      user.uid = randomBit(6);
      user.username = '会员${user.uid}';
      return user;
  }
  static Future<int> changeUserName(String name,int id) async{
    List<Map> maps = await db.query(tableName,
        where: 'id = ?',
        whereArgs: [id]);
    User user;
    if (maps.length > 0) {
      user = User.fromMap(maps.first);
    }else{
      CommUtil.toast('没有找到此用户');
      return -1;
    }
    user.username = name;
    return await db.update(tableName, user.toMap(),
        where: 'id = ?', whereArgs: [user.tid]);
  }
  static Future<int> changeUserPhone(String phone,int id) async{
    List<Map> maps = await db.query(tableName,
        where: 'id = ?',
        whereArgs: [id]);
    User user;
    if (maps.length > 0) {
      user = User.fromMap(maps.first);
    }else{
      CommUtil.toast('没有找到此用户');
      return -1;
    }
    user.phone = phone;
    return await db.update(tableName, user.toMap(),
        where: 'id = ?', whereArgs: [user.tid]);
  }
  static Future<String> verification(String phone,String password) async{
    List<Map> maps = await db.query(tableName,
        where: 'phone = ? and password = ?',
        whereArgs: [phone,password]);
    if (maps.length > 0) {
      var user = User.fromMap(maps.first);
      return 'succ,${user.tid}';
    }else{
      return 'error,-1';
    }
  }
  static Future<User> getUser(int id) async{
    List<Map> maps = await db.query(tableName,
        where: 'id = ?',
        whereArgs: [id]);
    if (maps.length > 0) {
      var user = User.fromMap(maps.first);
      return user;
    }else{
      return null;
    }

  }
  static Future<int> orderInsert(Order order) async{
    int value = await db.insert('tb_order', order.toMap());
    return value;
  }
  static Future<bool> orderverifty (String orderId,String type) async{
    List<Map> maps = await db.query('tb_order',
        where: 'orderId = ? and type = ?',
        whereArgs: [orderId,type]);
    if (maps.length > 0) {
      return true;
    }else{
      return false;
    }
  }
  static randomBit(int len) {
    String scopeF = '123456789';//首位
    String scopeC = '0123456789';//中间
    String result = '';
    for (int i = 0; i < len; i++) {
      if (i == 1) {
        result = scopeF[Random().nextInt(scopeF.length)];
      } else {
        result = result + scopeC[Random().nextInt(scopeC.length)];
      }
    }
    return result;
  }


  ///插入数据
  static Future<void> insertData(History data) async {
    isHaveText(data)
        .then((value){
      if(value != null){
        delete(value)
            .then((ss){
          insert(data);
        });
      }else{
        insert(data);
      }
    });
  }

  ///删除
  static Future<int> delete(History data) async{
    return await db.delete('tb_history',where: 'id = ?' ,whereArgs: [data.id]);
  }


  ///插入
  static Future<void> insert(History data) async {
    return await db.insert('tb_history', data.toMap());
  }
  ///查询所有
  static Future<List<History>> selectAll() async{
    List<Map> maps = await db.query('tb_history',orderBy: 'id desc',limit: 20);
    List<History> hs = List();
    for(Map map in maps.toList()){
      History tt = History.fromMap(map);
      if (tt.text.length > 6){
        tt.text = '${tt.text.substring(0,5)}..';
      }
      hs.add(tt);
    }
    return hs;
  }
  ///修改
  static Future<void> update(History data) async {
    return await db.update('tb_history', data.toMap() ,where: 'id = ?' ,whereArgs: [data.id]);
  }

  ///是否存在这个元素
  static Future<History> isHaveText(History data) async {
    List<Map> maps = await db.query('tb_history',where: 'text = ?' ,whereArgs: [data.text]);
    if(maps.length > 0){
      return History.fromMap(maps.first);
    }else{
      return null;
    }
  }

  ///是否存在这个元素
  static Future<History> isHaveId(int id) async {
    List<Map> maps = await db.query('tb_history',where: 'id = ?' ,whereArgs: [id]);
    if(maps.length > 0){
      return History.fromMap(maps.first);
    }else{
      return null;
    }
  }

  ///获取集合长度
  static Future<int> getCount()async{
    List<Map> maps = await db.query('tb_history');
    return maps.length;
  }


  Future close() async => db.close();


}

class SqlTable{
  static final String sql_createTable_order = """
    CREATE TABLE tb_order (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 
    orderId Text NOT NULL, 
    type TEXT NOT NULL);
    """;
  static final String sql_createTable_user = """
    CREATE TABLE user (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 
    uid INTEGER NOT NULL UNIQUE, 
    phone TEXT NOT NULL UNIQUE, 
    username TEXT NOT NULL,
    heart TEXT,
    password TEXT);
    """;

  static final String sql_createTable_history = """
    CREATE TABLE tb_history (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 
    text Text NOT NULL UNIQUE);
    """;
}

bean类

class User{
  int tid;
  String uid;
  String username;
  String phone;
  String pwd;
  String hh;
  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      'uid': uid,
      'username': username,
      'phone': phone,
      'password': pwd,
      'heart':hh,
    };
    if (tid != null) {
      map['id'] = tid;
    }
    return map;
  }
  User();
  User.fromMap(Map<String, dynamic> map) {
    tid = map['id'];
    uid = map['uid'].toString();
    username = map['username'];
    phone = map['phone'].toString();
    pwd = map['password'];
    hh = map['heart'].toString();
  }
}

class Order{
  int id;
  String oid;
  String type;
  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      'orderId': oid,
      'type': type,
    };
    if (id != null) {
      map['id'] = id;
    }
    return map;
  }
  Order();
  Order.fromMap(Map<String, dynamic> map) {
    id = map[id];
    oid = map['orderId'].toString();
    type = map['type'].toString();
  }
}

class History{
  int id;
  String text;
  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      'text': text,
    };
    if (id != null) {
      map['id'] = id;
    }
    return map;
  }
  History(this.text);
  History.fromMap(Map<String, dynamic> map) {
    id = map['id'];
    text = map['text'].toString();

  }
}