Primer SQL

Introdução ao SQLite

O curso Como desenvolver apps Android no Kotlin pressupõe que você conheça estes assuntos:

  • Bancos de dados em geral
  • Bancos de dados SQL em particular
  • A linguagem SQL usada para interagir com bancos de dados

Esta página é um resumo e uma referência rápida.

Bancos de dados SQL

Os bancos de dados SQL armazenam dados em tabelas de linhas e colunas:

  • A interseção de uma linha e uma coluna é chamada de campo.
  • Os campos contêm dados, referências a outros campos ou referências a outras tabelas.
  • Cada linha contém uma entidade. A entidade é identificada por um ID exclusivo que geralmente é usado como a chave primária.
  • Cada coluna é identificada por um nome exclusivo por tabela.

SQLite

O SQLite implementa um mecanismo de banco de dados SQL que tem as seguintes características:

  • Autossuficiência (não precisa de outros componentes)
  • Sem servidor (não precisa de back-end de servidor)
  • Sem configuração (não precisa ser configurado para seu app)
  • Transacional (as mudanças em uma única transação no SQLite ocorrem completamente ou não ocorrem)

O SQLite é o mecanismo de banco de dados mais implantado no mundo. O código-fonte do SQLite está em domínio público. Para mais detalhes, consulte o site do SQLite (link em inglês).

Exemplo de tabela

  • Um banco de dados chamado DATABASE_NAME
  • Uma tabela chamada WORD_LIST_TABLE
  • Colunas para _id, word e definition

Depois de inserir as palavras alpha e beta, em que alpha tem duas definições, a tabela pode ter esta aparência:

DATABASE_NAME

WORD_LIST_TABLE

_id

palavra

definição

1

"alfa"

"primeira letra"

2

"beta"

"segunda letra"

3

"alfa"

"partícula"

Para encontrar o que está em uma linha específica, use o _id ou recupere linhas formulando consultas que selecionem linhas da tabela ao especificar restrições.

Transações

Uma transação é uma sequência de operações realizadas como uma única unidade lógica de trabalho. Para se qualificar como transação, uma unidade lógica de trabalho precisa exibir quatro propriedades: atomicidade, consistência, isolamento e durabilidade (ACID, na sigla em inglês):

  • Atomicidade. Todas as modificações de dados de uma transação são realizadas ou nenhuma delas é realizada. A atomicidade é verdadeira mesmo que uma falha do programa, uma falha do sistema operacional ou uma falha de energia interrompa a ação da programação da mudança no disco.
  • Consistência. Quando uma transação é concluída, ela precisa deixar todos os dados em um estado consistente.
  • Isolamento. As modificações feitas por transações simultâneas precisam ser isoladas de outras modificações feitas por transações simultâneas. Uma transação reconhece os dados no estado em que estavam antes de outra transação simultânea modificá-los ou reconhece os dados após a conclusão da segunda transação. A transação não reconhece um estado intermediário.
  • Durabilidade. Depois da conclusão de uma transação, os efeitos dela ficam permanentemente em vigor no sistema. As modificações são mantidas mesmo em caso de falha no sistema.

Exemplos de transações:

  • Transferência de dinheiro de uma conta poupança para uma conta corrente.
  • Adição de um termo e uma definição no dicionário.
  • Confirmação de uma lista de mudanças na ramificação mestra.

Para mais informações sobre transações, consulte Confirmação atômica no SQLite (link em inglês).

Linguagem da consulta

Você usará a linguagem da consulta SQL para interagir com o banco de dados. As consultas podem ser muito complexas, mas há quatro operações básicas:

  • Inserção de linhas
  • Exclusão de linhas
  • Atualização de valores em linhas
  • Recuperação de linhas que atendem a determinados critérios

No Android, o objeto de acesso a dados (DAO, na sigla em inglês) oferece métodos convenientes para inserir, excluir e atualizar o banco de dados. Para ver uma descrição completa da linguagem da consulta, acesse SQL na compreensão do SQLite (link em inglês).

Estrutura da consulta

Uma consulta SQL é altamente estruturada. Exemplo de consulta:

  • SELECT word, definition FROM WORD_LIST_TABLE WHERE word="alpha"

