Android SQLite Database

bawltherapistΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 8 μήνες)

66 εμφανίσεις

Android
SQLite Database
CS 301
Peter Kemper
Reference: Murphy’s Busy Coder’s Guide, SQLite Databases p481 ff
Why Databases on Android?

Common usage for a database

Huge amounts of data

Convenient query language to obtain data

Accessed/modified by huge amount of users

Concurrency: Two-phase commit transaction concept for concurrent access

Applications:

backend for multi-tier web architecture,

backend for business process architectures like SAP

areas: banks, insurance companies, ...

Android

Small scale architecture, little data, few applications ...
SQLite

Relational database with tables (DB schema)

Popular embedded database, here: integrated in Android

SQL interface  small memory footprint  decent speed

Native API not JDBC

Properties
(from http://www.sqlite.org/different.html)

Zero configuration and serverless

Single database file & stable across platforms

Compact, public domain, readable source code

Manifest typing and variable length records
SQLite vs Content Provider

SQLite

Persistent storage of data

Data accessible to a single application (the owner)

Often wrapped by a Content Provider

Content Provider

Specialized type of data store to share data across apps

Exposes standardized ways to retrieve/manipulate data

Query with URI: standard_prefix://authority/data_path/id

Examples: content://browser/bookmarks, content://contacts/people

Built in
SQLite example from Murphy’s book

Constants sample application based on SQLite DB

as initially launched plus its add-constant dialog
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
2%$34*&5678&9:*&;-#+0"#0+&+"<=.*&"==.%)"0%-#>&"+&%#%0%"..?&."3#):*'
!"#$%&'$("($#($&$)*'#$+"$&,,$&$'*-$%"'.+&'+/$-01%0$231'4.$#($&$,1&5"4$+"
$
6155$1'$+0*$'&)*$&',$7&5#*$"6$+0*$%"'.+&'+8
2%$34*&56@8&9:*&;-#+0"#0+&+"<=.*&"==.%)"0%-#A+&"''B)-#+0"#0&'%".-$
CD7
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
2%$34*&5678&9:*&;-#+0"#0+&+"<=.*&"==.%)"0%-#>&"+&%#%0%"..?&."3#):*'
!"#$%&'$("($#($&$)*'#$+"$&,,$&$'*-$%"'.+&'+/$-01%0$231'4.$#($&$,1&5"4$+"
$
6155$1'$+0*$'&)*$&',$7&5#*$"6$+0*$%"'.+&'+8
2%$34*&56@8&9:*&;-#+0"#0+&+"<=.*&"==.%)"0%-#A+&"''B)-#+0"#0&'%".-$
CD7
On Using a Database

Creating
a database

How to
create and fill
database when used first time after
installation of app?

How to
update
/adjust an existing database to a new
schema that comes with an update for an existing app?

Reading
data from a database /
writing
data to a database

How to
open
an existing database?

How to
create/read/update/delete
(CRUD) entries?

How to
close
a database?
SQLite Primer

Implement a Subclass of SQLiteOpenHelper

to
create & open
a database, mainly 3 methods needed:

constructor
with parameters: Context (e.g., an Activity), name DB, cursor factory
(optional, typically just null), integer version number of the DB schema used.

Note: following examples from Murphy operate with a simpler
constructor

onCreate()
with parameter SQLiteDatabase that needs to be filled with tables
and initial data.

onUpgrade()
with parameter SQLiteDatabase object, old and new version
numbers, so you can figure out how best to convert the database from the old
schema to the new one. The simplest, albeit least friendly, approach is to simply
drop the old tables and create new ones.

to
access
database:

getReadableDatabase()
and
getWritableDatabase()
see also: http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html
Example: Database/Constants
This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!"#$%&#'()#&*)+#,%#-%(./01#-/,2#'#3'4%(#+','5'*)6#7/.)#8('97)6#$%&#3'$#7%%.
#
&:%0#;<=/,)#'*#5)/01#'#>,%$>#+','5'*)?#@7)'*)#5)'(#/0#3/0+#,2',#8('97)#'0+
#
;<=/,)#'()#3)'0,#,%#*%7A)#+/"")()0,#:(%57)3*6#'0+#,2',#$%&#-/77#0%,#5)
#
*))/01#'#"&77#9%:$#%"#8('97)#%0#'#:2%0)#'0$#,/3)#*%%06#/0#'77#7/.)7/2%%+?
20"30&"0&04*&5*$%##%#$
B%#+','5'*)*#'()#'&,%3',/9'77$#*&::7/)+#,%#$%&#5$#C0+(%/+?#!"#$%&#-'0,#,%
#
&*)#;<=/,)6#$%&#2'A)#,%#9()',)#$%&(#%-0#+','5'*)6#,2)0#:%:&7',)#/,#-/,2
#
$%&(#%-0#,'57)*6#/0+)D)*6#'0+#+','?
E%#9()',)#'0+#%:)0#'#+','5'*)6#$%&(#5)*,#%:,/%0#/*#,%#9('",#'#*&597'**#%"
#
!"#$%&'(&)*&+(&,
?#E2/*#97'**#-(':*#&:#,2)#7%1/9#,%#9()',)#'0+#&:1('+)#'
#
+','5'*)6#:)(#$%&(#*:)9/"/9',/%0*6#'*#0))+)+#5$#$%&(#'::7/9',/%0?#F%&(
#
*&597'**#%"#
!"#$%&'(&)*&+(&,
#-/77#0))+#,2())#3),2%+*G
!
E2)#9%0*,(&9,%(6#92'/0/01#&:-'(+#,%#,2)#
!"#$%&'(&)*&+(&,
-
9%0*,(&9,%(?#E2/*#,'.)*#,2)#
./)%&0%
#H)?1?6#'0#
12%$3$%4
I6#,2)#0'3)#%"
#
,2)#+','5'*)6#'0#%:,/%0'7#9&(*%(#"'9,%($#H,$:/9'77$6#4&*,#:'**#0&77I6
#
'0+#'0#/0,)1)(#():()*)0,/01#,2)#A)(*/%0#%"#,2)#+','5'*)#*92)3'#$%&
#
'()#&*/01?
!
/).,&5%&67
6#-2/92#:'**)*#$%&#'#
!"#$%&85%595:&
#%54)9,#,2',#$%&#0))+
#
,%#:%:&7',)#-/,2#,'57)*#'0+#/0/,/'7#+','6#'*#'::(%:(/',)?
!
/);(<,5=&67
6#-2/92#:'**)*#$%&#'#
!"#$%&85%595:&
#%54)9,#'0+#,2)#%7+
#
'0+#0)-#A)(*/%0#0&35)(*6#*%#$%&#9'0#"/1&()#%&,#2%-#5)*,#,%#9%0A)(,
#
,2)#+','5'*)#"(%3#,2)#%7+#*92)3'#,%#,2)#0)-#%0)?#E2)#*/3:7)*,6
#
'75)/,#7)'*,#"(/)0+7$6#'::(%'92#/*#,%#*/3:7$#+(%:#,2)#%7+#,'57)*#'0+
#
9()',)#0)-#%0)*?
J%(#)D'3:7)6#2)()#/*#'#
85%595:&*&+(&,
#97'**#"(%3#
85%595:&>./):%5)%:
#,2',6#/0
#
/).,&5%&67
6#9()',)*#'#,'57)#'0+#'++*#'#0&35)(#%"#(%-*6#'0+#/0#
/);(<,5=&67
-
>92)',*>#5$#+(%::/01#,2)#)D/*,/01#,'57)#'0+#)D)9&,/01#
/).,&5%&67
G
(52?5<&
-2/@
A
2/@@/):B5,&
A
5)=,/$=
A
2/):%5)%:
C
$@(/,%
-5)=,/$=
A
2/)%&)%
A
./)%&)%D5+E&:
C
$@(/,%
-5)=,/$=
A
2/)%&)%
A
./)%&0%
C
$@(/,%
-5)=,/$=
A
=5%595:&
A
.E,:/,
C
678
This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!"#$%&
'()*%$!*
+
*(&(,(-.
+
/01234.#&!$)
5
!"#$%&
'()*%$!*
+
*(&(,(-.
+
-67!&.
+
/01!&.8#.)9.7#.%
5
!"#$%&
'()*%$!*
+
*(&(,(-.
+
-67!&.
+
/01!&.:(&(,(-.
5
!"#$%&
'()*%$!*
+
;(%*<(%.
+
/.)-$%=()(>.%
5
#?,7!4
'
47(--
'
:(&(,(-.9.7#.%
'
.3&.)*-
'/01!&.8#.)9.7#.%'
@
''
#%!A(&.
'
-&(&!4
'
B!)(7
'
/&%!)>
':CDCEC/2FGC=2
H
I*,I
5
''
#?,7!4
'
-&(&!4
'
B!)(7
'
/&%!)>
'DJD12
H
I&!&7.I
5
''
#?,7!4
'
-&(&!4
'
B!)(7
'
/&%!)>
'KC1L2
H
IA(7?.I
5
''
''
#?,7!4
'
!"#"$"%&'&()&*
M
N$)&.3&
'4$)&.3&
O
'
@
''''
-?#.%
M
4$)&.3&
P
':CDCEC/2FGC=2
P
'
)?77
P
'
Q
O5
''
R
''
''S8A.%%!*.
''
#?,7!4
'
A$!*
'
+,-*&"#&
M
/01!&.:(&(,(-.
'*,
O
'
@
''''*,
+
&.&/012
M
INT2CD2'DCE12'4$)-&()&-'MF!*'JGD2U2T'VTJ=CTW'X2W'CLD8JGNT2=2GDP
'
&!&7.'D2YDP'A(7?.'T2C1O5I
O5
''''
''''
N$)&.)&K(7?.-
'4A
H
).<
'
-+,#&,#3"(4&%
MO5
''''
''''4A
+
)4#
M
DJD12
P
'
IU%(A!&ZP':.(&;'/&(%'JI
O5
''''4A
+
)4#
M
KC1L2
P
'/.)-$%=()(>.%
+
UTCKJDWF:2CD9F/DCTFJ
O5
''''*,
+
5,%&*#
M
I4$)-&()&-I
P
'DJD12
P
'4A
O5
''''
''''4A
+
)4#
M
DJD12
P
'
IU%(A!&ZP'2(%&;I
O5
''''4A
+
)4#
M
KC1L2
P
'/.)-$%=()(>.%
+
UTCKJDWF2CTD9
O5
''''*,
+
5,%&*#
M
I4$)-&()&-I
P
'DJD12
P
'4A
O5
''''
''''4A
+
)4#
M
DJD12
P
'
IU%(A!&ZP'[?#!&.%I
O5
''''4A
+
)4#
M
KC1L2
P
'/.)-$%=()(>.%
+
UTCKJDWF[LVJD2T
O5
''''*,
+
5,%&*#
M
I4$)-&()&-I
P
'DJD12
P
'4A
O5
''''
''''4A
+
)4#
M
DJD12
P
'
IU%(A!&ZP'=(%-I
O5
''''4A
+
)4#
M
KC1L2
P
'/.)-$%=()(>.%
+
UTCKJDWF=CT/
O5
''''*,
+
5,%&*#
M
I4$)-&()&-I
P
'DJD12
P
'4A
O5
''''
''''4A
+
)4#
M
DJD12
P
'
IU%(A!&ZP'=.%4?%ZI
O5
''''4A
+
)4#
M
KC1L2
P
'/.)-$%=()(>.%
+
UTCKJDWF=2TNLTW
O5
''''*,
+
5,%&*#
M
I4$)-&()&-I
P
'DJD12
P
'4A
O5
''''
''''4A
+
)4#
M
DJD12
P
'
IU%(A!&ZP'=$$)I
O5
''''4A
+
)4#
M
KC1L2
P
'/.)-$%=()(>.%
+
UTCKJDWF=88G
O5
''''*,
+
5,%&*#
M
I4$)-&()&-I
P
'DJD12
P
'4A
O5
''''
''''4A
+
)4#
M
DJD12
P
'
IU%(A!&ZP'G.#&?).I
O5
''''4A
+
)4#
M
KC1L2
P
'/.)-$%=()(>.%
+
UTCKJDWFG2VDLG2
O5
''''*,
+
5,%&*#
M
I4$)-&()&-I
P
'DJD12
P
'4A
O5
''''
''''4A
+
)4#
M
DJD12
P
'
IU%(A!&ZP'V7?&$I
O5
''''4A
+
)4#
M
KC1L2
P
'/.)-$%=()(>.%
+
UTCKJDWFV1LD8
O5
''''*,
+
5,%&*#
M
I4$)-&()&-I
P
'DJD12
P
'4A
O5
''''
''''4A
+
)4#
M
DJD12
P
'
IU%(A!&ZP'/(&?%)I
O5
234
Note:
Example slightly
different in
Murphy 5.2
Example: Database/Constants
This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:,'8)6;
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!!!
!!!!"#
$
!"#
%
878(*
+
!
@513#BAC+!,D.@
<=
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:,);
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!!!
!!!!"#
$
!"#
%
878(*
+
!
@513#BAC+!8E-!7/F3.>@
<=
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:8G*:7,(';H
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!!!
!!!!"#
$
!"#
%
878(*
+
!
@513#BAC+!)13.D/@
<=
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:)6';),
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!!!
!!!!"#
$
!"#
%
878(*
+
!
@513#BAC+!&-.D/@
<=
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:&*;),
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!
I
!!JK#-11B>-
!!
LD?FB"
!
#0B>
!
)%*!+(,-'
%
,M(BA-H3A3?3/-
!>?
+
!
B.A
!0F>&-1/B0.
+
!
B.A
!.-N&-1/B0.
<
!
O
!!!!3.>10B>
$
DABF
$
(04
$
.
%
@P0./A3.A/@
+
!
@)L413>B.4!>3A3?3/-+!NEB"E!NBFF!>-/A10C!3FF
!
0F>!>3A3@
<=
!!!!>?
$
'/'0123
%
@H6KQ!8'R(*!7S!*T7,8,!"0./A3.A/@
<=
!!!!
)%4(',#'
%
>?
<=
!!
I
I
!"#$%&#'"$(#
,M(BA-KL-.G-FL-1
#%$)*+,%%-#*(&,.&#,/0#1"+0#"/."#,/#2/%.,/*&#"3
#
2.4#!1&/-#51&/#'"$#/&&0#,#
,M(BA-H3A3?3/-
#")6&*.#."#0"#7$&(2&%#"(#0,.,
#
8"0 2 3 2 *,.2"/% -#,% 9#'"$ (#
,M(BA-KL-.G-FL-1
#."#
4-A6-3>3?F-H3A3?3/-%<
#"(
#
4-AU1BA-3?F-H3A3?3/-%<
-#0&:&/02/;#$:"/#51&.1&(#"(#/".#'"$#52++#)&
#
*1,/;2/;#2.%#*"/.&/.%4#<"(#&=,8:+&-#"$(#
P0./A3.A/R10N/-1
#,*.2>2.'#":&/%#.1&
#
0,.,),%&#2/#
0.P1-3A-%<
#,%#:,(.#"3#0"2/;#,#7$&('?
"0./A3.A/PD1/01
V
>?
!!!!!!!!!!!!!!!!!!
$
+'#5',-,67'8,#,6,&'
%<
!!!!!!!!!!!!!!!!!!
$
(,.2"'(9
%
@,*(*P8!:7H+!ABAF-+!#3FD-!@
W
!!!!!!!!!!!!!!!!!!!!!!!!!!!
@S6K2!"0./A3.A/!K6H*6!R9!ABAF-@
+
!!!!!!!!!!!!!!!!!!!!!!!!!!!
.DFF
<=
@1&/#'"$#,(&#0"/&#52.1#.1&#0,.,),%&#A&4;4-#'"$(#,*.2>2.'#2%#)&2/;#*+"%&0B-
#
%28:+'#*,++#
"F0/-%<
#"/#'"$(#
,M(BA-KL-.G-FL-1
#."#(&+&,%&#'"$(#*"//&*.2"/4
234
...
to open database for read / write access use helper db
This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:,'8)6;
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!!!
!!!!"#
$
!"#
%
878(*
+
!
@513#BAC+!,D.@
<=
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:,);
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!!!
!!!!"#
$
!"#
%
878(*
+
!
@513#BAC+!8E-!7/F3.>@
<=
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:8G*:7,(';H
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!!!
!!!!"#
$
!"#
%
878(*
+
!
@513#BAC+!)13.D/@
<=
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:)6';),
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!!!
!!!!"#
$
!"#
%
878(*
+
!
@513#BAC+!&-.D/@
<=
!!!!"#
$
!"#
%
&'()*
+
!,-./0123.34-1
$
56'&789:&*;),
<=
!!!!>?
$
$%&'(#
%
@"0./A3.A/@
+
!878(*
+
!"#
<=
!!
I
!!JK#-11B>-
!!
LD?FB"
!
#0B>
!
)%*!+(,-'
%
,M(BA-H3A3?3/-
!>?
+
!
B.A
!0F>&-1/B0.
+
!
B.A
!.-N&-1/B0.
<
!
O
!!!!3.>10B>
$
DABF
$
(04
$
.
%
@P0./A3.A/@
+
!
@)L413>B.4!>3A3?3/-+!NEB"E!NBFF!>-/A10C!3FF
!
0F>!>3A3@
<=
!!!!>?
$
'/'0123
%
@H6KQ!8'R(*!7S!*T7,8,!"0./A3.A/@
<=
!!!!
)%4(',#'
%
>?
<=
!!
I
I
!"#$%&#'"$(#
,M(BA-KL-.G-FL-1
#%$)*+,%%-#*(&,.&#,/0#1"+0#"/."#,/#2/%.,/*&#"3
#
2.4#!1&/-#51&/#'"$#/&&0#,#
,M(BA-H3A3?3/-
#")6&*.#."#0"#7$&(2&%#"(#0,.,
#
8"0 2 3 2 *,.2"/% -#,% 9#'"$ (#
,M(BA-KL-.G-FL-1
#."#
4-A6-3>3?F-H3A3?3/-%<
#"(
#
4-AU1BA-3?F-H3A3?3/-%<
-#0&:&/02/;#$:"/#51&.1&(#"(#/".#'"$#52++#)&
#
*1,/;2/;#2.%#*"/.&/.%4#<"(#&=,8:+&-#"$(#
P0./A3.A/R10N/-1
#,*.2>2.'#":&/%#.1&
#
0,.,),%&#2/#
0.P1-3A-%<
#,%#:,(.#"3#0"2/;#,#7$&('?
"0./A3.A/PD1/01
V
>?
!!!!!!!!!!!!!!!!!!
$
+'#5',-,67'8,#,6,&'
%<
!!!!!!!!!!!!!!!!!!
$
(,.2"'(9
%
@,*(*P8!:7H+!ABAF-+!#3FD-!@
W
!!!!!!!!!!!!!!!!!!!!!!!!!!!
@S6K2!"0./A3.A/!K6H*6!R9!ABAF-@
+
!!!!!!!!!!!!!!!!!!!!!!!!!!!
.DFF
<=
@1&/#'"$#,(&#0"/&#52.1#.1&#0,.,),%&#A&4;4-#'"$(#,*.2>2.'#2%#)&2/;#*+"%&0B-
#
%28:+'#*,++#
"F0/-%<
#"/#'"$(#
,M(BA-KL-.G-FL-1
#."#(&+&,%&#'"$(#*"//&*.2"/4
234
once done, call close()
SQLite Primer

SQL dialect

Data definition: CREATE TABLE

Data manipulation: INSERT, UPDATE, DELETE

Queries: SELECT ... FROM ... WHERE

deviates from SQL-92 standard,
not
supported

FOREIGN KEY constraints, nested transactions,
RIGHT OUTER JOIN, FULL OUTER JOIN, ...

Most particular: “Manifest typing”
This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!"#$%&'()*')$+($)"#'$*,,#,$)&$)"#$-+().$/$-&'01)*2$&'$*'$#3+()+'0$%&'()*')
$
4+--$56+'0$72$*$%&')#3)$8#'7$4+)"$*$9:#-#)#9$&2)+&'$;$*<)#6$%&'<+68*)+&'=
$
)"*)$4+--$,#-#)#$)"#$%&'()*').
/',=$&<$%&76(#=$*--$&<$)"+($+($()&6#,$+'$*$>?@+)#$,*)*5*(#.
(&23%)4&52,%0*&67%8*7
>?@+)#=$*($)"#$'*8#$(700#()(=$7(#($*$,+*-#%)$&<$ >?@$ <&6$A7#6+#($ B
!"#"$%
C=
$
,*)*$8*'+27-*)+&'$B
&'!"(%
=$#).$*-.C=$*',$,*)*$,#<+'+)+&'$B
$(")%"*%)+#"
=$#).$*-.C.
$
>?@+)#$"*($*$<#4$2-*%#($4"#6#$ +)$,#D+*)#($<6&8$)"#$>?@1EF$()*',*6,=$'&
$
,+<<#6#')$ )"*'$ 8&()$ >?@$,*)*5*(#(.$!"#$ 0&&,$'#4($ +($ )"*)$ >?@+)#$ +($ (&
$
(2*%#1#<<+%+#') $ )"*) $ )"#$/',6&+,$ 67')+8#$ %*'$ +'%-7,#$ *-- $ &< $ >?@+)#= $'&)
$
(&8#$*65+)6*6G$(75(#)$)&$)6+8$+)$,&4'$)&$(+H#.
!"#$ 5+00#() $,+<<#6#'%#$ <6&8$ &)"#6$ >?@$,*)*5*(#($ G&7$ 4+-- $#'%&7')#6$ +(
$
26&5*5-G$)"#$,*)*$)G2+'0.$I"+-#$G&7$%*'$(2#%+<G$)"#$,*)*$)G2#($<&6$%&-78'(
$
+'$*$
$(")%"*%)+#"
$()*)#8#')=$*',$4"+-#$>?@+)#$4+--$7(#$)"&(#$*($*$"+')=$)"*)
$
+($*($<*6$*($+)$0&#(.$J&7$%*'$27)$4"*)#D#6$,*)*$G&7$4*')$+'$4"*)#D#6$%&-78'
$
G&7$4*').$K7)$*$()6+'0$+'$*'$
&'%","(
$%&-78'L$>76#M$N&$26&5-#8M$O+%#$D#6(*L
$
I&6P($)&&M$>?@+)#$6#<#6($)&$)"+($*($98*'+<#()$)G2+'09=$*($,#(%6+5#,$+'$)"#
$
,&%78#')*)+&'
Q
!"#$%"&'()*#*+,&"-.#*/(#0%*%*+,(#&)#%#,12,(1*+#2'#*/(#3%45(#&*
6
)(4'.#"2*#2'#*/(#7245$"#&"#8/&7/#*/(#3%45(#&)#)*21(09#:;<&*(
#
*/5)#%4428)#*/(#5)(1#*2#)*21(#%"+#3%45(#2'#%"+#0%*%*+,(#&"*2
#
%"+#7245$"#1(-%104())#2'#*/(#0(74%1(0#*+,(#2'#*/%*#7245$"9
R'$*,,+)+&'=$)"#6#$*6#$*$"*',<7-$&<$()*',*6,$>?@$<#*)76#($'&)$(722&6)#,$+'
$
>?@+)#=$'&)*5-G $
-.("&,'*/"0
$ %&'()6*+')(=$'#()#,$)6*'(*%)+&'(= $
(&,1%*.2%"(
*
3.&'
$*',$
-2##*.2%"(*3.&'
=$*',$(&8#$<-*D&6($&<$
)#%"(*%)+#"
.
S#G&',$)"*)=$)"&70"=$G&7$0#)$*$<7--$>?@$(G()#8= $%&82-#)#$4+)"$)6+00#6(=
$
)6*'(*%)+&'(=$*',$)"#$-+P#.$>)&%P$>?@$()*)#8#')(=$-+P#$
!"#"$%
=$4&6P$26#))G
$
87%"$*($G&7$8+0")$#32#%).
9:;
see:
http://www.sqlite.org/different.html
SQLite Primer: CRUD

DB queries generated as strings and executed.

Creating a table with

a primary key column named
_id
and auto-incremented
integer ids

data column named
title

data column named
value

other indexes could be added with CREATE INDEX
statements

INSERT, UPDATE, DELETE operations in a similar manner
This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
2*00%#$&03*&4"1.*
!"#$%#&'()*+$,"-#$('./&0$'*1$)*1&2&03$,"-$4)//$*&&1$("$%'//$
!"!#$%&'(
$"*$,"-#
$
$%&)*!+,*,-,.!
3$5#"6)1)*+$(7&$889$0('(&:&*($,"-$4)07$("$'55/,$'+')*0($(7&
$
1'('.'0&;$<'##)*+$'$1'('.'0&$&##"#3$(7)0$:&(7"1$#&(-#*0$*"(7)*+;
="3 $ >"#$ &2':5/&3 $,"-$%'*$ %'//$
!"!#$%&'(
$ ("$%#&'(&$ (7& $
#/0.*,0*.
$ ('./&3 $'0
$
0 7"4* $ ) * $ ( 7 & $
+,*,-,.!1!23!4
$
/054!,*!'(
$:&(7"1?
6-
7
!"!#$%&
'
859:;<:=<;>&:=#/0.*,0*.='?)6=@A<:B:9=C9@D;9E=F:E=;G<H@A59:D:A<I=*)*2!
=
<:J<I=K,2L!=9:;&(M8
(M
@7)0 $ 4)//$ %#&'(&$'$ ('./&3 $ *':&1 $
#/0.*,0*.
3 $ 4)(7$'$ 5#):'#,$ A&,$ %"/-:*
$
*':&1 $
?)6
$(7'($)0$'*$'-("B)*%#&:&*(&1$)*(&+&#$C);&;3$=D9)(&$4)//$'00)+*$(7&
$
6'/-&$>"#$,"-$47&*$,"-$)*0&#($#"40E3$5/-0$(4"$1'('$%"/-:*0?$
*)*2!
$ C(&2(E
$
'*1 $
K,2L!
$ C'$ >/"'(3 $"#$ F#&'/F$ )*$ =D9)(&$ (&#:0E;$ =D9)(&$ 4)//$'-(":'()%'//,
$
%#&'( & $'* $ ) * 1 & 2 $ >"#$,"- $"* $,"-#$ 5#):'#,$ A &,$ %"/-:* $ G $,"- $ %"-/1 $'1 1 $"( 7 &#
$
) * 1 & 2 & 0 $ 7 &#& $ 6 )'$ 0":& $
59:;<:=@A+:J
$0('(&:&*(03$)>$,"-$0"$%7"0&$(";
H"0($/)A&/,3$,"-$4)//$%#&'(&$('./&0$'*1$)*1&2&0$47&*$,"-$>)#0($%#&'(&$(7&
$
1'('.'0&3$"#$5"00)./,$47&*$(7&$1'('.'0&$*&&10$-5+#'1)*+$("$'%%"::"1'(&
$
'$*&4$#&/&'0&$">$,"-#$'55/)%'()"*;$I>$,"-$1"$*"($%7'*+&$,"-#$('./&$0%7&:'03
$
,"-$:)+7( $ *&6&#$ 1#"5$,"-#$ ('./&0 $"#$ )*1&2&03 $.-( $ )> $,"-$ 1"3 $ J-0( $ -0&
$
!"!#$%&'(
$("$)*6"A&$
+9HC=@A+:J
$'*1$
+9HC=<;>&:
$0('(&:&*(0$'0$*&&1&1;
!"5%#6&/"0"
K)6&*$(7'($,"-$7'6&$'$1'('.'0&$'*1$"*&$"#$:"#&$('./&03$,"-$5#".'./,$4'*(
$
("$5-($0":&$1'('$)*$(7&:$'*1$0-%7;$L"-$ 7'6&$(4"$:'J"#$'55#"'%7&0$>"#
$
1")*+$(7)0;
L"-$%'*$'/4',0$-0&$
!"!#$%&'(
3$J-0($/)A&$,"-$1)1$>"#$%#&'()*+$(7&$('./&0;$@7&
$
!"!#$%&'(
$:&(7"1$4"#A0$>"#$'*,$=D9$(7'($1"&0$*"($#&(-#*$#&0-/(03$0"$)($%'*
$
7'*1/&$
@A$:9<
3$
GC+;<:
3$
+:&:<:
3$&(%;$J-0($>)*&;
788
SQLite Primer: CRUD

Alternative to add/delete/modify data: use
insert
(), update(),
delete() methods on SQLiteDatabase object

Makes use of
ContentValues
objects

get() to obtain a value by its key

getAsInteger(), getAsString() ....

Insert
() parameters: 1) name of table,
2) one column as “null column hack”, 3) values
This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!"#$%&'()$*&(+,)%+-%("%#-)%(.)%
!"#$%&'(
/%
)*+,&$'(
/%&*0%
+$-$&$'(
% 1)(."0-%"*
%
(.)%
./0!&$1,&,2,#$
%"23)4(5%6.)-)%&$)%72#+'0)$7%-"$(-%"8%1)(."0-/%+*%(.&(%(.)9
%
2$)&:% 0";*% (.)% <=>% -(&()1)*(-% +*("% 0+-4$)()% 4.#*:-/% (.)*% (&:)% (."-)
%
4.#*:-%&-%?&$&1)()$-5
@"$%)A&1?')/%.)$)%;)%
!"#$%&'(
%&%*);%$";%+*("%"#$%
34"#&,"&#
%(&2')B
*%!5,&$
6
54!+
6
!"#$%&&'((
'
1!,-478%,**$%
69%,**$%
(
6
:
66
;4"&$"&<,-)$#
65,-)$#
=
"$9
6
)#*+%*+,-./%&
'
>
(?
665,-)$#
@
!/+
'
A&!&-$A
B
69%,**$%
@
0%+12+.%
'((?
665,-)$#
@
!/+
'
A5,-)$A
B
69%,**$%
@
0%+,-./%
'((?
66+2
@
0%+3"2+-4.%5-+-4-&%
'(@
2*&%"+
'
A34"#&,"&#A
B
6
A&!&-$A
B
65,-)$#
(?
6634"#&,"&#;)%#4%
@
"%6/%"7
'(?
C
6.)-)%1)(."0-%1&:)%#-)%"8%
;4"&$"&<,-)$#
%"23)4(-/%;.+4.%+1?')1)*(%&%
D,*
C
)-D#)%+*()$8&4)/%&'2)+(%"*)%(.&(%.&-%&00+(+"*&'%1)(."0-%8"$%;"$:+*E%;+(.
%
<=>+()%(9?)-5%@"$%)A&1?')/%+*%&00+(+"*%("%
7$&'(
%("%$)($+),)%&%,&'#)%29%+(-%:)9/
%
9"#%.&,)%
7$&E#F"&$7$%'(
/%
7$&E#.&%!"7'(
/%&*0%-"%8"$(.5
6.)%
!"#$%&'(
%1)(."0%(&:)-%(.)%*&1)%"8%(.)%(&2')/%(.)%*&1)%"8%"*)%4"'#1*
%
&-%(.)%7*#''%4"'#1*%.&4:7/%&*0%&%
;4"&$"&<,-)$#
%;+(.%(.)%+*+(+&'%,&'#)-%9"#
%
;&*(%?#(%+*("%(.+-%$";5%6.)%7*#''%4"'#1*%.&4:7%+-%8"$%(.)%4&-)%;.)$)%(.)
%
;4"&$"&<,-)$#
% +*-(&*4)%+-%)1?(9%F%(.)%4"'#1*%*&1)0%&-%(.)%7*#''%4"'#1*
%
.&4:7%;+''%2)%)A?'+4+('9%&--+E*)0%(.)%,&'#)%
GH00
%+*%(.)%<=>%
FG.IJK
%-(&()1)*(
%
E)*)$&()0%29%
!"#$%&'(
5
6.) %
)*+,&$'(
% 1)(."0 % (&:)- % (.) % *&1) %"8 % (.) % (&2')/% & %
;4"&$"&<,-)$#
6
$)?$)-)*(+*E%(.)%4"'#1*-%&*0%$)?'&4)1)*(%,&'#)-%("%#-)/%&*%"?(+"*&'%
8LIJI
6
4'&#-)/%&*0%&*%"?(+"*&'%'+-(%"8%?&$&1)()$-%("%8+''% +*("%(.) %
8LIJI
% 4'&#-)/%("
%
$ )?'&4) % &*9 % )12)00)0 % D#)-( +"* % 1&$:- % G
M
H 5 % <+ *4) %
)*+,&$'(
%"*'9% $)?'&4)-
%
4"'#1*- %;+(.% 8+A)0 %,&'#)-/%,)$-#- %"*)- % 4"1?#()0 % 2&-)0 %"* %"(.)$
%
+*8"$1&(+"*/%9"#%1&9%*))0%("%#-)%
$N$3./0'(
%("%&44"1?'+-.%-"1)%)*0-5%6.)
%
8LIJI
% 4'&#-) % &*0 %?&$&1)()$ %'+-( %;"$:- % &:+* % ("% (.) %?"-+(+"*&'% <=>
%
?&$&1)()$-%9"#%1&9%2)%#-)0%("%8$"1%"(.)$%<=>%IJK-5
234
SQLite Primer: CRUD

Alternative to add/delete/modify data: use insert(), update(),
delete
() methods on SQLiteDatabase object

Delete
() has parameters:

table,

optional “where” clause

corresponding parameters to fill the “where” clause “?”

Example: remove single row with matching _ID value
This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!"#$
!"#"$"%&
$%#&"'($)'*+,$-+./$&'$
'(!)$"%&
0$&-+./1$&"#$/-%#$'2$&"#$&-34#0
$
&"#$'5&.'/-4$
*+,-,
$64-7,#0$-/($&"#$6'**#,5'/(./1$5-*-%#&#*,$&'$2.44$./&'$&"#
$
*+,-,
$64-7,#8$9'*$#:-%54#0$"#*#$)#$
!"#"$"%&
$-$*')$2*'%$'7*$
./01$)0$1
$&-34#0
$
1.;#/$.&,$<=>?
(234)$"
5
4/3!
5
!"#$%&&'%(%)%
%
#/06
52/78!
&
5
9
55:$2306
;<
5)261
=
9
:$2306
>
*+(,%-.
%
2/78!
&
?
@
55!A
>
/%)0"1)+2(%'+)+2+&%
%&>
3%(%)%
%
B./01$)0$1B
C
5
BD8E=FB
C
5)261
&@
55./01$)0$1G'21/2
>
"%4,%"5
%&@
?
23"0&4-*+&(5-6#'7&8-9*+&(5-6#'
@,$).&"$
8H:,-I
0$
JKELI,
0$-/($
E,M,I,
0$A'7$"-;#$&)'$%-./$'5&.'/,$2'*$*#&*.#;./1
$
(-&-$2*'%$-$BCD.&#$(-&-3-,#$7,./1$
:,M,GI
?
E8
F'7$6-/$7,#$
2)7N'"2O%&
$&'$./;'+#$-$
:,M,GI
$,&-&#%#/&$(.*#6&4A0$'*
G8
F'7$6-/$7,#$
P'"2O%&
$&'$37.4($75$-$H7#*A$2*'%$.&,$6'%5'/#/&$5-*&,
I'/2'7/(./1$%-&&#*,$27*&"#*$.,$&"#$
:NM3$"N'"2OQ'3#!"2
$ 64-,,$-/($&"#$.,,7#
$
'2$67*,'*,$-/($67*,'*$2-6&'*.#,8$D#&J,$&-+#$-44$'2$&".,$'/#$5.#6#$-&$-$&.%#8
!"#$%&'()'*
!"#$,.%54#,&$,'47&.'/0$-&$4#-,&$./$&#*%,$'2$&"#$@K=0$.,$
2)7N'"2O%&
8$B.%54A$6-44
$
.& $ ).&"$ A'7* $ BCD $
:,M,GI
$,&-&#%#/&8 $!"#$
:,M,GI
$,&-&#%#/& $ 6-/$./647(#
$
5',.&.'/-4$5-*-%#&#*,L$&"#$-**-A$'2$&"#,#$2'*%,$A'7*$,#6'/($5-*-%#&#*$&'
$
2)7N'"2O%&
8$B'0$)#$)./($75$).&"?
./01$)0$1G'21/2
=
!A
555555555555555555
>
/%)6%+3+2(%'+)+2+&%
%&
555555555555555555
>
"+78,%"5
%
B:,M,GI5D8EC5$3$#"C54)#'"5B
R
555555555555555555555555555
BS-TU5./01$)0$15T-E,-5QV5$3$#"B
C
555555555555555555555555555
0'##
&@
!"#$ *#&7*/$;-47#$.,$- $
G'21/2
0 $)".6"$6'/&-./,$ %#&"'(,$ 2'*$.&#*-&./1$';#*
$
*#,74&,$M,##$3#4')N8
:;:
SQLite Primer: Retrieve
3 ways to retrieve data using SELECT:

use
rawQuery()
to invoke a SELECT statement directly, or

use
query()
or
SQLiteQueryBuilder
to build up a query
from its component parts

Example for a
raw query
:

rawQuery
with SQL SELECT statement & optional string
array of the parameters to replace ? characters in query

get (id,title,value) triple for all rows in table “constants”

results are ordered by entries in column “title”
This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!"#$
!"#"$"%&
$%#&"'($)'*+,$-+./$&'$
'(!)$"%&
0$&-+./1$&"#$/-%#$'2$&"#$&-34#0
$
&"#$'5&.'/-4$
*+,-,
$64-7,#0$-/($&"#$6'**#,5'/(./1$5-*-%#&#*,$&'$2.44$./&'$&"#
$
*+,-,
$64-7,#8$9'*$#:-%54#0$"#*#$)#$
!"#"$"%&
$-$*')$2*'%$'7*$
./01$)0$1
$&-34#0
$
1.;#/$.&,$<=>?
(234)$"
5
4/3!
5
!"#$%&&'%(%)%
%
#/06
52/78!
&
5
9
55:$2306
;<
5)261
=
9
:$2306
>
*+(,%-.
%
2/78!
&
?
@
55!A
>
/%)0"1)+2(%'+)+2+&%
%&>
3%(%)%
%
B./01$)0$1B
C
5
BD8E=FB
C
5)261
&@
55./01$)0$1G'21/2
>
"%4,%"5
%&@
?
23"0&4-*+&(5-6#'7&8-9*+&(5-6#'
@,$).&"$
8H:,-I
0$
JKELI,
0$-/($
E,M,I,
0$A'7$"-;#$&)'$%-./$'5&.'/,$2'*$*#&*.#;./1
$
(-&-$2*'%$-$BCD.&#$(-&-3-,#$7,./1$
:,M,GI
?
E8
F'7$6-/$7,#$
2)7N'"2O%&
$&'$./;'+#$-$
:,M,GI
$,&-&#%#/&$(.*#6&4A0$'*
G8
F'7$6-/$7,#$
P'"2O%&
$&'$37.4($75$-$H7#*A$2*'%$.&,$6'%5'/#/&$5-*&,
I'/2'7/(./1$%-&&#*,$27*&"#*$.,$&"#$
:NM3$"N'"2OQ'3#!"2
$ 64-,,$-/($&"#$.,,7#
$
'2$67*,'*,$-/($67*,'*$2-6&'*.#,8$D#&J,$&-+#$-44$'2$&".,$'/#$5.#6#$-&$-$&.%#8
!"#$%&'()'*
!"#$,.%54#,&$,'47&.'/0$-&$4#-,&$./$&#*%,$'2$&"#$@K=0$.,$
2)7N'"2O%&
8$B.%54A$6-44
$
.& $ ).&"$ A'7* $ BCD $
:,M,GI
$,&-&#%#/&8 $!"#$
:,M,GI
$,&-&#%#/& $ 6-/$./647(#
$
5',.&.'/-4$5-*-%#&#*,L$&"#$-**-A$'2$&"#,#$2'*%,$A'7*$,#6'/($5-*-%#&#*$&'
$
2)7N'"2O%&
8$B'0$)#$)./($75$).&"?
./01$)0$1G'21/2
=
!A
555555555555555555
>
/%)6%+3+2(%'+)+2+&%
%&
555555555555555555
>
"+78,%"5
%
B:,M,GI5D8EC5$3$#"C54)#'"5B
R
555555555555555555555555555
BS-TU5./01$)0$15T-E,-5QV5$3$#"B
C
555555555555555555555555555
0'##
&@
!"#$ *#&7*/$;-47#$.,$- $
G'21/2
0 $)".6"$6'/&-./,$ %#&"'(,$ 2'*$.&#*-&./1$';#*
$
*#,74&,$M,##$3#4')N8
:;:
SQLite-Primer: Retrieve

Alternative:
Regular Query
: takes discrete pieces of SELECT statement
and builds the query from them

The name of the table to query against

The list of columns to retrieve

The WHERE clause, optionally including positional parameters

The list of values to substitute in for those positional parameters

The GROUP BY clause, if any

The HAVING clause, if any

The ORDER BY clause, if any
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!"#$%&#%$'()$")
*
+,
------------------
.
!"#$"%&%'(")%#%'%*"
/0
------------------
.
+%,-."+/
/
12343'5-6789-%:%;<9-=&;(<-1
>
---------------------------
1?@AB-!"#$%&#%$-A@83@-CD-%:%;<1
9
---------------------------
#(;;
0E
!"#$%#&'%($)*+'#$,-$* $
'()$")
.$/",0"$01(&*,(-$ 2#&"13-$ 41%$,&#%*&,(5$1)#%
$
%#-'+&-$6-##$7#+1/89
:4$;1'%$<'#%,#-$*%#$=%#&&;$2'0"$>7*?#3$,(&1>$;1'%$*==+,0*&,1(.$&",-$,-$*$)#%;
$
-&%*,5"&41%/*%3$/*;$&1$'-#$&"#29$@1/#)#%.$,&$5#&-$012=+,0*&#3$,4$=*%&-$14
$
&"#$ <'#%;$ *%#$ 3;(*2,0.$ 7#;1(3$/"*& $ =1-,&,1(*+ $ =*%*2#&#%- $ 0*($ %#*++;
$
"*(3+#9 $ A1%$#B*2=+#.$,4 $ &"#$ -#&$ 14 $ 01+'2(-$;1'$ (##3$ &1$ %#&%,#)#$,-$ (1&
$
?(1/($ *&$ 012=,+#$ &,2#.$ ='&&#%,(5$ *%1'(3$ 01(0*&#(*&,(5$ 01+'2($ (*2#-
$
,(&1$ *$ 0122*C3#+,2,&#3$ +,-& $ 0*($ 7#$ *((1;,(5999/",0"$,- $/"#%#$
F(<)G/0
-
012#-$,(9
!"#$%&'()$"'*"+
!"#$
F(<)G/0
$ 2#&"13$&*?#-$&"#$3,-0%#&#$=,#0#-$14$*$DEFEG!$-&*&#2#(&$*(3
$
7',+3- $ &"#$ <'#%;$ 4%12$ &"#29 $!"#$ =,#0#-.$,($ 1%3#% $ &"*& $ &"#;$ *==#*% $ *-
$
=*%*2#&#%-$&1$
F(<)G/0
.$*%#H
!
!"#$(*2#$14$&"#$&*7+#$&1$<'#%;$*5*,(-&
!
!"#$+,-&$14$01+'2(-$&1$%#&%,#)#
!
!"#$
HI3@3
$0+*'-#.$1=&,1(*++;$,(0+'3,(5$=1-,&,1(*+$=*%*2#&#%-
!
!"#$+,-&$14$)*+'#-$&1$-'7-&,&'&#$,($41%$&"1-#$=1-,&,1(*+$=*%*2#&#%-
!
!"#$
J@AKL-CD
$0+*'-#.$,4$*(;
!
!"#$
IMN7OJ
$0+*'-#.$,4$*(;
!
!"#$
A@83@-CD
$0+*'-#.$,4$*(;
!"#-#$0*($ 7#$
#(;;
$/"#($&"#;$*%#$ (1&$ (##3#3$ 6#B0#=&$&"#$&*7+#$ (*2#.$14
$
01'%-#8H
2%):#P
QR
-!";(S#$
*
T
1781
9
-
1:#=<#%")G1
U
E
2%):#P
QR
-V&)S$
*
T
1$#:!W;<X):%Y1
U
E
232
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!"#$%#
&#'$"()
*
+,
-
!"#$%
.
/01+2')$/
3
&4%("56$
3
&
/675'*8/
3
&&&&&&&&&&&&&&&&&&&&&&&97#5$
3
&
6"((
3
&
6"((
3
&
6"((
:;
!"#$%&'()"*)"
!"#$%&&'(#)"*#+",#'-'.,&'#&)'#/,'(+0#+",#1'&#%#
!"#$%#
#2%.34#5)67#67#&)'
#
89:("6:;<=>6&'#':6&6"9#"?#&)'#:%&%2%7'#.,(7"(0#%#."9.'@&#,7':#69#$%9+
#
:%&%2%7'#7+7&'$74#A6&)#&)'#.,(7"(0#+",#.%9B
!
C69:#",&#)"*#$%9+#("*7#%('#69#&)'#('7,D&#7'&#E6%#
2')!%"6).:
!
F&'(%&'#"E'(#&)'#("*7#E6%#
5%<'=%>1#$).:
0#
5%<'=%?'@).:
0#%9:
#
1$AB)'#C7$).:
!
C69:#",&#&)'#9%$'7#"?#&)'#."D,$97#E6%#
2')!%("56?75'$.:
0#."9E'(&
#
&)"7'#69&"#."D,$9#9,$2'(7#E6%#
2')!%("56D6+'@.:
0#%9:#1'&#E%D,'7#?"(
#
&)'#.,(('9&#("*#?"(#%#16E'9#."D,$9#E6%#$'&)":7#D63'#
2')E)#162.:
0
#
2')D6).:
0#'&.4
!
G'H'-'.,&'#&)'#/,'(+#&)%&#.('%&':#&)'#.,(7"(#E6%#
#'F"'#G.:
!
G'D'%7'#&)'#.,(7"(I7#('7",(.'7#E6%#
4(%$'.:
C"(#'-%$@D'0#)'('#*'#6&'(%&'#"E'(#%#
01+2')$
#&%2D'#'9&(6'7B
!"#$%#
&#'$"()
*
&&+,
-
$&'("#$%
.
/EHCH!=&DI3&675'3&16<'6)%#G&>JKL&01+2')$/
3
&
6"((
:;
0M1('
&
.N
#'$"()
-
)*+#,*-#./
.::
&
O
&
16)
&1+
*
#'$"()
-
0#/12/
.
P
:;
&
E)#162
&675'
*
#'$"()
-
0#/3/$420
.
Q
:;
&
16)
&16<'6)%#G
*
#'$"()
-
0#/12/
.
R
:;
&
SS&+%&$%5')M162&"$'B"(&01)M&)M'$'
T
#'$"()
-
56*7#
.:;
J",#.%9#%D7"#*(%@#%#
!"#$%#
#69#%#
E159('!"#$%#A+79)'#
#"(#"&)'(
#
6 $ @ D'$'9 & % & 6"9 0#& )'9#) % 9:#& )'#('7,D & 6 9 1#%:% @ &'(#&"#%#
C1$)U1'0
#"(#"&)'(
#
7'D'.& 6"9#*6:1'& 4#!"&'0#& )",1 )0#& )% &#6?#+",#% ('#1"6 91#&"#,7'#
!"#$%#A+79)'#
#"(
#
6&7#7,2.D%77'7#KD63'#
E159('!"#$%#A+79)'#
L0#+",(#('7,D&#7'&#"?#+",(#/,'(+#
!"#$
%
."9&%69#%9#69&'1'(#."D,$9#9%$':#
VDI
#&)%&#67#,96/,'#?"(#&)'#('7,D&#7'&4#5)67
#
234
SQLite-Primer: Retrieve

Query returns a Cursor that encapsulates the result set (memory usage!)

With the cursor, you can:

Find out how many rows are in the result set via getCount()

Iterate over the rows via moveToFirst(), moveToNext(), and isAfterLast()

Find out the names of the columns via getColumnNames(), convert those into column
numbers via getColumnIndex(), and get values for the current row for a given column
via methods like getString(), getInt(), etc.

Re-execute the query that created the cursor via requery()

Release the cursor's resources via close()  important to release memory for GC!
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+
!"#$%#
&#'$"()
*
+,
-
!"#$%
.
/01+2')$/
3
&4%("56$
3
&
/675'*8/
3
&&&&&&&&&&&&&&&&&&&&&&&97#5$
3
&
6"((
3
&
6"((
3
&
6"((
:;
!"#$%&'()"*)"
!"#$%&&'(#)"*#+",#'-'.,&'#&)'#/,'(+0#+",#1'&#%#
!"#$%#
#2%.34#5)67#67#&)'
#
89:("6:;<=>6&'#':6&6"9#"?#&)'#:%&%2%7'#.,(7"(0#%#."9.'@&#,7':#69#$%9+
#
:%&%2%7'#7+7&'$74#A6&)#&)'#.,(7"(0#+",#.%9B
!
C69:#",&#)"*#$%9+#("*7#%('#69#&)'#('7,D&#7'&#E6%#
2')!%"6).:
!
F&'(%&'#"E'(#&)'#("*7#E6%#
5%<'=%>1#$).:
0#
5%<'=%?'@).:
0#%9:
#
1$AB)'#C7$).:
!
C69:#",&#&)'#9%$'7#"?#&)'#."D,$97#E6%#
2')!%("56?75'$.:
0#."9E'(&
#
&)"7'#69&"#."D,$9#9,$2'(7#E6%#
2')!%("56D6+'@.:
0#%9:#1'&#E%D,'7#?"(
#
&)'#.,(('9&#("*#?"(#%#16E'9#."D,$9#E6%#$'&)":7#D63'#
2')E)#162.:
0
#
2')D6).:
0#'&.4
!
G'H'-'.,&'#&)'#/,'(+#&)%&#.('%&':#&)'#.,(7"(#E6%#
#'F"'#G.:
!
G'D'%7'#&)'#.,(7"(I7#('7",(.'7#E6%#
4(%$'.:
C"(#'-%$@D'0#)'('#*'#6&'(%&'#"E'(#%#
01+2')$
#&%2D'#'9&(6'7B
!"#$%#
&#'$"()
*
&&+,
-
$&'("#$%
.
/EHCH!=&DI3&675'3&16<'6)%#G&>JKL&01+2')$/
3
&
6"((
:;
0M1('
&
.N
#'$"()
-
)*+#,*-#./
.::
&
O
&
16)
&1+
*
#'$"()
-
0#/12/
.
P
:;
&
E)#162
&675'
*
#'$"()
-
0#/3/$420
.
Q
:;
&
16)
&16<'6)%#G
*
#'$"()
-
0#/12/
.
R
:;
&
SS&+%&$%5')M162&"$'B"(&01)M&)M'$'
T
#'$"()
-
56*7#
.:;
J",#.%9#%D7"#*(%@#%#
!"#$%#
#69#%#
E159('!"#$%#A+79)'#
#"(#"&)'(
#
6 $ @ D'$'9 & % & 6"9 0#& )'9#) % 9:#& )'#('7,D & 6 9 1#%:% @ &'(#&"#%#
C1$)U1'0
#"(#"&)'(
#
7'D'.& 6"9#*6:1'& 4#!"&'0#& )",1 )0#& )% &#6?#+",#% ('#1"6 91#&"#,7'#
!"#$%#A+79)'#
#"(
#
6&7#7,2.D%77'7#KD63'#
E159('!"#$%#A+79)'#
L0#+",(#('7,D&#7'&#"?#+",(#/,'(+#
!"#$
%
."9&%69#%9#69&'1'(#."D,$9#9%$':#
VDI
#&)%&#67#,96/,'#?"(#&)'#('7,D&#7'&4#5)67
#
234
Where’s the
bug?
Putting SQLite results on display

Concept: Cursor feeds into CursorAdapter for AdapterView

Example: SimpleCursorAdapter extends CursorAdapter
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
!"#$%&'()&*+,"-,&./$+/001
$/#/#
Cursor
C4
*0)E
aF

