1
00:00:00,000 --> 00:00:04,460
[MUSIC]

2
00:00:04,460 --> 00:00:05,140
Hi.

3
00:00:05,140 --> 00:00:08,330
My name is Ben Jakuben, and I'm the
Android teacher here at Treehouse.

4
00:00:08,330 --> 00:00:09,840
Thanks for joining us today.

5
00:00:09,840 --> 00:00:11,680
Before we get started I just want to say,
if you

6
00:00:11,680 --> 00:00:16,180
have any questions during the presentation
please type them below the video.

7
00:00:16,180 --> 00:00:17,860
And we'll try and get to them at the,

8
00:00:17,860 --> 00:00:20,620
at the end or if it's especially important
then

9
00:00:20,620 --> 00:00:22,010
my friend Chris will flag me down and
we'll,

10
00:00:22,010 --> 00:00:24,500
we'll try and answer it while we're doing
the presentation.

11
00:00:24,500 --> 00:00:25,730
If we don't get to your question, or if

12
00:00:25,730 --> 00:00:27,710
you're watching this later and you think
of something,

13
00:00:27,710 --> 00:00:31,270
then post it in our Treehouse Forum and
somebody

14
00:00:31,270 --> 00:00:33,300
will try and answer it for you, myself
included.

15
00:00:33,300 --> 00:00:35,290
Or you can find me on Twitter @benjacobin.

16
00:00:35,290 --> 00:00:41,430
All right, so we're going to cover the
basics of SQLite for Android.

17
00:00:41,430 --> 00:00:45,340
And the first question we wanna ask
ourselves is what exactly is SQLite?

18
00:00:45,340 --> 00:00:49,680
It is a light waist, lightweight SQL-based
database for storing data in an app.

19
00:00:49,680 --> 00:00:52,720
It's available in all versions of Android
and a good example to think

20
00:00:52,720 --> 00:00:54,670
of is the contacts app, where you

21
00:00:54,670 --> 00:00:57,140
have information about all the different
contacts.

22
00:00:57,140 --> 00:00:59,220
If you think of the database that sits
behind

23
00:00:59,220 --> 00:01:02,980
it you've got people's names and phone
numbers, pictures

24
00:01:02,980 --> 00:01:05,320
maybe, and that's all stored in a way that

25
00:01:05,320 --> 00:01:07,930
you can access it from that app at any
time.

26
00:01:07,930 --> 00:01:12,510
If it were just well, let me go ahead to
the next slide and explain.

27
00:01:12,510 --> 00:01:14,370
Why exactly do we wanna use this?

28
00:01:14,370 --> 00:01:16,140
It's so that we can store and retrieve
data within the

29
00:01:16,140 --> 00:01:19,350
app across sessions, that's the big thing,
this is about data persistence.

30
00:01:19,350 --> 00:01:23,330
When we have data in an app, if we're
running it you know, in an activity

31
00:01:23,330 --> 00:01:26,910
and if the app is visible on the screen,
then that data in memory is available.

32
00:01:26,910 --> 00:01:30,360
But, as soon as we close the app, that
data in memory goes away, so

33
00:01:30,360 --> 00:01:34,660
we need to store that data in memory on
the phone or on the tablet somehow.

34
00:01:34,660 --> 00:01:37,600
So that's why solutions like SQLite
databases

35
00:01:37,600 --> 00:01:39,950
are used to persist that data across
sessions.

36
00:01:41,650 --> 00:01:43,252
The reason we're looking at SQLite

37
00:01:43,252 --> 00:01:45,550
specifically is, very popular with Android
developers.

38
00:01:45,550 --> 00:01:49,100
A lot of software developers are familiar
with SQL databases

39
00:01:49,100 --> 00:01:51,690
and using the SQL language to access and
store data.

40
00:01:53,810 --> 00:01:57,140
You might be wondering if it's secure,
well, it is in

41
00:01:57,140 --> 00:02:01,065
that the databases exist only within the
context of the app.

42
00:02:01,065 --> 00:02:05,370
Android has this concept of sandboxing
apps, where apps and all

43
00:02:05,370 --> 00:02:10,290
of their related files, databases, et
cetera are all in a restricted

44
00:02:10,290 --> 00:02:14,080
section of the file system and no other
apps or anything

45
00:02:14,080 --> 00:02:17,950
else can access that part of the file
system without explicit permission.

46
00:02:17,950 --> 00:02:21,840
So if you wanna give access to this data
to outside apps you have to

47
00:02:21,840 --> 00:02:25,510
explicitly allow it using a content
provider which

48
00:02:25,510 --> 00:02:27,750
we're gonna talk about briefly in a
moment.

49
00:02:27,750 --> 00:02:31,230
The other thing is when you uninstall an
app, it does usually delete

50
00:02:31,230 --> 00:02:35,810
the data as far as I know from, I forget
which version of Android,

51
00:02:35,810 --> 00:02:40,670
there may have been some inconsistencies
earlier on, but when you delete an

52
00:02:40,670 --> 00:02:42,310
app it, corresponding database, and all
the

53
00:02:42,310 --> 00:02:44,110
files in that sandbox should be deleted.

54
00:02:46,060 --> 00:02:47,970
We're gonna keep things pretty basic
today.

55
00:02:47,970 --> 00:02:51,350
We are going to talk about the C.R.U.D.

56
00:02:51,350 --> 00:02:54,410
operation, so if you are familiar with web
development

57
00:02:54,410 --> 00:02:57,210
or other, you know, working with data,
then these

58
00:02:57,210 --> 00:02:59,270
are the basic operations you can perform
on data.

59
00:02:59,270 --> 00:03:04,100
The acronym C.R.U.D. stands for create,
retrieve, update, and delete.

60
00:03:04,100 --> 00:03:13,750
Now in the syntax of SQL that is instead
insert, select, update and delete.

61
00:03:13,750 --> 00:03:16,090
And I should mention about SQL
specifically, SQL

62
00:03:16,090 --> 00:03:19,670
stands for, SQL stands for a Structured
Query Language.

63
00:03:19,670 --> 00:03:22,640
Now if you're not familiar with SQL then
we

64
00:03:22,640 --> 00:03:25,830
have a fantastic course by Andrew Chalkley
called database basics,

65
00:03:25,830 --> 00:03:29,000
and it will get you comfortable with
creating databases, manipulating

66
00:03:29,000 --> 00:03:32,910
the data, and it's all based on the SQL
language.

67
00:03:32,910 --> 00:03:38,170
We're gonna work with very basic data
we're going to get some forecast data

68
00:03:38,170 --> 00:03:41,640
from the web and we're going to store it
in a table, and all

69
00:03:41,640 --> 00:03:45,870
we care about right now, just to show how
to use these concepts in

70
00:03:45,870 --> 00:03:47,490
Android, we're gonna do one column of

71
00:03:47,490 --> 00:03:50,040
data, which is numbers that correspond to
temperatures.

72
00:03:51,490 --> 00:03:54,890
We are also going to talk about upgrading
databases.

73
00:03:54,890 --> 00:03:59,600
So, that is when you're, let's say in our
app we are tracking information about

74
00:03:59,600 --> 00:04:02,900
a user and we have a user name, and an
email address, and a picture.

75
00:04:02,900 --> 00:04:04,230
Well, we released a new version of our

76
00:04:04,230 --> 00:04:06,210
app and now we wanna track additional
data, too.

77
00:04:06,210 --> 00:04:10,980
Let's say we wanna store their favorite
video game for some reason.

78
00:04:10,980 --> 00:04:14,320
Well, we need to amend the data that's
currently in our system.

79
00:04:14,320 --> 00:04:15,530
And there's different ways we can do that,
we

80
00:04:15,530 --> 00:04:17,740
can, because we have an existing database
for the

81
00:04:17,740 --> 00:04:19,430
people who have the previous version of
the app,

82
00:04:19,430 --> 00:04:22,540
we can either drop the database and
re-create it.

83
00:04:22,540 --> 00:04:28,030
Migrate the data over somehow or we can
alter tables in place and add new columns

84
00:04:28,030 --> 00:04:29,910
like this where the existing data stays
the

85
00:04:29,910 --> 00:04:31,560
same and then we populate the new columns.

86
00:04:31,560 --> 00:04:35,043
So, we'll take a look at that later on
after we cover those C.R.U.D. operations.

87
00:04:36,490 --> 00:04:39,820
Now, the last thing I mention before we
get

88
00:04:39,820 --> 00:04:44,010
started with actual code is the idea of
content providers.

89
00:04:44,010 --> 00:04:46,130
A lot of times you'll hear content
providers

90
00:04:46,130 --> 00:04:48,790
talked about in the same conversation as
SQLite

91
00:04:48,790 --> 00:04:52,400
databases, and a content provider is an
Android

92
00:04:52,400 --> 00:04:55,500
construct used to manage structured sets
of data.

93
00:04:55,500 --> 00:04:58,130
And a database is a structured set of
data,

94
00:04:58,130 --> 00:05:00,710
they're primarily intended to be used by
other applications.

95
00:05:00,710 --> 00:05:04,220
Which is why I said you have to explicitly
provide

96
00:05:04,220 --> 00:05:08,910
access to your data to other applications
through a content provider.

97
00:05:08,910 --> 00:05:12,160
But you don't need a provider to use an
SQL database

98
00:05:12,160 --> 00:05:15,190
if the use is entirely within the context
of your own app.

99
00:05:15,190 --> 00:05:16,630
So, that's what we're going to do today.

100
00:05:16,630 --> 00:05:19,060
We're just building a very simple app
that's going to have its own data.

101
00:05:19,060 --> 00:05:20,480
We're not going to provide it to anywhere
else.

102
00:05:20,480 --> 00:05:24,090
That's a topic for another workshop or
another Treehouse course.

103
00:05:24,090 --> 00:05:27,070
But I do wanna just mention before we

104
00:05:27,070 --> 00:05:30,280
get started one more thing about the
content provider.

105
00:05:30,280 --> 00:05:33,160
There's a, a good discussion that one of
our mobile developers Joe

106
00:05:33,160 --> 00:05:37,170
Steel mentioned to me about the benefits
of using a content provider.

107
00:05:37,170 --> 00:05:41,690
It is a best practice and this is a Google
Plus conversation that was

108
00:05:41,690 --> 00:05:44,070
happening with some Android developers
about the

109
00:05:44,070 --> 00:05:46,520
pros and cons of using a content provider.

110
00:05:46,520 --> 00:05:48,760
I'm gonna provide all these links for the

111
00:05:48,760 --> 00:05:51,130
workshop when this is published in a few
days.

112
00:05:51,130 --> 00:05:55,560
So anything that we cover on the web come
back when this is published in a

113
00:05:55,560 --> 00:05:59,440
few days and you'll be able to find these
links or you can search for this here.

114
00:05:59,440 --> 00:05:59,730
Okay.

115
00:05:59,730 --> 00:06:02,491
So, what exactly are we building?

116
00:06:02,491 --> 00:06:05,188
We are going to build a simple weather

117
00:06:05,188 --> 00:06:09,493
forecast app using an, an API provided by
forecast.io.

118
00:06:09,493 --> 00:06:14,101
Now if you're not familiar with
forecast.io, it's a web-based weather

119
00:06:14,101 --> 00:06:18,430
app that is available also as a web-based
app on your phones.

120
00:06:18,430 --> 00:06:23,260
So, it's a, it's a really great example of
the kind of web app

121
00:06:23,260 --> 00:06:26,780
you can build on a phone using web
technology that's not a native app.

122
00:06:26,780 --> 00:06:31,380
It's completely built from the website,
but they do some really neat things with

123
00:06:31,380 --> 00:06:32,950
animations and making it feel like a

124
00:06:32,950 --> 00:06:35,920
native app despite it all being web
technology.

125
00:06:35,920 --> 00:06:39,220
And the reason we're using this site is
they have a friendly

126
00:06:39,220 --> 00:06:42,940
developer portal where you can access
their API to get some forecast data.

127
00:06:42,940 --> 00:06:44,530
It's free, you can sign up if you

128
00:06:44,530 --> 00:06:48,080
just come here to developer.forecast.io
and register, you

129
00:06:48,080 --> 00:06:52,800
create an account and you get an API key
that unlocks your access to their API.

130
00:06:52,800 --> 00:06:54,460
That way they can shut you off if you're
abusing

131
00:06:54,460 --> 00:06:56,900
it [LAUGH], but it's a pretty, I, I don't
remember

132
00:06:56,900 --> 00:06:58,850
the exact numbers but you get a lot of
calls,

133
00:06:58,850 --> 00:07:02,140
so, it's really useful to build some
simple weather apps.

134
00:07:02,140 --> 00:07:04,230
And they, they give you a lot of
information, so I've

135
00:07:04,230 --> 00:07:09,770
switched here to the output from the API
the forecast call.

136
00:07:09,770 --> 00:07:12,130
So, this is a, a JSON output and I'm

137
00:07:12,130 --> 00:07:15,360
formatting my JSON with a chrome extension
called JSONView.

138
00:07:15,360 --> 00:07:19,160
You can see the plain source looks like
this, but, if we close this and go

139
00:07:19,160 --> 00:07:21,770
back we get information about our current
location,

140
00:07:21,770 --> 00:07:23,960
and then we get different types of
forecasts.

141
00:07:23,960 --> 00:07:27,800
The current conditions, minutes, hourly,
daily forecasts.

142
00:07:27,800 --> 00:07:30,570
We're going to look with the hourly
forecast because it

143
00:07:30,570 --> 00:07:34,080
gives us a set of 48 data points to work
with.

144
00:07:34,080 --> 00:07:37,480
And because we wanna show, inserting
multiple data points and manipulating

145
00:07:37,480 --> 00:07:40,330
it this is just a simple data set of 40
data points.

146
00:07:40,330 --> 00:07:42,710
We know we're getting and we can work with
it from there.

147
00:07:45,420 --> 00:07:45,630
Okay.

148
00:07:45,630 --> 00:07:47,940
So lets get started with our code.

149
00:07:49,410 --> 00:07:52,270
I'm building this app in Android Studio.

150
00:07:52,270 --> 00:07:56,820
And our Treehouse projects currently use
Eclipse.

151
00:07:56,820 --> 00:07:59,020
But everything we do here in Android
Studio

152
00:07:59,020 --> 00:08:00,660
are the code you can just transfer to
Eclipse.

153
00:08:00,660 --> 00:08:03,330
The only thing that's different is the
project set up itself.

154
00:08:03,330 --> 00:08:06,850
So when you create a new project you have
to manually add these code

155
00:08:06,850 --> 00:08:11,170
files in Eclipse, there's no easy way to
migrate from Android Studio to Eclipse.

156
00:08:11,170 --> 00:08:12,510
The reason I'm using Android Studio is

157
00:08:12,510 --> 00:08:14,320
because the tool itself just gets better
and

158
00:08:14,320 --> 00:08:16,090
better and I want to encourage you

159
00:08:16,090 --> 00:08:19,060
if you're comfortable to try out Android
Studio.

160
00:08:19,060 --> 00:08:21,480
Take a look I had an different workshop

161
00:08:21,480 --> 00:08:23,720
a couple months ago about getting started
with Android

162
00:08:23,720 --> 00:08:24,940
Studio, so if you want to check that

163
00:08:24,940 --> 00:08:28,360
out it's available now as an Android Tools
course.

164
00:08:28,360 --> 00:08:31,410
So you can review getting started with
Android

165
00:08:31,410 --> 00:08:33,850
Studio, and then take a look at this
project.

