[MUSIC]

Hi.

My name is Ben Jakuben, and I'm the Android teacher here at Treehouse.

Thanks for joining us today.

Before we get started I just want to say, if you

have any questions during the presentation please type them below the video.

And we'll try and get to them at the,

at the end or if it's especially important then

my friend Chris will flag me down and we'll,

we'll try and answer it while we're doing the presentation.

If we don't get to your question, or if

you're watching this later and you think of something,

then post it in our Treehouse Forum and somebody

will try and answer it for you, myself included.

Or you can find me on Twitter @benjacobin.

All right, so we're going to cover the basics of SQLite for Android.

And the first question we wanna ask ourselves is what exactly is SQLite?

It is a light waist, lightweight SQL-based database for storing data in an app.

It's available in all versions of Android and a good example to think

of is the contacts app, where you

have information about all the different contacts.

If you think of the database that sits behind

it you've got people's names and phone numbers, pictures

maybe, and that's all stored in a way that

you can access it from that app at any time.

If it were just well, let me go ahead to the next slide and explain.

Why exactly do we wanna use this?

It's so that we can store and retrieve data within the

app across sessions, that's the big thing, this is about data persistence.

When we have data in an app, if we're running it you know, in an activity

and if the app is visible on the screen, then that data in memory is available.

But, as soon as we close the app, that data in memory goes away, so

we need to store that data in memory on the phone or on the tablet somehow.

So that's why solutions like SQLite databases

are used to persist that data across sessions.

The reason we're looking at SQLite

specifically is, very popular with Android developers.

A lot of software developers are familiar with SQL databases

and using the SQL language to access and store data.

You might be wondering if it's secure, well, it is in

that the databases exist only within the context of the app.

Android has this concept of sandboxing apps, where apps and all

of their related files, databases, et cetera are all in a restricted

section of the file system and no other apps or anything

else can access that part of the file system without explicit permission.

So if you wanna give access to this data to outside apps you have to

explicitly allow it using a content provider which

we're gonna talk about briefly in a moment.

The other thing is when you uninstall an app, it does usually delete

the data as far as I know from, I forget which version of Android,

there may have been some inconsistencies earlier on, but when you delete an

app it, corresponding database, and all the

files in that sandbox should be deleted.

We're gonna keep things pretty basic today.

We are going to talk about the C.R.U.D.

operation, so if you are familiar with web development

or other, you know, working with data, then these

are the basic operations you can perform on data.

The acronym C.R.U.D. stands for create, retrieve, update, and delete.

Now in the syntax of SQL that is instead insert, select, update and delete.

And I should mention about SQL specifically, SQL

stands for, SQL stands for a Structured Query Language.

Now if you're not familiar with SQL then we

have a fantastic course by Andrew Chalkley called database basics,

and it will get you comfortable with creating databases, manipulating

the data, and it's all based on the SQL language.

We're gonna work with very basic data we're going to get some forecast data

from the web and we're going to store it in a table, and all

we care about right now, just to show how to use these concepts in

Android, we're gonna do one column of

data, which is numbers that correspond to temperatures.

We are also going to talk about upgrading databases.

So, that is when you're, let's say in our app we are tracking information about

a user and we have a user name, and an email address, and a picture.

Well, we released a new version of our

app and now we wanna track additional data, too.

Let's say we wanna store their favorite video game for some reason.

Well, we need to amend the data that's currently in our system.

And there's different ways we can do that, we

can, because we have an existing database for the

people who have the previous version of the app,

we can either drop the database and re-create it.

Migrate the data over somehow or we can alter tables in place and add new columns

like this where the existing data stays the

same and then we populate the new columns.

So, we'll take a look at that later on after we cover those C.R.U.D. operations.

Now, the last thing I mention before we get

started with actual code is the idea of content providers.

A lot of times you'll hear content providers

talked about in the same conversation as SQLite

databases, and a content provider is an Android

construct used to manage structured sets of data.

And a database is a structured set of data,

they're primarily intended to be used by other applications.

Which is why I said you have to explicitly provide

access to your data to other applications through a content provider.

But you don't need a provider to use an SQL database

if the use is entirely within the context of your own app.

So, that's what we're going to do today.

We're just building a very simple app that's going to have its own data.

We're not going to provide it to anywhere else.

That's a topic for another workshop or another Treehouse course.

But I do wanna just mention before we

get started one more thing about the content provider.

There's a, a good discussion that one of our mobile developers Joe

Steel mentioned to me about the benefits of using a content provider.

It is a best practice and this is a Google Plus conversation that was

happening with some Android developers about the

pros and cons of using a content provider.

I'm gonna provide all these links for the

workshop when this is published in a few days.

So anything that we cover on the web come back when this is published in a

few days and you'll be able to find these links or you can search for this here.

Okay.

So, what exactly are we building?

We are going to build a simple weather

forecast app using an, an API provided by forecast.io.

Now if you're not familiar with forecast.io, it's a web-based weather

app that is available also as a web-based app on your phones.

So, it's a, it's a really great example of the kind of web app

you can build on a phone using web technology that's not a native app.

It's completely built from the website, but they do some really neat things with

animations and making it feel like a

native app despite it all being web technology.

And the reason we're using this site is they have a friendly

developer portal where you can access their API to get some forecast data.

It's free, you can sign up if you

just come here to developer.forecast.io and register, you

create an account and you get an API key that unlocks your access to their API.

That way they can shut you off if you're abusing

it [LAUGH], but it's a pretty, I, I don't remember

the exact numbers but you get a lot of calls,

so, it's really useful to build some simple weather apps.

And they, they give you a lot of information, so I've

switched here to the output from the API the forecast call.

So, this is a, a JSON output and I'm

formatting my JSON with a chrome extension called JSONView.

You can see the plain source looks like this, but, if we close this and go

back we get information about our current location,

and then we get different types of forecasts.

The current conditions, minutes, hourly, daily forecasts.

We're going to look with the hourly forecast because it

gives us a set of 48 data points to work with.

And because we wanna show, inserting multiple data points and manipulating

it this is just a simple data set of 40 data points.

We know we're getting and we can work with it from there.

Okay.

So lets get started with our code.

I'm building this app in Android Studio.

And our Treehouse projects currently use Eclipse.

But everything we do here in Android Studio

are the code you can just transfer to Eclipse.

The only thing that's different is the project set up itself.

So when you create a new project you have to manually add these code

files in Eclipse, there's no easy way to migrate from Android Studio to Eclipse.

The reason I'm using Android Studio is

because the tool itself just gets better and

better and I want to encourage you

if you're comfortable to try out Android Studio.

Take a look I had an different workshop

a couple months ago about getting started with Android

Studio, so if you want to check that

out it's available now as an Android Tools course.

So you can review getting started with Android

Studio, and then take a look at this project.

All the code that we write is also going

