SQLite fails for insert for json generated via json_serializable

Question

I am trying to understand why the following exception is occurring on DB insert and how to do this correctly.

ERROR:flutter/lib/ui/ui_dart_state.cc(157)] Unhandled Exception: DatabaseException(java.util.HashMap cannot be cast to java.lang.Integer) sql 'INSERT OR REPLACE INTO homes (id, name, color, pets) VALUES (?, ?, ?, ?)' args [1, Smith, Green, [{name: Mackie, id: 1, breed: Rottweiler, age: 8}, {name: Tanner, id: 2, breed: Mastiff, age: 8}]]}

What part of this is interpreted as an Integer that is resulting in the cast error?

Through other posts and suggestions, I have added json_serializable to my project and both model classes in my project are annotated, resulting in the toJson and fromJson methods being created during the build process.

Inserting the simple Dog object works perfectly Inserting the House object which includes a List as one of its fields fails.

I am using the toJson function generated from serializable for the insert into my DB.

Printing the output of each object results in the following:

A Dog: {id: 1, breed: Rottweiler, name: Mackie, age: 8}

A House: {id: 1, name: Smith, color: Green, pets: [{id: 1, breed: Rottweiler, name: Mackie, age: 8}, {id: 2, breed: Mastiff, name: Tanner, age: 8}]}

What is the issue with the house json that is preventing the insert from being successful and resulting in the cast exception I mentioned at the beginning of the post?

My code

Dog Class

import 'package:json_annotation/json_annotation.dart';

part 'dog.g.dart';

@JsonSerializable()
class Dog {
  final int id;
  final String breed;
  final String name;
  final int age;

  Dog({this.id, this.breed, this.name, this.age});

  factory Dog.fromJson(Map<String, dynamic> json) => _$DogFromJson(json);
  Map<String, dynamic> toJson() => _$DogToJson(this);
}

Dog part

// GENERATED CODE - DO NOT MODIFY BY HAND

part of 'dog.dart';

// **************************************************************************
// JsonSerializableGenerator
// **************************************************************************

Dog _$DogFromJson(Map<String, dynamic> json) {
  return Dog(
    id: json['id'] as int,
    breed: json['breed'] as String,
    name: json['name'] as String,
    age: json['age'] as int,
  );
}

Map<String, dynamic> _$DogToJson(Dog instance) => <String, dynamic>{
      'id': instance.id,
      'breed': instance.breed,
      'name': instance.name,
      'age': instance.age,
    };

House class

import 'dog.dart';
import 'package:json_annotation/json_annotation.dart';

part 'house.g.dart';

@JsonSerializable(explicitToJson: true)
class House{
  final int id;
  final String name;
  final String color;
  final List<Dog> pets;

  House({this.id, this.name, this.color, this.pets});

  factory House.fromJson(Map<String, dynamic> json) => _$HouseFromJson(json);
  Map<String, dynamic> toJson() => _$HouseToJson(this);
}

House part

// GENERATED CODE - DO NOT MODIFY BY HAND

part of 'house.dart';

// **************************************************************************
// JsonSerializableGenerator
// **************************************************************************

House _$HouseFromJson(Map<String, dynamic> json) {
  return House(
    id: json['id'] as int,
    name: json['name'] as String,
    color: json['color'] as String,
    pets: (json['pets'] as List)
        ?.map((e) => e == null ? null : Dog.fromJson(e as Map<String, dynamic>))
        ?.toList(),
  );
}

Map<String, dynamic> _$HouseToJson(House instance) => <String, dynamic>{
      'id': instance.id,
      'name': instance.name,
      'color': instance.color,
      'pets': instance.pets?.map((e) => e?.toJson())?.toList(),
    };

Any assistance in helping me understand the piece I am missing here would be hugely appreciated.

Is there something additional I need to do following the toJson function before submitting the data to my db insert method when the object in question is not just a simple PODO consisting of primitive types?

Full Error