$)*0/#*
2()
4-
*
2.-
$)/# -
.0'/. /1$
getCount()
bF


/
-
/
*
1
-/# -
*
2.1$
moveToFirst()
C
moveToNext()
C)
isAfterLast()
cF

$)*0//# )( .*!/# 
*'0().1$
getColumnNames()
C
*)
1
-//#*. $)/
*

*'0())0( -.1$
getColumnIndex()
C)"
/1
'0 .!
*-/# 0--
)/-
*
2
!
*-"$
1
)
*'0()1$( /#*.'$&

getString()
C
getInt()
C /
F

*- 3
(+' C# -
2
$/
-
/
*
1
-=/$/$*0.
widgets
/'
L
.-
*
2.E
Cursor
result
=
db
.
rawQuery
(
"SELECT _id, name, inventory FROM widgets"
,
null
null
);
while
while
(
result
.
moveToNext
())
{
int
id
=
result
.
getInt
(
0
);
String
name
=
result
.
getString
(
1
);
int
inventory
=
result
.
getInt
(
2
);
// do something useful with these
}
result
.
close
();
2$',-3+4#$%&*+,"-,53/6'),
)*/# -2

4/
*0. 
Cursor
$./
*2-
+$/$)
CursorAdapter
F
0./.
ArrayAdapter
+/.--

