SQLite Database for Android - Full Course

Hi, we're about ready to start on a tutorial that will show you how to use SQLite, which is Android's database system used for local [Music] storage. My name is Chad Slutter; I'm a professor of computer science and software development at Grand Canyon University in Phoenix. I'm really glad to be here at freeCodeCamp and provide another resource for you. I endorse freeCodeCamp; 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 freeCodeCamp 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 SQLite. 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. 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 an online service. 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 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 autocomplete—you know, when you type in a search term and their suggestions are 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 MySQL 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 SQLite library and 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 getWritableDatabase. 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 openOrCreateDatabase; 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 where 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 ContentValues. So ContentValues 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 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 ContentValues 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 [Music].

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 do 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 EditTexts, 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 3.5.3. 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 AndroidX 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_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 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. 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 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 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 button1, 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—the IDs, so let's go into the EditText first of all; this is called editText4, and I'm going to change it to eth 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. The other button, let's change from button2 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 ListView, so I'm going to use the legacy items and call ListView. Now ListView is considered legacy because there's a new improved version of a ListView; this one's a lot simpler to work with. So for our simple tutorial, we're going to work with a simple ListView. The one that you probably should be using instead of a ListView is called this 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 SQLite 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].

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 MainActivity 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 CustomerModel. 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 isActive, 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 means 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 setters.

There's one more thing I'd like to add: the toString method. The toString 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 toString 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 toString 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. You got to wait for the next video 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 SQLite 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 MainActivity.

So when you start an application, MainActivity is rather empty, and so now we have to start telling it what the buttons are and who's going to be clicking. 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. First of all, there were two buttons: there was buttonAdd and buttonViewAll. 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 EditText; 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 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 sensical; 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 onCreate 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 onCreate 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 findViewById 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. 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 switchActive, 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 are no references outside of the file that you're looking at right here. So an onClick 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 a 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 MainActivity 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 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 CustomerModel 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 -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 etName, 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 etAge 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 isChecked, so I'm going to select isChecked 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 last one seems to be okay where it says boolean. Boolean is what the switch generates 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 I, Integer.dot, and there is a whole bunch of methods for managing integers; the one that we're looking for is parseInt, and you can see that the parameter that expects is a string, so this one will work fine. So we will parseInt, 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 customerModel, 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 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.parseInt 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 "Error 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 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 an 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 an 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 in 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 SQLiteDatabase class, which is the database itself. So the second class is called the SQLiteOpenHelper, 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 getAllCustomers, 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, in this video, on the open create database. 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 openOrCreateDatabase 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 executeSql 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 CustomerTable, 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# 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 DatabaseHelper. 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 SQLiteOpenHelper, and sure enough, Android suggested for me. Now SQLiteOpenHelper 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 onCreate, and then we're also going to have to implement the method called onUpgrade.

