android sqlite essentials - ey.md related/pdfs and... · simran bhogal joanna mcmahon indexers...
TRANSCRIPT
![Page 1: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/1.jpg)
![Page 2: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/2.jpg)
![Page 3: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/3.jpg)
AndroidSQLiteEssentials
![Page 4: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/4.jpg)
TableofContents
AndroidSQLiteEssentials
Credits
AbouttheAuthors
AbouttheReviewers
www.PacktPub.com
Supportfiles,eBooks,discountoffersandmore
WhySubscribe?
FreeAccessforPacktaccountholders
Preface
Whatthisbookcovers
Whatyouneedforthisbook
Whothisbookisfor
Conventions
Readerfeedback
Customersupport
Downloadingtheexamplecode
Errata
Piracy
Questions
1.EnterSQLite
WhySQLite?
TheSQLitearchitecture
TheSQLiteinterface
TheSQLcompiler
Thevirtualmachine
TheSQLitebackend
Aquickreviewofdatabasefundamentals
WhatisanSQLitestatement?
TheSQLitesyntax
![Page 5: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/5.jpg)
DatatypesinSQLite
Storageclasses
TheBooleandatatype
TheDateandTimedatatype
SQLiteinAndroid
SQLiteversion
Databasepackages
APIs
TheSQLiteOpenHelperclass
TheSQLiteDatabaseclass
ContentValues
Cursor
Summary
2.ConnectingtheDots
Buildingblocks
Adatabasehandlerandqueries
BuildingtheCreatequery
BuildingtheInsertquery
BuildingtheDeletequery
BuildingtheUpdatequery
ConnectingtheUIanddatabase
Summary
3.SharingisCaring
Whatisacontentprovider?
Usingexistingcontentproviders
Whatisacontentresolver?
Creatingacontentprovider
UnderstandingcontentURIs
Declaringourcontractclass
CreatingUriMatcherdefinitions
Implementingthecoremethods
![Page 6: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/6.jpg)
InitializingtheproviderthroughtheonCreate()method
Queryingrecordsthroughthequery()method
Addingrecordsthroughtheinsert()method
Updatingrecordsthroughtheupdate()method
Deletingrecordsthroughthedelete()method
GettingthereturntypeofdatathroughthegetType()method
Addingaprovidertoamanifest
Usingacontentprovider
Summary
4.ThreadCarefully
LoadingdatawithCursorLoader
Loaders
LoaderAPI’ssummary
UsingCursorLoader
Datasecurity
ContentProviderandpermissions
Encryptingcriticaldata
Generaltipsandlibraries
Upgradingadatabase
DatabaseminusSQLstatements
Shippingwithaprepopulateddatabase
Summary
Index
![Page 7: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/7.jpg)
![Page 8: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/8.jpg)
AndroidSQLiteEssentials
![Page 9: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/9.jpg)
![Page 10: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/10.jpg)
AndroidSQLiteEssentialsCopyright©2014PacktPublishing
Allrightsreserved.Nopartofthisbookmaybereproduced,storedinaretrievalsystem,ortransmittedinanyformorbyanymeans,withoutthepriorwrittenpermissionofthepublisher,exceptinthecaseofbriefquotationsembeddedincriticalarticlesorreviews.
Everyefforthasbeenmadeinthepreparationofthisbooktoensuretheaccuracyoftheinformationpresented.However,theinformationcontainedinthisbookissoldwithoutwarranty,eitherexpressorimplied.Neithertheauthors,norPacktPublishing,anditsdealersanddistributorswillbeheldliableforanydamagescausedorallegedtobecauseddirectlyorindirectlybythisbook.
PacktPublishinghasendeavoredtoprovidetrademarkinformationaboutallofthecompaniesandproductsmentionedinthisbookbytheappropriateuseofcapitals.However,PacktPublishingcannotguaranteetheaccuracyofthisinformation.
Firstpublished:August2014
Productionreference:1200814
PublishedbyPacktPublishingLtd.
LiveryPlace
35LiveryStreet
BirminghamB32PB,UK.
ISBN978-1-78328-295-1
www.packtpub.com
CoverimagebyPratyushMohanta(<[email protected]>)
![Page 11: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/11.jpg)
![Page 12: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/12.jpg)
CreditsAuthors
SunnyKumarAditya
VikashKumarKarn
Reviewers
AmeyHaldankar
GauravMaru
CommissioningEditor
PramilaBalan
AcquisitionEditor
NikhilKarkal
ContentDevelopmentEditor
RuchitaBhansali
TechnicalEditors
DennisJohn
GauravThingalaya
CopyEditors
RoshniBanerjee
GladsonMonteiro
AdithiShetty
ProjectCoordinator
KrantiBerde
Proofreaders
SimranBhogal
JoannaMcMahon
Indexers
MariammalChettiyar
RekhaNair
Graphics
RonakDhruv
ProductionCoordinator
![Page 13: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/13.jpg)
SaiprasadKadam
CoverWork
SaiprasadKadam
![Page 14: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/14.jpg)
![Page 15: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/15.jpg)
AbouttheAuthorsSunnyKumarAdityahasbeenworkingontheAndroidplatformforthepast4years.HistrystwithAndroidbeganwithhiscollegeproject,andhecontinuedwithhisworkinR&DatHCLInfosystemsLtd.SunnylovestostayuptodatewiththelatesttrendsandpracticesinAndroiddevelopment.ApartfrombuildingAndroidapplications,hewritesatwww.deadmango.com.HeiscurrentlytheheadofAndroiddevelopmentatYamunix.
IwouldliketothankPacktPublishingforthisopportunityandmyfamilyaswellasfriendsfortheirsupport.
VikashKumarKarnisanIIITAllahabadalumnusandanECEstudentwhoseloveforcodedrovehimtowardsthesoftwaredevelopmentfield.HehasworkedwithleadingmultinationalsandiscurrentlyworkingatSamsungResearchInstitute,Bangalore,exploringAndroid.
VikashlikestolearntheintricaciesoftheAndroidframeworkandhelpnewcomersinthisfield.Someofhisapplications,suchasMovtanFishingandComparePictures,canbefoundonthePlayStore.
Iwouldliketothankmyfriendsandfamilyfortheirsupportduringthecourseofwritingthisbook.
![Page 16: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/16.jpg)
![Page 17: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/17.jpg)
AbouttheReviewersAmeyHaldankarisanAndroidenthusiasthookedontheplatformsinceitsearlydays.EquippedwithadegreeinComputerScienceEngineeringfromGIT,Belgaum,heisworkingforHCLInfosystemsLtd.asaSeniorSoftwareEngineer.
Ameyhasbeenworkingontheplatformforthepast3yearsdevelopingseveralapplicationsformajorclientssuchasDomino’s,Galatsaray,HCL,andNokia.
AnoteofthankstothepublishinghouseforconsideringmefortheroleofareviewerforAndroidSQLiteEssentials.
GauravMaruhasaBachelor’sdegreeinComputersfromShah&AnchorKutchhiEngineeringCollege.Since2011,hehasbeenworkingasanAndroidapplicationdeveloperatvariousorganizations,includingIndia’slargestretailsectorcompany.Gauravhasdevelopedvariousapps,includingtheonedevelopedfortheUSA’slargestbookseller(aFortune500company).Hedrinks,eats,andsleepsAndroid.Youcancontacthimat<[email protected]>.
Iwouldliketothankmyfamily,friends,colleagues,andPacktPublishing,whohelpedmepullthisoneoffsuccessfully.Cheers!
![Page 18: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/18.jpg)
![Page 19: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/19.jpg)
www.PacktPub.com
![Page 20: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/20.jpg)
Supportfiles,eBooks,discountoffersandmoreYoumightwanttovisitwww.PacktPub.comforsupportfilesanddownloadsrelatedtoyourbook.
DidyouknowthatPacktofferseBookversionsofeverybookpublished,withPDFandePubfilesavailable?YoucanupgradetotheeBookversionatwww.PacktPub.comandasaprintbookcustomer,youareentitledtoadiscountontheeBookcopy.Getintouchwithusat<[email protected]>formoredetails.
Atwww.PacktPub.com,youcanalsoreadacollectionoffreetechnicalarticles,signupforarangeoffreenewslettersandreceiveexclusivediscountsandoffersonPacktbooksandeBooks.
http://PacktLib.PacktPub.com
DoyouneedinstantsolutionstoyourITquestions?PacktLibisPackt’sonlinedigitalbooklibrary.Here,youcanaccess,readandsearchacrossPackt’sentirelibraryofbooks.
![Page 21: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/21.jpg)
WhySubscribe?FullysearchableacrosseverybookpublishedbyPacktCopyandpaste,printandbookmarkcontentOndemandandaccessibleviawebbrowser
![Page 22: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/22.jpg)
FreeAccessforPacktaccountholdersIfyouhaveanaccountwithPacktatwww.PacktPub.com,youcanusethistoaccessPacktLibtodayandviewnineentirelyfreebooks.Simplyuseyourlogincredentialsforimmediateaccess.
![Page 23: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/23.jpg)
![Page 24: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/24.jpg)
PrefaceAndroidisprobablythebuzzwordofthisdecade.Inashortspan,ithastakenoverthemajorityofthehandsetmarket.Androidisstagedtotakeoverwearables,ourTVrooms,aswellasourcarsthisautumnwiththeAndroidLrelease.WiththefranticpaceatwhichAndroidisgrowing,adeveloperneedstouphisorherskillsetsaswell.Database-orientedapplicationdevelopmentisoneofthekeyskillseverydevelopershouldhave.SQLitedatabaseinapplicationsistheheartofadata-centricproductandkeytobuildinggreatproducts.UnderstandingSQLiteandimplementingtheAndroiddatabasecanbeasteeplearningcurveforsomepeople.Conceptssuchascontentprovidersandloadersaremorecomplextounderstandandimplement.AndroidSQLiteEssentialsequipsdeveloperswithtoolstobuilddatabase-basedAndroidapplicationsinasimplisticmanner.Itiswrittenkeepinginmindthecurrentneedsandbestpracticesbeingfollowedintheindustry.Letusstartourjourney.
![Page 25: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/25.jpg)
WhatthisbookcoversChapter1,EnterSQLite,providesaninsightintoSQLitearchitecture,SQLitebasics,anditsAndroidconnection.
Chapter2,ConnectingtheDots,covershowtoconnectyourdatabasetoAndroidviews.Italsocoverssomeofthebestpracticesoneshouldfollowinordertobuildadatabase-centric/database-enabledapplication.
Chapter3,SharingisCaring,willreflectonhowtoaccessandsharedatainAndroidviacontentprovidersandhowtoconstructacontentprovider.
Chapter4,ThreadCarefully,willguideyouonhowtouseloadersandensuresecurityofdatabaseanddata.ItwillalsoprovideyouwithtipstoexplorealternateapproachestobuildingandusingdatabasesinAndroidapplications.
![Page 26: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/26.jpg)
![Page 27: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/27.jpg)
WhatyouneedforthisbookToefficientlyusethisbook,youwillrequireaworkingsystemwithWindows,Ubuntu,orMacOSpreinstalled.DownloadandsetuptheJavaenvironment;werequirethisfortheIDEofourchoice,Eclipse,torun.DownloadAndroidSDKfromtheAndroiddeveloper’ssiteandAndroidADTpluginforEclipse.Alternatively,youcandownloadtheEclipseADTbundlethatcontainsEclipseSDKandtheADTplugin.YoucanalsotryAndroidStudio;thisIDE,whichjustmovedtobeta,isalsoavailableonthedevelopersite.Makesureyouroperatingsystem,JDK,andIDEareallofeither32bitor64bit.
![Page 28: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/28.jpg)
![Page 29: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/29.jpg)
WhothisbookisforAndroidSQLiteEssentialsisaguidebookforAndroidprogrammerswhowanttoexploreSQLitedatabase-basedAndroidapplications.Thereaderisexpectedtohavealittlebitofhands-onexperienceofAndroidfundamentalbuildingblocksandtheknow-howofIDEandAndroidtools.
![Page 30: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/30.jpg)
![Page 31: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/31.jpg)
ConventionsInthisbook,youwillfindanumberofstylesoftextthatdistinguishbetweendifferentkindsofinformation.Herearesomeexamplesofthesestylesandanexplanationoftheirmeaning.
Codewordsintext,databasetablenames,foldernames,filenames,fileextensions,pathnames,dummyURLs,userinput,andTwitterhandlesareshownasfollows:“ToclosetheCursorobject,theclose()methodcallwillbeused.”
Ablockofcodeissetasfollows:
ContentValuescv=newContentValues();
cv.put(COL_NAME,"johndoe");
cv.put(COL_NUMBER,"12345000");
dataBase.insert(TABLE_CONTACTS,null,cv);
Anycommand-lineinputoroutputiswrittenasfollows:
adbshellSQLite3--version
SQLite3.7.11:API16-19
SQLite3.7.4:API11-15
SQLite3.6.22:API8-10
SQLite3.5.9:API3-7
Newtermsandimportantwordsareshowninbold.Wordsthatyouseeonthescreen,inmenusordialogboxesforexample,appearinthetextlikethis:“GotoAndroidVirtualDeviceManagerfromtheWindowsmenutostarttheemulator.”
NoteWarningsorimportantnotesappearinaboxlikethis.
TipTipsandtricksappearlikethis.
![Page 32: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/32.jpg)
![Page 33: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/33.jpg)
ReaderfeedbackFeedbackfromourreadersisalwayswelcome.Letusknowwhatyouthinkaboutthisbook—whatyoulikedormayhavedisliked.Readerfeedbackisimportantforustodeveloptitlesthatyoureallygetthemostoutof.
Tosendusgeneralfeedback,simplysendane-mailto<[email protected]>,andmentionthebooktitleviathesubjectofyourmessage.
Ifthereisatopicthatyouhaveexpertiseinandyouareinterestedineitherwritingorcontributingtoabook,seeourauthorguideonwww.packtpub.com/authors.
![Page 34: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/34.jpg)
![Page 35: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/35.jpg)
CustomersupportNowthatyouaretheproudownerofaPacktbook,wehaveanumberofthingstohelpyoutogetthemostfromyourpurchase.
![Page 36: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/36.jpg)
DownloadingtheexamplecodeYoucandownloadtheexamplecodefilesforallPacktbooksyouhavepurchasedfromyouraccountathttp://www.packtpub.com.Ifyoupurchasedthisbookelsewhere,youcanvisithttp://www.packtpub.com/supportandregistertohavethefilese-maileddirectlytoyou.
![Page 37: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/37.jpg)
ErrataAlthoughwehavetakeneverycaretoensuretheaccuracyofourcontent,mistakesdohappen.Ifyoufindamistakeinoneofourbooks—maybeamistakeinthetextorthecode—wewouldbegratefulifyouwouldreportthistous.Bydoingso,youcansaveotherreadersfromfrustrationandhelpusimprovesubsequentversionsofthisbook.Ifyoufindanyerrata,pleasereportthembyvisitinghttp://www.packtpub.com/submit-errata,selectingyourbook,clickingontheerratasubmissionformlink,andenteringthedetailsofyourerrata.Onceyourerrataareverified,yoursubmissionwillbeacceptedandtheerratawillbeuploadedonourwebsite,oraddedtoanylistofexistingerrata,undertheErratasectionofthattitle.Anyexistingerratacanbeviewedbyselectingyourtitlefromhttp://www.packtpub.com/support.
![Page 38: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/38.jpg)
PiracyPiracyofcopyrightmaterialontheInternetisanongoingproblemacrossallmedia.AtPackt,wetaketheprotectionofourcopyrightandlicensesveryseriously.Ifyoucomeacrossanyillegalcopiesofourworks,inanyform,ontheInternet,pleaseprovideuswiththelocationaddressorwebsitenameimmediatelysothatwecanpursuearemedy.
Pleasecontactusat<[email protected]>withalinktothesuspectedpiratedmaterial.
Weappreciateyourhelpinprotectingourauthors,andourabilitytobringyouvaluablecontent.
![Page 39: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/39.jpg)
QuestionsYoucancontactusat<[email protected]>ifyouarehavingaproblemwithanyaspectofthebook,andwewilldoourbesttoaddressit.
![Page 40: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/40.jpg)
![Page 41: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/41.jpg)
Chapter1.EnterSQLiteDr.RichardHipp,thearchitectandprimaryauthorofSQLite,explainshowitallbeganinhisinterviewwithTheGuardianpublishedinJune2007:
“IstartedonMay292000.It’sjustoversevenyearsold,”hesays.Hewasworkingonaprojectwhichusedadatabaseserver,butfromtimetotimethedatabasewentoffline.“Thenmyprogramwouldgiveanerrormessagesayingthatthedatabaseisn’tworking,andIgottheblameforthis.SoIsaid,thisisnotademandingapplicationforthedatabase,whydon’tIjusttalkdirectlytothedisk,andbuildanSQLdatabaseenginethatway?Thatwashowitstarted.”
BeforewebeginourjourneyexploringSQLiteinthecontextofAndroid,wewouldliketoinformyouofsomeprerequisites.Thefollowingareverybasicrequirementsandwillrequirelittleeffortfromyou:
YouneedtoensurethattheenvironmentforbuildingAndroidapplicationsisinplace.Whenwesay“environment,”werefertothecombinationofJDKandEclipse,ourIDEchoice,ADTplugins,andAndroidSDKtools.Incasethesearenotinplace,theADTbundle,whichconsistsofIDE,ADTplugins,AndroidSDKtools,andplatformtools,canbedownloadedfromhttp://developer.android.com/sdk/index.html.Thestepsmentionedinthelinkareprettyself-explanatory.ForJDK,youcanvisitOracle’swebsitetodownloadthelatestversionandsetitupathttp://www.oracle.com/technetwork/java/javase/downloads/index.html.YouneedtohaveabasicknowledgeofAndroidcomponentsandhaverunmorethan“HelloWorld”programsonanAndroidemulator.Ifnot,averyaptguideispresentontheAndroiddevelopersitetosetupanemulator.WewouldsuggestyoubecomefamiliarwithbasicAndroidcomponents:Intent,Service,ContentProviders,andBroadcastReceiver.TheAndroiddevelopersitehasgoodrepositoriesofsamplesalongwithdocumentation.Someoftheseareasfollows:
Emulator:http://developer.android.com/tools/devices/index.htmlAndroidbasics:http://developer.android.com/training/basics/firstapp/index.html
Withthesethingsinplace,wecannowstartourforayintoSQLite.
Inthischapter,wewillcoverthefollowing:
WhySQLite?TheSQLitearchitectureAquickreviewofdatabasefundamentalsSQLiteinAndroid
![Page 42: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/42.jpg)
WhySQLite?SQLiteisanembeddedSQLdatabaseengine.ItisusedbyprominentnamessuchasAdobeinAdobeIntegratedRuntime(AIR);Airbus,intheirflightsoftware;PythonshipswithSQLite;PHP;andmanymore.Inthemobiledomain,SQLiteisaverypopularchoiceacrossvariousplatformsbecauseofitslightweightnature.AppleusesitintheiPhoneandGoogleintheAndroidoperatingsystem.
Itisusedasanapplicationfileformat,adatabaseforelectronicgadgets,adatabaseforwebsites,andasanenterpriseRDBMS.WhatmakesSQLitesuchaninterestingchoicefortheseandmanyothercompanies?Let’stakeacloserlookatthefeaturesofSQLitethatmakeitsopopular:
Zero-configuration:SQLiteisdesignedinsuchamannerthatitrequiresnoconfigurationfile.Itrequiresnoinstallationstepsorinitialsetup;ithasnoserverprocessrunningandnorecoverystepstotakeevenifitcrashes.Thereisnoserveranditisdirectlyembeddedinourapplication.Furthermore,noadministratorisrequiredtocreateormaintainaDBinstance,orsetpermissionsforusers.Inshort,thisisatrueDBA-lessdatabase.No-copyright:SQLite,insteadofalicense,comeswithablessing.ThesourcecodeofSQLiteisinthepublicdomain;youarefreetomodify,distribute,andevensellthecode.Eventhecontributorsareaskedtosignanaffidavittoprotectfromanycopyrightswarfarethatmayoccurinfuture.Cross-platform:Databasefilesfromonesystemcanbemovedtoasystemrunningadifferentarchitecturewithoutanyhassle.Thisispossiblebecausethedatabasefileformatisbinaryandallthemachinesusethesameformat.Inthefollowingchapters,wewillbepullingoutadatabasefromanAndroidemulatortoWindows.Compact:AnSQLitedatabaseisasingleordinarydiskfile;itcomeswithoutaserverandisdesignedtobelightweightandsimple.Theseattributesleadtoaverylightweightdatabaseengine.SQLiteVersion3.7.8hasafootprintoflessthan350KiB(kibibyte)comparedtoitsotherSQLdatabaseengines,whicharemuchlarger.Foolproof:Thecodebaseiswellcommented,easytounderstand,andmodular.ThetestcasesandtestscriptsinSQLitehaveapproximately1084timesmorecodethanthesourcecodeofSQLitelibraryandtheyclaim100percentbranchtestcoverage.ThisleveloftestingreaffirmsthefaithinstilledinSQLitebydevelopers.
NoteInterestedreaderscanreadmoreaboutbranchtestcoveragefromWikipediaathttp://en.wikipedia.org/wiki/Code_coverage.
![Page 43: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/43.jpg)
![Page 44: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/44.jpg)
TheSQLitearchitectureThecore,SQLcompiler,backend,anddatabaseformtheSQLitearchitecture:
![Page 45: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/45.jpg)
TheSQLiteinterfaceAtthetopoftheSQLitelibrarystack,accordingtodocumentation,muchofthepublicinterfacetotheSQLitelibraryisimplementedbythewen.c,legacy.c,andvdbeapi.csourcefiles.Thisisthepointofcommunicationforotherprogramsandscripts.
![Page 46: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/46.jpg)
TheSQLcompilerTokenizerbreakstheSQLstringpassedfromtheinterfaceintotokensandhandsthetokensovertotheparser,onebyone.Tokenizerishand-codedinC.TheparserforSQLiteisgeneratedbytheLemonparsergenerator.ItisfasterthanYACCandBisonand,atthesametime,isthreadsafeandpreventsmemoryleaks.Theparserbuildsaparsetreefromthetokenspassedbythetokenizerandpassesthetreetothecodegenerator.Thegeneratorproducesvirtualmachinecodefromtheinputandpassesittothevirtualmachineasexecutables.MoreinformationabouttheLemonparsergeneratorcanbefoundathttp://en.wikipedia.org/wiki/Lemon_Parser_Generator.
![Page 47: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/47.jpg)
ThevirtualmachineThevirtualmachine,alsoknownasVirtualDatabaseEngine(VDBE),istheheartofSQLite.Itisresponsibleforfetchingandchangingvaluesinthedatabase.Itexecutestheprogramgeneratedbythecodegeneratortomanipulatedatabasefiles.EachSQLstatementisfirstconvertedintovirtualmachinelanguageforVDBE.EachinstructionofVDBEcontainsanopcodeanduptothreeadditionaloperands.
![Page 48: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/48.jpg)
TheSQLitebackendB-trees,alongwithPagerandtheOSInterface,formthebackendoftheSQLitearchitecture.B-treesareusedtoorganizethedata.ThepagerontheotherhandassistsB-treebycaching,modifying,androllingbackdata.B-tree,whenrequired,requestsparticularpagesfromthecache;thisrequestisprocessedbythepagerinanefficientandreliablemanner.TheOSInterface,asthenamesuggests,providesanabstractionlayertoporttodifferentoperatingsystems.IthidestheunnecessarydetailsofcommunicatingwithdifferentoperatingsystemsfromSQLitecallsandhandlesthemonbehalfofSQLite.
ThesearetheinternalsofSQLiteandanapplicationdeveloperinAndroidneednotworryabouttheinternalsofAndroidbecausetheSQLiteAndroidlibrarieshaveeffectivelyusedtheconceptofabstractionandallthecomplexitiesarehidden.OnejustneedstomastertheAPIsprovided,andthatwillcatertoallthepossibleusecasesofSQLiteinanAndroidapplication.
![Page 49: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/49.jpg)
![Page 50: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/50.jpg)
AquickreviewofdatabasefundamentalsAdatabase,insimplewords,isanorganizedwaytostoredatainacontinualfashion.Dataissavedintables.Atableconsistsofcolumnswithdifferentdatatypes.Everyrowinatablecorrespondstoadatarecord.YoumaythinkofatableasanExcelspreadsheet.Fromtheperspectiveofobject-orientedprogramming,everytableinadatabaseusuallydescribesanobject(representedbyaclass).Eachtablecolumnillustratesaclassattribute.Everyrecordinatablerepresentsaparticularinstanceofthatobject.
Let’slookataquickexample.Let’sassumeyouhaveaShopdatabasewithatablecalledInventory.Thistablemightbeusedtostoretheinformationaboutalltheproductsintheshops.TheInventorytablemightcontainthesecolumns:Productname(string),ProductId(number),Cost(number),Instock(0/1),andNumbersavailable(number).YoucouldthenaddarecordtothedatabaseforaproductnamedShoe:
ID Productname ProductId Cost Instock Numbersavailable
1 Carpet 340023 2310 1 4
2 Shoe 231257 235 1 2
Datainthedatabaseissupposedtobecheckedandinfluenced.Thedatawithinatablecanbeasfollows:
Added(withtheINSERTcommand)Modified(withtheUPDATEcommand)Removed(withtheDELETEcommand)
Youmaysearchforparticulardatawithinadatabasebyutilizingwhatisknownasaquery.Aquery(usingtheSELECTcommand)caninvolveonetable,oranumberoftables.Togenerateaquery,youmustdeterminethetables,datacolumns,andvaluesofthedataofinterestusingSQLcommands.EachSQLcommandisconcludedwithasemicolon(;).
![Page 51: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/51.jpg)
WhatisanSQLitestatement?AnSQLitestatementiswritteninSQL,whichisissuedtoadatabasetoretrievedataortocreate,insert,update,ordeletedatainthedatabase.
AllSQLitestatementsstartwithanyofthekeywords:SELECT,INSERT,UPDATE,DELETE,ALTER,DROP,andsoon,andallthestatementsendwithasemicolon(;).Forinstance:
CREATETABLEtable_name(column_nameINTEGER);
TheCREATETABLEcommandisusedtocreateanewtableinanSQLitedatabase.ACREATETABLEcommanddescribesthefollowingattributesofthenewtablethatisbeingcreated:
Thenameofthenewtable.Thedatabaseinwhichthenewtableiscreated.Tablesmaybegeneratedinthemaindatabase,thetempdatabase,orinanydatabaseattached.Thenameofeachcolumninthetable.Thedeclaredtypeofeachcolumninthetable.Adefaultvalueorexpressionforeachcolumninthetable.Adefaultrelationsequencetobeusedwitheachcolumn.Preferably,aPRIMARYKEYforthetable.Thiswillsupportbothsingle-columnandcomposite(multiple-column)primarykeys.AsetofSQLconstraintsforeachtable.ConstraintssuchasUNIQUE,NOTNULL,CHECK,andFOREIGNKEYaresupported.Insomecases,thetablewillbeaWITHOUTROWIDtable.
ThefollowingisasimpleSQLitestatementtocreateatable:
StringdatabaseTable="CREATETABLE"
+TABLE_CONTACTS+"("
+KEY_ID
+"INTEGERPRIMARYKEY,"
+KEY_NAME+"TEXT,"
+KEY_NUMBER+"INTEGER"
+")";
Here,CREATETABLEisthecommandtocreateatablewiththenameTABLE_CONTACTS.KEY_ID,KEY_NAMEandKEY_NUMBERarethecolumnIDs.SQLiterequiresauniqueIDtobeprovidedforeachcolumn.INTEGERandTEXTarethedatatypesassociatedwiththecorrespondingcolumns.SQLiterequiresthetypeofdatatobestoredinacolumntobedefinedatthetimeofcreationofthetable.PRIMARYKEYisthedatacolumnconstraint(rulesenforcedondatacolumnsinthetable).
SQLitesupportsmoreattributesthatcanbeusedforcreatingatable,forinstance,letuscreateacreatetablestatementthatinputsadefaultvalueforemptycolumns.NoticethatforKEY_NAME,weareprovidingadefaultvalueasxyzandfortheKEY_NUMBERcolumn,weareprovidingadefaultvalueof100:
StringdatabaseTable=
"CREATETABLE"
![Page 52: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/52.jpg)
+TABLE_CONTACTS+"("
+KEY_ID+"INTEGERPRIMARYKEY,"
+KEY_NAME+"TEXTDEFAULTxyz,"
+KEY_NUMBER+"INTEGERDEFAULT100"+")";
Here,whenarowisinsertedinthedatabase,thesecolumnswillbepreinitializedwiththedefaultvaluesasdefinedintheCREATESQLstatement.
Therearemorekeywords,butwedon’twantyoutogetboredwithahugelist.Wewillbecoveringotherkeywordsinthesubsequentchapters.
![Page 53: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/53.jpg)
TheSQLitesyntaxSQLitefollowsauniquesetofrulesandguidelinescalledsyntax.
AnimportantpointtobenotedisthatSQLiteiscase-insensitive,buttherearesomecommandsthatarecase-sensitive,forexample,GLOBandglobhavedifferentmeaninginSQLite.LetuslookattheSQLiteDELETEstatement’ssyntaxforinstance.Althoughwehaveusedcapitalletters,replacingthemwithlowercaseletterswillalsoworkfine:
DELETEFROMtableWHERE{condition};
![Page 54: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/54.jpg)
DatatypesinSQLiteSQLiteusesadynamicandweaklytypedSQLsyntax,whereasmostoftheSQLdatabasesusestatic,rigidtyping.Ifwelookatotherlanguages,JavaisastaticallytypedlanguageandPythonisadynamicallytypedlanguage.Sowhatdowemeanwhenwesaydynamicorstatic?Letuslookatanexample:
a=5
a="android"
Instaticallytypedlanguages,thiswillthrowanexception,whereasinadynamicallytypedlanguageitwillwork.InSQLite,thedatatypeofavalueisnotassociatedwithitscontainer,butwiththevalueitself.Thisisnotacauseofconcernwhendealingwithstaticallytypedsystems,whereavalueisdeterminedbyacontainer.ThisisbecauseSQLiteisbackwardscompatiblewiththemorecommonstatictypesystems.Hence,theSQLstatementsthatweuseforstaticsystemscanbeusedseamlesslyhere.
StorageclassesInSQLite,wehavestorageclassesthataremoregeneralthandatatypes.Internally,SQLitestoresdatainfivestorageclassesthatcanalsobereferredtoasprimitivedatatypes:
NULL:Thisrepresentsamissingvaluefromthedatabase.INTEGER:Thissupportsarangeofsignedintegersfrom1,2,3,4,6,or8bytesdependingonthemagnitudeofthevalue.SQLitehandlesthisautomaticallybasedonthevalue.Atthetimeofprocessinginthememory,theyareconvertedtothemostgeneral8-bytesignedintegerform.REAL:Thisisafloatingpointvalue,andSQLiteusesthisasan8-byteIEEEfloatingpointnumbertostoresuchvalues.TEXT:SQLitesupportsvariouscharacterencodings,suchasUTF-8,UTF-16BE,orUTF-16LE.Thisvalueisatextstring.BLOB:Thistypestoresalargearrayofbinarydata,exactlyhowitwasprovidedasinput.
SQLiteitselfdoesnotvalidateifthetypeswrittentothecolumnsareactuallyofthedefinedtype,forexample,youcanwriteanintegerintoastringcolumnandviceversa.Wecanevenhaveasinglecolumnwithdifferentstorageclasses:
idcol_t
------------
123
2NULL
3test
TheBooleandatatypeSQLitedoesnothaveaseparatestorageclassforBooleanandusestheIntegerclassforthispurpose.Integer0representsthefalsestatewhereas1representsatruestate.ThismeansthatthereisanindirectsupportforBooleanandwecancreateBooleantype
![Page 55: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/55.jpg)
columnsonly.Thecatchis,itwon’tcontainthefamiliarTRUE/FALSEvalues.
TheDateandTimedatatypeAswesawfortheBooleandatatype,thereisnostorageclassfortheDateandTimedatatypesinSQLite.SQLitehasfivebuilt-indateandtimefunctionstohelpuswithit;wecanusedateandtimeasinteger,text,orrealvalues.Moreover,thevaluesareinterchangeable,dependingontheneedoftheapplication.Forexample,tocomputethecurrentdate,usethefollowingcode:
SELECTdate('now');
![Page 56: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/56.jpg)
![Page 57: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/57.jpg)
SQLiteinAndroidTheAndroidsoftwarestackconsistsofcoreLinuxkernel,Androidruntime,AndroidlibrariesthatsupporttheAndroidframework,andfinallyAndroidapplicationsthatrunontopofeverything.TheAndroidruntimeusesDalvikvirtualmachine(DVM)toexecutethedexcode.InnewerversionsofAndroid,thatis,fromKitKat(4.4),AndroidhasenabledanexperimentalfeatureknownasART,whichwilleventuallyreplaceDVM.ItisbasedonAheadofTime(AOT),whereasDVMisbasedonJustinTime(JIT).Inthefollowingdiagram,wecanseethatSQLiteprovidesnativedatabasesupportandispartofthelibrariesthatsupporttheapplicationframeworkalongwithlibrariessuchasSSL,OpenGLES,WebKit,andsoon.Theselibraries,writteninC/C++,runovertheLinuxkerneland,alongwiththeAndroidruntime,formsthebackboneoftheapplicationframework,asshowninthefollowingdiagram:
BeforewestartexploringSQLiteinAndroid,let’stakealookattheotherpersistentstoragealternativesinAndroid:
Sharedpreference:Dataisstoredinasharedpreferenceinthekey-valueform.ThefileitselfisanXMLfilecontainingthekey-valuepairs.Thefileispresentinthe
![Page 58: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/58.jpg)
internalstorageofanapplication,andaccesstoitcanbepublicorprivateasneeded.AndroidprovidesAPIstowriteandreadsharedpreferences.Itisadvisedtousethisincasewehavetosaveasmallcollectionofsuchdata.AgeneralexamplewouldbesavingthelastreadpositioninaPDF,orsavingauser’spreferencetoshowaratingbox.Internal/externalstorage:Thisterminologycanbealittlemisleading;Androiddefinestwostoragespacestosavefiles.Onsomedevices,youmighthaveanexternalstoragedeviceinformofanSDcard,whereasonothers,youwillfindthatthesystemhaspartitioneditsmemoryintotwoparts,tobelabeledasinternalandexternal.PathstotheexternalaswellasinternalstoragecanbefetchedbyusingAndroidAPIs.Internalstorage,bydefault,islimitedandaccessibleonlytotheapplication,whereastheexternalstoragemayormaynotbeavailableincaseitismounted.
Tipandroid:installLocationcanbeusedinthemanifesttospecifytheinternal/externalinstallationlocationofanapplication.
![Page 59: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/59.jpg)
SQLiteversionSinceAPIlevel1,AndroidshipswithSQLite.Atthetimeofwritingthisbook,thecurrentversionofSQLitewas3.8.4.1.Accordingtothedocumentation,theversionofSQLiteis3.4.0,butdifferentAndroidversionsareknowntoshipwithdifferentversionsofSQLite.WecaneasilyverifythisviatheuseofatoolcalledSQLite3presentintheplatform-toolsfolderinsidetheAndroidSDKinstallationfolderandAndroidEmulator:
adbshellSQLite3--version
SQLite3.7.11:API16-19
SQLite3.7.4:API11-15
SQLite3.6.22:API8-10
SQLite3.5.9:API3-7
WeneednotworryaboutthedifferentversionsofSQLiteandshouldstickto3.5.9forcompatibility,orwecangobythesayingthatAPI14isthenewminSdkVersionandswitchitwith3.7.4.Untilandunlessyouhavesomethingveryspecifictoaparticularversion,itwillhardlymatter.
NoteSomeadditionalhandySQLite3commandsareasfollows:
.dump:Toprintoutthecontentsofatable
.schema:ToprinttheSQLCREATEstatementforanexistingtable
.help:Forinstructions
![Page 60: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/60.jpg)
DatabasepackagesTheandroid.databasepackagecontainsallthenecessaryclassesforworkingwithdatabases.Theandroid.database.SQLitepackagecontainstheSQLite-specificclasses.
APIsAndroidprovidesvariousAPIstoenableustocreate,access,modify,anddeleteadatabase.Thecompletelistcanbequiteoverwhelming;forthesakeofbrevity,wewillcoverthemostimportantandusedones.
TheSQLiteOpenHelperclassTheSQLiteOpenHelperclassisthefirstandmostessentialclassofAndroidtoworkwithSQLitedatabases;itispresentintheandroid.database.SQLitenamespace.SQLiteOpenHelperisahelperclassthatisdesignedforextensionandtoimplementthetasksandactionsyoudeemimportantwhencreating,opening,andusingadatabase.ThishelperclassisprovidedbytheAndroidframeworktoworkwiththeSQLitedatabaseandhelpsinmanagingthedatabasecreationandversionmanagement.Themodusoperandiwouldbetoextendtheclassandimplementtasksandactionsasrequiredbyourapplication.SQLiteOpenHelperhasconstructorsdefinedasfollows:
SQLiteOpenHelper(Contextcontext,Stringname,SQLiteDatabase.CursorFactory
factory,intversion)
SQLiteOpenHelper(Contextcontext,Stringname,SQLiteDatabase.CursorFactory
factory,intversion,DatabaseErrorHandlererrorHandler)
Theapplicationcontextpermitsaccesstoallthesharedresourcesandassetsfortheapplication.ThenameparameterconsistsofthedatabasefilenameintheAndroidstorage.SQLiteDatabase.CursorFactoryisafactoryclassthatcreatescursorobjectsthatactastheoutputsetforallthequeriesyouapplyagainstSQLiteunderAndroid.Theapplication-specificversionnumberforthedatabasewillbetheversionparameter(ormoreparticularly,itsschema).
TheconstructorofSQLiteOpenHelperisusedtocreateahelperobjecttocreate,open,ormanageadatabase.Thecontextistheapplicationcontextthatallowsaccesstoallthesharedresourcesandassets.Thenameparametereithercontainsthenameofadatabaseornullforanin-memorydatabase.TheSQLiteDatabase.CursorFactoryfactorycreatesacursorobjectthatactsastheresultsetforallthequeries.Theversionparameterdefinestheversionnumberofthedatabaseandisusedtoupgrade/downgradethedatabase.TheerrorHandlerparameterinthesecondconstructorisusedwhenSQLitereportsdatabasecorruption.
SQLiteOpenHelperwilltriggeritsonUpgrade()methodifourdatabaseversionnumberisnotatdefault1.ImportantmethodsoftheSQLiteOpenHelperclassareasfollows:
synchronizedvoidclose()
synchronizedSQLiteDatabasegetReadableDatabase()
synchronizedSQLiteDatabasegetWritableDatabase()
![Page 61: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/61.jpg)
abstractvoidonCreate(SQLiteDatabasedb)
voidonOpen(SQLiteDatabasedb)
abstractvoidonUpgrade(SQLiteDatabasedb,intoldVersion,int
newVersion)
Thesynchronizedclose()methodclosesanyopendatabaseobject.Thesynchronizedkeywordpreventsthreadandmemoryconsistencyerrors.
Thenexttwomethods,getReadableDatabase()andgetWriteableDatabase(),arethemethodsinwhichthedatabaseisactuallycreatedoropened.BothreturnthesameSQLiteDatabaseobject;thedifferenceliesinthefactthatgetReadableDatabase()willreturnareadabledatabaseincaseitcannotreturnawritabledatabase,whereasgetWriteableDatabase()returnsawritabledatabaseobject.ThegetWriteableDatabase()methodwillthrowanSQLiteExceptionifadatabasecannotbeopenedforwriting.IncaseofgetReadableDatabase(),ifadatabasecannotbeopened,itwillthrowthesameexception.
WecanusetheisReadOnly()methodoftheSQLiteDatabaseclassonthedatabaseobjecttoknowthestateofthedatabase.Itreturnstrueforread-onlydatabases.
CallingeithermethodswillinvoketheonCreate()methodifthedatabasedoesn’texistyet.Otherwise,itwillinvoketheonOpen()oronUpgrade()methods,dependingontheversionnumber.TheonOpen()methodshouldchecktheisReadOnly()methodbeforeupdatingthedatabase.Onceopened,thedatabaseiscachedtoimproveperformance.Finally,weneedtocalltheclose()methodtoclosethedatabaseobject.
TheonCreate(),onOpen(),andonUpgrade()methodsaredesignedforthesubclasstoimplementtheintendedbehavior.TheonCreate()methodiscalledwhenthedatabaseiscreatedforthefirsttime.ThisistheplacewherewecreateourtablesbyusingSQLitestatements,whichwesawearlierintheexample.TheonOpen()methodistriggeredwhenthedatabasehasbeenconfiguredandafterthedatabaseschemahasbeencreated,upgraded,ordowngradedasnecessary.Read/writestatusshouldbecheckedherewiththehelpoftheisReadOnly()method.
TheonUpgrade()methodiscalledwhenthedatabaseneedstobeupgradeddependingontheversionnumbersuppliedtoit.Bydefault,thedatabaseversionis1,andasweincrementthedatabaseversionnumbersandreleasenewversions,theupgradewillbeperformed.
AsimpleexampleillustratingtheuseoftheSQLiteOpenHelperclassispresentinthecodebundleforthischapter;wewouldbeusingitforexplanation:
classSQLiteHelperClass
{
...
...
publicstaticfinalintVERSION_NUMBER=1;
sqlHelper=
newSQLiteOpenHelper(context,"ContactDatabase",null,
![Page 62: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/62.jpg)
VERSION_NUMBER)
{
@Override
publicvoidonUpgrade(SQLiteDatabasedb,
intoldVersion,intnewVersion)
{
//droptableonupgrade
db.execSQL("DROPTABLEIFEXISTS"
+TABLE_CONTACTS);
//Createtablesagain
onCreate(db);
}
@Override
publicvoidonCreate(SQLiteDatabasedb)
{
//creatingtableduringonCreate
StringcreateContactsTable=
"CREATETABLE"
+TABLE_CONTACTS+"("
+KEY_ID+"INTEGERPRIMARYKEY,"
+KEY_NAME+"TEXT,"
+KEY_NUMBER+"INTEGER"+")";
try{
db.execSQL(createContactsTable);
}catch(SQLExceptione){
e.printStackTrace();
}
}
@Override
publicsynchronizedvoidclose()
{
super.close();
Log.d("TAG","Databaseclosed");
}
@Override
publicvoidonOpen(SQLiteDatabasedb)
{
super.onOpen(db);
Log.d("TAG","Databaseopened");
}
};
...
...
//openthedatabaseinread-onlymode
SQLiteDatabasedb=SQLiteOpenHelper.getWritableDatabase();
![Page 63: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/63.jpg)
...
...
//openthedatabaseinread/writemode
SQLiteDatabasedb=SQLiteOpenHelper.getWritableDatabase();
TipDownloadingtheexamplecode
YoucandownloadtheexamplecodefilesforallPacktbooksyouhavepurchasedfromyouraccountathttp://www.packtpub.com.Ifyoupurchasedthisbookelsewhere,youcanvisithttp://www.packtpub.com/supportandregistertohavethefilese-maileddirectlytoyou.
TheSQLiteDatabaseclassNowthatyouarefamiliarwiththehelperclassthatkick-startstheuseofSQLitedatabaseswithinAndroid,it’stimetolookatthecoreSQLiteDatabaseclass.SQLiteDatabaseisthebaseclassrequiredtoworkwithanSQLitedatabaseinAndroidandprovidesmethodstoopen,query,update,andclosethedatabase.
Morethan50methodsareavailablefortheSQLiteDatabaseclass,eachwithitsownnuancesandusecases.Ratherthananexhaustivelist,we’llcoverthemostimportantsubsetsofmethodsandallowyoutoexploresomeoftheoverloadedmethodsatyourleisure.Atanytime,youcanrefertothefullonlineAndroiddocumentationfortheSQLiteDatabaseclassathttp://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html.
SomemethodsoftheSQLiteDatabaseclassareshowninthefollowinglist:
publiclonginsert(Stringtable,StringnullColumnHack,ContentValues
values)
publicCursorquery(Stringtable,String[]columns,Stringselection,
String[]selectionArgs,StringgroupBy,Stringhaving,StringorderBy)
publicCursorrawQuery(Stringsql,String[]selectionArgs)
publicintdelete(Stringtable,StringwhereClause,String[]
whereArgs)
publicintupdate(Stringtable,ContentValuesvalues,String
whereClause,String[]whereArgs)
LetusseetheseSQLiteDatabaseclassesinactionwithanexample.Wewillinsertanameandnumberinourtable.Thenwewillusetherawquerytofetchdatabackfromthetable.Afterthis,wewillgothroughthedelete()andupdate()methods,bothofwhichwilltakeidasaparametertoidentifywhichrowofdatainourdatabasetableweintendtodeleteorupdate:
publicvoidinsertToSimpleDataBase()
{
SQLiteDatabasedb=sqlHelper.getWritableDatabase();
ContentValuescv=newContentValues();
![Page 64: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/64.jpg)
cv.put(KEY_NAME,"John");
cv.put(KEY_NUMBER,"0000000000");
//Insertingvaluesindifferentcolumnsofthetableusing
//ContentValues
db.insert(TABLE_CONTACTS,null,cv);
cv=newContentValues();
cv.put(KEY_NAME,"Tom");
cv.put(KEY_NUMBER,"5555555");
//Insertingvaluesindifferentcolumnsofthetableusing
//ContentValues
db.insert(TABLE_CONTACTS,null,cv);
}
...
...
publicvoidgetDataFromDatabase()
{
intcount;
db=sqlHelper.getReadableDatabase();
//Useofnormalquerytofetchdata
Cursorcr=db.query(TABLE_CONTACTS,null,null,
null,null,null,null);
if(cr!=null){
count=cr.getCount();
Log.d("DATABASE","countis:"+count);
}
//Useofrawquerytofetchdata
cr=db.rawQuery("select*from"+TABLE_CONTACTS,null);
if(cr!=null){
count=cr.getCount();
Log.d("DATABASE","countis:"+count);
}
}
...
...
publicvoiddelete(Stringname)
{
StringwhereClause=KEY_NAME+"=?";
String[]whereArgs=newString[]{name};
db=sqlHelper.getWritableDatabase();
introwsDeleted=db.delete(TABLE_CONTACTS,whereClause,whereArgs);
}
...
...
publicvoidupdate(Stringname)
{
StringwhereClause=KEY_NAME+"=?";
String[]whereArgs=newString[]{name};
ContentValuescv=newContentValues();
cv.put(KEY_NAME,"Betty");
![Page 65: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/65.jpg)
cv.put(KEY_NUMBER,"999000");
db=sqlHelper.getWritableDatabase();
introwsUpdated=db.update(TABLE_CONTACTS,cv,whereClause,
whereArgs);
}
ContentValuesContentValuesisessentiallyasetofkey-valuepairs,wherethekeyrepresentsthecolumnforthetableandthevalueisthevaluetobeinsertedinthatcolumn.So,inthecaseofvalues.put("COL_1",1);,thecolumnisCOL_1andthevaluebeinginsertedforthatcolumnis1.
Thefollowingisanexample:
ContentValuescv=newContentValues();
cv.put(COL_NAME,"johndoe");
cv.put(COL_NUMBER,"12345000");
dataBase.insert(TABLE_CONTACTS,null,cv);
CursorAqueryrecoversaCursorobject.ACursorobjectdepictstheresultofaqueryandfundamentallypointstoonerowoftheresultofthequery.Withthismethod,Androidcanbuffertheresultsofthequeryinaproductivemanner;asitdoesn’tneedtoloadallofthedataintomemory.
Toobtaintheelementsoftheresultingquery,youcanusethegetCount()method.
Tonavigateamidindividualdatarows,youcanutilizethemoveToFirst()andmoveToNext()methods.TheisAfterLast()methodpermitsyoutoanalyzewhethertheendoftheoutputhasarrived.
TheCursorobjectprovidestypedget*()methods,forexample,thegetLong(columnIndex)andgetString(columnIndex)methodstogainentrytothecolumndatafortheongoingpositionoftheresult.columnIndexisthenumberofthecolumnyouwillbeaccessing.
TheCursorobjectalsoprovidesthegetColumnIndexOrThrow(String)methodthatpermitsyoutogetthecolumnindexforacolumnnameofthetable.
ToclosetheCursorobject,theclose()methodcallwillbeused.
Adatabasequeryreturnsacursor.Thisinterfaceprovidesrandomread-writeaccesstotheresultset.ItpointstoarowofthequeryresultthatenablesAndroidtobuffertheresultseffectivelysincenowitisnotrequiredtoloadallthedatainthememory.
Thepointerofthereturnedcursorpointstothe0thlocation,whichisknownasthefirstlocationofthecursor.WeneedtocallthemoveToFirst()methodontheCursorobject;ittakesthecursorpointertothefirstlocation.Nowwecanaccessthedatapresentinthefirstrecord.
Cursorimplementations,iffrommultiplethreads,shouldperformtheirownsynchronizationwhenusingthecursor.Acursorneedstobeclosedtofreetheresourcethe
![Page 66: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/66.jpg)
objectholdsbycallingtheclose()method.
Someothersupportmethodswewillencounterareasfollows:
ThegetCount()method:Thisreturnsthenumbersofelementsintheresultingquery.Theget*()methods:Theseareusedtoaccessthecolumndataforthecurrentpositionoftheresult,forexample,getLong(columnIndex)andgetString(columnIndex).ThemoveToNext()method:Thismovesthecursortothenextrow.Ifthecursorisalreadypastthelastentryintheresultset,itwillreturnfalse.
![Page 67: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/67.jpg)
![Page 68: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/68.jpg)
SummaryWecoveredinthischaptertheknow-howofSQLitefeaturesanditsinternalarchitecture.WestartedwithadiscussiononwhatmakesSQLitesopopularbylookingatitssalientfeatures,thenwecoveredtheunderlyingarchitectureofSQLiteandwentoverdatabasefundamentalssuchassyntaxanddatatypes,andfinallymovedontoSQLiteinAndroid.WeexploredtheAndroidAPIsforusingSQLiteinAndroid.
Inthenextchapter,wewillfocusoncarryingforwardwhatwehavelearnedinthischapterandapplyittobuildAndroidapplications.WewillfocusontheUIelementsandconnectingUItothedatabasecomponents.
![Page 69: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/69.jpg)
![Page 70: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/70.jpg)
Chapter2.ConnectingtheDots “Youdon’tunderstandanythinguntilyoulearnitmorethanoneway.”
—-MarvinMinsky
Inthepreviouschapter,welearnedthetwoimportantAndroidclassesandtheircorrespondingmethodsinordertoworkwithanSQLitedatabase:
TheSQLiteOpenHelperclassTheSQLiteDatabaseclass
Wealsosawcodesnippetsexplainingtheirimplementation.Now,wearereadytousealltheseconceptsinanAndroidapplication.Wewillbeleveragingwhatwelearnedinthepreviouschaptertomakeafunctionalapplication.WewillfurtherlookintotheSQLstatementstoinsert,query,anddeletedatafromadatabase.
Inthischapter,wewillbebuildingandrunninganAndroidapplicationonanAndroidemulator.Wewillalsobebuildingourownfull-fledgedcontactsdatabase.WewillencounterAndroidUIcomponents,suchasButtonsandListView,whileprogressingthroughthischapter.IncasearevisitofUIcomponentsinAndroidisrequired,pleasevisitthelinkhttp://developer.android.com/design/building-blocks/index.html.
Beforewebegin,thecodeinthischapterismeanttoexplaintheconceptsrelatedtoanSQLitedatabaseinAndroidandisnotproductionready;inalotofplaces,youwillfindlackofproperexceptionhandlingorlackofpropernullchecksandsimilarpracticestoreduceverbosityinthecode.YoucandownloadthecompletecodefromPackt’swebsiteforthecurrentandfollowingchapters.Forbestresults,werecommenddownloadingthecodeandreferringtoitaswemovealongthechapter.
Inthischapter,wewillcover:
BuildingblocksDatabasehandlerandqueriesConnectingtheUIanddatabase
![Page 71: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/71.jpg)
BuildingblocksAndroidisknowntorunonavarietyofdeviceswithdifferenthardwareandsoftwarespecifications.Atthetimeofwritingthisbook,1billionactivationmarkshavebeencrossed.ThenumberofdevicesrunningAndroidisstaggering,providinguserswitharichvarietyofoptionsindifferentformfactorsandondifferenthardwarebases.Thisaddsaroadblockwhenitcomestotestingyourapplicationondifferentdevices,becauseitishumanlyimpossibletogetholdofthemall,nottoforgetthetimeandcapitalneededtobeinvestedinit.Emulatorinitselfisagreattool;itenablesustocircumventthisproblembygivingustheflexibilitytomimicdifferenthardwarefeatures,suchasCPUarchitecture,RAM,andcamera,anddifferentsoftwareversionsrangingfromearlyCupcaketoKitKat.Wewillalsotrytoleveragethistoouradvantageinourprojectandtrytorunourapplicationontheemulator.Anaddedbenefitofusingtheemulatoristhatwewillberunningarooteddevicethatwillallowustoperformsomeactions.Wewillnotbeabletoachievetheseactionsonanormaldevice.
Let’sstartbysettingupanemulatorinEclipse:
1. GotoAndroidVirtualDeviceManagerfromtheWindowmenutostarttheemulator.
WecansetdifferenthardwarepropertiessuchastheCPUtype,front/backcamera,RAMpreferablylessthan768MBonaWindowsmachine,internal,andexternalstoragesize.
2. Whilelaunchingtheapp,enableSavetosnapshot;thiswillreducethelaunchtimethenexttimewearelaunchinganemulatorinstancefromthesnapshot:
![Page 72: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/72.jpg)
NoteInterestedreaderswhowanttotryoutafasteremulatorcangiveGenymotionatryathttp://www.genymotion.co.
Let’sstartbuildingourAndroidapplicationnow.
3. WewillstartbycreatinganewprojectPersonalContactManager.GotoFile|New|Project.Now,navigatetoAndroidandthenselectAndroidApplicationProject.ThisstepwillgiveusanactivityfileandacorrespondingXMLfile.
Wewillcomebacktothesecomponentsafterwehavealltheblocksweneedinplace.For
![Page 73: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/73.jpg)
ourapplication,wewillcreateadatabasecalledcontact,whichwillcontainonetable,ContactsTable.Inthepreviouschapter,wewentoverhowtocreateadatabaseusingaSQLstatement;let’sconstructadatabaseschemaforourproject.Thisisaveryimportantstepthatisbasedonourapplication’srequirements;forexample,inourcase,wearebuildingapersonalcontactmanagerandwillrequirefieldssuchasname,number,e-mail,andadisplaypicture.
ThedatabaseschemaforContactsTableisoutlined:
Column Datatype
Contact_ID Integer/primarykey/autoincrement
Name Text
Number Text
Email Text
Photo Blob
NoteAnAndroidapplicationcanhavemorethanonedatabaseandeachdatabasecanhavemorethanonetable.Eachtablestoresdatainthe2D(rowsandcolumns)format.
ThefirstcolumnisContact_ID.Itsdatatypeisintegeranditscolumnconstraintistheprimarykey.Also,thecolumnisautoincremented,whichmeansforeachrowitwillbeincrementedbyonewhendataisinsertedinthatrow.
Theprimarykeyuniquelyidentifieseachrowandcannotbenull.Eachtableinadatabasecanhaveoneprimarykeyatthemost.Theprimarykeyofonetablecanactastheforeignkeyforanothertable.Theforeignkeyservesasaconnectionbetweentworelatedtables;forinstance,ourcurrentContactsTableschemais:
ContactsTable(Contact_ID,Name,Number,Email,Photo)
Let’ssaywehaveanothertableColleagueTablewiththefollowingschema:
ColleagueTable(Colleague_ID,Contact_ID,Position,Fax)
Here,theprimarykeyofContactTable,thatis,Contact_IDcanbetermedasaforeignkeyforColleagueTable.ItservesthepurposeoflinkingtwotablesinarelationaldatabaseandhenceallowsustoperformoperationsonColleagueTable.Wewillexplorethisconceptindetailinthechaptersandexamplesahead.
NoteColumnconstraint
Constraintsaretherulesenforcedondatacolumnsinatable.Thisensurestheaccuracyandreliabilityofdatainthedatabase.
![Page 74: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/74.jpg)
UnlikemostSQLdatabases,SQLitedoesnotrestrictthetypeofdatathatmaybeinsertedintoacolumnbasedonthedeclaredtypeofcolumns.Instead,SQLiteusesdynamictyping.Thedeclaredtypeofacolumnisusedtodeterminetheaffinityofthecolumnonly.Thereisatypeconversionalso(automatically)whenonetypeofvariableisstoredintheother.
Constraintscanbecolumnlevelortablelevel.Column-levelconstraintsareappliedonlytoonecolumn,whereastable-levelconstraintsareappliedtothewholetable.
ThefollowingarethecommonlyusedconstraintsandkeywordsavailableinSQLite:
TheNOTNULLconstraint:ThisensuresthatacolumndoesnothaveaNULLvalue.TheDEFAULTconstraint:Thisprovidesadefaultvalueforacolumnwhennoneisspecified.TheUNIQUEconstraint:Thisensuresthatallthevaluesinacolumnaredifferent.ThePRIMARYkey:Thisuniquelyidentifiesallrows/recordsinadatabasetable.TheCHECKconstraint:TheCHECKconstraintensuresthatallthevaluesinacolumnsatisfycertainconditions.TheAUTOINCREMENTkeyword:AUTOINCREMENTisakeywordusedtoautoincrementavalueofafieldinthetable.WecanautoincrementafieldvaluebyusingtheAUTOINCREMENTkeywordwhencreatingatablewithaspecificcolumnnametoautoincrementit.ThekeywordAUTOINCREMENTcanbeusedwiththeINTEGERfieldonly.
Thenextstepistoprepareourdatamodel;wewilluseourschematoframethedatamodelclass.TheContactModelclasswillhaveContact_ID,Name,Number,Email,andPhotoasfields,theyarerepresentedasid,name,contactNo,email,andbyteArrayrespectively.Theclasswillconsistofagetter/settermethodtosetandfetchpropertyvaluesasneeded.Theuseofadatamodelwillfacilitateinthecommunicationoftheactivityusedtoshow/processdataandourdatabasehandler,whichwearegoingtodefinelaterinthischapter.WewillcreateanewpackageandanewclassinitcalledtheContactModelclass.Pleasenotethatcreatinganewpackageisnotanecessarystep;itisusedtoorganizeourclassesinalogicalandeasilyaccessiblemanner.Thisclasscanbedescribedasfollows:
publicclassContactModel{
privateintid;
privateStringname,contactNo,email;
privatebyte[]byteArray;
publicbyte[]getPhoto(){
returnbyteArray;
}
publicvoidsetPhoto(byte[]array){
byteArray=array;
}
publicintgetId(){
returnid;
}
publicvoidsetId(intid){
![Page 75: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/75.jpg)
this.id=id;
}
……………
}
TipEclipseprovidesalotofhelpfulshortcutsbutnotforgeneratinggetterandsettermethods.Wecanbindgeneratinggetterandsettermethodstoanykeybindingasperourliking.InEclipse,gotoWindow|Preferences|General|Keys,searchforgetter,andaddyourbindings.WeareusingAlt+Shift+G;youarefreetosetanyotherkeycombination.
![Page 76: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/76.jpg)
![Page 77: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/77.jpg)
AdatabasehandlerandqueriesWewillbuildoursupportclassthatwillcontainmethodstoread,update,anddeletedataasperourdatabaserequirements.Thisclasswillenableustocreateandupdatethedatabaseandwillactasourhubfordatamanagement.WewillusethisclasstorunSQLitequeriesandsendacrossdatatotheUI;inourcase,alistviewtodisplaytheresults:
publicclassDatabaseManager{
privateSQLiteDatabasedb;
privatestaticfinalStringDB_NAME="contact";
privatestaticfinalintDB_VERSION=1;
privatestaticfinalStringTABLE_NAME="contact_table";
privatestaticfinalStringTABLE_ROW_ID="_id";
privatestaticfinalStringTABLE_ROW_NAME="contact_name";
privatestaticfinalStringTABLE_ROW_PHONENUM="contact_number";
privatestaticfinalStringTABLE_ROW_EMAIL="contact_email";
privatestaticfinalStringTABLE_ROW_PHOTOID="photo_id";
.........
}
WewillcreateanobjectoftheSQLiteDatabaseclass,whichwewillinitializelaterwitheithergetWritableDatabase()orgetReadableDatabase().Wewilldefinetheconstantsthatwewillbeusingthroughtheclass.
NoteByconvention,constantsaredefinedincapitalsbutuseofstaticfinalindefiningaconstantisbitmorethantheconvention.Toknowmore,refertohttp://goo.gl/t0PoQj.
Wewilldefinethenameofourdatabaseascontactanddefinetheversionas1.Ifwelookbacktothepreviouschapter,wewillrecalltheimportanceofthisvalue.Aquickrecapofthisenablesustoupgradethedatabasefromthecurrentversiontothenewversion.Theusecasewillbecomeclearwiththisexample.Let’ssayinfuturethereisanewrequirement,thatis,weneedtoaddafaxnumbertoourcontactdetails.Wewillmodifyourcurrentschematoincorporatethischangeandourcontactdatabasewillcorrespondinglychange.Ifweareinstallingtheapplicationonnewdevices,therewillbenoissue;butincaseofadevicewherewealreadyhavearunninginstanceoftheapplication,wewillfaceproblems.Inthissituation,DB_VERSIONwillcomeinhandyandhelpusreplacetheoldversionofthedatabasewiththecurrentversion.Anotherapproachwouldbetouninstalltheapplicationandinstallitagain,butthatisnotencouraged.
Thetablenameandimportantfieldssuchastablecolumnswillbedefinednow.TABLE_ROW_IDisaveryimportantcolumn.Thiswillserveastheprimarykeyforthetable;itwillalsoautoincrementandcannotbenull.NOTNULLisagainacolumnconstraint,whichmayonlybeattachedtoacolumndefinitionandisnotspecifiedasatableconstraint.Notsurprisingly,aNOTNULLconstraintdictatesthattheassociatedcolumnmaynotcontainaNULLvalue.AttemptingtosetthecolumnvaluetoNULLwheninsertinganewroworupdatinganexistingone,causesaconstraintviolation.Thiswillbeusedtofinda
![Page 78: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/78.jpg)
particularvalueinthetable.TheuniquenessoftheIDguaranteesthatwedonothaveanyconflictswithdatainthetable,sinceeachrowisuniquelyidentifiedbythekey.Therestofthetablecolumnsareprettyself-explanatory.TheconstructorfortheDatabaseManagerclassisasfollows:
publicDatabaseManager(Contextcontext){
this.context=context;
CustomSQLiteOpenHelperhelper=newCustomSQLiteOpenHelper(context);
this.db=helper.getWritableDatabase();
}
NoticethatweareusingaclasscalledCustomSQLiteOpenHelper.Wewillcomebacktothislater.WewillusetheclassobjecttogetourSQLitedatabaseinstance.
![Page 79: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/79.jpg)
BuildingtheCreatequeryTocreateatablewiththedesiredcolumns,wewillbuildaquerystatementandexecuteit.Thestatementwillcontainthetablename,differenttablecolumns,andrespectivedatatype.Wewillnowlookatmethodsforcreatinganewdatabaseandalsoupgradinganexistingdatabaseaccordingtotheneedsoftheapplication:
privateclassCustomSQLiteOpenHelperextendsSQLiteOpenHelper{
publicCustomSQLiteOpenHelper(Contextcontext){
super(context,DB_NAME,null,DB_VERSION);
}
@Override
publicvoidonCreate(SQLiteDatabasedb){
StringnewTableQueryString="createtable"
+TABLE_NAME+"("
+TABLE_ROW_ID
+"integerprimarykeyautoincrementnotnull,"
+TABLE_ROW_NAME
+"textnotnull,"
+TABLE_ROW_PHONENUM
+"textnotnull,"
+TABLE_ROW_EMAIL
+"textnotnull,"
+TABLE_ROW_PHOTOID
+"BLOB"+");";
db.execSQL(newTableQueryString);
}
@Override
publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,
intnewVersion){
StringDROP_TABLE="DROPTABLEIFEXISTS"+
TABLE_NAME;
db.execSQL(DROP_TABLE);
onCreate(db);
}
}
CustomSQLiteOpenHelperextendsSQLiteOpenHelperandprovidesuswiththekeymethodsonCreate()andonUpgrade().WehavedefinedthisclassastheinnerclassofourDatabaseManagerclass.Thisenablesustomanageallthedatabase-relatedfunctions,namelyCRUD(Create,Read,Update,andDelete),fromoneplace.
InourCustomSQLiteOpenHelperconstructor,whichisresponsibleforcreatinganinstanceofourclass,wewillpassacontext,whichinturnwillbepassedtothesuperconstructorwiththefollowingparameters:
Contextcontext:ThisisthecontextwepassedtoourconstructorStringname:ThisisthenameofourdatabaseCursorFactoryfactory:Thisisthecursorfactoryobject,whichcanbepassedasnull
intversion:Thisisthedatabaseversionofthedatabase
![Page 80: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/80.jpg)
ThenextimportantmethodisonCreate().WewillbuildourSQLitequerystring,whichwillcreateourdatabasetable:
"createtable"+TABLE_NAME+"("
+TABLE_ROW_ID
+"integerprimarykeyautoincrementnotnull,"
….....
+TABLE_ROW_PHOTOID+"BLOB"+");";
Thepreviousstatementisbasedonthefollowingsyntaxdiagram:
Here,thekeywordcreatetableisusedtocreateatable.Thisisfollowedbythetablename,thedeclarationofcolumns,andtheirdatatype.AfterpreparingourSQLstatement,wewillexecuteitusingtheexecSQL()methodoftheSQLitedatabase.Incasesomethingiswrongwiththequerystatementthatwebuiltearlier,wewillencountertheexception,android.database.sqlite.SQLiteException.Bydefault,thedatabaseisformedintheinternalmemoryspaceallocatedtotheapplication.Thefoldercanbefoundat/data/data/<yourpackage>/databases/.
Wecaneasilyverifywhetherourdatabaseisformedwhilerunningthispieceofcodeonanemulatororarootedphone.InEclipse,gototheDDMSperspectiveandthengotothefilemanager.Wecaneasilynavigatetothegivenfolderifwehavesufficientpermission,thatis,arooteddevice.Wecanalsopullupourdatabasewiththehelpofthefileexplorer,andwiththehelpofastandaloneSQLitemanagertool,wecanviewourdatabaseandperformCRUDoperationsonitaswell.WhatmakestheAndroidapplication’sdatabasereadablethroughanothertool?Rememberhowwediscussedcross-platforminSQLitefeaturesinthelastchapter?Inthefollowingscreenshot,noticethetablename,theSQLstatementusedtobuildit,andthecolumnnamesalongwiththeirdatatype:
![Page 81: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/81.jpg)
NoteTheSQLiteManagertoolcanbedownloadedeitherintheChromeorFirefoxbrowser.ThefollowingisthelinkforFirefoxextension:http://goo.gl/NLu8JT.
Anotherhandywayofpullingupourdatabaseoranyotherfileisbyusingtheadbpullcommand:
adbpull/data/data/yourpackagename/databases/filelocation
AnotherinterestingpointtonoteisthatthedatatypeofTABLE_ROW_PHOTOIDisBLOB.BLOBstandsforbinarylargeobject.Itisdifferentfromotherdatatype,suchastextandinteger,asitcanstorebinarydata.Thebinarydatacanbeanimage,audio,oranyothertypeofmultimediaobject.
Itisnotadvisabletostorelargeimagesinadatabase;wecanstorefilenamesorlocations,butstoringimagesisbitofoverkill.Imagineasituationlikethiswherewestorecontactimages.Toamplifythissituation,insteadofafewhundredcontacts,makeitafewthousandcontacts.Thesizeofthedatabasewillbecomelargeandtheaccesstimewillalsoincrease.WewanttodemonstratetheuseofBLOBsbystoringcontactimages.
TheonUpgrade()methodiscalledwhenthedatabaseisupgraded.Thedatabaseisupgradedbychangingtheversionnumberofthedatabase.Here,theimplementationdependsontheneedoftheapplication.Insomecases,thewholetablemayhavetobedeletedandanewonemayneedtobecreated,andinsomeapplications,onlyslightmodificationisneeded.HowtomigratefromoneversiontoanotheriscoveredinChapter4,ThreadCarefully.
![Page 82: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/82.jpg)
BuildingtheInsertqueryToinsertanewrowofdatainthedatabasetable,weneedtouseeithertheinsert()methodorwecanmakeaninsertquerystatementandusetheexecute()method:
publicvoidaddRow(ContactModelcontactObj){
ContentValuesvalues=prepareData(contactObj);
try{
db.insert(TABLE_NAME,null,values);
}catch(Exceptione){
Log.e("DBERROR",e.toString());
e.printStackTrace();
}
}
Incaseourtablenameiswrong,SQLitewillgivealognosuchtablemessageandtheexception,android.database.sqlite.SQLiteException.TheaddRow()methodisusedtoinsertcontactdetailsinthedatabaserow;noticethattheparameterofthemethodisanobjectofContactModel.WehavecreatedanadditionalmethodprepareData()toconstructaContentValuesobjectfromtheContactModelobject’sgettermethods:
.......................
values.put(TABLE_ROW_NAME,contactObj.getName());
values.put(TABLE_ROW_PHONENUM,contactObj.getContactNo());
....................
AfterthepreparationoftheContentValuesobject,wearegoingtousetheinsert()methodoftheSQLiteDatabaseclass:
publiclonginsert(Stringtable,StringnullColumnHack,ContentValues
values)
Theparametersoftheinsert()methodareasfollows:
table:Thedatabasetabletoinserttherowinto.values:Thiskey-valuemapcontainstheinitialcolumnvaluesforthetablerow.Columnnamesactaskeys.Valuesasthecolumnvalues.nullColumnHack:Thisisasinterestingasitsname.Here’saquotefromtheAndroiddocumentationwebsite:
“optional;maybenull.SQLdoesn’tallowinsertingacompletelyemptyrowwithoutnamingatleastonecolumnname.Ifyourprovidedvaluesareempty,nocolumnnamesareknownandanemptyrowcan’tbeinserted.Ifnotsettonull,thenullColumnHackparameterprovidesthenameofnullablecolumnnametoexplicitlyinsertNULLintothecasewhereyourvaluesareempty.”
Inshort,incaseswherewearetryingtopassanemptyContentValuestobeinserted,SQLiteneedssomecolumnthatissafetobeassignedNULL.
Alternatively,insteadoftheinsert()method,wecanpreparetheSQLstatementandexecuteitasshown:
![Page 83: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/83.jpg)
publicvoidaddRowAlternative(ContactModelcontactObj){
StringinsertStatment="INSERTINTO"+TABLE_NAME
+"("
+TABLE_ROW_NAME+","
+TABLE_ROW_PHONENUM+","
+TABLE_ROW_EMAIL+","
+TABLE_ROW_PHOTOID
+")"
+"VALUES"
+"(?,?,?,?)";
SQLiteStatements=db.compileStatement(insertStatment);
s.bindString(1,contactObj.getName());
s.bindString(2,contactObj.getContactNo());
s.bindString(3,contactObj.getEmail());
if(contactObj.getPhoto()!=null)
{s.bindBlob(4,contactObj.getPhoto());}
s.execute();
}
Wewillbecoveringalternativesforalotofthemethodswementionedhere.Theideaistomakeyoucomfortablewithotherpossiblewaystobuildandexecutequeries.Theexplanationofthealternativepartisleftasanexerciseforyou.ThegetRowAsObject()methodwillreturnthefetchedrowfromthedatabaseintheformofaContactModelobject,asshowninthefollowingcode.ItwillrequirerowIDasaparametertouniquelyidentifywhichrowinthetablewewanttoaccess:
publicContactModelgetRowAsObject(introwID){
ContactModelrowContactObj=newContactModel();
Cursorcursor;
try{
cursor=db.query(TABLE_NAME,newString[]{
TABLE_ROW_ID,TABLE_ROW_NAME,TABLE_ROW_PHONENUM,TABLE_ROW_EMAIL,
TABLE_ROW_PHOTOID},
TABLE_ROW_ID+"="+rowID,null,
null,null,null,null);
cursor.moveToFirst();
if(!cursor.isAfterLast()){
prepareSendObject(rowContactObj,cursor);}
}catch(SQLExceptione){
Log.e("DBERROR",e.toString());
e.printStackTrace();
}
returnrowContactObj;
}
ThismethodwillreturnthefetchedrowfromthedatabaseintheformofaContactModelobject.WeareusingtheSQLiteDatabase()querymethodtofetchtherowfromourcontacttableagainsttheprovidedrowIDparameter.Themethodreturnsacursorovertheresultset:
publicCursorquery(Stringtable,String[]columns,Stringselection,
String[]selectionArgs,StringgroupBy,Stringhaving,StringorderBy,
Stringlimit)
![Page 84: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/84.jpg)
Thefollowingaretheparametersofthepreviouscode:
table:Thisdenotesthedatabasetableagainstwhichthequerywillberun.columns:Thisisalistofthecolumnsthatarereturned;ifwepassnull,itwillreturnallthecolumns.selection:ThisiswherewedefinewhichrowsaretobereturnedandframedasaSQLWHEREclause.Passingnullwillreturnalltherows.selectionArgs:Wecanpassnullforthisparameterorwemayincludequestionmarksintheselection,whichwillbereplacedbythevaluesfromselectionArgs.groupBy:ThisisafilterframedasaSQLGROUPBYclausedeclaringhowtogrouprows.Passingnullwillcausetherowstonotbegrouped.Having:Thisisafilterthattellswhichrowgroupsaretobemadepartofthecursor,framedasaSQLHAVINGclause.Passingnullwillcausealltherowgroupstobeincluded.OrderBy:ThistellsthequeryhowtoordertherowsframedasanSQLORDERBYclause.Passingnullwillusethedefaultsortorder.limit:ThiswilllimitthenumberofrowsreturnedbythequeryframedastheLIMITclause.PassingnulldenotesanoLIMITclause.
Anotherimportantconcepthereismovingthecursoraroundtoaccessdata.Noticethefollowingmethods:cursor.moveToFirst(),cursor.isAfterLast(),andcursor.moveToNext().
Whenwetrytoretrievedata-buildingSQLquerystatements,thedatabasewillfirstcreateanobjectofthecursorobjectandreturnitsreference.Thepointerofthisreturnedreferenceispointingtothe0thlocation,whichisalsoknownas“beforefirstlocation”ofthecursor.Whenwewanttoretrievedata,wehavetofirstmovetothefirstrecord;hence,theuseofcursor.moveToFirst().Talkingabouttherestofthetwomethods,cursor.isAfterLast()returnswhetherthecursorispointingtothepositionafterthelastrowandcursor.moveToNext()movesthecursortothenextrow.
TipReadersareadvisedtogothroughmoreofthecursormethodsattheAndroiddevelopersite:http://goo.gl/fR75t8.
Alternatively,wecanusethefollowingmethod:
publicContactModelgetRowAsObjectAlternative(introwID){
ContactModelrowContactObj=newContactModel();
Cursorcursor;
try{
StringqueryStatement="SELECT*FROM"
+TABLE_NAME+"WHERE"+TABLE_ROW_ID+"=?";
cursor=db.rawQuery(queryStatement,
newString[]{String.valueOf(rowID)});
cursor.moveToFirst();
![Page 85: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/85.jpg)
rowContactObj=newContactModel();
rowContactObj.setId(cursor.getInt(0));
prepareSendObject(rowContactObj,cursor);
}catch(SQLExceptione){
Log.e("DBERROR",e.toString());
e.printStackTrace();
}
returnrowContactObj;
}
Theupdatestatementisbasedonthefollowingsyntaxdiagram:
Beforewemovetoothermethodsinthedatamanagerclass,let’shavealookatfetchingdatafromacursorobjectintheprepareSendObject()method:
rowObj.setContactNo(cursor.getString(cursor.getColumnIndexOrThrow(TABLE_ROW
_PHONENUM)));
rowObj.setEmail(cursor.getString(cursor.getColumnIndexOrThrow(TABLE_ROW_EMA
IL)));
Herecursor.getstring()takesthecolumnindexasaparameterandreturnsthevalueoftherequestedcolumn,whereascursor.getColumnIndexOrThrow()takesthecolumnnameasaparameterandreturnsthezero-basedindexforthegivencolumnname.Instead
![Page 86: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/86.jpg)
ofthischainingapproach,wecandirectlyusecursor.getstring().Ifweknowthecolumnnumberoftherequiredcolumntofetchdatafrom,wecanusethefollowingnotation:
cursor.getstring(2);
![Page 87: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/87.jpg)
BuildingtheDeletequeryTodeleteaparticularrowofdatafromourdatabasetable,weneedtoprovidetheprimarykeytouniquelyidentifythedatasettoberemoved:
publicvoiddeleteRow(introwID){
try{
db.delete(TABLE_NAME,TABLE_ROW_ID
+"="+rowID,null);
}catch(Exceptione){
Log.e("DBERROR",e.toString());
e.printStackTrace();
}
}
ThismethodusestheSQLiteDatabasedelete()methodtodeletetherowofthegivenIDinthetable:
publicintdelete(Stringtable,StringwhereClause,String[]whereArgs)
Thefollowingaretheparametersoftheprecedingcodesnippet:
table:ThisisthedatabasetableagainstwhichthequerywillberunwhereClause:Thisisaclausetobeappliedwhendeletingarow;passingnullinthisclausewilldeletealltherowswhereArgs:Wemayincludequestionmarksinthewhereclause,whichwillbereplacedbythevaluesthatwillbeboundasstrings
Alternatively,wecanusethefollowingmethod:
publicvoiddeleteRowAlternative(introwId){
StringdeleteStatement="DELETEFROM"
+TABLE_NAME+"WHERE"
+TABLE_ROW_ID+"=?";
SQLiteStatements=db.compileStatement(deleteStatement);
s.bindLong(1,rowId);
s.executeUpdateDelete();
}
Thedeletestatementisbasedonthefollowingsyntaxdiagram:
![Page 88: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/88.jpg)
BuildingtheUpdatequeryToupdateanexistingvalue,weneedtousetheupdate()methodwiththerequiredparameters:
publicvoidupdateRow(introwId,ContactModelcontactObj){
ContentValuesvalues=prepareData(contactObj);
StringwhereClause=TABLE_ROW_ID+"=?";
StringwhereArgs[]=newString[]{String.valueOf(rowId)};
db.update(TABLE_NAME,values,whereClause,whereArgs);
}
Generally,weneedtheprimarykey,inourcasetherowIdparameter,toidentifytherowtobemodified.AnSQLiteDatabaseupdate()methodisusedtomodifytheexistingdataofzeroormorerowsinadatabasetable:
publicintupdate(Stringtable,ContentValuesvalues,StringwhereClause,
String[]whereArgs)
Thefollowingaretheparametersoftheprecedingcodesnippet:
table:Thisisthequalifieddatabasetablenametobeupdated.values:Thisisamappingfromthecolumnnamestothenewcolumnvalues.whereClause:ThisistheoptionalWHEREclausetobeappliedwhenupdatingavalue/row.IftheUPDATEstatementdoesnothaveaWHEREclause,alltherowsinthetablearemodified.whereArgs:Wemayincludequestionmarksinthewhereclause,whichwillbereplacedbythevaluesthatwillbeboundasstrings.
Alternatively,youcanusethefollowingcode:
publicvoidupdateRowAlternative(introwId,ContactModelcontactObj){
StringupdateStatement="UPDATE"+TABLE_NAME+"SET"
+TABLE_ROW_NAME+"=?,"
+TABLE_ROW_PHONENUM+"=?,"
+TABLE_ROW_EMAIL+"=?,"
+TABLE_ROW_PHOTOID+"=?"
+"WHERE"+TABLE_ROW_ID+"=?";
SQLiteStatements=db.compileStatement(updateStatement);
s.bindString(1,contactObj.getName());
s.bindString(2,contactObj.getContactNo());
s.bindString(3,contactObj.getEmail());
if(contactObj.getPhoto()!=null)
{s.bindBlob(4,contactObj.getPhoto());}
s.bindLong(5,rowId);
s.executeUpdateDelete();
}
![Page 89: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/89.jpg)
Theupdatestatementisbasedonthefollowingsyntaxdiagram:
![Page 90: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/90.jpg)
![Page 91: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/91.jpg)
ConnectingtheUIanddatabaseNowthatwehaveourdatabasehooksinplace,let’sconnectourUIwiththedata:
1. Thefirststepwouldbetogetthedatafromtheuser.WecanusetheexistingcontactdatafromtheAndroid’scontactapplicationbymeansofthecontentprovider.
Wewillbecoveringthisapproachinthenextchapter.Fornow,wewillbeaskingtheusertoaddanewcontact,whichwewillinsertintothedatabase:
2. WeareusingstandardAndroidUIwidgets,suchasEditText,TextView,andButtonstocollectthedataprovidedbytheuser:
privatevoidprepareSendData(){
if(TextUtils.isEmpty(contactName.getText().toString())
||TextUtils.isEmpty(
![Page 92: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/92.jpg)
contactPhone.getText().toString())){
.............
}else{
ContactModelcontact=newContactModel();
contact.setName(contactName.getText().toString());
............
DatabaseManagerdm=newDatabaseManager(this);
if(reqType==ContactsMainActivity
.CONTACT_UPDATE_REQ_CODE){
dm.updateRowAlternative(rowId,contact);
}else{
dm.addRowAlternative(contact);
}
setResult(RESULT_OK);
finish();
}
}
prepareSendData()isthemethodthatisresponsibleforbundlingdataintoourobjectmodelandlaterinsertingitinourdatabase.NoticethatinsteadofusingnullcheckandlengthcheckoncontactName,weareusingTextUtils.isEmpty(),whichisaveryhandymethod.Thisreturnstrueifthestringisnullorofzerolength.
3. WeprepareourContactModelobjectfromthedatareceivedbytheuserfillingtheform.WecreateaninstanceofourDatabaseManagerclassandaccessouraddRow()methodpassingourcontactobjecttobeinsertedinthedatabase,aswediscussedearlier.
AnotherimportantmethodisgetBlob(),whichisusedtogettheimagedataintheBLOBformat:
privatebyte[]getBlob(){
ByteArrayOutputStreamblob=newByteArrayOutputStream();
imageBitmap.compress(Bitmap.CompressFormat.JPEG,100,blob);
byte[]byteArray=blob.toByteArray();
returnbyteArray;
}
4. WecreateanewByteArrayOutputStreamobjectblob.Bitmap’scompress()methodwillbeusedtowriteacompressedversionofthebitmaptoouroutputstreamobject:
publicbooleancompress(Bitmap.CompressFormatformat,intquality,
OutputStreamstream)
Thefollowingaretheparametersoftheprecedingcode:
format:Thisistheformatofacompressedimage,inourcase,JPEG.quality:Thisisahinttothecompressor,whichrangesfrom0to100.Thevalue0meanstocompresstoasmallersizeandlowquality,while100isfor
![Page 93: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/93.jpg)
maximumquality.stream:Thisistheoutputstreamtowritethecompresseddatato.
5. Then,wecreateourbyte[]object,whichwillbeconstructedfromtheByteArrayOutputStreamtoByteArray()method.
NoteYouwillnoticethatwearenotcoveringallthemethods;onlythosethatarerelevanttodataoperationsandsomemethodsorcallsthatmightcauseconfusion.Thereareafewmoremethodsthatareusedtoinvokethecameraorgallerytopickaphototobeusedasthecontactimage.Youareadvisedtoexplorethemethodsinthecodeprovidedalongwiththebook.
Let’smoveontothepresentationpartwhereweuseacustomlistviewtodisplayourcontactinformationinapresentableandreadablemanner.Wearegoingtoskipabulkofthecoderelatedtothepresentationandconcentrateonthepartswherewefetchandprovidedatatoourlistview.Wewillalsoimplementacontextmenuinordertoprovideauserwiththefunctionalityofdeletingaparticularcontact.WewillbetouchingbaseonthedatabasemanagermethodssuchasgetAllData()tofetchallouraddedcontacts.WewillusedeleteRow()inordertoremoveanyunwantedcontactsfromourcontactsdatabase.Thefinaloutcomewillbesomethinglikethefollowingscreenshot:
![Page 94: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/94.jpg)
6. Tomakeacustomlistviewsimilartotheoneshownintheprecedingscreenshot,wecreateCustomListAdapterextendingBaseAdapterandusingthecustomlayoutforthelistviewrows.NoticeinthefollowingconstructorwehaveinitializedanewarraylistandwilluseourdatabasemanagertofetchvaluesbyusingthegetAllData()methodtofetchallthedatabaseentries:
publicCustomListAdapter(Contextcontext){
contactModelList=newArrayList<ContactModel>();
_context=context;
inflater=(LayoutInflater)context.getSystemService(
Context.LAYOUT_INFLATER_SERVICE);
dm=newDatabaseManager(_context);
contactModelList=dm.getAllData();
}
AnotherveryimportantmethodisthegetView()method.Thisiswhereweinflateourcustomlayoutinaview:
convertView=inflater.inflate(R.layout.contact_list_row,null);
Wewillusetheviewholderpatterntoimprovethelistviewscrollingsmoothness:
vHolder=(ViewHolder)convertView.getTag();
7. Andfinally,setthedatatothecorrespondingviews:
vHolder.contact_email.setText(contactObj.getEmail());
NoteHoldingviewobjectsinaviewholderimprovestheperformancebyreducingcallstofindViewById().Youcanreadmoreaboutthisandhowtomakelistviewscrollingsmoothathttp://developer.android.com/training/improving-layouts/smooth-scrolling.html.
8. Wewillalsobeimplementingawaytodeletealistviewentry.Wewillusethecontextmenuforthispurpose.Wewillfirstcreateamenuiteminthemenufolderunderresofourapplicationstructure:
<?xmlversion="1.0"encoding="utf-8"?>
<menuxmlns:android="http://schemas.android.com/apk/res/android">
<item
android:id="@+id/delete_item"
android:title="Delete"/>
<item
android:id="@+id/update_item"
android:title="Update"/>
</menu>
9. Now,inourmainactivitywherewewilldisplayourlistview,wewillusethefollowingcalltoregisterourlistviewwiththecontextmenu.Inordertolaunchthecontextmenu,weneedtoperformalongpressactiononthelistviewitem:
![Page 95: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/95.jpg)
registerForContextMenu(listReminder)
10. Thereareafewmoremethodsthatweneedtoimplementinordertoachievethedeletefunctionality:
@Override
publicvoidonCreateContextMenu(ContextMenumenu,Viewv,
ContextMenuInfomenuInfo){
super.onCreateContextMenu(menu,v,menuInfo);
MenuInflaterm=getMenuInflater();
m.inflate(R.menu.del_menu,menu);
}
ThismethodisusedtoinflatethecontextmenuwiththemenuwedefinedearlierinXML.TheMenuInfaterclassgeneratesmenuobjectsfromthemenuXMLfiles.MenuinflationreliesheavilyonthepreprocessingofXMLfilesthatisdoneatbuildtime;thisisdonetoimproveperformance.
11. Now,wewillimplementamethodtocapturetheclickonthecontextmenu:
@Override
publicbooleanonContextItemSelected(MenuItemitem){
..............
caseR.id.delete_item:
cAdapter.delRow(info.position);
cAdapter.notifyDataSetChanged();
returntrue;
caseR.id.update_item:
Intentintent=newIntent(
ContactsMainActivity.this,AddNewContactActivity.class);
......................
}
12. Here,wewillfindthepositionIDoftheclickedlistviewitemandinvokethedelRow()methodoftheCustomListAdapter,andintheend,wewillnotifytheadapterthatthedatasethaschanged:
publicvoiddelRow(intdelPosition){
dm.deleteRowAlternative(contactModelList.get(delPosition).getId());
contactModelList.remove(delPosition);
ThedelRow()methodisresponsibleforconnectingourdatabase’sdeleteRowAlternative()methodtoourcontextmenu’sdelete()method.Here,wefetchtheIDoftheobjectsetontheparticularlistviewitemandpassittothedeleteRowAlternative()methodofdatabaseManagerinordertodeletethedatafromthedatabase.Afterremovingthedatafromthedatabase,wewillinstructourlistviewtoremovethecorrespondingentryfromourcontactlist.
IntheonContextItemSelected()method,wecanalsoseetheupdate_itemincasetheuserhasclickedontheupdatebutton.Wewilllaunchtheactivitytoaddanewcontact
![Page 96: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/96.jpg)
andaddthedatawealreadyhaveincasetheuserwantstoeditsomefields.Thecatchistoknowfromwherethecallhasbeeninitiated.Isittoaddanewentryorupdateanexistingone?Wetakethehelpofthefollowingcodetotelltheactivitythatthisactionisusedtoupdateratherthanaddanewentry:
intent.putExtra(REQ_TYPE,CONTACT_UPDATE_REQ_CODE);
![Page 97: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/97.jpg)
![Page 98: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/98.jpg)
SummaryInthischapter,wecoveredthestepsofbuildingupadatabase-basedapplication,fromscratchandthenfromschematoobjectmodelandthenfromobjectmodeltobuildingactualdatabases.WeunderwenttheprocessofbuildingourdatabasemanagerandfinallyimplementedtheUIdatabaseconnecttoachieveafullyfunctionalapplication.Thetopicscoveredrangedfromthebuildingblocksofthemodelclass,databaseschematoourdatabasehandler,andCRUDmethods.WealsocoveredtheimportantconceptofconnectingadatabasetotheAndroidviewswithproperhooksinplacetopickupuserdata,adddatatothedatabase,andshowrelevantinformationafterpickingupdatafromthedatabase.
Inthenextchapter,wewillfocusonbuildinguponthegroundworkwehavedonehere.WewillexploreContentProviders.WewillalsolearnhowtofetchdatafromContentProviders,howtomakeourowncontentprovider,thebestpracticesassociatedwhilebuildingthem,andmuchmore.
![Page 99: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/99.jpg)
![Page 100: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/100.jpg)
Chapter3.SharingisCaring “Datareallypowerseverythingthatwedo.”
—–JeffWeiner,LinkedIn
Inthelastchapter,westartedprogrammingourveryowncontactmanager.Wecameacrossvariousbuildingblocksofadatabase-centricapplication;wecovereddatabasehandlersandbuildingqueriesinordertogetmeaningfuldatafromourdatabase.WealsoexploredhowtomakeaconnectionbetweenourUIanddatabaseandpresentitinaconsumablemannerfortheenduser.
Inthischapter,wewilllearnhowtoaccessotherapplication’sdataviameansofcontentproviders.Wewillalsolearnhowtobuildourveryowncontentproviderinordertoshareourdatawithotherapplications.WewilllookintoAndroid’sproviderssuchascontactprovider.Towrapthingsup,wewillconstructatestapplicationtouseournewlyconstructedcontentprovider.
Inthischapter,wewillcoverthefollowingtopics:
Whatisacontentprovider?CreatingacontentproviderImplementingthecoremethodsUsingacontentprovider
![Page 101: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/101.jpg)
Whatisacontentprovider?AcontentprovideristhefourthcomponentofanAndroidapplication.Itisusedtomanageaccesstoastructuredsetofdata.Contentprovidersencapsulatethedata,andprovideabstractionandthemechanismtodefinedatasecurity.However,contentprovidersareprimarilyintendedtobeusedbyotherapplicationsthataccesstheproviderusingaprovider’sclientobject.Together,providersandproviderclientsofferaconsistent,standardinterfacefordata,whichalsohandlesinterprocesscommunicationandsecuredataaccess.
Acontentproviderallowsoneapptosharedatawithotherapplications.Bydesign,anAndroidSQLitedatabasecreatedbyanapplicationisprivatetotheapplication;itisexcellentifyouconsiderthesecuritypointofview,buttroublesomewhenyouwanttosharedataacrossdifferentapplications.Thisiswhereacontentprovidercomestotherescue;youcaneasilysharedatabybuildingyourcontentprovider.Itisimportanttonotethatalthoughourdiscussionwillfocusonadatabase,acontentproviderisnotlimitedtoit.Itcanalsobeusedtoservefiledatathatnormallygoesintofiles,suchasphotos,audio,orvideos:
Intheprecedingdiagram,noticehowtheinteractionbetweenApplicationsAandBhappenswhileexchangingdata.Here,wehaveanApplicationAwhoseactivityneedstoaccessthedatabaseofApplicationB.Aswehavealreadyseen,thedatabaseofApplicationBisstoredintheinternalmemoryandcannotbedirectlyaccessedbyApplicationA.ThisiswhereContentProvidercomesintothepicture;itallowsustosharedataandmodifyaccesstootherapplications.Thecontentproviderimplementsmethodstoquery,insert,update,anddeletedataindatabases.ApplicationAnowrequeststhecontentprovidertoperformsomedesiredoperationsonbehalfofit.Wewillexplorebothsidesofthecoin,butwewillfirstuseContentProvidertofetchcontactsfromaphone’scontactdatabase,andthenwewillbuildourveryowncontentproviderforothers
![Page 102: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/102.jpg)
topickdatafromourdatabase.
![Page 103: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/103.jpg)
UsingexistingcontentprovidersAndroidlistsalotofstandardcontentprovidersthatwecanuse.SomeofthemareBrowser,CalendarContract,CallLog,Contacts,ContactsContract,MediaStore,userDictionary,andsoon.
Inourcurrentcontactmanagerapplication,wewilladdanewfeature.IntheUIoftheAddNewContactActivityclass,wewilladdasmallbuttontofetchcontactsfromaphone’scontactlistwithhelpfromthesystem’sexistingContentProviderandContentResolverproviders.WewillbeusingtheContactsContractproviderforthispurpose.
Whatisacontentresolver?TheContentResolverobjectintheapplication’scontextisusedtocommunicatewiththeproviderasaclient.TheContentResolverobjectcommunicateswiththeproviderobject—aninstanceofaclassthatimplementsContentProvider.Theproviderobjectreceivesdatarequestsfromclients,performstherequestedaction,andreturnstheresults.
ContentResolverisasingle,globalinstanceinourapplicationthatprovidesaccesstootherapplication’scontentproviders;wedonotneedtoworryabouthandlinginterprocesscommunication.TheContentResolvermethodsprovidethebasicCRUD(create,retrieve,update,anddelete)functionsofpersistentstorage;ithasmethodsthatcallidenticallynamedmethodsintheproviderobjectbutdoesnotknowtheimplementation.WewillcoverContentResolverinmoredetailasweprogressthroughthischapter.
Intheprecedingscreenshot,noticethenewiconontheright-handsidetoaddcontactsdirectlyfromthephonecontacts;wemodifiedtheexistingXMLtoaddtheicon.ThecorrespondingclassAddNewContactActivitywillalsobemodified:
publicvoidpickContact(){
try{
IntentcIntent=newIntent(Intent.ACTION_PICK,
ContactsContract.Contacts.CONTENT_URI);
startActivityForResult(cIntent,PICK_CONTACT);
}catch(Exceptione){
e.printStackTrace();
Log.i(TAG,"Exceptionwhilepickingcontact");
}
}
![Page 104: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/104.jpg)
WeaddedanewmethodpickContact()toprepareanintentinordertopickcontacts.Intent.ACTION_PICKallowsustopickanitemfromadatasource;inaddition,allweneedtoknowistheUniformResourceIdentifier(URI)oftheprovider,whichinourcaseisContactsContract.Contacts.CONTENT_URI.ThisfunctionalityisalsoprovidedbyMessaging,Gallery,andContacts.IfyoulookintothecodefromChapter2,ConnectingtheDots,youwillfindwehaveusedthesamecodetopickimagesfromGallery.TheContactsscreenwillpopupallowingustobrowseorsearchforcontactswerequiretomigratetoournewapplication.NoticeonActivityResult,thatis,ournextstopwewillmodifythismethodtohandleourcorrespondingrequesttohandlecontacts.LetuslookatthecodewehavetoaddtopickcontactsfromanAndroid’scontactprovider:
{
.
.
.
elseif(requestCode==PICK_CONTACT){
if(resultCode==Activity.RESULT_OK)
{
UricontactData=data.getData();
Cursorc=getContentResolver().query(contactData,null,null,
null,null);
if(c.moveToFirst()){
Stringid=c
.getString(c
.getColumnIndexOrThrow(ContactsContract.Contacts._ID));
StringhasPhone=c
.getString(c
.getColumnIndex(ContactsContract.Contacts.HAS_PHONE_NUMBER));
if(hasPhone.equalsIgnoreCase("1")){
Cursorphones=getContentResolver()
.query(ContactsContract.CommonDataKinds.Phone.CONTENT_URI,
null,
ContactsContract.CommonDataKinds.Phone.CONTACT_ID
+"="+id,null,null);
phones.moveToFirst();
contactPhone.setText(phones.getString(phones
.getColumnIndex("data1")));
contactName
.setText(phones.getString(phones
.getColumnIndex(ContactsContract.Contacts.DISPLAY_NAME)));
}
…..
![Page 105: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/105.jpg)
TipToaddalittleflairtoyourapplication,downloadtheentiresetofstencils,sources,theactionbariconpack,colorswatches,andtheRobotofontfamilyfromtheAndroiddevelopersite,http://goo.gl/4Msuct.DesigningafunctionalapplicationisincompletewithoutaconsistentUIthatfollowsAndroidguidelines.
Westartbycheckingwhethertherequestcodematchesours.Then,wecross-checkresultcode.WegettheContentResolverobjectbymakingacalltogetcontentresolverontheContextobject;itisamethodoftheandroid.content.Contextclass.AsweareinanactivitythatinheritsfromContext,wedonotneedtobeexplicitinmakingacalltoit.Thesamegoesforservices.Wewillnowverifywhetherthecontactwepickedhasaphonenumberornot.Afterverifyingthenecessarydetails,wepullthedatathatwerequire,suchascontactnameandphonenumber,andsettheminrelevantfields.
![Page 106: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/106.jpg)
![Page 107: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/107.jpg)
CreatingacontentproviderAcontentproviderprovidesaccesstodataintwoways:oneisstructureddatathatgoesintheformofadatabase,astheexampleweareworkingoncurrently,orintheformoffiledata,thatis,datathatgoesintheformofpictures,audio,video,andsoonstoredintheprivatespaceoftheapplication.Beforewebegindiggingintohowtocreateacontentprovider,weshouldalsoretrospectwhetherweneedone.Ifwewanttoofferdatatootherapplications,allowuserstocopydatafromourapptoanother,orusethesearchframeworkinourapplication,thentheanswerisyes.
JustlikeotherAndroidcomponents(Activity,Service,orBroadcastReceiver),acontentprovideriscreatedbyextendingtheContentProviderclass.SinceContentProviderisanabstractclass,wehavetoimplementthesixabstractmethods.Thesemethodsareasfollows:
Method Usage
voidonCreate() Initializestheprovider
StringgetType(Uri)ReturnstheMIMEtypeofdatainthecontentprovider
intdelete(Uriuri,Stringselection,String[]selectionArgs)Deletesdatafromthecontentprovider
Uriinsert(Uriuri,ContentValuesvalues)Insertsnewdataintothecontentprovider
Cursorquery(Uriuri,String[]projection,Stringselection,
String[]selectionArgs,StringsortOrder)Returnsdatatothecaller
intupdate(Uriuri,ContentValuesvalues,Stringselection,String[]
selectionArgs)
Updatestheexistingdatainthecontentprovider
Thesemethodswillbedealtwithinmoredetaillaterasweprogressthroughthechapterandbuildourapplication.
![Page 108: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/108.jpg)
UnderstandingcontentURIsEverydataaccessmethodofContentProviderhasacontentURI,asanargumentthatallowsittodeterminethetable,row,orfiletoaccess.Itgenerallyfollowsthefollowingstructure:
content://authority/Path/Id
Let’sanalyzethebreakdownofthecomponentsofthecontent://URI.Theschemeforcontentprovidersisalwayscontent.Thecolonanddouble-slash(://)actasaseparatorfromtheauthoritypart.Then,wehavetheauthoritypart.Byrule,authoritieshavetobeuniqueforeverycontentprovider.ThenamingconventiontheAndroiddocumentationrecommendsusingisthefullyqualifiedclassnameofyourcontentprovidersubclass.Generally,itisbuiltasapackagenameplusaqualifierforeachcontentproviderwepublish.
Theremainingpartisoptional,alsoreferredtoaspath,andisusedforsegregationbetweendifferenttypesofdataourcontentprovidercanprovide.AverygoodexampleistheMediaStoreproviderwhichneedstodistinguishbetweenaudio,video,andimagefiles.
Anotheroptionalpartisid,whichpointstoaspecificrecord;dependingonwhetheridispresentornot,theURIbecomesID-basedordirectory-based,respectively.AnotherwaytounderstanditwouldbethatanID-basedURIenablesustointeractwithdataindividuallyatrowlevel,whereasadirectory-basedURIenablesustointeractwithmultiplerowsofadatabase.
Forexample,considercontent://com.personalcontactmanager.provider/contacts;wewillencounterthissoonenoughaswemoveaheadwiththechapterwherewedefinehowtoaccessthecontentproviderwearecurrentlybuilding.
NoteOnasidenote,thepackagenameforapplicationsshouldalwaysbeunique;thisisbecausealltheapplicationsonPlayStoreareidentifiedbytheirpackagename.AlltheupdatesforanapplicationonPlayStoreneedtohavethesamepackagenameandbesignedwiththesamekeystoreusedinitially.Forinstance,thefollowingisthePlayStorelinkofaGmailapplication;noticethatattheendofURL,wewillfindthepackagenameoftheapplication:
play.google.com/store/apps/details?id=com.google.android.gm
![Page 109: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/109.jpg)
DeclaringourcontractclassDeclaringacontractisaveryimportantpartofbuildingourcontentprovider.Thisclass,asthenamesuggests,willactasacontractbetweenourcontentproviderandtheapplicationthatisgoingtoaccessourcontentprovider.Itisapublicfinalclass,whichcontainsconstantdefinitionsforURIs,columnnames,andothermetadata.ItcanalsocontainJavadoc,butthebiggestadvantageisthatthedeveloperusingitneednotworryaboutthenamesoftables,columns,andconstants,leadingtolesserror-pronecode.
Thecontractclassprovidesuswiththenecessaryabstraction;wecanchangetheunderlyingoperationsasandwhenrequiredandwecanalsochangethecorrespondingdatamanipulationaffectingotherdependentapplications.Animportantthingtonoteisthatweneedtobecarefulwhilechangingthecontractinfuture;ifwearenotcareful,wemightbreaktheotherapplicationsthatareusingourcontractclass.
Ourcontractclasslookslikethefollowing:
publicfinalclassPersonalContactContract{
/**
*TheauthorityofthePersonalContactProvider
*/
publicstaticfinalStringAUTHORITY=
"com.personalcontactmanager.provider";
publicstaticfinalStringBASE_PATH="contacts";
/**
*TheUriforthetop-levelPersonalContactProvider
*authority
*/
publicstaticfinalUriCONTENT_URI=Uri.parse("content://"+AUTHORITY
+"/"+BASE_PATH);
/**
*Themimetypeofadirectoryofitems.
*/
publicstaticfinalStringCONTENT_TYPE=
ContentResolver.CURSOR_DIR_BASE_TYPE+
"/vnd.com.personalcontactmanager.provider.table";
/**
*Themimetypeofasingleitem.
*/
publicstaticfinalStringCONTENT_ITEM_TYPE=
ContentResolver.CURSOR_ITEM_BASE_TYPE+
"/vnd.com.personalcontactmanager.provider.table_item";
/**
*Aprojectionofallcolumns
*intheitemstable.
*/
publicstaticfinalString[]PROJECTION_ALL={"_id",
"contact_name","contact_number",
![Page 110: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/110.jpg)
"contact_email","photo_id"};
/**
*Thedefaultsortorderfor
*queriescontainingNAMEfields.
*/
//publicstaticfinalStringSORT_ORDER_DEFAULT=NAME+"ASC";
publicstaticfinalclassColumns{
publicstaticStringTABLE_ROW_ID="_id";
publicstaticStringTABLE_ROW_NAME="contact_name";
publicstaticStringTABLE_ROW_PHONENUM="contact_number";
publicstaticStringTABLE_ROW_EMAIL="contact_email";
publicstaticStringTABLE_ROW_PHOTOID="photo_id";
}
}
AUTHORITYisthesymbolicnamethatidentifiestheprovideramongmanyotherprovidersregisteredaspartofanAndroidsystem.BASE_PATHisthepathofthetable.CONTENT_URIistheURIofthetableencapsulatedbytheprovider.CONTENT_TYPEistheAndroidplatform’sbaseMIMEtypeforcontentURIcontainingacursorofzeroormoreitems.CONTENT_ITEM_TYPEistheAndroidplatform’sbaseMIMEtypeforcontentURIscontainingacursorofasingleitem.PROJECTION_ALLandColumnscontainthecolumnIDsofthetable.
Withoutthisinformation,otherdeveloperswillnotbeabletoaccessyourprovidereventhoughitisopenforaccess.
NoteTherecanbemanytablesinsideaproviderandeachshouldhaveauniquepath;thepathisnotarealphysicalpathbutanidentifier.
![Page 111: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/111.jpg)
CreatingUriMatcherdefinitionsUriMatcherisautilityclass,whichaidsinmatchingURIsincontentproviders.TheaddURI()methodtakesthecontentURIpatternsthattheprovidershouldrecognize.WeaddaURItomatch,andthecodetoreturnwhenthisURIismatched:
addURI(Stringauthority,Stringpath,intcode)
Wepassauthority,apathpattern,andanintegervaluetotheaddURI()methodofUriMatcher;itreturnstheintvalue,whichwedefinedasconstantwhenwetriedtomatchpatterns.
OurUriMatcherlookslikethefollowing:
privatestaticfinalintCONTACTS_TABLE=1;
privatestaticfinalintCONTACTS_TABLE_ITEM=2;
privatestaticfinalUriMatchermmURIMatcher=new
UriMatcher(UriMatcher.NO_MATCH);
static{
mmURIMatcher.addURI(PersonalContactContract.AUTHORITY,
PersonalContactContract.BASE_PATH,CONTACTS_TABLE);
mmURIMatcher.addURI(PersonalContactContract.AUTHORITY,
PersonalContactContract.BASE_PATH+"/#",
CONTACTS_TABLE_ITEM);
}
Noticethatitalsosupportstheuseofwildcards;wehaveusedhashtag(#)intheprecedingcodesnippet,wecanalsousewildcardssuchas*.Inourcase,withthehashtag,"content://com.personalcontactmanager.provider/contacts/2"thisexpressionmatches,butusing*"content://com.personalcontactmanager.provider/contactsitdoesn’t.
![Page 112: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/112.jpg)
![Page 113: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/113.jpg)
ImplementingthecoremethodsInordertobuildourcontentprovider,thenextstepwillbetoprepareourcoredatabaseaccessanddatamodifyingmethods,betterknownasCRUDmethods.Thisiswherethecorelogicofhowwewanttointeractwithourdatadependingontheinsert,query,ordeletecallsreceivedisspecified.WewillalsoimplementtheAndroidarchitecture’slifecyclemethodssuchasonCreate().
![Page 114: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/114.jpg)
InitializingtheproviderthroughtheonCreate()methodWecreateanobjectofourdatabasemanagerclassinonCreate().Thereshouldbeminimumoperationsinoncreate()asitrunsontheMainUIthread,anditmaycauselagforsomeusers.Itisgoodpracticetoavoidlong-runningtasksinoncreate()asitincreasesthestartuptimeoftheprovider.Itisevenrecommendedtodeferdatabasecreationanddataloadinguntilourprovideractuallyreceivesarequestforthedata,thatis,tomovelong-lastingactionstotheCRUDmethods:
@Override
PublicBooleanonCreate(){
dbm=newDatabaseManager(getContext());
returnfalse;
}
![Page 115: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/115.jpg)
Queryingrecordsthroughthequery()methodThequery()methodwillreturnacursorovertheresultset.TheURIispassedtoourUriMatchertoseewhetheritmatchesanypatternswedefinedearlier.Inourswitchcasestatement,ifitisatable-item-relatedcase,wecheckwhethertheselectionstatementisempty;incaseitis,webuildourselectionstatementuptothelastpathsegment,elseweappendtheselectiontothelastpathsegmentstatement.WeuseaDatabaseManagerobjecttoarunqueryonthedatabaseandgetacursorasaresult.Itisexpectedofthequery()methodtothrowanIllegalArgumentExceptiontoinformofanunknownURI;itisalsogoodpracticetothrowanullPointerExceptionincaseweencounteraninternalerrorduringthequeryprocess:
@Override
publicCursorquery(Uriuri,String[]projection,Stringselection,
String[]selectionArgs,StringsortOrder){
inturiType=mmURIMatcher.match(uri);
switch(uriType){
caseCONTACTS_TABLE:
break;
caseCONTACTS_TABLE_ITEM:
if(TextUtils.isEmpty(selection)){
selection=PersonalContactContract.Columns.TABLE_ROW_ID
+"="+uri.getLastPathSegment();
}else{
selection=PersonalContactContract.Columns.TABLE_ROW_ID
+"="+uri.getLastPathSegment()+
"and"+selection;
}
break;
default:
thrownewIllegalArgumentException("UnknownURI:"+uri);
}
Cursorcr=dbm.getRowAsCursor(projection,selection,
selectionArgs,sortOrder);
returncr;
}
NoteRememberthatanAndroidsystemmustbeabletocommunicatetheexceptionacrossprocessboundaries.Androidcandothisforthefollowingexceptionsthatmaybeusefulinhandlingqueryerrors:
IllegalArgumentException:YoumaychoosetothrowthisifyourproviderreceivesaninvalidcontentURINullPointerException:Thisisthrownwhentheobjectisnullandwetrytoaccessitsfieldormethod
![Page 116: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/116.jpg)
Addingrecordsthroughtheinsert()methodAsthenamesuggests,theinsert()methodisusedtoinsertavalueinourdatabase.ItreturnstheURIoftheinsertedrowand,whilecheckingtheURI,weneedtorememberthataninsertioncanhappenatthetablelevel,hencetheoperationsinthemethodareprocessedattheURIthatmatchesthetable.Aftermatching,weusethestandardDatabaseManagerobjecttoinsertournewvalueintothedatabase.ThecontentURIforthenewrowisconstructedbyappendingthenewrow’s_IDvaluetothetable’scontentURI:
@Override
publicUriinsert(Uriuri,ContentValuesvalues){
inturiType=mmURIMatcher.match(uri);
longid;
switch(uriType){
caseCONTACTS_TABLE:
id=dbm.addRow(values);
break;
default:
thrownewIllegalArgumentException("UnknownURI:"+uri);
}
Uriur=ContentUris.withAppendedId(uri,id);
returnur;
}
![Page 117: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/117.jpg)
Updatingrecordsthroughtheupdate()methodTheupdate()methodupdatesanexistingrowintheappropriatetable,usingthevaluesintheContentValuesargument.First,weidentifytheURI,whetheritisdirectory-basedorID-based,thenwebuildourselectionstatementaswedidinthequery()method.Now,wewillexecutethestandardupdateRow()methodofDatabaseManagerthatwedefinedearlierwhilebuildingthisapplicationinChapter2,ConnectingtheDots,whichreturnsthenumberofaffectedrows.
Theupdate()methodreturnsthenumberofrowsupdated.Basedontheselectionclause,oneormorerowscanbeupdated:
@Override
publicintupdate(Uriuri,ContentValuesvalues,Stringselection,
String[]selectionArgs){
inturiType=mmURIMatcher.match(uri);
switch(uriType){
caseCONTACTS_TABLE:
break;
caseCONTACTS_TABLE_ITEM:
if(TextUtils.isEmpty(selection)){
selection=PersonalContactContract.Columns.TABLE_ROW_ID
+"="+uri.getLastPathSegment();
}else{
selection=PersonalContactContract.Columns.TABLE_ROW_ID
+"="+uri.getLastPathSegment()
+"and"+selection;
}
break;
default:
thrownewIllegalArgumentException("UnknownURI:"+uri);
}
intcount=dbm.updateRow(values,selection,selectionArgs);
returncount;
}
![Page 118: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/118.jpg)
Deletingrecordsthroughthedelete()methodThedelete()methodisverysimilartotheupdate()methodandtheprocessofusingitissimilar;here,thecallismadetodeletearowinsteadofupdatingit.Thedelete()methodreturnsthenumberofrowsdeleted.Basedontheselectionclause,oneormorerowscanbedeleted:
@Override
publicintdelete(Uriuri,Stringselection,String[]selectionArgs){
inturiType=mmURIMatcher.match(uri);
switch(uriType){
caseCONTACTS_TABLE:
break;
caseCONTACTS_TABLE_ITEM:
if(TextUtils.isEmpty(selection)){
selection=PersonalContactContract.Columns.TABLE_ROW_ID
+"="+uri.getLastPathSegment();
}else{
selection=PersonalContactContract.Columns.TABLE_ROW_ID
+"="+uri.getLastPathSegment()
+"and"+selection;
}
break;
default:
thrownewIllegalArgumentException("UnknownURI:"+uri);
}
intcount=dbm.deleteRow(selection,selectionArgs);
returncount;
}
![Page 119: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/119.jpg)
GettingthereturntypeofdatathroughthegetType()methodThesignatureofthissimplemethodtakesaURIandreturnsastringvalue;everycontentprovidermustreturnthecontenttypeforitssupportedURIs.Averyinterestingfactisthatnopermissionsareneededforanapplicationtoaccessthisinformation;ifourcontentproviderrequirespermissions,orisnotexported,alltheapplicationscanstillcallthismethodregardlessoftheiraccesspermissionstoretrieveMIMEtypes.
AlltheseMIMEtypesshouldbedeclaredinthecontractclass:
@Override
publicStringgetType(Uriuri){
inturiType=mmURIMatcher.match(uri);
switch(uriType){
caseCONTACTS_TABLE:
returnPersonalContactContract.CONTENT_TYPE;
caseCONTACTS_TABLE_ITEM:
returnPersonalContactContract.CONTENT_ITEM_TYPE;
default:
thrownewIllegalArgumentException("UnknownURI:"+uri);
}
}
![Page 120: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/120.jpg)
AddingaprovidertoamanifestAnotherimportantstepistoaddourcontentprovidertoamanifest,likewedowithotherAndroidcomponents.Wecanregistermultipleprovidershere.Theimportantbithere,otherthanandroid:authorities,isandroid:exported;itdefineswhetherthecontentproviderisavailableforotherapplicationstouse.Incaseoftrue,theproviderisavailabletootherapplications;ifitisfalse,theproviderisnotavailabletootherapplications.IfapplicationshavethesameuserID(UID)astheprovider,theywillhaveaccesstoit:
<provider
android:name="com.personalcontactmanager.provider.PersonalContactProvider"
android:authorities="com.personalcontactmanager.provider"
android:exported="true"
android:grantUriPermissions="true">
</provider>
Anotherimportantconceptispermissions.Wecanaddadditionalsecuritybyaddingreadandwritepermissions,whichtheotherapplicationhastoaddintheirmanifestXMLfileand,inturn,automaticallyinformauserthattheyaregoingtouseaparticularapplication’scontentprovidereithertoread,write,orboth.Wecanaddpermissionsinthefollowingmanner:
android:readPermission="com.personalcontactmanager.provider.READ"
![Page 121: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/121.jpg)
![Page 122: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/122.jpg)
UsingacontentproviderThemainreasonwebuiltacontentproviderwastoallowotherapplicationstoaccessthecomplexdatastoreinourdatabaseandperformCRUDoperations.Wewillnowbuildonemoreapplicationinordertotestournewlybuiltcontentprovider.Thetestapplicationisverysimple,comprisingofonlyoneactivityclassandonelayoutfile.Ithasstandardbuttonstoperformactions.Nothingfancy,justthetoolsforustotestthefunctionalitywejustimplemented.WewillnowdelveintotheTestMainActivityclassandlookintoitsimplementation:
publicclassTestMainActivityextendsActivity{
publicfinalStringAUTHORITY="com.personalcontactmanager.provider";
publicfinalStringBASE_PATH="contacts";
privateTextViewqueryT,insertT;
publicclassColumns{
publicfinalstaticStringTABLE_ROW_ID="_id";
publicfinalstaticStringTABLE_ROW_NAME="contact_name";
publicfinalstaticStringTABLE_ROW_PHONENUM=
"contact_number";
publicfinalstaticStringTABLE_ROW_EMAIL="contact_email";
publicfinalstaticStringTABLE_ROW_PHOTOID="photo_id";
}
Toaccessacontentprovider,weneeddetailssuchasAUTHORITYandBASE_PATHandthenamesofthecolumnsofdatabasetables;weneedtoaccessthepublicclassColumnsforthispurpose.Wehavemoretablesandwewillseemoreoftheseclasses.Generally,allthisnecessaryinformationwillbetakenfromthepublishedcontractclassofthecontentprovider.Somecontentprovidersalsorequireimplementingreadorwritepermissionsinthemanifest:
<uses-permissionandroid:name="AUTHORITY.permission.WRITE_TASKS"/>
Insomecases,thecontentproviderweneedtoaccesscanaskustoaddpermissionsinourmanifest.Whentheusersinstalltheapplication,theywillseeanaddedpermissionintheirpermissionlist:
@Override
protectedvoidonCreate(BundlesavedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_test_main);
queryT=(TextView)findViewById(R.id.textQuery);
insertT=(TextView)findViewById(R.id.textInsert);
}
NoteTotryoutsomeotherapp’scontentprovider,refertohttp://goo.gl/NEX2hN.
ItlistshowyoucanusetheAny.do’scontentprovider—averyfamoustaskapplication.
![Page 123: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/123.jpg)
WewillsetourlayoutandinitializetheviewswerequireinonCreate()ofactivity.Toquery,wefirstneedtopreparetheURIobjectthatmatchesthetable.
Contentresolvernowcomesintoplay;itactsasaresolverforthecontentURIweprepared.OurgetContentResolver.query()method,inthiscase,willfetchallthecolumnsandrows.Wewillnowmovethecursortothefirstpositioninordertoreadtheresult.Fortestingpurposes,it’sreadasastring:
publicvoidquery(Viewv){
UricontentUri=Uri.parse("content://"+AUTHORITY
+"/"+BASE_PATH);
Cursorcr=getContentResolver().query(contentUri,null,
null,null,null);
if(cr!=null){
if(cr.getCount()>0){
cr.moveToFirst();
Stringname=cr.getString(cr.getColumnIndexOrThrow(
Columns.TABLE_ROW_NAME));
queryT.setText(name);
}
}
....
....
}
Now,webuildaURItoreadaparticularrowinsteadofacompletetable.WealreadymentionedthattomakeURIID-based,weneedtoaddtheIDparttoourexistingcontenturi.Now,webuildourprojectionstringarraytobepassedasaparameterinourquery()method:
publicvoidquery(Viewv){
...
...
UrirowUri=contentUri=ContentUris.withAppendedId
(contentUri,getFirstRowId());
String[]projection=newString[]{
Columns.TABLE_ROW_NAME,Columns.TABLE_ROW_PHONENUM,
Columns.TABLE_ROW_EMAIL,Columns.TABLE_ROW_PHOTOID};
cr=getContentResolver().query(contentUri,projection,
null,null,null);
if(cr!=null){
if(cr.getCount()>0){
cr.moveToFirst();
Stringname=cr.getString(cr.getColumnIndexOrThrow(
Columns.TABLE_ROW_NAME));
![Page 124: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/124.jpg)
queryT.setText(name);
}
}
}
ThegetFirstRowId()methodgetstheIDofthefirstrowinthetable.ItisdonebecausetheIDofthefirstrowwillnotalwaysbe1.Itchangeswhentherowsaredeleted.IfthefirstiteminthetablewithrowID1isdeleted,thentheseconditemwithrowID1becomesthefirstitem:
privateintgetFirstRowId(){
intid=1;
UricontentUri=Uri.parse("content://"+AUTHORITY+"/"
+"contacts");
Cursorcr=getContentResolver().query(contentUri,null,
null,null,null);
if(cr!=null){
if(cr.getCount()>0){
cr.moveToFirst();
id=cr.getInt(cr.getColumnIndexOrThrow(
Columns.TABLE_ROW_ID));
}
}
returnid;
}
Let’stakeacloserlookatthequery()method:
publicfinalCursorquery(Uriuri,String[]projection,Stringselection,
String[]selectionArgs,StringsortOrder)
PresentinAPIlevel1,thequery()methodreturnsacursorovertheresultsetagainsttheparameterswesupplied.Thefollowingaretheparametersoftheprecedingcode:
uri:ThisiscontentURIinourcase,usingthecontent://schemeforthecontenttoberetrieved.ItcanbeID-basedordirectory-based.projection:Thisisalistofthecolumnstobereturnedaswehavepreparedusingthecolumnnames.Passingnullwillreturnallthecolumns.selection:FormattedasaSQLWHEREclause,excludingtheWHEREitself,thisactsasafilterdeclaringwhichrowstoreturn.selectionArgs:Wemayinclude?parametermarkersinselection.AndroidSQLquerybuilderwillreplacethe?parametermarkersbythevaluesboundasstringfromselectionArgs,intheorderthattheyappearintheselection.sortOrder:Thistellsushowtoordertherows,formattedasanSQLORDERBYclause.Anullvaluewillusethedefaultsortorder.
NoteAccordingtoofficialdocumentation,thereareafewguidelinesweshouldfollowfor
![Page 125: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/125.jpg)
optimumperformance:
Provideanexplicitprojectiontopreventreadingdatafromstoragethatisn’tgoingtobeused.Usequestionmarkparametermarkerssuchasphone=?insteadofexplicitvaluesintheselectionparameter,sothatqueriesthatdifferonlybythosevalueswillberecognizedasthesameforcachingpurposes.
Thesameprocessweusedearliertocheckfornullvaluesandanemptycursorisperformed,andfinally,arequiredvalueisextractedfromthecursor.
Now,letuslookattheinsertmethodforourtestapplication.
Westartbybuildingourcontentvalueobjectandrelevantkey-valuepairs,forinstance,puttingaphonenumberintherelevantColumns.TABLE_ROW_PHONENUMfield.Noticethatbecausedetailssuchasacolumn’snameweresharedwithusintheformofaclass,weneednotworryaboutdetailssuchastheactualcolumnname.WejustneedtoaccessitviameansoftheColumnsclass.Thisensuresthatweonlyneedtoupdatetherelevantvalues.Ifinfuturethecontentproviderundergoessomechangeandchangesthetablenames,therestofthefunctionalityandimplementationremainsthesame.Webuildourprojectionstringarraywiththecolumnnameswerequired,aswedidearlierinthecaseofqueryingthecontentproviderfordata.
WealsobuildourcontentURI;noticethatitmatchesthetableandnotindividualrows.Theinsert()methodalsoreturnsaURIunlikethequery()method,whichreturnedacursorovertheresultset:
publicvoidinsert(Viewv){
Stringname=getRandomName();
Stringnumber=getRandomNumber();
ContentValuesvalues=newContentValues();
values.put(Columns.TABLE_ROW_NAME,name);
values.put(Columns.TABLE_ROW_PHONENUM,number);
values.put(Columns.TABLE_ROW_EMAIL,name+"@gmail.com");
values.put(Columns.TABLE_ROW_PHOTOID,"abc");
String[]projection=newString[]{
Columns.TABLE_ROW_NAME,Columns.TABLE_ROW_PHONENUM,
Columns.TABLE_ROW_EMAIL,Columns.TABLE_ROW_PHOTOID};
UricontentUri=Uri.parse("content://"+AUTHORITY+"/"
+BASE_PATH);
UriinsertedRowUri=getContentResolver().insert(
contentUri,values);
//checkingtheaddedrow
Cursorcr=getContentResolver().query(insertedRowUri,
projection,null,null,null);
if(cr!=null){
![Page 126: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/126.jpg)
if(cr.getCount()>0){
cr.moveToFirst();
name=cr.getString(cr.getColumnIndexOrThrow(
Columns.TABLE_ROW_NAME));
insertT.setText(name);
}
}
}
ThegetRandomName()andgetRandomNumber()methodsgeneratearandomnameandnumbertoinsertinthetable:
privateStringgetRandomName(){
Randomrand=newRandom();
Stringname=""+(char)(122-rand.nextInt(26))
+(char)(122-rand.nextInt(26))
+(char)(122-rand.nextInt(26))
+(char)(122-rand.nextInt(26))
+(char)(122-rand.nextInt(26))
+(char)(122-rand.nextInt(26))
+(char)(122-rand.nextInt(26))
+(char)(122-rand.nextInt(26));
returnname;
}
publicStringgetRandomNumber(){
Randomrand=newRandom();
Stringnumber=rand.nextInt(98989)*rand.nextInt(59595)+"";
returnnumber;
}
Let’stakeacloserlookattheinsert()method:
publicfinalUriinsert(Uriurl,ContentValuesvalues)
Thefollowingaretheparametersoftheprecedinglineofcode:
url:TheURLofthetabletoinsertthedataintovalues:ThevaluesforthenewlyinsertedrowintheformofaContentValuesobject,thekeyisthecolumnnameforthefield
Noticethatafterinserting,wearerunningthequery()methodagainwiththeURIthatwasreturnedbytheinsert()method.Werunthistoseethatthevalueweintendedtoinserthasbeeninserted;thisquerywillreturncolumnsaspertheprojectionoftherowwhoseIDisappended.
Sofar,wehavecoveredthequery()andinsert()methods;now,wewillcovertheupdate()method.
Weprogressedintheinsert()methodbypreparingtheContentValuesobject.Similarly,wewillprepareanobjectthatwewilluseintheupdate()methodofContentResolverto
![Page 127: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/127.jpg)
updateanexistingrow.WewillbuildourURIinthiscaseuptotheID,asthisoperationisIDbased.UpdatetherowaspointedbytherowUriobjectanditwillreturnthenumberofrowsupdated,whichwillbethesameastheURI;inthiscase,itisrowUrithatpointstoonlyasinglerow.AnalternatemethodcouldbeusingacombinationofcontentUri(whichpointstothetable)andselection/selectionArgs.Inthiscase,therowsupdatedcouldbemorethanoneaspertheselectionclause:
publicvoidupdate(Viewv){
Stringname=getRandomName();
Stringnumber=getRandomNumber();
ContentValuesvalues=newContentValues();
values.put(Columns.TABLE_ROW_NAME,name);
values.put(Columns.TABLE_ROW_PHONENUM,number);
values.put(Columns.TABLE_ROW_EMAIL,name+"@gmail.com");
values.put(Columns.TABLE_ROW_PHOTOID,"");
UricontentUri=Uri.parse("content://"+AUTHORITY
+"/"+BASE_PATH);
UrirowUri=ContentUris.withAppendedId(
contentUri,getFirstRowId());
intcount=getContentResolver().update(rowUri,values,null,null);
}
Let’stakeacloserlookattheupdate()method:
publicfinalintupdate(Uriuri,ContentValuesvalues,Stringwhere,
String[]selectionArgs)
Thefollowingaretheparametersoftheprecedinglineofcode:
uri:ThisisthecontentURIwewishtomodifyvalues:Thisissimilartothevaluesweusedearlierwithothermethods;passinganullvaluewillremoveanexistingfieldvaluewhere:ASQLWHEREclausethatactsasafiltertorowsbeforeupdatingthem
Wecanrunthequery()methodagaintoseewhetherthechangeisreflected;thisactivityhasbeenleftasanexerciseforyou.
Thelastmethodisdelete(),whichwerequireinordertocompleteourarsenalofCRUDmethods.Thedelete()methodbeginsinasimilarfashionastherestofthemethodsdo;first,prepareourcontentURIatthedirectorylevelandthenbuilditfortheIDlevel,thatis,attheindividualrowlevel.Afterthat,wepassittothedelete()methodofContentResolver.Unlikethequery()andinsert()methodsthatreturnanintegervalue,thedelete()methoddeletesarowaspointedbyourID-basedcontentURIobjectrowUriandreturnsthenumberofrowsdeleted.Thiswillbe1inourcaseasourURIpointstoonlyonerow.AnalternatemethodcouldbeusingacombinationofcontentUri,whichpointstothetable,andselection/selectionArgs.Inthiscase,therowsdeletedcouldbemorethan1aspertheselectionclause:
![Page 128: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/128.jpg)
publicvoiddelete(Viewv){
UricontentUri=Uri.parse("content://"+AUTHORITY
+"/"+BASE_PATH);
UrirowUri=contentUri=ContentUris.withAppendedId(
contentUri,getFirstRowId());
intcount=getContentResolver().delete(rowUri,null,
null);
}
TheUIandoutputlooklikethefollowing:
NoteIfyouwanttodiveinalittlemoreintohowanAndroidcontentprovideractuallymanagesvariouswriteandreadcallsbetweenvarioustables(hint:itusesCountDownLatch),youcancheckoutthevideoatCourserabyDr.DouglasC.Schmidtformoreinformation.Thevideocanbefoundathttps://class.coursera.org/posa-002/lecture/49.
![Page 129: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/129.jpg)
![Page 130: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/130.jpg)
SummaryInthischapter,wecoveredthebasicsofcontentproviders.Welearnedhowtoaccesssystem-providedcontentprovidersandevenourownversionofacontentprovider.Wewentfromcreatingabasiccontactmanagertoevolvingitintoafully-fledgedcitizenoftheAndroidecosystembyimplementingContentProviderinordertosharedataacrossotherapplications.
Inthefollowingchapter,wewillcoverLoaders,CursorAdapters,niftyhacksandtips,andsomeopensourcelibrariestomakeourlifeeasierwhileworkingwiththeSQLitedatabase.
![Page 131: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/131.jpg)
![Page 132: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/132.jpg)
Chapter4.ThreadCarefully “Prematureoptimizationistherootofallevil.”
—-DonaldKnuth
Wecoveredaveryimportantconceptinthepreviouschapter:contentprovider.Weprogressedinastep-by-stepmanner,coveringessentialquestionssuchashowtocreateacontentproviderandhowtouseanexistingsystemwithacontentproviderindetail.Wealsocoveredhowtousethecontentproviderwecreatedbymeansofcreatingatestapplicationtoaccessit.
Inthischapter,wewillexplorehowtouseloaders,inparticular,aloadercalledcursorloader.Wewilllookathowtointeractwithacontentproviderasynchronouslywiththehelpofanexample.WewilldiscusstheimportanttopicofsecurityintheAndroiddatabaseandhowwecanensurethatdataissecuredinanAndroidmodel.Lastbutnotleast,wewillalsoseesomecodesnippetsthatwillcovertopicssuchashowtoupgradeadatabaseandhowtoshipapreloadeddatabasewithourapplication.
Inthischapter,wewillcoverthefollowingtopics:
LoadingdatawithCursorLoaderDatasecurityGeneraltipsandlibraries
![Page 133: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/133.jpg)
LoadingdatawithCursorLoaderCursorLoaderispartoftheloaderfamily.BeforewedivedeepintoanexampleexplaininghowtouseCursorLoader,wewillexploreabitaboutloadersandwhyitisimportantinthecurrentscenario.
![Page 134: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/134.jpg)
LoadersIntroducedinHoneyComb(APIlevel11),loadersservethepurposeofasynchronouslyservingdatainanactivityorfragment.Theneedtohaveloadersarosefrommanythings:callstovarioustime-consumingmethodsonthemainUIthreadinordertofetchdatathatleadstoaclunkyUI,andeveninsomecases,thedreadedANRbox.Thisisdemonstratedinthefollowingscreenshot:
Forexample,themanagedQuery()method,whichwasdeprecatedinAPI11,wasawrapperaroundtheContentResolver'squery()method.
Inthepreviouschapter,whilehighlightinghowtofetchdatafromacontentproviderinsidethequerymethod,weusedgetContentResolver.query()insteadofmanagedQuery().Usingdeprecatedmethodscanleadtoproblemswithfuturereleasesandshouldbeavoided.
Loadersprovideasynchronousloadingofdataforanactivityoffragmentonanon-UIthread.Theloaderorthesubclassesofaloaderperformtheirworkinaseparatethreadanddelivertheirresultstothemainthread.Thesegregationofcallsfromthemainthreadandthepostingofresultsonthemainthreadwhileworkinginaseparatethreadensurethatwehavearesponsiveapplication.
TipPosttheloaderera,wewerefacedwithproblemssuchaswhenanactivityshouldberecreatedduetoaconfigurationchange,forinstance,rotationofadevice’sorientation.Wehadtoworryaboutdataandrefetchdatawhilecreatinganewinstance.Butwithloaders,wedon’thavetoworryaboutalltheseasloadersautomaticallyreconnecttothelastloader’scursorwhenbeingrecreatedafteradeviceconfigurationchangeandrefetchthedata.Asanaddedbonus,loadersmonitorthedatasourceanddelivernewresultswhenthecontentchanges.Inotherwords,loadersautomaticallygetupdated,andhence,thereisnoneedtorequerythecursor.ReadmoreaboutkeepingyourAndroidapplicationresponsiveandavoidingapplicationnotresponding(ANR)messagesattheAndroiddeveloperwebsite,http://developer.android.com/training/articles/perf-anr.html.
![Page 135: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/135.jpg)
LoaderAPI’ssummaryLet’slookattheloaderAPIthatconsistsofvariousclassesandinterfaces.Inthissection,wewilllookattheimplementationaspectofloaderAPI’sclasses/interfaces:
Class/interface Description
LoaderManager
Thisisanabstractclassassociatedwithanactivityorfragmenttomanagealoader.Althoughtherecanbeoneormoreloaderinstances,onlyoneinstanceofLoaderManagerperactivityorfragmentispermitted.Itisresponsiblefordealingwiththeactivityorfragment’slifecycleandparticularlyhelpfulwhenrunninglong-runningtasks.
LoaderManager.LoaderCallbacks ThisisacallbackinterfacewemustimplementtointeractwithLoaderManager.
Loader
Thisisthebaseclassforaloader.It’sanabstractclassthatperformsasynchronousloadingofdata.WecanimplementourownsubclassinsteadofusingsubclassessuchasCursorLoader.
AsyncTaskLoader
ThisisanabstractloaderthatprovidesAsyncTasktoperformtheworkinthebackground,thatis,onaseparatethread;however,theresultisdeliveredonthemainthread.Accordingtothedocumentation,itisadvisedtosubclassAsyncTaskLoaderinsteadofdirectlysubclassingtheLoaderclass.
CursorLoaderThisisasubclassofAsyncTaskLoaderthatqueriesContentResolveronthebackgroundthreadinanon-blockingmannerandreturnsacursor.
![Page 136: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/136.jpg)
UsingCursorLoaderLoadersprovideuswithalotofhandyfeatures;oneofthemisthatonceouractivityorfragmentimplementsaloader,itneednotworryaboutrefreshingthedata.Aloadermonitorsthedatasourceforus,reflectsanychanges,andevenperformsnewloads;allofthisisdoneasynchronously.Hence,wedonotneedtotakecareofimplementingandmanagingthreads,offloadingqueriesonthebackgroundthread,andretrievingresultsoncethequeryiscompleted.
Aloadercanbeinanyoneofthefollowingthreedistinctstates:
Startedstate:Oncestarted,loadersremaininthisstateuntilstoppedorreset.Itexecutesloads,monitorsanychange,andreflectsthesametothelisteners.Stoppedstate:Here,loaderscontinuetomonitorchangesbutdonotpasstheresulttotheclients.Resetstate:Inthisstate,loadersreleaseanyresourcestheyhaveheldanddonotperformtheprocessofexecuting,loading,ormonitoringdata.
WewillnowrelookatourpersonalcontactmanagerapplicationandmakethecorrespondingchangestoimplementCursorLoaderinourapplication.CursorLoader,asthenamesuggests,isaloaderthatqueriesContentResolverandreturnsacursor.ThisisasubclassofAsyncTaskLoaderandperformsthecursorqueryonthebackgroundthreadsothatitdoesnotblocktheapplication’sUI.Inthediagram,youcanseethevariousmethodsofaloadercallbackandhowtheycommunicatewithCursorLoaderandCursorAdapter.
![Page 137: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/137.jpg)
Forimplementingacursorloader,weneedtoperformthefollowingsteps:
1. Tobeginwith,weneedtoimplementtheLoaderManager.LoaderCallbacks<Cursor>interface:
publicclassContactsMainActivityextendsActivityimplements
OnClickListener,LoaderManager.LoaderCallbacks<Cursor>{…}
Then,implementthemethodsthatreflectthedistinctstatesofaloader:onCreateLoader(),onLoadFinished(),andonLoaderReset().
2. Toinitiateaquery,wewillmakeacalltotheLoaderManager.initLoader()method;thisinitializesthebackgroundframework:
getLoaderManager().initLoader(CUR_LOADER,null,this);
TheCUR_LOADERvalueispassedontotheonCreateLoader()method,whichactsasanIDfortheloader.Acalltoinitloader()invokesonCreateLoader(),passingtheIDweusedtocallinitloader():
@Override
publicLoader<Cursor>onCreateLoader(intloaderID,
Bundlebundle)
{
switch(loaderID){
caseCUR_LOADER:
returnnewCursorLoader(this,PersonalContactContract.CONTENT_URI,
PersonalContactContract.PROJECTION_ALL,null,null,null);
default:returnnull;
}
}
3. WeuseaswitchcasetotaketheloaderbasedonitsIDandreturnnullforaninvalidID.WecreateaURIobjectcontentUriandpassitasaparametertotheCursorLoaderconstructor.Apointtonoteisthatwecanimplementacursorloaderusingeitherthisconstructororanemptyunspecifiedcursorloader,CursorLoader(Contextcontext).Also,wecansetvaluesviamethodssuchassetUri(Uri),setSelection(String),setSelectionArgs(String[]),setSortOrder(String),andsetProjection(String[]):
publicCursorLoader(Contextcontext,Uriuri,String[]projection,
Stringselection,String[]selectionArgs,StringsortOrder)
Thefollowingaretheparametersofthepreviouscode:
context:Thisistheparentactivitycontext.uri:WeemploycontentURI,usingthecontent://scheme,toretrievethecontent.ItcanbebasedonanIDordirectory.projection:Thisisalistofcolumnstobereturnedaswearepreparedwiththecolumnnames.Passingnullwillreturnallthecolumns.selection:ThisisformattedasaSQLWHEREclause,excludingtheWHEREitself,actingasafilterdeclaringwhichrowstoreturn.
![Page 138: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/138.jpg)
selectionArgs:Wemayincludequestionmarksintheselection,whichwillbereplacedbythevaluesboundasastringfromselectionArgs,andtheywillappearintheorderoftheirselection.sortOrder:Thistellsushowtoorderrows,formattedasaSQLORDERBYclause.Anullvaluewillusethedefaultsortorder.
4. onCreateLoaderstartsthequeryinthebackground,andwhenthequeryisfinished,thecursorloaderobjectispassedtothebackground’sframework,whichcallsonLoadFinished(),whereweprovideouradapterinstancewiththecursorobjectdata:
@Override
publicvoidonLoadFinished(Loader<Cursor>loader,Cursordata)
{
this.mAdapter.changeCursor(data);
}
5. TheadapterisasubclassofCursorAdapter.InsteadofthetraditionalgetView()method,whichwegetbyextendingBaseAdapter,wehavethebindView()andnewView()methods.WeinflateourlistviewrowlayoutintheviewobjectinnewView,andinbindview,weperformanactionsimilartothegetView()method.Wedefineourlayoutelementsandassociatethemewiththerelevantdata:
publicclassCustomCursorAdapterextendsCursorAdapter
{
...
publicvoidbindView(Viewview,Contextarg1,Cursorcursor)
{
finalImageViewcontact_photo=(ImageView)view
.findViewById(R.id.contact_photo);
...
...
contact_email.setText(cursor.getString(cursor
.getColumnIndexOrThrow(DatabaseConstants.TABLE_ROW_EMAIL)));
setImage(cursor.getBlob(cursor
.getColumnIndex(DatabaseConstants.TABLE_ROW_PHOTOID)),
contact_photo);
}
@Override
publicViewnewView(Contextarg0,Cursorarg1,ViewGrouparg2)
{
finalViewview=LayoutInflater.from(context).inflate(
R.layout.contact_list_row,null,false);
returnview;
}
...
}
6. Thismethodisinvokedwhenthecursorloaderisbeingreset.WeclearoutanyreferencetothecursorbypassingnulltothechangeCursor()method.Wheneverthedataassociatedwithacursorchanges,thecursorloadercallsthismethodbeforeitrerunsthequerytoclearanypastreferences,therebypreventingmemoryleaks.Once
![Page 139: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/139.jpg)
onLoaderReset()isset,thecursorloaderwillrerunitsquery:
@Override
publicvoidonLoaderReset(Loader<Cursor>loader)
{
this.mAdapter.changeCursor(null);
}
7. Nowwemoveontoourcontentproviderwherewehavetomakesmallchangestoensurethatanychangeswemaketothedatabasearereflectedinourapplication’slistview:
cr.setNotificationUri(getContext().getContentResolver(),uri);
8. WeneedtoregisterobserverinContentResolverthroughthecursorinthequerymethodofContentProvider.WedothistowatchthecontentURIforanychanges,whichcanbetheURIofaspecificdatarowortableinourcase:
getContext().getContentResolver().notifyChange(ur,null);
9. Intheinsert()method,weusethenotifyChange()methodtoinformregisteredobserversthatarowwasupdated.Bydefault,theCursorAdapterobjectswillgetthisnotification.So,nowwhenweaddanewrowofdatabyinsertinganewcontactinourapplication,theinsert()methodofcontentProviderisinvokedviaacall:
resolver.insert(PersonalContactContract.CONTENT_URI,
prepareData(contact));
10. Asimilaractionneedstobeperformedforthedelete()andupdate()methods,bothofwhichhavebeenleftasanexerciseforthereaderasmostoftheboilerplatecodeispresent.Implementingaloaderissimpleandsavesusfromalotofheadachewhenitcomestothreading,andajarringUIishighlyrecommendedtoperformthistask.
NoteloadInBackground()isanotherimportantmethod;thisreturnsacursorinstanceforaloadoperationandiscalledontheworkerthread.Ideally,loadInBackground()shouldnotdirectlyreturntheresultoftheloadoperation,butwecanachievethisbyoverridingthedeliverResult(D)method.Tocancel,weneedtocheckthevalueofisLoadInBackgroundCanceled()aswedointhecaseofAsyncTask,wherewecheckisCancelled()periodically.
![Page 140: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/140.jpg)
![Page 141: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/141.jpg)
DatasecuritySecurityisthelatestbuzzwordintown.TheAndroidecosystemensuresthatourdatabaseisexposedtopryingeyes;however,arooteddevicecanleaveourdatabaseexposed,aswesawinChapter2,ConnectingtheDots.Withthehelpofarooteddevice,anemulatorandtheadbpullcommandinourcase,wepulledourdatabaseforinspectionwiththeSQLitemanagertool.Anotherimportantaspectiscontentproviders;weneedtobecarefulwhilesettingpermissions.Weshouldmaketheprocessofapplyingappropriatepermissionscompulsoryinordertoinformusersaboutthecontrolthatanappestablishesoverdata,usingthecontractclass.
![Page 142: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/142.jpg)
ContentProviderandpermissionsInChapter3,SharingisCaring,webrieflycoveredthetopicofpermissionsintheAddingaprovidertoamanifestsection.Let’selaboratealittlemoreonthis:
1. Asmentionedearlier,whileaddingthecontentprovidertothemanifest,wewillalsoaddourcustompermissions.Thiswillensuretwothings,namely,stopanunauthorizedactioninanapplicationandinformtheusersaboutpermissions:
<provider
android:name="com.personalcontactmanager.provider.PersonalContactProvid
er"
android:authorities="com.personalcontactmanager.provider"
android:readPermission="com.personalcontactmanager.provider.read"
android:exported="true"
android:grantUriPermissions="true"
>
2. Additionally,wewilladdthepermissionstagtothemanifesttoindicatethesetofpermissionsthatotherapplicationswillrequire:
<permission
android:name="com.personalcontactmanager.provider.read"
android:icon="@drawable/ic_launcher"
android:label="ContactManager"
android:protectionLevel="normal">
</permission>
3. Now,intheapplicationinwhichwewanttoaccessthecontentproviderweusethepermissiontag,inourcase,Ch4-TestAppincodebundle:
<uses-permission
android:name="com.personalcontactmanager.provider.read"/>
Whenusersinstallthisapplication,theywillgetourcustompermissionmessagealongwithotherpermissionsrequiredbytheapplication.Forthisstep,insteadofdirectlyrunningtheapplicationfromEclipse,exportanapkandinstallit:
![Page 143: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/143.jpg)
Ifyouhavenotdefinedthepermissionintheapplicationandiftheapplicationtriestoaccessthecontentprovider,itwillgettheSecurityException:PermissionDenialmessage.
Ifthecontentproviderwecreatedisnotmeanttobeshared,wewillneedtochangetheandroid:exported="true"propertytofalse.Thiswillmakeourcontentprovidersecure,andifsomeonetriestorunamaliciousqueryonit,theywillencounterasecurityexception.
Ifwewanttosharedataonlybetweenourapplications,Androidprovidesasolution;wecanuseandroid:protectionLevelandsetthepermissiontosignatureinsteadofnormal.Forthis,boththeapps,theonethatimplementsthecontentproviderandtheonethatwantstoaccessit,havetobesignedbythesamekeywhiletheyareexported.Thisisbecauseabonussignaturepermissiondoesnotrequireuserconfirmation.Thisdoesnotconfusetheuserasitisdoneinternallyandalsodoesnotobstructtheuserexperience.
![Page 144: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/144.jpg)
EncryptingcriticaldataWehavealreadydiscussedwhatkindofaccessrightsotherapplicationshaveonourdatabaseandhowtoefficientlyshareourcontentproviders,andwealsobrieflydiscussedwhyweshouldnotbelievethatthesystemisfoolproof.Inthemostfoolproofmethod,sensitivedatawillnotbekeptonthedevicebutontheserverinstead,anditwillusetokenstogiveaccess.Ifyouhavetostorethedataonthedevice’sdatabase,useencryption.Useauser-definedkeytoencryptanddecryptsensitivedata.
Wewillexploreawaytouseanencrypteddatabase,whichwillnotbereadableifsomeoneisabletoextractitviameansofarootorviaexploitingbackups.IfsomeonetriestoreaditusingSQLiteManagerorsomeothertool,theywillreceiveafriendlymessage,suchastheoneshowninthefollowingscreenshot;thisisthedatabasefilethatwewillcreateinamomentwithalibraryknownasSQLCipher.
SQLCipherisanopensourceextensiontoSQLitethatprovidesatransparent256-bitAESencryptionofdatabasefiles,asmentionedontheirwebsite.ItisveryeasytodeploySQLCipher.Nowwe’lllookatthestepstobuildasampleapplication:
1. First,wewilldownloadthenecessaryfilesfromhttp://sqlcipher.net/open-source.Here,theyhavelistedacommunityeditionoftheAndroid-basedSQLCipher;downloadit.
2. NowwewillcreateanewAndroidprojectinoureclipseenvironment.3. Insidethedownloadedfolder,wewillfindthelibsfolder;insideit,areasetofjars
thatwewillneedtoworkwithSQLCipher.Wewillalsonoticethatfoldersarenamedasarmeabi,armeabi-v7a,andx86,andallofthesecontainthe.sofiles.IfyouarefamiliarwithAndroidNDK,thiswillnotseemnew.The.sofileisasharedobjectfile,whichisacomponentofdynamiclibraries.Fordifferentarchitectures,werequiredifferent.sofiles,hencethethreefolders.Ifyouarerunninganx86emulator,youwillneedthex86folderinyourlibsfolder.Forsimplicity,wewillcopyallthefolderstothelibsfolder.Copytheassetfolder’scontentintoourproject’sassetfolderandnavigatetotheproject’sproperties.Itwilllooksomethinglikethefollowingscreenshot.YoucanalsoseetheseJARfilesintheproject’sclasspath.Theinitialsetupforthisprojectisnowcomplete.
![Page 145: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/145.jpg)
Aftercompletingthenecessarysetuppart,let’smovetowritingcodetomakeasmalltestapplication:
publicclassMainActivityextendsActivity
{
TextViewshowResult;
@Override
protectedvoidonCreate(BundlesavedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
showResult=(TextView)findViewById(R.id.showResult);
InitializeSQLCipher();
}
privatevoidInitializeSQLCipher()
{
SQLiteDatabase.loadLibs(this);
FiledatabaseFile=getDatabasePath("test.db");
databaseFile.mkdirs();
databaseFile.delete();
SQLiteDatabasedatabase=SQLiteDatabase
.openOrCreateDatabase(databaseFile,"test123",null);
database.execSQL("createtablet1(a,b)");
database.execSQL("insertintot1(a,b)values(?,?)",
newObject[]{"Iam","Encrypted"});
}
publicvoidrunQuery(Viewv)
{
FiledatabaseFile=getDatabasePath("test.db");
SQLiteDatabasedatabase=SQLiteDatabase.openOrCreateDatabase(
databaseFile,"test123",null);
Stringselection="select*fromt1";
Cursorc=database.rawQuery(selection,null);
c.moveToFirst();
showResult.setText(c.getString(c.getColumnIndex("a"))+
![Page 146: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/146.jpg)
c.getString(c.getColumnIndex("b")));
}
}
Theprecedingcodehastwomainmethods:InitializeSQLCipher()andrunQuery().InsideInitializeSQLCipher(),weloadour.solibraryfilesbyinvokingtheloadLibs()method.
4. Nowwefindtheabsolutepathtothedatabaseandcreateamissingparentfolderifany.WithopenOrCreateDatabase(),wewillmakeacalltoopenanexistingdatabaseorcreateoneifthedatabaseisnonexistent.Wewillexecutestandarddatabasecallstocreateatablewithcolumnsaandbandinsertvaluesinarow.
NowwewillperformasimplequerytofetchthevaluesbacktotherunQuery()method.Youwillnoticethatapartfromloadingthelibrary,allthecoremethodsweusedareprettymuchstandard,sowhereisthemajorchange?GototheCh4-PersonalContactManagerexampleinthecodebundleandnoticethepackageswehaveused:
importandroid.database.Cursor;
importandroid.database.sqlite.SQLiteDatabase;
WehaveSQLCipherpackages:
importnet.sqlcipher.Cursor;
importnet.sqlcipher.database.SQLiteDatabase;
Theimplementationissimple,familiar,andeasytoimplement.Ifyoupullthedatabaseoutandtrytoreadit,youwillfindtheerrormessage,aswedisplayedearlierinascreenshot.Theuserwillfindnochange,andevenourapp’slogicremainsthesame.Inthescreenshot,youcanseetheapplicationscreenwejustbuiltwhichencryptsthedatabase:
![Page 147: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/147.jpg)
NoteOAuthisanopenstandardforauthorization.Itprovidesclientapplicationswithasecuredelegatedaccesstoserverresourcesonbehalfofaresourceowner.Itspecifiesaprocessforresourceownerstoauthorizethird-partyaccesstotheirserverresourceswithoutsharingtheircredentials,asexplainedinWikipedia;readmoreaboutOAuthathttp://oauth.net/2/.
![Page 148: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/148.jpg)
![Page 149: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/149.jpg)
GeneraltipsandlibrariesWewillcoversomegeneralandnotsogeneralworkaroundsandpractices,whichcanbeputtogoodusedependingonthesituation.Forinstance,insomecases,weneedtohaveaprepopulateddatabaseofvaluesthatwewillmakeuseofinourAndroidapplicationorupgradingadatabase,whichseemstrivialbutcanbreakourapplication.
![Page 150: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/150.jpg)
UpgradingadatabaseInChapter2,ConnectingtheDots,weusedonUpgrade()toshowhowadatabaseisupdated.Ifwegobacktotheexample,youwillnoticethatitexecutesaDropTablecommand.Whatwillhappenhereisthattheoriginaltablewillbedroppedandanewtablewillbecreatedbythecall,onCreate().Thiswillleadtoalossoftheexistingdataandhenceisnotsuitableifweneedtoalterourdatabase.TheonUpgrade()functioncanbedefinedasfollows:
publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion)
{
StringDROP_TABLE="DROPTABLEIFEXISTS"+TABLE_NAME;
db.execSQL(DROP_TABLE);
onCreate(db);
}
Onemorechallengeistoidentifytheversionweareusinghere.Theusermightberunningolderversionsoftheapplication,sowehavetokeepinmindthedifferentversionsthatanapplicationhasandwhetherthoseversionswouldbringaboutanychangesinthedatabase.Foranewuser,weneednotworrybecauseifthedatabasedoesnotexist,onCreate()willbecalled.
Tomakesurewehaveaproperupgrade,wewillusetheDB_VERSIONconstantinourCustomSQLiteOpenHelperclasstotellouronUpgrade()methodabouttheactiontobetaken:
privatestaticfinalintDB_VERSION=1;
WewillchangetheDB_VERSIONconstantto3toreflecttheupgrade:
privatestaticfinalintDB_VERSION=3;
Theconstructorwilltakecareoftherest:
publicCustomSQLiteOpenHelper(Contextcontext)
{
super(context,DB_NAME,null,DB_VERSION);
}
Whenthesuperclassconstructorisrun,itcomparestheDB_VERSIONconstantofthestoredSQLite.dbfileagainsttheDB_VERSIONwepassedasaparameterandcallstheonUpgrade()methodifneeded:
publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion)
{
switch(oldVersion){
case1:db.execSQL(DATABASE_CREATE_MAIN_TABLE);
case2:db.execSQL(DATABASE_CREATE_MAIN_TABLE);
case3:db.execSQL(DATABASE_CREATE_DEL_TABLE);
}
}
![Page 151: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/151.jpg)
InsideouronUpgrade()method,wehaveaswitchcasetomakechanges.Noticethatwedonotusethebreakstatementbecausetheusercanbeonanolderversionandmaynothaveupdatedtheapplication,asexplainedearlier.Forinstance,let’sconsiderthatauserisonaparticularversionofanapplicationthatisrunningDB_VERSION=1andheorsheskipsthenextupdatethatcontainedDB_VERSION=2,andeventually,anewversionoftheapplicationwithDB_VERSION=3isreleased.Now,wehaveacasewheretheuserisstillusinganolderversionoftheapplicationandhasnotinstalledthenewupdateswehavereleased.So,inthiscase,whentheuserinstallstheapplication,theonUpgrade()methodwillfirstexecutecase1andthengotocase2toinstallupdatesthattheusermissed;finally,theuserwillinstalltheupdatesofthethirdversion,ensuringthatallthedatabasechangesarereflected.Noticethatthereisnobreakstatement.Thisisbecausewewanttorunallthecaseswheretheswitchstatementobtainsthevalue1andthelasttwostatementswheretheswitchcaseobtainsthevalue2.
Alternatively,wecanalsousetheifstatement.ThiswillalsobehaveasweintendedasourtestDB_VERSIONconstantwas1,whichwillsatisfyboththeconditionsandreflectthechanges:
if(oldVersion<2){db.execSQL(DATABASE_CREATE_MORE_TABLE);}
if(oldVersion<3){db.execSQL(DATABASE_CREATE_DEL_TABLE);}
![Page 152: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/152.jpg)
DatabaseminusSQLstatementsInmostpartsofthebook,welookedaroundfornooksandcornersofAndroidandSQLite.Forsome,writingSQLstatementswouldbejustanotherdayintheoffice,whileforsome,itwillcomeacrossasaroller-coasterride.ThissectionwillcoveralibrarythatenablesustosaveandretrieveSQLitedatabaserecordswithoutwritingasingleSQLstatement.ActiveAndroidisanactiverecord-styleSQLitepersistenceforAndroid.Accordingtothedocumentation,eachdatabaserecordiswrappedneatlyintoaclasswithmethodssuchassave()anddelete().WewillbeusingtheexampleintheActiveAndroiddocumentationandbuildaworkingsamplebasedonit.Let’slookatthestepsrequiredtogetitupandrunning.
Havealookattheofficialsite,http://www.activeandroid.com/,foranoverviewanddownloadthefilesfromhttp://goo.gl/oW2kod.
Onceyoudownloadthefile,runantontherootfoldertobuildtheJARfile.Onceyourunant,youwillfindyourJARfileinthedistfolder.InEclipse,makeanewproject,addtheJARfiletothelibsfolderoftheproject,andthenaddtheJARfiletotheJavaBuildPathintheprojectproperties.
ActiveAndroidlooksoutforsomeglobalsettingsconfiguredbyperformingthefollowingsteps:
1. Wewillstartbycreatingaclass,extendingtheapplicationclass:
publicclassMyApplicationextendscom.activeandroid.app.Application
{
@Override
publicvoidonCreate()
{
super.onCreate();
ActiveAndroid.initialize(this);
}
@Override
publicvoidonTerminate()
{
super.onTerminate();
ActiveAndroid.dispose();
}
}
2. Nowwewilladdthisapplicationclasstoourmanifestfileandaddmetadatacorrespondingtoourapplication:
<application
android:name="com.active.android.MyApplication">
<meta-data
android:name="AA_DB_NAME"
android:value="test.db"/>
<meta-data
android:name="AA_DB_VERSION"
![Page 153: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/153.jpg)
android:value="1"/>
………..
</application>
3. Withthisbasicsetupcomplete,wewillnowproceedontocreatingourdatamodel.TheActiveAndroidlibrarysupportsannotationandwewilluseitinthefollowingmodelclasses:
//Categoryclass
@Table(name="Categories")
publicclassCategoryextendsModel
{
@Column(name="Name")
publicStringname;
}
//Itemclass
@Table(name="Items")
publicclassItemextendsModel
{
//Ifnameisomitted,thenthefieldnameisused.
@Column(name="Name")
publicStringname;
@Column(name="Category")
publicCategorycategory;
publicItem()
{
super();
}
publicItem(Stringname,Categorycategory)
{
super();
this.name=name;
this.category=category;
}
}
NoteIfyouwanttoexploreannotationsandusetheminyourprojectandreduceboilerplatecode,youcancheckoutthefollowinglibrariesforAndroid:AndroidAnnotations,Square’sDagger,andButterKnife.
4. Toaddanewcategoryoritem,weneedtomakeacalltosave().Inthecodesegment,wecanseethatanitemobjectiscreatedandassociatedwithaparticularcategory,andintheend,save()iscalled:
publicvoidinsert(Viewv)
{
![Page 154: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/154.jpg)
ItemtestItem=newItem();
testItem.category=testCategory;
testItem.name=editTextItem.getText().toString();
testItem.save();
}
Todeleteanitem,wecancallitem.delete().Similarly,tofetchvalues,wehaverelevantmethodsaswell.Thefollowingisacalltofetchallofthedataforaparticularcategory:
List<Item>getall=newSelect().from(Item.class)
.where("Category=?",testCategory.getId())
.orderBy("NameASC").execute();
ThereislotmoretobeexploredinActiveAndroid.Theyhaveschemamigrationandtypeserialization;inadditiontothis,youcanshipaprepopulateddatabasebyplacingthedatabaseintheassetfolder,andyoucanusecontentprovidersaswell.Inshort,itisawell-builtlibraryforpeoplelookingforindirectwaystocommunicatewiththedatabaseandperformdatabaseoperations.IthelpsinaccessingthedatabaseinthefamiliarformofJavamethodsinsteadofpreparingSQLstatementstoperformthesameaction.Thecompletesamplecodeisbundledinthechapter4codebundle.
![Page 155: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/155.jpg)
ShippingwithaprepopulateddatabaseWewillbuildadatabaseandputitinsideourassetfolder,whichisaread-onlydirectory.Atruntime,wewillcheckwhetheradatabaseexists.Ifnot,wewillcopyourdatabasefromtheassetfolderto/data/data/yourpackage/databases.InChapter2,ConnectingtheDots,weusedatoolcalledSQLiteManager;havealookatthethirdscreenshotofthechapter.Wearegoingtousethesametooltobuildourdatabasenow.Ifyoupullyourdatabaseasexplainedinthatsectionorlookatthatscreenshot,youwillnoticeafewmoretablesalongwithyourdatabasetable:
Thestepstobefollowedtocreateaprepopulateddatabaseareasfollows:
1. Tomakeaprepopulateddatabase,weneedtocreateatablenamedandroid_metadataapartfromthetablewerequire.UsingtheSQLiteManagertool,wewillcreateanewdatabasenamedcontact,thenwewillcreatetheandroid_metdatatable:
CREATETABLE"android_metadata"("locale"TEXTDEFAULT'en_US')
2. Wewillinsertarowinthetable:
INSERTINTO"android_metadata"VALUES('en_US')
3. Nowwewillcreatethetableswerequire,inourcase,contact_tableusingtheSQLqueryweusedinChapter2,ConnectingtheDots.IntheDatabaseManagerclass,wewilljustreplacetheconstantswiththeactualvalues:
CREATETABLE"contact_table"("_id"integerprimarykeyautoincrement
notnull,"contact_name"textnotnull,"contact_number"textnot
null,"contact_email"textnotnull,"photo_id"BLOB)
ItisnecessarytorenametheprimaryIDfieldofourtablesto_idifitisnotalreadydefined.ThishelpsAndroidinidentifyingwheretobindtheIDfieldofourtables.
4. Letusfillafewrowsofdata.WecandothisbyrunningtheInsertqueryormanuallytypinginthevaluesusingthetool.Now,copythedatabasefileintotheassetfolder.
5. Now,inouroriginalpersonalcontactmanager,wewillmodifyourDatabaseManagerclass.Thegoodpartisthatthisistheonlyclassweneedtomodifyandtherestofthesystemwillworkasintended.
6. WhentheapplicationrunsandcreatesanewDatabaseManagerclassbypassingthecontext,wewillmakeacalltocreateDatabase()inwhichfirstofallwewillcheckwhetherthedatabasealreadyexists:
![Page 156: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/156.jpg)
PrivateBooleancheckDataBase()
{
SQLiteDatabasecheckDB=null;
try{
StringmyPath=DB_PATH+DB_NAME;
checkDB=SQLiteDatabase.openDatabase(myPath,null,
SQLiteDatabase.OPEN_READONLY);
}catch(SQLiteExceptione){
//databasedoesn'texistyet.
}
if(checkDB!=null){
checkDB.close();
}
returncheckDB!=null?true:false;
}
7. Ifitdoesn’t,wewillcreateanemptydatabasethatwewillreplacewithourdatabase,whichwecopiedintoourassetfolder.Aftercopyingthedatabasefromtheassetfolder,wewillcreateanewSQLiteDatabaseobject:
privatevoidcopyDataBase()throwsIOException
{
InputStreammyInput=myContext.getAssets().open(DB_NAME);
StringoutFileName=DB_PATH+DB_NAME;
OutputStreammyOutput=newFileOutputStream(outFileName);
byte[]buffer=newbyte[1024];
intlength;
while((length=myInput.read(buffer))>0){
myOutput.write(buffer,0,length);
}
myOutput.flush();
myOutput.close();
myInput.close();
}
AnotherpointtonoteisthattheonCreate()methodofourCustomSQLiteOpenHelperclasswillbeemptyaswearenotcreatingadatabaseandtables,butwearecopyingone.Thesamplecodeisbundledinthechapter4codebundle.Ifthisprocesslookstedious,don’tworry;theAndroiddevelopers’communityhasasolutionforyou.SQLiteAssetHelperisanAndroidlibrarythatwillhelpyouinmanagingdatabasecreationandversionmanagement,usinganapplication’srawassetfiles.
Toimplementthis,wehavetofollowafewsimplesteps:
1. CopytheJARfileintoourproject’slibsfolder.2. AddalibrarytoJavaBuildPath.3. Copyourzippeddatabasefileintotheassetfolderof
projectassets/databases/your_database.db.zip.4. TheZIPfileshouldcontainonlyonedbfile.5. Insteadofextendingtheframework’sSQLiteOpenHelperclass,wewillextendthe
SQLiteAssetHelperclass.
![Page 157: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/157.jpg)
6. Theyalsoprovideyouwithassistancetoupgradethedatabasefile,whichneedstobeplacedinassets/databases/<database_name>_upgrade_<from_version>-<to_version>.sql.
7. Thelibrary,documentation,anditscorrespondingsamplecanbefoundathttp://goo.gl/8XSSmR.
![Page 158: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/158.jpg)
![Page 159: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/159.jpg)
SummaryWecoveredamyriadofadvancedtopicsinthischapter,rangingfromloaderstothesecurityofdata.Weimplementedourcursorloadertounderstandhowaloaderworksmagicforourapplications,andwedelvedintosecuringourdatabaseandunderstandingtheconceptofpermissionswhileexposingourcontentprovidertootherapplications.Wealsocoveredsometipssuchasshippingwithaprepopulateddatabase,upgradingadatabasewithoutbreakingthesystem,andusingdatabasequerieswithoutusingSQLcommands.ThisisinnowaytheonlysetofthingswecanachievewithdatabaseandAndroid.Thischapteronlyservesasanudgetowardsthevastprogrammingpossibilitiesoutthere.
![Page 160: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/160.jpg)
IndexA
Activeandroidabout/DatabaseminusSQLstatementsURL/DatabaseminusSQLstatementsglobalsettings,configuring/DatabaseminusSQLstatements
addRow()method/BuildingtheInsertqueryaddURI()method
about/CreatingUriMatcherdefinitionsaffinity/BuildingblocksAheadofTime(AOT)
about/SQLiteinAndroidAndroid
storage/SQLiteinAndroidandroid.database.SQLitepackage
about/DatabasepackagesAndroiddeveloperwebsite
URL/LoadersAPIs
about/APIsApplicationA
about/Whatisacontentprovider?ApplicationB
about/Whatisacontentprovider?applicationnotresponding(ANR)/Loadersarchitecture,SQLite
interface/TheSQLiteinterfaceSQLcompiler/TheSQLcompilervirtualmachine/Thevirtualmachinebackend/TheSQLitebackend
ARTabout/SQLiteinAndroid
AUTOINCREMENTkeyword/Buildingblocks
![Page 161: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/161.jpg)
BB-trees
about/TheSQLitebackendbackend,SQLite
about/TheSQLitebackendB-trees/TheSQLitebackendPager/TheSQLitebackendOSInterface/TheSQLitebackend
BLOBclassabout/Storageclasses
Booleandatatypeabout/TheBooleandatatype
branchtestcoveragereferencelink/WhySQLite?
buildingblocks,Android/Buildingblocks
![Page 162: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/162.jpg)
Ccase-insensitive
about/TheSQLitesyntaxclose()method
about/TheSQLiteOpenHelperclasscolumnconstraint
about/BuildingblocksURL/Buildingblocks
columnconstraint,SQLiteNOTNULLconstraint/BuildingblocksDEFAULTconstraint/BuildingblocksUNIQUEconstraint/BuildingblocksPRIMARYkey/BuildingblocksCHECKconstraint/BuildingblocksAUTOINCREMENTkeyword/Buildingblocks
constraintabout/WhatisanSQLitestatement?
content$//URIabout/UnderstandingcontentURIs
contentproviderabout/Whatisacontentprovider?using/Usingexistingcontentproviders,UsingacontentproviderContentResolverobject/Whatisacontentresolver?creating/CreatingacontentprovidercontentURI/UnderstandingcontentURIscontractclass,declaring/DeclaringourcontractclassURIMatcher,creating/CreatingUriMatcherdefinitionsinitializing,onCreate()methodused/InitializingtheproviderthroughtheonCreate()methodadding,tomanifest/Addingaprovidertoamanifest
/ContentProviderandpermissionsContentResolverobject
about/Whatisacontentresolver?contentURI
about/UnderstandingcontentURIsContentValues
about/ContentValuescontext
about/TheSQLiteOpenHelperclasscontractclass
declaring/Declaringourcontractclasscreatequery
building/BuildingtheCreatequery
![Page 163: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/163.jpg)
CREATETABLEcommandabout/WhatisanSQLitestatement?attributes/WhatisanSQLitestatement?
criticaldata,datasecurityencrypting/Encryptingcriticaldata
CursorLoaderused,forloadingdata/LoadingdatawithCursorLoaderusing/UsingCursorLoaderstartedstate/UsingCursorLoaderstoppedstate/UsingCursorLoaderresetstate/UsingCursorLoaderimplementing/UsingCursorLoader
Cursorobjectabout/Cursor
Cursorquery(Uriuri,String[]projection,Stringselection,String[]selectionArgs,StringsortOrder)method/Creatingacontentprovider
![Page 164: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/164.jpg)
DDalvikvirtualmachine(DVM)
about/SQLiteinAndroiddata
loading,withCursorLoader/LoadingdatawithCursorLoaderdata,loading
CursorLoader,using/UsingCursorLoaderdata,loadingwithCursorLoader
loaders,using/LoadersloaderAPI/LoaderAPI’ssummary
databaseabout/AquickreviewofdatabasefundamentalsSQLitestatement/WhatisanSQLitestatement?SQLitesyntax/TheSQLitesyntaxUI,connectingwith/ConnectingtheUIanddatabaseupgrading/Upgradingadatabaseprepopulateddatabase,creating/Shippingwithaprepopulateddatabase
databasehandler/Adatabasehandlerandqueriesdatabasepackages
about/DatabasepackagesAPIs/APIsSQLiteOpenHelperclass/TheSQLiteOpenHelperclassSQLiteDatabaseclass/TheSQLiteDatabaseclassContentValues/ContentValuesCursorobject/Cursor
datasecurityabout/Datasecuritycontentprovider/ContentProviderandpermissionspermissions/ContentProviderandpermissionscriticaldata,encrypting/Encryptingcriticaldata
datatypes,SQLiteabout/DatatypesinSQLitestorageclasses/StorageclassesBooleandatatype/TheBooleandatatypeDatedatatype/TheDateandTimedatatypeTimedatatype/TheDateandTimedatatype
Datedatatypeabout/TheDateandTimedatatype
DEFAULTconstraint/Buildingblocksdelete()method/TheSQLiteDatabaseclass
used,fordeletingrecords/Deletingrecordsthroughthedelete()methoddelete()method,SQLiteDatabase/BuildingtheDeletequeryDELETEcommand
![Page 165: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/165.jpg)
about/Aquickreviewofdatabasefundamentalsdeletequery
building/BuildingtheDeletequerydeleteRow()method/ConnectingtheUIanddatabasedelRowmethod/ConnectingtheUIanddatabasedynamictyping/Buildingblocks
![Page 166: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/166.jpg)
EEclipse
emulator,settingup/Buildingblocksemulator
about/Buildingblocksemulator,Eclipse
settingup,steps/Buildingblocksexternalstorage
about/SQLiteinAndroid
![Page 167: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/167.jpg)
Ffeatures,SQLite
zero-configuration/WhySQLite?no-copyright/WhySQLite?cross-platform/WhySQLite?compact/WhySQLite?foolproof/WhySQLite?
![Page 168: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/168.jpg)
GGenymotion
URL/Buildingblocksget*()methods
about/CursorgetBlob()method/ConnectingtheUIanddatabasegetCount()method
about/CursorgetRandomName()method/UsingacontentprovidergetRandomNumber()method/UsingacontentprovidergetReadableDatabase()method
about/TheSQLiteOpenHelperclassgetType()method
used,forgettingreturntypeofcontent/GettingthereturntypeofdatathroughthegetType()method
getView()method/ConnectingtheUIanddatabasegetWriteableDatabase()method
about/TheSQLiteOpenHelperclass
![Page 169: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/169.jpg)
IIllegalArgumentException/Queryingrecordsthroughthequery()methodinsert()method
used,foraddingrecords/Addingrecordsthroughtheinsert()methodurlparameter/Usingacontentprovidervaluesparameter/Usingacontentprovider
INSERTcommandabout/Aquickreviewofdatabasefundamentals
insertquerybuilding/BuildingtheInsertquery
intdelete(Uriuri,Stringselection,String[]selectionArgs)method/CreatingacontentproviderINTEGERclass
about/Storageclassesinterface,SQLite
about/TheSQLiteinterfaceinternalstorage
about/SQLiteinAndroidintupdate(Uriuri,ContentValuesvalues,Stringselection,String[]selectionArgs)method/CreatingacontentproviderisAfterLast()method
about/CursorisReadOnly()method
about/TheSQLiteOpenHelperclass
![Page 170: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/170.jpg)
JJustinTime(JIT)
about/SQLiteinAndroid
![Page 171: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/171.jpg)
LLemonparsergenerator
URL/TheSQLcompilerLoaderAPI
classes/interfaces/LoaderAPI’ssummaryloaders
about/LoadersloadInBackgroundmethod/UsingCursorLoader
![Page 172: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/172.jpg)
MmoveToFirst()method
about/CursormoveToNext()method
about/Cursor
![Page 173: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/173.jpg)
NNOTNULLconstraint/BuildingblocksNULLclass
about/StorageclassesNullPointerException/Queryingrecordsthroughthequery()method
![Page 174: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/174.jpg)
OOAuth
URL/EncryptingcriticaldataonContextItemSelected()method/ConnectingtheUIanddatabaseonCreate()method/BuildingtheCreatequery
about/TheSQLiteOpenHelperclassused,forinitializingcontentprovider/InitializingtheproviderthroughtheonCreate()method
onOpen()methodabout/TheSQLiteOpenHelperclass
onUpgrade()methodabout/TheSQLiteOpenHelperclass
/BuildingtheCreatequeryOSInterface
about/TheSQLitebackend
![Page 175: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/175.jpg)
PPager
about/TheSQLitebackendpath
about/UnderstandingcontentURIspermissions/Addingaprovidertoamanifest,ContentProviderandpermissionsprepareData()method/BuildingtheInsertqueryprepareSendData()method/ConnectingtheUIanddatabaseprepopulateddatabase
shipping/Shippingwithaprepopulateddatabasecreating/Shippingwithaprepopulateddatabase
PRIMARYkey/Buildingblocks
![Page 176: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/176.jpg)
Qquery
about/Aquickreviewofdatabasefundamentals,Adatabasehandlerandqueriescreatequery,building/BuildingtheCreatequeryinsertquery,building/BuildingtheInsertquerydeletequery,building/BuildingtheDeletequeryupdatequery,building/BuildingtheUpdatequery
query()methodused,forqueryingrecords/Queryingrecordsthroughthequery()methoduriparameter/Usingacontentproviderprojectionparameter/Usingacontentproviderselectionparameter/UsingacontentproviderselectionArgsparameter/UsingacontentprovidersortOrderparameter/Usingacontentprovider
![Page 177: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/177.jpg)
RREALclass
about/Storageclassesresetstate,CursorLoader/UsingCursorLoader
![Page 178: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/178.jpg)
SSELECTcommand
about/Aquickreviewofdatabasefundamentalssharedpreference
about/SQLiteinAndroidSQLCipher
about/EncryptingcriticaldataURL/Encryptingcriticaldatasampleapplication,steps/Encryptingcriticaldata
SQLcompilerabout/TheSQLcompiler
SQLiteabout/WhySQLite?using/WhySQLite?features/WhySQLite?architecture/TheSQLitearchitecturedatatypes/DatatypesinSQLite
SQLite3about/SQLiteversion
SQLite3command.dump/SQLiteversion.schema/SQLiteversion.help/SQLiteversion
SQLiteDatabase()querymethod/BuildingtheInsertquerySQLiteDatabaseclass
about/TheSQLiteDatabaseclassURL,fordocumentation/TheSQLiteDatabaseclass
SQLiteinAndroidabout/SQLiteinAndroidversion/SQLiteversiondatabasepackages/Databasepackages
SQLiteManagertoolURL/BuildingtheCreatequery
SQLiteOpenHelperclassabout/TheSQLiteOpenHelperclass
SQLitestatementabout/WhatisanSQLitestatement?INSERT/WhatisanSQLitestatement?SELECT/WhatisanSQLitestatement?UPDATE/WhatisanSQLitestatement?DELETE/WhatisanSQLitestatement?ALTER/WhatisanSQLitestatement?DROP/WhatisanSQLitestatement?
![Page 179: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/179.jpg)
SQLstatementstips/DatabaseminusSQLstatements
startedstate,CursorLoader/UsingCursorLoaderstoppedstate,CursorLoader/UsingCursorLoaderstorage,Android
sharedpreference/SQLiteinAndroidexternalstorage/SQLiteinAndroidinternalstorage/SQLiteinAndroid
storageclassesabout/StorageclassesNULL/StorageclassesINTEGER/StorageclassesREAL/StorageclassesTEXT/StorageclassesBLOB/Storageclasses
StringgetType(Uri)method/Creatingacontentprovidersynchronizedkeyword
about/TheSQLiteOpenHelperclasssyntax,SQLite
about/TheSQLitesyntax
![Page 180: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/180.jpg)
TTEXTclass
about/StorageclassesTextUtils.isEmpty()method/ConnectingtheUIanddatabaseTimedatatype
about/TheDateandTimedatatypetips,prepopulateddatabase/Generaltipsandlibraries
![Page 181: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/181.jpg)
UUI
connecting,withdatabase/ConnectingtheUIanddatabaseUNIQUEconstraint/Buildingblocksupdate()method/TheSQLiteDatabaseclass
used,forupdatingrecords/Updatingrecordsthroughtheupdate()methoduriparameter/Usingacontentprovidervaluesparameter/UsingacontentproviderWHEREclause/Usingacontentprovider
update()method,SQLiteDatabase/BuildingtheUpdatequeryUPDATEcommand
about/Aquickreviewofdatabasefundamentalsupdatequery
building/BuildingtheUpdatequeryURI
about/UnderstandingcontentURIsUriinsert(Uriuri,ContentValuesvalues)method/Creatingacontentprovider
![Page 182: Android SQLite Essentials - EY.MD Related/PDFs and... · Simran Bhogal Joanna McMahon Indexers Mariammal Chettiyar Rekha Nair Graphics Ronak Dhruv Production Coordinator. Saiprasad](https://reader033.vdocuments.us/reader033/viewer/2022041917/5e6a0aeaf55bf84307565fb4/html5/thumbnails/182.jpg)
VVDBE
about/Thevirtualmachineversion,SQLite
about/SQLiteversionvirtualmachine
about/ThevirtualmachinevoidonCreate()method/Creatingacontentprovider