4
.
C
CursorAdapter
+/.
Cursor
*% /.
C(&$)"/# $-/
1
$'' /
*
)
AdapterView
'$&

ListView
F
#  .$ ./2

4/
*. /*) *!/# . 0+$./
*0.
SimpleCursorAdapter
C2
#$# 3/
).
CursorAdapter
)+-
*
1$ ..*( *$' -+'/
'*"$!
*-/&$)"1
'0 .*0/*!
*'0().
)+0/
/$)"/# ($)/
*-
*
2
View
*% /.!
*-
ListView
T*-*/# -
AdapterView
UF#
.(+' ++* .%0.//#/E
@SuppressWarnings
(
"deprecation"
)
@Override
public
public
void
onPostExecute
(
Void
arg0
)
{
SimpleCursorAdapter
adapter
;
if
if
(
Build
.
VERSION
.
SDK_INT
>=
Build
.
VERSION_CODES
.
HONEYCOMB
)
{
adapter
=
new
new
SimpleCursorAdapter
(
getActivity
(),
R
.
layout
.
row
,
constantsCursor
,
new
new
String
[]
{
DatabaseHelper
.
TITLE
,
DatabaseHelper
.
VALUE
},
789
2:;
&<
5:5=57;7
>?@
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
new
new
int
[]
{
R
.
id
.
title
,
R
.
id
.
value
},
0
);
}
else
else
{
adapter
=
new
new
SimpleCursorAdapter
(
getActivity
(),
R
.
layout
.
row
,
constantsCursor
,
new
new
String
[]
{
DatabaseHelper
.
TITLE
,
DatabaseHelper
.
VALUE
},
new
new
int
[]
{
R
.
id
.
title
,
R
.
id
.
value
});
}
setListAdapter
(
adapter
);
}

-
C2
-
/
''$)"
SimpleCursorAdapter
/
*/&
-
*
2.*0/*!
Cursor
)( 
constantsCursor
C/0-)$)" #$)/
*)$)?/

R.layout.row
ViewGroup
C$)/#$.
. C
RelativeLayout
#*'$)"+$-*!
TextView
2$"
/.E
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout
<RelativeLayout
xmlns:android=
"http://schemas.android.com/apk/res/android"
android:layout_width=
"match_parent"
android:layout_height=
"wrap_content"
>
>
<TextView
<TextView
android:id=
"@+id/title"
android:layout_width=
"wrap_content"
android:layout_height=
"wrap_content"
android:layout_alignParentLeft=
"true"
android:textSize=
"20sp"
android:textStyle=
"bold"
/>
/>
<TextView
<TextView
android:id=
"@+id/value"
android:layout_width=
"wrap_content"
android:layout_height=
"wrap_content"
android:layout_alignParentRight=
"true"
android:textSize=
"20sp"
android:textStyle=
"bold"
/>
/>
</RelativeLayout>
</RelativeLayout>

*- #-
*
2$)/#
Cursor
C/# 
*'0().)( 
title
)
value
T-
+-
. )/

4
TITLE
)
VALUE

*)./)/.*)
DatabaseHelper
U-
/
* +*0-
$)/
*/# $-
-
.+ /$
1

