Alright, so that's the database file created. But at that moment
there's nothing in it.
So we need to execute some SQL statements to create a table and put some data in it.
So let's go ahead and add the code for that.
So we're going to do var sql
equals, and then double quotes CREATE TABLE space contacts. Then parentheses, or left parentheses, underscore
id space INTEGER space PRIMARY KEY, PRIMARY space KEY, space NOT space
NULL comma name space TEXT comma phone space INTEGER comma email space TEXT. Then right parentheses and close the
double quote
to close the string off. Now what we can do is do a database.execSQL, then in parentheses
pass sql being the string.
Then we want execute. And then what we'll do is, on the next line, we'll do sql equals, in double quotes,
INSERT space INTO space contacts parentheses name comma space phone comma space email right parentheses
space. Then values, then left parentheses single quote, and I'll put tim in single quote comma and I'll put 6456789
comma. Then single quotes tim@email.com and a single quote there, then a right parentheses,
then a double quote.
Now I'm going to do the same thing there, database.execSQL and pass sql as the argument again.
So the method we call to execute SQL statements in our database, as you can see, is the execSQL
method, and all we're doing there is passing in a string containing the SQL statement we want executed.
So we start off by creating a table called contacts. Now that contains three columns; name, phone and email.
And we're also including an id column there.
And we've discussed the id column before, and we'll see how Android uses it
when we come to populate a list view from our data.
So next we inserted some data into the table.
We are going to insert some data in the table by using the sql insert statement on line 23.
We've used that in previous videos.
Now by the way, Kotlin strings are enclosed in speech marks or double quotes, so therefore it makes sense to use
single quotes inside our SQL insert statements. And I've done that as you can see on line 23.
Now we could use double quotes or speech marks inside the string,
but then we'd have to escape them with a backslash
so that Kotlin knows we're putting a speech mark or double inside the string, rather than terminating the
string at that point. Now it can get confusing having all those backslash characters
though. So I think it makes a lot more sense to use single quotes for the values inside a SQL string. Alright, so
at this point that's our first insert statement, and it should create a new row in the contacts table with
those values.
And these are the same SQL commands that we've used in previous videos when we entered SQL into
the sqlite3 command line interface.
So here we're literally just passing them as arguments to the execSQL method.
Now the execSQL method isn't really intended to be used for data manipulation statements.
It works and we will verify that shortly, but
let's see what the documentation has to say about it.
So I'm going to come over here and click on the execSQL,
and I'm going to bring up the, open the documentation.
And you can see here it "Executes a single SQL statement that is NOT a SELECT or any other
SQL statement that returns data".
So in other words, if you try to execute a select statement you'll get an error.
Now the documentation, if you scroll down a little bit further,
also mentions the insert and update methods and we're encouraged to use them instead of execSQL
when we want to change the data in the database.
Now one reason for using Insert and Update, is that we've got no record of the ID that was generated for
our new row.
Now that's often important.
We may want to store it in the views tag, for example, so that we can quickly retrieve the record that's
being displayed.
One example.
Now I won't change the way we're adding that record, but I will add another one, this time using the insert
method instead.
And to do that, we can come down here after the INSERT INTO contacts line, after the second call to the exec
SQL method. We can put val values equals, and we're going to call ContentValues, opening and closing parentheses
dot apply. Then left
curly brace and right, which you can say has been added automatically. Then on the next line I'm going
to put put parentheses,
and it's going to be name in double quotes comma and Fred in double quotes. Next line we're going to do a put parentheses
again,
phone in double quotes, and then this time we're going to put 12345 without double quotes. And
on the third line, put parentheses email in double quotes comma and in double quotes fred@nurk dot
com double quote and a right parentheses.
Then we've got the closing right
curly brace there as you can see. Then that after that what we're going to do is put val generatedId is
equal to database.insert parentheses contacts in double quotes comma and values. And I missed
one argument there because if we have a look at the insert again, we'll just go back here and have a look. It
needs to have the null column hack as well, as well as values. So what I'll do is I'll come back there and add null
as the second argument there, and that should fix that up.
Alright.
So we get, you can obviously see now that the insert method is taking three arguments; the name of the table
to insert into - contacts in this case.
We've also got this null column hack, then also and then the values that we're actually adding, or
wanting added to the table, or to that particular table.
Now I'm going to talk about the null column hack argument
a bit later.
For now though, as you can see, I'm just using or passing
null for that.
Now the values are provided in something called a content values object.
And really all that is is just a wrapper class around a hash map which lets it store a set of key value
pairs. And you can see obviously, we're assigning the key value pairs on line 29 through 31. We've basically got one
for each field in that table, each column in that table.
Basically the keys are the column names in the table which you've probably figured out. And we're then passing the
table, table name and our values to that insert method.
Now I've also assigned the value return by insert on line 34, to a variable called generatedId, so that we
can log it and check that it's what we expected it to be.
Now you may not have come across apply before - I'm talking about the code up here
on line 28.
It's a Kotlin extension function that takes a function block, and executes it with an object it's called
on as its receiver.
So effectively that means that the function block runs just as if it was a function of the class, the
ContentValues class,
in this case. So apply returns the object it was called on which is extremely useful, because here we're assigning
that to the values variable, which means that the values get the new content values object.
Now the equivalent code we could have done, I'm just going to paste this
in just to give you an alternative, paste that in there.
I'll just fix up this formatting.
We could have done it that way as you can see. That's a similar way of doing it but I like the way that
apply returns the object that it was called on. I think it makes for better looking code.
But the example that I pasted in here would be how you would write it in, in Java or non-idiomatic Kotlin.
It's usual,
it's more usual to use apply when doing things like this though.
Alright, so the code now that we've written it should create our database and a contacts table, and insert
some data into it. Before we run this and check it though,
let's add some logging.
We're going to start with the usual TAG constant.
Let's go up and do that. We'll do a private constant val tag equals, in double quotes MainActivity,
and obviously that should've been above the class. So let me put that in the right place.
OK, there's our const written, and let's add a bit of logging just to make sure it's working.
So I'm going to come down here, and just before the exec I'm going to put Log.d parentheses TAG
comma, then in double quotes we're going to put onCreate colon sql is $sql.
I'm going to take a copy of that line.
So that's for our creation table, and down here for the SQL for the insert,
I'm going to do the same thing before the exec, the exec is called, onCreate sql is sql again. That's
our second line.
And for the third one after the val generatedId line, let's come down to the end of that. I'm going to
do some logging there and we're going to change that a little bit.
We're going to put record added with id $generatedId. At this point
now we should be able to run the app and see the entries in the logcat. So let's actually run this now.
So what I'm going to do is select an emulator that I've defined
that's not a Google Play emulator.
And the reason is that we're going to be using adb to connect to the device and view the database
later. So I've got one that I've already created here called Nexus 5X API 26 ROOT ACCESS.
And that tells me straight away that it's not a Google Play emulator, and that's because a Google Play emulator
doesn't give you the ability to get root access.
So I'm going to select that one, click on
OK, and we'll just give that a moment to start.
I can make that a bit bigger but we really want to see the logs.
So let's have a look in our log.
And we've got a lot of stuff here, so what we can do just to make it a bit easier to see what we've done
here,
we can remove some unnecessary noise from the filter by filtering on MainActivity.
So let's do that; forward slash mainactivity, and you can see doing that we can see really much more clearly
what's actually happened here. Without the slash, by the way, the forward slash, you may still get some
uninteresting log entries from the Android system. That's expected though if we have a look at the log entries.
The new ID for that second row is number 2 which we would have expected.
Alright so that's how to execute sequel statements from our Kotlin code.
We use the the execSQL method of the sqlite database class to execute SQL statements.
We've also got insert, update and delete methods we can use as well.
And these are more appropriate when inserting or updating data in the database.
Now by the way, the execSQL will work for insert, update and delete statements, but you'll
see later why it can be useful to get that ID that was generated for a count of the number of rows affected,
in the case of the update and delete methods. Alright, so let's finish the video here.
The next step is to write some code to retrieve the data and we'll work on that in the next video.