166
00:08:33,850 --> 00:08:35,320
All the code that we write is also going

167
00:08:35,320 --> 00:08:38,030
to be available for download, so we'll
take a look.

168
00:08:38,030 --> 00:08:41,470
And the idea is, we're gonna walk through
it, write some code, explain what's going

169
00:08:41,470 --> 00:08:44,780
on, and then you'll have a nice simple
example to get started with your own code.

170
00:08:44,780 --> 00:08:48,820
You can take these examples and, and
either copy it and, and manipulate

171
00:08:48,820 --> 00:08:51,680
it, or maybe even use some of it directly
in your own applications.

172
00:08:51,680 --> 00:08:54,130
And, it's the kind of thing where it's
good to know how you do

173
00:08:54,130 --> 00:08:56,870
it, but then once you get an example, you
can just reuse your existing code.

174
00:08:58,670 --> 00:09:02,430
So let's before we do anything, this code
is incomplete but let's

175
00:09:02,430 --> 00:09:05,250
run it just to get a quick look at the app
itself.

176
00:09:05,250 --> 00:09:07,700
[BLANK_AUDIO]

177
00:09:07,700 --> 00:09:11,963
Okay and it's about to load here on my
Genymotion emulator and

178
00:09:11,963 --> 00:09:16,010
this is another topic that I covered in
that Android tool's course.

179
00:09:16,010 --> 00:09:20,300
A Genymotion is a third party emulator
that works usually works better than the

180
00:09:20,300 --> 00:09:22,400
default emulator, it's very fast they have

181
00:09:22,400 --> 00:09:24,290
all sorts of different devices you can
try.

182
00:09:24,290 --> 00:09:26,530
So in general I, I highly recommend

183
00:09:26,530 --> 00:09:28,260
using that for your development purposes
as well.

184
00:09:28,260 --> 00:09:31,760
It's the next best thing to having an
actual device to develop on.

185
00:09:31,760 --> 00:09:36,734
So you can see here this app just has two
activities the first is this front one

186
00:09:36,734 --> 00:09:42,600
has our C.R.U.D or I.S.U.D operationa,
insert, select, update and delete.

187
00:09:42,600 --> 00:09:47,740
And we're gonna start with just creating
the database and inserting some data.

188
00:09:47,740 --> 00:09:49,740
Makes sense it's a good place to start.

189
00:09:49,740 --> 00:09:54,670
So back in our project we have, here's the
two activities we were working with.

190
00:09:54,670 --> 00:09:58,240
And the services package here, I've got
my, my code organized into

191
00:09:58,240 --> 00:10:02,340
a couple of sub packages, so that UI
packages for our activities.

192
00:10:02,340 --> 00:10:05,790
Services is for the code related to
getting data from the web.

193
00:10:05,790 --> 00:10:08,190
So I'm not gonna review any of this, it's
here.

194
00:10:08,190 --> 00:10:12,770
You can get the data from the web however
you want whatever data you're going after.

195
00:10:12,770 --> 00:10:18,590
But I'm using a library called Retrofit
it's an open source library from Square.

196
00:10:18,590 --> 00:10:20,180
And I haven't used it much, but what I
have used

197
00:10:20,180 --> 00:10:24,060
I really like, and so I recommend checking
that out as well.

198
00:10:24,060 --> 00:10:26,240
Could be a topic for another live stream,
but

199
00:10:26,240 --> 00:10:28,570
the the code here just as a quick review.

200
00:10:28,570 --> 00:10:31,130
You give it the base URL, and then you
configure

201
00:10:31,130 --> 00:10:35,180
your, your weather service or whatever
service you're running for

202
00:10:35,180 --> 00:10:37,990
and the, the library makes use of these
annotations, and

203
00:10:37,990 --> 00:10:40,600
it generates a lot of code for you behind
the scenes.

204
00:10:40,600 --> 00:10:43,370
It does a lot of magic but it does it in

205
00:10:43,370 --> 00:10:46,140
a way that, that makes it very clean and
easy to understand.

206
00:10:46,140 --> 00:10:50,420
So here the method to load the forecast
data, we create a new

207
00:10:50,420 --> 00:10:56,870
rest adapter because our, web service
we're going after is a rest API.

208
00:10:56,870 --> 00:11:01,520
And we call this get forecast and it runs
asynchronously.

209
00:11:01,520 --> 00:11:03,750
So, it goes out, requests the data, it

210
00:11:03,750 --> 00:11:07,360
uses the key and our current location and
then

211
00:11:07,360 --> 00:11:09,180
it, when it's all done, when the
asynchronous

212
00:11:09,180 --> 00:11:12,670
call finishes, it calls back with this
callback method.

213
00:11:12,670 --> 00:11:15,720
Now, this is that common asynchronous
processing with a callback

214
00:11:15,720 --> 00:11:18,550
that we've seen in all, a few of our
Android projects.

215
00:11:18,550 --> 00:11:22,630
So, when it calls back, we'll go back, so
we're calling this from our main activity

216
00:11:23,990 --> 00:11:28,740
and when the data returns, we pass in this
call back down here.

217
00:11:28,740 --> 00:11:32,785
And it's a retrofit callback and it has

218
00:11:32,785 --> 00:11:34,380
two different methods that we need to
override.

219
00:11:34,380 --> 00:11:37,720
First is success, which will include a
forecast object

220
00:11:37,720 --> 00:11:39,320
and that's the other object we have over
here.

221
00:11:39,320 --> 00:11:41,650
This is a mapping of the data from

222
00:11:41,650 --> 00:11:44,280
the JSON file, this is a very simplified
version.

223
00:11:44,280 --> 00:11:46,650
To map the entire data set would be a bit
more

224
00:11:46,650 --> 00:11:51,430
of a complex class structure, but this
drills down the reason

225
00:11:51,430 --> 00:11:53,600
I have it minimal like this is so that we
can

226
00:11:53,600 --> 00:11:56,850
get after that hourly data, the
temperatures, specifically, that we want.

227
00:11:58,190 --> 00:12:00,020
So, on a successful call, let's go back

228
00:12:00,020 --> 00:12:03,070
to the MainActivity, we get that forecast
object.

229
00:12:03,070 --> 00:12:05,350
And, right now we're just looping through.

230
00:12:05,350 --> 00:12:08,930
So, if we run this, we are gonna run it
just to make sure in the

231
00:12:08,930 --> 00:12:10,820
log we, I'm, I'm out putting the current

232
00:12:10,820 --> 00:12:13,560
temperatures just to make sure that we're
getting data.

233
00:12:13,560 --> 00:12:15,750
Let's proceed with actually creating the
database.

234
00:12:15,750 --> 00:12:18,170
So, I've got this other sub-package here
called db

235
00:12:18,170 --> 00:12:20,900
and that's where we're gonna put our
database classes.

236
00:12:20,900 --> 00:12:24,330
So before we write anything let's take a
look at the android documentation.

237
00:12:24,330 --> 00:12:27,930
Now, I'm a little bit in general I really
like the android documentation.

238
00:12:27,930 --> 00:12:31,160
I think they do a great job of explaining
how to do things.

239
00:12:31,160 --> 00:12:36,520
Providing example code of, I use it all
the time I use in our courses but the, the

240
00:12:36,520 --> 00:12:40,400
documentation about SQLite databases is a
little bit obtuse

241
00:12:40,400 --> 00:12:43,770
and it's, it's Well, it is what it is.

242
00:12:43,770 --> 00:12:46,050
So hopefully this course is enough [LAUGH]
to get you started and then

243
00:12:46,050 --> 00:12:46,960
you can work your way through

244
00:12:46,960 --> 00:12:49,690
the navigation through the documentation
as needed.

245
00:12:49,690 --> 00:12:52,280
But if you search for saving data, you can
find

246
00:12:52,280 --> 00:12:56,510
this reference of how to save data in SQL
databases.

247
00:12:56,510 --> 00:13:00,150
And we're gonna walk through these steps
but the, the first thing

248
00:13:00,150 --> 00:13:04,190
we need to do is create a database using a
SQL helper.

249
00:13:04,190 --> 00:13:09,250
And we do that by extending a base class
called SQLiteOpenHelper.

250
00:13:09,250 --> 00:13:13,020
So we're gonna create our own custom class
that will extend

251
00:13:13,020 --> 00:13:16,640
that base class, and take care of the
create operations for us.

252
00:13:16,640 --> 00:13:18,653
So if I right-click on db and select New

253
00:13:18,653 --> 00:13:21,800
> Java Class, we're gonna call this our
forecasthelper.

254
00:13:25,940 --> 00:13:34,130
Okay, and we are going to extend the
SQLiteOpenHelper.

255
00:13:34,130 --> 00:13:34,710
Okay?

256
00:13:34,710 --> 00:13:37,735
And right away, we get an error that we
were missing some required methods.

257
00:13:37,735 --> 00:13:41,250
SQLiteOpenHelper is an abstract class
which means we can't use it directly.

258
00:13:41,250 --> 00:13:43,880
That's why we need to subclass it like
this, and we can

259
00:13:43,880 --> 00:13:49,570
use Alt+Enter to bring up a quick fix for
implementing the methods.

260
00:13:49,570 --> 00:13:52,650
And if I enter here, we can create the two

261
00:13:52,650 --> 00:13:55,230
methods that we need, this will be just
the stubs.

262
00:13:55,230 --> 00:13:56,200
Click OK.

263
00:13:56,200 --> 00:13:57,420
And there you see the first one is called

264
00:13:57,420 --> 00:14:00,890
onCreate, which is used to, obviously,
create the database.

265
00:14:00,890 --> 00:14:03,920
Second is called onUpgrade which we'll
look at a little bit later on

266
00:14:03,920 --> 00:14:07,360
and that's when we wanna upgrade the
database from one version to another.

267
00:14:07,360 --> 00:14:09,990
Okay, so we still have a warning here
about

268
00:14:09,990 --> 00:14:12,730
a missing constructor, so let's take care
of that.

269
00:14:12,730 --> 00:14:15,990
Now, if we, if we do a quick fix here, if
I hit Alt+Enter

270
00:14:15,990 --> 00:14:18,600
and create the constructor we get a

271
00:14:18,600 --> 00:14:20,540
little more, little more information than
we want.

272
00:14:21,730 --> 00:14:23,290
This is to create it with passing in a
bunch of

273
00:14:23,290 --> 00:14:26,600
information, but we're gonna just make
this information part of the class.

274
00:14:26,600 --> 00:14:28,750
So our constructor will instead just
require

275
00:14:28,750 --> 00:14:32,220
the context, which is a common, common
pattern.

276
00:14:32,220 --> 00:14:37,730
And then we will add these individual
parameters here in the class.

277
00:14:37,730 --> 00:14:40,600
So the first parameter here is the name of
the database.

278
00:14:40,600 --> 00:14:43,890
We can name this whatever we like, and
let's make it a constant here.

279
00:14:43,890 --> 00:14:46,160
And we're gonna make it a public constant
for the

280
00:14:46,160 --> 00:14:49,290
database name, well, actually no, let's
make this one a private.

281
00:14:49,290 --> 00:14:50,300
Some of these constants we're gonna

282
00:14:50,300 --> 00:14:52,080
make public, because we'll reference them
elsewhere.

283
00:14:52,080 --> 00:14:56,112
But let's make this a private static final

284
00:14:56,112 --> 00:15:01,601
String call DB_NAME and we'll call this
temperatures.

285
00:15:01,601 --> 00:15:05,609
And we have to give it the .db extension.

286
00:15:05,609 --> 00:15:09,920
Okay, so now we can use this DB_NAME here
in the construct.DB_NAME.

287
00:15:11,210 --> 00:15:13,460
Now the next parameter we just are going
to

288
00:15:13,460 --> 00:15:18,540
pass in null, this is what's this
parameter, it's a

289
00:15:21,600 --> 00:15:26,500
I don, I don't remember off the top of my
head but we don't need it for this.

290
00:15:26,500 --> 00:15:30,040
And the version number is just an integer
version and this

291
00:15:30,040 --> 00:15:32,580
is how we know if we're upgrading the
database or not.

292
00:15:32,580 --> 00:15:37,116
So let's make this a private static final
int, I'll call a DB_VERSION,

293
00:15:37,116 --> 00:15:41,230
and I'll set equal to 1, because we're
just starting of with 1.

294
00:15:41,230 --> 00:15:43,780
It's an integer value so its gonna be 1
and above,

295
00:15:43,780 --> 00:15:47,010
and we need to increment the version when
we're upgrading database.

296
00:15:47,010 --> 00:15:51,810
So its passion, passing DB_VERSION here,
and now, our constructor

297
00:15:51,810 --> 00:15:55,660
is complete, and we can focus on the,
onCreate method.

298
00:15:57,720 --> 00:15:59,330
Let's go back to our documentation.

299
00:16:00,722 --> 00:16:01,860
All right.

300
00:16:01,860 --> 00:16:02,400
Okay.

301
00:16:02,400 --> 00:16:03,390
I apologize.

302
00:16:03,390 --> 00:16:06,480
So the next thing you wanna do in here is
use

303
00:16:06,480 --> 00:16:12,830
our, database object to run some SQL to
create a table.

304
00:16:12,830 --> 00:16:18,070
So, the next thing we need [LAUGH]
is a, the table name that we want.

305
00:16:18,070 --> 00:16:20,380
This is the constant that we're going to
make public.

306
00:16:20,380 --> 00:16:26,160
So the first final String TABLE, and we'll
call this

307
00:16:26,160 --> 00:16:32,200
table TEMPERATURES, and we'll just set
this equal to.

308
00:16:32,200 --> 00:16:35,310
We'll do these in all caps too as a

309
00:16:35,310 --> 00:16:41,196
common SQL convention to write in all
caps, TEMPERATURES.

310
00:16:41,196 --> 00:16:41,616
Okay.

311
00:16:41,616 --> 00:16:47,670
And, let's, you know, let's, let's define
our database create statement.

312
00:16:47,670 --> 00:16:50,766
So what I'm gonna do is use the SQLite
database

313
00:16:50,766 --> 00:16:55,430
that's passed in here as the parameter
into the onCreate method.

314
00:16:55,430 --> 00:16:56,060
It's called db.

315
00:16:56,060 --> 00:17:01,030
So, if we type db and look at the content
assist here then we see

316
00:17:01,030 --> 00:17:04,630
a, a command for executing SQL and this is
where we can write raw SQL.

317
00:17:04,630 --> 00:17:08,095
Now, in some cases with working with
SQLite, we wanna write

318
00:17:08,095 --> 00:17:10,820
we, in some cases we will write raw SQL
like this.

319
00:17:10,820 --> 00:17:12,920
Where it's a create statement that's just
a basic SQL

320
00:17:12,920 --> 00:17:15,890
statement just like you would see working
with any database.

321
00:17:15,890 --> 00:17:18,740
Other times we're gonna use some methods
and some classes to

322
00:17:18,740 --> 00:17:21,290
encapsulate that SQL and work with it in a
different way,

323
00:17:21,290 --> 00:17:23,740
a more modular way that makes for a
cleaner code for

324
00:17:23,740 --> 00:17:27,910
us, it's a cleaner interface between our
app and the underlying database.

325
00:17:27,910 --> 00:17:32,820
But here we're gonna pass in a string
called DB_CREATE.

326
00:17:32,820 --> 00:17:37,840
And then let's go up here and create that,
with the variables we define so.

327
00:17:39,830 --> 00:17:46,959
Another private static final String named
DB_CREATE.

