Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Developer and personalized recommendations.Start Your Free Trial Now
- View Offline
- Exploring local data storage options
- Creating an Android virtual device
- Starting a new project
- Defining preferences with Java and activities
- Creating and reading JSON and XML data files
- Creating a new SQLite database
- Inserting and retrieving data in the database
Skill Level Intermediate
Once you've created your Open Helper and Data Source classes, you can start adding code to manage your database, including code to insert, update, and delete rows, and to read data from the database tables. I'll start with inserting data in this project. I've opened the class ToursDataSource.java, and I'll add a new method to the class that I'll name create. I'll place this at the end of the existing methods. It will be public so that it can be called from anywhere in the application and it will return an instance of my tour class which is in my model package.
I'll make sure to add the import statement. The name of the method is create, and it will receive a single argument, also data type as Tour and named tour. In order to pass data into the database, you could execute an explicit SQL statement, but then it's up to you to handle all sorts of coding, including escaping special characters, using single quotes where you shouldn't be using double quotes, and so on. Instead, you want to package your values into an instance of a class called ContentValues and then call a method of the database object called insert.
That will result in creating a well-formed SQL insert statement, and you don't have to do the hard work yourself. So I'll start by creating an instance of ContentValues. I'll type in the name of the class and then press Ctrl+Space to add the import statement for the class. I'll name this object values, and I'll instantiate it with the class constructor method. The ContentValues class implements the map interface.
So you'll be putting items into the map where the key is the name of the column and the value is the value you want to insert. I'll start with the title of the tour. I'll call values.put. For the key, I'll use a constant that's a part of my open helper class. I'll use ToursDBOpenHelper and then I'll use the COLUMN_TITLE constant. By using the constant, I'm making sure that the name of the column that I'm using here matches exactly what's really in the database.
Then I'll get the value from the tour argument that was passed into the method calling tour.getTitle. So, that code doesn't generate any errors, and I'll duplicate it a few times for the other values. I'll add three new versions, and then I'll change the names of the columns and the values. The next item will be the description represented by the constant COLUMN_DESC, and I'll change the method I'm calling to get the value from getTitle to getDescription, and I'll make the same changes for the price.
There's the column name and there's the value and for the image. Notice that I'm not putting a value in for the ID. That's because for this table, the tours table, the key is an auto-incrementing integer value. It will be generated automatically. Now I'm ready to insert the row into the database. I'm going to call the method insert.
That's going to return a long value, which will be the new ID of the new row. So I'll create a new variable that our data type is long, and I'll call it insertid. I'll get it's value by calling the database objects insert method. Once again, I need to pass in some arguments. The first is a string which is the name of the table, and I'll again use a constant for my Open Helper class. I'll call toursDBOpenHelper and I'll use the constant TABLE_TOURS.
The next argument is named nullColumnHack. This is a value that you can pass in if for some reason you're being forced to pass in a content values object that doesn't have anything. In SQLite, you always have to insert at least one column, and in that case, you could pass in the name of the column here. I know that's not a problem for this operation, so I'll just pass in the value of null and that will work fine. Then the third argument is my content values object that contains the values that I've placed in it.
So, now I've inserted a row into the database table, and I've received back the automatically assigned primary key value. I'll take that value and assign it back to the tour object that I received as an argument. Calling tour.setId and I'll pass an insertid, but I get an error. Here's what's going on. When I defined my tour class, I set the data type of the primary key as an integer, but this method is returning a long value, and it turns out that when you're defining a model class--that is a class that represents a single data entity-- and you want to represent an integer value to match up with the Android API, you should always data type that property in the class as a long and not an int.
So I'm going to go back to that class, the tour class. I'll go back to my Package Explorer, to my model package, and I'll open Tour.java, and I'll change the data type of my ID property in three places, in the declaration here, and then in the getId getter, and I'll change that to long, and I'll also make the change in the setter. I'll save those changes, I'll come back to the data source class, and I'll see the error on this line of code has gone away, because now the data types are compatible.
So I've inserted the new row and I've gotten back the automatically assigned primary key and I've assigned it to the tour object and now all I have to do is return the object with return tour and that's my create method. I receive a tour object, I pass it into the database, I update the tour object, and I return it. Now let's exercise this code. I'll go to my MainActivity class. Eventually, I'm going to be adding a whole bunch of data into the database, but for the moment, I want to keep it simple.
I'm just going to add three tours into the database table. So I'm going to create a new method which will only be called from within this activity. I'll call it createData, and then I'll make sure that it's returning void. Within the new method, I'll create a new instance of my Tour class. I'll set the data type as Tour, and the name is tour in all lowercase, and I'll instantiate it with the null arguments constructor method.
Next, I'll set some values. I'll set the title to Salton Sea, then I'll set the description to a tour to Salton Sea. I'll set the price to a value of 600, and I'll set the image property to salton_sea, all lowercase. Next, I'll pass the data into the database by calling the data source object that I've already opened.
I'll call datasource.create and I'll pass in the tour object, and then to find out what happened--that is what the new primary key value is--I'll say tour = datasource.create(tour) then I'll do a little bit of logging. I'll call my Log class and I'll use the I method. I'll pass in my LOGTAG, and I'll output a message of Tour created with id and I'll append to that tour.getId.
So, that's all the code you need to create a single row. I'll copy that code and then I'll paste it in. For the second version, I'll take away the tour declaration. It's already declared. I'll set the title to Death Valley. I'll copy that string and include it in the description. I'll update the price and the image and then I'll duplicate this code one more time and for the third one, I'll use San Francisco.
I'll update the description and the price again and the image. My last step is to call this method from my onCreate method. So I'll scroll up to the top. Now just in case the data source hasn't been opened yet, I'll call datasource.open and then createData. And remember, it's okay to call the open method more than once. The database connection is cached and you won't be over-working the database.
And now I'm ready to test. I'll run the application in the emulator. As the application opens, it executes the createData method, and now let's take a look at what happened in LogCat. I'll expand LogCat to full screen and I see that my three tours were created with IDs of one, two, and three. So, that's the code you need to create new rows in your database table. The code goes into the data source class and not into your main application code.
You create a content values object, you populate it with each of the values that you want to insert into the database row. You call the insert method. If you're working with an auto- incrementing field, you'll get back a long value as the new primary key and then you can do whatever you need to do with the data to retrieve and display it later on.