In the last few videos we've seen how to use the SQL language to create tables in a database, insert and
delete rows and query the data.
It's now time to use what we've learned in an Android app.
We're going to start with what is really the simplest application
you'll probably ever see for a database.
Now it's not following best practices, so don't think that this is the way that you generally access
a database in your applications.
This exercise is just to give you a simple overview of how to use what you've learned in the previous
videos, in Kotlin code, rather than from the SQLite console program.
We'll be looking at much better ways of doing this in future apps.
So view this more as an introduction to how to use SQL in your Kotlin code and not as the correct
way to work with databases. Alright,
so let's get started.
We're going to create a new project in Android Studio.
We'll call this one Sqlite Test.
I'm going to make sure the domain is set to learnprogramming.academy as it has been throughout the
course.
And make sure the checkbox is checked for include Kotlin support. Click on
Next, and make sure that API 17 is selected and we've de-selected the other options, which we have. Click on
Next.
Now for this one, we're going to be using the floating action button for this app. So we're going to select the basic
activity and click on Next.
And finally, everything on this final configure activity screen is correct and fine as it is.
So I'm going to accept those defaults and click on Finish.
Alright, so we'll just give that project a short while to load and finish building and indexing.
And then we're going to open up MainActivity and write some code in the onCreate method.
Now as I said earlier in the introduction, we're going to create what is probably the simplest application
for writing data to a database that you'll code.
It's very, very simple.
It's just a proof of concept,
before we go into more detail and do things in a more structured way. Alright so you can see that things are loading
here now. We're just about done.
What we'll do is, we'll go to the folder, give that a moment to compile and finish and re-index, or index
for the first time I should say.
It's finished now and I'm going to double-click MainActivity from our academy.learn programming package.
And we're now good to go.
Now the Android framework includes a SQLite database class called SQLite database, and we're
going to be using that to create and access our database. Now you don't need to know the full ins and outs
of how this particular class works.
Think of it like the SQL 3 command line program - the one that we used previously in this section -
to allow us to execute SQL statements. The SQLite database class works in a similar fashion,
but instead of typing sqlite prompt, we pass strings containing SQL statements, and the SQLite
database object executes them for us.
So to get this to work we need to start by creating a database object. So I'm going to go ahead and do that, above
the fab.setOnClickListener line. I'm going to type val database is equal to baseContext dot
and it's going to be openOrCreateDatabase. Now Android Studio's very helpful when you type these methods, and
you saw that as soon as I typed the opening parentheses after the openOrCreateDatabase,
it showed the arguments that we have to provide.
Now by the way, I tend to use the term method when referring to a function belonging to one of the Android
Java classes, and function when talking about Kotlin code.
Now they are the same thing and if you want to call them functions instead that's fine.
Just be aware though of the different terms when you're talking to Java programmers. OK so there's several
ways to use this method, but we'll stick with the first one for now.
And you can see it's asking for a string, an int and something called a CursorFactory.
So to find out what these arguments should be we can check the documentation.
Now if I typed in the arguments I could click anywhere on the openOrCreateDatabase method, and use
Control Q or Control J, depending whether you're on a PC or a Mac, and bring up the documentation. But at the
moment though, this doesn't work.
Android Studio is unable to work out which particular version of the openOrCreateDatabase documentation
we want to see, and that prevents it from linking to the documentation correctly.
Now of course this is a catch 22, because if we knew what the arguments were, we probably wouldn't need
to call up the documentation in the first place.
So it did used to be possible to put the cursor inside the opening and closing parentheses before typing
Control J, and again it's Control Q
on a PC, but that's no longer working.
So if you find yourself in that position when trying to get the documentation for Android Studio, just
enter any suitable values for each parameter, and once you know what they should be, you can change them. So here you can
see the top version of the method - one's a string, an int and something called a SQLitedatabase dot
CursorFactory.
So I'm just going to put a string and call it name, and I'll put the number 1 in there for the int. And I'm just going to pass
null for the time being, for the CursorFactory. So now I can go back to the method name now that we've filled that out.
Put my cursor there and I can do a Control J on a Mac, and again it's control
Q on a PC, and now the documentation is coming up for us.
So that's actually a lot more helpful.
We can see now that this method opens a SQLite database, and reading further you can see it creates
the database file if it doesn't already exist. Now when you use this way to view the documentation, Control
Q or Control J,
you'll often find there's a blue upwards pointing arrow available. And you can see there is up here as
well.
Now if it's greyed out then there's no further documentation available, but here it's in blue and you can
see that the link said that we could view the documentation, or view the external documentation. So I
can click it, and it'll open up the relevant documentation on my Mac's default browser.
And as you can see it hasn't gone straight to the method. We still need to look up the method.
So I'm just going to do a find for the method name we're looking at, and that's openOrCreateDatabase,
and I'll just look for the second one.
And the one that we want to have a look at is the overloaded one that had name, mode and Cursor
Factory.
So I'm going to click on that.
So I'm presuming that was a bug, and that eventually this'll be fixed and it'll go straight to the
method, but you can see there that I needed to add another step to get to the method that I wanted to see
via a browser.
But this part is a lot more helpful. In the old days we had to leaf through the printed manuals, but
now all the documentation's available with a couple of key strokes. And you can do this for just about
anything you want to know more about. Just click in the name of the class or object, and use Control J
if you've got a Mac or Control Q on a PC, and you can find out more information on whatever it is.
Alright so we're going to continue on in the web browser for the documentation, seeing as though we're here now. Now we're going to
use this MODE_PRIVATE,
this option here, for the default operation, but we can check what the other options are by clicking on
the links. So scrolling down this alphabetical list here,
we can see we've got MODE_ENABLE_ WRITE_AHEAD_ LOGGING.
So let's click on that and then prepare to be disappointed.
So that's not really very helpful. If you were hoping for an explanation of what WRITE AHEAD LOGGING
means,
this doesn't really explain a lot.
When that happens, just look for other links that might more provide more information.
Now there's not much point clicking on the first two links here because they're the ones going back to the
openOrCreateDatabase methods. We've just come from there
so we'll end up going round in circles.
But have a look at this third one here,
this enableWriteAheadLogging method. That's far more intuitive. You can see we've got lots of information
about that.
Now I won't read through it all. Basically
it allows concurrent access to the database.
Now in Android there's not often any need to allow your database to be accessed on multiple threads.
There's only one app in the foreground at any one point in time.
Now if you start writing things like services and your service needs to write to the database, then you'd
probably have to enableWriteAheadLogging. By the time you get to that stage you'll know that you need it.
So we're going to use mode private for the mode, and set the factory argument to null.
You can then use your own subclass of Cursor, and that arguments how to do that.
So generally though the Cursor class that Android provides is fine for most purposes.
And if we go back and have a look there,
this factory here, "optional factory class that is called to instantiate a cursor when the query is called".
As I mentioned, generally the cursor class that Android provides is fine for most purposes. Alright,
so let's go back to our code.
So the name argument will be our database file name, and the mode, we should be setting that to context dot
mode underscore private.
Now before I enter those values, you want to hover over the error that the mode is providing, the mode argument
there. The error message mentions a few other options as you can see there. We've got mode world readable and
mode world writeable.
Well they were deprecated in API 17 and we really shouldn't be using them.
Making our database world readable is a pretty bad idea from a security point of view, and world writeable's
obviously much worse.
Now they've been marked as deprecated in the documentation we just looked at, but did allow the database
to be shared with other apps.
So they're still present in the Android source code,
in other words, but could be removed in a future version.
Now there are other ways of sharing data, that we're going to be talking about
those a little bit later - things like a content provider, broadcast receiver and a service.
So let's now update this with the right settings. So we're going to go with sqlite dash test dash
1.db.
And instead of hard-coding a 1 we're going to go with MODE underscore PRIVATE,
then we're going to leave null as a third option for now.
So this first bit of code will open the database for us if it finds it. If there isn't already a database
called sqlite-test-1.db, then it will automatically create one for us. And we'll have
a look once we're ready to run the code and see the database file on the emulator.
For now though let's finish the video here,
and in the next one we'll start writing some SQL code. So we'll start creating some strings with our SQL
code, then get to the stage of executing the app to see what happens.
So I'll see you in the next video.