to be available for download, so we'll take a look.

And the idea is, we're gonna walk through it, write some code, explain what's going

on, and then you'll have a nice simple example to get started with your own code.

You can take these examples and, and either copy it and, and manipulate

it, or maybe even use some of it directly in your own applications.

And, it's the kind of thing where it's good to know how you do

it, but then once you get an example, you can just reuse your existing code.

So let's before we do anything, this code is incomplete but let's

run it just to get a quick look at the app itself.

[BLANK_AUDIO]

Okay and it's about to load here on my Genymotion emulator and

this is another topic that I covered in that Android tool's course.

A Genymotion is a third party emulator that works usually works better than the

default emulator, it's very fast they have

all sorts of different devices you can try.

So in general I, I highly recommend

using that for your development purposes as well.

It's the next best thing to having an actual device to develop on.

So you can see here this app just has two activities the first is this front one

has our C.R.U.D or I.S.U.D operationa, insert, select, update and delete.

And we're gonna start with just creating the database and inserting some data.

Makes sense it's a good place to start.

So back in our project we have, here's the two activities we were working with.

And the services package here, I've got my, my code organized into

a couple of sub packages, so that UI packages for our activities.

Services is for the code related to getting data from the web.

So I'm not gonna review any of this, it's here.

You can get the data from the web however you want whatever data you're going after.

But I'm using a library called Retrofit it's an open source library from Square.

And I haven't used it much, but what I have used

I really like, and so I recommend checking that out as well.

Could be a topic for another live stream, but

the the code here just as a quick review.

You give it the base URL, and then you configure

your, your weather service or whatever service you're running for

and the, the library makes use of these annotations, and

it generates a lot of code for you behind the scenes.

It does a lot of magic but it does it in

a way that, that makes it very clean and easy to understand.

So here the method to load the forecast data, we create a new

rest adapter because our, web service we're going after is a rest API.

And we call this get forecast and it runs asynchronously.

So, it goes out, requests the data, it

uses the key and our current location and then

it, when it's all done, when the asynchronous

call finishes, it calls back with this callback method.

Now, this is that common asynchronous processing with a callback

that we've seen in all, a few of our Android projects.

So, when it calls back, we'll go back, so we're calling this from our main activity

and when the data returns, we pass in this call back down here.

And it's a retrofit callback and it has

two different methods that we need to override.

First is success, which will include a forecast object

and that's the other object we have over here.

This is a mapping of the data from

the JSON file, this is a very simplified version.

To map the entire data set would be a bit more

of a complex class structure, but this drills down the reason

I have it minimal like this is so that we can

get after that hourly data, the temperatures, specifically, that we want.

So, on a successful call, let's go back

to the MainActivity, we get that forecast object.

And, right now we're just looping through.

So, if we run this, we are gonna run it just to make sure in the

log we, I'm, I'm out putting the current

temperatures just to make sure that we're getting data.

Let's proceed with actually creating the database.

So, I've got this other sub-package here called db

and that's where we're gonna put our database classes.

So before we write anything let's take a look at the android documentation.

Now, I'm a little bit in general I really like the android documentation.

I think they do a great job of explaining how to do things.

Providing example code of, I use it all the time I use in our courses but the, the

documentation about SQLite databases is a little bit obtuse

and it's, it's Well, it is what it is.

So hopefully this course is enough [LAUGH] to get you started and then

you can work your way through

the navigation through the documentation as needed.

But if you search for saving data, you can find

this reference of how to save data in SQL databases.

And we're gonna walk through these steps but the, the first thing

we need to do is create a database using a SQL helper.

And we do that by extending a base class called SQLiteOpenHelper.

So we're gonna create our own custom class that will extend

that base class, and take care of the create operations for us.

So if I right-click on db and select New

> Java Class, we're gonna call this our forecasthelper.

Okay, and we are going to extend the SQLiteOpenHelper.

Okay?

And right away, we get an error that we were missing some required methods.

SQLiteOpenHelper is an abstract class which means we can't use it directly.

That's why we need to subclass it like this, and we can

use Alt+Enter to bring up a quick fix for implementing the methods.

And if I enter here, we can create the two

methods that we need, this will be just the stubs.

Click OK.

And there you see the first one is called

onCreate, which is used to, obviously, create the database.

Second is called onUpgrade which we'll look at a little bit later on

and that's when we wanna upgrade the database from one version to another.

Okay, so we still have a warning here about

a missing constructor, so let's take care of that.

Now, if we, if we do a quick fix here, if I hit Alt+Enter

and create the constructor we get a

little more, little more information than we want.

This is to create it with passing in a bunch of

information, but we're gonna just make this information part of the class.

So our constructor will instead just require

the context, which is a common, common pattern.

And then we will add these individual parameters here in the class.

So the first parameter here is the name of the database.

We can name this whatever we like, and let's make it a constant here.

And we're gonna make it a public constant for the

database name, well, actually no, let's make this one a private.

Some of these constants we're gonna

make public, because we'll reference them elsewhere.

But let's make this a private static final

String call DB_NAME and we'll call this temperatures.

And we have to give it the .db extension.

Okay, so now we can use this DB_NAME here in the construct.DB_NAME.

Now the next parameter we just are going to

pass in null, this is what's this parameter, it's a

I don, I don't remember off the top of my head but we don't need it for this.

And the version number is just an integer version and this

is how we know if we're upgrading the database or not.

So let's make this a private static final int, I'll call a DB_VERSION,

and I'll set equal to 1, because we're just starting of with 1.

It's an integer value so its gonna be 1 and above,

and we need to increment the version when we're upgrading database.

So its passion, passing DB_VERSION here, and now, our constructor

is complete, and we can focus on the, onCreate method.

Let's go back to our documentation.

All right.

Okay.

I apologize.

So the next thing you wanna do in here is use

our, database object to run some SQL to create a table.

So, the next thing we need [LAUGH] is a, the table name that we want.

This is the constant that we're going to make public.

So the first final String TABLE, and we'll call this

table TEMPERATURES, and we'll just set this equal to.

We'll do these in all caps too as a

common SQL convention to write in all caps, TEMPERATURES.

Okay.

And, let's, you know, let's, let's define our database create statement.

So what I'm gonna do is use the SQLite database

that's passed in here as the parameter into the onCreate method.

It's called db.

So, if we type db and look at the content assist here then we see

a, a command for executing SQL and this is where we can write raw SQL.

Now, in some cases with working with SQLite, we wanna write

we, in some cases we will write raw SQL like this.

Where it's a create statement that's just a basic SQL

statement just like you would see working with any database.

Other times we're gonna use some methods and some classes to

encapsulate that SQL and work with it in a different way,

a more modular way that makes for a cleaner code for

us, it's a cleaner interface between our app and the underlying database.

But here we're gonna pass in a string called DB_CREATE.

