Flutter基礎-041-資料庫

sqflite

在flutter中使用的是sqflite外掛：pub.dev/packages/sq…

新增依賴

pubspec.yaml

dependencies:
  ......
  sqflite: ^1.1.6+2
複製程式碼

檔案引入

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
複製程式碼

資料庫建立

資料庫建立：

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

import 'user/UserDb.dart';

class DbHelper {
  String path = '';
  Database db ;

  createDb() async {
    var databasesPath = await getDatabasesPath();
    path = join(databasesPath, 'flutter_app.db');
  }

  createTable() async {
    db = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await UserDb().createTable(db);
    });
  }

  getDb() {
    return db;
  }

  deleteDb() async {
    await deleteDatabase(path);
  }

  close() async {
    await db.close();
    db = null;
  }
}

複製程式碼

表內CRUD：

import 'UserBean.dart';
import 'package:sqflite/sqflite.dart';

class UserDb {
  static const String TABLE_USER = "TABLE_USER";
  static const String NAME = "name";
  static const String AGE = "age";
  static const String ADDRESS = "address";

  createTable(db) async {
    await db.execute(
        'CREATE TABLE $TABLE_USER (id INTEGER PRIMARY KEY, $NAME TEXT, $AGE INTEGER, $ADDRESS TEXT)');
  }

  rawInsert(Database db, UserBean userBean) async {
    await db.transaction((txn) async {
      await txn.rawInsert(
          'INSERT INTO $TABLE_USER($NAME, $AGE, $ADDRESS) VALUES(${userBean.name}, '
          '${userBean.age},'
          '${userBean.address}'
          ')');
    });
  }

  insert(Database db, UserBean userBean) async {
    await db.insert(TABLE_USER, userBean.toMap());
  }

  rawUpdate(Database db, UserBean userBean) async {
    return await db.transaction((txn) async {
      await txn
          .rawUpdate('UPDATE $TABLE_USER SET $ADDRESS = "${userBean.address}" '
              'WHERE $NAME = "${userBean.name}"');
    });
  }

  Future<int> update(Database db, UserBean userBean) async {
    return await db.update(TABLE_USER, userBean.toMap(),
        where: '$NAME = ?', whereArgs: [userBean.name]);
  }

  Future<List<Map>> queryALl(Database db) async {
    List<Map> list = await db.rawQuery('SELECT * FROM $TABLE_USER');
    return list;
  }

  Future<List<Map<String,dynamic>>> queryByName(Database db, String name) async {
    List<Map<String,dynamic>> list =
        await db.query(TABLE_USER, where: '$NAME = ?', whereArgs: [name]);
    return list;
  }

  deleteRawUserByID(Database db, int id) async {
    return await db.transaction((txn) async {
      await txn.rawDelete('DELETE FROM $TABLE_USER WHERE id = ${id}');
    });
  }

  deleteUserByID(Database db, int id) async {
    return await db.delete(TABLE_USER, where: 'id = ?', whereArgs: [id]);
  }

  deleteUserByName(Database db, String name) async {
    return await db.delete(TABLE_USER, where: '$NAME = ?', whereArgs: [name]);
  }

  clearTable(Database db) async {
    await db.delete('$TABLE_USER');
  }
}

複製程式碼

資料類：

class UserBean {
  String address;
  String name;
  int age;

  UserBean(this.name,this.age,this.address);
  Map<String, dynamic> toMap() {
    var map = <String, dynamic>{
      "name": name,
      "age": age,
      "address": address,
    };
    return map;
  }

  UserBean.fromMap(Map<String, dynamic> map) {
    name = map["name"];
    age = map["age"];
    address = map["address"];
  }
}
複製程式碼

測試：

import 'package:flutter/material.dart';
import 'sqflite/DbHelper.dart';
import 'sqflite/user/UserBean.dart';
import 'sqflite/user/UserDb.dart';

class SqfliteTest extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'sqflite',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: MyHomePage(title: 'sqflite'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {

  DbHelper dbHelper;
  UserDb userDb;
  @override
  void initState() {
    super.initState();
    dbHelper = new DbHelper();
    userDb = new UserDb();
  }


  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: ConstrainedBox(
        constraints: BoxConstraints.expand(),
        child: Column(
          children: <Widget>[
            RaisedButton(
              child: Text("create db"),
                onPressed: (){
                  dbHelper.createDb();
                }
            ),
            RaisedButton(
                child: Text("create table"),
                onPressed: (){
                  dbHelper.createTable();
                }
            ),
            RaisedButton(
                child: Text("insert "),
                onPressed: (){
                  userDb.insert(dbHelper.getDb(), new UserBean("jack",22,"beijing"));
                }
            ),
            RaisedButton(
                child: Text("query "),
                onPressed: (){
                  userDb.queryByName(dbHelper.getDb(), "jack")
                      .then((List<Map<String,dynamic>> value){
                        if(value!=null && value.length>0){
                          UserBean user = UserBean.fromMap(value[0]);
                          print(user.name);
                          print(user.address);
                          print(user.age);
                        }

                  });

                }
            ),RaisedButton(
                child: Text("update "),
                onPressed: (){
                  userDb.update(dbHelper.getDb(), new UserBean("jack",22,"wuhan"));
                }
            ),
            RaisedButton(
                child: Text("delete "),
                onPressed: (){
                  userDb.deleteUserByName(dbHelper.getDb(), "jack");
                }
            ),
            RaisedButton(
                child: Text("close "),
                onPressed: (){
                  dbHelper.close();
                }
            ),
          ],
        ),
      ),
    );
  }
}

複製程式碼