Android y SQLite

En este tutorial descubriremos como usar bases de datos en SQLite para no perder la información de nuestras Aplicaciones Android. A medida que vayamos avanzando veremos la utilidad de clases como SQLiteOpenHelper, SQLiteDatabase, Cursor, CursorAdapter y de Herramientas como sqlite3 y SQLite Browser.

Asumimos que tienes conocimientos básicos en SQLite

Con el fin de facilitar tu aprendizaje usaremos un ejemplo de guía, que te permitirá ir la práctica en paralelo a las teorías y conceptos estudiados. Puedes descargar los recursos desde aquí:

Descargar CódigoTenlo a mano para comprender los siguientes temas:

¿Que es SQLite?

Diseño de la Aplicación

Crear una Base de Datos

Crear el Esquema de la Base de Datos

CRUD
Insertar Registros

Consultar Registros

Borrar Registros

Actualizar Registros

sqlite3

SQLite Browser

La Clase CursorAdapter

La Clase SimpleCursorAdapter

limitaciones en algunas operaciones. Por ejemplo, no se puede implementar las clausulas FULL OUTER JOIN y RIGHT OUTER JOIN. Aunque en la mayoría de casos esto no afectará, siempre habrán otras alternativas como JavaDB o MongoDB.

Para comenzar a probar nuestras bases de datos, quisiera explicarte un poco mas sobre el ejemplo práctico que vamos a desarrollar. Se trata de una aplicación llamada "Quotilius". Cuyo objetivo es guardar una lista de frases celebres.

Debemos almacenar el cuerpo de la frase y el autor que la trajo al mundo. El siguiente sería un bosquejo rápido de como se vería la tabla de frases:

_id body author
1 "El ignorante afirma, el sabio duda y reflexiona" Aristóteles
2 "Hay derrotas que tienen mas dignidad que la victoria" Jorge Luis Borges
3 "Si buscas resultados distintos, no hagas siempre lo mismo" Albert Einstein
4 "Donde mora la libertad, allí está mi patria" Benjamin Franklin
5 "Ojo por ojo y todo el mundo acabará ciego" Mahatma Gandhi
La idea es proporcionarle al usuario la posibilidad de añadir nuevas frases desde la Action Bar.

Action Button "Añadir". Observemos su previsualización:

La comunicación entre ambas actividades se realiza a través de un Intent explicito. Este nos permite obtener los datos que el usuario ingresó en Form para poder usarlos en Main e ingresarlos en la base de datos y al mismo tiempo actualizar nuestro ListView.

Lo primero que harás será enviar un Intent para ejecutar la actividad Form a través de un canal de comunicación:

//Código de envío
public final static int ADD_REQUEST_CODE = 1;
...
//Iniciando la actividad Form
Intent intent = new Intent(this, Form.class);
//Inicio de la actividad esperando un resultado
startActivityForResult(intent, ADD_REQUEST_CODE);

Una vez el usuario este en la actividad y haya presionado el botón de "Guardar", devuelves los textos en los textviews por el mismo canal hacia Main:

//Obtener los datos de los campos
EditText quoteField = (EditText) findViewById(R.id.quoteField);
EditText authorField = (EditText) findViewById(R.id.authorField);
//Nuevo Intent con Extras
Intent backData = new Intent();
backData.putExtra("body", quoteField.getText().toString());
backData.putExtra("author", authorField.getText().toString());
//Enviar la información
setResult(RESULT_OK, backData);

Luego recibes los datos con el método onActivityResult() y realizas las operaciones necesarias para guardar los datos en la base de datos:

@Override
protected void onActivityResult(int requestCode, int resultCode, Intent data) {
super.onActivityResult(requestCode, resultCode, data);
if (requestCode == ADD_REQUEST_CODE) {
if (resultCode == RESULT_OK) {
//Insertar registro en la base de datos
}
}
}

Hasta aquí hicimos un bosquejo rápido de la comunicación entre ambas actividades. Si te perdiste y aún no sabes como funcionan los Intents, entonces visita nuestro tutorial sobre Intents en Android para esclarecer mas este proceso de transmisión.

La documentación de Android nos recomienda crear una clase llamada Contract Class. Esta clase guarda como constantes todas las características de la base de datos, ademas del código SQL necesario para la creación, inserción, actualización, etc.

El esquema de la aplicación Quotilius está basado en un diseño simple, el cual se compone de una tabla que llamarás Quotes con tres atributos: _id, cuerpo (body) y autor (author).

Ahora solo queda implementar ese esquema en una clase que llamaremos QuotesDataSource. Veamos:

