Pages

Monday, 12 March 2012

SQLite

Introduction:-
                      SQLite is a popular choice for local/client storage on web browsers. It has many bindings to programming languages.SQLite is an Open Source Database which is embedded into Android. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition it requires only little memory at runtime (approx. 250 KByte).

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.

Android Architecture :-
 1.SQLiteOpenHelper:-
                                      SqLiteOpenHelper are use to database creation and managing database version.
In SQLiteOpenHelper you implement onCreate(SQLiteDatabase), onUpgrade(SQLiteDatabase, int, int) and and optionally onOpen(SQLiteDatabase) method.

-onCreate(SQLiteDatabase db):-
                                            Called when the database is created for the first time.
                                 @Override
                                  public void onCreate(SQLiteDatabase db)
                                 {
                                             db.execSQL(DATABASE_CREATE);
                                  }

- onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion):-
                                             Called when the database needs to be upgraded.
                                    @Override
                                     public void onUpgrade(SQLiteDatabase db, int oldVersion,  int newVersion)
                                     {
                                             Log.w(TAG, "Upgrading database from version " + oldVersion
                                              + " to "
                                              + newVersion + ", which will destroy all old data");
                                               db.execSQL("DROP TABLE IF EXISTS titles");
                                              onCreate(db);
                                     }

- onOpen(SQLiteDatabase db):-
                                            Called when the database has been opened.
                                    
 2.SQLiteDatabase:-
                                      SQLiteDatabase is the base class for working with a SQLite database in Android. SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.

         3.Package:-
                           android.database.sqlite contain all related package of SQLite.

Creating Database:-
                                Following code show how to create database.Suppose it's a employee data including name and salary.                             
     private static final int DATABASE_VERSION = 1;
     private static final String DATABASE_CREATE =
                   "create table salary (_id integer primary key autoincrement, "
                   + "name text Not Null, salary text Not Null);";

Inserting Data in Database:-
                                           Using following function you can insert data into database.

                                public long insertsalary(String name, String salary)
                                {
                                               ContentValues initialValues = new ContentValues();
                                                initialValues.put(KEY_NAME, name);
                                                initialValues.put(KEY_SALARY, salary);
                                                return db.insert(DATABASE_TABLE, null, initialValues);
                                 }

Retrieving Data:- 
                     1. One Record:- Fetch one record at a time.
                                         public Cursor getdetails(String id) throws SQLException
                                        {
                                                        Cursor mCursor =
                                                                    db.query(true, DATABASE_TABLE, new String[]
                                                                     {
                                                                             KEY_ROWID,
                                                                              KEY_NAME,
                                                                             KEY_SALARY,
                                                                       },
                                                                     KEY_ROWID + "=" + id, null, null, null, null, null);
                                                       if (mCursor != null)
                                                      {
                                                                    mCursor.moveToFirst();
                                                       }
                                                      return mCursor;
                                       }

                 2. AllRecord:- Fetch all record..
                                   public Cursor getAllDetails()
                                  {
                                        return db.query(DATABASE_TABLE, new String[]
                                         {
                                                  KEY_ROWID,
                                                  KEY_NAME,
                                                  KEY_SALARY,
                                         },
                                             null,null,null,null,null);
                                   }
How to find Database:-
                                   SQLite database is located at DDMS. 
Select DDMS- Select FileBrowser\Data\Data\Your Package Name\Database\"database_Name.db".

SQLiteBrowser:-
                                 You find Database file but how to check the content of database whether insert correctly or not?. SQLite Browser is use to check the database content. You need to download it from following link.
Download SQlite Browser from here  

Delete One Record:-
                                 It's very easy to delete single record from database. You need only call the Delete() from your Activity and pass appropriate value(integer or string). You need to apply different query for integer and String.

                                1.Integer Parameter.
                                  public void delete(int value)
                                 {
                                        try{           
                                                   SQLiteDatabase db= this.getWritableDatabase();
                                                   db.delete(TABLE_NAME, COLUMN_ID+"="+value, null);

                                             }
                                             catch(Exception e){
                                             e.printStackTrace();
                                             }
                                  }    

        
                               2.String Parameter.
                                public void deleteBName(String keyword)
                               {
                                    try
                                    {
                                        SQLiteDatabase db=this.getWritableDatabase();
                          db.delete(TABLE_NAME,COLUMN1+"=?", new String [] {String.valueOf(keyword)});
                                    }
                                   catch(Exception e)
                                   {
                                           e.printStackTrace();
                                   }          
                             }

                   
Delete all Record:-
                               Following function delete all record.
                                public void deleteAll(){
                                try{           
                                       SQLiteDatabase db= this.getWritableDatabase();
                                       db.delete(TABLE_NAME, null, null);
                                     }catch(Exception e){
                                    e.printStackTrace();
                                     }
                               }


Thank you!

Save Tree Save Life.....

                                                              

1 comment:

  1. This comment has been removed by the author.

    ReplyDelete