328
00:17:46,959 --> 00:17:51,580
And right now we're gonna plug in some
variables, but let's, let's write

329
00:17:51,580 --> 00:17:54,370
this as if we were writing it right in
some kind of SQL editor.

330
00:17:54,370 --> 00:17:59,030
So we are going to write, CREATE TABLE,
and the name is TEMPERATURES.

331
00:18:00,500 --> 00:18:02,940
And when we create this table we need to
define the

332
00:18:02,940 --> 00:18:06,280
columns that are included and the data
type of those columns.

333
00:18:07,340 --> 00:18:10,170
So the first column, and they go in
parenthesis here.

334
00:18:10,170 --> 00:18:15,300
The first column we're going to use is a
primary key that's just an integer ID.

335
00:18:15,300 --> 00:18:19,260
And we don't need it for this example like
we saw in the, the full

336
00:18:19,260 --> 00:18:21,080
screen earlier with we're just gonna work
with

337
00:18:21,080 --> 00:18:22,980
temperatures, it's just gonna be one
column video.

338
00:18:22,980 --> 00:18:25,540
However, if we include this ID column that

339
00:18:25,540 --> 00:18:28,160
I'm about to write, it's common convention
for working

340
00:18:28,160 --> 00:18:30,560
with a SQLite in Android, and we need that

341
00:18:30,560 --> 00:18:33,340
column to work with certain things in the
future.

342
00:18:33,340 --> 00:18:36,510
So for content providers for example, or a
content adapters

343
00:18:36,510 --> 00:18:39,300
when you're adapting your data for views,
they require that ID

344
00:18:39,300 --> 00:18:41,120
column be in place, so it's a good
practice to

345
00:18:41,120 --> 00:18:43,910
generally create it, even if you're not
gonna use it yourself.

346
00:18:43,910 --> 00:18:44,980
We don't really need to worry about it.

347
00:18:44,980 --> 00:18:47,790
Once we create it, we're done with it,
because we're gonna create it in such

348
00:18:47,790 --> 00:18:49,170
a way that it auto-increments in the

349
00:18:49,170 --> 00:18:51,490
database by itself every time we insert
data.

350
00:18:52,560 --> 00:18:58,930
So, the first column the convention is _ID
and it is of a type INTEGER.

351
00:18:58,930 --> 00:19:00,950
And then we're gonna add that additional
information

352
00:19:00,950 --> 00:19:04,460
to make it the primary key, that auto
increments.

353
00:19:06,590 --> 00:19:07,680
Okay.

354
00:19:07,680 --> 00:19:11,170
The second column is our TEMPERATURE
column, and

355
00:19:11,170 --> 00:19:13,250
a data type for this is just REAL.

356
00:19:13,250 --> 00:19:17,520
So there's just a few basic datatypes
available in SQLite and

357
00:19:17,520 --> 00:19:20,540
they're REAL, datatype is for numbers that
have a decimal value.

358
00:19:20,540 --> 00:19:23,640
So the, if we take a look at the data that
we're getting

359
00:19:23,640 --> 00:19:28,540
back from our API, we see the temperature
is given with a decimal value.

360
00:19:28,540 --> 00:19:33,490
So we're gonna maintain that accuracy and
store it with a decimal in our database.

361
00:19:35,240 --> 00:19:39,400
Okay so now, let's plug in the actual
values, the variables instead of

362
00:19:39,400 --> 00:19:41,050
having this all hard coded, so here,

363
00:19:41,050 --> 00:19:45,035
instead of TEMPERATURES, let's plug in
TABLE_TEMPERATURES.

364
00:19:46,130 --> 00:19:47,040
Okay.

365
00:19:47,040 --> 00:19:50,660
And then we're gonna create column names
as well, for these variables.

366
00:19:50,660 --> 00:19:56,664
So instead of _ID, let's use a variable
called COLUMN_ID

367
00:19:56,664 --> 00:20:02,918
and we're gonna add that in a second and
then also down here let's do

368
00:20:02,918 --> 00:20:08,780
COLUMN_TEMPERATURE and leave that data
set.

369
00:20:08,780 --> 00:20:09,020
Okay.

370
00:20:09,020 --> 00:20:12,090
So now we just need to define those two
variables, I'm gonna copy and paste here.

371
00:20:13,740 --> 00:20:18,650
First one COLUMN_ID and this is where
that, we had that _ID.

372
00:20:21,080 --> 00:20:27,300
Second one is COLUMN_TEMPERATURES and I'm
sorry just TEMPERATURES since this is an

373
00:20:27,300 --> 00:20:29,620
individual row, an individual value within
our

374
00:20:29,620 --> 00:20:32,730
temperatures table, so we just use
temperature.

375
00:20:32,730 --> 00:20:35,970
So now we have these variables to find our
create table statement is ready.

376
00:20:35,970 --> 00:20:40,260
And we can execute it here within the
context of our onCreate method.

377
00:20:40,260 --> 00:20:42,170
So we're almost ready to create our
database

378
00:20:42,170 --> 00:20:44,740
and make sure that everything is in place.

379
00:20:44,740 --> 00:20:47,550
But before we do that we want to introduce
another class.

380
00:20:47,550 --> 00:20:51,396
We have, we're going to use two database
classes to access our database.

381
00:20:51,396 --> 00:20:55,080
This database helper is what we use for

382
00:20:55,080 --> 00:20:58,040
defining the database and creating and
upgrading it.

383
00:20:58,040 --> 00:21:00,440
But then we have a separate class we're
gonna create

384
00:21:00,440 --> 00:21:04,720
called forecast data source that's going
to act as a an

385
00:21:04,720 --> 00:21:08,540
interface on, on top of the, this forecast
helper and the

386
00:21:08,540 --> 00:21:11,300
forecast data source is going to do all of
our operations.

387
00:21:11,300 --> 00:21:15,430
So our code in our app is going to use the
call methods from

388
00:21:15,430 --> 00:21:19,350
forecast data source and in there it will
execute on the underlying SQL database.

389
00:21:19,350 --> 00:21:22,922
So let's right-click on the db package,
select New >

390
00:21:22,922 --> 00:21:27,489
Java Class and like I said, this one's
called ForecastDataSource.

391
00:21:30,100 --> 00:21:30,550
And click OK.

392
00:21:30,550 --> 00:21:32,660
And this one doesn't have to extend
anything, it's just that we are

393
00:21:32,660 --> 00:21:36,300
going to make use of that data helper
class that we just defined.

394
00:21:36,300 --> 00:21:39,780
Okay, so the first thing that we need is a
SQLite database object itself.

395
00:21:39,780 --> 00:21:46,410
So let's define a private SQLiteDatabase,
named mDatabase.

396
00:21:46,410 --> 00:21:52,630
And then we want our own version of that
forecast data, forecast helper.

397
00:21:52,630 --> 00:21:58,020
So we have private ForecastHelper and
we'll call this let's call

398
00:21:58,020 --> 00:22:03,310
it mForecastHelper.

399
00:22:03,310 --> 00:22:04,010
Okay.

400
00:22:04,010 --> 00:22:07,310
So let's start with the constructor, we're
gonna, we're gonna write

401
00:22:07,310 --> 00:22:09,000
this code, and then we're gonna use it in
our main activity.

402
00:22:09,000 --> 00:22:14,880
So the constructor is public
ForecastDataSource, and we're gonna pass

403
00:22:14,880 --> 00:22:20,970
in the context again, we need to pass it
along to the ForecastHelper class.

404
00:22:22,160 --> 00:22:24,440
And all we wanna do here is, well, I

405
00:22:24,440 --> 00:22:27,020
should have created a context variable to
hold on to.

406
00:22:27,020 --> 00:22:30,530
So we'll do private Context mContext.

407
00:22:32,340 --> 00:22:36,371
And now we can set mContext equal to the
context.

408
00:22:36,371 --> 00:22:42,151
And the second thing we wanna do is use
our instantiate our forecast helper,

409
00:22:42,151 --> 00:22:47,518
so that's equal to a new ForecastHelper
and that requires the mContext.

410
00:22:47,518 --> 00:22:49,389
So we pass that in.

411
00:22:49,389 --> 00:22:50,072
Okay?

412
00:22:50,072 --> 00:22:55,850
And now this class is going to be
responsible for a couple different things.

413
00:22:55,850 --> 00:22:59,160
So let's add some comments here, and we're
gonna, we're, we're gonna define what

414
00:22:59,160 --> 00:23:01,880
we're gonna do in this class and then
we're gonna do it piece by piece.

415
00:23:01,880 --> 00:23:06,790
So the first thing we need to do is open a
database, and then conversely close one.

416
00:23:06,790 --> 00:23:12,806
And then we're gonna do those C.R.U.D
operations,

417
00:23:12,806 --> 00:23:17,380
insert, select, update, and delete.

418
00:23:17,380 --> 00:23:18,760
Okay, and I know we're doing a lot

419
00:23:18,760 --> 00:23:20,810
here before running anything, and you
know, generally I

420
00:23:20,810 --> 00:23:24,380
like to do as little as possible, and then
test it to make sure that it's working.

421
00:23:24,380 --> 00:23:26,560
But we'll get this basic stuff setup,

422
00:23:26,560 --> 00:23:28,140
we'll run, and then hopefully it'll make
sense.

423
00:23:28,140 --> 00:23:30,670
We'll walk through exactly where each
piece is cuz you know,

424
00:23:30,670 --> 00:23:33,620
we have these couple different pieces
moving, but once we get

425
00:23:33,620 --> 00:23:35,780
into the main activity, and call it, it
should be more

426
00:23:35,780 --> 00:23:37,678
apparent as to what's going on behind the
scenes for us.

427
00:23:37,678 --> 00:23:43,660
Okay, so let's start with the open method.

428
00:23:46,685 --> 00:23:50,570
Let's do, so call this open and this one
is special

429
00:23:50,570 --> 00:23:55,170
we're gonna throw a SQLException just in
case anything goes wrong.

430
00:23:56,830 --> 00:24:00,660
As a generic SQL error that we wanna watch
out for with certain operations.

431
00:24:03,290 --> 00:24:08,290
but, yeah, so, this is just one line of
code and it's from our forecast helper.

432
00:24:09,440 --> 00:24:12,270
So we set our database, the SQLite
database

433
00:24:12,270 --> 00:24:17,320
from the forecast helper, using a method
called getWriteableDatabase.

434
00:24:17,320 --> 00:24:18,340
And this is the important line of code,

435
00:24:18,340 --> 00:24:20,190
this is really what we were building
toward.

436
00:24:20,190 --> 00:24:26,270
So getWritableDatabase is from that
SQLiteOpenHelper method, open help.

437
00:24:26,270 --> 00:24:27,690
I'm sorry SQLiteOpenHelper class.

438
00:24:27,690 --> 00:24:32,130
And it's a method that will create the
database if it

439
00:24:32,130 --> 00:24:35,668
doesn't exist, or just open the database
if it exists for writing.

440
00:24:35,668 --> 00:24:36,436
Now in content assist you may

441
00:24:36,436 --> 00:24:38,300
have seen there's a different version,
getReadableDatabase.

442
00:24:38,300 --> 00:24:40,540
That's where if you want to have a read
only database, but we're

443
00:24:40,540 --> 00:24:44,320
gonna be inserting data and updating so we
definitely wanna be able to write.

444
00:24:44,320 --> 00:24:48,770
So now this is ready to use within our
main activities.

445
00:24:48,770 --> 00:24:52,420
So let's switch over and what I've done
here is

446
00:24:52,420 --> 00:24:55,520
I've left a lot of code based on the UI

447
00:24:55,520 --> 00:24:58,250
and the buttons and things like that,
things that you'll

448
00:24:58,250 --> 00:25:01,870
hopefully already know how to do based on
our Android courses.

449
00:25:01,870 --> 00:25:05,055
But I've left notes for us to work through
on

450
00:25:05,055 --> 00:25:07,570
and the pieces we need to get the data in
place.

451
00:25:08,580 --> 00:25:10,210
So the first one up here is to declare a
data

452
00:25:10,210 --> 00:25:13,720
source, and that's gonna be using our new
forecast data source class.

453
00:25:13,720 --> 00:25:16,034
So let's make this a protected

454
00:25:16,034 --> 00:25:20,360
ForecastDataSource, and we'll call it
mDataSource.

455
00:25:20,360 --> 00:25:22,470
Kay the next thing we do, wanna do

456
00:25:22,470 --> 00:25:25,310
is instantiate that when the activity is
created.

457
00:25:25,310 --> 00:25:27,494
So sure enough down here there's

458
00:25:27,494 --> 00:25:31,550
another note, mDataSource equals a new
ForecastDataSource

459
00:25:31,550 --> 00:25:37,400
and remember we need to pass on the
context so we'll use MainActivity.this.

460
00:25:37,400 --> 00:25:38,570
Okay.

461
00:25:38,570 --> 00:25:41,670
So this creates our data source object and
now

462
00:25:41,670 --> 00:25:43,330
that we have it, we can open the database.

463
00:25:46,190 --> 00:25:50,462
So we just call mDataSource.open and then
this show

464
00:25:50,462 --> 00:25:54,990
to create the database and open a
connection to it.

465
00:25:54,990 --> 00:25:59,500
So that's another thing we need to talk
about is database connections.

466
00:25:59,500 --> 00:26:03,450
So the database the way it works is we
once

467
00:26:03,450 --> 00:26:05,790
it's created, we open a connection to it,
and that

468
00:26:05,790 --> 00:26:08,940
connection we use to write and retrieve
data, and when

469
00:26:08,940 --> 00:26:11,230
we're all done with it, we have to close
that connection.

470
00:26:11,230 --> 00:26:15,020
Now it's important to manage opening and
closing appropriately, so

471
00:26:15,020 --> 00:26:18,500
that we don't leave a connection open when
our activity is

472
00:26:18,500 --> 00:26:20,999
destroyed or our app closes because you
can end up with

473
00:26:22,280 --> 00:26:26,760
no pointer references or memory issues, or
corrupting the database somehow.

474
00:26:26,760 --> 00:26:30,130
So we have to carefully correspond,

475
00:26:30,130 --> 00:26:32,710
carefully match opening calls with closing
calls.

476
00:26:32,710 --> 00:26:34,390
So there are a couple of different ways
you can do this.

477
00:26:34,390 --> 00:26:38,930
One way would be to whenever you wanna
perform a SQL operation,

478
00:26:38,930 --> 00:26:42,440
you open a connection, perform that
operation and then close it right away.

479
00:26:42,440 --> 00:26:43,710
That's fine, although if you're doing a

480
00:26:43,710 --> 00:26:45,720
lot of operations in an activity, you'll
end

481
00:26:45,720 --> 00:26:47,010
up with a lot of open and closing

482
00:26:47,010 --> 00:26:49,880
calls which there's nothing really wrong
with it.

483
00:26:49,880 --> 00:26:53,960
The performance, it is minimal, but it
just kinda clutters your code.

484
00:26:53,960 --> 00:26:55,840
So, in general, what, what I like to do is
open

485
00:26:55,840 --> 00:26:59,515
and close my connections in life cycle
methods of the activity.

486
00:26:59,515 --> 00:27:02,920
So just a quick review of the life cycle
of an activity.

487
00:27:02,920 --> 00:27:05,460
When it's first created this onCreate
method is

488
00:27:05,460 --> 00:27:07,405
called, and that's where we set up and

489
00:27:07,405 --> 00:27:09,330
instantiate a lot of variables, data we
might

