Example 1: delete query in sqlite flutter
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');
await deleteDatabase(path);
Database database = await openDatabase(path, version: 1,
onCreate: (Database db, int version) async {
await db.execute(
'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
});
await database.transaction((txn) async {
int id1 = await txn.rawInsert(
'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
print('inserted1: $id1');
int id2 = await txn.rawInsert(
'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
['another name', 12345678, 3.1416]);
print('inserted2: $id2');
});
int count = await database.rawUpdate(
'UPDATE Test SET name = ?, value = ? WHERE name = ?',
['updated name', '9876', 'some name']);
print('updated: $count');
List<Map> list = await database.rawQuery('SELECT * FROM Test');
List<Map> expectedList = [
{'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.789},
{'name': 'another name', 'id': 2, 'value': 12345678, 'num': 3.1416}
];
print(list);
print(expectedList);
assert(const DeepCollectionEquality().equals(list, expectedList));
count = Sqflite
.firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
assert(count == 2);
count = await database
.rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
assert(count == 1);
await database.close();
Example 2: sqlite in flutter
import 'dart:async';
import 'dart:io';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'Product.dart';
class SQLiteDbProvider {
SQLiteDbProvider._();
static final SQLiteDbProvider db = SQLiteDbProvider._();
static Database _database;
Future<Database> get database async {
if (_database != null)
return _database;
_database = await initDB();
return _database;
}
initDB() async {
Directory documentsDirectory = await
getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, "ProductDB.db");
return await openDatabase(
path, version: 1,
onOpen: (db) {},
onCreate: (Database db, int version) async {
await db.execute(
"CREATE TABLE Product ("
"id INTEGER PRIMARY KEY,"
"name TEXT,"
"description TEXT,"
"price INTEGER,"
"image TEXT"")"
);
await db.execute(
"INSERT INTO Product ('id', 'name', 'description', 'price', 'image')
values (?, ?, ?, ?, ?)",
[1, "iPhone", "iPhone is the stylist phone ever", 1000, "iphone.png"]
);
await db.execute(
"INSERT INTO Product ('id', 'name', 'description', 'price', 'image')
values (?, ?, ?, ?, ?)",
[2, "Pixel", "Pixel is the most feature phone ever", 800, "pixel.png"]
);
await db.execute(
"INSERT INTO Product ('id', 'name', 'description', 'price', 'image')
values (?, ?, ?, ?, ?)",
[3, "Laptop", "Laptop is most productive development tool", 2000, "laptop.png"]
);
await db.execute(
"INSERT INTO Product ('id', 'name', 'description', 'price', 'image')
values (?, ?, ?, ?, ?)",
[4, "Tablet", "Laptop is most productive development tool", 1500, "tablet.png"]
);
await db.execute(
"INSERT INTO Product ('id', 'name', 'description', 'price', 'image')
values (?, ?, ?, ?, ?)",
[5, "Pendrive", "Pendrive is useful storage medium", 100, "pendrive.png"]
);
await db.execute(
"INSERT INTO Product ('id', 'name', 'description', 'price', 'image')
values (?, ?, ?, ?, ?)",
[6, "Floppy Drive", "Floppy drive is useful rescue storage medium", 20, "floppy.png"]
);
}
);
}
Future<List<Product>> getAllProducts() async {
final db = await database;
List<Map> results = await db.query(
"Product", columns: Product.columns, orderBy: "id ASC"
);
List<Product> products = new List();
results.forEach((result) {
Product product = Product.fromMap(result);
products.add(product);
});
return products;
}
Future<Product> getProductById(int id) async {
final db = await database;
var result = await db.query("Product", where: "id = ", whereArgs: [id]);
return result.isNotEmpty ? Product.fromMap(result.first) : Null;
}
insert(Product product) async {
final db = await database;
var maxIdResult = await db.rawQuery("SELECT MAX(id)+1 as last_inserted_id FROM Product");
var id = maxIdResult.first["last_inserted_id"];
var result = await db.rawInsert(
"INSERT Into Product (id, name, description, price, image)"
" VALUES (?, ?, ?, ?, ?)",
[id, product.name, product.description, product.price, product.image]
);
return result;
}
update(Product product) async {
final db = await database;
var result = await db.update(
"Product", product.toMap(), where: "id = ?", whereArgs: [product.id]
);
return result;
}
delete(int id) async {
final db = await database;
db.delete("Product", where: "id = ?", whereArgs: [id]);
}
}
Example 3: flutter sqflite DatabaseHelper
import 'dart:io' as io;
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
class DatabaseHelper {
static final DatabaseHelper _instance = new DatabaseHelper.internal();
factory DatabaseHelper() => _instance;
static Database _db;
DatabaseHelper.internal();
initDb() async {
io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, "database_name.db");
_db = await openDatabase(path, version: 1, onCreate: _onCreate);
}
Database get db {
return _db;
}
void _onCreate(Database db, int version) async {
await db.execute(
'CREATE TABLE todos (id INTEGER PRIMARY KEY AUTOINCREMENT,'
'item_name TEXT,'
'date_created TEXT')';
}
}
Example 4: flutter sqflite DatabaseHelper
import 'dart:async';
import 'package:eknumber/utils/database_helper.dart';
import 'package:flutter_simple_dependency_injection/injector.dart';
class Injection {
static DatabaseHelper _databaseHelper = DatabaseHelper();
static Injector injector;
static Future initInjection() async {
await _databaseHelper.initDb();
injector = Injector.getInjector();
injector.map<DatabaseHelper>((i) => _databaseHelper,
isSingleton: true);
}
}
Example 5: flutter sqflite DatabaseHelper
class Todo{
int id;
String itemName;
String dateCreated;
Todo(
{this.id,
this.itemName,
this.dateCreated})
Map<String, dynamic> toMapWithoutId() {
final map = new Map<String, dynamic>();
map["item_name"] = itemName;
map["date_created"] = dateCreated;
return map;
}
Map<String, dynamic> toMap() {
final map = new Map<String, dynamic>();
map["id"] = id;
map["item_name"] = itemName;
map["date_created"] = dateCreated;
return map;
}
factory Todo.fromMap(Map<String, dynamic> data) => new Todo(
id: data['id'],
itemName: data['item_name'],
dateCreated: data['date_created']
);
}