public class QuotesDataSource {
//Metainformación de la base de datos
public static final String QUOTES_TABLE_NAME = "Quotes";
public static final String STRING_TYPE = "text";
public static final String INT_TYPE = "integer";
//Campos de la tabla Quotes
public static class ColumnQuotes{
public static final String ID_QUOTES = BaseColumns._ID;
public static final String BODY_QUOTES = "body";
public static final String AUTHOR_QUOTES = "author";
}
//Script de Creación de la tabla Quotes
public static final String CREATE_QUOTES_SCRIPT =
"create table "+QUOTES_TABLE_NAME+"(" +
ColumnQuotes.ID_QUOTES+" "+INT_TYPE+" primary key autoincrement," +
ColumnQuotes.BODY_QUOTES+" "+STRING_TYPE+" not null," +
ColumnQuotes.AUTHOR_QUOTES+" "+STRING_TYPE+" not null)";
//Scripts de inserción por defecto
public static final String INSERT_QUOTES_SCRIPT =
"insert into "+QUOTES_TABLE_NAME+" values(" +
"null," +
"El ignorante afirma, el sabio duda y reflexiona," +
"Aristóteles)," +
"(null," +
"Hay derrotas que tienen mas dignidad que la victoria," +
"Jorge Luis Borges)," +
"(null," +
"Si buscas resultados distintos, no hagas siempre lo mismo," +
"Albert Einstein)," +
"(null," +
"Donde mora la libertad, allí está mi patria," +
"Benjamin Franklin)," +
"(null," +
"Ojo por ojo y todo el mundo acabará ciego," +
"Mahatma Gandhi)";
private QuotesReaderDbHelper openHelper;
private SQLiteDatabase database;
public QuotesDataSource(Context context) {
//Creando una instancia hacia la base de datos
openHelper = new QuotesReaderDbHelper(context);
database = openHelper.getWritableDatabase();
}
}

En el anterior código podemos notar los siguientes detalles:

Creamos una constante para el nombre de la tabla llamada QUOTES_TABLE_NAME y otras dos para el tipo de datos que usaremos( STRING_TYPE e INT_TYPE).

Creamos la clase interna ColumnQuotes para guardar el nombre de las columnas de la tabla Quotes. En el caso del campo ID_QUOTES usamos una constante BaseColumns._ID de Android con el valor "_id".

Creamos la constante INSERT_QUOTES_INSERT tipo String para guardar una sentencia SQL para crear la tabla.

Creamos la constante INSERT_QUOTES_SCRIPT tipo String para insertar todos los datos iniciales de nuestra tabla.Como ves, es una clase muy completa que nos proporcionará flexibilidad al realizar operaciones sobre la base de datos. Estas declaraciones facilitan la adaptación del esquema si en algún momento cambian los datos de las tablas o columnas.

Adicional a todas estas definiciones tenemos dos variables privadas: openHelper y database. Ambas las usaremos en el constructor para acceder a la base de datos con el método getWritableDatabase(), el cual retorna en una instancia de SQLiteDatabase que nos permitirá leer y modificar la información de la base de datos directamente.

Crear la Base de Datos

Una vez terminado nuestro Esquema, procedemos a implementar los métodos onCreate() y onUpgrade() de nuestra clase QuotesReaderDbHelper:

public class QuotesReaderDbHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "Quotes.db";
public static final int DATABASE_VERSION = 1;
public QuotesReaderDbHelper(Context context){
super(context,DATABASE_NAME,null,DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
//Crear la tabla Quotes
db.execSQL(QuotesDataSource.CREATE_QUOTES_SCRIPT);
//Insertar registros iniciales
db.execSQL(QuotesDataSource.INSERT_QUOTES_SCRIPT);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//Añade los cambios que se realizarán en el esquema
}
}

Lo primero que hicimos fue crear atributos de clase para el nombre de la base de datos y la versión. Para crear la tabla Quotes llamamos al método execSQL()de SQLiteDataBase.

Este método ejecuta una sola sentencia SQL que no retorne en filas. Por lo que el comando SELECT no es posible usarlo dentro de él.

Evita ejecutar múltiples sentencias en una sola invocación del método execSQL(). Puede que se ejecute la primera, pero las otras no surtirán efecto.

Ahora solo queda por instancear un objeto QuotesDataSource en Main para que se cree nuestra base de datos:

//Crear nuevo objeto QuotesDataSource
dataSource = new QuotesDataSource(this);

Al ejecutar la aplicación se supone que la base de datos habrá sido creada con éxito en el sistema de archivos de Android. Pero...¿Como comprobar que así fue?...la respuesta la encontramos en el uso de las siguientes herramientas.

página oficial de SQLite, pero tanto como la distribución de Android y Android Studio ya la traen consigo.

Antes de ejecutarla en el dispositivo, primero usaremos la herramienta DDMS (Dalvik Debug Monitor Server) de Android SDK, la cual permite visualizar las características del dispositivo que se está ejecutando. En ella podemos visualizar estadísticas de rendimiento, monitorear recursos y navegar por el sistema de archivos.

Si deseas ejecutarla solo presiona el siguiente ícono en Android Studio:

Ahora dirígete a la pestaña "File Explorer"

