Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
In this live Treehouse Workshop, teacher Ben Jakuben shows how to use SQLite in Android for storing and manipulating data in a very simple weather forecast app.
Downloads
Download the project files from this workshop:
- Starter files - use these to follow along
- Completed project files
Known Issues
To use the sqlite3 command line tool, you must use an emulator or rooted device. There are some potential workarounds.
Resources
- Check out the forecast.io weather web app, and grab your API key at their developer site.
- Watch Ben's Android Tools course to learn more about Android Studio and Genymotion.
- Read more about the sqlite3 command in the Android Developer's documentation.
- Command Line Shell for SQLite
- Log SQLite statements in logcat using these adb shell commands
- Download Android DbInspector at GitHub.
- Turn your data into POJOs (plain old Java objects) with greenDAO or Cupboard.
Related Reading
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
[MUSIC]
0:00
Hi.
0:04
My name is Ben Jakuben, and I'm the
Android teacher here at Treehouse.
0:05
Thanks for joining us today.
0:08
Before we get started I just want to say,
if you
0:09
have any questions during the presentation
please type them below the video.
0:11
And we'll try and get to them at the,
0:16
at the end or if it's especially important
then
0:17
my friend Chris will flag me down and
we'll,
0:20
we'll try and answer it while we're doing
the presentation.
0:22
If we don't get to your question, or if
0:24
you're watching this later and you think
of something,
0:25
then post it in our Treehouse Forum and
somebody
0:27
will try and answer it for you, myself
included.
0:31
Or you can find me on Twitter @benjacobin.
0:33
All right, so we're going to cover the
basics of SQLite for Android.
0:35
And the first question we wanna ask
ourselves is what exactly is SQLite?
0:41
It is a light waist, lightweight SQL-based
database for storing data in an app.
0:45
It's available in all versions of Android
and a good example to think
0:49
of is the contacts app, where you
0:52
have information about all the different
contacts.
0:54
If you think of the database that sits
behind
0:57
it you've got people's names and phone
numbers, pictures
0:59
maybe, and that's all stored in a way that
1:02
you can access it from that app at any
time.
1:05
If it were just well, let me go ahead to
the next slide and explain.
1:07
Why exactly do we wanna use this?
1:12
It's so that we can store and retrieve
data within the
1:14
app across sessions, that's the big thing,
this is about data persistence.
1:16
When we have data in an app, if we're
running it you know, in an activity
1:19
and if the app is visible on the screen,
then that data in memory is available.
1:23
But, as soon as we close the app, that
data in memory goes away, so
1:26
we need to store that data in memory on
the phone or on the tablet somehow.
1:30
So that's why solutions like SQLite
databases
1:34
are used to persist that data across
sessions.
1:37
The reason we're looking at SQLite
1:41
specifically is, very popular with Android
developers.
1:43
A lot of software developers are familiar
with SQL databases
1:45
and using the SQL language to access and
store data.
1:49
You might be wondering if it's secure,
well, it is in
1:53
that the databases exist only within the
context of the app.
1:57
Android has this concept of sandboxing
apps, where apps and all
2:01
of their related files, databases, et
cetera are all in a restricted
2:05
section of the file system and no other
apps or anything
2:10
else can access that part of the file
system without explicit permission.
2:14
So if you wanna give access to this data
to outside apps you have to
2:17
explicitly allow it using a content
provider which
2:21
we're gonna talk about briefly in a
moment.
2:25
The other thing is when you uninstall an
app, it does usually delete
2:27
the data as far as I know from, I forget
which version of Android,
2:31
there may have been some inconsistencies
earlier on, but when you delete an
2:35
app it, corresponding database, and all
the
2:40
files in that sandbox should be deleted.
2:42
We're gonna keep things pretty basic
today.
2:46
We are going to talk about the C.R.U.D.
2:47
operation, so if you are familiar with web
development
2:51
or other, you know, working with data,
then these
2:54
are the basic operations you can perform
on data.
2:57
The acronym C.R.U.D. stands for create,
retrieve, update, and delete.
2:59
Now in the syntax of SQL that is instead
insert, select, update and delete.
3:04
And I should mention about SQL
specifically, SQL
3:13
stands for, SQL stands for a Structured
Query Language.
3:16
Now if you're not familiar with SQL then
we
3:19
have a fantastic course by Andrew Chalkley
called database basics,
3:22
and it will get you comfortable with
creating databases, manipulating
3:25
the data, and it's all based on the SQL
language.
3:29
We're gonna work with very basic data
we're going to get some forecast data
3:32
from the web and we're going to store it
in a table, and all
3:38
we care about right now, just to show how
to use these concepts in
3:41
Android, we're gonna do one column of
3:45
data, which is numbers that correspond to
temperatures.
3:47
We are also going to talk about upgrading
databases.
3:51
So, that is when you're, let's say in our
app we are tracking information about
3:54
a user and we have a user name, and an
email address, and a picture.
3:59
Well, we released a new version of our
4:02
app and now we wanna track additional
data, too.
4:04
Let's say we wanna store their favorite
video game for some reason.
4:06
Well, we need to amend the data that's
currently in our system.
4:10
And there's different ways we can do that,
we
4:14
can, because we have an existing database
for the
4:15
people who have the previous version of
the app,
4:17
we can either drop the database and
re-create it.
4:19
Migrate the data over somehow or we can
alter tables in place and add new columns
4:22
like this where the existing data stays
the
4:28
same and then we populate the new columns.
4:29
So, we'll take a look at that later on
after we cover those C.R.U.D. operations.
4:31
Now, the last thing I mention before we
get
4:36
started with actual code is the idea of
content providers.
4:39
A lot of times you'll hear content
providers
4:44
talked about in the same conversation as
SQLite
4:46
databases, and a content provider is an
Android
4:48
construct used to manage structured sets
of data.
4:52
And a database is a structured set of
data,
4:55
they're primarily intended to be used by
other applications.
4:58
Which is why I said you have to explicitly
provide
5:00
access to your data to other applications
through a content provider.
5:04
But you don't need a provider to use an
SQL database
5:08
if the use is entirely within the context
of your own app.
5:12
So, that's what we're going to do today.
5:15
We're just building a very simple app
that's going to have its own data.
5:16
We're not going to provide it to anywhere
else.
5:19
That's a topic for another workshop or
another Treehouse course.
5:20
But I do wanna just mention before we
5:24
get started one more thing about the
content provider.
5:27
There's a, a good discussion that one of
our mobile developers Joe
5:30
Steel mentioned to me about the benefits
of using a content provider.
5:33
It is a best practice and this is a Google
Plus conversation that was
5:37
happening with some Android developers
about the
5:41
pros and cons of using a content provider.
5:44
I'm gonna provide all these links for the
5:46
workshop when this is published in a few
days.
5:48
So anything that we cover on the web come
back when this is published in a
5:51
few days and you'll be able to find these
links or you can search for this here.
5:55
Okay.
5:59
So, what exactly are we building?
5:59
We are going to build a simple weather
6:02
forecast app using an, an API provided by
forecast.io.
6:05
Now if you're not familiar with
forecast.io, it's a web-based weather
6:09
app that is available also as a web-based
app on your phones.
6:14
So, it's a, it's a really great example of
the kind of web app
6:18
you can build on a phone using web
technology that's not a native app.
6:23
It's completely built from the website,
but they do some really neat things with
6:26
animations and making it feel like a
6:31
native app despite it all being web
technology.
6:32
And the reason we're using this site is
they have a friendly
6:35
developer portal where you can access
their API to get some forecast data.
6:39
It's free, you can sign up if you
6:42
just come here to developer.forecast.io
and register, you
6:44
create an account and you get an API key
that unlocks your access to their API.
6:48
That way they can shut you off if you're
abusing
6:52
it [LAUGH], but it's a pretty, I, I don't
remember
6:54
the exact numbers but you get a lot of
calls,
6:56
so, it's really useful to build some
simple weather apps.
6:58
And they, they give you a lot of
information, so I've
7:02
switched here to the output from the API
the forecast call.
7:04
So, this is a, a JSON output and I'm
7:09
formatting my JSON with a chrome extension
called JSONView.
7:12
You can see the plain source looks like
this, but, if we close this and go
7:15
back we get information about our current
location,
7:19
and then we get different types of
forecasts.
7:21
The current conditions, minutes, hourly,
daily forecasts.
7:23
We're going to look with the hourly
forecast because it
7:27
gives us a set of 48 data points to work
with.
7:30
And because we wanna show, inserting
multiple data points and manipulating
7:34
it this is just a simple data set of 40
data points.
7:37
We know we're getting and we can work with
it from there.
7:40
Okay.
7:45
So lets get started with our code.
7:45
I'm building this app in Android Studio.
7:49
And our Treehouse projects currently use
Eclipse.
7:52
But everything we do here in Android
Studio
7:56
are the code you can just transfer to
Eclipse.
7:59
The only thing that's different is the
project set up itself.
8:00
So when you create a new project you have
to manually add these code
8:03
files in Eclipse, there's no easy way to
migrate from Android Studio to Eclipse.
8:06
The reason I'm using Android Studio is
8:11
because the tool itself just gets better
and
8:12
better and I want to encourage you
8:14
if you're comfortable to try out Android
Studio.
8:16
Take a look I had an different workshop
8:19
a couple months ago about getting started
with Android
8:21
Studio, so if you want to check that
8:23
out it's available now as an Android Tools
course.
8:24
So you can review getting started with
Android
8:28
Studio, and then take a look at this
project.
8:31
All the code that we write is also going
8:33
to be available for download, so we'll
take a look.
8:35
And the idea is, we're gonna walk through
it, write some code, explain what's going
8:38
on, and then you'll have a nice simple
example to get started with your own code.
8:41
You can take these examples and, and
either copy it and, and manipulate
8:44
it, or maybe even use some of it directly
in your own applications.
8:48
And, it's the kind of thing where it's
good to know how you do
8:51
it, but then once you get an example, you
can just reuse your existing code.
8:54
So let's before we do anything, this code
is incomplete but let's
8:58
run it just to get a quick look at the app
itself.
9:02
[BLANK_AUDIO]
9:05
Okay and it's about to load here on my
Genymotion emulator and
9:07
this is another topic that I covered in
that Android tool's course.
9:11
A Genymotion is a third party emulator
that works usually works better than the
9:16
default emulator, it's very fast they have
9:20
all sorts of different devices you can
try.
9:22
So in general I, I highly recommend
9:24
using that for your development purposes
as well.
9:26
It's the next best thing to having an
actual device to develop on.
9:28
So you can see here this app just has two
activities the first is this front one
9:31
has our C.R.U.D or I.S.U.D operationa,
insert, select, update and delete.
9:36
And we're gonna start with just creating
the database and inserting some data.
9:42
Makes sense it's a good place to start.
9:47
So back in our project we have, here's the
two activities we were working with.
9:49
And the services package here, I've got
my, my code organized into
9:54
a couple of sub packages, so that UI
packages for our activities.
9:58
Services is for the code related to
getting data from the web.
10:02
So I'm not gonna review any of this, it's
here.
10:05
You can get the data from the web however
you want whatever data you're going after.
10:08
But I'm using a library called Retrofit
it's an open source library from Square.
10:12
And I haven't used it much, but what I
have used
10:18
I really like, and so I recommend checking
that out as well.
10:20
Could be a topic for another live stream,
but
10:24
the the code here just as a quick review.
10:26
You give it the base URL, and then you
configure
10:28
your, your weather service or whatever
service you're running for
10:31
and the, the library makes use of these
annotations, and
10:35
it generates a lot of code for you behind
the scenes.
10:37
It does a lot of magic but it does it in
10:40
a way that, that makes it very clean and
easy to understand.
10:43
So here the method to load the forecast
data, we create a new
10:46
rest adapter because our, web service
we're going after is a rest API.
10:50
And we call this get forecast and it runs
asynchronously.
10:56
So, it goes out, requests the data, it
11:01
uses the key and our current location and
then
11:03
it, when it's all done, when the
asynchronous
11:07
call finishes, it calls back with this
callback method.
11:09
Now, this is that common asynchronous
processing with a callback
11:12
that we've seen in all, a few of our
Android projects.
11:15
So, when it calls back, we'll go back, so
we're calling this from our main activity
11:18
and when the data returns, we pass in this
call back down here.
11:23
And it's a retrofit callback and it has
11:28
two different methods that we need to
override.
11:32
First is success, which will include a
forecast object
11:34
and that's the other object we have over
here.
11:37
This is a mapping of the data from
11:39
the JSON file, this is a very simplified
version.
11:41
To map the entire data set would be a bit
more
11:44
of a complex class structure, but this
drills down the reason
11:46
I have it minimal like this is so that we
can
11:51
get after that hourly data, the
temperatures, specifically, that we want.
11:53
So, on a successful call, let's go back
11:58
to the MainActivity, we get that forecast
object.
12:00
And, right now we're just looping through.
12:03
So, if we run this, we are gonna run it
just to make sure in the
12:05
log we, I'm, I'm out putting the current
12:08
temperatures just to make sure that we're
getting data.
12:10
Let's proceed with actually creating the
database.
12:13
So, I've got this other sub-package here
called db
12:15
and that's where we're gonna put our
database classes.
12:18
So before we write anything let's take a
look at the android documentation.
12:20
Now, I'm a little bit in general I really
like the android documentation.
12:24
I think they do a great job of explaining
how to do things.
12:27
Providing example code of, I use it all
the time I use in our courses but the, the
12:31
documentation about SQLite databases is a
little bit obtuse
12:36
and it's, it's Well, it is what it is.
12:40
So hopefully this course is enough [LAUGH]
to get you started and then
12:43
you can work your way through
12:46
the navigation through the documentation
as needed.
12:46
But if you search for saving data, you can
find
12:49
this reference of how to save data in SQL
databases.
12:52
And we're gonna walk through these steps
but the, the first thing
12:56
we need to do is create a database using a
SQL helper.
13:00
And we do that by extending a base class
called SQLiteOpenHelper.
13:04
So we're gonna create our own custom class
that will extend
13:09
that base class, and take care of the
create operations for us.
13:13
So if I right-click on db and select New
13:16
> Java Class, we're gonna call this our
forecasthelper.
13:18
Okay, and we are going to extend the
SQLiteOpenHelper.
13:25
Okay?
13:34
And right away, we get an error that we
were missing some required methods.
13:34
SQLiteOpenHelper is an abstract class
which means we can't use it directly.
13:37
That's why we need to subclass it like
this, and we can
13:41
use Alt+Enter to bring up a quick fix for
implementing the methods.
13:43
And if I enter here, we can create the two
13:49
methods that we need, this will be just
the stubs.
13:52
Click OK.
13:55
And there you see the first one is called
13:56
onCreate, which is used to, obviously,
create the database.
13:57
Second is called onUpgrade which we'll
look at a little bit later on
14:00
and that's when we wanna upgrade the
database from one version to another.
14:03
Okay, so we still have a warning here
about
14:07
a missing constructor, so let's take care
of that.
14:09
Now, if we, if we do a quick fix here, if
I hit Alt+Enter
14:12
and create the constructor we get a
14:15
little more, little more information than
we want.
14:18
This is to create it with passing in a
bunch of
14:21
information, but we're gonna just make
this information part of the class.
14:23
So our constructor will instead just
require
14:26
the context, which is a common, common
pattern.
14:28
And then we will add these individual
parameters here in the class.
14:32
So the first parameter here is the name of
the database.
14:37
We can name this whatever we like, and
let's make it a constant here.
14:40
And we're gonna make it a public constant
for the
14:43
database name, well, actually no, let's
make this one a private.
14:46
Some of these constants we're gonna
14:49
make public, because we'll reference them
elsewhere.
14:50
But let's make this a private static final
14:52
String call DB_NAME and we'll call this
temperatures.
14:56
And we have to give it the .db extension.
15:01
Okay, so now we can use this DB_NAME here
in the construct.DB_NAME.
15:05
Now the next parameter we just are going
to
15:11
pass in null, this is what's this
parameter, it's a
15:13
I don, I don't remember off the top of my
head but we don't need it for this.
15:21
And the version number is just an integer
version and this
15:26
is how we know if we're upgrading the
database or not.
15:30
So let's make this a private static final
int, I'll call a DB_VERSION,
15:32
and I'll set equal to 1, because we're
just starting of with 1.
15:37
It's an integer value so its gonna be 1
and above,
15:41
and we need to increment the version when
we're upgrading database.
15:43
So its passion, passing DB_VERSION here,
and now, our constructor
15:47
is complete, and we can focus on the,
onCreate method.
15:51
Let's go back to our documentation.
15:57
All right.
16:00
Okay.
16:01
I apologize.
16:02
So the next thing you wanna do in here is
use
16:03
our, database object to run some SQL to
create a table.
16:06
So, the next thing we need [LAUGH]
is a, the table name that we want.
16:12
This is the constant that we're going to
make public.
16:18
So the first final String TABLE, and we'll
call this
16:20
table TEMPERATURES, and we'll just set
this equal to.
16:26
We'll do these in all caps too as a
16:32
common SQL convention to write in all
caps, TEMPERATURES.
16:35
Okay.
16:41
And, let's, you know, let's, let's define
our database create statement.
16:41
So what I'm gonna do is use the SQLite
database
16:47
that's passed in here as the parameter
into the onCreate method.
16:50
It's called db.
16:55
So, if we type db and look at the content
assist here then we see
16:56
a, a command for executing SQL and this is
where we can write raw SQL.
17:01
Now, in some cases with working with
SQLite, we wanna write
17:04
we, in some cases we will write raw SQL
like this.
17:08
Where it's a create statement that's just
a basic SQL
17:10
statement just like you would see working
with any database.
17:12
Other times we're gonna use some methods
and some classes to
17:15
encapsulate that SQL and work with it in a
different way,
17:18
a more modular way that makes for a
cleaner code for
17:21
us, it's a cleaner interface between our
app and the underlying database.
17:23
But here we're gonna pass in a string
called DB_CREATE.
17:27
And then let's go up here and create that,
with the variables we define so.
17:32
Another private static final String named
DB_CREATE.
17:39
And right now we're gonna plug in some
variables, but let's, let's write
17:46
this as if we were writing it right in
some kind of SQL editor.
17:51
So we are going to write, CREATE TABLE,
and the name is TEMPERATURES.
17:54
And when we create this table we need to
define the
18:00
columns that are included and the data
type of those columns.
18:02
So the first column, and they go in
parenthesis here.
18:07
The first column we're going to use is a
primary key that's just an integer ID.
18:10
And we don't need it for this example like
we saw in the, the full
18:15
screen earlier with we're just gonna work
with
18:19
temperatures, it's just gonna be one
column video.
18:21
However, if we include this ID column that
18:22
I'm about to write, it's common convention
for working
18:25
with a SQLite in Android, and we need that
18:28
column to work with certain things in the
future.
18:30
So for content providers for example, or a
content adapters
18:33
when you're adapting your data for views,
they require that ID
18:36
column be in place, so it's a good
practice to
18:39
generally create it, even if you're not
gonna use it yourself.
18:41
We don't really need to worry about it.
18:43
Once we create it, we're done with it,
because we're gonna create it in such
18:44
a way that it auto-increments in the
18:47
database by itself every time we insert
data.
18:49
So, the first column the convention is _ID
and it is of a type INTEGER.
18:52
And then we're gonna add that additional
information
18:58
to make it the primary key, that auto
increments.
19:00
Okay.
19:06
The second column is our TEMPERATURE
column, and
19:07
a data type for this is just REAL.
19:11
So there's just a few basic datatypes
available in SQLite and
19:13
they're REAL, datatype is for numbers that
have a decimal value.
19:17
So the, if we take a look at the data that
we're getting
19:20
back from our API, we see the temperature
is given with a decimal value.
19:23
So we're gonna maintain that accuracy and
store it with a decimal in our database.
19:28
Okay so now, let's plug in the actual
values, the variables instead of
19:35
having this all hard coded, so here,
19:39
instead of TEMPERATURES, let's plug in
TABLE_TEMPERATURES.
19:41
Okay.
19:46
And then we're gonna create column names
as well, for these variables.
19:47
So instead of _ID, let's use a variable
called COLUMN_ID
19:50
and we're gonna add that in a second and
then also down here let's do
19:56
COLUMN_TEMPERATURE and leave that data
set.
20:02
Okay.
20:08
So now we just need to define those two
variables, I'm gonna copy and paste here.
20:09
First one COLUMN_ID and this is where
that, we had that _ID.
20:13
Second one is COLUMN_TEMPERATURES and I'm
sorry just TEMPERATURES since this is an
20:21
individual row, an individual value within
our
20:27
temperatures table, so we just use
temperature.
20:29
So now we have these variables to find our
create table statement is ready.
20:32
And we can execute it here within the
context of our onCreate method.
20:35
So we're almost ready to create our
database
20:40
and make sure that everything is in place.
20:42
But before we do that we want to introduce
another class.
20:44
We have, we're going to use two database
classes to access our database.
20:47
This database helper is what we use for
20:51
defining the database and creating and
upgrading it.
20:55
But then we have a separate class we're
gonna create
20:58
called forecast data source that's going
to act as a an
21:00
interface on, on top of the, this forecast
helper and the
21:04
forecast data source is going to do all of
our operations.
21:08
So our code in our app is going to use the
call methods from
21:11
forecast data source and in there it will
execute on the underlying SQL database.
21:15
So let's right-click on the db package,
select New >
21:19
Java Class and like I said, this one's
called ForecastDataSource.
21:22
And click OK.
21:30
And this one doesn't have to extend
anything, it's just that we are
21:30
going to make use of that data helper
class that we just defined.
21:32
Okay, so the first thing that we need is a
SQLite database object itself.
21:36
So let's define a private SQLiteDatabase,
named mDatabase.
21:39
And then we want our own version of that
forecast data, forecast helper.
21:46
So we have private ForecastHelper and
we'll call this let's call
21:52
it mForecastHelper.
21:58
Okay.
22:03
So let's start with the constructor, we're
gonna, we're gonna write
22:04
this code, and then we're gonna use it in
our main activity.
22:07
So the constructor is public
ForecastDataSource, and we're gonna pass
22:09
in the context again, we need to pass it
along to the ForecastHelper class.
22:14
And all we wanna do here is, well, I
22:22
should have created a context variable to
hold on to.
22:24
So we'll do private Context mContext.
22:27
And now we can set mContext equal to the
context.
22:32
And the second thing we wanna do is use
our instantiate our forecast helper,
22:36
so that's equal to a new ForecastHelper
and that requires the mContext.
22:42
So we pass that in.
22:47
Okay?
22:49
And now this class is going to be
responsible for a couple different things.
22:50
So let's add some comments here, and we're
gonna, we're, we're gonna define what
22:55
we're gonna do in this class and then
we're gonna do it piece by piece.
22:59
So the first thing we need to do is open a
database, and then conversely close one.
23:01
And then we're gonna do those C.R.U.D
operations,
23:06
insert, select, update, and delete.
23:12
Okay, and I know we're doing a lot
23:17
here before running anything, and you
know, generally I
23:18
like to do as little as possible, and then
test it to make sure that it's working.
23:20
But we'll get this basic stuff setup,
23:24
we'll run, and then hopefully it'll make
sense.
23:26
We'll walk through exactly where each
piece is cuz you know,
23:28
we have these couple different pieces
moving, but once we get
23:30
into the main activity, and call it, it
should be more
23:33
apparent as to what's going on behind the
scenes for us.
23:35
Okay, so let's start with the open method.
23:37
Let's do, so call this open and this one
is special
23:46
we're gonna throw a SQLException just in
case anything goes wrong.
23:50
As a generic SQL error that we wanna watch
out for with certain operations.
23:56
but, yeah, so, this is just one line of
code and it's from our forecast helper.
24:03
So we set our database, the SQLite
database
24:09
from the forecast helper, using a method
called getWriteableDatabase.
24:12
And this is the important line of code,
24:17
this is really what we were building
toward.
24:18
So getWritableDatabase is from that
SQLiteOpenHelper method, open help.
24:20
I'm sorry SQLiteOpenHelper class.
24:26
And it's a method that will create the
database if it
24:27
doesn't exist, or just open the database
if it exists for writing.
24:32
Now in content assist you may
24:35
have seen there's a different version,
getReadableDatabase.
24:36
That's where if you want to have a read
only database, but we're
24:38
gonna be inserting data and updating so we
definitely wanna be able to write.
24:40
So now this is ready to use within our
main activities.
24:44
So let's switch over and what I've done
here is
24:48
I've left a lot of code based on the UI
24:52
and the buttons and things like that,
things that you'll
24:55
hopefully already know how to do based on
our Android courses.
24:58
But I've left notes for us to work through
on
25:01
and the pieces we need to get the data in
place.
25:05
So the first one up here is to declare a
data
25:08
source, and that's gonna be using our new
forecast data source class.
25:10
So let's make this a protected
25:13
ForecastDataSource, and we'll call it
mDataSource.
25:16
Kay the next thing we do, wanna do
25:20
is instantiate that when the activity is
created.
25:22
So sure enough down here there's
25:25
another note, mDataSource equals a new
ForecastDataSource
25:27
and remember we need to pass on the
context so we'll use MainActivity.this.
25:31
Okay.
25:37
So this creates our data source object and
now
25:38
that we have it, we can open the database.
25:41
So we just call mDataSource.open and then
this show
25:46
to create the database and open a
connection to it.
25:50
So that's another thing we need to talk
about is database connections.
25:54
So the database the way it works is we
once
25:59
it's created, we open a connection to it,
and that
26:03
connection we use to write and retrieve
data, and when
26:05
we're all done with it, we have to close
that connection.
26:08
Now it's important to manage opening and
closing appropriately, so
26:11
that we don't leave a connection open when
our activity is
26:15
destroyed or our app closes because you
can end up with
26:18
no pointer references or memory issues, or
corrupting the database somehow.
26:22
So we have to carefully correspond,
26:26
carefully match opening calls with closing
calls.
26:30
So there are a couple of different ways
you can do this.
26:32
One way would be to whenever you wanna
perform a SQL operation,
26:34
you open a connection, perform that
operation and then close it right away.
26:38
That's fine, although if you're doing a
26:42
lot of operations in an activity, you'll
end
26:43
up with a lot of open and closing
26:45
calls which there's nothing really wrong
with it.
26:47
The performance, it is minimal, but it
just kinda clutters your code.
26:49
So, in general, what, what I like to do is
open
26:53
and close my connections in life cycle
methods of the activity.
26:55
So just a quick review of the life cycle
of an activity.
26:59
When it's first created this onCreate
method is
27:02
called, and that's where we set up and
27:05
instantiate a lot of variables, data we
might
27:07
need tie things to the layout et cetera.
27:09
As soon as it's onCreate method is called,
as
27:12
soon as it finishes rather, the onResume
method is called.
27:15
So those, those two life cycle methods are
piggy backed
27:18
on top of each other when an activity's
first created.
27:21
Then when that activity is sent to the
background
27:23
whether the user navigates to another
activity or a
27:25
different app, closes the app, the onPause
method is
27:27
called, so you always think of onResume
and onPause together.
27:30
So if we open the database in onResume,
and close it in onPause, then we'll make
27:33
sure that the database connection is only
open
27:38
while our activity is active on the phone.
27:41
And that's the behavior we're after, so
that's what we're gonna
27:43
do here is we're gonna move this
mDataSource.open to the onResume method.
27:44
So I've got it down here again a little
note let's just paste it in.
27:51
In here onPause we want our corresponding
closer, and so we call mDataSource.close.
27:55
But wait we haven't defined the close
method yet, so
28:01
let's switch back to ForecastDataSource
and this is real simple.
28:04
We said we were, are going to use a close
method, public void close,
28:09
and all we do is from this database method
call the close method itself.
28:13
So this is, again, just a wrap where
28:18
this is an instance where this
ForecastDataSource is really
28:19
just a wrapper on the call, so that we
28:22
aren't interacting with the database
directly in our code.
28:25
Alright, so at this point, there is no
errors
28:31
in the project and it should be ready to
run.
28:33
So and this is, I've got a brand new
emulator instance here
28:35
and I ran it without any database code in
here.
28:40
So there's, there is nothing currently on
28:44
this Android phone, on this Android
emulator.
28:46
So if we run it, we should create a
database, and
28:48
we'll just wait a moment for it to load on
the emulator.
28:52
Okay, there we go, oh, look, I got my log
28:54
head back [LAUGH], I forgot when I run the
project.
28:58
That'll bring it back all right so nothing
to
29:01
see here in app yet because we haven't
done anything.
29:04
But behind the scenes, we should have
created the database.
29:07
So how can we tell?
29:09
Well, if you remember we created a
database name called TEMPERATURES.db.
29:11
And that's an actual database file that's
available on our file system.
29:15
So there's different ways you can look at
files on the end of your file system.
29:19
If you're using Eclipse, there's a built
29:22
in file explorer from the DDMS
perspective.
29:24
But you can also use command line tools.
29:27
And that's what we're gonna do here
because there's an additional
29:29
command line tool that interacts with SQL
directly, it's called SQLite3.
29:32
So let's take a look at how to use that
tool and make sure that we
29:35
are in fact, that we do in fact have a
database created behind the scenes here.
29:38
So in the terminal it depends on how you
have your environment set up.
29:42
You may need to navigate to the Android
SDK.
29:47
So if you look here I've got I'm in the
Android SDK that corresponds
29:49
to Android Studio and specifically I go
into platform tools and if I type
29:53
ls for list, then I see a couple different
tools available and what we're
29:58
gonna use is this one called adb which now
adb stands for Android Debug Bridge.
30:01
So the one thing I wanna show is you can
see the currently connected
30:09
devices if I do ./adb devices, then you
could see the list of devices attached.
30:12
And this is the serial number for my
emulator
30:18
instance and we need that to connect to
SQLite.
30:22
So let me go back to Chrome for just a
30:26
moment, that's the next tab I wanted to
show you.
30:27
This is the, tool from the command line
that we're gonna use to take
30:30
a look at our database, and this is
available in, under the Tools section.
30:33
There are a lot of different command line
tools
30:37
available in Android, and we haven't
really talked about
30:38
many at Treehouse, but if you go in
through
30:40
a command line to a connected device like
this.
30:43
You can do some of the powerful stuff to
30:45
make sure that your development is
proceeding as planned.
30:46
So the next thing we're gonna do is
execute, enter the remote ADB shell,
30:49
using this following command, and we need
that serial number that I just showed you.
30:53
So the next line type ./adb -s for serial
30:57
number and then I'll just copy and paste
this.
31:01
And we want the show.
31:06
Okay, now this is a shell on a device
itself, on
31:08
the Genymotion emulator, so here we can
use that sqlite3 command.
31:11
And if we just run this, sure enough we
31:18
get the three corresponds to the version
of SQL.
31:21
And here we got a SQLite prompt and you
know, depending on, on your
31:24
familiarity with SQL development you may
have
31:28
used tools like this on other systems.
31:30
But there's not much here, we need to
31:33
instead open up to our, our own database
specifically.
31:36
If you type .help you can see a bunch
31:40
of different options these are different
commands you can run.
31:42
You can type straight SQL in here as well
and
31:45
execute it as long as you end it with a
semicolon.
31:47
But if we look here at the databases we
should get the
31:50
list of databases but it's not, this is
not for our app specifically.
31:52
We need to instead quit here, so we do
.quit and we wanna
31:57
open the connection to our database and
here you can use the format here.
32:02
So where is it?
32:06
Here we go, this line here shows the path
to emulator device instance.
32:09
It's a data/data/package_name/databases,
and then you
32:16
append the database at the end.
32:18
So, let's type that out, and that should
make more sense.
32:20
So, sqlite3 and then we pass in
/data/data/com.teamtreehouse.,
32:22
and this is called, I called this
friendlyforecast.
32:28
And /databases/, let's see if that's
correct.
32:33
Yep.
32:38
We called it temperatures.db, so that
should
32:39
open up okay, we've got the command
prompt.
32:43
Now if we type let's see databases i think
it lists.
32:46
Yeah, there you could see the file that
corresponds the main database
32:51
because we're in our temperatures.db, you
can see the file name itself.
32:54
Now if we look at the tables we should
have one table temperatures.
32:58
Well there's an android meta datatable
that's included
33:03
by default as well for your SQLite
instance.
33:06
But temperatures is the one we define and
you'll notice, you'll
33:09
notice that matches exactly what we
created in our ForecastOpenHelper class.
33:11
So before we're go on, let's make sure
this is what we expect.
33:19
We can look at the schema by typing schema
and then giving the table name
33:22
TEMPERATURES, and sure enough, there is
the exact create statement we defined.
33:27
It's got the _ID COLUMN and the second one
is for TEMPERATURE.
33:32
Great, so that means our data is in place
and we can start using it.
33:35
So, let's insert some data.
33:39
So coming back to Android studio.
33:42
We're gonna just work our way through this
33:46
class, we have instantiated the, the
database we've opened.
33:47
We created it, opened it, we're closing it
on onPause.
33:51
So now, let's take a look at the insert so
let's see where we insert it.
33:53
So, if we go back to the app we're gonna
have these couple of
33:58
buttons here and there's nothing to view
to SELECT, UPDATE or DELETE right now.
34:01
So those buttons are greyed out.
34:05
But let's add the code to insert.
34:07
So there's an insert button, [COUGH]
34:11
and when it's on the click listener,
34:14
we're just calling this method called
loadForecastData.
34:17
So if we scroll down a bit,
34:20
we see that loadForecastData calls our
ForecastService.
34:21
And it calls at loadForecastData which
executes a call back, so we're passing in.
34:26
This is again what we went through when we
took
34:30
a brief look at this class, this is that
loadForecastData.
34:32
So this call back gets executed when the
data is returned, and that's down here.
34:36
Right now we're just logging the data, but
now we can go ahead
34:42
and insert our new data but we need a
method to do that.
34:45
So let's go back to our DataSource class,
because that's
34:49
where we're writing our interface to all
the database operations.
34:51
And for the rest of these operations we're
going to
34:56
make use of a SQL construct known as a
cursor.
34:59
So each of these methods is going to
return a
35:03
cursor, or in some cases, a, a, a
different value.
35:06
But what the cursor does is, we say to the
database, okay, go and get me this data.
35:09
The database returns something called a
results set, and it's a
35:13
set of data in whatever format we need,
whatever we requested.
35:16
And then we use a cursor to iterate
through that data however we want.
35:19
So you can think of it as like a block of,
of rows in
35:23
the table and we say with the cursor, the
cursor points to that block
35:26
and we can do operations like, okay go to
the first row and it'll
35:29
go there and from that cursor we can then
access the values in that table.
35:32
Then we can move the cursor around, we can
just
35:36
loop through, we can move to the end,
different things.
35:39
But right now with an insert what we wanna
do is create our
35:41
create the data and, and store it into the
table, into that temperatures table.
35:47
So, the first thing, let's, let's create
the method itself.
35:51
Let me just glance at my notes.
35:55
okay, public void, insert forecast, and we
had that
35:58
forecast object which has all the data we
want.
36:04
Remember, that forecast object is
returning from the web service, so, we'll
36:06
pass it in right here and we'll, we'll
manipulate it from here directly.
36:11
[SOUND]
36:14
Kay, and, and, I, I got a little bit ahead
of myself talking about the cursors.
36:17
That's actually more for selects and
updates and deletes, but for the this one
36:21
because we're inserting, we're just going
to
36:24
insert and assume success and come back.
36:26
We're not going to look at the data that
was
36:29
inserted with the cursor, although you
could certainly do that.
36:30
So, let's see, what should we do here?
36:34
Let's insert, we're gonna insert one row
first,
36:39
and then we'll talk about inserting
multiple rows.
36:42
So to do just one simple insert, now we're
not gonna write raw SQL, we're
36:44
not going to create a SQL string that says
insert into table values blah, blah, blah.
36:48
We're going to use a method from that
sequel
36:53
open helper, from the sequel I dated as
class, rather.
36:56
So the first thing we want to do is when
we pass in values
37:01
like this whether we're inserting or
updating
37:03
we use a class called content values.
37:05
We'll name this values and we'll just
initialize it with a new content values.
37:08
Okay, but now it's kind of like a
dictionary or a hash map, things
37:13
you might be familiar with, the contact
values, we put things in to it.
37:18
So for these values we put a key at a
value.
37:21
So the key here, because we're inserting
in to our table, we're going
37:24
to insert data in to that temperature
column, so we want to temperature key.
37:27
And sure enough that's why we made
37:30
this public constant if we type forecast
helper.
37:31
Dot column temperature, that gets us the
appropriate
37:35
column and now we just need a value.
37:38
Before we dive into the forecast object
let's
37:41
just, let's just test it with decimal
value.
37:43
We'll do a nice, beautiful 75 degree day
okay.
37:46
And now we can use these values in the
insert itself.
37:50
So, from our database object that we have
running here.
37:54
We want to call insert and the first
37:58
parameter is the table, so forecast
helper.table temperatures.
38:02
Second parameter is a null column hack,
that's if
38:08
you, if you want to have null data in
38:11
your table, you can allow Null for certain
columns,
38:13
and this is, well we're just gonna use
null now.
38:18
But you can provide a value there if you
need to get around
38:20
some restrictions with your null columns,
it does not matter for this purpose.
38:25
And then here the last parameter you see
are those content values.
38:30
So, what's happening is, we're gonna
insert into our temperatures table, the
38:32
values that we just defined, so it's gonna
go to, column temperature.
38:37
It's gonna add a new temperature of
38:41
75 degrees, and it's going to
auto-increment that
38:42
id column that we defined, but you can
38:46
see here we're no manipulating that id
ourselves.
38:48
It auto-increments in the database behind
the scenes,
38:50
and that should be all we need for this
38:56
insert so let's go back to our main
activity.
38:58
And now we can replace this to do with an
actual
39:01
insert, so once again, from the data
source, we call, insert forecast.
39:04
And lo and behold, we have a nice forecast
variable we can use that was returned.
39:08
Okay?
39:13
And these methods here update high and
low, we're actually not gonna
39:14
get to that, because we, I don't think
we'll have enough time.
39:17
And then enable that other button.
39:20
So, once we insert data, we can go
39:21
ahead and start looking at select update
and delete.
39:22
So let's just save this and run it,
39:26
snd make sure that we're actually
inserting data.
39:28
Okay, we'll give Genymotion just a second
to reload.
39:33
There we go, okay.
39:36
Now, if we type, if we click on
39:38
insert, we should see the buttons enabled,
assuming success.
39:40
Great and you see these aren't, doing
anything right now.
39:42
That's okay, but how do we know that we
actually inserted data?
39:45
Well, we can go right back to the terminal
and it didn't have any data before but now
39:49
if we run straight SQL here, if we select
everything, select star from temperatures,
39:54
there we go.
40:02
We have one row, the very first ID is 1,
because it
40:03
starts auto-incrementing from 1, and
there's the 75 degrees we put in.
40:06
Very good.
40:10
Notice that the case the SQL is case
sensitive or it's case insensitive rather.
40:11
So I typed in lowercase although common
40:15
convention is to type SQL commands in
uppercase.
40:19
That's why we have uppercase names in our
classes.
40:21
Okay, so let's take a look, we have 48
40:25
rows of data to insert, so let's input,
let's
40:27
put all 48 temperatures into our table, so
we
40:29
need to do that back in our forecast data
source.
40:33
And the forecast itself crea, has an array
40:37
of hourly forecast and we can just loop
through
40:40
each one, and put them in, however, there
40:43
is something we want to think about before
that.
40:46
If we just looped through and did it like
this using the database
40:49
insert, then, behind the scenes, there's a
little bit of a performance hit.
40:52
And, every time you run a SQL command,
like this, the data is inserted
40:57
and then immediately, it's committed, so
any
41:02
kind of change is committed right
afterwards.
41:04
And, when you're doing bulk sequel
operations,
41:06
whether it's an Android or any other
system.
41:09
What you want to do is try and delay those
commits until you're either completely
41:12
done or maybe it's staged commits along
the process if you have a lot of data.
41:16
So what we're gonna do is we're going to
group all of these
41:21
48 commits, all of this 48
41:24
insertions, rather, into one single
database transaction.
41:26
So the transaction will start, we will
insert 48 pieces
41:29
of data, and then we'll finish the
transaction and commit it.
41:32
That just saves us a little bit of
41:35
overhead because we're writing to the disk
for
41:37
the data base, you know, it's going to
41:40
be completely negligible, the performance
hit on this app.
41:42
But when you're working with large amounts
of data you
41:45
may see a little bit of performance
degradation, and another thing
41:47
we wanna do when working with sequel
databases with larger
41:51
sets of data is make sure we, we do
asynchronous operations.
41:54
So just like when we're getting data or
information from the web, and
41:58
we may have some delay introduced
42:01
there, we do things asynchronously with
callbacks.
42:02
Well we'll do the same thing with database
operations a lot of time.
42:05
We'll execute, asynchronously and then
when it's all done, we'll call
42:08
back to our code and say, okay data's
ready, go on.
42:11
So, the first thing we need is to begin
our transaction.
42:14
So, let's begin the transaction and then
end the
42:18
transaction, and then we'll go back and
add our loop.
42:20
So if we use our database object, and
call, begin transaction and
42:23
then down at the end, we want to end our
transaction.
42:29
Okay, so we can right our loop inside
here, so
42:34
let's do a foreloop, We'll do a for each,
and this
42:38
is for each I'm not going to go into too
42:41
much detail about this is the model
structure for our data.
42:43
We'll call this, our, and we'll use the
array
42:49
from the forecast, it's called forecast
dot hourly dot data.
42:52
This corresponds, again if you want to
see, if you have questions
42:56
about this parsing, certainly feel free to
ask in the forum afterwards.
42:59
But since that isn't the focus here, we're
just focused on the SQL but there's
43:06
one more thing we need to do here.
43:11
Because something might go wrong we wanna
put a Try catch black here.
43:15
We wanna make sure we, end our transaction
no matter what.
43:18
So instead of just, in case anything was,
was, any exceptions were
43:21
thrown, we want to make sure we have a
finally block that guarantees
43:25
that the transaction will be closed off
because again, we don't wanna leave
43:29
the database in that in between
43:32
state where things didn't complete as
anticipated.
43:34
And then, so this is that transaction
itself, and then we wanna
43:38
trigger that commit that I mentioned and
we do that with M database.set
43:42
transaction is successful, so our set
transaction is successful, so notice we're
43:47
doing this here at the end, so after the
entire four loop executes.
43:51
We come down here and set the transaction
43:54
as successful, and then we close that
transaction.
43:56
So let's this should now insert, oh, but
we haven't updated the temperature.
43:59
So this just gotta be to insert 75 48
times, instead we have that hour variable.
44:04
And from there we can get the specific
temperature, which is a double value.
44:10
And that double decimal value corresponds
to the real value in the database.
44:14
So if we run this, we should be able to
see
44:18
in the app we'll execute the Insert.
44:23
Okay.
44:29
Oh and I forgot to add some code that
44:31
re-enables these buttons when we come in
and there
44:32
is the data in the database, there was a,
44:35
they had a check for an existing data
method.
44:36
But that way these would be enabled
because
44:39
we do have data in the database now.
44:40
And it was just a simple select, count,
star from
44:42
the database to make sure there were rows
in there.
44:44
Okay, so I can retrigger it with the
insert.
44:47
And if we go back to our command line,
let's take a look at this.
44:49
Unfortunately, I can't just type the up
arrow like in a console.
44:53
So, I have to retype this sequel.
44:56
But, if we select star, there we go, 48
rows.
44:58
well, 50 is because we had existing rows
in there already.
45:02
Okay.
45:06
So there's a, different types of insert,
we inserted
45:08
one row, and then we did a bulk insert.
45:11
So, this is fine, and we can view things
in the terminal
45:13
but it'd be nice to view that data in our
app, itself.
45:16
So let's see how to select that data from
the database, and display it in the app.
45:18
So, to do that, we have a separate
45:22
activity that's the select button on the
main activity.
45:24
And if we go up here and look at the on
click
45:28
we see that we're just starting a new
activity for view forecast activity.
45:30
So view forecast activity is a simplisitic
activity,
45:35
and what it does is it's going to
45:39
execute a query, select all the data from
45:40
the database and then display in a simple
list.
45:43
There are different ways to do this, this
is a
45:45
very basic way again if you're using a
content provider
45:47
you can use a thing called a cursor
adapter to
45:50
adapt things but it's, again we're just
introducing the basics here.
45:53
This is just to show you how to get data
in and get data out.
45:57
So, much like the main activity, let's do
a few things about getting a data source.
46:00
So, we have a protected, forecast data
source, name
46:03
and data source and we're going to
instantiate it here
46:08
and create data source equals a new
forecast data
46:12
source with the context of view, forecast
activity dot this.
46:18
Okay, now we want to, open and close in
unpause and unresume.
46:21
So end DataSource dot open and end data
source,
46:27
dot close.
46:33
Sure enough, and here, we don't have any
button or anything to trigger the action.
46:34
We're just going to query all the data and
bring it back
46:38
to the display every time the activity is,
called with on resume.
46:41
So let's, add the code to select
everything from the
46:45
database and then once we have that we'll
update the list.
46:49
So let's go back.
46:52
Again, you'll be familiar with this patter
now.
46:53
We're going to go back to our data source
46:55
object and that's where we'll write our
select method.
46:57
So, the first one, this is where I was
talking about the cursor.
47:00
So, we're going to return a cursor to the
47:04
results and let's call this Select all
temperatures [SOUND]
47:06
and we don't need to pass anything into
47:13
this one, but we wanna create a cursor
variable.
47:16
Call it Cursor, and we're going to execute
a query method from our database object.
47:21
So this is again instead of writing the
47:29
raw sequel we're going to use the query
method.
47:33
We'll return the cursor when we're done,
and, I'm going
47:36
to break this, the parameters for this
method on multiple lines.
47:40
So we can see exactly what we're doing, so
the first thing we need is the data table
47:44
we're querying because we're just working
with one data
47:48
table, but we can create multiple tables
as well.
47:50
So we could have temperature, we could
have a separate
47:52
table for wind conditions, or whatever we
want, or locations maybe.
47:54
But this is the table name which will
47:59
go through our helper and we'll use table
temperatures.
48:01
Okay.
48:06
And then the second thing is the columns
that we wanna select.
48:08
So the format here, instead of just a
single column, we need to list these as an
48:13
array of strings, so here, like, if we put
forecast helper dot column temperature.
48:18
We would get an error which well, I'll
just go ahead and fix
48:28
it now, what we do is just you just pass
in a new
48:31
string away, and if we put curly braces
around the string, that way
48:33
we get the columns names that we want, and
this is just one column.
48:38
But we could separate multiple columns as
individual items in
48:42
this array, which themselves would be
separated by columns in here.
48:45
Okay.
48:49
So now there are a couple more
parameters.But if we just
48:51
are selecting everything from a table,
then this is good enough.
48:54
We just need to pass in null values for
the rest of the parameters.
48:57
So this first one is the where clause.
49:00
Whoops.
49:03
I forgot a comment.
49:03
And what we'll do is we'll come back with
a
49:06
separate method and look at some of these
in more detail.
49:07
These are the where parameters, and then
we
49:10
can do more advanced Sequel operations
like group by.
49:14
And
49:17
again, we're just passing in null because
we're not worried
49:19
about any of these having and finally, the
order by clause.
49:21
And if you want to learn more about any
49:27
of these SQL constructs, again there's the
database basics course.
49:30
Ok so this should give us a cursor, and if
we go
49:34
back to the view forecast activity we can
now create a cursor.
49:36
Cursor, cursor, equals and then from our
data source.
49:40
Recall select all temperatures.
49:46
Now, what we're gonna do is just pass this
cursor into a method called update list.
49:48
And we have it as a separate method
because
49:53
we're gonna update the list in two
different places.
49:54
So, if we go down to updateList, you'll be
familiar with the part down here.
49:57
All it is, is a simple array adapter of
decimals.
50:01
And it's gonna use a simple list item.
50:04
It's just gonna show a text view with the
array with the values.
50:06
And we're using a variable called
mTemperatures
50:11
which is an array of big decimals.
50:12
Now we're using big decimal here because
double is not allowed, primitive types
50:15
are not allowed for inside generics like
this we need to use a
50:19
wrapper class which is why we have big
decimal it's just a conversion
50:24
back and forth, but it gets us the same
data that we want.
50:27
So, what we need to do here is
50:31
loop through the cursor to populate our
temperatures.
50:33
So, we start, the cursor is tied to our
results set but we need, the
50:38
first thing we need to do is move it to
the beginning of the result set.
50:43
So we say, cursor.moveToFirst, then we're
gonna loop
50:46
through the entire result set using a wow
loop.
50:50
And here we're gonna use a method well,
cursor is after last.
50:53
So
50:59
let's, let's see, we'll do stuff in here
51:02
and then we're gonna move the cursor with
cursor.moveNext.
51:04
Kay, so we, we move into the first one.
51:11
We come, we enter the while loop, it's not
51:12
after the last, because we're still at the
first one.
51:14
We're gonna get the values we need, and
then we'll move
51:17
the cursor, and then redo the loop until
we're all done.
51:19
Okay, so what do we wanna do in here,
where we're doing stuff?
51:22
Let's start with an index, so the way the
cursor accesses.
51:27
Well, let's, let's do this, the cursor we,
we get
51:32
things based off the index, the indices of
the columns.
51:36
So we're after a double value and it's the
temperature.
51:38
And
51:42
if we use the cursor we can get all
different datatypes.
51:46
So these correspond to the different
datatypes,
51:49
we can use in our SQLite database.
51:51
We're getting the double, double based on
the column index.
51:53
So to get the column index, now we know
that our
51:56
column index is zero because it's the only
column in the database.
52:00
well, we have the ID column but that one
is an
52:04
index so it starts at zero for the columns
that we define.
52:06
So we could just pass on zero here but
instead, let's get it this
52:10
is a little bit cleaner because we may
change the order of our column.
52:14
So we wanna avoid hard coding the numbers
if possible.
52:16
So instead we have an index, and i equals
cursor, and low and
52:20
behold, there is a getColumnIndex based on
a name, and the name is forecast helper.,
52:25
whoops, helper.COLUMN_TEMPERATURE.
52:31
That gets us the appropriate temperature
and now we
52:36
can just add this temperature to our array
of mTemperatures.
52:39
So we're adding a bigdecimal.
52:43
We need a new big decimal from the double
value temperature OK
52:46
and that gets us what we need However,
when you're converting a double
52:51
to a big decimal, if you do it just like
this with
52:55
a simple constructor you end up with a
whole bunch of decimal places.
52:57
So it's gonna fill the entire listview and
it looks silly, we don't
53:01
care about that precision cuz we're only
getting data back in two decimal places.
53:04
So a simple fix is to add a second
parameter, different constructor And
53:08
from a, a class called Math Context, we
can use a decimal format.
53:11
And this will just format our data in a
little more of a presentable manner.
53:16
And that should, update our list.
53:22
That's all we need here, we're looping
through the cursor, setting the adapter.
53:25
So lets run it and verify.
53:28
Okay we'll wait for jenny motion.
53:32
Just a moment.
53:34
[BLANK_AUDIO]
53:34
Okay.
53:39
And, the, there's data already in the
system so we should be able to click
53:40
on select but again I, I forgot to add
that method to this prototype code.
53:43
So, we have to reinsert new data and it's
just gonna keep a pending data into
53:48
our database which is fine because once we
get to the end we're gonna delete it all.
53:51
So now that we have the button enabled, if
we click on select then there is all the
53:55
data that corresponds to what we saw in
the SQL Light tool in the command line.
54:00
Okay.
54:07
So that was getting everything.
54:08
But also, very common, we are going to
select specific data.
54:10
You know, whether it's temperature for a
specific
54:14
hour or again, like, information about a
specific user.
54:17
So we want to use a where clause to limit
the data we get back.
54:20
So what I've done is, I've added a field,
54:23
an edit text back here up in the action
bar.
54:25
And when enter a temperature, it won't
doing anything right now.
54:28
But when we enter a temperature, it's
54:31
going to filter out anything below that
temperature.
54:32
So let's go back to our data source and
add the method.
54:35
And this going to be, it's going to return
a
54:39
cursor because we're still going to have a
results set.
54:41
And let's call this,
54:44
select all temps, SelectTempsGreaterThan
and then we'll pass
54:48
in a, we're gonna use string value let me
just change that to make things easier.
54:54
I'm not worried about type checking here
although in a production app we wanna
54:59
make sure that we're actually getting the
55:03
number, this is just just for speed's
sake.
55:04
Okay, so let's start with a cursor.
55:07
Actually, what we're gonna do is copy and
paste because all we're gonna, we're gonna
55:09
use the same format here, but we're going
55:12
to add a where clause and where
parameters.
55:14
So let's copy everything here and paste it
below.
55:17
And the where clause is going to be a
string.
55:22
Let's call it whereClause, and we'll
define it up here.
55:26
We could define it here in the method, but
it just gets, it starts to
55:30
get a little too hard to understand so
let's add it up here, String whereClause.
55:33
And let's write the where clause we
55:41
want and then were gonna parameterize it
appropriately.
55:42
So here were going to select all
temperatures
55:46
where temperature is greater then the
minimum temp.
55:49
So where TEMPERATURE is greater then
minTemp, easy enough.
55:53
So let's swap out TEMPERATURE
55:59
with from our ForecastHelper, our
COLUMN_TEMPERATURE.
56:01
And let's swap out minTemp with a question
mark.
56:07
That is the placeholder value where we can
add our parameters afterwards.
56:10
So, here, we see the where parameters, we
can put minTemp here.
56:14
Sure, we could append it in a string this
is just showing different ways to do it.
56:17
But Just like we had to use a string array
for column names.
56:22
We have to use a string array for the
where parameters.
56:26
So, let's type, new String, and we'll put
minTemp as the only item.
56:30
Inside and we won't do anything else with
group
56:36
I having order by, but now we can use this
56:40
method back in our viewForecastActivity
and that code for the action bar
56:42
is down here in a method called
configureActionBar.
56:50
And I'm not gonna go in too much detail
about this
56:55
if you have any questions, ask me in the
forum afterwards.
56:57
But you can see we have an edit text, and
from that
57:00
edit text we're gonna use it
57:02
as the parameter for method called
filterTemperatures.
57:03
And that's where we will do our magic
work.
57:06
We will
57:08
call
57:09
mDataSource.selectTemperaturesGreaterThan
and
57:09
we'll just pass in that minTemp.
57:11
And when we're done we're gonna update the
list with that cursor, oh, I forgot to
57:14
declare a cursor variable cursor equals
the result from that.
57:19
So this filterTemperatures will be called
when
57:27
we hit Enter set out of,
setOnEditorActionListener.
57:29
When I hit Enter will use the value that
was entered in that edit text.
57:32
So let's run it.
57:37
So take a look.
57:37
Okay.
57:40
We'll give Genymotion a moment to reload.
57:40
[SOUND].
57:42
Okay.
57:45
And again, we're gonna have to insert more
data.
57:46
So we're just tripling or quadrupling our
data now.
57:49
Now, if hit on SELECT, we get the list
view of everything.
57:53
And now, let's see.
57:55
We've got temperatures in the 70s.
57:57
Let's see if there's anything greater than
75 degrees.
57:58
There's a few temperatures you see it was
filtered if we
58:02
put a high temperature of a 100 there
shouldn't be anything, right?
58:04
And if we do a low temperature freezing 32
then
58:08
everything is the same because there is
nothing lower then 32.
58:11
Okay, so that shows how we got two
examples here of
58:14
a select all and then a select with a
where clause.
58:17
So the rest are that's really you know
this
58:20
is the meat of using the SQL database
here.
58:24
The update and delete are, should be
trivial based on these examples, but
58:27
let's walk through them and make sure that
we understand everything that's going on.
58:30
For the update, we have an update button,
let's see what do we have it doing.
58:36
It's just an onClick and we're gonna call
it update method.
58:42
Let's just update everything to in the
interest of time,
58:44
we'll update all values to 100, it'll be a
heatwave.
58:49
So, back to our ForecastDataSource, we're
gonna do our update method.
58:52
Now, the update returns the number of rows
that have been updated.
58:57
So instead of a cursor, we're just gonna
return an int.
59:02
And, let's call this updateTemperature,
and we'll have a double for the newTemp.
59:05
Okay so because if you go back up to the
59:20
insertion, we used our content values to
insert the due data.
59:24
Well we're gonna do the same thing for
updating.
59:28
So let's declare ContentValues, call it
values equals new ContentValues.
59:30
Okay, and let's put in the new value.
59:37
So this is gonna be once again for
ForecastHelper.COLUMN_TEMPERATURE,
59:40
and the value is gonna be that newtemp
that we're passing in.
59:48
Oops, I forgot to add the method called
put.
59:52
Okay putting those new values in.
59:55
And then from the database, we're gonna
call
59:58
the update method, and this is similar to
1:00:02
similar to the other methods we're using,
but like
1:00:10
I mentioned, it returns [COUGH] an integer
of rows updated.
1:00:12
So let's set that equal there.
1:00:17
We'll return, rowsUpdated.
1:00:19
And then we'll fill in the blanks for this
update method.
1:00:21
So as usual, you can guess the first thing
we need is the table name.
1:00:24
And then the second is the values that
we're
1:00:31
going to update, and we'll pass in the
content values.
1:00:35
And the third, we're gonna pass in null,
it's a where clause.
1:00:38
And finally it's if we had an where
clause,
1:00:44
we would have parameters and passing null
there as well.
1:00:46
Okay, I think that's every thing we need,
so let's go back to main activity.
1:00:53
To the OnClickListener for the update
button.
1:01:00
And all I'll do is call
mDataSource.updateTemperatures
1:01:04
and we're gonna update everything to 100.
1:01:08
What we'll do is run the update and then
click on
1:01:12
the select button to make sure that all
the temperatures have changed.
1:01:14
[BLANK_AUDIO]
1:01:18
Okay, so here comes our app, we'll
reinsert, now
1:01:22
we'll UPDATE, okay, no visual indicator
here, but if we
1:01:25
go to SELECT, everything is 100, so
everything was
1:01:28
updated to 200 because, we didn't have any
filter in.
1:01:31
So if we wanted to filter, if we only
wanted to
1:01:35
update specific rows, we could use that ID
column that we added.
1:01:37
We'd have to get it first, let's just get
the appropriate
1:01:40
ID, then we can update the value for just
that ID.
1:01:43
And we won't walk though an example,
1:01:49
because it should be pretty straight
forward.
1:01:50
So you have an example now of how to add a
where clause.
1:01:52
You do the same thing down here in the in
the update statement.
1:01:55
Okay, so let's take a look at deleting one
more method, public void deleteAll.
1:01:59
We're just gonna clear everything from the
database.
1:02:06
And, it's a pretty simple method from the
database object,
1:02:10
we just call delete and let's add our
table,
1:02:15
ForecastHelper.Table_TEMPERATURES
1:02:20
and we could have a where call, where
clause we'll just pass in null.
1:02:25
And go to parameters.
1:02:34
I forgot this comment here.
1:02:37
I'm just leaving these notes for reference
for later on.
1:02:39
[SOUND].
1:02:42
Okay, and then we just need to call this
from
1:02:45
the onClickListener for the delete button,
so let's go back here.
1:02:49
Scroll up just a little bit and we'll
replace
1:02:55
this final to do, woops, clicking on the
wrong spot.
1:02:57
Android Studio condenses things just to
make, make it
1:03:01
easier to look at a glance what's going
on.
1:03:05
You just click on the gray part to, to
show it like this.
1:03:07
So from the data source, let's call delete
all.
1:03:12
And run it to verify.
1:03:16
And once the emulator reloads,
unfortunately, DELETE is grayed
1:03:21
out, so we'll reinsert data, and now
delete it.
1:03:25
And now if we click on SELECT, we can
1:03:30
view it, and sure enough, there's nothing
in there.
1:03:31
We try and filter, there's nothing to
show.
1:03:34
Okay.
1:03:37
So that's a simple overview of the basic
1:03:39
operations you're going to do on a
database.
1:03:42
That covers a lot of scenarios you're
gonna come across, your, and that's why
1:03:44
it's, it's an acronym that is common
1:03:48
across programming languages, create,
retrieve, update, delete.
1:03:50
One last thing I do wanna cover, though,
before
1:03:54
we move on, because it's important, is
upgrading the database.
1:03:55
So I'm just gonna do another example of
how to, how to do that.
1:03:59
And then show a few tools, and then we'll
be all set.
1:04:02
So for the upgrade, we wanna go back to
1:04:05
that ForecastHelper class that we haven't
touched in a while.
1:04:07
And we have this empty onUpgrade method
down here that we haven't used.
1:04:10
What happens is, let me switch to Chrome
for a
1:04:15
moment because I've got a article that
I'll link to
1:04:19
No I don't, okay.
1:04:24
Well I'll link to it another time in the
notes.
1:04:25
But the, when your app calls that onCreate
it checks
1:04:28
that version number that we're setting
here on line 17.
1:04:34
And as long as that version number is the
same, then
1:04:37
it's going to reuse the same database over
and over again.
1:04:39
But, as soon as that version number
changes,
1:04:42
then it will call the onUpgrade version
instead.
1:04:45
And that's how you can trigger an update
with a new version of your app.
1:04:47
So if we're releasing version 2.0 in our
app, we have whole new
1:04:51
database schema that we want, then
1:04:53
we're gonna increment this database
version number.
1:04:55
And when the user installs the update, and
the first
1:04:58
time they run it, it's going to upgrade
the database.
1:05:02
And it's up to us to make sure all
1:05:03
their existing data migrates over, if
that's what we want.
1:05:05
So, that's what we're gonna do here.
1:05:08
We're gonna migrate existing data.
1:05:09
And we're gonna do it with an ALTER
statement.
1:05:11
So let's add a new private static final
String, DB_ALTER.
1:05:15
And like before, let's write it in plain
SQL and then we'll add the parameters.
1:05:23
So we have an ALTER table.
1:05:28
[BLANK_AUDIO]
1:05:34
And join a blank on the alter syntax.
1:05:38
Okay, that's right.
1:05:44
ALTER TABLE with the table name
TEMPERATURES.
1:05:45
And then we're going to add a column with
a
1:05:49
new name TIME and it's gonna be of type
INTEGER.
1:05:53
The reason we're using integer for time is
because if we look at the data returned by
1:05:57
the API it's returned in a Unix time
1:06:02
format, which is second since certain time
in 1970.
1:06:04
So this is a, there are utilities converts
this to
1:06:08
human readable time, but we'll start on
the back end
1:06:11
as an integer and that's what, that's how
SQLite because
1:06:14
it's a light version that's how it stores
time data.
1:06:17
Okay, so let's just plug in the
appropriate values
1:06:21
here, so instead of TEMPERATURES let's add
the TABLE_TEMPERATURES variable.
1:06:24
Instead of the COLUMN TIME let's add a new
variable.
1:06:30
We call it COLUMN_TIME and just like we
have
1:06:36
COLUMN_TEMPERATURE, let's copy and paste
and make that time.
1:06:39
[BLANK_AUDIO]
1:06:44
Okay.
1:06:50
And to execute on ALTER statement, we use
the same exact method
1:06:51
db.execSQL and instead of DB_CREATE we
have DB_ALTER.
1:06:57
Now before we just run this though what
would happen if a
1:07:02
new user installed version 2.0 of our app
who didn't have version 1.0.
1:07:05
If we don't make sure to also upgrade that
DB_CREATE statement then they're gonna run
1:07:10
the old version and it's not gonna work
1:07:15
with our, the new code that we're
providing.
1:07:18
So we wanna make sure we update, if we're
ever providing new upgrade
1:07:19
statement, we wanna make sure that we're
providing a new create statement as well.
1:07:23
So let's change the create statement,
we're just going to append here, after
1:07:28
COLUMN TEMPERATURE, add a comma, space
plus, and then we have COLUMN_TIME.
1:07:33
Plus and it's a type INTEGER, and now our
1:07:40
Create Table matches the upgrade statement
from the alter statement.
1:07:44
Okay, so that's all we need to do.
1:07:49
well, no it's not because this is the
important part that I, I kept stressing.
1:07:51
We need to we must increment to trigger,
an upgrade.
1:07:55
So instead of version one, we are now
dealing with version two.
1:08:01
So
1:08:04
now let's try it and see.
1:08:07
And we're gonna go back in to the command
line in a moment, and just verify the
1:08:08
data structure, data structure change,
we're not going
1:08:11
to write any additional code, to look at
it.
1:08:14
[BLANK_AUDIO]
1:08:16
Here in the code should, well it should
1:08:20
have been executed right away in the
onCreate method.
1:08:23
So if we go to the terminal and we can do
schema for temperatures.
1:08:26
And sure enough, you see that the schema
changed.
1:08:37
And now in addition to the temperature
itself,
1:08:39
we have the time in the integer format.
1:08:42
But if we select star from temperatures
1:08:44
you see that this blank values.
1:08:50
Because we didn't actually insert the
time, we haven't changed our code.
1:08:51
But you can see that the the column ID has
been incrementing.
1:08:54
We still have the temperatures, and now we
just have a
1:08:57
blank value, and we didn't have these
vertical pipes to separate before.
1:08:59
It's just a visual cue, to let us know
there's an additional column there.
1:09:02
[BLANK_AUDIO]
1:09:04
Okay, so that's all I wanted to cover in
the app itself.
1:09:07
So again, it's an overview of the basic
C.R.U.D operations,
1:09:11
an upgrade, how to create and set up your
database.
1:09:13
But, as you do more and more with SQL
1:09:16
databases, you may start to get involved
with using the
1:09:18
content providers that we talked about, or
you my wanna
1:09:21
use some additional tools to make your
development life easier.
1:09:23
So, you may be familiar in
1:09:27
other environments with abstracting that
data away.
1:09:29
We got that data source object, but we can
take it even further by
1:09:31
using model objects that have additional
database
1:09:35
behavior that we can access from our
activities.
1:09:38
So I, I'm gonna give a quick run down of
1:09:41
a few things, a few tips for SQL
development for Android.
1:09:43
The first one here is a really useful way
to run some commands
1:09:47
in the ADB shell that makes all SQL
statements logged to log head.
1:09:51
So this is great for debugging instead of
having to log your own statements.
1:09:56
If you turn this on than every single SQL
1:10:01
statement from the device will be logged
in log head
1:10:03
and you can take a look and make sure
1:10:06
that the SQL you're expecting is coming
through the device.
1:10:07
Next tool here I really like, it's an open
source library called android_dbinspector.
1:10:12
And the way it works is, this version is
just for Android Studio so
1:10:18
to get it to work with Eclipse would take
a little bit of work.
1:10:24
But it's a library project that you set in
your existing project.
1:10:27
And then you change your manifest, you add
a debug manifest.
1:10:31
It's a little bit convoluted if you want
to try it out and
1:10:35
you have any trouble, certainly stop by in
the forum and let us know.
1:10:38
But once you get it up and running, when
you run your app from Android Studio, it
1:10:41
will load up this DbInspector app on the
side with a, a separate launcher.
1:10:46
And you can view the data for your app and
you see
1:10:53
in the screenshots here, there's the
1:10:58
database file, it's got it's individual
tables.
1:11:00
If you click into tables, you can see
1:11:02
the structure and you can also see the
contents.
1:11:04
So it's a nice way to look on the app at
the data behind the scenes.
1:11:06
And then lastly I have two different
versions of
1:11:11
how do you wanna call these?
1:11:15
Abstraction models for taking your data
into plain Java
1:11:16
objects that you can then reuse in your
app.
1:11:20
And that just makes your code easier to
understand and maintain.
1:11:22
If you're working with a forecast object
instead
1:11:24
of working, writing your SQL and working
with
1:11:27
it directly, then your, your code in your
1:11:30
activities and elsewhere is a lot easier
to understand.
1:11:31
The first one here is called greenDAO.
1:11:34
DAO stands for data access object.
1:11:37
And this is our wrapper around the SQLite
database you can see here they
1:11:39
have some claims about being increased
performance
1:11:45
small memory consumption and easy to use
APIs.
1:11:51
I have read, I haven't used this for
myself, but I have read
1:11:53
reviews and performance tests that say
this is a good library to try out.
1:11:57
And the last one was just brought to
1:12:02
my attention yesterday, I was talking with
one of
1:12:03
our mobile developers, Joe Steel, and he
mentioned cupboard,
1:12:05
another library that does some of the same
things.
1:12:07
It extracts out that database layer and it
uses plain old
1:12:10
Java objects to, and, and here, the
example, I think illustrates.
1:12:14
So, in your activity, you'd have this,
this code
1:12:18
with the cupboard library with the
database that you provide.
1:12:20
You put a book object, so it puts that
book into the database.
1:12:24
And makes it a little bit cleaner to
insert or extract data from the database.
1:12:27
So, that just about does it.
1:12:34
Thanks a lot for sticking through to the
end here.
1:12:36
I'm gonna check and see if there's any
questions at the moment.
1:12:39
And again if not, if we don't get to them.
1:12:41
Then we will catch up on the forum.
1:12:42
Okay, so, Jonathan asks is there a reason
why I
1:12:46
decided to use big decimal versus the
auto-boxed double class.
1:12:50
no.
1:12:57
No I could have just used that.
1:12:59
I feel like I may have had a reason,
before, but no, yeah, so, yeah.
1:13:01
What that's referring to is where we're
taking the double
1:13:06
values, the primitive values and is
adapting them for the display.
1:13:08
We could have just used the double with a
capital D as the, the more complete
1:13:12
object datatype that, that's usable within
the array adapters, and within the list.
1:13:18
Okay.
1:13:26
Looks like there may not be any other
questions at the moment.
1:13:28
Okay, great.
1:13:30
Again, if you have any questions follow up
in the forum
1:13:31
or find me on Twitter @benjakuben, and
thanks so much for watching.
1:13:34
See you later.
1:13:37
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up