Alright so as I mentioned in the,
towards the end of the previous video,
we've currently got two rows and I've
got them on the screen there. They've got
a sort order of 2. So let's see how we
can use the selection parameters to
update several rows at once. So I'm going
to go back to our code. Now once again,
what I'll do is create a new function,
and I'm going to put that above our test
Update function just so that we can
actually see what we're doing, and see
some of the output as well. So we'll go
back up. So I'm going to put that just below
the onCreate method that we've got here,
and I'll call this one testUpdateTwo because
obviously we need a unique function name.
Alright so what we're going to do here - the
change we're going to make - we're going
to change the Sort Order but we're going
to change that for two entries. Actually
we're going to change the description as well.
So the entries I'm going to put in there,
firstly there's the Sort Order, so TASK
_SORT_ORDER. I'm going to set
that to 99 - that's a number so no
double quotes - and the description, we'll
also change that to Completed for both.
Alright next, outside of the apply code
block, we need to add a val selection
equals, it's going TasksContract dot
Columns.TASK_SORT_ORDER
and plus and double quotes a space
equals 2 double quotes. That's going
to be our selection criteria there, to
choose which records, or which rows rather
we want to update. I'm going to comment
out the taskUri for the id 4,
and what I'm going to do is change - and I'll
just bring this up on the screen so we
can see it a bit better - rowsAffected
should now be contentResolver dot
updates still. We're going to go back now
instead of taskUri. We're
going to call just our regular Tasks
Contract.CONTENT_
URI. Then we're going to pass, and I'll put
this on a separate line so it's easy
to read,
null and selection criteria. And actually I'll put
values on the next line as well, and we
can leave the TAG - logging tagging - as it
is there on line 65 showing how many rows
were affected. And obviously then, we need
to go back up into our onCreate method,
comment out the original testUpdate
function - I said onCreate method
but of course it's a function - and test
UpdateTwo, we want to call that. Alright so
we'll run this again, go back to our log
cat, and you can see that I've made a
critical error there, and this is
something that you will almost certainly
do in your career. And you can see what
I've done is I haven't actually set the
selection criteria, so consequently
what's happened is, all the records in
the table have been updated. You can see
Sort order 99 is set for all four, and also
the description has been set for all
four. What I should've done, if we come back
down here and have a look under test
UpdateTwo, we defined the selection to be
the SORT_ORDER, TASK_SORT_ORDER with a
value of 2, but then I didn't use it in
our contentResolver.update. So what
I should have done is actually added
that under the WHERE clause, effectively
under this first argument, selection. But
at this point in time I've now updated
the database, so what I'm going to do is
quickly pause the video. I'm going to go back
to the state that the database was in
prior to this call to the testUpdateTwo
function, and then we'll continue. But a
good lessen there, especially in
situations like this, to really check and
make sure that you put the changes in to
avoid doing just what I did there, which
was, of course, updating all records. So
I'll be back shortly and we'll continue.
Okay so I'm back now, and what I've done
is I've restored it to the state now,
where we're at just after we called the
first update, the testUpdateTwo function.
So you can see that at the moment we've
got 4 records - number of rows returned
4 - but also you can see that we've
got our fourth record set to Name equals,
Task names, Content Provider: Description:
Record content providers video. So that's
the status of the database just prior to
us calling that second testUpdateTwo.
So you can see now that I've got selection
defined for the WHERE clause for the
update - it's that argument - and then in
terms of what we're going to execute up
here in our onCreate function, it's
about to execute testUpdateTwo. So let's
give that another go now and just make
sure that it actually works, or check
that it works.
Alright so that's a lot better this
time now that I've added the selection
criteria. You can see that firstly, that
the log entries report, those two records
are updated - number of records updated is
equal to two - and we're going to also see,
if you look down here, changes now to
just the two entries down the bottom
there; Sort order 99 set for the entries,
that had prior to this, the Sort order of
2, namely Tasks ID for Android N and
also the newly created id 4, which was
our Content Provider. So in other words,
the bulk update updating more than one
entry at a time has worked. Alright so
that's how to use the selection, and
we'll go back to our function again, by
passing this selection which is
effectively the WHERE clause on what
records in this case, or what rows I
should say, to update. But what is this fourth
argument, this selectionArgs, used for?
What's that all about? Well selectionArgs
is a string array of values that are
used in the selection criteria, and they're
used to prevent SQL injection attacks.
So a SQL injection attack is where an
attacker causes specially crafted
SQL statements to be executed against
the database. Now if your code accepts
selection criteria from user input, then
the user could type in something quite
dodgy. So as for example, the code
that we've defined on line 60 there, if
we'd got the value 2 in this case, from
user input, then they could have entered
something more than just 2. They could
have entered something effectively like
equals 2 semicolon drop table tasks. Now
this won't work - we'd have to be a bit
cleverer to fool the system - but it is
possible to do. So let's actually run the
app with that code, just to see what
happens, and we should get an error
message but let's just run that anyway.
And what I should've done, I didn't quite
change that properly. It should have been
equals two, then I need to actually change
that to put a right brace, a right
parentheses. Let's just try running that
again.
This time we get an error. This is the
error that I was trying to show you, and
if we scroll over and take a look at the
error, we get a syntax error as you can
see there, trying to do it that way. But
if we go back to what it was before,
equals two, and we remove the parentheses,
the right parentheses for some
reason, that's clearly not going to work
either. You saw what happened last time -
we didn't get any output at all. So again,
depending on how you try and do this, if
you were setting what the user's actually
typed in here, you may actually get a
SQL exception pop up. The point here
I'm trying to make here, is that
Android Studio and the code that Android
is developing, is getting a lot smarter
at trying to figure out this code and
trying to see whether someone's trying
to do a SQL injection attack.
Basically, what the point here is that a
few years ago, removing the extra right
parentheses that I did there would have
been enough to have deleted the entire table
from the database, but there's now
prevention stopping that from happening
these days, which is good. Basically
database systems are getting wise to
this sort of thing. So even if you did
delete the extra right parentheses
from the string, the tables won't be
dropped and you saw when I ran this
that they actually weren't dropped.
SQLite won't execute multiple
queries in a single call to a query. But
even so, with that said, it is possible to
come up with statements that are
formatted so that something like a drop
table command will be executed. So
getting back to what we were talking about
here, the selectionArgs parameter, the
values provided in that selectionArgs
parameter are sanitized to reduce the
risk of SQL injection attacks, so it's
definitely worth using them if your
criteria uses values that a user has
typed into your app. So all we have to do to
use that parameter is to replace the
values in the selection string with a
question mark, and then put the actual
values into another array. Then we can
pass that array as the final parameter
to that function. So let's actually have
a look at how to do that, so if you're
faced with that scenario you'll know
what to do. So after selection, we're
going to delete that so it's just set back to
equal, but instead of two it's going to be
equal question mark. Then on the next line
it's going to be val
selectionArgs is equal to arrayOf
parentheses double quotes 99, rows
affected we're going to leave, and I'm
going to delete this, those two lines
there, and selectionArgs, we're now going
to pass selectionArgs. And just so we
can see that it has been an update I'm
going to change TASK_SORT_ORDER to 999.
I'm going to change the TASK_DESCRIPTION
to For deletion so we can actually see
that this has actually worked. And
obviously I'm not using 99 there because
we've already updated that in a previous
update call. Now we've only provided a
single argument here for TASK_SORT_ORDER
because that's our selection criteria,
but it's still expecting an array, and
notice also that the 99 is in double
quotes in this case because we're adding it to
an array. Now when the SQL statement's
built up to perform the update, each of
the values in args is used to replace
the question marks in the selection. And
they're replaced in order, and you have
to make sure that there are the same
number of args values as there are
question marks. So when I run the app
again now, number of rows updated equals
2, and we've now got two entries down
the bottom here; description is For
deletion and the Sort order is 999 for
both of them. So you've actually got a
choice in how you specify a selection
criteria when using the Content Provider.
If all the values for the criteria come
from inside your code, then it doesn't
really matter which way or which
approach, which method you use, but even
so it is worth getting into the habit of
using selectionArgs like we've actually
used here. So I'd suggest you do that,
again using that with a combination of
the question marks for your selection.
Alright so the last thing we now need to
know how to do, or to test rather, is
deletion, and as you'd expect this works
very similarly to updating, except we
don't have to specify the ContentValues.
So I'm going to copy the testUpdate code. Let's
just grab that. I'm going to put it up
the top here and we'll call this one
testDelete.
So in terms of what we need to add, we don't
need to pass a ContentValues object at
all so I'm going to delete that. All we
need to do is have a URI set to
specify the ID we want to delete. In this
case, this is for a single deletion or
pass id 3; rows affected, the deletion
call hasn't got ContentValues because
we're deleting not updating, so I can
delete that second argument. And obviously
update now has changed to delete -
we're calling the delete function from
our Content Provider. Number of rows
deleted is then our rows affected. So
that's the testDelete. That's pretty
straightforward. Then we just need to
go to our onCreate in this case, and
I'm going to comment out testUpdateTwo,
and we're going to call our testDelete
function that we just wrote. So run the
app again and we'll check our log. Number
of rows deleted equals one, and we've now
only got three entries as you can see
here; IDs 1, 2 and 4, and
obviously we specified ID 3 and
that got deleted. And you can see
there, by the way, the deletion, there's our
URI and it's got 3 as the ID passed
on the end of it. Alright, so the Kotlin
task basically got deleted there so
that's working fine. Now we can also
specify a URI without an ID, and instead
use a selection to specify which row or
rows to delete, just like when we updated
several rows earlier in the video. So
let's go and do that. So I'll come down here
and I'll take a, and what I'll do is I'll
take a copy of testUpdateTwo, because this
has got some code there that we can use
which will be quite handy, even though
we'll need to delete some of it. So we'll
call that test deletion two, testDelete
Two will do. So firstly, we don't need a
ContentValues object so I'm going to
delete that. Our selection, we're going to
start off with TasksContract.Columns.
Let's try deleting something by Task
description, so dot TASK_
DESCRIPTION, and we're going to leave it as
plus, equals and then a
question mark there. The selectionArgs
this time, we going to, instead of 99, we're
going to pass For deletion because if you
remember we've currently got entries
there For deletion. We've got that entry there.
We've also got this one here, so IDs 2
and 4 are currently set, or have the
DESCRIPTION, TASK_DESCRIPTION set to
For deletion. And then the rowsAffected
line is going to be calling the delete
and of the update, so I'm going to do
that. So I'll change that to delete, so I'm going
to leave that unchanged, CONTENT__URI.
Then values, we haven't got that anymore
so I'm going to delete that; selection
and then selectionArgs are the two
things that we've defined on line 58 and
59, and we'll change this number of rows
affected, updated rather, to Number of
rows deleted. So that should be all we
need to do, and we'll just go back up to
onCreate, comment out testDelete, then
we'll call our testDeleteTwo function.
And let's try running that again to make
sure it works. You can see here, Number of
rows deleted equals 2, and looking at
the number of entries in our database
from our query, number of rows returned,
rows in returned cursor equals 1. We've
now just got the one entry which is ID
1, which is our TaskTimer app creation
with the description there - Sort order
null ID 1. So that's our Content Provider
now working - we've actually tested it. So
in the next video, now that we've got a
fully working Content Provider, we're
going to start work on the user
interface for this app. So I'll see you
in the next video.