And then let's go up here and create that, with the variables we define so.

Another private static final String named DB_CREATE.

And right now we're gonna plug in some variables, but let's, let's write

this as if we were writing it right in some kind of SQL editor.

So we are going to write, CREATE TABLE, and the name is TEMPERATURES.

And when we create this table we need to define the

columns that are included and the data type of those columns.

So the first column, and they go in parenthesis here.

The first column we're going to use is a primary key that's just an integer ID.

And we don't need it for this example like we saw in the, the full

screen earlier with we're just gonna work with

temperatures, it's just gonna be one column video.

However, if we include this ID column that

I'm about to write, it's common convention for working

with a SQLite in Android, and we need that

column to work with certain things in the future.

So for content providers for example, or a content adapters

when you're adapting your data for views, they require that ID

column be in place, so it's a good practice to

generally create it, even if you're not gonna use it yourself.

We don't really need to worry about it.

Once we create it, we're done with it, because we're gonna create it in such

a way that it auto-increments in the

database by itself every time we insert data.

So, the first column the convention is _ID and it is of a type INTEGER.

And then we're gonna add that additional information

to make it the primary key, that auto increments.

Okay.

The second column is our TEMPERATURE column, and

a data type for this is just REAL.

So there's just a few basic datatypes available in SQLite and

they're REAL, datatype is for numbers that have a decimal value.

So the, if we take a look at the data that we're getting

back from our API, we see the temperature is given with a decimal value.

So we're gonna maintain that accuracy and store it with a decimal in our database.

Okay so now, let's plug in the actual values, the variables instead of

having this all hard coded, so here,

instead of TEMPERATURES, let's plug in TABLE_TEMPERATURES.

Okay.

And then we're gonna create column names as well, for these variables.

So instead of _ID, let's use a variable called COLUMN_ID

and we're gonna add that in a second and then also down here let's do

COLUMN_TEMPERATURE and leave that data set.

Okay.

So now we just need to define those two variables, I'm gonna copy and paste here.

First one COLUMN_ID and this is where that, we had that _ID.

Second one is COLUMN_TEMPERATURES and I'm sorry just TEMPERATURES since this is an

individual row, an individual value within our

temperatures table, so we just use temperature.

So now we have these variables to find our create table statement is ready.

And we can execute it here within the context of our onCreate method.

So we're almost ready to create our database

and make sure that everything is in place.

But before we do that we want to introduce another class.

We have, we're going to use two database classes to access our database.

This database helper is what we use for

defining the database and creating and upgrading it.

But then we have a separate class we're gonna create

called forecast data source that's going to act as a an

interface on, on top of the, this forecast helper and the

forecast data source is going to do all of our operations.

So our code in our app is going to use the call methods from

forecast data source and in there it will execute on the underlying SQL database.

So let's right-click on the db package, select New >

Java Class and like I said, this one's called ForecastDataSource.

And click OK.

And this one doesn't have to extend anything, it's just that we are

going to make use of that data helper class that we just defined.

Okay, so the first thing that we need is a SQLite database object itself.

So let's define a private SQLiteDatabase, named mDatabase.

And then we want our own version of that forecast data, forecast helper.

So we have private ForecastHelper and we'll call this let's call

it mForecastHelper.

Okay.

So let's start with the constructor, we're gonna, we're gonna write

this code, and then we're gonna use it in our main activity.

So the constructor is public ForecastDataSource, and we're gonna pass

in the context again, we need to pass it along to the ForecastHelper class.

And all we wanna do here is, well, I

should have created a context variable to hold on to.

So we'll do private Context mContext.

And now we can set mContext equal to the context.

And the second thing we wanna do is use our instantiate our forecast helper,

so that's equal to a new ForecastHelper and that requires the mContext.

So we pass that in.

Okay?

And now this class is going to be responsible for a couple different things.

So let's add some comments here, and we're gonna, we're, we're gonna define what

we're gonna do in this class and then we're gonna do it piece by piece.

So the first thing we need to do is open a database, and then conversely close one.

And then we're gonna do those C.R.U.D operations,

insert, select, update, and delete.

Okay, and I know we're doing a lot

here before running anything, and you know, generally I

like to do as little as possible, and then test it to make sure that it's working.

But we'll get this basic stuff setup,

we'll run, and then hopefully it'll make sense.

We'll walk through exactly where each piece is cuz you know,

we have these couple different pieces moving, but once we get

into the main activity, and call it, it should be more

apparent as to what's going on behind the scenes for us.

Okay, so let's start with the open method.

Let's do, so call this open and this one is special

we're gonna throw a SQLException just in case anything goes wrong.

As a generic SQL error that we wanna watch out for with certain operations.

but, yeah, so, this is just one line of code and it's from our forecast helper.

So we set our database, the SQLite database

from the forecast helper, using a method called getWriteableDatabase.

And this is the important line of code,

this is really what we were building toward.

So getWritableDatabase is from that SQLiteOpenHelper method, open help.

I'm sorry SQLiteOpenHelper class.

And it's a method that will create the database if it

doesn't exist, or just open the database if it exists for writing.

Now in content assist you may

have seen there's a different version, getReadableDatabase.

That's where if you want to have a read only database, but we're

gonna be inserting data and updating so we definitely wanna be able to write.

So now this is ready to use within our main activities.

So let's switch over and what I've done here is

I've left a lot of code based on the UI

and the buttons and things like that, things that you'll

hopefully already know how to do based on our Android courses.

But I've left notes for us to work through on

and the pieces we need to get the data in place.

So the first one up here is to declare a data

source, and that's gonna be using our new forecast data source class.

So let's make this a protected

ForecastDataSource, and we'll call it mDataSource.

Kay the next thing we do, wanna do

is instantiate that when the activity is created.

So sure enough down here there's

another note, mDataSource equals a new ForecastDataSource

and remember we need to pass on the context so we'll use MainActivity.this.

Okay.

So this creates our data source object and now

that we have it, we can open the database.

So we just call mDataSource.open and then this show

to create the database and open a connection to it.

So that's another thing we need to talk about is database connections.

So the database the way it works is we once

it's created, we open a connection to it, and that

connection we use to write and retrieve data, and when

we're all done with it, we have to close that connection.

Now it's important to manage opening and closing appropriately, so

that we don't leave a connection open when our activity is

destroyed or our app closes because you can end up with

no pointer references or memory issues, or corrupting the database somehow.

So we have to carefully correspond,

carefully match opening calls with closing calls.

So there are a couple of different ways you can do this.

One way would be to whenever you wanna perform a SQL operation,

you open a connection, perform that operation and then close it right away.

That's fine, although if you're doing a

lot of operations in an activity, you'll end

up with a lot of open and closing

calls which there's nothing really wrong with it.

The performance, it is minimal, but it just kinda clutters your code.

So, in general, what, what I like to do is open