Versão genérica do exemplo de consulta:

  • Colunas SELECT FROM tabela WHERE coluna="valor"

Partes do exemplo de consulta:

  • Colunas SELECT: selecione as colunas que serão retornadas. Use * para retornar todas as colunas.
  • Tabela FROM: especifique a tabela que enviará os resultados.
  • WHERE: palavra-chave opcional que precede condições que precisam ser atendidas. Por exemplo, coluna="valor". Os operadores comuns são =, LIKE, < e >. Para conectar várias condições, use AND ou OR.

Outras partes de consultas:

  • ORDER BY: frase opcional para classificar os resultados por uma coluna. Especifique ASC para classificar em ordem crescente e DESC para classificar em ordem decrescente. Se você não especificar uma ordem, receberá a ordem padrão, que pode ser desordenada.
  • LIMIT: palavra-chave que especifica um número limitado de resultados.

Exemplos de consultas e resultados

As consultas a seguir usam a tabela definida anteriormente:

SELECT * FROM WORD_LIST_TABLE

Recebe todas as linhas na tabela WORD_LIST_TABLE.
SELECT word, definition FROM WORD_LIST_TABLE WHERE _id > 2 Seleciona as colunas word e definition de todos os itens com id maior que 2.
Retorna

[["alfa", "partícula"]]

SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha" AND definition LIKE "%art%"

Retorna o id da palavra alpha com a substring art na definição.

[["3"]]

SELECT definition FROM WORD_LIST_TABLE ORDER BY word DESC LIMIT 1

Seleciona todas as definições. Classifica na ordem inversa e recebe a primeira linha após a classificação da lista. A classificação é de acordo com a coluna especificada, que é word. Observe que é possível classificar por uma coluna que não retornamos.

[["segunda letra"]]

SELECT * FROM WORD_LIST_TABLE LIMIT 2,1

Retorna um item a partir da posição 2. A contagem de posições começa em 1 (não zero). Retorna [["2", "beta", "second letter"]]

Confira como criar e consultar bancos de dados neste site do SQL Fiddle (link em inglês).

Consultas do SQLite no Android

Você pode enviar consultas ao banco de dados SQLite do sistema Android como consultas brutas ou como parâmetros.

O método rawQuery(String sql, String[] selectionArgs) executa o SQL fornecido. O método retorna um Cursor do conjunto de resultados. A tabela a seguir mostra como os dois primeiros exemplos de consulta acima ficariam como consultas brutas.

1

Consulta de string = "SELECT * FROM WORD_LIST_TABLE";
rawQuery(query, null);

2

consulta = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? ";
String[] selectionArgs = new String[]{"2"}
rawQuery(query, selectionArgs) ;

O método query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) consulta a tabela fornecida. O método retorna um Cursor sobre o conjunto de resultados. Veja a seguir uma consulta que mostra como preencher os argumentos:

SELECT * FROM WORD_LIST_TABLE
WHERE word="alpha"
ORDER BY word ASC LIMIT 2,1;

A consulta retorna o resultado a seguir:

[["alfa", "partícula"]]

Exemplo de argumentos que podem ser usados:

String table = "WORD_LIST_TABLE"
String[] columns = new String[]{"*"};
String selection = "word = ?"
String[] selectionArgs = new String[]{"alpha"};
String groupBy = null;
String having = null;
String orderBy = "word ASC"
String limit = "2,1"

query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

Observação: no código real, você não criaria variáveis para valores null. Consulte a documentação do Android SQLiteDatabase para ver versões desse método com parâmetros diferentes.

Cursores

Um cursor é um ponteiro para uma linha de dados estruturados. Pense em um cursor como um ponteiro para uma linha da tabela.

Uma consulta retorna um objeto Cursor, que aponta para o primeiro elemento no resultado da consulta. A classe Cursor fornece métodos para mover o cursor pelo resultado da consulta, além de métodos para receber os dados das colunas de cada linha do resultado.

Quando um método retorna um objeto Cursor, você itera o resultado, extrai os dados, faz algo com os dados e fecha o cursor para liberar a memória.

Saiba mais