Posted by

SQ Lite Concepts

reference : http://www.vogella.com/articles/AndroidSQLite/article.html

Table of Contents
1. SQLite and Android
1.1. What is SQLite?
1.2. SQLite in Android
2. Prerequisites for this tutorial
3. SQLite Architecture
3.1. Packages
3.2. SQLiteOpenHelper
3.3. SQLiteDatabase
3.4. rawQuery() Example
3.5. query() Example
3.6. Cursor
3.7. ListViews, ListActivities and SimpleCursorAdapter
4. Tutorial: Using SQLite
4.1. Introduction to the project
4.2. Create Project
4.3. Database and Data Model
4.4. User Interface
4.5. Running the apps
5. ContentProvider and sharing data
5.1. ContentProvider Overview
5.2. Own ContentProvider
5.3. Security and ContentProvider
5.4. Thread Safety
6. Tutorial: Using ContentProvider
6.1. Overview
6.2. Create contacts on your emulator
6.3. Using the Contact Content Provider
7. Activities, Loader and ContentProvider
7.1. Activities and Databases
7.2. Loader
8. Tutorial: SQLite, own ContentProvider and Loader
8.1. Overview
8.2. Project
8.3. Database classes
8.4. Create ContentProvider
8.5. Resources
8.6. Layouts
8.7. Activities
8.8. Start your application
9. Accessing SQLite databases directly
9.1. Storage location of the SQLite database
9.2. Shell access to the database
10. More on ListViews
11. Get the Kindle edition
12. Questions and Discussion
13. Links and Literature
13.1. Source Code
13.2. Android SQLite resources
13.3. Android Resources
13.4. vogella Resources
 
 
 
1.1 SQ Lite 
 
  • SQLite is an Open Source Database which is embedded into Android
   
          It requires only little memory at runtime (approx. 250 KByte). 
 
SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before saving them in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa. 
 
More information about SQLite can be found on the SQLite website: http://www.sqlite.org. 
 

1.2. SQLite in Android

SQLite is available on every Android device. Using an SQLite database in Android does not require any database setup or administration.
You only have to define the SQL statements for creating and updating the database. Afterwards the database is automatically managed for you by the Android platform.
Access to an SQLite database involves accessing the filesystem. This can be slow. Therefore it is recommended to perform database operations asynchronously, for example inside the AsyncTask class.
If your application creates a database, this database is by default saved in the directory DATA/data/APP_NAME/databases/FILENAME.
The parts of the above directory are constructed based on the following rules. DATA is the path which the Environment.getDataDirectory() method returns. APP_NAME is your application name. FILENAME is the name you specify in your application code for the database.


2. Prerequisites for this tutorial

The following assumes that you have already basic knowledge in Android development.




3. SQLite Architecture

3.1. Packages

The package android.database contains all general classes for working with databases. android.database.sqlite contains the SQLite specific classes.

3.2. SQLiteOpenHelper

To create and upgrade a database in your Android application you usually subclass SQLiteOpenHelper. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.
In this class you need to override the onCreate() and onUpgrade() methods.
onCreate() is called by the framework, if the database does not exists.
onUpgrade() is called, if the database version is increased in your application code. This method allows you to update the database schema.
Both methods receive an SQLiteDatabase object as parameter which represents the database.
SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to an SQLiteDatabase object; either in read or write mode.
The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.
It is best practice to create a separate class per table. This class defines static onCreate() and onUpdate() methods. These methods are called in the corresponding methods of SQLiteOpenHelper. This way your implementation of SQLiteOpenHelper will stay readable, even if you have several tables.


3. SQLite Architecture

3.1. Packages

The package android.database contains all general classes for working with databases. android.database.sqlite contains the SQLite specific classes.

3.2. SQLiteOpenHelper

To create and upgrade a database in your Android application you usually subclass SQLiteOpenHelper. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.
In this class you need to override the onCreate() and onUpgrade() methods.
onCreate() is called by the framework, if the database does not exists.
onUpgrade() is called, if the database version is increased in your application code. This method allows you to update the database schema.
Both methods receive an SQLiteDatabase object as parameter which represents the database.
SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to an SQLiteDatabase object; either in read or write mode.
The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.
It is best practice to create a separate class per table. This class defines static onCreate() and onUpdate() methods. These methods are called in the corresponding methods of SQLiteOpenHelper. This way your implementation of SQLiteOpenHelper will stay readable, even if you have several tables.


3.3. SQLiteDatabase

SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.
More specifically SQLiteDatabase provides the insert(), update() and delete() methods.
In addition it provides the execSQL() method, which allows to execute an SQL statement directly.
The object ContentValues allows to define key/values. The "key" represents the table column identifier and the "value" represents the content for the table record in this column. ContentValues can be used for inserts and updates of database entries.
Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class .
rawQuery() directly accepts an SQL select statement as input.
query() provides a structured interface for specifying the SQL query.
SQLiteQueryBuilder is a convenience class that helps to build SQL queries.