and close my connections in life cycle methods of the activity.

So just a quick review of the life cycle of an activity.

When it's first created this onCreate method is

called, and that's where we set up and

instantiate a lot of variables, data we might

need tie things to the layout et cetera.

As soon as it's onCreate method is called, as

soon as it finishes rather, the onResume method is called.

So those, those two life cycle methods are piggy backed

on top of each other when an activity's first created.

Then when that activity is sent to the background

whether the user navigates to another activity or a

different app, closes the app, the onPause method is

called, so you always think of onResume and onPause together.

So if we open the database in onResume, and close it in onPause, then we'll make

sure that the database connection is only open

while our activity is active on the phone.

And that's the behavior we're after, so that's what we're gonna

do here is we're gonna move this mDataSource.open to the onResume method.

So I've got it down here again a little note let's just paste it in.

In here onPause we want our corresponding closer, and so we call mDataSource.close.

But wait we haven't defined the close method yet, so

let's switch back to ForecastDataSource and this is real simple.

We said we were, are going to use a close method, public void close,

and all we do is from this database method call the close method itself.

So this is, again, just a wrap where

this is an instance where this ForecastDataSource is really

just a wrapper on the call, so that we

aren't interacting with the database directly in our code.

Alright, so at this point, there is no errors

in the project and it should be ready to run.

So and this is, I've got a brand new emulator instance here

and I ran it without any database code in here.

So there's, there is nothing currently on

this Android phone, on this Android emulator.

So if we run it, we should create a database, and

we'll just wait a moment for it to load on the emulator.

Okay, there we go, oh, look, I got my log

head back [LAUGH], I forgot when I run the project.

That'll bring it back all right so nothing to

see here in app yet because we haven't done anything.

But behind the scenes, we should have created the database.

So how can we tell?

Well, if you remember we created a database name called TEMPERATURES.db.

And that's an actual database file that's available on our file system.

So there's different ways you can look at files on the end of your file system.

If you're using Eclipse, there's a built

in file explorer from the DDMS perspective.

But you can also use command line tools.

And that's what we're gonna do here because there's an additional

command line tool that interacts with SQL directly, it's called SQLite3.

So let's take a look at how to use that tool and make sure that we

are in fact, that we do in fact have a database created behind the scenes here.

So in the terminal it depends on how you have your environment set up.

You may need to navigate to the Android SDK.

So if you look here I've got I'm in the Android SDK that corresponds

to Android Studio and specifically I go into platform tools and if I type

ls for list, then I see a couple different tools available and what we're

gonna use is this one called adb which now adb stands for Android Debug Bridge.

So the one thing I wanna show is you can see the currently connected

devices if I do ./adb devices, then you could see the list of devices attached.

And this is the serial number for my emulator

instance and we need that to connect to SQLite.

So let me go back to Chrome for just a

moment, that's the next tab I wanted to show you.

This is the, tool from the command line that we're gonna use to take

a look at our database, and this is available in, under the Tools section.

There are a lot of different command line tools

available in Android, and we haven't really talked about

many at Treehouse, but if you go in through

a command line to a connected device like this.

You can do some of the powerful stuff to

make sure that your development is proceeding as planned.

So the next thing we're gonna do is execute, enter the remote ADB shell,

using this following command, and we need that serial number that I just showed you.

So the next line type ./adb -s for serial

number and then I'll just copy and paste this.

And we want the show.

Okay, now this is a shell on a device itself, on

the Genymotion emulator, so here we can use that sqlite3 command.

And if we just run this, sure enough we

get the three corresponds to the version of SQL.

And here we got a SQLite prompt and you know, depending on, on your

familiarity with SQL development you may have

used tools like this on other systems.

But there's not much here, we need to

instead open up to our, our own database specifically.

If you type .help you can see a bunch

of different options these are different commands you can run.

You can type straight SQL in here as well and

execute it as long as you end it with a semicolon.

But if we look here at the databases we should get the

list of databases but it's not, this is not for our app specifically.

We need to instead quit here, so we do .quit and we wanna

open the connection to our database and here you can use the format here.

So where is it?

Here we go, this line here shows the path to emulator device instance.

It's a data/data/package_name/databases, and then you

append the database at the end.

So, let's type that out, and that should make more sense.

So, sqlite3 and then we pass in /data/data/com.teamtreehouse.,

and this is called, I called this friendlyforecast.

And /databases/, let's see if that's correct.

Yep.

We called it temperatures.db, so that should

open up okay, we've got the command prompt.

Now if we type let's see databases i think it lists.

Yeah, there you could see the file that corresponds the main database

because we're in our temperatures.db, you can see the file name itself.

Now if we look at the tables we should have one table temperatures.

Well there's an android meta datatable that's included

by default as well for your SQLite instance.

But temperatures is the one we define and you'll notice, you'll

notice that matches exactly what we created in our ForecastOpenHelper class.

So before we're go on, let's make sure this is what we expect.

We can look at the schema by typing schema and then giving the table name

TEMPERATURES, and sure enough, there is the exact create statement we defined.

It's got the _ID COLUMN and the second one is for TEMPERATURE.

Great, so that means our data is in place and we can start using it.

So, let's insert some data.

So coming back to Android studio.

We're gonna just work our way through this

class, we have instantiated the, the database we've opened.

We created it, opened it, we're closing it on onPause.

So now, let's take a look at the insert so let's see where we insert it.

So, if we go back to the app we're gonna have these couple of

buttons here and there's nothing to view to SELECT, UPDATE or DELETE right now.

So those buttons are greyed out.

But let's add the code to insert.

So there's an insert button, [COUGH]

and when it's on the click listener,

we're just calling this method called loadForecastData.

So if we scroll down a bit,

we see that loadForecastData calls our ForecastService.

And it calls at loadForecastData which executes a call back, so we're passing in.

This is again what we went through when we took

a brief look at this class, this is that loadForecastData.

So this call back gets executed when the data is returned, and that's down here.

Right now we're just logging the data, but now we can go ahead

and insert our new data but we need a method to do that.

So let's go back to our DataSource class, because that's

where we're writing our interface to all the database operations.

And for the rest of these operations we're going to

make use of a SQL construct known as a cursor.

So each of these methods is going to return a

cursor, or in some cases, a, a, a different value.

But what the cursor does is, we say to the database, okay, go and get me this data.

The database returns something called a results set, and it's a

set of data in whatever format we need, whatever we requested.

And then we use a cursor to iterate through that data however we want.

So you can think of it as like a block of, of rows in

the table and we say with the cursor, the cursor points to that block

and we can do operations like, okay go to the first row and it'll

go there and from that cursor we can then access the values in that table.

Then we can move the cursor around, we can just

loop through, we can move to the end, different things.

But right now with an insert what we wanna do is create our

create the data and, and store it into the table, into that temperatures table.