TextView
2$"
/.T
R.id.title
)
R.id.value
UF

0. /2
*$<
-
)/1
-.$*).*!/#
SimpleCursorAdapter

*)./-0/
*- 0. *)
2
. +-
/
$)


1
'aaF
0. /#
Build
'../
* /
/2
#$#

' 1
'2

-
*))#**. /# -$"
#/
*)./-0/
*-

*-
$)"
'
4
F
2$''"
*$)/
*/#$./
#)$,0
$)"-
/
- /$'
$)'/
-#+/
-
F
!"#
$%&
'(
)%)*)!&!
+,-
After
API
level 11:
Difference: flags parameter
Putting SQLite results on display

SimpleCursorAdapter maps individual rows from
constantsCursor to R.layout.row ViewGroup with a pair of
TextView widgets
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
new
new
int
[]
{
R
.
id
.
title
,
R
.
id
.
value
},
0
);
}
else
else
{
adapter
=
new
new
SimpleCursorAdapter
(
getActivity
(),
R
.
layout
.
row
,
constantsCursor
,
new
new
String
[]
{
DatabaseHelper
.
TITLE
,
DatabaseHelper
.
VALUE
},
new
new
int
[]
{
R
.
id
.
title
,
R
.
id
.
value
});
}
setListAdapter
(
adapter
);
}