490
00:27:09,330 --> 00:27:11,680
need tie things to the layout et cetera.

491
00:27:12,898 --> 00:27:15,316
As soon as it's onCreate method is called,
as

492
00:27:15,316 --> 00:27:18,570
soon as it finishes rather, the onResume
method is called.

493
00:27:18,570 --> 00:27:21,160
So those, those two life cycle methods are
piggy backed

494
00:27:21,160 --> 00:27:23,540
on top of each other when an activity's
first created.

495
00:27:23,540 --> 00:27:25,270
Then when that activity is sent to the
background

496
00:27:25,270 --> 00:27:27,940
whether the user navigates to another
activity or a

497
00:27:27,940 --> 00:27:30,690
different app, closes the app, the onPause
method is

498
00:27:30,690 --> 00:27:33,900
called, so you always think of onResume
and onPause together.

499
00:27:33,900 --> 00:27:38,810
So if we open the database in onResume,
and close it in onPause, then we'll make

500
00:27:38,810 --> 00:27:41,170
sure that the database connection is only
open

501
00:27:41,170 --> 00:27:43,040
while our activity is active on the phone.

502
00:27:43,040 --> 00:27:44,790
And that's the behavior we're after, so
that's what we're gonna

503
00:27:44,790 --> 00:27:50,251
do here is we're gonna move this
mDataSource.open to the onResume method.

504
00:27:51,340 --> 00:27:53,960
So I've got it down here again a little
note let's just paste it in.

505
00:27:55,600 --> 00:28:00,200
In here onPause we want our corresponding
closer, and so we call mDataSource.close.

506
00:28:01,380 --> 00:28:04,490
But wait we haven't defined the close
method yet, so

507
00:28:04,490 --> 00:28:08,120
let's switch back to ForecastDataSource
and this is real simple.

508
00:28:09,330 --> 00:28:12,610
We said we were, are going to use a close
method, public void close,

509
00:28:13,990 --> 00:28:18,440
and all we do is from this database method
call the close method itself.

510
00:28:18,440 --> 00:28:19,530
So this is, again, just a wrap where

511
00:28:19,530 --> 00:28:22,880
this is an instance where this
ForecastDataSource is really

512
00:28:22,880 --> 00:28:25,130
just a wrapper on the call, so that we

513
00:28:25,130 --> 00:28:27,380
aren't interacting with the database
directly in our code.

514
00:28:31,260 --> 00:28:33,060
Alright, so at this point, there is no
errors

515
00:28:33,060 --> 00:28:35,340
in the project and it should be ready to
run.

516
00:28:35,340 --> 00:28:37,970
So and this is, I've got a brand new
emulator instance here

517
00:28:40,250 --> 00:28:44,060
and I ran it without any database code in
here.

518
00:28:44,060 --> 00:28:46,130
So there's, there is nothing currently on

519
00:28:46,130 --> 00:28:48,290
this Android phone, on this Android
emulator.

520
00:28:48,290 --> 00:28:50,520
So if we run it, we should create a
database, and

521
00:28:52,040 --> 00:28:54,335
we'll just wait a moment for it to load on
the emulator.

522
00:28:54,335 --> 00:28:58,840
Okay, there we go, oh, look, I got my log

523
00:28:58,840 --> 00:29:01,420
head back [LAUGH], I forgot when I run the
project.

524
00:29:01,420 --> 00:29:04,880
That'll bring it back all right so nothing
to

525
00:29:04,880 --> 00:29:07,010
see here in app yet because we haven't
done anything.

526
00:29:07,010 --> 00:29:09,940
But behind the scenes, we should have
created the database.

527
00:29:09,940 --> 00:29:11,390
So how can we tell?

528
00:29:11,390 --> 00:29:15,350
Well, if you remember we created a
database name called TEMPERATURES.db.

529
00:29:15,350 --> 00:29:19,280
And that's an actual database file that's
available on our file system.

530
00:29:19,280 --> 00:29:22,540
So there's different ways you can look at
files on the end of your file system.

531
00:29:22,540 --> 00:29:24,030
If you're using Eclipse, there's a built

532
00:29:24,030 --> 00:29:27,710
in file explorer from the DDMS
perspective.

533
00:29:27,710 --> 00:29:29,400
But you can also use command line tools.

534
00:29:29,400 --> 00:29:32,220
And that's what we're gonna do here
because there's an additional

535
00:29:32,220 --> 00:29:35,750
command line tool that interacts with SQL
directly, it's called SQLite3.

536
00:29:35,750 --> 00:29:38,860
So let's take a look at how to use that
tool and make sure that we

537
00:29:38,860 --> 00:29:41,740
are in fact, that we do in fact have a
database created behind the scenes here.

538
00:29:42,850 --> 00:29:47,080
So in the terminal it depends on how you
have your environment set up.

539
00:29:47,080 --> 00:29:49,730
You may need to navigate to the Android
SDK.

540
00:29:49,730 --> 00:29:53,520
So if you look here I've got I'm in the
Android SDK that corresponds

541
00:29:53,520 --> 00:29:58,180
to Android Studio and specifically I go
into platform tools and if I type

542
00:29:58,180 --> 00:30:01,970
ls for list, then I see a couple different
tools available and what we're

543
00:30:01,970 --> 00:30:06,580
gonna use is this one called adb which now
adb stands for Android Debug Bridge.

544
00:30:09,800 --> 00:30:12,520
So the one thing I wanna show is you can
see the currently connected

545
00:30:12,520 --> 00:30:18,790
devices if I do ./adb devices, then you
could see the list of devices attached.

546
00:30:18,790 --> 00:30:22,120
And this is the serial number for my
emulator

547
00:30:22,120 --> 00:30:26,660
instance and we need that to connect to
SQLite.

548
00:30:26,660 --> 00:30:27,994
So let me go back to Chrome for just a

549
00:30:27,994 --> 00:30:30,410
moment, that's the next tab I wanted to
show you.

550
00:30:30,410 --> 00:30:33,545
This is the, tool from the command line
that we're gonna use to take

551
00:30:33,545 --> 00:30:37,370
a look at our database, and this is
available in, under the Tools section.

552
00:30:37,370 --> 00:30:38,900
There are a lot of different command line
tools

553
00:30:38,900 --> 00:30:40,850
available in Android, and we haven't
really talked about

554
00:30:40,850 --> 00:30:43,650
many at Treehouse, but if you go in
through

555
00:30:43,650 --> 00:30:45,670
a command line to a connected device like
this.

556
00:30:45,670 --> 00:30:46,920
You can do some of the powerful stuff to

557
00:30:46,920 --> 00:30:49,800
make sure that your development is
proceeding as planned.

558
00:30:49,800 --> 00:30:53,550
So the next thing we're gonna do is
execute, enter the remote ADB shell,

559
00:30:53,550 --> 00:30:57,140
using this following command, and we need
that serial number that I just showed you.

560
00:30:57,140 --> 00:31:01,440
So the next line type ./adb -s for serial

561
00:31:01,440 --> 00:31:04,640
number and then I'll just copy and paste
this.

562
00:31:06,420 --> 00:31:07,250
And we want the show.

563
00:31:08,650 --> 00:31:11,740
Okay, now this is a shell on a device
itself, on

564
00:31:11,740 --> 00:31:18,580
the Genymotion emulator, so here we can
use that sqlite3 command.

565
00:31:18,580 --> 00:31:21,490
And if we just run this, sure enough we

566
00:31:21,490 --> 00:31:24,670
get the three corresponds to the version
of SQL.

567
00:31:24,670 --> 00:31:28,269
And here we got a SQLite prompt and you
know, depending on, on your

568
00:31:28,269 --> 00:31:30,240
familiarity with SQL development you may
have

569
00:31:30,240 --> 00:31:32,440
used tools like this on other systems.

570
00:31:33,870 --> 00:31:36,050
But there's not much here, we need to

571
00:31:36,050 --> 00:31:40,180
instead open up to our, our own database
specifically.

572
00:31:40,180 --> 00:31:42,550
If you type .help you can see a bunch

573
00:31:42,550 --> 00:31:45,940
of different options these are different
commands you can run.

574
00:31:45,940 --> 00:31:47,610
You can type straight SQL in here as well
and

575
00:31:47,610 --> 00:31:50,110
execute it as long as you end it with a
semicolon.

576
00:31:50,110 --> 00:31:52,410
But if we look here at the databases we
should get the

577
00:31:52,410 --> 00:31:57,230
list of databases but it's not, this is
not for our app specifically.

578
00:31:57,230 --> 00:32:02,130
We need to instead quit here, so we do
.quit and we wanna

579
00:32:02,130 --> 00:32:06,410
open the connection to our database and
here you can use the format here.

580
00:32:06,410 --> 00:32:09,300
So where is it?

581
00:32:09,300 --> 00:32:16,220
Here we go, this line here shows the path
to emulator device instance.

582
00:32:16,220 --> 00:32:18,812
It's a data/data/package_name/databases,
and then you

583
00:32:18,812 --> 00:32:20,290
append the database at the end.

584
00:32:20,290 --> 00:32:22,370
So, let's type that out, and that should
make more sense.

585
00:32:22,370 --> 00:32:28,607
So, sqlite3 and then we pass in
/data/data/com.teamtreehouse.,

586
00:32:28,607 --> 00:32:33,246
and this is called, I called this
friendlyforecast.

587
00:32:33,246 --> 00:32:38,870
And /databases/, let's see if that's
correct.

588
00:32:38,870 --> 00:32:39,777
Yep.

589
00:32:39,777 --> 00:32:43,017
We called it temperatures.db, so that
should

590
00:32:43,017 --> 00:32:46,730
open up okay, we've got the command
prompt.

591
00:32:46,730 --> 00:32:51,399
Now if we type let's see databases i think
it lists.

592
00:32:51,399 --> 00:32:54,172
Yeah, there you could see the file that
corresponds the main database

593
00:32:54,172 --> 00:32:57,220
because we're in our temperatures.db, you
can see the file name itself.

594
00:32:58,450 --> 00:33:03,570
Now if we look at the tables we should
have one table temperatures.

595
00:33:03,570 --> 00:33:06,970
Well there's an android meta datatable
that's included

596
00:33:06,970 --> 00:33:09,690
by default as well for your SQLite
instance.

597
00:33:09,690 --> 00:33:11,930
But temperatures is the one we define and
you'll notice, you'll

598
00:33:11,930 --> 00:33:19,180
notice that matches exactly what we
created in our ForecastOpenHelper class.

599
00:33:19,180 --> 00:33:22,150
So before we're go on, let's make sure
this is what we expect.

600
00:33:22,150 --> 00:33:27,412
We can look at the schema by typing schema
and then giving the table name

601
00:33:27,412 --> 00:33:32,180
TEMPERATURES, and sure enough, there is
the exact create statement we defined.

602
00:33:32,180 --> 00:33:35,160
It's got the _ID COLUMN and the second one
is for TEMPERATURE.

603
00:33:35,160 --> 00:33:39,090
Great, so that means our data is in place
and we can start using it.

604
00:33:39,090 --> 00:33:40,960
So, let's insert some data.

605
00:33:42,620 --> 00:33:44,260
So coming back to Android studio.

606
00:33:46,280 --> 00:33:47,540
We're gonna just work our way through this

607
00:33:47,540 --> 00:33:51,520
class, we have instantiated the, the
database we've opened.

608
00:33:51,520 --> 00:33:53,610
We created it, opened it, we're closing it
on onPause.

609
00:33:53,610 --> 00:33:58,130
So now, let's take a look at the insert so
let's see where we insert it.

610
00:33:58,130 --> 00:34:01,230
So, if we go back to the app we're gonna
have these couple of

611
00:34:01,230 --> 00:34:05,800
buttons here and there's nothing to view
to SELECT, UPDATE or DELETE right now.

612
00:34:05,800 --> 00:34:07,540
So those buttons are greyed out.

613
00:34:07,540 --> 00:34:11,390
But let's add the code to insert.

614
00:34:11,390 --> 00:34:14,680
So there's an insert button, [COUGH]

615
00:34:14,680 --> 00:34:17,420
and when it's on the click listener,

616
00:34:17,420 --> 00:34:20,600
we're just calling this method called
loadForecastData.

617
00:34:20,600 --> 00:34:21,920
So if we scroll down a bit,

618
00:34:21,920 --> 00:34:24,930
we see that loadForecastData calls our
ForecastService.

619
00:34:26,080 --> 00:34:30,910
And it calls at loadForecastData which
executes a call back, so we're passing in.

620
00:34:30,910 --> 00:34:32,830
This is again what we went through when we
took

621
00:34:32,830 --> 00:34:36,180
a brief look at this class, this is that
loadForecastData.

622
00:34:36,180 --> 00:34:41,680
So this call back gets executed when the
data is returned, and that's down here.

623
00:34:42,720 --> 00:34:45,760
Right now we're just logging the data, but
now we can go ahead

624
00:34:45,760 --> 00:34:49,860
and insert our new data but we need a
method to do that.

625
00:34:49,860 --> 00:34:51,970
So let's go back to our DataSource class,
because that's

626
00:34:51,970 --> 00:34:56,670
where we're writing our interface to all
the database operations.

627
00:34:56,670 --> 00:34:59,220
And for the rest of these operations we're
going to

628
00:34:59,220 --> 00:35:03,800
make use of a SQL construct known as a
cursor.

629
00:35:03,800 --> 00:35:06,190
So each of these methods is going to
return a

630
00:35:06,190 --> 00:35:09,090
cursor, or in some cases, a, a, a
different value.

631
00:35:09,090 --> 00:35:13,550
But what the cursor does is, we say to the
database, okay, go and get me this data.

632
00:35:13,550 --> 00:35:16,030
The database returns something called a
results set, and it's a

633
00:35:16,030 --> 00:35:19,880
set of data in whatever format we need,
whatever we requested.

634
00:35:19,880 --> 00:35:23,610
And then we use a cursor to iterate
through that data however we want.

635
00:35:23,610 --> 00:35:26,250
So you can think of it as like a block of,
of rows in

636
00:35:26,250 --> 00:35:29,550
the table and we say with the cursor, the
cursor points to that block

637
00:35:29,550 --> 00:35:32,960
and we can do operations like, okay go to
the first row and it'll

638
00:35:32,960 --> 00:35:36,830
go there and from that cursor we can then
access the values in that table.

639
00:35:36,830 --> 00:35:39,020
Then we can move the cursor around, we can
just

640
00:35:39,020 --> 00:35:41,920
loop through, we can move to the end,
different things.

641
00:35:41,920 --> 00:35:47,850
But right now with an insert what we wanna
do is create our

642
00:35:47,850 --> 00:35:50,530
create the data and, and store it into the
table, into that temperatures table.

643
00:35:51,550 --> 00:35:55,360
So, the first thing, let's, let's create
the method itself.

644
00:35:55,360 --> 00:35:57,230
Let me just glance at my notes.

645
00:35:58,320 --> 00:36:04,470
okay, public void, insert forecast, and we
had that

646
00:36:04,470 --> 00:36:06,530
forecast object which has all the data we
want.

647
00:36:06,530 --> 00:36:11,500
Remember, that forecast object is
returning from the web service, so, we'll

648
00:36:11,500 --> 00:36:14,005
pass it in right here and we'll, we'll
manipulate it from here directly.

649
00:36:14,005 --> 00:36:17,090
[SOUND]

650
00:36:17,090 --> 00:36:21,300
Kay, and, and, I, I got a little bit ahead
of myself talking about the cursors.