You must start with the understanding that the onCreate 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 an 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 onUpgrade method. And so onUpgrade 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 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 a from a parent class, and so we have to implement a constructor that will satisfy its parameters. So I'm going to switch over now to the documentation at Android and look at SQLiteOpenHelper. 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. You can see that it requires that we provide a context, so the context is 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 cursorFactory; 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 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 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 SQLiteOpenHelper, 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. 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 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, and 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 onUpgrade 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 onCreate 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 createTableStatement, 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.executeSql 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_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 refactor 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 columnCustomerName, 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 buttonAddOnClickListener 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 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, to the best of my ability, what context is, uh, however, you look at this one and it says the context that you're trying to send is a reference to the uh 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 SQLiteDatabase db equals, and then I'm going to get it from this class, so the database helper class, and I'm going to choose getWritableDatabase. Now, where did this come from? Did I code this anywhere? No, it came from the default properties that are available to the SQLiteOpenHelper, 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 getWritableDatabase option and getReadable option. So if I'm going to insert data, that means I'm writing to it. Next, I'm going to create an object called ContentValues, or I'm going to shorten it to the letters cv. Now, ContentValues is a special class, and it works like an 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 columnCustomerAge; 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 getAge. We'll associate these two together and put it into the ContentValues object. Lastly, we'll get the isActive 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 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 I can guess. So the first one is the table name; I have something called customerTable, so let's put in capital customer and choose customerTable. The second is called the nullColumnHack; 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 nullColumnHack? I don't know about you, but nullColumnHack sounds like it was designed wrong. Why are they hacking something? Let's go find out what they mean. So SQLiteDatabase 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 nullColumnHack 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 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 add1 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 createPerson 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 add1. 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 thingsSuccess. 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 will let us look at all the files on 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 .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.shad.sluter; 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 an 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 SQLite, and this one's called SQLite Studio. This is kind of like the MySQL 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 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 their active customers are both set to zero. So remember, in SQLite, 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 SQLite 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 [Music]. 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 getEveryone or selectAll or getAll. 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 an item called returnList; 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 * from the users table and the customers table really. Notice I'm using the defined constant called customerTable. So now I'm going to make a reference to the database that's active, so we'll say the SQLiteDatabase db equals, and then I'm going to type this dot, and the first item that I choose is called getWritableDatabase. 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 getWritableDatabase. What I could do is just type in getReadableDatabase. 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 getReadableDatabase, even though getWritableDatabase in this case would probably work. Next, I'm going to actually execute this query, so I'm going to type in db. and we have different choices; we could do executeSql or something called the rawQuery. Now we're going to choose rawQuery 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 rawQuery, and then for the properties, let's put in the queryString 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 a result or something similar to a 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. and what is the method that I'm going to choose? You would think that it would be like a method called size or return or success. What you want in this case is called moveToFirst, 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 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 an 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 getInt. 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 customerName and snatch it from the database at position 1. We'll do the same thing for the age. Now when I go to get the boolean variable for my isActive or not, I'm going to have a little problem, as you can see. I'm typing in boolean customerActive 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 SQLite 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 getInt 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 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 GeeksforGeeks. 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 expression1 then variable equals 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 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 isActive. 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 um, 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 SQLiteDatabase, and let's do a control-f and type in the word raw. Let's see rawQuery shows up on the page, and what are we supposed to do with rawQuery? So rawQuery returns a cursor; well, that's good to know. It says we're expecting a SQL string, so a SQL statement and something called selectionArgs. So what is selectionArgs? It says you may include question marks in the clause of the query, which will replace by values from selectionArgs. 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 selectionArgs are none, okay, or no.

The viewAll 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 buttonAdd, 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 CustomerModel, and we can capture this from the database helper with a method called getEveryone. 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 .toString 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 on add, I'm just going to choose the viewAll 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 moveToFirst. 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 moveToNext; 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 viewAll 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 viewAll, and the list is getting bigger. So it looks to me like we've got ourselves the database receiving information, and the viewAll 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 viewAll, 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 viewAll, 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 viewAll 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 customerArrayAdapter. 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 CustomerModel, so I'll put that in the angle brackets, and then the first parameter in our creation is called the context, so MainActivity.this is the context of our guy. Now the next part is a little odd; we go to android.R.layout., and I'm choosing simple_list_item_1, which is a predefined adapter that just gives a string one per line. This is the simplest ArrayAdapter that you can make in Android, and so if you want to see how to make more complex ArrayAdapters, 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 people that we just selected from the database. Now the next item should be to associate this ArrayAdapter with the actual control on the screen, and its name is lv_customers, and we should be able to find the item called a customer list. Then we put inside here the ArrayAdapter that we just made. So let's check it out to see what happens. Now when I click viewAll, 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 viewAll 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 viewAll 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 viewAll 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 customerArrayAdapter 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 onCreate method? So the onCreate 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 not assign him anything right away, but as soon as we launch the program, we can take the database helper and give it uh give it a new definition. So now instead of everyone, we can just come into here and type in databaseHelper.getEveryone, 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 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 ArrayAdapter 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 showCustomersOnListView 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 databaseHelper 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; 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 viewAll, 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 SQLite. 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] 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 CustomerModel 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 SQLite, creating these methods such as deleteOne and getEveryone and addOne. These are typical kind of database operations that you put into a DAO like I'm doing here. So a lot of tutorials in SQLite will have you do this code right in the main activity. So you can do it 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 CustomerModel; we want to delete anybody that has the same id number as his 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 deleteOne as an uppercase; let's make it as a lowercase 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 queryString 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 deleteOne 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 setOnItemClickListener. 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 `clickedCustomers`. So what I need to type in is the parent. The parent of the listener is the list view, and then there's a method that's looking at me in the face called `getItemAtPosition`. 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 to, 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 `deleteOne`, and we'll send it the `clickedCustomer`, and then we'll also update the list view. So we'll use the same function that was created a while ago called `showCustomersOnListView`. 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 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 EditText 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 you 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.