Understanding SQLite and Data Storage with Room in Android Development

 
 
1
 
1
 
Storing Data
with Room
 
Lesson 10
 
1
 
Android Developer Fundamentals V2
 
 
10.0 SQLite Primer
 
2
 
 
Contents
 
 
 
SQLite Database
Queries
 
3
 
 
This is only a refresher
 
 
4
 
This course assumes that you are familiar with
Databases in general
SQL databases in particular
SQL query language
 
This chapter is a refresher and quick reference
 
 
5
 
SQLite
Database
 
 
 
SQL Databases
 
Store data in tables of rows and columns (spreadsheet…)
Field = intersection of a row and column
Fields contain data, references to other fields, or references
to other tables
Rows are identified by unique IDs
Column names are unique per table
 
6
 
 
Tables
 
 
7
 
 
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)
 
 
8
 
 
What is a transaction?
 
A transaction is a sequence of operations performed as a
single logical unit of work.
A logical unit of work must have four properties
atomicity
consistency
isolation
durability
 
9
 
 
All or nothing
 
All changes within a single transaction in SQLite either occur
completely or not at all, even if the act of writing the change
out to the disk is interrupted by
program crash
operating system crash
power failure.
 
10
 
 
ACID
 
Atomicity
—All or no modifications are performed
Consistency
—When transaction has completed, all data is in a
consistent state
Isolation
—Modifications made by concurrent transactions must
be isolated from the modifications made by any other
concurrent transactions
Durability
—After a transaction has completed, its effects are
permanently in place in the system
 
11
 
 
12
 
Queries
 
 
 
SQL basic operations
 
 
13
 
Insert rows
Delete rows
Update values in rows
Retrieve rows that meet given criteria
 
 
 
SQL Query
 
 
14
 
SELECT word, description
FROM WORD_LIST_TABLE
WHERE word="alpha"
Generic
SELECT columns
FROM table
WHERE column="value"
 
 
SELECT columns FROM table
 
 
15
 
SELECT columns
Select the columns to return
Use * to return all columns
 
FROM table
—specify the table from which to get results
 
 
WHERE column="value"
 
 
16
 
WHERE
—keyword for conditions that have to be met
 
column="value"
—the condition that has to be met
common operators: =, LIKE, <, >
 
 
AND, ORDER BY, LIMIT
 
 
17
 
SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha"
AND
 definition LIKE "%art%" 
ORDER BY word DESC LIMIT 1
 
AND, OR
—connect multiple conditions with logic operators
ORDER BY
—omit for default order, or ASC for ascending,
DESC for descending
LIMIT
—get a limited number of results
 
 
Sample queries
 
18
 
 
More sample queries
 
19
 
 
Last sample query
 
20
 
 
rawQuery()
 
21
 
String query = "SELECT * FROM WORD_LIST_TABLE";
rawQuery(query, null);
 
query = "SELECT word, definition FROM
WORD_LIST_TABLE WHERE _id> ? ";
 
String[] selectionArgs = new String[]{"2"}
rawQuery(query, selectionArgs);
 
 
 
query()
 
22
 
 
Cursors
 
 
23
 
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
 
You will learn more about cursors in the following chapters
 
 
Learn more
 
 
24
 
SQLite website
Full description of the Query Language
SQLite
 class
Cursor
 class
 
 
What's Next?
 
25
Concept Chapter: 
10.0 SQLite Primer
No Practical
 
 
END
 
 
26
 
Slide Note
Embed
Share

SQLite is a self-contained, serverless, and zero-configuration SQL database engine used in Android development. This primer covers how data is stored in tables, columns, and rows, and provides a refresher on SQL databases, assuming familiarity with SQL query language. Room library simplifies SQLite interactions in Android apps.


Uploaded on Jul 17, 2024 | 2 Views


Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript


  1. Android Developer Fundamentals V2 Storing Data with Room Lesson 10 This work is licensed under a Creative Commons Attribution 4.0 International License. License. This work is licensed under a Creative Commons Attribution 4.0 International SQLite Primer SQLite Primer Android Developer Fundamentals V2 Android Developer Fundamentals V2 1 1 1

  2. 10.0 SQLite Primer This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 2

  3. Contents SQLite Database Queries This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 3

  4. This is only a refresher This course assumes that you are familiar with Databases in general SQL databases in particular SQL query language This chapter is a refresher and quick reference This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 4

  5. SQLite Database This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 5

  6. SQL Databases Store data in tables of rows and columns (spreadsheet ) Field = intersection of a row and column Fields contain data, references to other fields, or references to other tables Rows are identified by unique IDs Column names are unique per table This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 6

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

  8. 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) This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 8

  9. What is a transaction? A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must have four properties atomicity consistency isolation durability This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 9

  10. All or nothing All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by program crash operating system crash power failure. This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 10

  11. ACID Atomicity All or no modifications are performed Consistency When transaction has completed, all data is in a consistent state Isolation Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions Durability After a transaction has completed, its effects are permanently in place in the system This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 11

  12. Queries This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 12

  13. SQL basic operations Insert rows Delete rows Update values in rows Retrieve rows that meet given criteria This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 13

  14. SQL Query SELECT word, description FROM WORD_LIST_TABLE WHERE word="alpha" Generic SELECT columns FROM table WHERE column="value" This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 14

  15. SELECT columns FROM table SELECT columns Select the columns to return Use * to return all columns FROM table specify the table from which to get results This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 15

  16. WHERE column="value" WHERE keyword for conditions that have to be met column="value" the condition that has to be met common operators: =, LIKE, <, > This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 16

  17. AND, ORDER BY, LIMIT SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha" AND definition LIKE "%art%" ORDER BY word DESC LIMIT 1 AND, OR connect multiple conditions with logic operators ORDER BY omit for default order, or ASC for ascending, DESC for descending LIMIT get a limited number of results This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 17

  18. 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"]] This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 18

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

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

  21. rawQuery() String query = "SELECT * FROM WORD_LIST_TABLE"; rawQuery(query, null); query = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? "; String[] selectionArgs = new String[]{"2"} rawQuery(query, selectionArgs); This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 21

  22. query() SELECT * FROM WORD_LIST_TABLE WHERE word="alpha" ORDER BY word ASC LIMIT 2,1; 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" Returns: [["alpha", "particle"]] query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit); This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 22

  23. 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 You will learn more about cursors in the following chapters This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 23

  24. Learn more SQLite website Full description of the Query Language SQLite class Cursor class This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 24

  25. What's Next? Concept Chapter: 10.0 SQLite Primer No Practical This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 25

  26. END This work is licensed under a Creative Commons Attribution 4.0 International License. SQLite Primer Android Developer Fundamentals V2 26

More Related Content

giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#