651
00:36:21,300 --> 00:36:24,640
That's actually more for selects and
updates and deletes, but for the this one

652
00:36:24,640 --> 00:36:26,210
because we're inserting, we're just going
to

653
00:36:26,210 --> 00:36:29,510
insert and assume success and come back.

654
00:36:29,510 --> 00:36:30,900
We're not going to look at the data that
was

655
00:36:30,900 --> 00:36:33,430
inserted with the cursor, although you
could certainly do that.

656
00:36:34,980 --> 00:36:36,520
So, let's see, what should we do here?

657
00:36:39,920 --> 00:36:42,330
Let's insert, we're gonna insert one row
first,

658
00:36:42,330 --> 00:36:44,620
and then we'll talk about inserting
multiple rows.

659
00:36:44,620 --> 00:36:48,880
So to do just one simple insert, now we're
not gonna write raw SQL, we're

660
00:36:48,880 --> 00:36:53,950
not going to create a SQL string that says
insert into table values blah, blah, blah.

661
00:36:53,950 --> 00:36:56,870
We're going to use a method from that
sequel

662
00:36:56,870 --> 00:37:01,250
open helper, from the sequel I dated as
class, rather.

663
00:37:01,250 --> 00:37:03,480
So the first thing we want to do is when
we pass in values

664
00:37:03,480 --> 00:37:05,340
like this whether we're inserting or
updating

665
00:37:05,340 --> 00:37:08,810
we use a class called content values.

666
00:37:08,810 --> 00:37:13,040
We'll name this values and we'll just
initialize it with a new content values.

667
00:37:13,040 --> 00:37:18,060
Okay, but now it's kind of like a
dictionary or a hash map, things

668
00:37:18,060 --> 00:37:21,760
you might be familiar with, the contact
values, we put things in to it.

669
00:37:21,760 --> 00:37:24,520
So for these values we put a key at a
value.

670
00:37:24,520 --> 00:37:27,150
So the key here, because we're inserting
in to our table, we're going

671
00:37:27,150 --> 00:37:30,750
to insert data in to that temperature
column, so we want to temperature key.

672
00:37:30,750 --> 00:37:31,760
And sure enough that's why we made

673
00:37:31,760 --> 00:37:35,300
this public constant if we type forecast
helper.

674
00:37:35,300 --> 00:37:38,460
Dot column temperature, that gets us the
appropriate

675
00:37:38,460 --> 00:37:41,490
column and now we just need a value.

676
00:37:41,490 --> 00:37:43,590
Before we dive into the forecast object
let's

677
00:37:43,590 --> 00:37:46,650
just, let's just test it with decimal
value.

678
00:37:46,650 --> 00:37:50,480
We'll do a nice, beautiful 75 degree day
okay.

679
00:37:50,480 --> 00:37:54,280
And now we can use these values in the
insert itself.

680
00:37:54,280 --> 00:37:58,630
So, from our database object that we have
running here.

681
00:37:58,630 --> 00:38:02,910
We want to call insert and the first

682
00:38:02,910 --> 00:38:08,210
parameter is the table, so forecast
helper.table temperatures.

683
00:38:08,210 --> 00:38:11,550
Second parameter is a null column hack,
that's if

684
00:38:11,550 --> 00:38:13,930
you, if you want to have null data in

685
00:38:13,930 --> 00:38:18,130
your table, you can allow Null for certain
columns,

686
00:38:18,130 --> 00:38:20,280
and this is, well we're just gonna use
null now.

687
00:38:20,280 --> 00:38:25,470
But you can provide a value there if you
need to get around

688
00:38:25,470 --> 00:38:30,220
some restrictions with your null columns,
it does not matter for this purpose.

689
00:38:30,220 --> 00:38:32,880
And then here the last parameter you see
are those content values.

690
00:38:32,880 --> 00:38:37,990
So, what's happening is, we're gonna
insert into our temperatures table, the

691
00:38:37,990 --> 00:38:41,160
values that we just defined, so it's gonna
go to, column temperature.

692
00:38:41,160 --> 00:38:42,890
It's gonna add a new temperature of

693
00:38:42,890 --> 00:38:46,010
75 degrees, and it's going to
auto-increment that

694
00:38:46,010 --> 00:38:48,180
id column that we defined, but you can

695
00:38:48,180 --> 00:38:50,540
see here we're no manipulating that id
ourselves.

696
00:38:50,540 --> 00:38:52,810
It auto-increments in the database behind
the scenes,

697
00:38:56,770 --> 00:38:58,250
and that should be all we need for this

698
00:38:58,250 --> 00:39:01,340
insert so let's go back to our main
activity.

699
00:39:01,340 --> 00:39:04,260
And now we can replace this to do with an
actual

700
00:39:04,260 --> 00:39:08,970
insert, so once again, from the data
source, we call, insert forecast.

701
00:39:08,970 --> 00:39:12,710
And lo and behold, we have a nice forecast
variable we can use that was returned.

702
00:39:13,850 --> 00:39:14,560
Okay?

703
00:39:14,560 --> 00:39:17,240
And these methods here update high and
low, we're actually not gonna

704
00:39:17,240 --> 00:39:19,150
get to that, because we, I don't think
we'll have enough time.

705
00:39:20,390 --> 00:39:21,660
And then enable that other button.

706
00:39:21,660 --> 00:39:22,880
So, once we insert data, we can go

707
00:39:22,880 --> 00:39:26,400
ahead and start looking at select update
and delete.

708
00:39:26,400 --> 00:39:28,055
So let's just save this and run it,

709
00:39:28,055 --> 00:39:30,140
snd make sure that we're actually
inserting data.

710
00:39:33,350 --> 00:39:36,960
Okay, we'll give Genymotion just a second
to reload.

711
00:39:36,960 --> 00:39:38,930
There we go, okay.

712
00:39:38,930 --> 00:39:40,360
Now, if we type, if we click on

713
00:39:40,360 --> 00:39:42,800
insert, we should see the buttons enabled,
assuming success.

714
00:39:42,800 --> 00:39:45,880
Great and you see these aren't, doing
anything right now.

715
00:39:45,880 --> 00:39:49,330
That's okay, but how do we know that we
actually inserted data?

716
00:39:49,330 --> 00:39:54,600
Well, we can go right back to the terminal
and it didn't have any data before but now

717
00:39:54,600 --> 00:39:59,780
if we run straight SQL here, if we select
everything, select star from temperatures,

718
00:40:02,850 --> 00:40:03,220
there we go.

719
00:40:03,220 --> 00:40:06,660
We have one row, the very first ID is 1,
because it

720
00:40:06,660 --> 00:40:10,570
starts auto-incrementing from 1, and
there's the 75 degrees we put in.

721
00:40:10,570 --> 00:40:11,510
Very good.

722
00:40:11,510 --> 00:40:15,970
Notice that the case the SQL is case
sensitive or it's case insensitive rather.

723
00:40:15,970 --> 00:40:19,480
So I typed in lowercase although common

724
00:40:19,480 --> 00:40:21,370
convention is to type SQL commands in
uppercase.

725
00:40:21,370 --> 00:40:24,120
That's why we have uppercase names in our
classes.

726
00:40:25,200 --> 00:40:27,480
Okay, so let's take a look, we have 48

727
00:40:27,480 --> 00:40:29,450
rows of data to insert, so let's input,
let's

728
00:40:29,450 --> 00:40:33,880
put all 48 temperatures into our table, so
we

729
00:40:33,880 --> 00:40:36,610
need to do that back in our forecast data
source.

730
00:40:37,660 --> 00:40:40,220
And the forecast itself crea, has an array

731
00:40:40,220 --> 00:40:43,500
of hourly forecast and we can just loop
through

732
00:40:43,500 --> 00:40:46,860
each one, and put them in, however, there

733
00:40:46,860 --> 00:40:49,850
is something we want to think about before
that.

734
00:40:49,850 --> 00:40:52,710
If we just looped through and did it like
this using the database

735
00:40:52,710 --> 00:40:57,090
insert, then, behind the scenes, there's a
little bit of a performance hit.

736
00:40:57,090 --> 00:41:02,650
And, every time you run a SQL command,
like this, the data is inserted

737
00:41:02,650 --> 00:41:04,300
and then immediately, it's committed, so
any

738
00:41:04,300 --> 00:41:06,940
kind of change is committed right
afterwards.

739
00:41:06,940 --> 00:41:09,770
And, when you're doing bulk sequel
operations,

740
00:41:09,770 --> 00:41:12,300
whether it's an Android or any other
system.

741
00:41:12,300 --> 00:41:16,410
What you want to do is try and delay those
commits until you're either completely

742
00:41:16,410 --> 00:41:20,050
done or maybe it's staged commits along
the process if you have a lot of data.

743
00:41:21,060 --> 00:41:24,490
So what we're gonna do is we're going to
group all of these

744
00:41:24,490 --> 00:41:26,680
48 commits, all of this 48

745
00:41:26,680 --> 00:41:29,370
insertions, rather, into one single
database transaction.

746
00:41:29,370 --> 00:41:32,320
So the transaction will start, we will
insert 48 pieces

747
00:41:32,320 --> 00:41:35,140
of data, and then we'll finish the
transaction and commit it.

748
00:41:35,140 --> 00:41:37,240
That just saves us a little bit of

749
00:41:37,240 --> 00:41:40,130
overhead because we're writing to the disk
for

750
00:41:40,130 --> 00:41:42,600
the data base, you know, it's going to

751
00:41:42,600 --> 00:41:45,830
be completely negligible, the performance
hit on this app.

752
00:41:45,830 --> 00:41:47,820
But when you're working with large amounts
of data you

753
00:41:47,820 --> 00:41:51,640
may see a little bit of performance
degradation, and another thing

754
00:41:51,640 --> 00:41:54,510
we wanna do when working with sequel
databases with larger

755
00:41:54,510 --> 00:41:58,070
sets of data is make sure we, we do
asynchronous operations.

756
00:41:58,070 --> 00:42:01,670
So just like when we're getting data or
information from the web, and

757
00:42:01,670 --> 00:42:02,930
we may have some delay introduced

758
00:42:02,930 --> 00:42:05,560
there, we do things asynchronously with
callbacks.

759
00:42:05,560 --> 00:42:08,420
Well we'll do the same thing with database
operations a lot of time.

760
00:42:08,420 --> 00:42:11,360
We'll execute, asynchronously and then
when it's all done, we'll call

761
00:42:11,360 --> 00:42:14,970
back to our code and say, okay data's
ready, go on.

762
00:42:14,970 --> 00:42:18,270
So, the first thing we need is to begin
our transaction.

763
00:42:18,270 --> 00:42:20,480
So, let's begin the transaction and then
end the

764
00:42:20,480 --> 00:42:22,430
transaction, and then we'll go back and
add our loop.

765
00:42:23,640 --> 00:42:27,490
So if we use our database object, and
call, begin transaction and

766
00:42:29,850 --> 00:42:32,660
then down at the end, we want to end our
transaction.

767
00:42:34,570 --> 00:42:38,050
Okay, so we can right our loop inside
here, so

768
00:42:38,050 --> 00:42:41,200
let's do a foreloop, We'll do a for each,
and this

769
00:42:41,200 --> 00:42:43,990
is for each I'm not going to go into too

770
00:42:43,990 --> 00:42:49,230
much detail about this is the model
structure for our data.

771
00:42:49,230 --> 00:42:52,230
We'll call this, our, and we'll use the
array

772
00:42:52,230 --> 00:42:55,470
from the forecast, it's called forecast
dot hourly dot data.

773
00:42:56,560 --> 00:42:59,610
This corresponds, again if you want to
see, if you have questions

774
00:42:59,610 --> 00:43:03,770
about this parsing, certainly feel free to
ask in the forum afterwards.

775
00:43:06,320 --> 00:43:08,550
But since that isn't the focus here, we're
just focused on the SQL but there's

776
00:43:11,180 --> 00:43:12,740
one more thing we need to do here.

777
00:43:15,040 --> 00:43:18,120
Because something might go wrong we wanna
put a Try catch black here.

778
00:43:18,120 --> 00:43:21,610
We wanna make sure we, end our transaction
no matter what.

779
00:43:21,610 --> 00:43:25,800
So instead of just, in case anything was,
was, any exceptions were

780
00:43:25,800 --> 00:43:29,930
thrown, we want to make sure we have a
finally block that guarantees

781
00:43:29,930 --> 00:43:32,870
that the transaction will be closed off
because again, we don't wanna leave

782
00:43:32,870 --> 00:43:34,710
the database in that in between

783
00:43:34,710 --> 00:43:37,390
state where things didn't complete as
anticipated.

784
00:43:38,900 --> 00:43:42,170
And then, so this is that transaction
itself, and then we wanna

785
00:43:42,170 --> 00:43:47,360
trigger that commit that I mentioned and
we do that with M database.set

786
00:43:47,360 --> 00:43:51,260
transaction is successful, so our set
transaction is successful, so notice we're

787
00:43:51,260 --> 00:43:54,200
doing this here at the end, so after the
entire four loop executes.

788
00:43:54,200 --> 00:43:56,400
We come down here and set the transaction

789
00:43:56,400 --> 00:43:58,610
as successful, and then we close that
transaction.

790
00:43:59,850 --> 00:44:04,460
So let's this should now insert, oh, but
we haven't updated the temperature.

791
00:44:04,460 --> 00:44:10,290
So this just gotta be to insert 75 48
times, instead we have that hour variable.

792
00:44:10,290 --> 00:44:14,060
And from there we can get the specific
temperature, which is a double value.

793
00:44:14,060 --> 00:44:18,440
And that double decimal value corresponds
to the real value in the database.

794
00:44:18,440 --> 00:44:20,910
So if we run this, we should be able to
see

795
00:44:23,490 --> 00:44:25,280
in the app we'll execute the Insert.

796
00:44:29,980 --> 00:44:31,030
Okay.

797
00:44:31,030 --> 00:44:32,865
Oh and I forgot to add some code that

798
00:44:32,865 --> 00:44:35,140
re-enables these buttons when we come in
and there

799
00:44:35,140 --> 00:44:36,670
is the data in the database, there was a,

800
00:44:36,670 --> 00:44:39,100
they had a check for an existing data
method.

801
00:44:39,100 --> 00:44:40,950
But that way these would be enabled
because

802
00:44:40,950 --> 00:44:42,650
we do have data in the database now.

803
00:44:42,650 --> 00:44:44,860
And it was just a simple select, count,
star from

804
00:44:44,860 --> 00:44:47,230
the database to make sure there were rows
in there.

805
00:44:47,230 --> 00:44:49,960
Okay, so I can retrigger it with the
insert.

806
00:44:49,960 --> 00:44:53,240
And if we go back to our command line,
let's take a look at this.

807
00:44:53,240 --> 00:44:56,340
Unfortunately, I can't just type the up
arrow like in a console.

808
00:44:56,340 --> 00:44:57,490
So, I have to retype this sequel.

809
00:44:58,810 --> 00:45:01,460
But, if we select star, there we go, 48
rows.

810
00:45:02,710 --> 00:45:05,340
well, 50 is because we had existing rows
in there already.

811
00:45:06,810 --> 00:45:07,310
Okay.

812
00:45:08,320 --> 00:45:11,050
So there's a, different types of insert,
we inserted

813
00:45:11,050 --> 00:45:13,080
one row, and then we did a bulk insert.

814
00:45:13,080 --> 00:45:16,450
So, this is fine, and we can view things
in the terminal

