Create an SQLite database

From the course: Android Development Essential Training: Local Data Storage with Java

Start my 1-month free trial
  • Course details

    Android developers who need to manage and present data have many tools available to them in the application framework of Android. This course covers a variety of data persistence techniques, including storing data in shared preferences—key-value pairs—in JSON-formatted text files, and in relational databases with SQLite. Join David Gassner as he demonstrates these data management techniques. He also explains how to use relevant design patterns to model data with Java classes, and how to present data to the user with the Android SDK's ListView and RecyclerView components.


    • Click here to view David Gassner’s instructor page

      David Gassner

      Managing Staff Instructor, LinkedIn Learning Technology Library at LinkedIn

      • David Gassner is the author of over 60 video-based technical training courses for software developers.

        David is a senior staff instructor who specializes in development platforms and programming languages including Android and Java. He currently creates training content for Android developers and content that covers multiple programming languages including Java, C#, Visual Basic, and Go. He was formerly the president and founder of Bardo Technical Services, an Adobe Solutions Network Training Provider. As an Adobe Certified Expert, he wrote courseware for Adobe and delivered extensive training on Flex, ColdFusion, Dreamweaver and Flash. He was the author of Wiley's Flex 3 Bible and Flash Builder 4 and Flex 4 Bible. He most recently designed and developed Audio Cues—an Android app for running sound in live performances—which is available in the Google Play store.

    Skills covered in this course

  • Welcome

    - [Instructor] The first step in working with SQL Lite in Android is to define your database and table structure and the best practice for this is to create a java class that extends a class named SQL Lite open helper. I'll describe how to do this in the project named create database. As with other previous projects, I have some sample data that I'll eventually import into the database, and the model class that hints at the structure for the database table. I'll start by creating a class for the database table, and I'm going to put this into a new package that I'll name database. The name of the class will be items table. This class will include a number of constants. Setting the name of the table, the names and types of its columns, and other critical information. This takes a bit of code. So I've created a gist that I can copy from. The short URL is I'll select all of this code and then copy and paste it over into my class. Now the name of the table can be pretty much anything you want it to be, and the names of the columns can be whatever you want as well. You actually declare the structure of the table in the SQL create statement. Which is this simple string constant, but it's important to match the datatypes of the columns to the types of your model fields. In my application I've created a model class named data item, and it has fields named item ID, item name, and so on. It has five strings, an integer, and a double value, and in my SQL create statement, once again, I have five text fields. The first four and then the last one. One integer, and one real column. The real column matches the double type in Java. I also have a bi of code here to delete or drop the table from the database. I won't actually execute these lines of code here though. The execution of these SQL statements will go into the open helper class, and so now I'm ready to create that class. I'll name my class DD helper and I'll set its super class as SQL lite open helper from the package android.database.sqllite. I'll click okay, and that creates the class, and I immediately see a warning. I'll click into that line of code and use an intention action and choose implement methods. There are two abstract methods that you must override, named on create, and on upgrade. I'll add those to my code and I see that there's still an error indicator. So I'll follow that path again, and this time I'm being asked to create a constructor method. So I'll select that and then i'll choose the first version of the constructor and click okay, and the error indicators go away, and I'm ready to start adding some custom code. I'll need two constants that represent the name of the database file and the current version of my database structure. I'll add those here, I'll start with PSF for public static and final, and I'll press tab to complete that. Then I'll set the type as string, and I'll create a constant named DB file name, and I'll set it as nadias.db. Now as I mentioned previously, the .db extension is not required. It's a convenience and it will help you recognize the file easily if you go look for it no the file system of the device. I'll ad another constant in this will be an integer, and I'll set its name as db_version, and I'll set it to a value of one. The database version is managed by the framework. It must be an integer. Your first database structure can be number one, but if you need to change the database structure after you've released an application, each time you release a new version of the structure, you should increment this value by one. So the next version is two, the next three and so on. Now let's go to that constructor method. I generated it with four arguments, but that's more than I need. My constructor will only be called by my code. So I can decide how to structure it, and I'd like to simplify it. I'm going to just remove the last three arguments. The only think I'm going to save is the context, and then I'll replace the name and the version with my constants. DB file name and DB version. I don't need a factory object so I can pass in null for that. Next I'll customize my on create method. The first time the app opens and you open the SQL Lite open helper object, I'll call the on create method automatically and pass in a reference to the managed database file. It's up to you to actually execute the code to create the tables you need, and this is where that items table class becomes important. I already have a constant available named SQL create that has the required SQL statement. So to create that table, I'm just going to call DB.execSQL and I'll pass in items table.SQLcreate. If my database is going to have more than one table, I'll call the create statements from each of the tables in turn. And that's all I need to do in the on create method. Now the on upgrade method is slightly more complex and here's how it works. Let's say that you've released a version of the application and it has database version one. Then you restructure your database, you make some changes, and you increment the DB version to two. The first time the user opens the new version of the application, the on upgrade method will be called. Once again, you'll get a DB reference but this time you'll get the old version and the new version. It's up to you to write the custom code that's needed. Say to add columns to a table, but if you just want to wipe out the database and start over again, you can do this. First I'll call the xqdesqo method again, and this time I'll pass in items table.sql delete, and that's the SQL statement that drops the table from he database, and then I'll call the on create method. If you want to maintain data that's already in the database, you could perhaps export the current data, save to a JSON file, then drop the database, recreate it, and then re import the data, and have that all happen in a single method, but again, for our purposes, simply wiping out the database structure and creating it from scratch will be good enough. Now I'll add some code to my main activity to use this code that I've just created. In the main activity class, I'm going to create a reference to a SQL Lite database object, and I'll name it database. I won't initialize it here. Instead I'll do it in the on create method. And I'll place it right here after the call to set content view. I'll create an instance of my open helper object, and I'll cast it as its super class. SQL Lite open helper. This will allow me to call methods of the super class easily, but when I instantiate the object, I'll use my class, DB helper, and I'll pass in this as the context. Then to open the database, I'll call database equals, and then I'll call DB helper.get writable database. When you call the get writable database method, that will automatically call that on create method if the database hasn't been created previously. That will result in executing the required SQL and the database will have been created. I'll add a toast method so I can see that this happened successfully, and the message will just be database acquired. Now I don't know if it's actually just been created, because in the main activity, all I'm saying is give me the database reference. All of the logic that decides whether to create the table or not is all a part of the helper class, and again, it's all managed by the framework. So now I'll run the application. I see the message database acquired, and that tells me that everything worked successfully, but to verify it, as I've done before, I'll go to the Android device monitor, and I'll look at my virtual devices file system. I'll go to the internal storage area under data-data. Then I'll scroll down to my applications ID. That's, and I see a new databases directory has been created and there are tow files. Nadias.db and nadias.db-journal. You don't need to worry too much about the journal, but if you want to examine the database, you can pull it from the storage and look at it on your hard disK, and I'll show you how to do that in a later video.

  • Practice while you learn with exercise files

    Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.

    Download the exercise files for this course. Get started with a free trial today.

  • Download courses and learn on the go

    Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.

    Watch this course anytime, anywhere. Get started with a free trial today.