-
C2
-
/
''$)"
SimpleCursorAdapter
/
*/&
-
*
2.*0/*!
Cursor
)( 
constantsCursor
C/0-)$)" #$)/
*)$)?/

R.layout.row
ViewGroup
C$)/#$.
. C
RelativeLayout
#*'$)"+$-*!
TextView
2$"
/.E
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout
<RelativeLayout
xmlns:android=
"http://schemas.android.com/apk/res/android"
android:layout_width=
"match_parent"
android:layout_height=
"wrap_content"
>
>
<TextView
<TextView
android:id=
"@+id/title"
android:layout_width=
"wrap_content"
android:layout_height=
"wrap_content"
android:layout_alignParentLeft=
"true"
android:textSize=
"20sp"
android:textStyle=
"bold"
/>
/>
<TextView
<TextView
android:id=
"@+id/value"
android:layout_width=
"wrap_content"
android:layout_height=
"wrap_content"
android:layout_alignParentRight=
"true"
android:textSize=
"20sp"
android:textStyle=
"bold"
/>
/>
</RelativeLayout>
</RelativeLayout>

*- #-
*
2$)/#
Cursor
C/# 
*'0().)( 
title
)
value
T-
+-
. )/

4
TITLE
)
VALUE

*)./)/.*)
DatabaseHelper
U-
/
* +*0-
$)/
*/# $-
-
.+ /$
1