3.4. rawQuery() Example

The following gives an example of a rawQuery() call. 

Cursor cursor = getReadableDatabase().
  rawQuery("select * from todo where _id = ?", new String[] { id });

3.5. query() Example

The following gives an example of a query() call. 

return database.query(DATABASE_TABLE, 
  new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION }, 
  null, null, null, null, null);

The method query() has the following parameters.

Table 1. Parameters of the query() method
Parameter Comment
String dbName The table name to compile the query against.
int[] columnNames A list of which table columns to return. Passing "null" will return all columns.
String whereClause Where-clause, i.e. filter for the selection of data, null will select all data.
String[] selectionArgs You may include ?s in the "whereClause"". These placeholders will get replaced by the values from the selectionArgs array.
String[] groupBy A filter declaring how to group rows, null will cause the rows to not be grouped.
String[] having Filter for the groups, null means no filter.
String[] orderBy Table columns which will be used to order the data, null means no ordering.


If a condition is not required you can pass null, e.g. for the group by clause.
The "whereClause" is specified without the word "where", for example a "where" statement might look like: "_id=19 and summary=?".
If you specify placeholder values in the where clause via ?, you pass them as the selectionArgs parameter to the query.





 
 

3.6. Cursor

A query returns a Cursor object. A Cursor represents the result of a query and basically points to one row of the query result. This way Android can buffer the query results efficiently; as it does not have to load all data into memory.
To get the number of elements of the resulting query use the getCount() method.
To move between individual data rows, you can use the moveToFirst() and moveToNext() methods. The isAfterLast() method allows to check if the end of the query result has been reached.
Cursor provides typed get*() methods, e.g. getLong(columnIndex), getString(columnIndex) to access the column data for the current position of the result. The "columnIndex" is the number of the column you are accessing.
Cursor also provides the getColumnIndexOrThrow(String) method which allows to get the column index for a column name of the table.
A Cursor needs to be closed with the close() method call.

3.7. ListViews, ListActivities and SimpleCursorAdapter

ListViews are Views which allow to display a list of elements.
ListActivities are specialized Activities which make the usage of ListViews easier.
To work with databases and ListViews you can use the SimpleCursorAdapter. The SimpleCursorAdapter allows to set a layout for each row of the ListViews.
You also define an array which contains the column names and another array which contains the IDs of Views which should be filled with the data.
The SimpleCursorAdapter class will map the columns to the Views based on the Cursor passed to it.
To obtain the Cursor you should use the Loader class.
 
 
 
 

4. Tutorial: Using SQLite

4.1. Introduction to the project

The following demonstrates how to work with an SQLite database. We will use a data access object (DAO) to manage the data for us. The DAO is responsible for handling the database connection and for accessing and modifying the data. It will also convert the database objects into real Java Objects, so that our user interface code does not have to deal with the persistence layer.
The resulting application will look like the following.


Using a DAO is not always the right approach. A DAO creates Java model objects; using a database directly or via a ContentProvider is typically more resource efficient as you can avoid the creation of model objects.
I still demonstrate the usage of the DAO in this example to have a relatively simple example to begin with. Use the latest version of Android 4.0. This is currently API Level 15. Otherwise I would have to introduce the Loader class, which should be used as of Android 3.0 for managing a database Cursor. And this class introduces additional complexity.

4.2. Create Project

Create the new Android project with the name de.vogella.android.sqlite.first and an Activity called TestDatabaseActivity.

4.3. Database and Data Model

Create the MySQLiteHelper class. This class is responsible for creating the database. The onUpdate() method will simply delete all existing data and re-create the table. It also defines several constants for the table name and the table columns. 

 
package de.vogella.android.sqlite.first;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {

  public static final String TABLE_COMMENTS = "comments";
  public static final String COLUMN_ID = "_id";
  public static final String COLUMN_COMMENT = "comment";

  private static final String DATABASE_NAME = "commments.db";
  private static final int DATABASE_VERSION = 1;

  // Database creation sql statement
  private static final String DATABASE_CREATE = "create table "
      + TABLE_COMMENTS + "(" + COLUMN_ID
      + " integer primary key autoincrement, " + COLUMN_COMMENT
      + " text not null);";

  public MySQLiteHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase database) {
    database.execSQL(DATABASE_CREATE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(MySQLiteHelper.class.getName(),
        "Upgrading database from version " + oldVersion + " to "
            + newVersion + ", which will destroy all old data");
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
    onCreate(db);
  }

}

Create the Comment class. This class is our model and contains the data we will save in the database and show in the user interface. 

 
package de.vogella.android.sqlite.first;

