SQLite Database Tutorial for Android Studio

hi what about ready to start on a tutorial that will show you how to use sequel Lite which is androids database system used for local storage [Music] so we're going to create an application that will demonstrate everything you need to know about sequel light so first of all let's ask yourself when you were 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 for your first choice might be locally and 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 sequel Lite database you can 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 sequel server that runs online and you can run sequel 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 no sequel 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 sequel database so we'll be writing sequel statements that look something like this you could also think about using firebase or something along the lines that has a no sequel 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 a 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 may be 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 autocomplete 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 and the next time they log into a website for the same app they'll have the most recent copy of what they did so sequel Lite 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 sequel Lite is embedded if 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 that I have a tutorial on that in a different series now if you wouldn't 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 so there are ways to manage sequel light 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 sequel workbench but they're not required now the data types that you can put into a sequel Lite 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 globs 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 sequel Lite library in some of the class methods that we're going to use so we're going to create instances of the sequel Lite 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 sequel 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 also when we create tables we will have another function just like you would expect in a sequel statement well 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 aged 7 and he is an active customer and 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 sequel Lite database continue on we're gonna show you how to do it step by step hi let's create an application and Android studio that uses the sequel Lite database to save our data on a local database [Music] you 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 sequel 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 do all the buttons in the ListView 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 layouts 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 ListView 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 three five three I'm going to start a new project let's choose empty activity and next let's name this thing sequel demo I think I have to name mine as version three 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 up 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 under 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 nay 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 gonna 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 gonna 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 alright 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 eight 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 checkbox 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 ok 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 8 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 others 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 they 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 gonna 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 1 and so now you can see they balance out so they're kind of in the middle of the page alright so we got the buttons on the screen we got them centered now I need to change some the IDs so let's go into the edit text first of all this is called edit text for and I'm gonna change it to ET age and just set that and say yes I'm gonna change it now for the switch let's change him to be called SW for a prefix and they'll put any 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 gonna 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 alright the last thing I wanted to put in here is a list of 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 gonna work with a simple list view the one that you probably should be using instead of a list view is called the recycler view the recycler view is more efficient it runs faster let's do the constraints I'm gonna 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 ahead and call it L view and we'll call it customer lists 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 application [Music] you 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 agent 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 ages 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 parameterised 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 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 two 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 they put it in a list but two string will take all of these properties and put them together into a single string since the two string method is very common it's part of the menu that you can generate automatically so I'll right click in the text choose generate select two string 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 another layout and now we've had this class so you gotta 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 sequel 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 + return or alt enter then I want to put in the edit text we had two of them one for the Aged 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 ListView 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 sensible it's it's easier to follow if you do now I made a mistake already I promised you that these were a 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 uncreate 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 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 active customer 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 a 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 onclicklistener 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 alright it looks like the applications 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 view all on the layout some strange things happen I have no idea why it says that so I'm gonna 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 3 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 Neath 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 let's go fix that one and the last one seems to be ok where it says boolean boolean is what 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 gonna use the two string 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 gonna put in a customer name I'm gonna 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 ad so you can see it's generating Howard age 34 and active is false however what happens if I go into if I if I make a mistake here so for age I'm gonna put it the word I try to put in old I can't type how about if I leave it blank and then she was add my application just crashed so I'm suspecting that the integer parse int 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 log cat in the corner here and switch back to the terminal 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 4 input string blank the process died on line 39 so I click here 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 gonna 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 gonna 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 II 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 gonna say ever creating customer and let's run it again okay so the apps 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 debate database part so stick around and let's install a sequel Lite database and save these customers as we click the Add button hi welcome to another in the series of our sequel Lite 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] you [Music] so when it comes to managing a sequel light 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 in an external tool such as my sequel workbench or something similar but here in Android we can do it right from the code now there are several minute management tools that are available outside of this like sequel 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 sequel Lite library so there'll be two classes that we will be using here one is the sequel Lite database class which is the database itself so the second class is called the sequel Lite 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 sequel Lite we're going to be using the execute sequel statement and so we'll be writing sequel strings and then execute him through this method so here's an example we're going to create a table called customers table provide the call name's 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 a 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 sequel Lite open helper and sure enough Android suggested for me now sequel Lite 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 okay 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 on upgrade 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 sequel statement second method is going to be called whenever the version number of your database changes so let's say you have version 1 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 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 for 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 compile compatibility depends on how you look at it now we still have an Emer 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 a 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 sequel 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 reference to the application itself and will 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 1 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 used to help find your database the second is the name of the database is the string and then here it says a factory object used for crea cursors it says or no for the default so this value made me know 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 sequel Lite open helper I get that 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 D B so you can name any string you like for the file name now for the factory we read in the documentation that that can be no 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 and so we'll have to figure that out in a few minutes alright 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 sequel Lite database in an Android app and so in this video we're going to actually use some sequel statements to create the tables that are going to store the data you see on the screen here [Music] you 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 and 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 sequel statements that will generate the tables in the sequel Lite 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 sequel 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 sequel 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 sequel 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 sequel stuff if you don't know sequel very well then look at some previous tutorials that I've created on working with sequel 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 off 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 extract this as a constant and so constant it's going to say what is the constant name and let's choose okay now look what it did it swapped out using some quotation marks that was handy and concatenated 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 refactor and choose extract as a constant so now I'm going to put attached 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 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 name so that this quotation mark and a space have a gap it doesn't need to have spaces here and 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 onclicklistener is where I'm going to go all right 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 lower case 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 so they 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 and 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 are trying to send is a reference to the method called unclick listener 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 main activity dot this that's the correct this that we're talking about and so we'll go with that one with the main activity dot 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 sequel 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 add one 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 will create is called sequel Lite 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 sequel Lite 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 hash map in other languages I can take pairs of values and associate with them this also looks like the intent in Android studio and 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 customers 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 well 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 we're 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 the third which is the content values well two out of these three I can great 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 no 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 sequel Lite database is the Android Docs that I'm looking at and let's do a ctrl F on this page and I'm typing in null column so it looks like I'm coming into one of twenty 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 to do aha here we go we have some explanation string it is optional it may be no sequel doesn't allow inserting of a completely empty row without naming at least one column it goes on to say if you're 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 no 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 no I'm going to put in no 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 it 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 that was excessive excessively 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 we'll 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 1 a name as error an age of zero and as 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 put let's call its 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 put in Mary age is 52 active customer and add 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'll 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 socom 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 etu and Schad Schlueter 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 sequel Lite and this one's called sequel Lite studio this is kind of like my sequel workbench tool now the one that I like to use is called DB browser for sequel light here's their website it's called sequel light browser org it is very simple to use sequel light 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 her here now what I'm looking for is customer DB 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 they're active customers are both set to 0 so remember in sequel Lite 0 is a boolean false and 1 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 sequel Lite 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 sequel Lite demo we're creating an Android app that will save data to a local database and then you can view it on a ListView 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 a item to the database but now we're going to display it [Music] all right thanks for sticking around we're gonna 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 that 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 ArrayList 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 sequel 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 sequel Lite 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 readable database 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 a dated 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 sequel or something called a raw query now we're gonna 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 our cursor in sequel light is the result set so just like in other database languages when you ask for a query you get back something called results 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 went 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 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 one 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 one we'll do the same thing for the Aged 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 three I'm expecting a boolean now it doesn't even give me that option why not it's because in sequel Lite there are no such things as boolean x' 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 geeks for Greek geeks for geeks 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 1 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 ternary 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 so I'm 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's sake I'm gonna leave empty in this else section now one thing that you have to remember in sequel light is to always clean up after yourself so you've opened up a connection to the database let's close it so other people can use it so we're gonna close the cursor with a closed statement and we'll close the database with a closed statement - 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 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 good Android Docs and we see here sequel light database and let's do a ctrl 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 Rock query returns a cursor well that's good to know it says we're expecting a sequel string so a sequel 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 were replaced 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 our 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 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 gonna replace the string with every one 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 gonna run this okay so I got the app up and running now before I click an ad I'm just gonna 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 while 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 ok 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 Kim's 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 welcome back we're at part 6 or 7 by now on this sequel 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 alright so here's the current state of our app right now when I click view all I get this ugly toast message it told 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 a database but it's very related what we need is called something called an array adapter so let's create an array adapter here and let's just call it the customer array adapter so to create the array adapter 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 I'll put that in the the angle brackets and then the first parameter in our creation is called the context so mainactivity dot this is the context of our guy now the next part is a little odd we go to our android dot r dot layout dot and I'm choosing simple list item a1 which is a predefined adapter that just gives a string one one per line this is the simplest array adapter that you can make an Android and so if you want to see how to make more complex right 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 array adapter well we want to show everyone the the people that we just selected from the data now 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 well I had Richard and she was ad 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 5 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 array adapter so that it works with more than just one button first of all let's take the array adapter 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 add 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 hmm so everyone didn't get assigned automatically where did we create everyone well everyone was set right here in this adapter part so hmm we're gonna 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 not assign him anything right away but as soon as we launch the program we can take the database helper and give it give it a new definition so now instead of every one we can just come into here and type in database helper dot get every one and that seems to work now let's come back down to here and we did split up these two into 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 array adapter 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 the 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 data come up let's put in somebody new let's put in que and choose ad says there's a success well let's keep 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 would 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 I welcome to another part of our application on sequel 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 [Music] you 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 sequel Lite creating these methods such as delete one and get every one 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 sequel Lite will have you do this code right in the main activity so you can do it the main activity and they've actually designed sequel Lite to work that way but as far as best practices go is 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 sequel database and we want to get an instance of a writable database and this one's writable because we're going to delete from it so now you rely on your sequel 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 hid 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 customers 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 lower case 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 the 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 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 it seems a little strange are the results 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 a true now let's come back into our main activity and see if we can implement this delete one 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 item click listener is not the same as a click listener item click listener 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 ListView 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 clicked customers so what I need to type in is the parent so the parent of the listener is the ListView 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 have an 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 could do it manually or let's see if they can do it for me 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 - 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 delete one and we'll send it the clicked customer and then we'll also update the ListView so we'll use this same function that was created a while ago called show customers on ListView so that should work lastly it would be a courtesy to tell the user that since you clicked the list we just deleted akai 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 I forgot you need to have the database helper sent along as a parameter so we'll put that in the parenthesis 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 gonna 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 2 1 2 3 let's delete some of the even ones so there's a 4 a 6 & 8 a 10 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 add her 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 where 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 channel because the videos have gone a long time now so just to remind you don't subscribe to this channel there's way too many people that are listening I want to make sure that this remains secret so don't subscribe don't click the bell don't watch the next video this is it stop here and we'll see you later

Channel: shad sluiter

Views: 135,306

Rating: 4.9551506 out of 5

Keywords: android studio, sqlite database, sqlite android studio, sqlite android, sqlite android tutorial, android database app, android database tutorial, android database, sqlite example, sqlite example in android studio, SQLiteOpenHelper, onCreate, onUpgrade, SQLiteDatabase, contentvalues, put, getint, execSQL, cursor, moveToFirst, moveToNext, cursor.close, crud, select, insert, drop, sql queries

Id: hDSVInZ2JCs

Length: 87min 7sec (5227 seconds)

Published: Wed Mar 04 2020