815
00:45:16,450 --> 00:45:18,740
but it'd be nice to view that data in our
app, itself.

816
00:45:18,740 --> 00:45:22,680
So let's see how to select that data from
the database, and display it in the app.

817
00:45:22,680 --> 00:45:24,390
So, to do that, we have a separate

818
00:45:24,390 --> 00:45:28,290
activity that's the select button on the
main activity.

819
00:45:28,290 --> 00:45:30,210
And if we go up here and look at the on
click

820
00:45:30,210 --> 00:45:33,880
we see that we're just starting a new
activity for view forecast activity.

821
00:45:35,100 --> 00:45:37,570
So view forecast activity is a simplisitic
activity,

822
00:45:39,090 --> 00:45:40,680
and what it does is it's going to

823
00:45:40,680 --> 00:45:43,000
execute a query, select all the data from

824
00:45:43,000 --> 00:45:45,740
the database and then display in a simple
list.

825
00:45:45,740 --> 00:45:47,560
There are different ways to do this, this
is a

826
00:45:47,560 --> 00:45:50,430
very basic way again if you're using a
content provider

827
00:45:50,430 --> 00:45:53,200
you can use a thing called a cursor
adapter to

828
00:45:53,200 --> 00:45:57,040
adapt things but it's, again we're just
introducing the basics here.

829
00:45:57,040 --> 00:46:00,140
This is just to show you how to get data
in and get data out.

830
00:46:00,140 --> 00:46:03,840
So, much like the main activity, let's do
a few things about getting a data source.

831
00:46:03,840 --> 00:46:08,110
So, we have a protected, forecast data
source, name

832
00:46:08,110 --> 00:46:12,650
and data source and we're going to
instantiate it here

833
00:46:12,650 --> 00:46:18,140
and create data source equals a new
forecast data

834
00:46:18,140 --> 00:46:21,790
source with the context of view, forecast
activity dot this.

835
00:46:21,790 --> 00:46:27,040
Okay, now we want to, open and close in
unpause and unresume.

836
00:46:27,040 --> 00:46:31,210
So end DataSource dot open and end data
source,

837
00:46:33,720 --> 00:46:34,770
dot close.

838
00:46:34,770 --> 00:46:38,360
Sure enough, and here, we don't have any
button or anything to trigger the action.

839
00:46:38,360 --> 00:46:41,350
We're just going to query all the data and
bring it back

840
00:46:41,350 --> 00:46:45,510
to the display every time the activity is,
called with on resume.

841
00:46:45,510 --> 00:46:49,030
So let's, add the code to select
everything from the

842
00:46:49,030 --> 00:46:51,780
database and then once we have that we'll
update the list.

843
00:46:52,860 --> 00:46:53,800
So let's go back.

844
00:46:53,800 --> 00:46:55,880
Again, you'll be familiar with this patter
now.

845
00:46:55,880 --> 00:46:57,330
We're going to go back to our data source

846
00:46:57,330 --> 00:47:00,900
object and that's where we'll write our
select method.

847
00:47:00,900 --> 00:47:04,690
So, the first one, this is where I was
talking about the cursor.

848
00:47:04,690 --> 00:47:06,680
So, we're going to return a cursor to the

849
00:47:06,680 --> 00:47:13,920
results and let's call this Select all
temperatures [SOUND]

850
00:47:13,920 --> 00:47:16,300
and we don't need to pass anything into

851
00:47:16,300 --> 00:47:20,120
this one, but we wanna create a cursor
variable.

852
00:47:21,680 --> 00:47:29,880
Call it Cursor, and we're going to execute
a query method from our database object.

853
00:47:29,880 --> 00:47:33,010
So this is again instead of writing the

854
00:47:33,010 --> 00:47:35,220
raw sequel we're going to use the query
method.

855
00:47:36,320 --> 00:47:40,210
We'll return the cursor when we're done,
and, I'm going

856
00:47:40,210 --> 00:47:44,130
to break this, the parameters for this
method on multiple lines.

857
00:47:44,130 --> 00:47:48,290
So we can see exactly what we're doing, so
the first thing we need is the data table

858
00:47:48,290 --> 00:47:50,520
we're querying because we're just working
with one data

859
00:47:50,520 --> 00:47:52,750
table, but we can create multiple tables
as well.

860
00:47:52,750 --> 00:47:54,980
So we could have temperature, we could
have a separate

861
00:47:54,980 --> 00:47:59,360
table for wind conditions, or whatever we
want, or locations maybe.

862
00:47:59,360 --> 00:48:01,670
But this is the table name which will

863
00:48:01,670 --> 00:48:04,770
go through our helper and we'll use table
temperatures.

864
00:48:06,420 --> 00:48:06,920
Okay.

865
00:48:08,030 --> 00:48:11,790
And then the second thing is the columns
that we wanna select.

866
00:48:13,160 --> 00:48:18,000
So the format here, instead of just a
single column, we need to list these as an

867
00:48:18,000 --> 00:48:28,260
array of strings, so here, like, if we put
forecast helper dot column temperature.

868
00:48:28,260 --> 00:48:31,650
We would get an error which well, I'll
just go ahead and fix

869
00:48:31,650 --> 00:48:33,270
it now, what we do is just you just pass
in a new

870
00:48:33,270 --> 00:48:38,940
string away, and if we put curly braces
around the string, that way

871
00:48:38,940 --> 00:48:42,230
we get the columns names that we want, and
this is just one column.

872
00:48:42,230 --> 00:48:45,780
But we could separate multiple columns as
individual items in

873
00:48:45,780 --> 00:48:49,490
this array, which themselves would be
separated by columns in here.

874
00:48:49,490 --> 00:48:49,990
Okay.

875
00:48:51,470 --> 00:48:54,780
So now there are a couple more
parameters.But if we just

876
00:48:54,780 --> 00:48:57,420
are selecting everything from a table,
then this is good enough.

877
00:48:57,420 --> 00:49:00,090
We just need to pass in null values for
the rest of the parameters.

878
00:49:00,090 --> 00:49:03,520
So this first one is the where clause.

879
00:49:03,520 --> 00:49:03,830
Whoops.

880
00:49:03,830 --> 00:49:04,540
I forgot a comment.

881
00:49:06,030 --> 00:49:07,170
And what we'll do is we'll come back with
a

882
00:49:07,170 --> 00:49:09,280
separate method and look at some of these
in more detail.

883
00:49:10,780 --> 00:49:14,050
These are the where parameters, and then
we

884
00:49:14,050 --> 00:49:17,390
can do more advanced Sequel operations
like group by.

885
00:49:17,390 --> 00:49:17,890
And

886
00:49:19,820 --> 00:49:21,910
again, we're just passing in null because
we're not worried

887
00:49:21,910 --> 00:49:27,350
about any of these having and finally, the
order by clause.

888
00:49:27,350 --> 00:49:30,060
And if you want to learn more about any

889
00:49:30,060 --> 00:49:34,140
of these SQL constructs, again there's the
database basics course.

890
00:49:34,140 --> 00:49:36,650
Ok so this should give us a cursor, and if
we go

891
00:49:36,650 --> 00:49:40,560
back to the view forecast activity we can
now create a cursor.

892
00:49:40,560 --> 00:49:46,500
Cursor, cursor, equals and then from our
data source.

893
00:49:46,500 --> 00:49:47,730
Recall select all temperatures.

894
00:49:48,950 --> 00:49:53,240
Now, what we're gonna do is just pass this
cursor into a method called update list.

895
00:49:53,240 --> 00:49:54,560
And we have it as a separate method
because

896
00:49:54,560 --> 00:49:57,730
we're gonna update the list in two
different places.

897
00:49:57,730 --> 00:50:01,580
So, if we go down to updateList, you'll be
familiar with the part down here.

898
00:50:01,580 --> 00:50:04,350
All it is, is a simple array adapter of
decimals.

899
00:50:04,350 --> 00:50:06,730
And it's gonna use a simple list item.

900
00:50:06,730 --> 00:50:11,090
It's just gonna show a text view with the
array with the values.

901
00:50:11,090 --> 00:50:12,620
And we're using a variable called
mTemperatures

902
00:50:12,620 --> 00:50:15,280
which is an array of big decimals.

903
00:50:15,280 --> 00:50:19,490
Now we're using big decimal here because
double is not allowed, primitive types

904
00:50:19,490 --> 00:50:24,200
are not allowed for inside generics like
this we need to use a

905
00:50:24,200 --> 00:50:27,280
wrapper class which is why we have big
decimal it's just a conversion

906
00:50:27,280 --> 00:50:30,219
back and forth, but it gets us the same
data that we want.

907
00:50:31,830 --> 00:50:33,420
So, what we need to do here is

908
00:50:33,420 --> 00:50:36,550
loop through the cursor to populate our
temperatures.

909
00:50:38,200 --> 00:50:43,340
So, we start, the cursor is tied to our
results set but we need, the

910
00:50:43,340 --> 00:50:46,100
first thing we need to do is move it to
the beginning of the result set.

911
00:50:46,100 --> 00:50:50,390
So we say, cursor.moveToFirst, then we're
gonna loop

912
00:50:50,390 --> 00:50:53,010
through the entire result set using a wow
loop.

913
00:50:53,010 --> 00:50:59,530
And here we're gonna use a method well,
cursor is after last.

914
00:50:59,530 --> 00:51:00,030
So

915
00:51:02,500 --> 00:51:04,660
let's, let's see, we'll do stuff in here

916
00:51:04,660 --> 00:51:11,030
and then we're gonna move the cursor with
cursor.moveNext.

917
00:51:11,030 --> 00:51:12,930
Kay, so we, we move into the first one.

918
00:51:12,930 --> 00:51:14,380
We come, we enter the while loop, it's not

919
00:51:14,380 --> 00:51:16,110
after the last, because we're still at the
first one.

920
00:51:17,220 --> 00:51:19,670
We're gonna get the values we need, and
then we'll move

921
00:51:19,670 --> 00:51:21,900
the cursor, and then redo the loop until
we're all done.

922
00:51:22,990 --> 00:51:25,300
Okay, so what do we wanna do in here,
where we're doing stuff?

923
00:51:27,890 --> 00:51:32,320
Let's start with an index, so the way the
cursor accesses.

924
00:51:32,320 --> 00:51:36,190
Well, let's, let's do this, the cursor we,
we get

925
00:51:36,190 --> 00:51:38,930
things based off the index, the indices of
the columns.

926
00:51:38,930 --> 00:51:42,870
So we're after a double value and it's the
temperature.

927
00:51:42,870 --> 00:51:43,370
And

928
00:51:46,740 --> 00:51:49,720
if we use the cursor we can get all
different datatypes.

929
00:51:49,720 --> 00:51:51,694
So these correspond to the different
datatypes,

930
00:51:51,694 --> 00:51:53,770
we can use in our SQLite database.

931
00:51:53,770 --> 00:51:56,730
We're getting the double, double based on
the column index.

932
00:51:56,730 --> 00:52:00,160
So to get the column index, now we know
that our

933
00:52:00,160 --> 00:52:04,650
column index is zero because it's the only
column in the database.

934
00:52:04,650 --> 00:52:06,840
well, we have the ID column but that one
is an

935
00:52:06,840 --> 00:52:09,460
index so it starts at zero for the columns
that we define.

936
00:52:10,460 --> 00:52:14,320
So we could just pass on zero here but
instead, let's get it this

937
00:52:14,320 --> 00:52:16,920
is a little bit cleaner because we may
change the order of our column.

938
00:52:16,920 --> 00:52:20,180
So we wanna avoid hard coding the numbers
if possible.

939
00:52:20,180 --> 00:52:25,500
So instead we have an index, and i equals
cursor, and low and

940
00:52:25,500 --> 00:52:31,102
behold, there is a getColumnIndex based on
a name, and the name is forecast helper.,

941
00:52:31,102 --> 00:52:36,230
whoops, helper.COLUMN_TEMPERATURE.

942
00:52:36,230 --> 00:52:39,190
That gets us the appropriate temperature
and now we

943
00:52:39,190 --> 00:52:42,824
can just add this temperature to our array
of mTemperatures.

944
00:52:43,870 --> 00:52:45,385
So we're adding a bigdecimal.

945
00:52:46,520 --> 00:52:49,850
We need a new big decimal from the double
value temperature OK

946
00:52:51,510 --> 00:52:55,290
and that gets us what we need However,
when you're converting a double

947
00:52:55,290 --> 00:52:57,720
to a big decimal, if you do it just like
this with

948
00:52:57,720 --> 00:53:01,740
a simple constructor you end up with a
whole bunch of decimal places.

949
00:53:01,740 --> 00:53:04,280
So it's gonna fill the entire listview and
it looks silly, we don't

950
00:53:04,280 --> 00:53:08,100
care about that precision cuz we're only
getting data back in two decimal places.

951
00:53:08,100 --> 00:53:11,490
So a simple fix is to add a second
parameter, different constructor And

952
00:53:11,490 --> 00:53:16,330
from a, a class called Math Context, we
can use a decimal format.

953
00:53:16,330 --> 00:53:20,440
And this will just format our data in a
little more of a presentable manner.

954
00:53:22,420 --> 00:53:25,060
And that should, update our list.

955
00:53:25,060 --> 00:53:28,790
That's all we need here, we're looping
through the cursor, setting the adapter.

956
00:53:28,790 --> 00:53:30,320
So lets run it and verify.

957
00:53:32,890 --> 00:53:34,030
Okay we'll wait for jenny motion.

958
00:53:34,030 --> 00:53:34,750
Just a moment.

959
00:53:34,750 --> 00:53:39,890
[BLANK_AUDIO]

960
00:53:39,890 --> 00:53:40,180
Okay.

961
00:53:40,180 --> 00:53:43,540
And, the, there's data already in the
system so we should be able to click

962
00:53:43,540 --> 00:53:48,210
on select but again I, I forgot to add
that method to this prototype code.

963
00:53:48,210 --> 00:53:51,240
So, we have to reinsert new data and it's
just gonna keep a pending data into

964
00:53:51,240 --> 00:53:55,510
our database which is fine because once we
get to the end we're gonna delete it all.

965
00:53:55,510 --> 00:54:00,440
So now that we have the button enabled, if
we click on select then there is all the

966
00:54:00,440 --> 00:54:06,210
data that corresponds to what we saw in
the SQL Light tool in the command line.

967
00:54:07,260 --> 00:54:07,760
Okay.

968
00:54:08,850 --> 00:54:10,020
So that was getting everything.

969
00:54:10,020 --> 00:54:14,300
But also, very common, we are going to
select specific data.

970
00:54:14,300 --> 00:54:17,020
You know, whether it's temperature for a
specific

971
00:54:17,020 --> 00:54:20,490
hour or again, like, information about a
specific user.

972
00:54:20,490 --> 00:54:23,550
So we want to use a where clause to limit
the data we get back.

973
00:54:23,550 --> 00:54:25,950
So what I've done is, I've added a field,

974
00:54:25,950 --> 00:54:28,240
an edit text back here up in the action
bar.

975
00:54:28,240 --> 00:54:31,090
And when enter a temperature, it won't
doing anything right now.

976
00:54:31,090 --> 00:54:32,080
But when we enter a temperature, it's

977
00:54:32,080 --> 00:54:34,560
going to filter out anything below that
temperature.

978
00:54:35,570 --> 00:54:38,590
So let's go back to our data source and
add the method.

979
00:54:39,690 --> 00:54:41,040
And this going to be, it's going to return
a

