In the previous video we got our code writing data to the database.
Next we're going to look at how to get the data back out again. To do that,
we're going to use a cursor. In database terms a cursor allows us to access individual records in the
database.
We can move backwards and forwards in the cursor, and it takes care of retrieving the rows for us. Once
we position the cursor at a particular row,
we can then use it to read the individual columns or fields of data.
Now you'll often use a cursor
even if you only expect to get a single row back from a query. You don't have to worry about how many
rows will be retrieved. They're all accessed via the database cursor. Your database query could return
thousands of rows, and that could be a problem because smart phones have limited amounts of memory. All
that data possibly wouldn't fit in the memory we've got available,
but a cursor takes care of this and points to just a single row at a time.
So we can access and manipulate the data without having to load it all into memory,
and this avoids the performance issues of having to retrieve thousands of records into memory. A cursor
enables us to manipulate the data, but only retrieves it one record at a time,
as you'll see when we start writing our code.
So Android comes with a cursor class built in so we can use that.
So let's give that a go now.
What I'm going to do is come down here, after our
last statement we added which was the val generatedId for the database insert. And I'm going to type val
query is equal to database.rawQuery, and in parentheses and double quotes,
SELECT * FROM contacts.
Then the second argument, we're going to pass null to it. The rawQuery method returns a cursor. The first parameter's
the SQL query that we want to use to retrieve the data, and I've set the second argument to null.
Now if you want to know what the second argument is let's have a look at the documentation,
and you can see there that second argument is the selectionArgs.
This provides values that we can include in a WHERE clause to our SQL query, or into our SQL query, and we'll
have a look at that a little bit later.
For now I've just set that to null. And by the way, with these windows,
these documentation windows, they often contain more text than will fit in them, and they always
seem to start off the same size when you first open one, regardless of their contents.
So make sure you either resize them or scroll down, so that you get to see all the information.
In this case the information didn't require me to scroll.
And by the way, we can also see that if we have a look at the return information for rawQuery,
we can see that it's a cursor object positioned before the first record.
It tells us here that it's positioned before the first entry.
Another thing to note there is the SQL string must not be semi-colon terminated, as you would
normally do when you're creating SQL source code or
what you've done earlier in this section of the course, when using a sqlite3 command
line, program.
Alright, so that's rawQuery. There's also a query method as well, or several query methods to be precise, and they
allow you to specify ORDER BY clauses, as well as more filtering and grouping.
And it's definitely worth checking those methods out when you come to write your own apps.
We're going to be using something similar later, and you'll understand what the various arguments are
once we've done that. But I'm using rawQuery here just to keep things simple.
Now at the moment actually, if I hover over that, you notice that we've got this warning from Android Studio that this
cursor should be freed up after use with close, hash close.
Now if you're familiar with Java you'd probably use a try with resources to achieve that.
More importantly if you're converting Java code that uses try with resources, you may struggle to do
that in Kotlin.
And that's because Kotlin doesn't use the same syntax as Java, and there isn't actually a try with resources
syntax as such in Kotlin.
Instead Kotlin provides the use function to achieve the same thing.
So let's just type a bit of code and then we'll check out the documentation.
So it's query.use, and
notice that it's adding a code block after that, and
we'll have a look at the documentation for use. Basically, it executes the given block function on this resource
and then closes it down correctly.
So in this case it'll close the cursor, whether or not an exception is thrown.
So you can use the use function with any closeable object, in the same way that Java allows try with resources
to be used with any closeable object.
Alright, so back in our code now.
I'm going to put all the code to deal with our cursor inside that block.
Now inside the code block that we've added here, we refer to the object that was called on it as it, and you can see
that IntelliJ helpfully tells us that as well -
it:Cursor.
It's adding that for us as a reminder of what you need to do there.
So to write the code we'll do something like:
if, this is in the code block, parentheses it.moveToFirst.
So again, the reason that we're using it and we're not using query here, is that we're referring
to the object that it was called on
as it. And that's why we've got it.moveToFirst here rather than query -
dot moveToFirst.
Query is a cursor, and we're telling it to move to the first record.
Now it's actually quite possible that our sequel query didn't return any records. In which case, the moveTo
First method would return false.
So that's why we're checking that it did manage to move to the first record before we do any more processing.
And just to confirm that if you want to check that.
Have a look at the documentation for moveToFirst, and you can see
it tells us there specifically that this method will return false if the cursor is empty.
So OK, so if it returns true then the cursor's positioned at the first record and we can start reading the
data.
So let's put some code in that code block,
knowing that at this point we guaranteed to have at least one record.
So I'll put a comment here that we're going to "Cycle through all the records"
with parentheses query, and by the way we aren't yet cycling through all records. I'm about to do that a little
bit later, but for now though we're going to query a single record using with query. Then within the with query cold block we'll put
val id equals getLong parentheses zero, val name equals getString parentheses 1, val phone equals get
Int 2, and val email
equals getString 3. So again, we're not cycling through all the records yet.
We're going to do that in a minute,
hence the comment.
For now though we're just reading the values for the first record.
Now note that we're using the get methods which need the index of a column and a table, and obviously it's
get
and then the type of data.
So we've got a long here, we've got a
string here, we've got an int here,
etc.
So these, all of these functions need an index of the column and the table. So remember that computers
index the first item as index 0,
so that's why we're reading the first column into ID, the second into name, the third into phone and
the last into email. And the ID is a long value where its name and email are strings, and because
we've stored the phone number as an integer,
we're using getInt rather than getString but it works the same.
In reality we really shouldn't have used an integer for the phone number, but I just wanted to show you
how to retrieve different types of data.
And that's also what I meant in an earlier video when I warned about SQLite columns not really
having a data type.
There's nothing to prevent us from storing a string in the phone field, but that would break out Kotlin
code when it tried to read an int.
Alright.
So for our simple example, we're going to display the data in the logcat.
So what I'm trying to do,
is below the lines that retrieve the data, I'm going to type val
result equals, then double quotes,
ID colon $id Name equals $name phone equals $phone and email equals $email.
Then I'm going to type Log.d parentheses
TAG comma onCreate colon reading data $result, and you can see that IntelliJ is helpfully
telling us that this is all attached to the With that we've actually set up for this query and the appropriate
code block. Alright so finally, the other thing we want to do here now is we want to close the database. So database.close, so calling
the close method. Closing the database is a good idea, but you'd normally do that when the app is closed,
rather than here.
That way the database is available while the app's running and you don't have to keep opening it.
And we could also have wrapped all this code up in a database dot use block, just like we did for the cursor code.
I didn't do that here because, as I've said, it's more usual to keep the database open until the app finishes.
In fact if you wanted to use this basic approach in a real app, you'd probably create a separate class
to handle all the database accesses.
We're not going to do that.
We're going to be using a content provider instead.
At the moment though, we're just seeing how to run SQL statements from our Kotlin code.
Alright so at this point in time, we're now ready to run this simple app. We're going to run it now.
OK, I'm going to select the same one that I ran it on last, click OK. I'll just fast forward this until
it gets to the stage of running.
Alright so you can see we've got an error immediately. So when that happens, open
up the logcat tab and see what we can see there, and we might have to remove the filter there so
that we can say all the information. Also I've noticed that sometimes Android Studio continues to show
unrelated log entries, even with Show only selected application over here to the right selected.
If that happens just select your application again, in the second drop-down from the left,
so this one here.
In my case I don't need to do that. The reason I'm not seeing all the output here is because we've got /mainactivity
still showing there. Again, I could select that again,
and notice by doing that now it actually clears the log of all that unrelated information.
And we're back to looking and seeing why our application crashed.
Now one technique for reading these tech traces is to read the first bit that describes the error, then ignore
the rest of the block - all the lines starting with at,
in other words. Now down down up to here and have a look at this first line: table contacts already exists,
when compiling, and it gives us the SQL that was executing.
So that's pretty clear as to what the problem was.
And again just to be clear though,
you can see down here that it was caused by sqlite exceptions. So that told us fairly quickly anyway, even if we
didn't already know, that it was complaining about the CREATE TABLE statement
and you can see that to the right of the screen there now.
So basically we knew that there was a sqlite exception of the code,
so the problem was probably relating to our database code. We probably guessed that anyway because we haven't
added any other code, but it is always useful to check these logs just to make sure that's the case.
So basically the sqlite
exception is complaining about the CREATE TABLE statement.
And if we skip to the next block, eventually we see some code here that we can click. Anything in blue
is the code that we've been working on, so we can click on that to navigate to the line of code that
is actually causing the problem. And you can see that that's the line that caused the execSQLs method, which
means the problem's going to be in the SQL statement a couple of lines further up -
line 24 in fact.
Basically the table contacts already exists and that's why we're getting that crash.
Now fortunately while testing, there's an easy way to fix that. We can just come up here and change the
SQL code slightly
and add the words IF NOT EXISTS after the CREATE TABLE statement. So CREATE TABLE IF NOT EXISTS.
contacts. So in other words, the table won't be attempted to be recreated
if it's already found.
So I'm just going to click on and run,
close logcat for now. And we'll just go back and check our app. This time our app has worked.
We haven't got a crash this time so that's a good sign.
And we will go back to our logcat again, and we can just type /main, and we can quickly see that we've
got some data showing on the screen there now. So it's doing what we expect.
Well it sort of is. We get the details for the first row appearing,
but the ID for the row that's just been created is now 4 rather than 2.
And that's because our code keeps adding those two records every time we run it.
Now normally you'd be adding new rows when the user does something, such as saving a new contact. You
wouldn't normally run these Insert queries each time the app runs.
And we don't want to keep adding the same two rows,
and we can easily prevent that by dropping the table.
Let's just make a quick change to the code to do that, and we'll add it immediately after our database is being
defined on line 23.
We're going to type database.execSQL, and in parentheses I'm going to type double quotes DROP TABLE
IF EXISTS contacts. So I'm going to DROP THE TABLE every time
IF it EXISTS. Let's actually run that again,
and confirm that that works.
This time you can see we've got record added with id 2, and that confirms that the table was dropped
immediately after we got access to the database object.
Now generally I prefer to put the SQL statements into a string variable like I did for the CREATE TABLE
on line 25.
This makes it easier to log the SQL which makes debugging easier if you make a mistake.
You can just include the SQL as a string as we're doing here.
But I do recommend using a variable and logging it's value, especially while developing your app.
You can always delete the logging later once the app's working.
Also be aware that these debug logging commands are stripped out by the build system when you create
the release version of your app. Basically when you use Log.d, the logging lines are removed automatically
so they won't affect your apps performance.
Alright so there's one last change to make before we stop this video.
We can loop through all the rows in the cursor by using moveToNext instead of moveToFirst. Remember
that the cursor's positioned before the first record,
and that means that moveToNext will do the same thing as moveToFirst, the first time it's used.
So let's go ahead and make a simple change to our code to check that.
So I'm going to close down logcat first.
And the change we want to make here - at the moment we've got an if statement here on line 49. I'm going to change the
if to while. I'm creating a while loop
now instead, and I'm going to change this code here, or rather I'm going to remove some of this now, because we in fact
have added the code now to cycle through all records.
So now when we run this we should find that we've got two entries showing on the screen, so let's run
this. Stop the app and run it again, and we'll bring up logcat.
Let's have a look and see what it gives us. And obviously I've
made a mistake there so what I'm going to do is just stop that,
stop our app. And you can see what I've done there. I basically didn't change this properly. I put while it.move
ToFirst.
So it always continually cycled back to the first record, so that therefore the while statement never became
false and
we never exited the loop.
And of course I talked about it but we didn't do it.
We need to add a moveToNext,
bearing in mind that with moveToNext,
it does the same thing as moveToFirst, the first time it's used. So it'll move to the first record, then
automatically progress through to the next record and so on.
Let's run it again,
and we should find this time we'll see both records.
This time you can see now, we've got reading data ID 1 and ID 2, and clearly the values are different
there on the screen as well.
Basically the logcat's showing both records correctly and our very simple app's working fine.
So this is an incredibly basic app, but we've seen how to create a database using the SQLite
database class.
We then created a table and inserted records by executing the appropriate SQL statements.
Now I've said several times that you wouldn't write a real app this way. At the very least you'd create
a class to handle all the database access.
You'd also want to make sure that your query isn't performed on the main thread.
That's not a good idea because database operations can be slow.
Now there are ways to handle this using things like content providers and cursor loaders, and we're
going to be looking at both of those.
But the basics of executing SQL statements from Kotlin code will still be needed.
Now we know how to do that,
we're ready to look at more suitable ways of dealing with databases. In the next video though, we're going to
have a look at where our database is stored on the device, so that we can open it using the sqlite command
line, and check that it's been set up correctly. When debugging, it's very useful to be able to examine
the data that's been stored in your database.
So in the next video we'll find out how to do just that.
See you in the next video.