android and sqlitecdn.cs76.net/2012/spring/sections/6/section6.pdf · android and sqlite gloria...
TRANSCRIPT
![Page 1: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/1.jpg)
Android and SQLite Gloria Hedlund Teaching Fellow
CS76 – Building Mobile Applications Harvard Extension School
![Page 2: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/2.jpg)
SQLite
• SQL = Structured Query Language
• Available on every Android device
• Automatically managed by the Android platform
• Requires only a little memory at runtime (approx 250 Kbyte)
![Page 3: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/3.jpg)
Datatypes
• Text (String)
• Integer (long)
• Real (double)
• SQLite does not verify that the type written to each column is of the defined type
• http://www.sqlite.org/datatype3.html
![Page 4: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/4.jpg)
android.database.sqlite
• SQLiteOpenHelper class • onCreate() creates a new database
• onUpgrade() updates database schema
• SQLiteDatabase class • insert(), update(), delete()
• execSQL() – execute SQL directly
• rawQuery() – parameter is SQL statement
• query() – parameters specify the SQL query
![Page 5: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/5.jpg)
SQL statements
• To create a table • CREATE TABLE table (col1 type, col2 type, …);
• To insert into a table • INSERT INTO table (col1, col2..) VALUES (val1, val2..);
• To update a table • UPDATE table SET col2 = newVal2 WHERE col1 = val1;
• To delete from a table • DELETE FROM table WHERE col1 = val1;
![Page 6: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/6.jpg)
Demo Time
• MyRecordCollection • Simple app for record (as in vinyl/LP) collectors
• Check out the source code • https://www.cs76.net/Sections
![Page 7: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/7.jpg)
Data Retrieval
• Create a String [] of result_columns for the columns of data you want to retrieve • String[] cols = new String[] { _id, first_col, … nth_col };
• Create a cursor object to retrieve results of a query • Cursor cursor = this.db.query(table, cols, null, null, null,
null, null);
• WHAT ARE ALL OF THESE NULLS?
![Page 8: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/8.jpg)
query(tablename, null, null, null, null, null, null) String tablename The table name
String[] columnNames List of which table columns to return. Null returns all.
String whereClause Filter for data selection. Null selects all data.
String[] selectionArgs If there are ?s in the whereClause this is where you put the values
String[] groupBy Filter for grouping rows. Null means no grouping
String[] having Filter for the groups. Null means no filter.
String[] orderBy Table column used to sort the data. Null means no sorting.
![Page 9: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/9.jpg)
Data Retrieval cont.
• A query() returns a Cursor object
• getCount() • number of elements returned in Cursor
• moveToFirst(), moveToNext() • Move between rows in the Cursor
• getColumnIndexOrThrow(String) • Gets column index for a passed column name
![Page 10: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/10.jpg)
Displaying Data
• List<Object> • Create a list of your stored objects
• ArrayAdapter<Object> • Bind each object to an item layout
• LayoutInflater • Instantiates an item layout file into View objects
• ViewHolder • Class that will keep references to layout ids for faster
performance / efficiency
![Page 11: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/11.jpg)
Command line Access
• adb command located in sdk/platform-tools
• Run command: adb shell OR ./adb shell (Mac) • (your device or emulator must be running)
• Go to the data directory: cd /data/data
• Go to your (app) package: cd package name
• Go to databases: cd databases • (you need to have created a database)
• Connect to your database: sqlite3 databasename.db
![Page 12: Android and SQLitecdn.cs76.net/2012/spring/sections/6/section6.pdf · Android and SQLite Gloria Hedlund Teaching Fellow CS76 – Building Mobile Applications Harvard Extension School](https://reader030.vdocuments.us/reader030/viewer/2022040914/5e8bd9cf0c5e67245e204f4f/html5/thumbnails/12.jpg)
Command line sqlite
• sqlite> .help
• .databases
• .tables • filter - .tables my gives all tables with “my” in name
• select * from tablename;
• delete from tablename where key = value;
• .exit or .quit