flutter

Sqlite数据库操作

Posted by アライさん on 2019年10月22日 

1
2
3
4
#sqlite
sqflite: ^1.1.6
#path
path_provider: ^1.1.0
1
2
3
final dbHelper = DatabaseHelper.instance;
int count = await dbHelper.queryRssUrlTableNameRowCount(title.text);
final id = await dbHelper.insertRssTable(row);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
import 'dart:io';

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

/// 数据库操作类
class DatabaseHelper {
  //id
  static final String rssId = '_id';

  //rss源名称
  static final String rssName = 'name';

  //rss源url
  static final String rssUrl = 'url';

  //rss标题
  static final String title = 'title';

  //rss简述
  static final String description = 'description';

  //rss链接
  static final String isRead = 'isread';

  //rss table
  static final String rssUrlTable = "rss_url_table";
  static final String rssMsgTable = "rss_msg_table";

  //database name
  static final _databaseName = "RssDatabase.db";
  static final _databaseVersion = 1;

  static Database _database;

  // make this a singleton class
  DatabaseHelper._privateConstructor();

  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  Future<Database> get database async {
    if (_database != null) {
      return _database;
    }
    _database = await _initDatabase();
    return _database;
  }

  _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _databaseName);
    return await openDatabase(path,
        version: _databaseVersion, onCreate: _onCreate);
  }

  ///创建数据库
  Future _onCreate(Database db, int version) async {
    await db.execute('''
          CREATE TABLE $rssUrlTable (
            $rssId INTEGER PRIMARY KEY,
            $rssName TEXT NOT NULL,
            $rssUrl TEXT NOT NULL
          )
          ''');
    await db.execute('''
          CREATE TABLE $rssMsgTable (
            $rssId INTEGER PRIMARY KEY,
            $title TEXT NOT NULL,
            $description TEXT NOT NULL,
            $url TEXT NOT NULL,
            $isRead TINYINT NOT NULL
          )
    ''');
  }

  ///插入rss源表
  Future<int> insertRssTable(Map<String, dynamic> row) async {
    Database db = await instance.database;
    return await db.insert(rssUrlTable, row);
  }

  ///插入rss记录表
  Future<int> insertRssMsgTable(Map<String, dynamic> row) async {
    Database db = await instance.database;
    return await db.insert(rssMsgTable, row);
  }

  ///搜索rss源表
  Future<List<Map<String, dynamic>>> queryRssUrlTableAllRows() async {
    Database db = await instance.database;
    return await db.query(rssUrlTable);
  }

  ///搜索rss记录表
  Future<List<Map<String, dynamic>>> queryRssMsgTableAllRows() async {
    Database db = await instance.database;
    return await db.query(rssUrlTable);
  }

  ///根据url查询rss源表
  Future<int> queryRssUrlTableNameRowCount(String name) async {
    Database db = await instance.database;
    final String sql =
        'SELECT COUNT(*) FROM $rssUrlTable WHERE $rssName = \'$name\'';
    return Sqflite.firstIntValue(await db.rawQuery(sql));
  }

  ///搜索rss记录表中未读的数量
  Future<int> queryUnReadRowCount() async {
    Database db = await instance.database;
    return Sqflite.firstIntValue(await db
        .rawQuery('SELECT COUNT(*) FROM $rssMsgTable WHERE $isRead = 1'));
  }

  ///更新rss记录表中的已读未读状态
  Future<int> updateRead(Map<String, dynamic> row) async {
    Database db = await instance.database;
    int id = row[rssId];
    return await db
        .update(rssMsgTable, row, where: '$rssId = ?', whereArgs: [id]);
  }

  ///删除rss源表
  Future<int> deleteRssUrlById(int id) async {
    Database db = await instance.database;
    return await db.delete(rssUrlTable, where: '$rssId = ?', whereArgs: [id]);
  }

  ///根据url删除rss源表
  Future<int> deleteRssUrlByUrl(String url) async {
    Database db = await instance.database;
    return await db.delete(rssUrlTable, where: '$rssUrl = ?', whereArgs: [url]);
  }

  ///删除rss记录表
  Future<int> deleteRssMsg(int id) async {
    Database db = await instance.database;
    return await db.delete(rssMsgTable, where: '$rssId = ?', whereArgs: [id]);
  }

  ///删除rss记录表
  Future<int> deleteAllRssMsg(int id) async {
    Database db = await instance.database;
    return await db.delete(rssMsgTable);
  }
}




Tags
flutter