Como ves, se visualizan todos los directorios que se encuentran en el dispositivo. Así que para ver si existe nuestro archivo de base de datos, iremos a la ruta de la cual hablamos al inicio /data/data/<paquete>/databases/Quotes.db

Si todo salió bien, veremos nuestro archivo Quotes.db. Procede a guárdalo con el botón de la parte superior derecha denominado "Pull a file from the device". En la siguiente sección veremos algo interesante con él.

Ya que hemos comprobado que existe nuestra base de datos, iniciaremos sqlite3 dentro del dispositivo. Sigue los siguientes pasos:

Paso 1

Inicia el terminal de Windows (cmd) o usa la pestaña "Terminal" de Android Studio:

Paso 2

Navega hasta el directorio platform-tools del SDK de Android. En mi caso la dirección es: C:\Users\James\AppData\Local\Android\android-studio\sdk\platform-tools. Recuerda que para navegar a través de carpetas en DOS se utiliza el comando cd.

Paso 3

Una vez hayas encontrado el directorio, digita la siguiente linea de comandos:

adb shell

Este comando conecta remotamente la consola de comandos del dispositivo Android con tu consola local. Cuando ya estés conectado a la consola del AVD, verás en el terminal algo como esto:

root@android:/ #

Paso 4

Inicia sqlite3 en el dispositivo con el siguiente comando:

root@android:/ # sqlite3 data/data/TUPAQUETE/databases/Quotes.db

La anterior instrucción accede a sqlite3 y al mismo tiempo le pide que abra la base de datos expuesta en el directorio especificado. Si accedió a la base de datos verás los siguientes mensajes:

SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Paso 5

Usa el comando .schema de sqlite3 para ver el resumen del esquema de la base de datos Quotes:

CREATE TABLE Quotes(_id integer primary key autoincrement,body text not null,author text not null);
CREATE TABLE android_metadata (locale TEXT);

El Esquema muestra que nuestra tabla Quotes ha sido creada correctamente. La tabla llamada android_metadata es parte de la metainformación de la base de datos, por lo que siempre la encontrarás.

Otra forma de comprobar el esquema de nuestra base de datos es usar sqlite3.exe en nuestro equipo local.

Ve a la ruta para encontrar la carpeta platform-tools del SDK de Android y ejecuta la aplicación. Luego usa .open para abrir el archivo en una ruta especificada o copia y pega el archivo Quotes.db en la carpeta:

sqlite>.open Quotes.db

Luego usa .schema y averigua su contenido.

SQLite Browser es una opción que te gustaría considerar. Se trata de una editor para archivos de bases de datos SQLite de código abierto y super sencillo de usar.

Solo basta con iniciarlo en tu pc y arrastrar el archivo "Quotes.db" a su editor. Inmediatamente nos mostrará el esquema en forma de tablas con interfaz gráfica de usuario, ademas de permitirnos editar la estructura y ejecutar sentencias SQL dentro de ella.

Aprender más sobre la clausula WHERE

Ahora, existe otro método alternativo para realizar consultas llamado rawQuery(). Con él debes crear un String que contenga todo el código SQL de la consulta y lo pasamos como parámetro. Veamos:

database.rawQuery("select * from " + QUOTES_TABLE_NAME, null);

Si deseas crear una consulta generalizada usa el placeholder "?" en la clausula WHERE. Luego asignamos los valores a cada incógnita en el segundo parámetro:

String query = "select * from " + QUOTES_TABLE_NAME + "WHERE _id=?";
database.rawQuery(query, new String[]{"3"});

Tanto query() como rawQuery() retornan un objeto de tipo Cursor. Este objeto es un apuntador al conjunto de valores obtenidos de la consulta. Al inicio el cursor apunta a una dirección previa a la primera fila. La idea es leer cada fila moviendo el cursor a la fila siguiente y así sucesivamente.

Emplea el método booleano moveToNext() para recorrer el cursor. Su cometido es apuntar al siguiente elemento del cursor, el cual retorna en true si fue posible o false si ya no existen mas elementos. Este concepto es similar a cuando vimos cursores en SQL Server o en MySQL. Donde recorríamos cada elemento con un bucle while hasta que ya no existieran mas elementos que referenciar.

Veamos:

while(c.moveToNext()){
String body = c.getString(ColomunQuotes.BODY_QUOTES);
//Acciones con el valor obtenido
}

Como ves, usamos métodos "get" para obtener el valor de cada columna a través del nombre o clave. Por ejemplo, al obtener el cuerpo de la frase usamos getString() debido a que su tipo en la tabla es text. Si fueses a obtener el código, entonces usas getInt().

Puedes aprovechar este nuevo concepto e implementar un nuevo método en la clase QuotesDataSource llamado getAllQuotes(). Su objetivo es retornar en un cursor todas las filas de la tabla Quotes, lo que aísla la complejidad de la operación sobre la base de datos de