So, the first thing, let's, let's create the method itself.

Let me just glance at my notes.

okay, public void, insert forecast, and we had that

forecast object which has all the data we want.

Remember, that forecast object is returning from the web service, so, we'll

pass it in right here and we'll, we'll manipulate it from here directly.

[SOUND]

Kay, and, and, I, I got a little bit ahead of myself talking about the cursors.

That's actually more for selects and updates and deletes, but for the this one

because we're inserting, we're just going to

insert and assume success and come back.

We're not going to look at the data that was

inserted with the cursor, although you could certainly do that.

So, let's see, what should we do here?

Let's insert, we're gonna insert one row first,

and then we'll talk about inserting multiple rows.

So to do just one simple insert, now we're not gonna write raw SQL, we're

not going to create a SQL string that says insert into table values blah, blah, blah.

We're going to use a method from that sequel

open helper, from the sequel I dated as class, rather.

So the first thing we want to do is when we pass in values

like this whether we're inserting or updating

we use a class called content values.

We'll name this values and we'll just initialize it with a new content values.

Okay, but now it's kind of like a dictionary or a hash map, things

you might be familiar with, the contact values, we put things in to it.

So for these values we put a key at a value.

So the key here, because we're inserting in to our table, we're going

to insert data in to that temperature column, so we want to temperature key.

And sure enough that's why we made

this public constant if we type forecast helper.

Dot column temperature, that gets us the appropriate

column and now we just need a value.

Before we dive into the forecast object let's

just, let's just test it with decimal value.

We'll do a nice, beautiful 75 degree day okay.

And now we can use these values in the insert itself.

So, from our database object that we have running here.

We want to call insert and the first

parameter is the table, so forecast helper.table temperatures.

Second parameter is a null column hack, that's if

you, if you want to have null data in

your table, you can allow Null for certain columns,

and this is, well we're just gonna use null now.

But you can provide a value there if you need to get around

some restrictions with your null columns, it does not matter for this purpose.

And then here the last parameter you see are those content values.

So, what's happening is, we're gonna insert into our temperatures table, the

values that we just defined, so it's gonna go to, column temperature.

It's gonna add a new temperature of

75 degrees, and it's going to auto-increment that

id column that we defined, but you can

see here we're no manipulating that id ourselves.

It auto-increments in the database behind the scenes,

and that should be all we need for this

insert so let's go back to our main activity.

And now we can replace this to do with an actual

insert, so once again, from the data source, we call, insert forecast.

And lo and behold, we have a nice forecast variable we can use that was returned.

Okay?

And these methods here update high and low, we're actually not gonna

get to that, because we, I don't think we'll have enough time.

And then enable that other button.

So, once we insert data, we can go

ahead and start looking at select update and delete.

So let's just save this and run it,

snd make sure that we're actually inserting data.

Okay, we'll give Genymotion just a second to reload.

There we go, okay.

Now, if we type, if we click on

insert, we should see the buttons enabled, assuming success.

Great and you see these aren't, doing anything right now.

That's okay, but how do we know that we actually inserted data?

Well, we can go right back to the terminal and it didn't have any data before but now

if we run straight SQL here, if we select everything, select star from temperatures,

there we go.

We have one row, the very first ID is 1, because it

starts auto-incrementing from 1, and there's the 75 degrees we put in.

Very good.

Notice that the case the SQL is case sensitive or it's case insensitive rather.

So I typed in lowercase although common

convention is to type SQL commands in uppercase.

That's why we have uppercase names in our classes.

Okay, so let's take a look, we have 48

rows of data to insert, so let's input, let's

put all 48 temperatures into our table, so we

need to do that back in our forecast data source.

And the forecast itself crea, has an array

of hourly forecast and we can just loop through

each one, and put them in, however, there

is something we want to think about before that.

If we just looped through and did it like this using the database

insert, then, behind the scenes, there's a little bit of a performance hit.

And, every time you run a SQL command, like this, the data is inserted

and then immediately, it's committed, so any

kind of change is committed right afterwards.

And, when you're doing bulk sequel operations,

whether it's an Android or any other system.

What you want to do is try and delay those commits until you're either completely

done or maybe it's staged commits along the process if you have a lot of data.

So what we're gonna do is we're going to group all of these

48 commits, all of this 48

insertions, rather, into one single database transaction.

So the transaction will start, we will insert 48 pieces

of data, and then we'll finish the transaction and commit it.

That just saves us a little bit of

overhead because we're writing to the disk for

the data base, you know, it's going to

be completely negligible, the performance hit on this app.

But when you're working with large amounts of data you

may see a little bit of performance degradation, and another thing

we wanna do when working with sequel databases with larger

sets of data is make sure we, we do asynchronous operations.

So just like when we're getting data or information from the web, and

we may have some delay introduced

there, we do things asynchronously with callbacks.

Well we'll do the same thing with database operations a lot of time.

We'll execute, asynchronously and then when it's all done, we'll call

back to our code and say, okay data's ready, go on.

So, the first thing we need is to begin our transaction.

So, let's begin the transaction and then end the

transaction, and then we'll go back and add our loop.

So if we use our database object, and call, begin transaction and

then down at the end, we want to end our transaction.

Okay, so we can right our loop inside here, so

let's do a foreloop, We'll do a for each, and this

is for each I'm not going to go into too

much detail about this is the model structure for our data.

We'll call this, our, and we'll use the array

from the forecast, it's called forecast dot hourly dot data.

This corresponds, again if you want to see, if you have questions

about this parsing, certainly feel free to ask in the forum afterwards.

But since that isn't the focus here, we're just focused on the SQL but there's

one more thing we need to do here.

Because something might go wrong we wanna put a Try catch black here.

We wanna make sure we, end our transaction no matter what.

So instead of just, in case anything was, was, any exceptions were

thrown, we want to make sure we have a finally block that guarantees

that the transaction will be closed off because again, we don't wanna leave

the database in that in between

state where things didn't complete as anticipated.

And then, so this is that transaction itself, and then we wanna

trigger that commit that I mentioned and we do that with M database.set

transaction is successful, so our set transaction is successful, so notice we're

doing this here at the end, so after the entire four loop executes.

We come down here and set the transaction

as successful, and then we close that transaction.

So let's this should now insert, oh, but we haven't updated the temperature.

So this just gotta be to insert 75 48 times, instead we have that hour variable.

And from there we can get the specific temperature, which is a double value.

And that double decimal value corresponds to the real value in the database.

So if we run this, we should be able to see

in the app we'll execute the Insert.

Okay.

Oh and I forgot to add some code that

re-enables these buttons when we come in and there

is the data in the database, there was a,

they had a check for an existing data method.

But that way these would be enabled because

we do have data in the database now.

And it was just a simple select, count, star from

the database to make sure there were rows in there.

Okay, so I can retrigger it with the insert.

