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
• • •
integrated in Android runtime
Combines SQL interface with small memory footprint and decent speed 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
2%$34*&5678&9:*&;-#+0"#0+&+"<=.*&"==.%)"0%-#>&"+&%#%0%"..?&."3#)
â&#x20AC;˘ â&#x20AC;˘
Constants sample application based on SQLite DB !"#$%&'$("($#($&$)*'#$+"$&,,$&$'*-$%"'.+&'+/$-01%0$231'4.$#($&
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+ 6155$1'$+0*$'&)*$&',$7&5#*$"6$+0*$%"'.+&'+8
as initially launched plus its add-constant dialog
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
>?@+)#$"*($*$<#4$2-*%#($4"#6#$+)$,#D+*)#($<6&8$)"#$>?@1EF$()*',*6,=$'&$ ,+<<#6#') $)"*' $ 8&() $ >?@$,*)*5*(#(. $!"# $0&&, $ '#4( $ +( $)"*) $ >?@+)# $ +( $(&$ SQLite Primer (2*%#1#<<+%+#') $ )"*) $ )"# $ /',6&+, $ 67')+8# $%*' $ +'%-7,# $ *-- $ &< $ >?@+)#= $ '&)$ SQL dialect (&8#$*65+)6*6G$(75(#)$)&$)6+8$+)$,&4'$)&$(+H#.
•
• Data definition: CREATE TABLE !"# $ 5+00#() $ ,+<<#6#'%# $ <6&8 $&)"#6 $ >?@ $ ,*)*5*(#( $G&7 $ 4+-- $ #'%&7')#6 $ +($ Data manipulation: INSERT • 26&5*5-G$)"#$,*)*$)G2+'0.$I"+-#$G&7$%*'$(2#%+<G$)"#$,*)*$)G2#($<&6$%&-78'($ +'$*$$(")%"*%)+#" $()*)#8#')=$*',$4"+-#$>?@+)#$4+--$7(#$)"&(#$*($*$"+')=$)"*)$ SELECT ... FROM ... WHERE • Queries: +($*($<*6$*($+)$0&#(.$J&7$%*'$27)$4"*)#D#6$,*)*$G&7$4*')$+'$4"*)#D#6$%&-78'$ standard, not supported • deviates from SQL-92 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#,$+'$)"#$ FOREIGN KEY constraints, nested transactions, • ,&%78#')*)+&'Q RIGHT OUTER JOIN, FULL OUTER JOIN, ...
•
Most particular: “Manifest typing” !"#$%"&'()*#*+,&"-.#*/(#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)#,$+'$
J%(#)D'3:7)6#2)()#/*#'#85%595:&*&+(&,#97'**#"(%3#85%595:&>./):%5)%:#,2',6#/0# /).,&5%&676#9()',)*#'#,'57)#'0+#'++*#'#0&35)(#%"#(%-*6#'0+#/0 #/);(<,5=&67>92)',*>#5$#+(%::/01#,2)#)D/*,/01#,'57)#'0+#)D)9&,/01#/).,&5%&67G
Example: Database/Constants
(52?5<&-2/@A2/@@/):B5,&A5)=,/$=A2/):%5)%:C
Note: Example slightly different in Murphy 4.3 This document is licensed for Peter Kemper's exclusive use by CommonsWare, LLC
!"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+ $@(/,%-5)=,/$=A2/)%&)%A./)%&)%D5+E&:C $@(/,%-5)=,/$=A2/)%&)%A./)%&0%C $@(/,%-5)=,/$=A=5%595:&A.E,:/,C !"#$%&'()*%$!*+*(&(,(-.+/01234.#&!$)5 !"#$%&'()*%$!*+*(&(,(-.+-67!&.+/01!&.8#.)9.7#.%5 678 !"#$%&'()*%$!*+*(&(,(-.+-67!&.+/01!&.:(&(,(-.5 !"#$%&'()*%$!*+;(%*<(%.+/.)-$%=()(>.%5
#?,7!4'47(--':(&(,(-.9.7#.%'.3&.)*-'/01!&.8#.)9.7#.%'@ ''#%!A(&.'-&(&!4'B!)(7'/&%!)>':CDCEC/2FGC=2HI*,I5 ''#?,7!4'-&(&!4'B!)(7'/&%!)>'DJD12HI&!&7.I5 ''#?,7!4'-&(&!4'B!)(7'/&%!)>'KC1L2HIA(7?.I5 '' ''#?,7!4'!"#"$"%&'&()&*MN$)&.3&'4$)&.3&O'@ ''''-?#.%M4$)&.3&P':CDCEC/2FGC=2P')?77P'QO5 ''R '' ''S8A.%%!*. ''#?,7!4'A$!*'+,-*&"#&M/01!&.:(&(,(-.'*,O'@ ''''*,+&.&/012MINT2CD2'DCE12'4$)-&()&-'MF!*'JGD2U2T'VTJ=CTW'X2W'CLD8JGNT2=2GDP' &!&7.'D2YDP'A(7?.'T2C1O5IO5 '''' ''''N$)&.)&K(7?.-'4AH).<'-+,#&,#3"(4&%MO5 '''' ''''4A+)4#MDJD12P'IU%(A!&ZP':.(&;'/&(%'JIO5 ''''4A+)4#MKC1L2P'/.)-$%=()(>.%+UTCKJDWF:2CD9F/DCTFJO5 ''''*,+5,%&*#MI4$)-&()&-IP'DJD12P'4AO5 '''' ''''4A+)4#MDJD12P'IU%(A!&ZP'2(%&;IO5 ''''4A+)4#MKC1L2P'/.)-$%=()(>.%+UTCKJDWF2CTD9O5 ''''*,+5,%&*#MI4$)-&()&-IP'DJD12P'4AO5 '''' ''''4A+)4#MDJD12P'IU%(A!&ZP'[?#!&.%IO5 ''''4A+)4#MKC1L2P'/.)-$%=()(>.%+UTCKJDWF[LVJD2TO5
!!!! !!!! !!!!"#$!"#%878(*+!@513#BAC+!&-.D/@<= !!!!"#$!"#%878(*+!@513#BAC+!8E-!7/F3.>@<= !!!!"#$!"#%&'()*+!,-./0123.34-1$56'&789:&*;),<= !!!!"#$!"#%&'()*+!,-./0123.34-1$56'&789:8G*:7,(';H<= !!!!>?$$%&'(#%@"0./A3.A/@+!878(*+!"#<= !!!!>?$$%&'(#%@"0./A3.A/@+!878(*+!"#<= !!I !!!! !!!!"#$!"#%878(*+!@513#BAC+!)13.D/@<= !!JK#-11B>!!!!"#$!"#%&'()*+!,-./0123.34-1$56'&789:)6';),<= !!LD?FB"!#0B>!)%*!+(,-'%,M(BA-H3A3?3/-!>?+!B.A!0F>&-1/B0.+!B.A!.-N&-1/B0.<!O !!!!>?$$%&'(#%@"0./A3.A/@+!878(*+!"#<= !!!!3.>10B>$DABF$(04$.%@P0./A3.A/@+!@)L413>B.4!>3A3?3/-+!NEB"E!NBFF!>-/A10C!3FF! !!!! 0F>!>3A3@<= !!!!"#$!"#%878(*+!@513#BAC+!&-.D/@<= !!!!"#$!"#%&'()*+!,-./0123.34-1$56'&789:&*;),<= !!!!>?$'/'0123%@H6KQ!8'R(*!7S!*T7,8,!"0./A3.A/@<= !!!!>?$$%&'(#%@"0./A3.A/@+!878(*+!"#<= !!!!)%4(',#'%>?<= !!I !!I 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! !"#$%&#'"$(# ,M(BA-KL-.G-FL-1#%$)*+,%%-#*(&,.&#,/0#1"+0#"/."#,/#2/%.,/*&#"3# 0F>!>3A3@<= 2.4 # !1&/- # 51&/ #'"$ # /&&0 # , #,M(BA-H3A3?3/-# ")6&*. # ." # 0" # 7$&(2&% # "( # 0,.,# !!!!>?$'/'0123%@H6KQ!8'R(*!7S!*T7,8,!"0./A3.A/@<= 8"0232*,.2"/%!!!!)%4(',#'%>?<=# ,%9 # '"$( #,M(BA-KL-.G-FL-1# ." #4-A6-3>3?F-H3A3?3/-%<# "(# !!I 4-AU1BA-3?F-H3A3?3/-%< - # 0&:&/02/; # $:"/ # 51&.1&( # "( # /". # '"$ # 52++ # )&# I
Example: Database/Constants ...
*1,/;2/;#2.%#*"/.&/.%4#<"(#&=,8:+&-#"$(#P0./A3.A/R10N/-1#,*.2>2.'#":&/%#.1&# 0,.,),%&#2/#0.P1-3A-%<#,%#:,(.#"3#0"2/;#,#7$&('? !"#$%&#'"$(# to open ,M(BA-KL-.G-FL-1 database for#%$)*+,%%-#*(&,.&#,/0#1"+0#"/."#,/#2/%.,/*&#"3# read / write access use helper db 2.4 # !1&/- # 51&/ #'"$ # /&&0 # , #,M(BA-H3A3?3/-# ")6&*. # ." # 0" # 7$&(2&% # "( # 0,.,# "0./A3.A/PD1/01V>? 8"0232*,.2"/%# ,%9 # '"$( #,M(BA-KL-.G-FL-1# ." #4-A6-3>3?F-H3A3?3/-%<# "(# !!!!!!!!!!!!!!!!!!$+'#5',-,67'8,#,6,&'%< !!!!!!!!!!!!!!!!!!$(,.2"'(9%@,*(*P8!:7H+!ABAF-+!#3FD-!@W 4-AU1BA-3?F-H3A3?3/-%< - # 0&:&/02/; # $:"/ # 51&.1&( # "( # /". # '"$ # 52++ # )&# !!!!!!!!!!!!!!!!!!!!!!!!!!!@S6K2!"0./A3.A/!K6H*6!R9!ABAF-@+ *1,/;2/;#2.%#*"/.&/.%4#<"(#&=,8:+&-#"$(# P0./A3.A/R10N/-1#,*.2>2.'#":&/%#.1&# !!!!!!!!!!!!!!!!!!!!!!!!!!!.DFF<= 0,.,),%&#2/#0.P1-3A-%<#,%#:,(.#"3#0"2/;#,#7$&('?
once done, call close() @1&/#'"$#,(&#0"/&#52.1#.1&#0,.,),%&#A&4;4-#'"$(#,*.2>2.'#2%#)&2/;#*+"%&0B-# "0./A3.A/PD1/01V>?
%28:+'#*,++# "F0/-%<#"/#'"$(#,M(BA-KL-.G-FL-1#."#(&+&,%&#'"$(#*"//&*.2"/4 !!!!!!!!!!!!!!!!!!$+'#5',-,67'8,#,6,&'%< !!!!!!!!!!!!!!!!!!$(,.2"'(9%@,*(*P8!:7H+!ABAF-+!#3FD-!@W
1'('.'0&;$<'##)*+$'$1'('.'0&$&##"#3$(7)0$:&(7"1$#&(-#*0$*"(7)*+;
SQLite Primer
="3 $>"# $&2':5/&3$,"- $%'* $%'// $!"!#$%&'($ (" $%#&'(& $(7& $#/0.*,0*.$ ('./&3 $'0$ 07"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
DB queries as strings and3 $ 4)(7 executed. $ 4)// $ %#&'(& generated $ ' $ ('./&3 $ *':&1 $ $ ' $ 5#):'#, $ A&, $ %"/-:*$ •@7)0 *':&1$ $(7'($)0$'*$'-("B)*%#&:&*(&1$)*(&+&#$C);&;3$=D9)(&$4)//$'00)+*$(7&$ Creating a table with $ $ C(&2(E$ •6'/-&$>"#$,"-$47&*$,"-$)*0&#($#"40E3$5/-0$(4"$1'('$%"/-:*0? '*1 $ $ C' $ >/"'(3 $"# $ F#&'/F $ )* $ =D9)(& $(&#:0E; $ =D9)(& $4)// $'-(":'()%'//,$ %#&'(&$'*$)*1&2$>"#$,"-$"*$,"-#$5#):'#,$A&,$%"/-:*$G$,"-$%"-/1$'11$"(7&#$ • a primary key column named _id and auto-incremented #/0.*,0*.
?)6
*)*2!
K,2L!
integer ids )*1&2&0$7&#&$6)'$0":&$ 59:;<:=@A+:J$0('(&:&*(03$)>$,"-$0"$%7"0&$(";
• •
data column named title
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'(&$ data column named value '$*&4$#&/&'0&$">$,"-#$'55/)%'()"*;$I>$,"-$1"$*"($%7'*+&$,"-#$('./&$0%7&:'03$ ,"$ :)+7(indexes $ *&6&# $ 1#"5 $ ,"-# $ )*1&2&03 $ .-( $ )>INDEX $ ,"- $ 1"3 $ J-0( $ -0&$ other could be$ ('./&0 added$ "#with CREATE !"!#$%&'($("$)*6"A&$+9HC=@A+:J$'*1$+9HC=<;>&:$0('(&:&*(0$'0$*&&1&1;
•
statements
INSERT, UPDATE, DELETE operations in a similar manner •!"5%#6&/"0" K)6&*$(7'($,"-$7'6&$'$1'('.'0&$'*1$"*&$"#$:"#&$('./&03$,"-$5#".'./,$4'*($
4.#*:-%&-%?&$&1)()$-5
SQLite Primer
@"$%)A&1?')/%.)$)%;)%!"#$%&'(%&%*);%$";%+*("%"#$%34"#&,"&#%(&2')B *%!5,&$654!+6!"#$%&&'(('1!,-478%,**$%69%,**$%(6: 66;4"&$"&<,-)$#65,-)$#="$96)#*+%*+,-./%&'>(? 665,-)$#@!/+'A&!&-$AB69%,**$%@0%+12+.%'((? 665,-)$#@!/+'A5,-)$AB69%,**$%@0%+,-./%'((? 66+2@0%+3"2+-4.%5-+-4-&%'(@2*&%"+'A34"#&,"&#AB6A&!&-$AB65,-)$#(? 6634"#&,"&#;)%#4%@"%6/%"7'(? C
Alternative to add/delete/modify data: use insert(), update(), •6.)-)%1)(."0-%1&:)%#-)%"8% %"23)4(-/%;.+4.%+1?')1)*(%&% ;4"&$"&<,-)$#
D,*C
delete() methods on SQLiteDatabase object )-D#)%+*()$8&4)/%&'2)+(%"*)%(.&(%.&-%&00+(+"*&'%1)(."0-%8"$%;"$:+*E%;+(.% <=>+()%(9?)-5%@"$%)A&1?')/%+*%&00+(+"*%("%7$&'(%("%$)($+),)%&%,&'#)%29%+(-%:)9/% Makes7$&E#F"&$7$%'( use of ContentValues objects 9"#%.&,)% /%7$&E#.&%!"7'( /%&*0%-"%8"$(.5
•
get() to obtain a value by its key • 6.)% %1)(."0%(&:)-%(.)%*&1)%"8%(.)%(&2')/%(.)%*&1)%"8%"*)%4"'#1*% &-%(.)%7*#''%4"'#1*%.&4:7/%&*0%& % %;+(.%(.)%+*+(+&'%,&'#)-%9"#% getAsInteger(), getAsString() .... • ;&*(%?#(%+*("%(.+-%$";5%6.)%7*#''%4"'#1*%.&4:7%+-%8"$%(.)%4&-)%;.)$)%(.)% % +*-(&*4)%+-%)1?(9%F%(.)%4"'#1*%*&1)0%&-%(.)%7*#''%4"'#1*% Insert() parameters: 1) name of table, •.&4:7%;+''%2)%)A?'+4+('9%&--+E*)0%(.)%,&'#)% %+*%(.)%<=>% %-(&()1)*(% !"#$%&'(
;4"&$"&<,-)$#
;4"&$"&<,-)$#
GH00 3) values FG.IJK 2) one column as “null column hack”,
E)*)$&()0%29%!"#$%&'(5
6.) %)*+,&$'(% 1)(."0 % (&:)- % (.) % *&1) % "8 % (.) % (&2')/ % & % ;4"&$"&<,-)$#6
!"#$!"#"$"%&$%#&"'($)'*+,$-+./$&'$'(!)$"%&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.;#/$.&,$<=>?
SQLite Primer
(234)$"54/3!5!"#$%&&'%(%)%%#/0652/78!&59 55:$2306;<5)261=9:$2306>*+(,%-.%2/78!&?@ 55!A>/%)0"1)+2(%'+)+2+&%%&>3%(%)%%B./01$)0$1BC5BD8E=FBC5)261&@ 55./01$)0$1G'21/2>"%4,%"5%&@ ?
Alternative to add/delete/modify data: use insert(), update(), •23"0&4-*+&(5-6#'7&8-9*+&(5-6#' delete() methods on SQLiteDatabase object
@,$).&"$8H:,-I0$JKELI,0$-/($E,M,I,0$A'7$"-;#$&)'$%-./$'5&.'/,$2'*$*#&*.#;./1$ (-&-$2*'%$-$BCD.&#$(-&-3-,#$7,./1$ Delete() has parameters: :,M,GI?
•
•G8 F'7$6-/$7,#$ $&'$37.4($75$-$H7#*A$2*'%$.&,$6'%5'/#/&$5-*&, • optional “where” clause I'/2'7/(./1$%-&&#*,$27*&"#*$.,$&"# $ $64-,,$-/($&"#$.,,7#$ • corresponding parameters to fill the “where” clause “?” '2$67*,'*,$-/($67*,'*$2-6&'*.#,8$D#&J,$&-+#$-44$'2$&".,$'/#$5.#6#$-&$-$&.%#8 Example: remove single row with matching _ID value •!"#$%&'()'* E8 table, F'7$6-/$7,#$2)7N'"2O%&$&'$./;'+#$-$:,M,GI$,&-&#%#/&$(.*#6&4A0$'* P'"2O%&
:NM3$"N'"2OQ'3#!"2
!"#$,.%54#,&$,'47&.'/0$-&$4#-,&$./$&#*%,$'2$&"#$@K=0$.,$2)7N'"2O%&8$B.%54A$6-44$
23"0&4-*+&(5-6#'7&8-9*+&(5-6#' @,$).&"$8H:,-I0$JKELI,0$-/($E,M,I,0$A'7$"-;#$&)'$%-./$'5&.'/,$2'*$*#&*.#;./1$ (-&-$2*'%$-$BCD.&#$(-&-3-,#$7,./1$:,M,GI?
SQLite Primer
E8 F'7$6-/$7,#$ 2)7N'"2O%& $&'$./;'+#$-$ :,M,GI$,&-&#%#/&$(.*#6&4A0$'* Two ways to retrieve data using SELECT:
• $ from its component $64-,,$-/($&"#$.,,7#$ use query() to build up a query parts •I'/2'7/(./1$%-&&#*,$27*&"#*$.,$&"# '2$67*,'*,$-/($67*,'*$2-6&'*.#,8$D#&J,$&-+#$-44$'2$&".,$'/#$5.#6#$-&$-$&.%#8 G8 F'7$6-/$7,#$P'"2O%&$&'$37.4($75$-$H7#*A$2*'%$.&,$6'%5'/#/&$5-*&,
use rawQuery() to invoke a SELECT statement directly, or :NM3$"N'"2OQ'3#!"2
Example for a raw query: •!"#$%&'()'* !"#$,.%54#,&$,'47&.'/0$-&$4#-,&$./$&#*%,$'2$&"#$@K=0$.,$ 8$B.%54A$6-44$ • get (id,title,value) triple for all rows in table “constants” .& $ ).&" $ A'7* $ BCD $ $ ,&-&#%#/&8 $ !"# $ $ ,&-&#%#/& $ 6-/ $ ./647(#$ • results are ordered by entries in column “title” 5',.&.'/-4$5-*-%#&#*,L$&"#$-**-A$'2$&"#,#$2'*%,$A'7*$,#6'/($5-*-%#&#*$&'$ 8$B'0$)#$)./($75$).&"? result is accessible via a “Cursor” object • 2)7N'"2O%&
:,M,GI
:,M,GI
2)7N'"2O%&
./01$)0$1G'21/2=!A 555555555555555555>/%)6%+3+2(%'+)+2+&%%& 555555555555555555>"+78,%"5%B:,M,GI5D8EC5$3$#"C54)#'"5BR 555555555555555555555555555BS-TU5./01$)0$15T-E,-5QV5$3$#"BC 5555555555555555555555555550'##&@
!"# $*#&7*/ $;-47# $ ., $- $G'21/20$)".6" $6'/&-./, $ %#&"'(, $ 2'* $ .&#*-&./1 $';#*$
!"# $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
SQLite-Primer
Regular Query: takes discrete pieces of SELECT statement !"#$(*2#$14$&"#$&*7+#$&1$<'#%;$*5*,(-& â&#x20AC;˘ ! Alternative: !
and builds the query from them !"#$+,-&$14$01+'2(-$&1$%#&%,#)# The name of the table to query against !"#$HI3@3$0+*'-#.$1=&,1(*++;$,(0+'3,(5$=1-,&,1(*+$=*%*2#&#%The list of columns to retrieve !"#$+,-&$14$)*+'#-$&1$-'7-&,&'&#$,($41%$&"1-#$=1-,&,1(*+$=*%*2#&#%-
â&#x20AC;˘ !â&#x20AC;˘ TheJ@AKL-CD WHERE$0+*'-#.$,4$*(; clause, optionally including positional parameters ! â&#x20AC;˘ !"#$ TheIMN7OJ list of $0+*'-#.$,4$*(; values to substitute in for those positional parameters ! â&#x20AC;˘ !"#$ TheA@83@-CD GROUP$0+*'-#.$,4$*(; BY clause, if any ! â&#x20AC;˘ !"#$ clause, if any â&#x20AC;˘ The HAVING !"#-#$0*($7# $#(;;$ /"#($&"#;$*%#$(1&$(##3#3$6#B0#=&$&"#$&*7+#$(*2#.$14$ 01'%-#8H â&#x20AC;˘ The ORDER BY clause, if any !
2%):#PQR-!";(S#$*T17819-1:#=<#%")G1UE !"#"$%#$&"#'&())*++%#$&,-)".&/"0"1"+*+ 2%):#PQR-V&)S$*T1$#:!W;<X):%Y1UE
!"#$%#&#'$"()*+,-!"#$%./01+2')$/3&4%("56$3&/675'*8/3 &&&&&&&&&&&&&&&&&&&&&&&97#5$3&6"((3&6"((3&6"((:; 232
7KLV GRFXPHQW LV OLFHQVHG IRU 3HWHU .HPSHU V H[FOXVLYH XVH E\ &RPPRQV:DUH //&
!"#$%&'()"*)"
:%&%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:#
SQLite-Primer
1$AB)'#C7$).: A Query returns a Cursor to iterate over returned results: ! C69:#",& #&)'#9%$'7#"?#&)'#."D,$97 #E6% # 2')!%("56?75'$.:0#."9E'(&# With the cursor, you can: &)"7'#69&"#."D,$9#9,$2'(7#E6%#2')!%("56D6+'@.:0#%9:#1'&#E%D,'7#?"(# Find out how many rows are in the result set via getCount() &)'#.,(('9&#("*#?"(#%#16E'9#."D,$9#E6%#$'&)":7#D63' # 2')E)#162.:0# Iterate over0#'&.4 the rows via moveToFirst(), moveToNext(), and isAfterLast() 2')D6).:
• • •! !
Find out the names of the columns via getColumnNames(), convert those into column G'H'-'.,&'#&)'#/,'(+#&)%&#.('%&':#&)'#.,(7"(#E6%# #'F"'#G.: numbers via getColumnIndex(), and get values for the current row for a given column via methods like getString(), getInt(), etc. 4(%$'.: G'D'%7'#&)'#.,(7"(I7#('7",(.'7#E6%#
• Re-execute the query that created the cursor via requery() C"(#'-%$@D'0#)'('#*'#6&'(%&'#"E'(#%# 01+2')$#&%2D'#'9&(6'7B Release the cursor's resources via close() • !"#$%#&#'$"()* &&+,-$&'("#$%./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:;
Where’s the bug?
&SS&+%&$%5')M162&"$'B"(&01)M&)M'$' T #'$"()-56*7#.:;
J", # .%9 # %D7" # *(%@ # % #!"#$%## 69 # % #E159('!"#$%#A+79)'## "( # "&)'(#
/4+$ ' # 1$*- !*- / . A ) $/ $. )* 3 +/$*)
•
# - - /2* - .*). 2#4 4*0 ($"#/ 2 )/ /* # 1 4*0- *2) /Transactions beyond single ' -" - /# ) .$)"' ./ /a ( )/C SQL statement Default behavior:
_D # ' ..$ K2 ) /# ./ / ( )/. /* .0 *- ! $' • !*- ( $)/ $)$)" / $)/ "-$/4 Why bother to have more statements in a single transaction? `D -!*-( ) A . # /- ). /$*) $)1*'1 . $.& M ) A may depend on success of set of statements • Data integrity $.& M ) - /# - .'*2 Each SQL statement executes its own transaction
• •
•
Performance as each transaction involves disk I/O
# .$ - $+ !*- 4*0- *2) /- ). /$*). $.C
How to achieve this?
try { db.beginTransaction();
// several SQL statements in here db.setTransactionSuccessful();
} finally { db.endTransaction(); }
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)
return instance;
}
public int nextInt() { return generator.nextInt(); }
}
instance = new SingleRandom();
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
$"0- OTU= # *)./ )/. -*2. - (+' < *)./ )/ $ '*" Image: M.Murphy, Android 4.3 Fig 167
! /# 0. - ;''. $) *)./ )/ ) '$ &. /# K L 0//*)A 2 ) /* $). -/ - *- $) /# / . D # / $. # ) ' 1$ ) InsertTaskC
in a separate thread or AsynchTask
private class InsertTask extends AsyncTask<ContentValues, Void, Void> private Cursor constantsCursor=null null;
@Override protected Void doInBackground(ContentValues... values) { db.getWritableDatabase().insert(DatabaseHelper.TABLE, DatabaseHelper.TITLE, values[0]); constantsCursor=doQuery(); constantsCursor.getCount(); }
return return(null null);
$"0- OTU= # *)./ )/. -*2. - (+' < *)./ )/ $ '*"
! /# 0. - ;''. $) *)./ )/ ) '$ &. /# K L 0//*)A 2 ) /* $). -/ ) 2 - *- $) /# / . D # / $. # ) ' 1$ ) InsertTaskC
Asynchronous DB Update
private class InsertTask extends AsyncTask<ContentValues, Void, Void> { private Cursor constantsCursor=null null; @Override protected Void doInBackground(ContentValues... values) { db.getWritableDatabase().insert(DatabaseHelper.TABLE, DatabaseHelper.TITLE, values[0]); constantsCursor=doQuery(); constantsCursor.getCount(); }
}
â&#x20AC;˘
return return(null null);
@Override public void onPostExecute(Void arg0) { ((CursorAdapter)getListAdapter()).changeCursor(constantsCursor); }
constantsCursor shared to+,deliver query results to UI 7KLV GRFXPHQW LV OLFHQVHG IRU 3HWHU .HPSHU V H[FOXVLYH XVH E\ &RPPRQV:DUH //&
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?