E/flutter ( 2822): [ERROR:flutter/lib/ui/ui_dart_state.cc(157)] Unhandled Exception: DatabaseException(java.util.HashMap cannot be cast to java.lang.Integer) sql 'INSERT OR REPLACE  INTO homes (id, name, color, pets) VALUES (?, ?, ?, ?)' args [1, Smith, Green, [{name: Mackie, id: 1, breed: Rottweiler, age: 8}, {name: Tanner, id: 2, breed: Mastiff, age: 8}]]}
E/flutter ( 2822): #0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:12:7)
E/flutter ( 2822): <asynchronous suspension>
E/flutter ( 2822): #1      SqfliteDatabaseFactoryImpl.wrapDatabaseException (package:sqflite/src/factory_impl.dart:25:7)
E/flutter ( 2822): #2      SqfliteDatabaseMixin.safeInvokeMethod (package:sqflite/src/database_mixin.dart:188:15)
E/flutter ( 2822): #3      SqfliteDatabaseMixin.txnRawInsert.<anonymous closure> (package:sqflite/src/database_mixin.dart:363:14)
E/flutter ( 2822): #4      SqfliteDatabaseMixin.txnSynchronized.<anonymous closure> (package:sqflite/src/database_mixin.dart:307:22)
E/flutter ( 2822): #5      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:32:26)
E/flutter ( 2822): #6      SqfliteDatabaseMixin.txnSynchronized (package:sqflite/src/database_mixin.dart:303:43)
E/flutter ( 2822): #7      SqfliteDatabaseMixin.txnWriteSynchronized (package:sqflite/src/database_mixin.dart:325:7)
E/flutter ( 2822): #8      SqfliteDatabaseMixin.txnRawInsert (package:sqflite/src/database_mixin.dart:362:12)
E/flutter ( 2822): #9      SqfliteDatabaseExecutorMixin.rawInsert (package:sqflite/src/database_mixin.dart:49:15)
E/flutter ( 2822): #10     SqfliteDatabaseExecutorMixin.insert (package:sqflite/src/database_mixin.dart:59:12)
E/flutter ( 2822): #11     Homes.addNewHome (package:search_list_view/providers/homes.dart:20:31)
E/flutter ( 2822): <asynchronous suspension>
E/flutter ( 2822): #12     _MyAppState.build.<anonymous closure> (package:search_list_view/main.dart:104:26)
E/flutter ( 2822): #13     _InkResponseState._handleTap (package:flutter/src/material/ink_well.dart:706:14)
E/flutter ( 2822): #14     _InkResponseState.build.<anonymous closure> (package:flutter/src/material/ink_well.dart:789:36)
E/flutter ( 2822): #15     GestureRecognizer.invokeCallback (package:flutter/src/gestures/recognizer.dart:182:24)
E/flutter ( 2822): #16     TapGestureRecognizer.handleTapUp (package:flutter/src/gestures/tap.dart:486:11)
E/flutter ( 2822): #17     BaseTapGestureRecognizer._checkUp (package:flutter/src/gestures/tap.dart:264:5)
E/flutter ( 2822): #18     BaseTapGestureRecognizer.handlePrimaryPointer (package:flutter/src/gestures/tap.dart:199:7)
E/flutter ( 2822): #19     PrimaryPointerGestureRecognizer.handleEvent (package:flutter/src/gestures/recognizer.dart:467:9)
E/flutter ( 2822): #20     PointerRouter._dispatch (package:flutter/src/gestures/pointer_router.dart:76:12)
E/flutter ( 2822): #21     PointerRouter._dispatchEventToRoutes.<anonymous closure> (package:flutter/src/gestures/pointer_router.dart:117:9)
E/flutter ( 2822): #22     _LinkedHashMapMixin.forEach (dart:collection-patch/compact_hash.dart:379:8)
E/flutter ( 2822): #23     PointerRouter._dispatchEventToRoutes (package:flutter/src/gestures/pointer_router.dart:115:18)
E/flutter ( 2822): #24     PointerRouter.route (package:flutter/src/gestures/pointer_router.dart:101:7)
E/flutter ( 2822): #25     GestureBinding.handleEvent (package:flutter/src/gestures/binding.dart:218:19)
E/flutter ( 2822): #26     GestureBinding.dispatchEvent (package:flutter/src/gestures/binding.dart:198:22)
E/flutter ( 2822): #27     GestureBinding._handlePointerEvent (package:flutter/src/gestures/binding.dart:156:7)
E/flutter ( 2822): #28     GestureBinding._flushPointerEventQueue (package:flutter/src/gestures/binding.dart:102:7)
E/flutter ( 2822): #29     GestureBinding._handlePointerDataPacket (package:flutter/src/gestures/binding.dart:86:7)
E/flutter ( 2822): #30     _rootRunUnary (dart:async/zone.dart:1138:13)
E/flutter ( 2822): #31     _CustomZone.runUnary (dart:async/zone.dart:1031:19)
E/flutter ( 2822): #32     _CustomZone.runUnaryGuarded (dart:async/zone.dart:933:7)
E/flutter ( 2822): #33     _invoke1 (dart:ui/hooks.dart:273:10)
E/flutter ( 2822): #34     _dispatchPointerDataPacket (dart:ui/hooks.dart:182:5)
E/flutter ( 2822): 