And if we go back to our command line, let's take a look at this.

Unfortunately, I can't just type the up arrow like in a console.

So, I have to retype this sequel.

But, if we select star, there we go, 48 rows.

well, 50 is because we had existing rows in there already.

Okay.

So there's a, different types of insert, we inserted

one row, and then we did a bulk insert.

So, this is fine, and we can view things in the terminal

but it'd be nice to view that data in our app, itself.

So let's see how to select that data from the database, and display it in the app.

So, to do that, we have a separate

activity that's the select button on the main activity.

And if we go up here and look at the on click

we see that we're just starting a new activity for view forecast activity.

So view forecast activity is a simplisitic activity,

and what it does is it's going to

execute a query, select all the data from

the database and then display in a simple list.

There are different ways to do this, this is a

very basic way again if you're using a content provider

you can use a thing called a cursor adapter to

adapt things but it's, again we're just introducing the basics here.

This is just to show you how to get data in and get data out.

So, much like the main activity, let's do a few things about getting a data source.

So, we have a protected, forecast data source, name

and data source and we're going to instantiate it here

and create data source equals a new forecast data

source with the context of view, forecast activity dot this.

Okay, now we want to, open and close in unpause and unresume.

So end DataSource dot open and end data source,

dot close.

Sure enough, and here, we don't have any button or anything to trigger the action.

We're just going to query all the data and bring it back

to the display every time the activity is, called with on resume.

So let's, add the code to select everything from the

database and then once we have that we'll update the list.

So let's go back.

Again, you'll be familiar with this patter now.

We're going to go back to our data source

object and that's where we'll write our select method.

So, the first one, this is where I was talking about the cursor.

So, we're going to return a cursor to the

results and let's call this Select all temperatures [SOUND]

and we don't need to pass anything into

this one, but we wanna create a cursor variable.

Call it Cursor, and we're going to execute a query method from our database object.

So this is again instead of writing the

raw sequel we're going to use the query method.

We'll return the cursor when we're done, and, I'm going

to break this, the parameters for this method on multiple lines.

So we can see exactly what we're doing, so the first thing we need is the data table

we're querying because we're just working with one data

table, but we can create multiple tables as well.

So we could have temperature, we could have a separate

table for wind conditions, or whatever we want, or locations maybe.

But this is the table name which will

go through our helper and we'll use table temperatures.

Okay.

And then the second thing is the columns that we wanna select.

So the format here, instead of just a single column, we need to list these as an

array of strings, so here, like, if we put forecast helper dot column temperature.

We would get an error which well, I'll just go ahead and fix

it now, what we do is just you just pass in a new

string away, and if we put curly braces around the string, that way

we get the columns names that we want, and this is just one column.

But we could separate multiple columns as individual items in

this array, which themselves would be separated by columns in here.

Okay.

So now there are a couple more parameters.But if we just

are selecting everything from a table, then this is good enough.

We just need to pass in null values for the rest of the parameters.

So this first one is the where clause.

Whoops.

I forgot a comment.

And what we'll do is we'll come back with a

separate method and look at some of these in more detail.

These are the where parameters, and then we

can do more advanced Sequel operations like group by.

And

again, we're just passing in null because we're not worried

about any of these having and finally, the order by clause.

And if you want to learn more about any

of these SQL constructs, again there's the database basics course.

Ok so this should give us a cursor, and if we go

back to the view forecast activity we can now create a cursor.

Cursor, cursor, equals and then from our data source.

Recall select all temperatures.

Now, what we're gonna do is just pass this cursor into a method called update list.

And we have it as a separate method because

we're gonna update the list in two different places.

So, if we go down to updateList, you'll be familiar with the part down here.

All it is, is a simple array adapter of decimals.

And it's gonna use a simple list item.

It's just gonna show a text view with the array with the values.

And we're using a variable called mTemperatures

which is an array of big decimals.

Now we're using big decimal here because double is not allowed, primitive types

are not allowed for inside generics like this we need to use a

wrapper class which is why we have big decimal it's just a conversion

back and forth, but it gets us the same data that we want.

So, what we need to do here is

loop through the cursor to populate our temperatures.

So, we start, the cursor is tied to our results set but we need, the

first thing we need to do is move it to the beginning of the result set.

So we say, cursor.moveToFirst, then we're gonna loop

through the entire result set using a wow loop.

And here we're gonna use a method well, cursor is after last.

So

let's, let's see, we'll do stuff in here

and then we're gonna move the cursor with cursor.moveNext.

Kay, so we, we move into the first one.

We come, we enter the while loop, it's not

after the last, because we're still at the first one.

We're gonna get the values we need, and then we'll move

the cursor, and then redo the loop until we're all done.

Okay, so what do we wanna do in here, where we're doing stuff?

Let's start with an index, so the way the cursor accesses.

Well, let's, let's do this, the cursor we, we get

things based off the index, the indices of the columns.

So we're after a double value and it's the temperature.

And

if we use the cursor we can get all different datatypes.

So these correspond to the different datatypes,

we can use in our SQLite database.

We're getting the double, double based on the column index.

So to get the column index, now we know that our

column index is zero because it's the only column in the database.

well, we have the ID column but that one is an

index so it starts at zero for the columns that we define.

So we could just pass on zero here but instead, let's get it this

is a little bit cleaner because we may change the order of our column.

So we wanna avoid hard coding the numbers if possible.

So instead we have an index, and i equals cursor, and low and

behold, there is a getColumnIndex based on a name, and the name is forecast helper.,

whoops, helper.COLUMN_TEMPERATURE.

That gets us the appropriate temperature and now we

can just add this temperature to our array of mTemperatures.

So we're adding a bigdecimal.

We need a new big decimal from the double value temperature OK

and that gets us what we need However, when you're converting a double

to a big decimal, if you do it just like this with

a simple constructor you end up with a whole bunch of decimal places.

So it's gonna fill the entire listview and it looks silly, we don't

care about that precision cuz we're only getting data back in two decimal places.

So a simple fix is to add a second parameter, different constructor And

from a, a class called Math Context, we can use a decimal format.

And this will just format our data in a little more of a presentable manner.

And that should, update our list.

That's all we need here, we're looping through the cursor, setting the adapter.

So lets run it and verify.

Okay we'll wait for jenny motion.

Just a moment.

[BLANK_AUDIO]

Okay.

And, the, there's data already in the system so we should be able to click

on select but again I, I forgot to add that method to this prototype code.

So, we have to reinsert new data and it's just gonna keep a pending data into

our database which is fine because once we get to the end we're gonna delete it all.

So now that we have the button enabled, if we click on select then there is all the

data that corresponds to what we saw in the SQL Light tool in the command line.

Okay.

So that was getting everything.

But also, very common, we are going to select specific data.

You know, whether it's temperature for a specific

hour or again, like, information about a specific user.

