Android SQLite Database

scacchicgardenSoftware and s/w Development

Dec 13, 2013 (3 years and 7 months ago)

111 views

Android
SQLite Database
CS 301
Peter Kemper
Reference: Murphy’s Busy Coder’s Guide, SQLite Databases p461 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

Popular embedded database

Combines SQL interface with small memory footprint and
decent speed

integrated in Android runtime

Native API not JDBC

Example taken from Android’s SensorManager
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

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/write/modify/delete particular entries?

How to close a database?
SQLite Primer

Implement a Subclass of SQLiteOpenHelper

to create & open a database

The constructor, chaining upward to the SQLiteOpenHelper constructor. This
takes the Context (e.g., an Activity), the name of the database, an optional cursor
factory (typically, just pass null), and an integer representing the version of the
database schema you are using.

Note: following examples from Murphy operate with a simpler constructor

onCreate(), which passes you a SQLiteDatabase object that you need to
populate with tables and initial data, as appropriate.

onUpgrade(), which passes you a SQLiteDatabase object and the 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
SQLite Primer

SQL dialect

Data definition: CREATE TABLE

Data manipulation: INSERT

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:;
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 4.3
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

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

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

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
Two ways to retrieve data using SELECT:

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

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

Example for a raw query:

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

results are ordered by entries in column “title”

result is accessible via a “Cursor” object
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

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

A Query returns a Cursor to iterate over returned results:

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()
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?
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 machines flash memory

fairly quick read operations,

potentially slow write operations

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

DB operations should operate in an asyncTask or separate
thread to avoid disruption to the UI thread

Database can be explored from adb shell with sqlite3

Database 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 ;

private SingleRandom() {
generator  (0 ! theSeed) ? new Random(theSeed) : new Random();
}
public static void setSeed(final long seed) { theSeed  seed; }

public static SingleRandom getRandom() {


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?