SQLite

SQLite

1. Definir un esquema: object FeedReaderContract { // Table contents are grouped together in an anonymous object. object FeedEntry : BaseColumns { const val TABLE_NAME = "entry" const val COLUMN_NAME_TITLE = "title" const val COLUMN_NAME_SUBTITLE = "subtitle" } }

2. Crear una base de datos con SQL, se implementan métodos que creen y mantengan la base de datos y las tablas. A continuación, puedes ver algunas instrucciones típicas que crean y borran una tabla: private const val SQL_CREATE_ENTRIES = "CREATE TABLE ${FeedEntry.TABLE_NAME} (" + "${BaseColumns._ID} INTEGER PRIMARY KEY," + "${FeedEntry.COLUMN_NAME_TITLE} TEXT," + "${FeedEntry.COLUMN_NAME_SUBTITLE} TEXT)" private const val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS ${FeedEntry.TABLE_NAME}"

3. Si se desea usar SQLiteOpenHelper, crea una subclase que anule los métodos de devolución de llamada onCreate() y onUpgrade(). También puedes implementar los métodos onDowngrade() o onOpen(), pero no son obligatorios. por ejemplo : class FeedReaderDbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) { override fun onCreate(db: SQLiteDatabase) { db.execSQL(SQL_CREATE_ENTRIES) } override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) { // This database is only a cache for online data, so its upgrade policy is // to simply to discard the data and start over db.execSQL(SQL_DELETE_ENTRIES) onCreate(db) } override fun onDowngrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) { onUpgrade(db, oldVersion, newVersion) } companion object { // If you change the database schema, you must increment the database version. const val DATABASE_

   • Para acceder a la base de datos, crea una instancia de la subclase de SQLiteOpenHelper: val dbHelper = FeedReaderDbHelper(context)

4. Ingresar indormacion: Inserta datos en la base de datos pasando un objeto ContentValues al método insert(): // Gets the data repository in write mode val db = dbHelper.writableDatabase // Create a new map of values, where column names are the keys val values = ContentValues().apply { put(FeedEntry.COLUMN_NAME_TITLE, title) put(FeedEntry.COLUMN_NAME_SUBTITLE, subtitle) } // Insert the new row, returning the primary key value of the new row val newRowId = db?.insert(FeedEntry.TABLE_NAME, null, values)

5. Leer Informacion: se usa el método query(), pasando los criterios de selección y columnas deseadas. El método combina elementos de insert() y update(), excepto que la lista de columnas define los datos que deseas recuperar (la "proyección") en lugar de datos para insertar. Los resultados de la consulta se muestran en un objeto Cursor.

   • val db = dbHelper.readableDatabase // Define a projection that specifies which columns from the database // you will actually use after this query. val projection = arrayOf(BaseColumns._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE) // Filter results WHERE "title" = 'My Title' val selection = "${FeedEntry.COLUMN_NAME_TITLE} = ?" val selectionArgs = arrayOf("My Title") // How you want the results sorted in the resulting Cursor val sortOrder = "${FeedEntry.COLUMN_NAME_SUBTITLE} DESC" val cursor = db.query( FeedEntry.TABLE_NAME, // The table to query projection, // The array of columns to return (pass null to get all) selection, // The columns for the WHERE clause selectionArgs, // The values for the WHERE clause null, // don't group the rows null, // don't filter by row groups sortOrder // The sort order )

6. Borrar Informacion:

   • // Define 'where' part of query. val selection = "${FeedEntry.COLUMN_NAME_TITLE} LIKE ?" // Specify arguments in placeholder order. val selectionArgs = arrayOf("MyTitle") // Issue SQL statement. val deletedRows = db.delete(FeedEntry.TABLE_NAME, selection, selectionArgs)

Images and files in the map