So we want to use a where clause to limit the data we get back.

So what I've done is, I've added a field,

an edit text back here up in the action bar.

And when enter a temperature, it won't doing anything right now.

But when we enter a temperature, it's

going to filter out anything below that temperature.

So let's go back to our data source and add the method.

And this going to be, it's going to return a

cursor because we're still going to have a results set.

And let's call this,

select all temps, SelectTempsGreaterThan and then we'll pass

in a, we're gonna use string value let me just change that to make things easier.

I'm not worried about type checking here although in a production app we wanna

make sure that we're actually getting the

number, this is just just for speed's sake.

Okay, so let's start with a cursor.

Actually, what we're gonna do is copy and paste because all we're gonna, we're gonna

use the same format here, but we're going

to add a where clause and where parameters.

So let's copy everything here and paste it below.

And the where clause is going to be a string.

Let's call it whereClause, and we'll define it up here.

We could define it here in the method, but it just gets, it starts to

get a little too hard to understand so let's add it up here, String whereClause.

And let's write the where clause we

want and then were gonna parameterize it appropriately.

So here were going to select all temperatures

where temperature is greater then the minimum temp.

So where TEMPERATURE is greater then minTemp, easy enough.

So let's swap out TEMPERATURE

with from our ForecastHelper, our COLUMN_TEMPERATURE.

And let's swap out minTemp with a question mark.

That is the placeholder value where we can add our parameters afterwards.

So, here, we see the where parameters, we can put minTemp here.

Sure, we could append it in a string this is just showing different ways to do it.

But Just like we had to use a string array for column names.

We have to use a string array for the where parameters.

So, let's type, new String, and we'll put minTemp as the only item.

Inside and we won't do anything else with group

I having order by, but now we can use this

method back in our viewForecastActivity and that code for the action bar

is down here in a method called configureActionBar.

And I'm not gonna go in too much detail about this

if you have any questions, ask me in the forum afterwards.

But you can see we have an edit text, and from that

edit text we're gonna use it

as the parameter for method called filterTemperatures.

And that's where we will do our magic work.

We will

call

mDataSource.selectTemperaturesGreaterThan and

we'll just pass in that minTemp.

And when we're done we're gonna update the list with that cursor, oh, I forgot to

declare a cursor variable cursor equals the result from that.

So this filterTemperatures will be called when

we hit Enter set out of, setOnEditorActionListener.

When I hit Enter will use the value that was entered in that edit text.

So let's run it.

So take a look.

Okay.

We'll give Genymotion a moment to reload.

[SOUND].

Okay.

And again, we're gonna have to insert more data.

So we're just tripling or quadrupling our data now.

Now, if hit on SELECT, we get the list view of everything.

And now, let's see.

We've got temperatures in the 70s.

Let's see if there's anything greater than 75 degrees.

There's a few temperatures you see it was filtered if we

put a high temperature of a 100 there shouldn't be anything, right?

And if we do a low temperature freezing 32 then

everything is the same because there is nothing lower then 32.

Okay, so that shows how we got two examples here of

a select all and then a select with a where clause.

So the rest are that's really you know this

is the meat of using the SQL database here.

The update and delete are, should be trivial based on these examples, but

let's walk through them and make sure that we understand everything that's going on.

For the update, we have an update button, let's see what do we have it doing.

It's just an onClick and we're gonna call it update method.

Let's just update everything to in the interest of time,

we'll update all values to 100, it'll be a heatwave.

So, back to our ForecastDataSource, we're gonna do our update method.

Now, the update returns the number of rows that have been updated.

So instead of a cursor, we're just gonna return an int.

And, let's call this updateTemperature, and we'll have a double for the newTemp.

Okay so because if you go back up to the

insertion, we used our content values to insert the due data.

Well we're gonna do the same thing for updating.

So let's declare ContentValues, call it values equals new ContentValues.

Okay, and let's put in the new value.

So this is gonna be once again for ForecastHelper.COLUMN_TEMPERATURE,

and the value is gonna be that newtemp that we're passing in.

Oops, I forgot to add the method called put.

Okay putting those new values in.

And then from the database, we're gonna call

the update method, and this is similar to

similar to the other methods we're using, but like

I mentioned, it returns [COUGH] an integer of rows updated.

So let's set that equal there.

We'll return, rowsUpdated.

And then we'll fill in the blanks for this update method.

So as usual, you can guess the first thing we need is the table name.

And then the second is the values that we're

going to update, and we'll pass in the content values.

And the third, we're gonna pass in null, it's a where clause.

And finally it's if we had an where clause,

we would have parameters and passing null there as well.

Okay, I think that's every thing we need, so let's go back to main activity.

To the OnClickListener for the update button.

And all I'll do is call mDataSource.updateTemperatures

and we're gonna update everything to 100.

What we'll do is run the update and then click on

the select button to make sure that all the temperatures have changed.

[BLANK_AUDIO]

Okay, so here comes our app, we'll reinsert, now

we'll UPDATE, okay, no visual indicator here, but if we

go to SELECT, everything is 100, so everything was

updated to 200 because, we didn't have any filter in.

So if we wanted to filter, if we only wanted to

update specific rows, we could use that ID column that we added.

We'd have to get it first, let's just get the appropriate

ID, then we can update the value for just that ID.

And we won't walk though an example,

because it should be pretty straight forward.

So you have an example now of how to add a where clause.

You do the same thing down here in the in the update statement.

Okay, so let's take a look at deleting one more method, public void deleteAll.

We're just gonna clear everything from the database.

And, it's a pretty simple method from the database object,

we just call delete and let's add our table,

ForecastHelper.Table_TEMPERATURES

and we could have a where call, where clause we'll just pass in null.

And go to parameters.

I forgot this comment here.

I'm just leaving these notes for reference for later on.

[SOUND].

Okay, and then we just need to call this from

the onClickListener for the delete button, so let's go back here.

Scroll up just a little bit and we'll replace

this final to do, woops, clicking on the wrong spot.

Android Studio condenses things just to make, make it

easier to look at a glance what's going on.

You just click on the gray part to, to show it like this.

So from the data source, let's call delete all.

And run it to verify.

And once the emulator reloads, unfortunately, DELETE is grayed

out, so we'll reinsert data, and now delete it.

And now if we click on SELECT, we can

view it, and sure enough, there's nothing in there.

We try and filter, there's nothing to show.

Okay.

So that's a simple overview of the basic

operations you're going to do on a database.

That covers a lot of scenarios you're gonna come across, your, and that's why

it's, it's an acronym that is common

across programming languages, create, retrieve, update, delete.

One last thing I do wanna cover, though, before

we move on, because it's important, is upgrading the database.

So I'm just gonna do another example of how to, how to do that.

And then show a few tools, and then we'll be all set.

So for the upgrade, we wanna go back to

that ForecastHelper class that we haven't touched in a while.

