Android Developer Fundamentals V 2 Storing Data Android

Android Developer Fundamentals V 2 Storing Data Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 1

Contents ● SQLite Database ● Queries Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 2

This is only a refresher This course assumes that you are familiar with ● Databases in general ● SQL databases in particular ● SQL query language Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 3

SQLite Database Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 4

SQL Databases ● Store data in tables of rows and columns (spreadsheet…) ● Fields contain data, references to other fields, or references to other tables ● Rows are identified by unique IDs ● Column names are unique per table Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 5

Tables WORD_LIST_TABLE _id word definition 1 "alpha" "first letter" 2 "beta" "second letter" 3 "alpha" "particle" Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 6

SQLite software library Implements SQL database engine that is ● self-contained (requires no other components) ● serverless (requires no server backend) ● zero-configuration (does not need to be configured for your application) ● transactional (changes within a single transaction in SQLite either occur completely or not at all) Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 7

Queries Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 8

SQL basic operations ● Insert rows ● Delete rows ● Update values in rows ● Retrieve rows that meet given criteria Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 9

SQL Query ● SELECT word, description FROM WORD_LIST_TABLE WHERE word="alpha“ Generic ● SELECT columns FROM table WHERE column="value" Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 10

Sample queries 1 SELECT * FROM WORD_LIST_TABLE Get the whole table 2 SELECT word, definition FROM WORD_LIST_TABLE WHERE _id > 2 Returns [["alpha", "particle"]] Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 11

More sample queries 3 SELECT _id FROM Return id of word alpha with WORD_LIST_TABLE substring "art" in definition WHERE word="alpha" AND [["3"]] definition LIKE "%art%" 4 SELECT * FROM WORD_LIST_TABLE ORDER BY word DESC LIMIT 1 Android Developer Fundamentals V 2 Sort in reverse and get first item. Sorting is by the first column (_id) [["3", "alpha", "particle"]] SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 12

Last sample query 5 SELECT * FROM Returns 1 item starting at position 2. WORD_LIST_TABLE Position counting starts at 1 (not LIMIT 2, 1 zero!). Returns [["2", "beta", "second letter"]] Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 13

raw. Query() String query = "SELECT * FROM WORD_LIST_TABLE"; raw. Query(query, null); query = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? "; String[] selection. Args = new String[]{"2"} raw. Query(query, selection. Args); Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 14

query() SELECT * FROM WORD_LIST_TABLE WHERE word="alpha" ORDER BY word ASC LIMIT 2, 1; Returns: [["alpha", "particle"]] String table = "WORD_LIST_TABLE" String[] columns = new String[]{"*"}; String selection = "word = ? " String[] selection. Args = new String[]{"alpha"}; String group. By = null; String having = null; String order. By = "word ASC" String limit = "2, 1" query(table, columns, selection. Args, group. By, having, order. By, limit); Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 15

Cursors Queries always return a Cursor object Cursor is an object interface that provides random read-write access to the result set returned by a database query ⇒ Think of it as a pointer to table rows Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 16

SQLite. Open. Helper A helper class to manage database creation and version management. public class My. SQLite. Data. Base extends SQLite. Open. Helper { @Override public void on. Create(SQLite. Database db) { } @Override public void on. Upgrade(SQLite. Database db, int old. Version, int new. Version) { } } Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 17

SQLite. Open. Helper public class My. SQLite. Data. Base extends SQLite. Open. Helper { private static final int DATABASE_VERSION = 1; private static final String DATABASE_NAME = "My. DB"; @Override public void on. Create(SQLite. Database db) { } @Override public void on. Upgrade(SQLite. Database db, int old. Version, int new. Version) { } } Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 18

SQLite. Open. Helper public class My. SQLite. Data. Base extends SQLite. Open. Helper { private static final int DATABASE_VERSION = 1; private static final String DATABASE_NAME = "My. DB"; public My. SQLite. Data. Base(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void on. Create(SQLite. Database db) { } @Override public void on. Upgrade(SQLite. Database db, int old. Version, int new. Version) { } } Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 19

Create and Upgrade Data. Base public void on. Create(SQLite. Database db) { String CREATE__TABLE = "CREATE TABLE words ( id INTEGER PRIMARY KEY AUTOINCREMENT, word TEXT, meaning TEXT )"; db. exec. SQL(CREATE__TABLE); } public void on. Upgrade(SQLite. Database db, int old. Version, int new. Version) { db. exec. SQL("DROP TABLE words"); this. on. Create(db); } Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 20

SQLite Data Types • INTEGER: The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. • REAL: The value is a floating point value, stored as an 8 -byte IEEE floating point number. • TEXT: The value is a text string, stored using the database encoding (UTF-8, UTF-16 BE or UTF-16 LE) • BLOB: The value is a blob of data, stored exactly as it was input. Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 21

Add a Record Content. Values class is used to store a set of key- values public void add(String w, String m){ SQLite. Database db = this. get. Writable. Database(); Content. Values values = new Content. Values(); values. put("word", w); values. put("meaning", m); db. insert("words", null, values); db. close(); } Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 22

Delete Record public void clear(int id ) { SQLite. Database db = this. get. Writable. Database(); db. delete("words", "id=? ", new String[]{id+""}); db. close(); } public void clear. All() { SQLite. Database db = this. get. Writable. Database(); db. delete("words", null); db. close(); } Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 23

Query a record public String get(int id){ SQLite. Database db = this. get. Readable. Database(); Cursor cursor = db. raw. Query( "select * from words where id=? " , new String[]{id+""}); if (cursor != null && cursor. move. To. First()){ String ret=""; ret=cursor. get. String( 0)+" : "+ cursor. get. String(1)+": "+cursor. get. String(2)+"n"; db. close(); return ret; } else {db. close(); return "Record not Found. n"; } } Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 24

Records List public String list(){ SQLite. Database db = this. get. Readable. Database(); Cursor cursor = db. query("words", new String[]{"id", "word", "meaning"}, null, null); if (cursor != null && cursor. move. To. First()){ String ret=""; ret=cursor. get. String( 0)+" : "+ cursor. get. String(1)+": "+cursor. get. String(2)+"n"; while(!cursor. is. Last()){ cursor. move. To. Next(); ret+=cursor. get. String( 0)+" : "+ cursor. get. String(1)+": "+cursor. get. String(2)+"n"; } db. close(); return ret; } else return "Data Base Is Empty. n"; } Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 25

Object of DB class My. SQLite. Data. Base db=new My. SQLite. Data. Base(this); db. clear(3); Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 26

END Android Developer Fundamentals V 2 SQLite Primer This work is licensed under a Creative Commons Attribution 4. 0 International License. 27