TextView
2$"
/.T
R.id.title
)
R.id.value
UF

0. /2
*$<
-
)/1
-.$*).*!/#
SimpleCursorAdapter

*)./-0/
*- 0. *)
2
. +-
/
$)


1
'aaF
0. /#
Build
'../
* /
/2
#$#

' 1
'2

-
*))#**. /# -$"
#/
*)./-0/
*-

*-
$)"
'
4
F
2$''"
*$)/
*/#$./
#)$,0
$)"-
/
- /$'
$)'/
-#+/
-
F
!"#
$%&
'(
)%)*)!&!
+,-
Title,Value pairs
are matched!
Note:
_id column
is required!
and finally

Do not close() and thus destroy the Cursor until it is no
longer needed.

Example: Cursor retrieved from CursorAdapter via
getListAdapter() on fragment.
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&

*/
C/#*0"
#C/#/$!4
*0-
"
*$)"/
*0.
CursorAdapter
*-$/..0'.. .T'$&

SimpleCursorAdapter
UC4
*0--
.0'/. /*!4
*0-,0 -
4
(0./

*)/$))$)/
"
-

*'0())( 
_id
/#/$.0)$,0 !
*-/# -
.0'/. /F#$.M$N1
'0 $./# ).0++'$ 
/
*( /#*.'$&

onListItemClick()
C/
*$ )/$!
42
#/$/
(/# 0. -'$&
0+*)$)
/#
AdapterView
F
*/
/#//#$.-
,0$-
( )/$.*)/# -
.0'/. /$)/#
Cursor
C.*$!
4
*0#
1
.0$/' 
*'0()$)/' /#/$.)*/)( 
_id
C4
*0)-
)( $/$)
4
*0-,0 -
4T F"
FC
SELECT key AS _id, ...
UF
2$''. .+ $= 3
(+' *!/#$.
'/
/
-/
#)$,0
'/
-$)/#$.#+/
-
F
'.*)*/
/#/4
*0))*/
close()
/#
Cursor
0. 
4
CursorAdapter
0)/$'4
*0)*
'*)"
-) /#
CursorAdapter
F#/$.2
#
42
*)*/'*. /#
Cursor
0)/$'
onDestroy()
*!/# !-
"( )/E
@Override
public
public
void
onDestroy
()
{
super
super
.
onDestroy
();
((
CursorAdapter
)
getListAdapter
()).
getCursor
().
close
();
db
.
close
();
}