980
00:54:41,040 --> 00:54:44,700
cursor because we're still going to have a
results set.

981
00:54:44,700 --> 00:54:45,890
And let's call this,

982
00:54:48,440 --> 00:54:54,430
select all temps, SelectTempsGreaterThan
and then we'll pass

983
00:54:54,430 --> 00:54:59,440
in a, we're gonna use string value let me
just change that to make things easier.

984
00:54:59,440 --> 00:55:03,320
I'm not worried about type checking here
although in a production app we wanna

985
00:55:03,320 --> 00:55:04,320
make sure that we're actually getting the

986
00:55:04,320 --> 00:55:06,880
number, this is just just for speed's
sake.

987
00:55:07,890 --> 00:55:09,590
Okay, so let's start with a cursor.

988
00:55:09,590 --> 00:55:12,420
Actually, what we're gonna do is copy and
paste because all we're gonna, we're gonna

989
00:55:12,420 --> 00:55:14,140
use the same format here, but we're going

990
00:55:14,140 --> 00:55:17,830
to add a where clause and where
parameters.

991
00:55:17,830 --> 00:55:21,010
So let's copy everything here and paste it
below.

992
00:55:22,970 --> 00:55:26,180
And the where clause is going to be a
string.

993
00:55:26,180 --> 00:55:30,310
Let's call it whereClause, and we'll
define it up here.

994
00:55:30,310 --> 00:55:33,980
We could define it here in the method, but
it just gets, it starts to

995
00:55:33,980 --> 00:55:38,485
get a little too hard to understand so
let's add it up here, String whereClause.

996
00:55:41,220 --> 00:55:42,460
And let's write the where clause we

997
00:55:42,460 --> 00:55:46,590
want and then were gonna parameterize it
appropriately.

998
00:55:46,590 --> 00:55:49,990
So here were going to select all
temperatures

999
00:55:49,990 --> 00:55:53,960
where temperature is greater then the
minimum temp.

1000
00:55:53,960 --> 00:55:59,050
So where TEMPERATURE is greater then
minTemp, easy enough.

1001
00:55:59,050 --> 00:56:01,156
So let's swap out TEMPERATURE

1002
00:56:01,156 --> 00:56:05,310
with from our ForecastHelper, our
COLUMN_TEMPERATURE.

1003
00:56:07,590 --> 00:56:10,660
And let's swap out minTemp with a question
mark.

1004
00:56:10,660 --> 00:56:14,270
That is the placeholder value where we can
add our parameters afterwards.

1005
00:56:14,270 --> 00:56:17,800
So, here, we see the where parameters, we
can put minTemp here.

1006
00:56:17,800 --> 00:56:21,030
Sure, we could append it in a string this
is just showing different ways to do it.

1007
00:56:22,050 --> 00:56:26,760
But Just like we had to use a string array
for column names.

1008
00:56:26,760 --> 00:56:30,650
We have to use a string array for the
where parameters.

1009
00:56:30,650 --> 00:56:35,000
So, let's type, new String, and we'll put
minTemp as the only item.

1010
00:56:36,230 --> 00:56:40,090
Inside and we won't do anything else with
group

1011
00:56:40,090 --> 00:56:42,810
I having order by, but now we can use this

1012
00:56:42,810 --> 00:56:50,150
method back in our viewForecastActivity
and that code for the action bar

1013
00:56:50,150 --> 00:56:55,054
is down here in a method called
configureActionBar.

1014
00:56:55,054 --> 00:56:57,650
And I'm not gonna go in too much detail
about this

1015
00:56:57,650 --> 00:57:00,580
if you have any questions, ask me in the
forum afterwards.

1016
00:57:00,580 --> 00:57:02,720
But you can see we have an edit text, and
from that

1017
00:57:02,720 --> 00:57:03,650
edit text we're gonna use it

1018
00:57:03,650 --> 00:57:06,530
as the parameter for method called
filterTemperatures.

1019
00:57:06,530 --> 00:57:08,690
And that's where we will do our magic
work.

1020
00:57:08,690 --> 00:57:09,120
We will

1021
00:57:09,120 --> 00:57:09,465
call

1022
00:57:09,465 --> 00:57:11,520
mDataSource.selectTemperaturesGreaterThan
and

1023
00:57:11,520 --> 00:57:14,676
we'll just pass in that minTemp.

1024
00:57:14,676 --> 00:57:19,180
And when we're done we're gonna update the
list with that cursor, oh, I forgot to

1025
00:57:19,180 --> 00:57:26,050
declare a cursor variable cursor equals
the result from that.

1026
00:57:27,160 --> 00:57:29,480
So this filterTemperatures will be called
when

1027
00:57:29,480 --> 00:57:32,480
we hit Enter set out of,
setOnEditorActionListener.

1028
00:57:32,480 --> 00:57:35,970
When I hit Enter will use the value that
was entered in that edit text.

1029
00:57:37,000 --> 00:57:37,620
So let's run it.

1030
00:57:37,620 --> 00:57:38,150
So take a look.

1031
00:57:40,220 --> 00:57:40,410
Okay.

1032
00:57:40,410 --> 00:57:42,565
We'll give Genymotion a moment to reload.

1033
00:57:42,565 --> 00:57:45,171
[SOUND].

1034
00:57:45,171 --> 00:57:46,800
Okay.

1035
00:57:46,800 --> 00:57:49,150
And again, we're gonna have to insert more
data.

1036
00:57:49,150 --> 00:57:51,910
So we're just tripling or quadrupling our
data now.

1037
00:57:53,070 --> 00:57:55,850
Now, if hit on SELECT, we get the list
view of everything.

1038
00:57:55,850 --> 00:57:57,350
And now, let's see.

1039
00:57:57,350 --> 00:57:58,220
We've got temperatures in the 70s.

1040
00:57:58,220 --> 00:58:00,990
Let's see if there's anything greater than
75 degrees.

1041
00:58:02,190 --> 00:58:04,690
There's a few temperatures you see it was
filtered if we

1042
00:58:04,690 --> 00:58:08,900
put a high temperature of a 100 there
shouldn't be anything, right?

1043
00:58:08,900 --> 00:58:11,960
And if we do a low temperature freezing 32
then

1044
00:58:11,960 --> 00:58:14,540
everything is the same because there is
nothing lower then 32.

1045
00:58:14,540 --> 00:58:17,980
Okay, so that shows how we got two
examples here of

1046
00:58:17,980 --> 00:58:20,990
a select all and then a select with a
where clause.

1047
00:58:20,990 --> 00:58:24,250
So the rest are that's really you know
this

1048
00:58:24,250 --> 00:58:27,240
is the meat of using the SQL database
here.

1049
00:58:27,240 --> 00:58:30,650
The update and delete are, should be
trivial based on these examples, but

1050
00:58:30,650 --> 00:58:33,320
let's walk through them and make sure that
we understand everything that's going on.

1051
00:58:36,370 --> 00:58:40,650
For the update, we have an update button,
let's see what do we have it doing.

1052
00:58:42,452 --> 00:58:44,950
It's just an onClick and we're gonna call
it update method.

1053
00:58:44,950 --> 00:58:49,200
Let's just update everything to in the
interest of time,

1054
00:58:49,200 --> 00:58:51,320
we'll update all values to 100, it'll be a
heatwave.

1055
00:58:52,840 --> 00:58:57,720
So, back to our ForecastDataSource, we're
gonna do our update method.

1056
00:58:57,720 --> 00:59:02,130
Now, the update returns the number of rows
that have been updated.

1057
00:59:02,130 --> 00:59:05,186
So instead of a cursor, we're just gonna
return an int.

1058
00:59:05,186 --> 00:59:14,020
And, let's call this updateTemperature,
and we'll have a double for the newTemp.

1059
00:59:20,030 --> 00:59:24,820
Okay so because if you go back up to the

1060
00:59:24,820 --> 00:59:28,220
insertion, we used our content values to
insert the due data.

1061
00:59:28,220 --> 00:59:30,000
Well we're gonna do the same thing for
updating.

1062
00:59:30,000 --> 00:59:35,220
So let's declare ContentValues, call it
values equals new ContentValues.

1063
00:59:37,450 --> 00:59:40,650
Okay, and let's put in the new value.

1064
00:59:40,650 --> 00:59:48,940
So this is gonna be once again for
ForecastHelper.COLUMN_TEMPERATURE,

1065
00:59:48,940 --> 00:59:50,930
and the value is gonna be that newtemp
that we're passing in.

1066
00:59:52,710 --> 00:59:55,595
Oops, I forgot to add the method called
put.

1067
00:59:55,595 --> 00:59:58,110
Okay putting those new values in.

1068
00:59:58,110 --> 01:00:02,530
And then from the database, we're gonna
call

1069
01:00:02,530 --> 01:00:07,750
the update method, and this is similar to

1070
01:00:10,670 --> 01:00:12,490
similar to the other methods we're using,
but like

1071
01:00:12,490 --> 01:00:15,730
I mentioned, it returns [COUGH] an integer
of rows updated.

1072
01:00:17,170 --> 01:00:19,580
So let's set that equal there.

1073
01:00:19,580 --> 01:00:21,550
We'll return, rowsUpdated.

1074
01:00:21,550 --> 01:00:24,540
And then we'll fill in the blanks for this
update method.

1075
01:00:24,540 --> 01:00:28,290
So as usual, you can guess the first thing
we need is the table name.

1076
01:00:31,950 --> 01:00:35,690
And then the second is the values that
we're

1077
01:00:35,690 --> 01:00:37,600
going to update, and we'll pass in the
content values.

1078
01:00:38,760 --> 01:00:40,770
And the third, we're gonna pass in null,
it's a where clause.

1079
01:00:44,760 --> 01:00:46,540
And finally it's if we had an where
clause,

1080
01:00:46,540 --> 01:00:52,630
we would have parameters and passing null
there as well.

1081
01:00:53,630 --> 01:01:00,760
Okay, I think that's every thing we need,
so let's go back to main activity.

1082
01:01:00,760 --> 01:01:02,700
To the OnClickListener for the update
button.

1083
01:01:04,060 --> 01:01:08,710
And all I'll do is call
mDataSource.updateTemperatures

1084
01:01:08,710 --> 01:01:12,493
and we're gonna update everything to 100.

1085
01:01:12,493 --> 01:01:14,773
What we'll do is run the update and then
click on

1086
01:01:14,773 --> 01:01:18,235
the select button to make sure that all
the temperatures have changed.

1087
01:01:18,235 --> 01:01:22,057
[BLANK_AUDIO]

1088
01:01:22,057 --> 01:01:25,137
Okay, so here comes our app, we'll
reinsert, now

1089
01:01:25,137 --> 01:01:28,637
we'll UPDATE, okay, no visual indicator
here, but if we

1090
01:01:28,637 --> 01:01:31,927
go to SELECT, everything is 100, so
everything was

1091
01:01:31,927 --> 01:01:35,480
updated to 200 because, we didn't have any
filter in.

1092
01:01:35,480 --> 01:01:37,170
So if we wanted to filter, if we only
wanted to

1093
01:01:37,170 --> 01:01:40,710
update specific rows, we could use that ID
column that we added.

1094
01:01:40,710 --> 01:01:43,110
We'd have to get it first, let's just get
the appropriate

1095
01:01:43,110 --> 01:01:45,300
ID, then we can update the value for just
that ID.

1096
01:01:49,130 --> 01:01:50,460
And we won't walk though an example,

1097
01:01:50,460 --> 01:01:52,740
because it should be pretty straight
forward.

1098
01:01:52,740 --> 01:01:55,050
So you have an example now of how to add a
where clause.

1099
01:01:55,050 --> 01:01:59,990
You do the same thing down here in the in
the update statement.

1100
01:01:59,990 --> 01:02:06,800
Okay, so let's take a look at deleting one
more method, public void deleteAll.

1101
01:02:06,800 --> 01:02:08,819
We're just gonna clear everything from the
database.

1102
01:02:10,460 --> 01:02:15,680
And, it's a pretty simple method from the
database object,

1103
01:02:15,680 --> 01:02:20,555
we just call delete and let's add our
table,

1104
01:02:20,555 --> 01:02:25,960
ForecastHelper.Table_TEMPERATURES

1105
01:02:25,960 --> 01:02:31,400
and we could have a where call, where
clause we'll just pass in null.

1106
01:02:34,600 --> 01:02:36,310
And go to parameters.

1107
01:02:37,900 --> 01:02:39,710
I forgot this comment here.

1108
01:02:39,710 --> 01:02:42,048
I'm just leaving these notes for reference
for later on.

1109
01:02:42,048 --> 01:02:45,683
[SOUND].

1110
01:02:45,683 --> 01:02:49,770
Okay, and then we just need to call this
from

1111
01:02:49,770 --> 01:02:53,130
the onClickListener for the delete button,
so let's go back here.

1112
01:02:55,000 --> 01:02:57,270
Scroll up just a little bit and we'll
replace

1113
01:02:57,270 --> 01:03:01,265
this final to do, woops, clicking on the
wrong spot.

1114
01:03:01,265 --> 01:03:05,450
Android Studio condenses things just to
make, make it

1115
01:03:05,450 --> 01:03:07,620
easier to look at a glance what's going
on.

1116
01:03:07,620 --> 01:03:12,110
You just click on the gray part to, to
show it like this.

1117
01:03:12,110 --> 01:03:15,170
So from the data source, let's call delete
all.

1118
01:03:16,650 --> 01:03:17,610
And run it to verify.

1119
01:03:21,400 --> 01:03:25,950
And once the emulator reloads,
unfortunately, DELETE is grayed

1120
01:03:25,950 --> 01:03:28,900
out, so we'll reinsert data, and now
delete it.

1121
01:03:30,210 --> 01:03:31,650
And now if we click on SELECT, we can

1122
01:03:31,650 --> 01:03:34,660
view it, and sure enough, there's nothing
in there.

1123
01:03:34,660 --> 01:03:37,168
We try and filter, there's nothing to
show.

1124
01:03:37,168 --> 01:03:39,064
Okay.

1125
01:03:39,064 --> 01:03:42,890
So that's a simple overview of the basic

1126
01:03:42,890 --> 01:03:44,660
operations you're going to do on a
database.

1127
01:03:44,660 --> 01:03:48,690
That covers a lot of scenarios you're
gonna come across, your, and that's why

1128
01:03:48,690 --> 01:03:50,430
it's, it's an acronym that is common

1129
01:03:50,430 --> 01:03:52,890
across programming languages, create,
retrieve, update, delete.

1130
01:03:54,070 --> 01:03:55,550
One last thing I do wanna cover, though,
before

1131
01:03:55,550 --> 01:03:59,000
we move on, because it's important, is
upgrading the database.

1132
01:03:59,000 --> 01:04:02,000
So I'm just gonna do another example of
how to, how to do that.

1133
01:04:02,000 --> 01:04:04,030
And then show a few tools, and then we'll
be all set.

1134
01:04:05,110 --> 01:04:07,070
So for the upgrade, we wanna go back to

1135
01:04:07,070 --> 01:04:09,620
that ForecastHelper class that we haven't
touched in a while.

1136
01:04:10,730 --> 01:04:13,629
And we have this empty onUpgrade method
down here that we haven't used.

1137
01:04:15,750 --> 01:04:19,040
What happens is, let me switch to Chrome
for a

1138
01:04:19,040 --> 01:04:21,230
moment because I've got a article that
I'll link to

1139
01:04:24,630 --> 01:04:25,650
No I don't, okay.