And we have this empty onUpgrade method down here that we haven't used.

What happens is, let me switch to Chrome for a

moment because I've got a article that I'll link to

No I don't, okay.

Well I'll link to it another time in the notes.

But the, when your app calls that onCreate it checks

that version number that we're setting here on line 17.

And as long as that version number is the same, then

it's going to reuse the same database over and over again.

But, as soon as that version number changes,

then it will call the onUpgrade version instead.

And that's how you can trigger an update with a new version of your app.

So if we're releasing version 2.0 in our app, we have whole new

database schema that we want, then

we're gonna increment this database version number.

And when the user installs the update, and the first

time they run it, it's going to upgrade the database.

And it's up to us to make sure all

their existing data migrates over, if that's what we want.

So, that's what we're gonna do here.

We're gonna migrate existing data.

And we're gonna do it with an ALTER statement.

So let's add a new private static final String, DB_ALTER.

And like before, let's write it in plain SQL and then we'll add the parameters.

So we have an ALTER table.

[BLANK_AUDIO]

And join a blank on the alter syntax.

Okay, that's right.

ALTER TABLE with the table name TEMPERATURES.

And then we're going to add a column with a

new name TIME and it's gonna be of type INTEGER.

The reason we're using integer for time is because if we look at the data returned by

the API it's returned in a Unix time

format, which is second since certain time in 1970.

So this is a, there are utilities converts this to

human readable time, but we'll start on the back end

as an integer and that's what, that's how SQLite because

it's a light version that's how it stores time data.

Okay, so let's just plug in the appropriate values

here, so instead of TEMPERATURES let's add the TABLE_TEMPERATURES variable.

Instead of the COLUMN TIME let's add a new variable.

We call it COLUMN_TIME and just like we have

COLUMN_TEMPERATURE, let's copy and paste and make that time.

[BLANK_AUDIO]

Okay.

And to execute on ALTER statement, we use the same exact method

db.execSQL and instead of DB_CREATE we have DB_ALTER.

Now before we just run this though what would happen if a

new user installed version 2.0 of our app who didn't have version 1.0.

If we don't make sure to also upgrade that DB_CREATE statement then they're gonna run

the old version and it's not gonna work

with our, the new code that we're providing.

So we wanna make sure we update, if we're ever providing new upgrade

statement, we wanna make sure that we're providing a new create statement as well.

So let's change the create statement, we're just going to append here, after

COLUMN TEMPERATURE, add a comma, space plus, and then we have COLUMN_TIME.

Plus and it's a type INTEGER, and now our

Create Table matches the upgrade statement from the alter statement.

Okay, so that's all we need to do.

well, no it's not because this is the important part that I, I kept stressing.

We need to we must increment to trigger, an upgrade.

So instead of version one, we are now dealing with version two.

So

now let's try it and see.

And we're gonna go back in to the command line in a moment, and just verify the

data structure, data structure change, we're not going

to write any additional code, to look at it.

[BLANK_AUDIO]

Here in the code should, well it should

have been executed right away in the onCreate method.

So if we go to the terminal and we can do schema for temperatures.

And sure enough, you see that the schema changed.

And now in addition to the temperature itself,

we have the time in the integer format.

But if we select star from temperatures

you see that this blank values.

Because we didn't actually insert the time, we haven't changed our code.

But you can see that the the column ID has been incrementing.

We still have the temperatures, and now we just have a

blank value, and we didn't have these vertical pipes to separate before.

It's just a visual cue, to let us know there's an additional column there.

[BLANK_AUDIO]

Okay, so that's all I wanted to cover in the app itself.

So again, it's an overview of the basic C.R.U.D operations,

an upgrade, how to create and set up your database.

But, as you do more and more with SQL

databases, you may start to get involved with using the

content providers that we talked about, or you my wanna

use some additional tools to make your development life easier.

So, you may be familiar in

other environments with abstracting that data away.

We got that data source object, but we can take it even further by

using model objects that have additional database

behavior that we can access from our activities.

So I, I'm gonna give a quick run down of

a few things, a few tips for SQL development for Android.

The first one here is a really useful way to run some commands

in the ADB shell that makes all SQL statements logged to log head.

So this is great for debugging instead of having to log your own statements.

If you turn this on than every single SQL

statement from the device will be logged in log head

and you can take a look and make sure

that the SQL you're expecting is coming through the device.

Next tool here I really like, it's an open source library called android_dbinspector.

And the way it works is, this version is just for Android Studio so

to get it to work with Eclipse would take a little bit of work.

But it's a library project that you set in your existing project.

And then you change your manifest, you add a debug manifest.

It's a little bit convoluted if you want to try it out and

you have any trouble, certainly stop by in the forum and let us know.

But once you get it up and running, when you run your app from Android Studio, it

will load up this DbInspector app on the side with a, a separate launcher.

And you can view the data for your app and you see

in the screenshots here, there's the

database file, it's got it's individual tables.

If you click into tables, you can see

the structure and you can also see the contents.

So it's a nice way to look on the app at the data behind the scenes.

And then lastly I have two different versions of

how do you wanna call these?

Abstraction models for taking your data into plain Java

objects that you can then reuse in your app.

And that just makes your code easier to understand and maintain.

If you're working with a forecast object instead

of working, writing your SQL and working with

it directly, then your, your code in your

activities and elsewhere is a lot easier to understand.

The first one here is called greenDAO.

DAO stands for data access object.

And this is our wrapper around the SQLite database you can see here they

have some claims about being increased performance

small memory consumption and easy to use APIs.

I have read, I haven't used this for myself, but I have read

reviews and performance tests that say this is a good library to try out.

And the last one was just brought to

my attention yesterday, I was talking with one of

our mobile developers, Joe Steel, and he mentioned cupboard,

another library that does some of the same things.

It extracts out that database layer and it uses plain old

Java objects to, and, and here, the example, I think illustrates.

So, in your activity, you'd have this, this code

with the cupboard library with the database that you provide.

You put a book object, so it puts that book into the database.

And makes it a little bit cleaner to insert or extract data from the database.

So, that just about does it.

Thanks a lot for sticking through to the end here.

I'm gonna check and see if there's any questions at the moment.

And again if not, if we don't get to them.

Then we will catch up on the forum.

Okay, so, Jonathan asks is there a reason why I

decided to use big decimal versus the auto-boxed double class.

no.

No I could have just used that.

I feel like I may have had a reason, before, but no, yeah, so, yeah.

What that's referring to is where we're taking the double

values, the primitive values and is adapting them for the display.

We could have just used the double with a capital D as the, the more complete

object datatype that, that's usable within the array adapters, and within the list.

Okay.

Looks like there may not be any other questions at the moment.

Okay, great.

Again, if you have any questions follow up in the forum

or find me on Twitter @benjakuben, and thanks so much for watching.

See you later.