-
/-$ 1
/#
Cursor
!-
*(/#
CursorAdapter
C2
#$#2
"
/
4''$)"
getListAdapter()
*)/# !-
"( )/F
!"##$%&'()#)'*+#,'-./%0123%3+.4/


 ''
4
C,0 -$ .-
*) *)&"-
*0)/#-
C./# 4(
4/&
.*( /$( F
) ++-
*#!
*-*$)"/#/$./
*0. )
AsyncTask
F
)/# .(+' ++'$/$*)C
ConstantsFragment
&$&.*<
LoadCursorTask
$)
onActivityCreated()
T.#*
2)
*
1
UF
LoadCursorTask
$.-
.+*).$' !
*-*$)"/# ,0 -
4T1$/#
doQuery()
( /#*.#*
2)*
1
U)+0/
/$)"/# -
.0'/.$)/#
ListView
$).$ /# !-
"( )/E
private
private
class
class
LoadCursorTask
LoadCursorTask
extends
extends
AsyncTask
<
Void
,
Void
,
Void
>
{
private
private
Cursor
constantsCursor
=
null
null
;
@Override
protected
protected
Void
doInBackground
(
Void
...
params
)
{
constantsCursor
=
doQuery
();
constantsCursor
.
getCount
();
return
return
(
null
null
);
}
@SuppressWarnings
(
"deprecation"
)
@Override
567
89:
'(
-9-;-5:5
<=>
Transactions beyond a single SQL statement

Default behavior:

Each SQL statement executes its own transaction

Why bother to have more statements in a single transaction?

Data integrity may depend on success of set of statements

Performance as each transaction involves disk I/O

How to achieve this?
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
#
InsertTask
$..0++'$ 
ContentValues
*% /2$/#*0-
title
)
value
A%0./
.2
0. $)
onCreate()
*!
DatabaseHelper
D
)
doInBackground()
A2
"
/
2-$/' /. !-
*(
DatabaseHelper
)+ -!
*-(/#
insert()
''A.*/#
/. 
M* .)*//$ 0+/# ($)++'$/$*)/#-
D

