How to display async sqlite query inside a Widget in Flutter

Question

Good morning!

I have an application with sqlite. The idea is to take the value entered by the user and display on the homepage through sum. I can display the value correctly by android studio console, but I can’t display the value in a ui widget.

how can I do this?

Pagina1.Dart

import 'package:flutter/material.dart';
import 'package:king_app/pages/inserir_receita.dart';

import 'estrutura/Database.dart';

    class Pagina1 extends StatelessWidget {
      final dbHelper = DatabaseHelper.instance;
      int value;

      void _query() async {
        var db = await DatabaseHelper.instance.database;
        // raw query
        var result = await db.rawQuery('SELECT SUM(valor) FROM lancamentos');
        int value = result[0]["SUM(valor)"];
      }

      @override
      Widget build(BuildContext context) {
    
        _query();
        return new Scaffold(
          appBar: new AppBar(
            title: new Text("Página Inicial",
            style: TextStyle(
              fontSize: 18,
            ),),
            backgroundColor: Color(0xFF11c76f),
            elevation: 0,
            actions: <Widget>[
              IconButton(
                icon: Icon(Icons.search),
                onPressed: (){},
              )
            ],
          ),
    
          body: Column(
            children: <Widget>[
              Container(
                width: MediaQuery.of(context).size.width,
                height: 123,
                decoration: BoxDecoration(
                  color: Color(0xFF11c76f)
                ),
                child: Column(
                  children: <Widget>[
                    Padding(
                      padding: EdgeInsets.only(top: 5),
                      child: Text("R\$: $value",
                      style: TextStyle(
                        fontSize: 35,
                        color: Colors.white
                      ),),
                    ),
                    Padding(
                      padding: EdgeInsets.only(top: 3),
                      child: Text('Saldo atual',
                      style: TextStyle(
                        fontSize: 18,
                        color: Colors.white
                      ),),
                    )
                  ],
                ),
              ),
            ],
          ),
          backgroundColor: Colors.white10,
          floatingActionButton: FloatingActionButton(
            onPressed: () {
              Navigator.push(
                context,
                MaterialPageRoute(
                  builder: (context) => InserirReceita(),
                ),
              );
            },
            child: Icon(Icons.add),
            backgroundColor: Color(0xFF11c76f),
          ),
        );
      }
    }

I want to display the value that is returned in the _query function in body as text.

Right here:

child: Column(
                  children: <Widget>[
                    Padding(
                      padding: EdgeInsets.only(top: 5),
                      child: Text("R\$: $value",
                      style: TextStyle(
                        fontSize: 35,
                        color: Colors.white
                      ),),
                    ),

How should I do? no $value was to return a value but I only get null, I will leave the rest of my code below that makes the connection with sqlite.

Database.Dart

import 'dart:io';

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

class DatabaseHelper {

  static final _databaseName = "MyDatabase.db";
  static final _databaseVersion = 1;

  static final table = 'lancamentos';

  static final colunaId = '_id';
  static final colunaValor = 'valor';
  static final colunaData = 'data';
  static final colunaDescri = 'descri';
  static final colunaTipo = 'tipo';

  // make this a singleton class
  DatabaseHelper._privateConstructor();
  static final DatabaseHelper instance = DatabaseHelper._privateConstructor();

  // only have a single app-wide reference to the database
  static Database _database;
  Future<Database> get database async {
    if (_database != null) return _database;
    // lazily instantiate the db the first time it is accessed
    _database = await _initDatabase();
    return _database;
  }

  // this opens the database (and creates it if it doesn't exist)
  _initDatabase() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, _databaseName);
    return await openDatabase(path,
        version: _databaseVersion,
        onCreate: _onCreate);
  }

  // SQL code to create the database table
  Future _onCreate(Database db, int version) async {
    await db.execute('''
          CREATE TABLE $table (
            $colunaId INTEGER PRIMARY KEY,
            $colunaValor NUMERIC,
            $colunaData TEXT,
            $colunaTipo TEXT,
            $colunaDescri TEXT
          )
          ''');
  }

  // Helper methods

  // Inserts a row in the database where each key in the Map is a column name
  // and the value is the column value. The return value is the id of the
  // inserted row.
  Future<int> insert(Map<String, dynamic> row) async {
    Database db = await instance.database;
    return await db.insert(table, row);
  }

  // All of the rows are returned as a list of maps, where each map is
  // a key-value list of columns.
  Future<List<Map<String, dynamic>>> queryAllRows() async {
    Database db = await instance.database;
    return await db.query(table);
  }

  // All of the methods (insert, query, update, delete) can also be done using
  // raw SQL commands. This method uses a raw query to give the row count.
  Future<int> queryRowCount() async {
    Database db = await instance.database;
    return Sqflite.firstIntValue(await db.rawQuery('SELECT COUNT(*) FROM $table'));
  }

  // We are assuming here that the id column in the map is set. The other
  // column values will be used to update the row.
  Future<int> update(Map<String, dynamic> row) async {
    Database db = await instance.database;
    int id = row[colunaId];
    return await db.update(table, row, where: '$colunaId = ?', whereArgs: [id]);
  }

  // Deletes the row specified by the id. The number of affected rows is
  // returned. This should be 1 as long as the row exists.
  Future<int> delete(int id) async {
    Database db = await instance.database;
    return await db.delete(table, where: '$colunaId = ?', whereArgs: [id]);
  }

}

Answer 1

The value value is null because you are not working properly asynchronously in your code.

His method _query() is asynchronous. You’re calling him on build(), to be executed and reach exactly on this line await db.rawQuery('SELECT SUM(valor) FROM lancamentos');, he will already return one Future<void> and the build() will continue the execution at that time int value will be void.

The first mistake is calling the _query() in the build(), every time it runs to update the widgets tree, you will do the same query in the database. Then you can change your widget to StatefulWidget and make that call in the method initState().

However, how do you want to print the value on the screen as soon as it is picked up from the form bank asynchronous, recommend using the widget Futurebuilder. It will wait for an asynchronous request and update when it is completed.

Instead of:

Padding(
  padding: EdgeInsets.only(top: 5),
  child: Text("R\$: $value",
  style: TextStyle(
    fontSize: 35,
    color: Colors.white
  ),),
),

Seria:

Future<int> _query() async {
  var db = await DatabaseHelper.instance.database;
  var result = await db.rawQuery('SELECT SUM(valor) FROM lancamentos');
  return result[0]["SUM(valor)"];
}

FutureBuilder<int>(
  future: _query(),
  builder: (BuildContext context, AsyncSnapshot<int> snapshot) {
    final state = snapshot.connectionState;
    final value = 'Carregando..';

    if(state == ConnectionState.done) {
      if(snapshot.hasError) {
        value = 'Erro..';
      } else {
        value = snapshot.data;
      }
    }

    return Padding(
      padding: EdgeInsets.only(top: 5),
      child: Text("R\$: $value",
        style: TextStyle(
        fontSize: 35,
        color: Colors.white
      ),),
    );
  },
)