1140
01:04:25,650 --> 01:04:27,400
Well I'll link to it another time in the
notes.

1141
01:04:28,820 --> 01:04:34,030
But the, when your app calls that onCreate
it checks

1142
01:04:34,030 --> 01:04:37,310
that version number that we're setting
here on line 17.

1143
01:04:37,310 --> 01:04:39,440
And as long as that version number is the
same, then

1144
01:04:39,440 --> 01:04:42,080
it's going to reuse the same database over
and over again.

1145
01:04:42,080 --> 01:04:43,950
But, as soon as that version number
changes,

1146
01:04:45,030 --> 01:04:47,970
then it will call the onUpgrade version
instead.

1147
01:04:47,970 --> 01:04:51,360
And that's how you can trigger an update
with a new version of your app.

1148
01:04:51,360 --> 01:04:53,740
So if we're releasing version 2.0 in our
app, we have whole new

1149
01:04:53,740 --> 01:04:55,250
database schema that we want, then

1150
01:04:55,250 --> 01:04:58,720
we're gonna increment this database
version number.

1151
01:04:58,720 --> 01:05:02,210
And when the user installs the update, and
the first

1152
01:05:02,210 --> 01:05:03,770
time they run it, it's going to upgrade
the database.

1153
01:05:03,770 --> 01:05:05,470
And it's up to us to make sure all

1154
01:05:05,470 --> 01:05:08,420
their existing data migrates over, if
that's what we want.

1155
01:05:08,420 --> 01:05:09,310
So, that's what we're gonna do here.

1156
01:05:09,310 --> 01:05:10,550
We're gonna migrate existing data.

1157
01:05:11,760 --> 01:05:15,210
And we're gonna do it with an ALTER
statement.

1158
01:05:15,210 --> 01:05:20,704
So let's add a new private static final
String, DB_ALTER.

1159
01:05:23,440 --> 01:05:28,820
And like before, let's write it in plain
SQL and then we'll add the parameters.

1160
01:05:28,820 --> 01:05:30,820
So we have an ALTER table.

1161
01:05:34,080 --> 01:05:38,156
[BLANK_AUDIO]

1162
01:05:38,156 --> 01:05:44,800
And join a blank on the alter syntax.

1163
01:05:44,800 --> 01:05:45,990
Okay, that's right.

1164
01:05:45,990 --> 01:05:48,490
ALTER TABLE with the table name
TEMPERATURES.

1165
01:05:49,850 --> 01:05:53,140
And then we're going to add a column with
a

1166
01:05:53,140 --> 01:05:57,067
new name TIME and it's gonna be of type
INTEGER.

1167
01:05:57,067 --> 01:06:02,457
The reason we're using integer for time is
because if we look at the data returned by

1168
01:06:02,457 --> 01:06:04,767
the API it's returned in a Unix time

1169
01:06:04,767 --> 01:06:08,643
format, which is second since certain time
in 1970.

1170
01:06:08,643 --> 01:06:11,499
So this is a, there are utilities converts
this to

1171
01:06:11,499 --> 01:06:14,559
human readable time, but we'll start on
the back end

1172
01:06:14,559 --> 01:06:17,959
as an integer and that's what, that's how
SQLite because

1173
01:06:17,959 --> 01:06:21,251
it's a light version that's how it stores
time data.

1174
01:06:21,251 --> 01:06:24,067
Okay, so let's just plug in the
appropriate values

1175
01:06:24,067 --> 01:06:28,830
here, so instead of TEMPERATURES let's add
the TABLE_TEMPERATURES variable.

1176
01:06:30,320 --> 01:06:33,620
Instead of the COLUMN TIME let's add a new
variable.

1177
01:06:36,080 --> 01:06:39,447
We call it COLUMN_TIME and just like we
have

1178
01:06:39,447 --> 01:06:44,612
COLUMN_TEMPERATURE, let's copy and paste
and make that time.

1179
01:06:44,612 --> 01:06:50,221
[BLANK_AUDIO]

1180
01:06:50,221 --> 01:06:51,883
Okay.

1181
01:06:51,883 --> 01:06:57,236
And to execute on ALTER statement, we use
the same exact method

1182
01:06:57,236 --> 01:07:02,337
db.execSQL and instead of DB_CREATE we
have DB_ALTER.

1183
01:07:02,337 --> 01:07:05,687
Now before we just run this though what
would happen if a

1184
01:07:05,687 --> 01:07:10,180
new user installed version 2.0 of our app
who didn't have version 1.0.

1185
01:07:10,180 --> 01:07:15,840
If we don't make sure to also upgrade that
DB_CREATE statement then they're gonna run

1186
01:07:15,840 --> 01:07:18,010
the old version and it's not gonna work

1187
01:07:18,010 --> 01:07:19,620
with our, the new code that we're
providing.

1188
01:07:19,620 --> 01:07:23,402
So we wanna make sure we update, if we're
ever providing new upgrade

1189
01:07:23,402 --> 01:07:28,120
statement, we wanna make sure that we're
providing a new create statement as well.

1190
01:07:28,120 --> 01:07:33,832
So let's change the create statement,
we're just going to append here, after

1191
01:07:33,832 --> 01:07:39,550
COLUMN TEMPERATURE, add a comma, space
plus, and then we have COLUMN_TIME.

1192
01:07:40,970 --> 01:07:44,570
Plus and it's a type INTEGER, and now our

1193
01:07:44,570 --> 01:07:49,680
Create Table matches the upgrade statement
from the alter statement.

1194
01:07:49,680 --> 01:07:51,802
Okay, so that's all we need to do.

1195
01:07:51,802 --> 01:07:55,730
well, no it's not because this is the
important part that I, I kept stressing.

1196
01:07:55,730 --> 01:08:01,710
We need to we must increment to trigger,
an upgrade.

1197
01:08:01,710 --> 01:08:04,690
So instead of version one, we are now
dealing with version two.

1198
01:08:04,690 --> 01:08:05,190
So

1199
01:08:07,130 --> 01:08:08,118
now let's try it and see.

1200
01:08:08,118 --> 01:08:11,690
And we're gonna go back in to the command
line in a moment, and just verify the

1201
01:08:11,690 --> 01:08:14,990
data structure, data structure change,
we're not going

1202
01:08:14,990 --> 01:08:16,880
to write any additional code, to look at
it.

1203
01:08:16,880 --> 01:08:20,940
[BLANK_AUDIO]

1204
01:08:20,940 --> 01:08:23,920
Here in the code should, well it should

1205
01:08:23,920 --> 01:08:26,980
have been executed right away in the
onCreate method.

1206
01:08:26,980 --> 01:08:34,530
So if we go to the terminal and we can do
schema for temperatures.

1207
01:08:37,170 --> 01:08:39,010
And sure enough, you see that the schema
changed.

1208
01:08:39,010 --> 01:08:42,290
And now in addition to the temperature
itself,

1209
01:08:42,290 --> 01:08:44,370
we have the time in the integer format.

1210
01:08:44,370 --> 01:08:47,660
But if we select star from temperatures

1211
01:08:50,000 --> 01:08:51,370
you see that this blank values.

1212
01:08:51,370 --> 01:08:54,060
Because we didn't actually insert the
time, we haven't changed our code.

1213
01:08:54,060 --> 01:08:57,390
But you can see that the the column ID has
been incrementing.

1214
01:08:57,390 --> 01:08:59,020
We still have the temperatures, and now we
just have a

1215
01:08:59,020 --> 01:09:02,210
blank value, and we didn't have these
vertical pipes to separate before.

1216
01:09:02,210 --> 01:09:04,820
It's just a visual cue, to let us know
there's an additional column there.

1217
01:09:04,820 --> 01:09:07,640
[BLANK_AUDIO]

1218
01:09:07,640 --> 01:09:11,040
Okay, so that's all I wanted to cover in
the app itself.

1219
01:09:11,040 --> 01:09:13,610
So again, it's an overview of the basic
C.R.U.D operations,

1220
01:09:13,610 --> 01:09:15,500
an upgrade, how to create and set up your
database.

1221
01:09:16,680 --> 01:09:18,100
But, as you do more and more with SQL

1222
01:09:18,100 --> 01:09:21,020
databases, you may start to get involved
with using the

1223
01:09:21,020 --> 01:09:23,280
content providers that we talked about, or
you my wanna

1224
01:09:23,280 --> 01:09:25,890
use some additional tools to make your
development life easier.

1225
01:09:27,820 --> 01:09:29,008
So, you may be familiar in

1226
01:09:29,008 --> 01:09:31,840
other environments with abstracting that
data away.

1227
01:09:31,840 --> 01:09:35,060
We got that data source object, but we can
take it even further by

1228
01:09:35,060 --> 01:09:38,510
using model objects that have additional
database

1229
01:09:38,510 --> 01:09:41,680
behavior that we can access from our
activities.

1230
01:09:41,680 --> 01:09:43,920
So I, I'm gonna give a quick run down of

1231
01:09:43,920 --> 01:09:47,100
a few things, a few tips for SQL
development for Android.

1232
01:09:47,100 --> 01:09:51,190
The first one here is a really useful way
to run some commands

1233
01:09:51,190 --> 01:09:56,360
in the ADB shell that makes all SQL
statements logged to log head.

1234
01:09:56,360 --> 01:10:01,560
So this is great for debugging instead of
having to log your own statements.

1235
01:10:01,560 --> 01:10:03,840
If you turn this on than every single SQL

1236
01:10:03,840 --> 01:10:06,140
statement from the device will be logged
in log head

1237
01:10:06,140 --> 01:10:07,568
and you can take a look and make sure

1238
01:10:07,568 --> 01:10:10,290
that the SQL you're expecting is coming
through the device.

1239
01:10:12,890 --> 01:10:18,400
Next tool here I really like, it's an open
source library called android_dbinspector.

1240
01:10:18,400 --> 01:10:24,010
And the way it works is, this version is
just for Android Studio so

1241
01:10:24,010 --> 01:10:26,080
to get it to work with Eclipse would take
a little bit of work.

1242
01:10:27,160 --> 01:10:31,670
But it's a library project that you set in
your existing project.

1243
01:10:31,670 --> 01:10:35,840
And then you change your manifest, you add
a debug manifest.

1244
01:10:35,840 --> 01:10:38,110
It's a little bit convoluted if you want
to try it out and

1245
01:10:38,110 --> 01:10:41,630
you have any trouble, certainly stop by in
the forum and let us know.

1246
01:10:41,630 --> 01:10:46,480
But once you get it up and running, when
you run your app from Android Studio, it

1247
01:10:46,480 --> 01:10:53,020
will load up this DbInspector app on the
side with a, a separate launcher.

1248
01:10:53,020 --> 01:10:58,310
And you can view the data for your app and
you see

1249
01:10:58,310 --> 01:11:00,560
in the screenshots here, there's the

1250
01:11:00,560 --> 01:11:02,890
database file, it's got it's individual
tables.

1251
01:11:02,890 --> 01:11:04,280
If you click into tables, you can see

1252
01:11:04,280 --> 01:11:06,410
the structure and you can also see the
contents.

1253
01:11:06,410 --> 01:11:09,780
So it's a nice way to look on the app at
the data behind the scenes.

1254
01:11:11,300 --> 01:11:13,500
And then lastly I have two different
versions of

1255
01:11:15,740 --> 01:11:16,840
how do you wanna call these?

1256
01:11:16,840 --> 01:11:20,810
Abstraction models for taking your data
into plain Java

1257
01:11:20,810 --> 01:11:22,530
objects that you can then reuse in your
app.

1258
01:11:22,530 --> 01:11:24,990
And that just makes your code easier to
understand and maintain.

1259
01:11:24,990 --> 01:11:27,970
If you're working with a forecast object
instead

1260
01:11:27,970 --> 01:11:30,180
of working, writing your SQL and working
with

1261
01:11:30,180 --> 01:11:31,910
it directly, then your, your code in your

1262
01:11:31,910 --> 01:11:34,760
activities and elsewhere is a lot easier
to understand.

1263
01:11:34,760 --> 01:11:37,060
The first one here is called greenDAO.

1264
01:11:37,060 --> 01:11:39,380
DAO stands for data access object.

1265
01:11:39,380 --> 01:11:45,620
And this is our wrapper around the SQLite
database you can see here they

1266
01:11:45,620 --> 01:11:51,030
have some claims about being increased
performance

1267
01:11:51,030 --> 01:11:53,960
small memory consumption and easy to use
APIs.

1268
01:11:53,960 --> 01:11:57,700
I have read, I haven't used this for
myself, but I have read

1269
01:11:57,700 --> 01:12:02,130
reviews and performance tests that say
this is a good library to try out.

1270
01:12:02,130 --> 01:12:03,790
And the last one was just brought to

1271
01:12:03,790 --> 01:12:05,200
my attention yesterday, I was talking with
one of

1272
01:12:05,200 --> 01:12:07,830
our mobile developers, Joe Steel, and he
mentioned cupboard,

1273
01:12:07,830 --> 01:12:10,720
another library that does some of the same
things.

1274
01:12:10,720 --> 01:12:14,145
It extracts out that database layer and it
uses plain old

1275
01:12:14,145 --> 01:12:18,620
Java objects to, and, and here, the
example, I think illustrates.

1276
01:12:18,620 --> 01:12:20,480
So, in your activity, you'd have this,
this code

1277
01:12:20,480 --> 01:12:24,670
with the cupboard library with the
database that you provide.

1278
01:12:24,670 --> 01:12:27,680
You put a book object, so it puts that
book into the database.

1279
01:12:27,680 --> 01:12:32,670
And makes it a little bit cleaner to
insert or extract data from the database.

1280
01:12:34,800 --> 01:12:36,870
So, that just about does it.

1281
01:12:36,870 --> 01:12:39,000
Thanks a lot for sticking through to the
end here.

1282
01:12:39,000 --> 01:12:41,170
I'm gonna check and see if there's any
questions at the moment.

1283
01:12:41,170 --> 01:12:42,690
And again if not, if we don't get to them.

1284
01:12:42,690 --> 01:12:44,760
Then we will catch up on the forum.

1285
01:12:46,520 --> 01:12:50,200
Okay, so, Jonathan asks is there a reason
why I

1286
01:12:50,200 --> 01:12:55,337
decided to use big decimal versus the
auto-boxed double class.

1287
01:12:57,333 --> 01:12:59,560
no.

1288
01:12:59,560 --> 01:13:01,540
No I could have just used that.

1289
01:13:01,540 --> 01:13:06,890
I feel like I may have had a reason,
before, but no, yeah, so, yeah.

1290
01:13:06,890 --> 01:13:08,950
What that's referring to is where we're
taking the double

1291
01:13:08,950 --> 01:13:12,850
values, the primitive values and is
adapting them for the display.

1292
01:13:12,850 --> 01:13:18,554
We could have just used the double with a
capital D as the, the more complete

1293
01:13:18,554 --> 01:13:23,179
object datatype that, that's usable within
the array adapters, and within the list.

1294
01:13:26,030 --> 01:13:26,530
Okay.

1295
01:13:28,480 --> 01:13:30,610
Looks like there may not be any other
questions at the moment.

1296
01:13:30,610 --> 01:13:31,970
Okay, great.

1297
01:13:31,970 --> 01:13:34,340
Again, if you have any questions follow up
in the forum

1298
01:13:34,340 --> 01:13:37,410
or find me on Twitter @benjakuben, and
thanks so much for watching.

1299
01:13:37,410 --> 01:13:38,190
See you later.