*
2
1
-
A$)
doInBackground()
A2

'.*
''
doQuery()
"$)D#$.-
/-$ 1
.!-
.#
Cursor
2$/#/# ) 2-
*./
-*!
*)./)/.
E$)'0$)"/# *) 2
%0./$). -/
D.2$/#
LoadCursorTask
A2
 3
0/

doQuery()
$)
doInBackground()
/
*&
+/# /.

M*:/# ($)++'$/$*)/#-
D
# )A$)
onPostExecute()
A2
).!
'
40+/
/# 
2$/#/# ) 2
Cursor
D
*
/#$.
4''$)"
changeCursor()
*)*0-
CursorAdapter
A-
/-$ 1
!-
*(/# !-
"( )/
1$
getListAdapter()
D
changeCursor()
2$''.2
+*0/*0-*'
Cursor
$)*0-
SimpleCursorAdapter
2$/#/# ) 2*) A0/
*(/$''
40+/$)"/#
ListView
'*)"
/# 2

4
D
!"##$%&'()*%+*,#$-%'.-/%0+
4 !0'/A # .//
( )/ 3
0/
.$)$/.*
2)/-
)./$*)P/#$.$.!$-
'
4
/4+$' #
1$*-!
*- /. A) $/
$.)* 3

+/$*)D
# -
-
/2
*-
.*).2
#
44
*0($"
#/2
)//
*#
1
4
*0-*
2)/-
)./$*)*0).
A
'-
"
-/#).$)"
' .//
( )/C
_D
# '..$K
2
) /# .//
( )/./
*.0

*-!$'.2
#*'
L-
/$*)' A
!
*-($)/$)$)"/$)/
"-$/4
`D

-!
*-()
A. #/-
)./$*)$)
1
*'
1
.$.&
M)A.#. ))*/
A
$.&
M) -
/# -.'*
2
# .$-
$+ !
*-4
*0-*
2)/-
)./$*).$.C
try
try
{
db
.
beginTransaction
();
// several SQL statements in here
db
.
setTransactionSuccessful
();
}
finally
finally
{
db
.
endTransaction
();
}
!12
3(4
'5
6(6.6!4!
788
SQLite-Primer

SQLite Database resides on machine’s flash memory

fairly quick read operations,

potentially slow write operations

Emulator works on files
, may mislead you with quick write
operations

DB operations should use
asyncTask / separate thread
to
avoid disruption to the UI thread

Database can be explored from adb shell with sqlite3

Database file stable across platforms:

can also be pulled from device, manipulated externally with
SQLite-aware client and pushed back onto device
Background Operations with SQLite

SQLite is thread safe if threads operate on same instance of
DataBaseHelper.

suggests use of Singleton Pattern
(Horstmann Ch 10)

SQLite operations are heavy:

Instantiation on demand:

first access causes call to dbhelper.onCreate()

Queries & updates can be time consuming

should run on AsyncTask or separate thread
Singleton Pattern (Horstmann, Ch 10)

Context

All clients need to access a single shared instance of a
class.

You want to ensure that no additional instances can be
created accidentally.

Solution

Define a class with a private constructor.

The class constructs a single instance of itself.

Supply a static method that returns a reference to the
single instance.
Singleton Random Number Generator
public final class SingleRandom {
private final Random generator;
private static SingleRandom instance  null ;
private static long theSeed  0 ;


p r i v a t e S i n g l e R a n d o m ( )
{
g e n e r a t o r  ( 0 ! t h e S e e d ) ? n e w R a n d o m ( t h e S e e d ) : n e w R a n d o m ( );
}
p u b l i c s t a t i c v o i d s e t S e e d ( fi n a l l o n g s e e d ) { t h e S e e d  s e e d; }


p u b l i c s t a t i c S i n g l e R a n d o m g e t R a n d o m ( )
{


if (null  instance)



instance  new SingleRandom();


return instance;

}

p u b l i c i n t n e x t I n t ( ) { r e t u r n g e n e r a t o r.n e x t I n t ( ); }
}
Instantiating a Single DataBaseHelper

Constructor of DataBaseHelper needs a Context

if current activity is used (which may come and go) the
DataBaseHelper prohibits garbage collection thanks to its
reference

Suggestion: use
getActivity().getApplicationContext()
to
obtain Context of overall application which is a singleton
itself and guaranteed to exist (as it is created shortly after
process creation).

Apply Singleton Pattern

less robust: use public static variable to share reference for
single DataBaseHelper
Asynchronous DB Update

Constants Demo Example

User adds a title & value pair

App needs to:

obtain data from UI

add data to SQLite DB

update data on screen

in a separate thread or AsynchTask
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
$"0-
OTU=# 
*)./
)/.-
*
2. -(+
' <

*)./
)/$'*"

!/# 0. -;''.$)
*)./)/)'$&./# K

L0/
/
*)A2
) /
*$). -/) 2
-

*-
$)/# /. D#/$.#)' 1$)
InsertTask
C
private
private
class
class
InsertTask
InsertTask
extends
extends
AsyncTask
<
ContentValues
,
Void
,
Void
>
{
private
private
Cursor
constantsCursor
=
null
null
;
@Override
protected
protected
Void
doInBackground
(
ContentValues
...
values
)
{
db
.
getWritableDatabase
().
insert
(
DatabaseHelper
.
TABLE
,
DatabaseHelper
.
TITLE
,
values
[
0
]);
constantsCursor
=
doQuery
();
constantsCursor
.
getCount
();
return
return
(
null
null
);
}
@Override
public
public
void
onPostExecute
(
Void
arg0
)
{
((
CursorAdapter
)
getListAdapter
()).
changeCursor
(
constantsCursor
);
}
}
!"#
$%&
'(
)%)*)!&!
+,-
Image: M.Murphy, Android 4.3 Fig 167
Asynchronous DB Update

constantsCursor shared to deliver query results to UI
7KLVGRFXPHQWLVOLFHQVHGIRU3HWHU.HPSHU
VH[FOXVLYHXVHE\&RPPRQV:DUH//&
$"0-
OTU=# 
*)./
)/.-
*
2. -(+
' <

*)./
)/$'*"

!/# 0. -;''.$)
*)./)/)'$&./# K

L0/
/
*)A2
) /
*$). -/) 2
-

*-
$)/# /. D#/$.#)' 1$)
InsertTask
C
private
private
class
class
InsertTask
InsertTask
extends
extends
AsyncTask
<
ContentValues
,
Void
,
Void
>
{
private
private
Cursor
constantsCursor
=
null
null
;
@Override
protected
protected
Void
doInBackground
(
ContentValues
...
values
)
{
db
.
getWritableDatabase
().
insert
(
DatabaseHelper
.
TABLE
,
DatabaseHelper
.
TITLE
,
values
[
0
]);
constantsCursor
=
doQuery
();
constantsCursor
.
getCount
();
return
return
(
null
null
);
}
@Override
public
public
void
onPostExecute
(
Void
arg0
)
{
((
CursorAdapter
)
getListAdapter
()).
changeCursor
(
constantsCursor
);
}
}
!"#
$%&
'(
)%)*)!&!
+,-
On Using a Database

Creating a database

How to create and fill database when used first time after
installation of app?

How to update/adjust an existing database to a new
schema that comes with an update for an existing app?

Reading data from a database / writing data to a database

How to open an existing database?

How to create/read/write/modify/delete particular entries?

How to close a database?