hi we're about ready to start on a
tutorial
that will show you how to use sql lite
which is
android's database system used for local
[Music]
storage my name is chad slooter i'm a
professor of computer science and
software development
at grand canyon university in phoenix
i'm really glad to be here at free code
camp
and provide another resource for you i
endorse free code camp i've used it in
my classroom i've used it personally
it's a great way to become smarter in
many things
so the goal of my career is to make you
a software developer
and free code camp is a great resource
so let's get started here with the next
lesson
so we're going to create an application
that will demonstrate everything you
need to know about sql lite
so first of all let's ask yourself when
you're designing an app
where would you save your data so you're
collecting names you're collecting
products you're collecting locations
whatever it is that you're collecting
you want to be able to save it somewhere
so your first choice might be locally
you could save things to a text file
on a local directory or in the sd card
or as we're going to show you in this
tutorial how to save to the sqlite
database
you could also think about saving your
data online
or maybe a hybrid of the two options so
you could work with api
services which are third party opened
access to let companies use their data
and you can even buy or build your own
api services if you want to save your
data
you can also set up a sql server that
runs online
and you can run sql commands against it
or you might use
some of the online instant service
databases such as
google's firebase or microsoft or
something from mongodb
which is all these things that are nosql
databases
so there's lots of options so if you're
building an app and you want to save
your data
you're probably going to come across
this type of data which is json data
so json works well with text files it
works with api
services it's for transferring between
different platforms
now we're going to be working with a
local sql database so we'll be writing
sql statements that look something like
this
you could also think about using
firebase or something along the lines
that has a nosql
structure and so your data can take many
forms
and there are different advantages to
choosing each so here's some
advice on why you would save your data
to a local
instead of a online service so local
database
would give you the fastest performance
that you can get
so it's nice to have your data online
but it's also nice to be really quick
so an offline cache or an offline
collection of data
keeps your app working even if you're
out of cell phone service
a local storage can be maybe just a
subset of the entire collection of data
that you need
so i think that maybe a good example of
this is google maps i've noticed that
when i'm out of cell service sometimes
i can still search for certain regions
of the country
and others i can't because there's a
local storage on the device
so if you build your app to have some
local storage your users can somehow
get by even if they're out of cell phone
range
now also if you're trying to do any auto
complete you know when you type in a
search term
and their suggestions given to you
that's a database lookup
and so if you're looking up databases
that are local it's fast
if you rely on a remote service to do
all your lookups it's not fast it's a
bad idea so
use autocomplete for local databases
also if you think about cloud storage
there's obviously advantages to cloud
first of all every device and every user
experience is going to have the same
data
so if you update a record in your app
that is saving to the cloud
the next time they log into a website
for the same app they'll have the
most recent copy of what they did so
sqlite
is a database that is something like you
would expect in microsoft access
it's a single file it's a text file
that's encoded in some binary format
it's not a large scale thing it's meant
for one user
and so all things are simplified
so sqlite is embedded there's no
installation required
you don't have to go ask for any new
libraries it's part of the android
system
it's something called acid compliant
which simply means
that if something goes wrong during the
middle of a transaction
you won't corrupt your data if you want
to see more about acid
databases and the compliance about
having
different rollbacks i have a tutorial on
that in a different series
now if you want to if you think about
the size of your database it's very
light so it will run quickly
and all the methods must remember to
close off their connections to the
database
so we don't lock up the database for
other applications
now you can also do read-only queries
and not have to worry about
having simultaneous access to it so
there are ways to manage
sqlite and you're going to see in the
tutorial that we're about to begin
is that we initially create these tables
and
databases in the application itself so
you don't have to go and launch some
third-party tool like my sequel
workbench or anything
you're supposed to be able to code this
in the application so the first time the
user
uses the application the tables are
automatically generated
and so no setup required by the user now
there are other tools that we'll take a
look at that will let you
peek in and modify the database if you
need to
and so that would be like sql workbench
but they're not required
now the data types that you can put into
a sqlite database are pretty limited
first of all you can have a null value
you can put in integers which are
automatically scaled so that the
numbers are appropriate to the number of
bits that you need to represent them
now a real number is like for a decimal
or a floating point
so you can save them as a real type text
covers almost everything so long text
short text they're all text
and then also something called a blob so
don't even bother with blobs
i wouldn't put them in your database
just if you have big things like
jpegs or movie files save them in a
directory
and then put the text of that link into
your database so your database remains
small and you can get to those files in
a folder
now let's talk about a specific sql
light library in some of the class
methods that we're going to use so we're
going to create instances of the sqlite
database
and the sentence will look just like
this in the code where it says
get writable database then we're going
to use the sql helper
and this is just in another video that's
ahead that will
create the database automatically the
first time that it's required
so you'll be seeing that in just a few
minutes so one of the methods that we're
going to see
is called the open or create database it
will open an existing database
or if that has not been created yet will
automatically make it for you
and so this is what the code will look
like so we will create a database
instance
and then we will create this method
where it says hey
go ahead and create customers.db and all
the work is done for you
also when we create tables we will have
another
function just like you'd expect in a sql
statement we create a table
mention all of its properties and then
execute that table
afterwards with a query string execute
also a concept that you're going to see
come up is called the cursor
so a cursor is pretty much a result set
so when you get a list of items back
from a search query
you're going to have a cursor which is
going to be something that you can
iterate through and go line by line
and then print them to the screen or
assign them to some other value
and so we'll be using the cursor inside
of a while loop
also we're going to work with something
called
content values so content values is
kind of an odd thing if you haven't seen
it before it's like an associative array
or
a hash map where you can put some
key value in in pairs so like in the
example here
if i want to put 1 for the student id
and jeff for the name and grade 9
you just do it like this and then when
you insert it into the database it says
hey i can automatically see
the column name associated with its
value so content values is something
we'll be seeing soon
so here's what the app is going to look
like when we're done you can see if i
choose view all i get everybody
if i add somebody let's try jeff he's
age 7
and he is an active customer it says i
have successfully added him and he shows
up in the list
as person number 12. if i click him it
deletes him
and so if you're interested in this
sqlite database continue on
we're going to show you how to do it
step by step hi
let's create an application in android
studio that uses the sqlite database to
save
our data on a local database
so you can see the app that we're going
to make is on the screen here
if i put in a name and an age of
somebody
and then i push the add button you can
see that it adds it to the list
also in the background there is a sql
database that's running and so the point
of this tutorial
is for you to be able to set up a
database on your android device
query it and delete things from it and
so in this first video
we're going to go through the setup
we'll set the layout and we'll to all
the buttons in the list view and
everything
and then in following videos we're going
to set up the classes that will manage
the data
and especially we're going to work with
the database so let's get started now
with the part where we add the layout to
the application
so this is what the layout's going to
look like when we're done you can see
i've got a couple of edit texts
i've got a switch a couple of buttons
and a list view and here's all the items
that we're going to create
so if you're really good with working
with layouts you can probably skip to
the next video
and do this one on your own however if
you'd like to have some help
on how these buttons are all arranged
we'll get started right now
so we're going to get started with the
application let's launch
android studio you can see that i'm
working on version 353
i'm going to start a new project let's
choose empty activity and next
let's name this thing sql demo i think i
have to name mine as
version 3 because i've done this several
times the language i'll choose is java
and i'm going to choose the minimum api
level as 14.
i believe somewhere along the line we
will have to bump this maybe to 19
because of the database features
but 19 will come when it needs to be i'm
going to uncheck this that says use
android x
artifacts and then i'm ready to go for
finish
looks like the app is all ready to go so
i'm going to take you through the part
where we put all the buttons on the
screen and arrange them in the right
places
so let's get rid of hello world i'll
select it and press delete
and i'm going to expand to get some
space to work with
and start dragging my items out onto the
screen
first thing i need to do is put plain
text on the top of the window here so
let's drag out the item then it's going
to give me name
so i'm going to set the anchor points to
the edge of the screen
to the left side of the screen and to
the right side of the screen
so this is all how you work with a
constrained layout so the constraints
are like springs that pull the items to
the sides
let's give it a little bit of a top
margin so i'll put in 8 pixels so it's
not quite at the edge of the screen
and for the width i'm going to set it to
match constraint
which will make the entire screen looks
to me like we could use a
padding of 8 on each side all right so
that's going to be the name
where we put in the name of the person
it's a good practice to change the id
names so that they're more descriptive
so this one is going to be called et
underscore name now down in the text
area i'm going to delete the part that
says name
and change it to the part in hint
so i'm going to put in the word name now
i can't put in the word name because
it's already been defined here as app
name so what i'm going to do instead is
i'm going to put in here
customer name which is its own string
as i hover here you can see that there
is a warning that says
hard coding is not a good idea you
should use string resources
so instead of typing in customer name
here i'm going to
cut it out and i'm going to the end
where it says here pick
a resource so the resources are a
centrally
located spot where you can put your
strings so i'm going to add a new string
value
and i'm going to call it customer name
and the value
is customer name with a space and let's
choose ok
now the same result in the end shows
customer name here
but the reason why you use string
references is because if you have to
change the language of your application
all the strings are found in one
location so if you go look
under the resources values and strings
you will see the new guy called customer
name has just been added
so that's the strings all right so we
better keep moving here
i want to put in the next item down
which is for the customer's age
so i'm going to select a number
attribute here it's the same kind of a
text edit but it's going to be a number
so let's see it says what do you want
your constraint to be i'm going to set
it to the
top to the bottom of the name there we
go and then i'll anchor this to the
front
and to the back i will change the width
to match the constraints
and for the hint so i'm going to use the
word age
and once again i'm going to select a
resource
and we'll call it customer age and i'll
put in the word
age okay i'm going to set the margins
again
let's go 8 around the edges
so the point of this is that i want to
use several data types for my
database so this is going to be a string
age is going to be an integer
and we need to have a boolean so let's
go into the buttons area
and i'm going to select one of these now
you could use a check box
i'm going to use a switch so let's drag
a switch out
and anchor them into place so now i got
my switch anchored let's go find the
text property
instead of saying switch i'm going to
put in here active customer
actually i don't want to just hard code
it again i'm going to choose a resource
and click add a new resource and call it
active customer
okay i can't put in spaces in my
resource names
next i need a couple of buttons so just
the standard button
and the other standard button i'm going
to link these as well
so the first button is going to fall
underneath of the
active customer and let's give him
eight pixels of buffer now the second
button we want to be on the same level
as here so i could anchor him to the
active customer or i could anchor him to
the top
of the first button now i'd like to
spread these out so let's do the
button here to go to this edge
let's do the right button to the right
edge now this gets tricky i want to
make these equidistant from each other
so i'm going to tell it to
anchor to this button over here and then
for this guy i want to anchor to the
left button and it never seems to work
for me
nope doesn't work so what i have to do
is i go into the text area
and do it manually and i want to set
this
as a new constraint so i'm in the xml
code
and i'm going to type in constraint and
i'm going to put in the word start
so the start is like the the left side
of the button
so the start is going to be matched to
the
end of another control which is button
one
and so now you can see they balance out
so they're kind of in the middle of the
page
all right so we got the buttons on the
screen we got them centered
now i need to change some uh the ids so
let's go into the edit text
first of all this is called edit text 4
and i'm going to change it to e t
h and just set that and say yes i'm
going to change it
now for the switch let's change him to
be called
sw for a prefix and then we'll put in
the word active
now for the buttons here i'm going to
change them as well so btn is my prefix
that i like
i'm going to call this thing view all
and for the text i'm going to change it
to be
view all for the other button let's
change from button to
to be called btn add
and let's change the text to add
all right the last thing i want to put
in here is a list view
so i'm going to use the legacy items and
call list view
now list view is considered legacy
because there's a new improved version
of a list view
this one's a lot simpler to work with so
for our simple tutorial
we're going to work with a simple list
view the one that you probably should be
using
instead of a list view is called the
recyclerview the recyclerview is more
efficient it runs faster
let's do the constraints i'm going to
attach this one to the bottom of the
button
and to the left side of the screen to
the right side of the screen
and to the bottom so it doesn't really
look like i'm creating constraints
but if i go look in the text view i
should see some constraints here
and i do not have an end so i want to
choose constraint
end to the end of and i'm going to
choose the parent
all right it looks like i have all the
controls on the screen one thing that i
would like to do is give this thing an
id
so let's go and call it l view and we'll
call it customer
list and we've got ourselves the
complete process
let's run the app and see if it works
hooray it looks like everything ran
so it looks like i'm able to put in some
numbers here and then i'm able to
click the buttons even though nothing
happens we're ready to go
on to the next part hey welcome this is
part two of a video series for making a
sequel light database on an android
app so in the previous video we created
the layout
in this video we're going to create the
model that will be the data for our
[Music]
application
hey so i'm glad you stuck around for
part two here we're going to create a
model which is a class that will hold
values
of a customer so let's go into the area
where we have the java code you can see
that main activity is already here
and we're going to add something to it
to join it so let's do a right-click
new and a java class now a java class is
just going to hold the data for our
object so we're going to create the
class we'll call it
customer model so let's think of three
things that we could add to our
customers
we could give them a name an age and a
boolean value called
is active and so these three things will
demonstrate the database fairly well
different types of data even though it's
not a very complex
object also think of an item that's
missing
so name age and activity that seems to
be about right
how about the id number so there's a
debate should you keep your id number as
part of the class
or just ignore it and use it as the
database i recommend that you have it as
part of the class
so at the top i'm going to also add
private int
id and so this object has four
properties
usually the next thing that i create in
a class is its constructor
and so i'm going to make two of them
let's do a right click
in the text and choose generate i'm
going to select
constructor and select all of the
properties
and click ok so this is standard
programming with java
and any object oriented language so a
constructor is called whenever a new
customer comes into being so when he
pops into view
we have to tell it what his id name is
what his
actual string name is what his age is
and if he's an active
or inactive customer and so all of those
are four parameters that we put into the
constructor
sometimes an application requires a
non-parameterized constructor
and so i'm going to just create one and
leave it blank
i'm not sure if we'll use it or not but
it's here just in case
the last thing that we'll need for this
class is for the getters and setters
so once again we will right click choose
generate getters and setters
and i'll select all of the properties so
all four of them
and click ok so if you count these you
should see that there are four getters
and four centers so there's one more
thing i'd like to add
is the to string method the two string
method is necessary if we're going to
print this
and so sometimes we print it in the log
sometimes we create it in a message on
the pop-up
sometimes we put it in a list but two
string will take all of these properties
and put them together into a single
string
since the tostring method is very common
it's part of the menu that you can
generate automatically
so right click in the text choose
generate
select tostring and all of the
properties
now when our two string method is all
put together it will create
a whole string that says customer model
id equals name
equals age equals is active equals and
then a close curly
so that will work for now we'll probably
come back and modify this later
because it will be kind of a long string
and we'll want to make it shorter
so now we're ready to make the program
that will actually make this application
run
so we've had to set up a layout and now
we've had this class
so you got to wait for the next video
and to see how we're actually going to
generate a database
and make this data save to it
hey welcome again in this video we're
going to continue
our application that demonstrates the
sql lite database
in android studio
[Music]
hey thanks for sticking around in this
video we're going to create the
actual listeners for each button click
and so
in the previous videos we created a
couple of buttons here
and we're going to wire those up to the
actual code
in main activity so when you start an
application
main activity is rather empty and so now
we have to start telling it
what the buttons are and who's going to
be clicking
so the first thing that i usually do
when i start an application
is make a reference to all of the
buttons and other controls
that are on the layout and so these are
called member variables of the class
they're at the very top
and they'll be accessible through all of
the other methods
in this module
so i'm going to put in a reference to
every control that we created
so first of all there were two buttons
there was button add and button view all
so if i need to import something because
the red text is there i press alt
and return or alt enter then i want to
put in the
edit text we had two of them one for the
age and one for the name
also the switch this is the toggle
switch that will tell us if it is an
active user or not
and so sw is the prefix that i used for
here
now the list view is also has to be
imported along with the switch so at the
end here
you should have to import by pressing
alt enter on each of these
now you don't have to use the same name
for the variable as you do for the
layout however it's much more
sensical it's it's easier to follow if
you do
now i made a mistake already i promised
you that these were class variables
member variables
and i stuck them inside of the uncreate
method this is a problem that you might
do as well
if you leave them here they will not be
accessible throughout the
page so i'm going to cut them out and
move them
up here on line 11 or 12.
so right in here so this is where
they're defined
and now they're going to be assigned a
value here on the uncreate method
so remember oncreate starts the
application
that's the first thing that it comes to
and so now when we create this
application we need to give these
variables a value
so for each of the variables that i
created up above i'm going to go find
their actual value that's been assigned
by the computer
so we're using find by id as the method
name
and we're looking in r which is the
resource folder
and then we follow it with id and then
the name that is located
in the resource so as i go through here
you notice that the
variable name probably matches the name
that i selected
in the layout it looks to me like i will
have one difference here with
activecustomer in the layout i just
called it switch
active but it'll still work even though
the matching
variables aren't exactly spelled the
same
the next part of the logic that i
usually program is the listeners so the
click listeners for each button
so there are multiple ways you can set a
click listener i prefer this method
here which is self-contained there's no
references outside of the
file that you're looking at right here
so an unclick listener
for one button works just fine this way
so just to test out things i'm going to
set in a toast message for each of these
so i'll just have a text appear on the
screen to make sure that
everything is set correctly and there
are no errors
all right it looks like the
application's up and running i click on
button
and it says the view all button here i
click on add
and the add button is working so looks
like the
button click listeners are working
however why did i just put the word
button on here shouldn't this have said
something else
let's go change that so i'm going back
into main activity here
and choose view all and why did the text
not show up
let's go look at the xml file and let's
see what it says for text here
sure enough i look in the xml and it
says button here even though it shows
uh view all on the layout some strange
things happen i have no idea why it says
that so i'm going to put in here view
all
and save it and run it again
okay so this time for some reason the
view all button is working properly
so now instead of just printing a toast
to the screen
let's actually create a customer object
and then print the toast to the screen
all right so i'm going to create a new
customer reference so
the customer model is the class that we
just created earlier
and now i'm going to create a new
instance of it so i'm going to generate
a new customer using the constructor now
as i
go through the constructor fields i'm
going to choose the values that
correspond to each property of the
customer
for instance the id number i have no
idea what number it's going to be so
let's just put negative 1 for a default
value that can be changed later
now i'm going to have a string for the
name so i'm going to snag that from the
entry form where it says et
name and i'll get the text and for some
reason you have to convert it from text
to a string
so go through that whole process three
items to get a string
next i want to grab the age so that
comes from the et age field
and then finally the switch that gives
us the active customer status
now this doesn't have a text item it
does have an is
checked so i'm going to select is
checked as well
when i'm all done here i have a list of
properties
but it's redlined so there's something
wrong here
and let's check it out so i hover over
the red line
and i see that my constructor does not
match
with the properties that i provided so
it's
lighting up the age you have provided a
string to me
well obviously age is an integer
so we need to convert that and then uh
let's go
fix that one and the the last one seems
to be okay where it says boolean
boolean is what uh the switch generates
is a is a true or false value
so let's convert this thing here to a
from a string to an integer
so the class that you're looking for to
do the conversion is called
integer capital integer dot and there is
a whole bunch of methods for
managing integers the one that we're
looking for is
parse int and you can see that the
parameter that expects
is a string so this one will work fine
so we will parse
int and then i will surround the rest of
it with parentheses
now lastly instead of saying add button
i'm going to put in here
the value of our new guy so it's
customer model and then i'm going to use
the tostring method that was in there
and let's see what shows up on the
screen so let's run it now
so here's the app i'm going to put in a
customer name i'm going to call him
how howard and then for his age let's
see if i can get to age
and let's click the add button finally
and you can see down here the customer
model shows up
so let's hide the keyboard and try that
again choose add
so you can see it's generating howard
age 34
and active is false however what happens
if i go into if i make a mistake here
so for age i'm going to put the word i
try to put in
old can't type how about if i leave it
blank and then choose add
my application just crashed
so i'm suspecting that the integer parse
in didn't work because i gave it a blank
let's try this again and let's check the
errors before i click the ok
button okay so the app is up and running
i'm going down to logcat in the corner
here
and switch back to the emulator and now
as i leave it blank i choose the word
add
and it says it keeps crashing you
stopped it
what happened that didn't work let's go
scroll through and see if we can find an
error
so it does say that the error occurred
here on
for input string blank
the process died on line 39. so i click
here and line 39 comes up and sure
enough
integer died so i could do some checking
i could do an if statement here
i could say if and then i could check
the contents of the
string or i could try another one i'm
going to try this it's literally called
try and catch if you haven't seen this
before it's kind of cool it's like an
if statement so the try says i'm going
to try
everything in the brackets here so i'm
going to try to
create the customer if it works
then i'm just going to continue on
and do the toast so the catch requires a
parameter here i think we have to put in
parentheses and type in the word
exception
and e so it knows what the problem was
let's paste in another toast and instead
of putting the customer's name in here
i'm going to say
ever creating customer
and let's run it again okay so the app's
up and running
i try add and i get an error creating
customer down here the app doesn't crash
anymore
it doesn't create the customer either so
the
the problem is that i need to prevent
the user from damaging the application
so there we go so in the next video
we're going to actually create
items and put them in a database we're
finally getting to the
database part so stick around and let's
install
a sqlite database and save these
customers
as we click the add button hi welcome to
another in the series of our sqlite demo
as you can see on the screen we are
creating a list of customers
and the data is saved in the database in
this video
we're going to actually get to the part
of the database tutorial where we set up
the database
[Music]
so when it comes to managing a sqlite
database you do almost everything
in the code so we can initially create
the database in a statement
in one of the methods of our android
application so i know in many
applications you create
a database and an external tool such as
mysql workbench or something similar
but here in android we can do it right
from the code
now there are several management tools
that are available
outside of this like sql workbench but
they're not
necessary but we will look at them and
see how they now we're going to include
the library which is part of
android and it is the sqlite library
so there'll be two classes that we will
be using here one is the sqlite database
class which is the database itself
so the second class is called the sqlite
open helper and it will force us to use
a couple of methods that will create and
update the database
the first time it's run or whenever the
database
itself design changes now we're also
going to include a few things that will
do the crud operations so we will have
things like get all customers
search for customers get one customer
delete somebody
insert one all of the crud operations
that you would expect
in a natural database now we're going to
focus
in on the on this video on the open
create database
so the first time we try to access the
database this
method will automatically trigger it
will only happen once
but when it does it will check to see if
the database exists
if there are no databases of this file
name then it will create one for us
and so we'll need this to be able to
define the tables that we'll use in our
database
so it'll look like this we'll have a new
database object
and then we will use the open or create
database method
and you can see that we are going to use
the string name of our
table and then a few other parameters
also when we work with any kind of
operations in sqlite we're going to be
using the
execute sql statement and so we'll be
writing sql strings
and then executing through this method
so here's an example we're going to
create a table called customerstable
provide the column names and then
following that immediately will be the
execute statement
and so this should look familiar if
you've done any kind of programming with
databases
in other languages and so we're trying
to keep consistent with what you've
learned in java and c-sharp
and other tools okay so now let's return
to android studio
and implement the things that i just
showed you in the slides
so we are going to go to the java code
and add a new class so let's do a
right-click in the java folder
add a java class i'm going to name mine
as database
helper now you can use your name you
could call it
database or dao object or something like
that
the point is this is going to be the
class that handles all of the operations
we're going to have to extend this as a
superclass to another object so we could
either type it in here or
wait just a second so i'm just going to
wait for a second
before i do that okay so here's our new
class now the part where i said we're
going to extend
is right here so extends is the keyword
so we're going to apply inheritance now
for this new class it's called sql
lite open helper and sure enough android
suggested for me
now sqlite open helper has some methods
that must be implemented
which is why we have this error now the
red line
it says it must be declared abstract or
implement the methods
so let's go ahead and look for the
little red guy
there it is the light bulb okay i'm
going to choose
implement methods and it
says you must use these two methods if
you're going to write this
class if it extends from its parent and
let's choose ok
so now you can see that we are going to
have to implement the method called
uncreate
and then we're also going to have to
implement the method called
onupgrade so you must start with the
understanding
that the uncreate method is going to be
called
the first time you try to access a
database object
and so inside of here there should be
code
that will generate a new table there
should be create table forms in this
kind of a
sql statement the second method is going
to be called whenever the version number
of your database changes
so let's say you have version one of
your application it's out there in the
world and there are
five million users and they're working
great and then you
plop a new version on top of them and
you say hey
we've just added a new feature we can
now add a few more columns to your
tables or
add a new table and instead of making
their application
crash because the tables don't line up
with the forms
we just have this on upgrade method and
so on upgrade
is triggered automatically it will
automatically modify
their schema for their database whenever
it needs to be done
and so this provides for forward
compatibility or backward compil
compatibility depends on how you look at
it now we still have an
error if you look up at the top you can
see that that says we've got a problem
it says a no default constructor is
available
now in usual times when you're
programming a class
it doesn't complain about a constructor
being absent
well this one is a problem because we
have extended from a
super or from a parent class and so we
have to implement a constructor that
will
satisfy his parameters so i'm going to
switch over now to the documentation at
android
and look at sql lite open helper
so if we scroll down here we can see
that there are three different
constructors that are available and we
have to implement at least one of these
so you can see that it requires that we
provide a context
so the context is a a reference to the
application itself and
we'll be able to compute that in just a
second string
name that refers to the name of the
database that we're going to create
this here thing called cursor factory
we'll look down at the notes in just a
minute to figure out what that is
and then version which is going to be
simply version one
for the first time we make our database
there's some other constructors but i
think we're going to be
working with the first guy so let's go
and scroll down a bit
and talk about the parameters so context
is
is used to help find your database uh
the second is the name of the database
is the string
and then here it says a factory object
used for creating
cursors it says or null for the default
so this value may be null well let's
just leave it null then and see how that
works
and then finally the version number will
start at version one
so let's go ahead and implement this
first constructor
using our helper tools
so if i hover over the sqlite open
helper i get the little red light bulb
i'm going to choose create a constructor
matching super
and you can see i extended this to the
right to see the whole thing
i'm going to pick the first constructor
and click ok
now we have four parameters provided
here
i would like to provide a string for
each of these last three and then pass
those
up to the super so look how this works
i'm just going to delete these last
three
and keep context around because i can
get context from my application
the other three i can just provide as
hard-coded values
so what's my database going to be called
i'm just going to name it as customer
and let's call it db so you can name any
string you like for the file name
now for the factory we we read in the
documentation that that can be null
and the version number can be one and so
we are passing up to the parent or to
the super
the constructor for these four
parameters and then we're going to
require
that the application give us the context
so we'll have to figure that out in a
few minutes all right so that gives us
the constructor for
our new class in the next few videos
we're going to create the
database using the oncreate part and
then we're also going to implement the
on upgrade part so stick around for the
next video where we're going to
implement these two methods
hey welcome back we're in the middle of
a demo for a tutorial for creating a
sqlite database in an
android app and so in this video
we're going to actually use some sql
statements to create the tables that are
going to store the data you see on the
screen here
[Music]
all right so the goal here we're going
to create is a table that will match the
values of our form so our user model has
a name
an age and a boolean for active customer
and so those are the three columns that
we're going to have to create in the
table
now in the previous video that we were
working on we started implementing this
class called the database helper and so
we created a constructor that will tell
us what the database will be named
and now we're here at the point where we
have to use the uncreate method
this method will be called the first
time that this database is referenced
and since it doesn't have any definition
we're going to have to create the sql
statements
that will generate the tables in the
sqlite system
so let's start off by defining a string
the string is going to be called
create table statement and for right now
i'm going to leave it
empty just so that we can save the sql
statements for
in the next few minutes and then to
remind myself of what's coming next i'm
going to type in the command
db execute sql
and then the statement string that i
just defined so where did db come from
well you can see that db comes from a
parameter that i'm passing in here
and so this pattern is going to hold
true
in many of the statements that we write
here in the future
so we need to now figure out what the
statement
is to create our new table all right so
i'm going to go
to my memory bank and type in what i
know about sql statements
so a create table statement is what
we're going to work on
so the name of my table i'm going to
define as customer
underscore table and then i'm going to
define the first column as the primary
key so let's call it id
it's an integer we're going to specify
it as a primary key
and auto increment so this is pretty
standard sql stuff if you don't know sql
very well
then look at some previous tutorials
that i've created on working with sql
databases
so i'm going to stop here for a second
and define some
static variables i know for instance
that the word customer table
is going to be used often in my
application
and i don't want to have to retype it
every time so i'm going to cut it out
here
and create a variable that i can refer
to in future statements
so let's uh let's highlight the
customers table and i'm going to right
click
and i am going to refactor
and look for extract and then i'm going
to say
i'm going to re extract this as a
constant
and so constant it's going to say what
is the constant name
and let's choose ok now look what it did
it swapped out using some quotation
marks that was handy
and concatenating strings and now the
value
for my string customer table is defined
at the top so now let's
define the rest of the columns that i'm
going to be using in the table
so we'll have customer name customer age
and their active state
so let's take a look here if we can
define some of these
as a string as well so customer name i'm
going to extract
so let's go to free factor and choose
extract as a constant
so now i'm going to put attach the word
column at the beginning of this name
so it will create a static variable
again
called column customer name and we'll
put it at the top
so i'm also going to define the column
names for the customer
status such as his age and his active
status or not
and then finally we might as well add
the column id
as well as a as a constant and so by the
time we're done here we will have a list
of five different constants at the top
of the page
so at this point it looks a little bit
tedious to redefine these constants at
the top of the page
but as soon as we start generating more
methods below you'll see the advantage
of having a constant instead of using
the variable name each time
now i should be able to go back into my
main application and invoke this
database helper
let's make sure that i've typed
everything correctly here because if i
mess it up then
well obviously we'll have problems so
let's check to see
create table make sure that you have
spaces between the names so that this
quotation mark
and a space have a of a gap uh it
doesn't need to have spaces here
make sure that you have spaces between
each of these
make sure that you have no missing
commas
and make sure that all the data types
are correct all right let's go back into
the main activity now
and this is where we're going to try to
generate this so the place where i'm
going to first
access the database is when i create a
new customer so
the method called button add on click
listener is where i'm going to go
alright so we're going to now create a
class a reference to that
database helper so i'm going to use the
standard java
instance creation method so we have a
type
lowercase name for database helper and
then
a new instance so what do we have here
we have some red stuff at the end
it says your constructor for the
database helper requires a parameter
now just to remind you if you go back
into database helper
the constructor that we created said
provide me the context to get to this uh
to get to your application okay so i
need to do that now
so what do we put in here some kind of
context
well if you're like me in some java
beginning you just say
i've seen this before you put in the
word this and you hope for the best
but it doesn't work what's the matter
with this so if you're having trouble
with context and you can see another
video that i created earlier that tries
to explain the best of my ability
what context is however you look at this
one and it says
the context that you're trying to send
is
a reference to the method called
onclicklistener
well the context is really a reference
to the application so we want one level
higher than that so there are two ways
that i can think of that would
get here we could first of all type in
mainactivity.this
that's the correct this that we're
talking about and so we'll go with that
one with the
mainactivity.this so this
is not doing anything yet except for
making a reference to the new customer
database
all right so now we've got this sql
table created
now we need to be able to add some data
to it and then we'll see if it actually
saves correctly
so in this same module in this class i'm
going to create a new method that's
going to add one item to the database
so the method that i'm going to create
is called add1
and it's going to expect to see a new
customer model
so we'll make that as a parameter and
then for the default return value we'll
make it a boolean
and for this first iteration of the
design i'm going to say that it always
returns
true but that will change depending on
whether the successful
insert was made or not so the first
statement that we'll create is called
sqlite database db
equals and then i'm going to get it from
this class so the database helper class
and i'm going to choose get writable
database
now where did this come from did i code
this anywhere no it came from the
default
properties that are available to the
sqlite open helper
so the class that i'm opening right now
and extending
has this property in it so this will get
our one
and only database that we're going to
write to notice i have the
get writable option and get a read
option so
if i'm going to insert data that means
i'm writing to it
next i'm going to create a object called
content values
or i'm going to shorten it to the
letters cv
now content values is a special class
and it works like a
associative array in the language php or
a hashmap in other languages i can take
pairs of values and associate with them
this also looks like the intent in
android studio when you when you try to
put in
a bundle or you try to put in values to
pass from one to the other you have to
specify
a value name and then its actual
contents
so in the first case i'm going to
associate the customer
column name with the actual value of the
parameter passed in which is called
customer model so we're associating a
column name
with a value which is a string the next
item i'm going to put in
is the customer's age so we'll select
the column name which is
column customer age remember i'm using
the
static constant values from the top of
the program here
and then i'm going to get the property
called get age
we'll associate these two together and
put it into the content values object
lastly we'll get the is active property
and we'll associate it with the proper
column
now you notice i didn't put in an id
so the reason why i don't have to tell
it what the id number is for this guy
is because it is an auto increment
column
in the database it have if were it not
an auto increment
or a automatically created value then i
would guess have to
specify it in the content values
the next command that i'm looking for is
the insert command
so i'm going to type in db and dot and
you can see
insert is one of the methods associated
with the database so let's hover over
the
parentheses here to see what kind of
method parameters we're looking for
so you can see that it needs a table
name something called
a null column hack and a third which is
the content
values well two out of these three i can
i can guess
so the first one is the table name i
have something called customer table so
let's put in capital customer
and choose customer table the second is
called the null column hack
we'll come back to that one in a second
and then the third one is the content
values which is
cv now what is null column hack
i don't know about you but null column
hack sounds like it was designed wrong
why are they hacking something
let's go find out what they mean so
sqlite database
is the android docs that i'm looking at
and let's do a control
f on this page and i'm typing in null
column
so it looks like i'm coming to one of 20
references on the page to the null
column hack let's see if we can figure
out what it is
as i page through here it looks like
null column hack is used on many of the
operations in the database
what's it do aha here we go we have some
explanation
string it is optional it may be null
sql doesn't allow inserting of a
completely empty row without naming at
least one column
it goes on to say if your provided
values is empty
no column names are known and an empty
row cannot be inserted so
it sounds to me like if you're trying to
insert an empty row then you have to
have a specific name here
well i'm not inserting an empty row so
i'm going to stick with this
null option for right now otherwise i
believe i would have to provide
at least one column name so you could
put in a column name or you could put
null
so since it will accept null i'm going
to put in null here
in the middle now i want to know if this
was successful or not so let's go hover
over the beginning
and we should see a little light bulb
appears that says introduce
local variable let's see what that does
so the return type from the insert will
return
a long so this means it's really a
success variable so if i get a positive
number it means it was success
successfully inserted
or a negative number means it was a fail
so i'll just simply put in an if
statement to say if it was negative one
will return false
if it were a positive one then we'll
give it a true all right so that gives
us a full
add one function now let's go back to
our main method where we'll actually use
this
so i believe we left the code in this
state before
i started on this process here we
created a customer model inside of the
try catch
actually what i want to do is create
this definition
before the try and so we'll just create
it here as a null value
then inside of the try we're going to
assign it so we will create the
assignment statement
separate from the definition statement
and then
if it works we will have a successful
customer model
however let's talk about the next
section if it fails let's make a
customer again
so let's say if this create person fails
then we can provide some default values
and that way the user knows that
something went wrong
so for the default values i'll give it
as an id of negative one
a name as error an age of zero and
is an active member is false so this is
arbitrary you can put anything in you
want if the
failure occurs finally near the bottom
we can go and try the insert method
so we will call on the database helper
class
we'll put a dot and we should see add
one then we can provide as a parameter
the customer model so will this work or
not
we can find out if we choose add and
introduce to our local variable here
which will give us some kind of a true
false statement so let's
let's call it things success
i will make a toast now and it will
indicate whether it was successfully
inserted into the database
so i should get a true or false now we
can test this out
all right so we got the app running here
let's put in somebody
what in mary age is 52 active customer
and ad
it says here success is true well it
must have gone into the database
but how can we tell well let's go check
out where that database is
now fortunately in android studio
there's a way to do that
let's go click on android studio choose
view
tool windows and look for device
file explorer this will let us look at
all the files and folders on our
computer or on our device
so let's go and look at all these
folders here now where could it be
let me help you find it so the folder
you're looking for is called
data we're looking for another folder
called
data and then under there are all of the
applications and services that are on
your android phone most of them go with
android itself so dot com android we can
probably ignore all those
and we scroll down a ways until we come
to something that looks customized to me
so sure enough there it is there's edu
and shad slater it looks like
as what i created and inside of the
folder called databases you see three
files
let's take all three of these and i'm
going to
save them so save as
this allows me to execute a export
so let's go to the desktop and choose
open
and that will save all three of these
guys so now what are they going to look
like
if i try to just double click on
customer db you can see i get total junk
this is
a binary file unreadable so not very
useful
however you can see down at the bottom
of the screen i have two applications
that are installed on my computer
this one here is called db browser for
sql lite
and this one's called sqlite studio
this is kind of like the my sequel
workbench tool
now the one that i like to use is called
db browser for sqlite
here's their website it's called
sqlitebrowser.org
it is very simple to use
sqlite studio is also available
apparently only for macintosh
so let's try to open the database i'll
click on open and i'll navigate to the
desktop
and you can see all three files that i
just saved are here
the one i'm looking for is customerdb
this is the file that is the principal
database file
click on open and you can see that
there's a browse
to the structure and the data so you can
see i've created a few users they've got
their ages
and their active customers are both set
to zero so remember in sql lite zero is
a boolean
false and one is a true so you can see
that i have
data i can manipulate the data i can
create new records just like you would
in any other database
management tool so the point here is
just to say that i can look inside the
data
i know it's being created so that would
pay
take us to the point where we can insert
data there's still a lot to do yet we
need to be able to delete data
we need to be able to show it to the
user on a list in the application
so we're getting closer but this is the
point where we start creating sqlite
databases
so stick around and we'll show how to
create this into a
list for the user to see
hey welcome to another part of our sql
lite demo
we're creating an android app that will
save data to a local database
and then you can view it on a list view
so
the point of this video that we're about
to see is how to create this list view
that you see in front of you
and we've been able to add an item to
the database but now we're going to
display it
all right thanks for sticking around
we're going to create this
list that you see here and pull the data
from our database and update it every
time we add a new record
so to make this work we first of all
have to start in the database helper
and we have to create a method that will
pull all items out of the database
so the first thing we should do is
define what the properties are going to
be when it returns
so since we're going to return a list of
everybody we need to make that the
function or the method return type so
after i choose lists of customer model
i can give it a name such as get
everyone or select
all or get all you can name this
whatever you want but it does have to be
specific so you know what it means
now it looks like we have to import
whatever a list is so let's press alt
enter and now the list is happy
so to generate the return list we're
going to have to define it first
so we'll create a item called return
list it's defined as
type customer model and then it
works as an array list that should work
really well then before i forget
at the bottom of the list i will put in
a return statement so it satisfies the
requirements of my method
so in between these two i have to
generate a bunch of code
that will get data from the database so
the first item that we should do
is create a sql statement that will do
what we asked it for
so this is a pretty simple one select
star from
the users table and the customers table
really
notice i'm using the defined constant
called customer table
so now i'm going to make a reference to
the database that's active
so we'll say the sqlite database db
equals
and then i'm going to type this dot and
the first item that i choose is called
get
writable database now think twice about
what you just chose here
do i really need a writable database i'm
just selecting here so i
do not need a get writable what i could
do is just
type in readabledatabase now what's the
difference
so the difference is that whenever you
open up a writable copy of the database
the database is locked no other
processes can
update it and so it creates a bottleneck
if you have a readable copy of the
database many different processes can
read from it since there's no changes
being done
so better to choose readable database
even though
writable in this case would probably
work
next i'm going to actually execute this
query so i'm going to type in db
dot and we have different choices we
could do execute sql
or something called the raw query now
we're going to choose raw query because
of a return type
you can see up at the top that it says
this return
type is called a cursor so we're going
to introduce the idea of a cursor in
just a moment and see how it works
but i'm going to choose raw query and
then for the properties let's put in the
query string that we just defined
okay so now we got this query that's run
what do we do with it
well let's see let's go back to the
database and let's hover over it
the light bulb appears and it says
introduce a local variable
so the local variable is called a cursor
so that's the return
type of the raw query so
a cursor in sqlite is the result set
so just like in other database languages
when you ask for a query you get back
something called result
or something similar to result a cursor
is your results
so it'll be a complex array of items
and it'll be rows and rows of data if
there are lots of people that were just
selected
so the cursor is your result set
so i want to check to see if there was
actually results brought
back from the database did it succeed so
that will be an if statement
now i'm going to check to see if cursor
dot and what is the method that i'm
going to choose from
you would think that it would be like a
method called
size of return or success what you want
in this case
is called move to first which means move
to the first result in the result set
now if this turns out to be a true or
boolean true value that means that there
were results
so yes then we can continue to query
through the list if there are no results
then the else statement will take over
and we'll do something different
okay so i'm about ready to begin some
coding here and i want to make sure i
know what i'm going to do so i'll put
comments
so what i want to do next is if there
are results
i want to loop through the results and
then i'm going to create a new customer
result
for each row a new customer object for
each row
and then when i create that object i'm
going to insert it into the return list
that was defined just a few lines up and
then we'll be able to
make this function succeed so next i'm
going to do
a a do while loop not something that we
do all the time but this will work here
so i'm going to do this method while
we can move to the next line so
we will finish this when as long as
there's new lines
all right so here's the pattern we're
going to follow we're going to introduce
a new local variable of the type that we
expect to come from the database
so the first item is a id number which
is an integer
and then we're going to get it from the
cursor
now i'm expecting an integer so i'm
going to use the word get
int now obviously if there is something
in the database in this column that is
not an integer
the program will crash or it will not
give us the right record
so how do i know which position is the
integer
so i'll just use position 0 because i
know what the database table design
looks like
so the next item from the database is
going to be the customer name
so i'll define a local variable called
customer name and
snatch it from database at position 1.
we'll do the same thing for the age now
when i go to get the boolean variable
for my is active or not i'm going to
have a little problem
as you can see i'm typing in boolean
customer active equals
so far so good but then when i try to
get it from the database i'm going to
say
from position 3 i'm expecting a boolean
now it doesn't even give me that option
why not
it's because in sql light there are no
such things as booleans
it just saves an integer of either 0 or
value 1.
so what i'm going to have to do is take
that integer and then convert it
into a boolean so wouldn't it be nice if
i could just say get int
and then assign it to the boolean well
obviously as you can see i've typed here
it doesn't work that way
so it says you are requiring to assign
a boolean value and you have provided me
with an integer
not going to work so i'm going to turn
this into what's called a ternary
operator
and you can see here that it's asking
some questions it's got a question mark
and a true false
but it's a mysterious syntax that's
confusing
especially if you're new to programming
so what does this ternary operator do
if if you understand this then skip
ahead 60 seconds otherwise let's take a
look at an example
so i did a google search and tell me
what a ternary operator is in java
and i came to geeksforgree geeks4geeks
it looks like
so here's the process so a result
variable
is going to be given a question so this
is like a true false statement
and if this first question turns out to
be true then we will assign
the first value after the question mark
if this results in a false then we'll
give the second
so it's like doing an if statement but
it's in more compact form
so here are two examples so we could say
if expression one
then variable equals this this this code
right here is the understandable if then
else now if you want to do it in ternary
operations you could do it like this you
could have a result
and then you could use the ternary so
turn area is what i used here if you
want to look it up some more
go ahead but it's a short form of
writing if then
else statements all right back to here
so i've got ourselves the
three objects that are required to make
a user on the next line i'm just
simply going to create a new user object
so of type customer
and we are going to provide all four
parameters in the constructor so we have
the id
the name the age and the is active
next we should be able to put in the new
customer
into the list so we should provide the
add function
so down here in the if section i'm going
to just put in some comments because
really we don't have to do anything
if there are no results from the
database we will not add anything to the
list
now the list is still defined we defined
it up here at the beginning in line
63 so it's an empty list so it won't be
null but it will have no items in it but
for just clarity sake i'm going to leave
empty in this else section now one thing
that you have to remember
in sqlite is to always clean up after
yourself so you've opened up a
connection to the database
uh let's close it so other people can
use it so we're going to close the
cursor
with a close statement and we'll close
the database with a closed statement too
all right check out near the top we have
an error still
so when we created this raw query it
says we provided the query string
but it says there's something wrong here
let's go check out the light bulb
it says nope that's not what i want uh
i'm looking for the parameters that are
missing so
i don't know what i'm supposed to put in
there let's go check out the
documentation if all else fails look it
up
right so i'm coming back into the
android docs
and we see here sqlite database and
let's do a control
f and type in the word raw let's see raw
query shows up on the page
and what are we supposed to do with raw
query so raw query
returns a cursor well that's good to
know it says we're
expecting a sql string so a sql
statement
and something called selection args so
what is selection args it says you may
include question marks in the clause of
the query which will replace by values
from selection args
okay so this is for working with
prepared statements and
we're not going to do that right now so
i think we can just provide a null value
here
so the selection args are none okay or
no
so the view all button is where we're
going to actually make this happen
so let's quickly put in some code and
then toast it to the screen
the first thing we have to do is create
a reference to our database so just like
we did
in the button add we create a database
helper object
and we'll set that up as a new instance
now this should be as simple as creating
a new list of
type customer model and we can
capture this from the database helper
with a method called get
everyone now for the toast i would like
to simply toast out a
really long string and so for the toast
message i'm going to replace the string
with everyone you can see that this
isn't quite right so let's
see if we can add a dot to string on it
and it looks to me like that will work
so let's see what happens i'm going to
run this
okay so i got the app up and running now
before i click an ad i'm just going to
choose
the view all button
well it certainly looks like i've locked
up the database nothing is responding
i can't make anything happen i've
probably put the application into an
infinite loop
so let's kill the app let's click the
little red square
and see if we can figure out why so my
guess is
i'm going to the database helper to look
at my loop here that i am doing on
the database so the do while loop comes
to mind as a possible
problem so look here at the while loop
it says i'm going to do this while i
move to first
well moving to the front of the list
means that i'm always going to be doing
the first record in the database so this
isn't what i meant to type
let's see what else there was there was
move to
next that's the one i want so that means
proceed through the database one at a
time not repeat the first one over and
over
let's try that again
okay let's add a few people just to make
sure that this works
so we got jim and kim and let's go ahead
and choose the
view all button now so click here and i
get a customer list it looks like it's
working so if i choose add a few more
times
i get a few more kims let's go to view
all and the list is getting bigger
so it looks to me like we've got
ourselves the database
receiving information and the view all
button is able to collect it back
now in the next video we need to make
this display a lot prettier instead of
this
toast message we're going to turn it
into a list so
up and down the page on the main
activity we will see
a scrollable list so hang on to the next
video and we will do that
hey welcome back we're at part six or
seven by now on this sql demo
we're going to add a new feature to the
app right now
in this button view all we will click it
and then we will display
all of the people in our list so that
way it's not just some ugly toast
message like we had in the previous
video so that's coming right up
[Music]
all right so here's the current state of
our app right now when i click view all
i get this
ugly toast message it tells me who's in
the database
but it doesn't display it in a form that
the user can use very well
so what we want to do is create a list
adapter for this view
and make it show up on the screen so i
was working
in the view all button just in the
previous video
and let's comment out the toast because
that doesn't seem to be where we want to
go
so this section is more about android
and how the
components work rather than the database
but it's very related
what we need is called something called
an arrayadapter so let's create an
arrayadapter here
and let's just call it the customer
arrayadapter so to create the
arrayadapter we're going to make a new
instance of the object
so the type is actually a list
so the type of thing that we're looking
for a list of is our customer model
so put that in the the angle brackets
and then the first parameter in our
creation
is called the context so main activity
dot this
is the context of our guy now the next
part is a little odd we go to
android dot r dot layout dot
and i'm choosing simple list item one
which is a predefined adapter that
just gives a string one one per line
this is the simplest array adapter that
you can make in android
and so if you want to see how to make
more complex array adapters i have
another tutorial for that that i'll
leave as a
suggestion above now the last item in
the parameters
is to have the list so what
items do we want to show in our
arrayadapter well we want to show
everyone
the the people that we just selected
from the database
now the the next item should be to
associate
this arrayadapter with the actual
control on the screen
and its name is lv underscore and we
should be able to find the
item called a customer list then we put
inside here the array adapter that we
just made
so let's check it out to see what
happens now when i click view all
you can see that instead of showing a
toast with a bunch of ugly stuff we have
all of these people on the list
now if i click view all again it seems
to work let's go ahead and add another
person
i'll add richard and choose add and it
says success is true
where's richard i have to click the view
all again
and this time he shows up as customer
number five
so it seems a little awkward that every
time i add a customer i have to click on
view
all to get the list we could probably
fix the arrayadapter so that it works
with more than just one button
first of all let's take the arrayadapter
and let's define it at the top of the
page so
we'll make it into a class member
variable copy this
and put it up here with the rest of the
items at the top
so the customer array adapter is here
now when we come back down to here we
don't want to redefine it we just want
to reference it so we'll delete the
definition
as a matter of fact why don't we just
take this entire
list here copy this and include it into
the uncreate method
so the create method should be to show
the customer list
as soon as the app is opened not a bad
idea
there is one problem however as you look
at the end of the line the word
everyone is highlighted in red so
everyone didn't get assigned
automatically where did we create
everyone
well everyone was set right here in this
adapter part so
we're going to have to define everyone
up here as well
we could also take some of the values
that are in here like the
database guy let's copy him and let's
define him
up near the top
so let's make him a class member
variable and not
uh not assign him anything right away
but as soon as we
launch the program we can take the
database
a helper and give it uh give it a new
definition
so now instead of everyone we can just
come into here and type in database
helper dot
get everyone and that seems to work now
let's come back down to here and we did
split up these
two into a you know separate lines that
could work
but we could also just put in the
everyone list
inside of the parentheses and then
eliminate the line above it
so it's a little bit a little bit
different but it seems to work just
as well so now it appears that we can do
the arrayadapter in two different
locations so
let's copy this code at the top here
and let's insert it down into the
buttons so after we have a successful
idea that we created a new person we
will update
the array adapter here now there's
another red flag that comes up
i've copied and pasted this code in
three different locations
it's only two lines but why have two
separate
lines and all that code when we can make
it into a single method
so let's refactor again i'm going to
highlight one of these sections here
and let's see if we can refactor this
and
extract it so we want to extract it this
time
into a function or a method we'll call
it so let's
create a method so i'm going to name
this something obvious like
show customers on list view and choose
refactor so it says hey this is you sure
you want to do this i will accept it
and let's say do you want to keep this
yes i will replace it
and replace it and so automatically you
can see these three areas that have been
highlighted
and database helper is the parameter
that goes into them so
it looks to me like we've got ourselves
a
refactored function let's see if it
works
so sure enough i run the application and
when as soon as it starts
all of the users in the database come up
let's put in somebody new
let's put in k and choose add it says
there's a success
well let's get this keyboard out of the
way and sure enough k comes down here so
if i choose view all it works
or if i click on add and we get another
version of k
version 7 8 9 10. so now we have lots of
people in my database
and it appears that the list view
automatically updates
so now you might say well are we done
well let's add
another method to our function that will
delete somebody so if i click on here
on somebody's name i could show
details about them in my case i'm
choosing that that's going to be the
delete function so
maybe not the best as far as user
experience but
as far as making a simple tutorial
deleting an item when you click on it
will work fine so we'll do that in just
a moment
hey welcome to another part of our
application on sql lite
in this video we're going to create a
delete function so
if i were to select somebody such as jan
and you'll see at the bottom of the page
that says deleted and tells me who it
was
and the list is automatically updated so
that's what we're going to do just ahead
so if we're going to do some deleting we
need to come in here to our
database helper and do some functions so
let's define quickly what this process
will do
so i'm going to set up the function as a
boolean return type
and if the customer model is found in
the database
then we will delete it and return a true
statement
if it is not found then we will set a
false value to this return statement
now i should also confess that what i'm
showing you is one way of working with
sql lite
creating these methods such as delete
one and get everyone
and add one these are typical kind of
database operations that you put into a
dao like i'm doing here
so a lot of tutorials in sql lite will
have you do this code
right in the main activity so you can do
it in the main activity and they've
actually designed
sqlite to work that way but as far as
best practices go
as separating the functions of your app
it's good to have a dao that has all of
the methods in it
so this is kind of a personal preference
of how i would code
but there are other ways so the first
statement that we need to get is the
sql database and we want to get an
instance
of a writable database this one's
writable because we're going to delete
from it
so now you rely on your sql knowledge
and you write a statement to delete
somebody
so remember we're getting a parameter
called customer model
we want to delete anybody that has the
same
id number as hi as this person so we'll
do delete from
and we use customer table where the
custom or the column is equal to
the column id and it's equal to the
customer's id
one other detail that might be worth
mentioning is i create a delete one
as an upper case let's make it as a
lower case simply because
in java most methods have a lowercase
for their first letter
so just like we did with the previous
queries we're going to do a raw query
we'll send it to query string and
provide it with a null value for the
parameters
if i click on the little light bulb at
the beginning you notice that it will
return a cursor type
and so we'll set what's called a new
local variable as type cursor
we'll check to see if we can move to the
first item in the results
so this seems a little strange are the
result sets for a delete function
yes there'll be one result probably
because we matched one id number
and so if we find that id number we
should return it true
now let's come back into our main
activity and see if we can implement
this delete1 function
so where does this happen do we have a
button click here
or here we need a new function that will
listen for a click so the method that
you're looking for is called set
on item click listener so an
itemclicklistener is not the same as a
click listener
itemclicklistener will give you a number
of which guy was clicked
so there's a lot of code that was just
given to us
let's see if we can interpret it so on
item click listener we are
listening to first of all we have an
object called the parent
we have the view which is the list view
and here is a key one called the
position so the position is which item
was clicked
in the list we'll use that
so what i'd like to do is get the
customer that was just clicked so that
way we can send it to the method to
delete him
so i'm going to name this guy click
customers
so what i need to type in is the parent
so the parent of the listener
is the list view and then there's a
method that's looking at me in the face
called
get item at position so let's choose
that one
what position do you suppose was clicked
well they gave it to us here it's called
position
on the previous line so that will tell
me which person
was just clicked or will it i haven't i
have a red line what's the matter
it says now that i have a conflict of
data types
so what's required is a customer model
that's what i defined over here
and what was sent back is just a generic
object
well i know that in a list you can put
all kinds of different
objects and i happen to put in customer
models here
so because i know that there are
customer models in the list i can
safely do a cast so i can put in
parentheses here
i could do it manually or let's see if
they can do it for me uh
automatically so let's see is there a
suggestion for what to do to fix it
the light bulb it says here cast let's
try that cast two
and it does give me that so
automatically it says we're going to
cast it to a customer model
so now we have who was clicked
so now we want to go ahead and call the
database helper function
called delete1 and we'll send it the
clicked customer
and then we'll also update the list view
so we'll use this same function that was
created a while ago called show
customers on list view
so that should work lastly it would be a
courtesy to tell the user that
since you clicked the list we just
deleted the guy
like i say not the best user experience
but we should at least tell them what
they did
let's see there's an error it says uh i
forgot you need to have the database
helper sent
along as a parameter so we'll put that
in the parentheses
and let's see if it works okay it looks
like i've got the app up and running
with all these different users i'm going
to pick somebody that's pretty obvious
like
beth number two let's click her and it
says here beth was deleted and sure
enough we skipped from id
to one to three let's delete some of the
even ones there's a four
a six an eight a ten and sure enough
just the odd people are left
all right so let's try to add somebody
let's put beth back in so beth is now
going to be 632 and she's not a customer
and adder
did it show it so she is now number 11.
if i click 11
the delete function seems to be working
all right so we've come to a point where
we've got
a functioning application we can add we
can show everybody
we can delete somebody what's missing
well i think what's missing
is a search function so if you want to
take a challenge
let's put in a new item and a new edit
text view or we can say
search phrase and then once you click
search
it will go to the database and get the
people that match your search criteria
and then update the list with a new list
of customers not the entire list
but just a partial list that matches the
search results
so i'll leave that to as a challenge
because the videos have gone a long time
now
now before we end this tutorial it would
also be fair to
let you know of a library called room
now in all frameworks there are things
called orms which are
object relational mappers and they make
creating databases
easy they create sql statements for you
and they handle some other details that
we're not even aware of at this point
and so in android the library that you
need is called
room but i didn't teach you room at the
beginning because
as a good teacher i want to make sure
you understand the fundamentals
you need to understand how to read and
write sql statements
before you can understand how a tool
like room
can make your job easier but the next
step then
is if you're into a database system you
should explore what room is and of
course i have a tutorial for you on how
to use
room with android but that is another
video
so until then thank you for watching