Thank you, Bob

Answer 1

You can copy paste run full code below
Store Pet as json string in TEXT field and convert json back to class Pet

working demo

full code

import 'package:flutter/material.dart';
import 'dart:io';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'package:dio/dio.dart';
import 'dart:convert';

void main() => runApp(MyApp());

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: HomePage(),
    );
  }
}

class HomePage extends StatefulWidget {
  const HomePage({Key key}) : super(key: key);

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

class _HomePageState extends State<HomePage> {
  var isLoading = false;

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('Api to sqlite'),
        centerTitle: true,
        actions: <Widget>[
          Container(
            padding: EdgeInsets.only(right: 10.0),
            child: IconButton(
              icon: Icon(Icons.settings_input_antenna),
              onPressed: () async {
                await _loadFromApi();
              },
            ),
          ),
          Container(
            padding: EdgeInsets.only(right: 10.0),
            child: IconButton(
              icon: Icon(Icons.delete),
              onPressed: () async {
                await _deleteData();
              },
            ),
          ),
        ],
      ),
      body: isLoading
          ? Center(
              child: CircularProgressIndicator(),
            )
          : _buildHouseListView(),
    );
  }

  _loadFromApi() async {
    setState(() {
      isLoading = true;
    });

    var apiProvider = HouseApiProvider();
    await apiProvider.getAllHouses();

    // wait for 2 seconds to simulate loading of data
    await Future.delayed(const Duration(seconds: 2));

    setState(() {
      isLoading = false;
    });
  }

  _deleteData() async {
    setState(() {
      isLoading = true;
    });

    await DBProvider.db.deleteAllHouses();

    // wait for 1 second to simulate loading of data
    await Future.delayed(const Duration(seconds: 1));

    setState(() {
      isLoading = false;
    });

    print('All Houses deleted');
  }

  _buildHouseListView() {
    return FutureBuilder(
      future: DBProvider.db.getAllHouses(),
      builder: (BuildContext context, AsyncSnapshot snapshot) {
        if (!snapshot.hasData) {
          return Center(
            child: CircularProgressIndicator(),
          );
        } else {
          return ListView.separated(
            separatorBuilder: (context, index) => Divider(
              color: Colors.black12,
            ),
            itemCount: snapshot.data.length,
            itemBuilder: (BuildContext context, int index) {
              List<Pet> pets = snapshot.data[index].pets;

              return ListTile(
                leading: Text(
                  "${index + 1}",
                  style: TextStyle(fontSize: 20.0),
                ),
                title: Text(
                    "Name: ${snapshot.data[index].id} ${snapshot.data[index].name} "),
                subtitle: Text('pets: ${pets[0].name}  ${pets[1].name}'),
              );
            },
          );
        }
      },
    );
  }
}

class DBProvider {
  static Database _database;
  static final DBProvider db = DBProvider._();

  DBProvider._();