public class Comment {
  private long id;
  private String comment;

  public long getId() {
    return id;
  }

  public void setId(long id) {
    this.id = id;
  }

  public String getComment() {
    return comment;
  }

  public void setComment(String comment) {
    this.comment = comment;
  }

  // Will be used by the ArrayAdapter in the ListView
  @Override
  public String toString() {
    return comment;
  }
}

Create the CommentsDataSource class. This class is our DAO. It maintains the database connection and supports adding new comments and fetching all comments. 

package de.vogella.android.sqlite.first;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class CommentsDataSource {

  // Database fields
  private SQLiteDatabase database;
  private MySQLiteHelper dbHelper;
  private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
      MySQLiteHelper.COLUMN_COMMENT };

  public CommentsDataSource(Context context) {
    dbHelper = new MySQLiteHelper(context);
  }

  public void open() throws SQLException {
    database = dbHelper.getWritableDatabase();
  }

  public void close() {
    dbHelper.close();
  }

  public Comment createComment(String comment) {
    ContentValues values = new ContentValues();
    values.put(MySQLiteHelper.COLUMN_COMMENT, comment);
    long insertId = database.insert(MySQLiteHelper.TABLE_COMMENTS, null,
        values);
    Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
        allColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
        null, null, null);
    cursor.moveToFirst();
    Comment newComment = cursorToComment(cursor);
    cursor.close();
    return newComment;
  }

  public void deleteComment(Comment comment) {
    long id = comment.getId();
    System.out.println("Comment deleted with id: " + id);
    database.delete(MySQLiteHelper.TABLE_COMMENTS, MySQLiteHelper.COLUMN_ID
        + " = " + id, null);
  }

  public List<Comment> getAllComments() {
    List<Comment> comments = new ArrayList<Comment>();

    Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
        allColumns, null, null, null, null, null);

    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
      Comment comment = cursorToComment(cursor);
      comments.add(comment);
      cursor.moveToNext();
    }
    // Make sure to close the cursor
    cursor.close();
    return comments;
  }

  private Comment cursorToComment(Cursor cursor) {
    Comment comment = new Comment();
    comment.setId(cursor.getLong(0));
    comment.setComment(cursor.getString(1));
    return comment;
  }
}

4.4. User Interface

Change your main.xml layout file in the res/layout folder to the following. This layout has two buttons for adding and deleting comments and a ListView which will be used to display the existing comments. The comment text will be generated later in the Activity by a small random generator. 

 
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <LinearLayout
        android:id="@+id/group"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" >

        <Button
            android:id="@+id/add"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Add New" 
            android:onClick="onClick"/>

        <Button
            android:id="@+id/delete"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Delete First" 
            android:onClick="onClick"/>
        
    </LinearLayout>

    <ListView
        android:id="@android:id/list"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="@string/hello" />

</LinearLayout>

Change your TestDatabaseActivity class. to the following. We use here a ListActivity for displaying the data. 

 
 
package de.vogella.android.sqlite.first;

import java.util.List;
import java.util.Random;

import android.app.ListActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;

public class TestDatabaseActivity extends ListActivity {
  private CommentsDataSource datasource;

  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    datasource = new CommentsDataSource(this);
    datasource.open();

    List<Comment> values = datasource.getAllComments();

    // Use the SimpleCursorAdapter to show the
    // elements in a ListView
    ArrayAdapter<Comment> adapter = new ArrayAdapter<Comment>(this,
        android.R.layout.simple_list_item_1, values);
    setListAdapter(adapter);
  }

  // Will be called via the onClick attribute
  // of the buttons in main.xml
  public void onClick(View view) {
    @SuppressWarnings("unchecked")
    ArrayAdapter<Comment> adapter = (ArrayAdapter<Comment>) getListAdapter();
    Comment comment = null;
    switch (view.getId()) {
    case R.id.add:
      String[] comments = new String[] { "Cool", "Very nice", "Hate it" };
      int nextInt = new Random().nextInt(3);
      // Save the new comment to the database
      comment = datasource.createComment(comments[nextInt]);
      adapter.add(comment);
      break;
    case R.id.delete:
      if (getListAdapter().getCount() > 0) {
        comment = (Comment) getListAdapter().getItem(0);
        datasource.deleteComment(comment);
        adapter.remove(comment);
      }
      break;
    }
    adapter.notifyDataSetChanged();
  }

  @Override
  protected void onResume() {
    datasource.open();
    super.onResume();
  }

  @Override
  protected void onPause() {
    datasource.close();
    super.onPause();
  }

}



4.5. Running the apps

Install your application and use the Add and Delete button. Restart your application to validate that the data is still there.






You can learn more about sql statements as well in the link 
                       http://www.sqlcourse.com/




Email Post

Comments

Post a Comment

Popular Posts