Flutter使用数据库

1.这里使用sqflite，android和ios通用,导入依赖：sqflite: ^2.0.0+3
2.建立数据库db，并获取db对象。
表结构如下：

//1.0 version 没有author，后面2.0 version更新表结构添加author
  static const String TABLE_BOOK = 'book';
  static const String BOOK_COLUMN_ID = 'id';
  static const String BOOK_COLUMN_NAME = 'name';
  static const String BOOK_COLUMN_AUTHOR = 'author';
  static const String BOOK_COLUMN_PRICE = 'price';
  static const String BOOK_COLUMN_PUBLISHING = 'publishing';
  static const CREATE_BOOK = '''
        CREATE TABLE $TABLE_BOOK(
        $BOOK_COLUMN_ID integer primary key,
        $BOOK_COLUMN_NAME text,
        $BOOK_COLUMN_PRICE real,
        $BOOK_COLUMN_PUBLISHING text
        )
                     ''';
}

   //dbpath = data/user/0/包名/databases
      var dbpath = await getDatabasesPath();
      //要使用join连接函数，要导入import 'package:path/path.dart';
      String dbname = join(dbpath+'/demo.db');
        db = await openDatabase(dbname,version: 2,onCreate: (Database db,int version)async{
            await db.execute(Constant.CREATE_BOOK);
      },onUpgrade: (Database db,int oldVersion,int newVersion) async {
          print('oldversion:${oldVersion},newVersion:${newVersion}');
            if(oldVersion<2){
              var batch = db.batch();
              var upgradeSql = '''alter table ${Constant.TABLE_BOOK} add column ${Constant.BOOK_COLUMN_AUTHOR} text''';
              batch.execute(upgradeSql);
              await batch.commit();
            }
        });

onUpgrade回调是更新表结构时使用的，只有数据库版本号不一致才会调用此回调。

2.CRUD
Book bean类如下

class Book{
  int id;
  String name;
  String author;
  double price;
  String publishing;

  Book({this.id, this.name, this.author, this.price, this.publishing});

  Map<String,dynamic> toMap(){
    var map = <String,dynamic>{
      Constant.BOOK_COLUMN_NAME:name,
      Constant.BOOK_COLUMN_PRICE:price,
      Constant.BOOK_COLUMN_AUTHOR:author,
      Constant.BOOK_COLUMN_PUBLISHING:publishing
    };
    if(id!=null){
      map[Constant.BOOK_COLUMN_ID] = id;
    }
    return map;
  }
  Book.fromMap(Map<String,dynamic>map){
    var tmpid = map[Constant.BOOK_COLUMN_ID];
    if(tmpid!=null){
      id = tmpid;
    }
    var tmpname = map[Constant.BOOK_COLUMN_NAME];
    if(tmpname!=null){
      name = tmpname;
    }
    var tmpauthor = map[Constant.BOOK_COLUMN_AUTHOR];
    if(tmpauthor!=null){
      author = tmpauthor;
    }
    var tmpprice = map[Constant.BOOK_COLUMN_PRICE];
    if(tmpprice!=null){
      price = tmpprice;
    }
    var tmppublishing = map[Constant.BOOK_COLUMN_PUBLISHING];

    if(tmppublishing!=null){
      publishing = tmppublishing;
    }
  }
}

（1）insert：

       var book =new Book(name: 'EDG',price:25.36,publishing: '上海',author: 'seven');
       var bookid = await  db.insert(Constant.TABLE_BOOK, book.toMap());
    

（2）query：

         List<Map> maps = await db.query(Constant.TABLE_BOOK,columns: [
           Constant.BOOK_COLUMN_ID,
           Constant.BOOK_COLUMN_AUTHOR,
           Constant.BOOK_COLUMN_NAME,
           Constant.BOOK_COLUMN_PRICE,
           Constant.BOOK_COLUMN_PUBLISHING
         ],
             where: '${Constant.BOOK_COLUMN_ID}=?',whereArgs: [1]
         );

（3）delete:

  var bookid = await  db.delete(Constant.TABLE_BOOK, where: '${Constant.BOOK_COLUMN_NAME}=?',whereArgs: ['RNG']);

（4）upgrade：

     var book =new Book(name: '启封秘籍',price:25.36,publishing: '上海');
       var bookid = await  db.update(Constant.TABLE_BOOK,book.toMap(), where: '${Constant.BOOK_COLUMN_ID}=?',whereArgs: [1]);

3.建立多个表
由于onCreate只会在创建db数据库时调用一次，所以你新建表只能放在onUpgrade中，总之，表的结构更新或者添加新的表，都是在onUpgrade中，示例如下：
Student表：

  /*
  student 表
   */
  static const String TABLE_STUDENT = 'student';
  static const String STUDENT_COLUMN_ID = 'id';
  static const String STUDENT_COLUMN_NAME= 'name';
  static const String STUDENT_COLUMN_GENDER = 'gender';
  static const String STUDENT_COLUMN_CLASS = 'class';
  static const CREATE_STUDENT ='''
          CREATE TABLE $TABLE_STUDENT(
        $STUDENT_COLUMN_ID integer primary key,
        $STUDENT_COLUMN_NAME text,
        $STUDENT_COLUMN_GENDER text,
        $STUDENT_COLUMN_CLASS integer
        )
  ''';

添加Student表：

      var dbpath = await getDatabasesPath();
      String dbname = join(dbpath+'/demo.db');
        db = await openDatabase(dbname,version: 3,onCreate: (Database db,int version)async{
            print('onCreate:version:$version');
            await db.execute(Constant.CREATE_BOOK);
      },onUpgrade: (Database db,int oldVersion,int newVersion) async {
          print('onUpgrade:oldversion:${oldVersion},newVersion:${newVersion}');
            if(oldVersion<2){
              var batch = db.batch();
              var upgradeSql = '''alter table ${Constant.TABLE_BOOK} add column ${Constant.BOOK_COLUMN_AUTHOR} text''';
              batch.execute(upgradeSql);
              await batch.commit();
            }
          if(oldVersion<3){
            await db.execute(Constant.CREATE_STUDENT);
          }
        });

4.transaction事务，和批处理batch 后面在写。