How to insert quickly multiple records in sqflite? The standard quickly method is:
await database.insert(table, object.toMap())
But I don't think that insert record one to one with a cycle is a good idea. Or I can insert all list with a transaction?
As I mentioned in the comment, you can use Batch. Here is the sample.
Batch batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
Now if you are looking for result (it will cost you some memory), you use
results = await batch.commit();
And if you are looking for fast performance, just ignore the result and use
await batch.commit(noResult: true);
You can use batch in such case.
batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
results = await batch.commit();
big batches,you can use await batch.commit(noResult: true);
If you have a list of objects you want to insert, you can do it like this (it's not a very clean code, but it works):
insertClients(List<Client> clients) async {
final db = await database;
var buffer = new StringBuffer();
clients.forEach((c) {
if (buffer.isNotEmpty) {
buffer.write(",\n");
}
buffer.write("('");
buffer.write(c.firstName);
buffer.write("', '");
buffer.write(c.lastName);
buffer.write("', '");
buffer.write(c.address);
buffer.write("')");
});
var raw =
await db.rawInsert("INSERT Into Clients (firstName,lastName,address)"
" VALUES ${buffer.toString()}");
return raw;
}
I'm using this statement from SQLite:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
I saw the possibility of using database transactions, very similar to Android. I find it better to use them, here I show an example of code if it could be useful to someone. In my example try-catches are shown, but it is possible to use Future function callbacks to get a cleaner code :
Future<List<dynamic>> insertAll(String table, List<dynamic> objects) async {
List<dynamic> listRes = new List();
var res;
try {
await DbHelper().database.transaction((db) async {
objects.forEach((obj) async {
try {
var iRes = await db.insert(table, obj.toMap());
listRes.add(iRes);
} catch (ex) {
DbHelper().databaseLog(CON_INSERT_MULTIPLE, "Error!", ex);
}
});
});
DbHelper().databaseLog(CON_INSERT_MULTIPLE, table, listRes);
res = listRes;
} catch (er) {
res = OutComeCallClient.ERROR;
DbHelper().databaseLog(CON_INSERT_MULTIPLE, "Error!", er);
}
return res;
}
And this is my code, maybe can help ;) Thanks to MihaiV.
insertOrUpdateAll(List<Map<String, dynamic>> ticks) async {
if (ticks.length == 0) return null;
final columns = ticks.first.keys.join(",");
var valuesBuffer = new StringBuffer();
ticks.forEach((task) {
if (valuesBuffer.isNotEmpty) valuesBuffer.write(",\n");
valuesBuffer.write("(");
int ix = 0;
task.forEach((key, value) {
if (ix++ != 0) valuesBuffer.write(',');
final isString =
columnsInfo.where((c) => c.name == key).first.type == RowType.text;
if (isString) valuesBuffer.write("'$value'");
valuesBuffer.write(value);
});
valuesBuffer.write(")");
});
return await db.rawInsert("INSERT Into Clients ($columns)"
" VALUES ${valuesBuffer.toString()}");
}
Model
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
final String tableName = "todom";
final String Column_id = "id";
final String Column_name = "name";
class TaskModel{
final String name;
int id;
TaskModel({this.name, this.id});
Map<String, dynamic> toMap(){
return {
Column_name : this.name
};
}
}
class TodoHelper{
Database db;
TodoHelper(){
initDatabase();
}
Future<void> initDatabase() async{
db = await openDatabase(
join(await getDatabasesPath(), "databse.db"),
onCreate: (db, version){
return db.execute("CREATE TABLE $tableName($Column_id INTEGER PRIMARY KEY AUTOINCREMENT, $Column_name TEXT)");
},
version: 1
);
}
Future<void> insertTask(TaskModel task) async{
try{
db.insert(tableName, task.toMap(), conflictAlgorithm: ConflictAlgorithm.replace);
}catch(_){
print(_);
}
}
Future<List<TaskModel>> getAllTask () async{
final List<Map<String, dynamic>> tasks = await db.query(tableName);
return List.generate(tasks.length, (i){
return TaskModel(name: tasks[i][Column_name], id: tasks[i][Column_id]);
});
}
}
main.dart
import 'package:flutter/material.dart';
import 'TaskModel.dart';
void main() => runApp(MyApp());
class MyApp extends StatelessWidget {
// This widget is the root of your application.
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'Flutter Demo',
theme: ThemeData(
// This is the theme of your application.
//
// Try running your application with "flutter run". You'll see the
// application has a blue toolbar. Then, without quitting the app, try
// changing the primarySwatch below to Colors.green and then invoke
// "hot reload" (press "r" in the console where you ran "flutter run",
// or simply save your changes to "hot reload" in a Flutter IDE).
// Notice that the counter didn't reset back to zero; the application
// is not restarted.
primarySwatch: Colors.blue,
),
home: MyHomePage(),
);
}
}
class MyHomePage extends StatefulWidget {
@override
_MyHomePageState createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
final textController = TextEditingController();
List<TaskModel> tasks = [];
TaskModel currentTask;
@override
Widget build(BuildContext context) {
final TodoHelper _todoHelper = TodoHelper();
return Scaffold(
body: Container(
padding:EdgeInsets.all(32),
child: Column(
children: <Widget>[
TextField(
controller: textController,
),
FlatButton(
child: Text("Insert"),
onPressed: (){
currentTask = TaskModel(name: textController.text);
_todoHelper.insertTask(currentTask);
},
color: Colors.blue,
textColor: Colors.white,
),
FlatButton(
child: Text("Show All Task"),
onPressed: () async{
List<TaskModel> list = await _todoHelper.getAllTask();
setState(() {
tasks = list;
});
},
color: Colors.red,
textColor: Colors.white,
),
Expanded(
child: ListView.separated(
itemBuilder: (context, index){
return ListTile(
leading: Text("${tasks[index].id}"),
title: Text("${tasks[index].name}"),
);
},
separatorBuilder: (context, index) => Divider(),
itemCount: tasks.length,
),
)
],
),
),
);
}
}
you can try with rawInsert
Database db = await instance.database;
String sql = '''
INSERT INTO p_tasklistproviderobjective (
idticket,
objective
) VALUES (?, ?)
''';
//you can get this data from json object /API
List<Map> data = [
{"idticket": 5, "objective": "dono"},
{"idticket": 6, "objective": "dono2"},
{"idticket": 7, "objective": "dono3"},
{"idticket": 8, "objective": "dono4"},
];
//and then loop your data here
data.forEach((element) async {
await db.rawInsert(sql, [element['idticket'], element['objective']]);
});
