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