  Future<Database> get database async {
    // If database exists, return database
    if (_database != null) return _database;

    // If database don't exists, create one
    _database = await initDB();

    return _database;
  }

  // Create the database and the House table
  initDB() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, 'House2.db');

    return await openDatabase(path, version: 1, onOpen: (db) {},
        onCreate: (Database db, int version) async {
      await db.execute('CREATE TABLE House('
          'id INTEGER PRIMARY KEY,'
          'name TEXT,'
          'color TEXT,'
          'pets TEXT'
          ')');
    });
  }

  // Insert House on database
  createHouse(House newHouse) async {
    await deleteAllHouses();
    final db = await database;
    //final res = await db.insert('House', newHouse.toJson());

    String petsString = petToJson(newHouse.pets);
    var raw = await db.rawInsert(
        "INSERT Into House (id,name,color,pets)"
        " VALUES (?,?,?,?)",
        [newHouse.id, newHouse.name, newHouse.color, petsString]);

    print('createHouse ${raw}');
    return raw;
  }

  // Delete all Houses
  Future<int> deleteAllHouses() async {
    final db = await database;
    final res = await db.rawDelete('DELETE FROM House');

    return res;
  }

  Future<List<House>> getAllHouses() async {
    final db = await database;
    final res = await db.rawQuery("SELECT * FROM House");
    print('getAllHouses $res');
    List<House> list = [];

    if (res != null) {
      res.forEach((row) {
        print(row["name"]);
        print(row["pets"]);
        var petsString = row["pets"];
        var pets = petFromJson(petsString);
        var house = House(
            id: row["id"],
            name: row["name"],
            color: row["color"],
            pets: pets);
        list.add(house);
      });
    }
    print('list  ${list.toString()} ');
    return list;
  }
}

class HouseApiProvider {
  Future<List<House>> getAllHouses() async {
    /* var url = "http://demo8161595.mockable.io/House";
    Response response = await Dio().get(url);

    return (response.data as List).map((House) {
      print('Inserting $House');
      DBProvider.db.createHouse(House.fromJson(House));
    }).toList();*/

    String response = '''
   [{"id": 1, "name": "Smith", "color": "Green", "pets": 
[{"id": 1, "breed": "Rottweiler", "name": "Mackie", "age": 8}, {"id": 2, "breed": "Mastiff", "name": "Tanner", "age": 8}]}]
   ''';

    List<House> houseList = houseFromJson(response);
    houseList.forEach((house) {
      DBProvider.db.createHouse(house);
    });

    print('houseList ${houseList.toString()}');
    return houseList;
  }
}

List<House> houseFromJson(String str) =>
    List<House>.from(json.decode(str).map((x) => House.fromJson(x)));

String houseToJson(List<House> data) =>
    json.encode(List<dynamic>.from(data.map((x) => x.toJson())));

List<Pet> petFromJson(String str) =>
    List<Pet>.from(json.decode(str).map((x) => Pet.fromJson(x)));

String petToJson(List<Pet> data) =>
    json.encode(List<dynamic>.from(data.map((x) => x.toJson())));

class House {
  int id;
  String name;
  String color;
  List<Pet> pets;

  House({
    this.id,
    this.name,
    this.color,
    this.pets,
  });

  factory House.fromJson(Map<String, dynamic> json) => House(
        id: json["id"],
        name: json["name"],
        color: json["color"],
        pets: List<Pet>.from(json["pets"].map((x) => Pet.fromJson(x))),
      );

  Map<String, dynamic> toJson() => {
        "id": id,
        "name": name,
        "color": color,
        "pets": List<dynamic>.from(pets.map((x) => x.toJson())),
      };
}

class Pet {
  int id;
  String breed;
  String name;
  int age;

  Pet({
    this.id,
    this.breed,
    this.name,
    this.age,
  });

  factory Pet.fromJson(Map<String, dynamic> json) => Pet(
        id: json["id"],
        breed: json["breed"],
        name: json["name"],
        age: json["age"],
      );

  Map<String, dynamic> toJson() => {
        "id": id,
        "breed": breed,
        "name": name,
        "age": age,
      };
}