sqflite插件简单使用

sqflite是github上tekartik提供的可供Android和iOS使用的SQLite插件,操作也是非常的简单.

下载

dependencies:
  
  ...

  sqflite: ^1.1.0

导入

import 'package:sqflite/sqflite.dart';

用SQL进行操作

获取资源存放位置

import 'package:path/path.dart';
// join 使用这个包

String _path = '';

Future getPath() async {
  return await getDatabasesPath();
}

// 使用
getPath().then((path) {
  setState(() {
    _path = join(path.toString(), 'demo.db');
  });
});

建表

Database _database;
Future createDB() async {
    _database = await openDatabase(
      _path,
      version: 1,
      onCreate: (db, version) async {
        await db.execute(
            'CREATE TABLE demo(id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
      },
    );
  }

插入一条数据

_database.transaction((txn) async {
  await txn.rawInsert(
     'INSERT INTO demo(name, value, num) VALUES("some name", 1234, 456.789)');
})

查询数据

Future<List> query() async {
    return await _database.rawQuery('SELECT * FROM demo');
  }

修改数据

Future update() async {
    return await _database.rawUpdate(
        'UPDATE demo SET name = ?, VALUE = ? WHERE name = ?',
        ['updated name', '9876', 'some name']);
  }

删除一条数据

Future delete() async {
    return await _database
        .rawDelete('DELETE FROM demo WHERE 1 ORDER BY id LIMIT 1');
  }

使用对象进行操作

import 'package:flutter/material.dart';
import 'package:flutter/cupertino.dart';
import 'package:sqflite/sqflite.dart';

/*
 * @Date: 2019-02-28 10:24 
 * @Description TODO
 */

final String tableTodo = 'todo';
final String columnId = '_id';
final String columnTitle = 'title';
final String columnDone = 'done';

class Todo {
  int id;
  String title;
  bool done;

  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      columnTitle: title,
      columnDone: done ? 0 : 1,
    };

    if (id != null) {
      map[columnId] = id;
    }
    return map;
  }

  Todo();

  Todo.fromMap(Map<String, dynamic> map) {
    id = map[columnId];
    title = map[columnTitle];
    done = map[columnDone] == 1;
  }
}

class TodoProvider {
  Database db;

  Future open(String path) async {
    db = await openDatabase(
      path,
      version: 1,
      onCreate: (db, version) async {
        await db.execute('''
create table $tableTodo ( 
  $columnId integer primary key autoincrement, 
  $columnTitle text not null,
  $columnDone integer not null)
''');
      },
    );
  }

  Future<Todo> insert(Todo todo) async {
    todo.id = await db.insert(tableTodo, todo.toMap());
    return todo;
  }

  Future<Todo> getTodo(int id) async {
    List<Map> map = await db.query(
      tableTodo,
      columns: [columnId, columnTitle, columnTitle],
      where: '$columnId = ?',
      whereArgs: [id],
    );

    if(map.length > 0) {
      return Todo.fromMap(map.first);
    }
    return null;
  }

  Future<int> delete(int id) async {
    return await db.delete(tableTodo, where: '$columnId = ?', whereArgs: [id]);
  }
  
  Future<int> update(Todo todo) async {
    return await db.update(tableTodo, todo.toMap(), where: '$columnId = ?', whereArgs: [todo.id]);
  }

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