DZone
Mobile Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Mobile Zone > SQLite DB Usage Example for Android

SQLite DB Usage Example for Android

Sai Geetha M N user avatar by
Sai Geetha M N
·
Oct. 19, 11 · Mobile Zone · Interview
Like (0)
Save
Tweet
29.74K Views

Join the DZone community and get the full member experience.

Join For Free
This tutorial will give you a working example to get you started with using SQLite as a storage method for Android applications.  This is part 12 in Sai Geetha's Android Developer Tutorials series.

There are 4 ways of storing data on the android platform:


  • 1.    Preferences
  • 2.    SQLite Database
  • 3.    Files
  • 4.    Network

A word about each of them here and then I will move on to an example that shows how to work with SQLite DB that comes along with the android platform.


Preferences –
Basically used for storing user preferences for a single application or across applications for a mobile. This is typically name-value pairs accessible to the context.


Databases –
Android supports creating of databases based on SQLite db. Each database is private to the applications that creates it

Files –
Files can be directly stored on the mobile or on to an extended storage medium. By default other applications cannot access it.


Network –
Data can be stored and retrieved from the network too depending on the availability.


If an application wants to store and retrieve data for its own use, without having to share the data across applications, it can access the SQLite DB directly. There is no need of a content provider. We have seen in an earlier post how to use content providers.


In this example, we will do the following:
1.    Create a database (typically a one time activity)
2.    Create a table (typically a one time activity)
3.    Insert values into the table
4.    Retrieve the values from the table
5.    Display the retrieved values as a List view
6.    Delete all the records from the table before closing the connection to the database


Step 1: Create a database:


   sampleDB =  this.openOrCreateDatabase(SAMPLE_DB_NAME, MODE_PRIVATE, null);

This opens a database defined in the constant SAMPLE_DB_NAME, if it already exists. Else it creates a database and opens it. The second parameter is operating mode : MODE_PRIVATE meaning it is accessible to only this context. The other modes are and MODE_WORLD_WRITABLE. MODE_WORLD_READABLE


Step 2: Create a Table:


sampleDB.execSQL("CREATE TABLE IF NOT EXISTS " +
                        SAMPLE_TABLE_NAME +
                        " (LastName VARCHAR, FirstName VARCHAR," +
                        " Country VARCHAR, Age INT(3));");


Step 3: Insert values into the table:



sampleDB.execSQL("INSERT INTO " +
                        SAMPLE_TABLE_NAME +
                        " Values ('Makam','Sai Geetha','India',25);");


Step 4: Retrieve values


Cursor c = sampleDB.rawQuery("SELECT FirstName, Age FROM " +
                        SAMPLE_TABLE_NAME +
                        " where Age > 10 LIMIT 5", null);
           
      if (c != null ) {
            if  (c.moveToFirst()) {
                  do {
String firstName = c.getString(c.getColumnIndex("FirstName"));
                  int age = c.getInt(c.getColumnIndex("Age"));
                  results.add("" + firstName + ",Age: " + age);
                  }while (c.moveToNext());
            }
       }


Step 5: Display the values as a list
           
       this.setListAdapter(new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,results));


The statement displays it as a list as the class extends a ListActivity.


Step 6: Delete the values from the table in the finally part of the try block


finally {
            if (sampleDB != null)
                  sampleDB.execSQL("DELETE FROM " + SAMPLE_TABLE_NAME);
                  sampleDB.close();
        }


It is as simple as this to work with the SQLite DB even in android. No different from a desktop application. However, there are various overloaded methods of query() provided by the SQLIteDatabase class which can be more optimally used instead of execSQL.


The complete code for this example is downloadable here.


Blog Source: http://saigeethamn.blogspot.com/2009/10/android-developer-tutorial-part-12.html
Android (robot) SQLite Database mobile app

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • What Are Microservices?
  • SQL vs. NoSQL: Pros and Cons
  • 12 Modern CSS Techniques For Older CSS Problems
  • 10 Programming Habits a Web Developer Should Embrace

Comments

Mobile Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo