Insert, Update and Delete With Sqlite

Hello Guys,

Today I am going to explain about SQLite Database in android.

SQLite is a same like other database. Its specially used for mobile operating system.

SQLite is nothing but a realtional database which contains C programming language. It is used as an internal or local database for mobile os.

SQLite is mostly used in mobile due to Light Weight, Easy to use for storage, SQL Compatible, Serverless features.

If you want to see the database structure and its data into Eclipse then very first you have to download SQLite Manager.

Download SQLite Manager from here: SQLite Manager

After downloading go to your Eclipse folder like following.

E:\Software\eclipse\plugins

Put (Copy & Paste) your jar file into Plugins folder.

Now Restart your eclipse. You can see the SQLite browser on top right side of the File Explorer.

Sample Code:

Step 1: First you have to create a database.

SQLiteDatabase mydb= openOrCreateDatabase ("firstdb.db", SQLiteDatabase. CREATE_IF_NECESSARY, null);

Step 2: Create a table and its fields.

String CREATE_TABLE="CREATE TABLE IF NOT EXISTS student(id INTEGER PRIMARY KEY AUTOINCREMENT,fname TEXT,lname TEXT,city TEXT,phone INTEGER);";
       
mydb.execSQL(CREATE_TABLE);

Step 3: Insert Records

mydb.execSQL("insert into student(fname, lname, city, phone) values ('"+txtFnm.getText()+"', '"+txtLnm.getText()+"', '"+txtCity.getText()+"', '"+txtPhn.getText()+"');");

Step 4: Delete Records

final SQLiteDatabase mydb=openOrCreateDatabase("firstdb.db", SQLiteDatabase. CREATE_IF_NECESSARY, null);

Cursor cur = mydb.query("student",new String[] { "id" }, "id='" + txtId.getText()+ "'", null, null, null, null, null);

if (cur.getCount() > 0)
{
           mydb.delete("student", "id=?",new String[] { txtId.getText().toString() });
          Toast.makeText(getApplicationContext(),"Record removed successfully.", Toast.                                 LENGTH_SHORT) .show();

          startActivity(new Intent(getApplicationContext(),DatabaseActivity.class));
}
else
{
Toast.makeText(getApplicationContext(),"No such Record found.", Toast.                                           LENGTH_SHORT).show();
}

Step 5: Update Records

mydb.execSQL("update student set fname='"+txtFnm.getText()+"',lname='"+txtLnm.getText() +"',city='"+txtCity.getText()+"',phone='"+txtPhn.getText()+"' where id='"+txtId.getText()+"'");

Here I update First Name like following.

Now I click on Update button and record is updated.

Step 6: Display Records

mydb=openOrCreateDatabase("firstdb.db", SQLiteDatabase.OPEN_READWRITE,null);

Cursor cur=mydb.query("student", new String[]{"id","fname","lname", "city","phone"} ,null,null,null,null,null,null);

printData(cur);

public void printData(Cursor c)
{
StringBuffer str=new StringBuffer();
String rowResults=null;

str.append("\t\t\t*** Student Data Table *** \n" + " \n Results:" +c.getCount() + "\n\n                         Columns: " + c.getColumnCount()+"\n");

// Print column names
String rowHeaders = "\t  ";

for (int i = 0; i < c.getColumnCount(); i++)
{
rowHeaders = rowHeaders.concat(c.getColumnName(i).toUpperCase() + "\t  ");
}
str.append("\n  " + rowHeaders+"\n");

// Print records
c.moveToFirst();
        while (c.isAfterLast() == false)
        {
str.append(rowResults = "\t  ");

for (int i = 0; i < c.getColumnCount(); i++)
{
rowResults = rowResults.concat(c.getString(i) +"\t\t ");
}

str.append("\n"+rowResults);
c.moveToNext();
}
tv.setText(str);
}

Download Full Source Code: Database Example

Enjoy Coding...!!!