Tag Archives: sqlite

sqlite_feature

Howdy all, today I am going to show you how to CRUD(Create ,Read,Update,Delete) in Android Application in a simplest way, this is very awesome tutorial and simple one. Every one specially for novice who think that Database is very difficult.So without wasting time lets dig inside-

Sqlite is a RDBMS contained in a C programming library functions.
Download Code

So Lets start with basics and database queries –

Create database – Database Name- StudentRecordsDB

 db=openOrCreateDatabase("StudentRecordsDB", Context.MODE_PRIVATE, null);

Create Table -Table Name student . With column names.

db.execSQL("CREATE TABLE IF NOT EXISTS student(rollno VARCHAR,name VARCHAR,marks VARCHAR);");

Insert Data-

execSQL("INSERT INTO student VALUES('"+editstu_Rollno.getText()+"','"+stu_editName.getText()+
    				   "','"+stu_editMarks.getText()+"');");

Update Data-

execSQL("UPDATE student SET name='"+stu_editName.getText()+"',marks='"+stu_editMarks.getText()+
    					"' WHERE rollno='"+editstu_Rollno.getText()+"'");

Select Data-

rawQuery("SELECT * FROM student WHERE rollno='"+editstu_Rollno.getText()+"'", null);

Delete Data-

execSQL("DELETE FROM student WHERE rollno='"+editstu_Rollno.getText()+"'");

Note – So these are the basic queries for database and whenever we have to insert,update,delete we will use exeSQL . And in case of Select we will use rawQUERY.

One more thing if we have to check that our database is really created or not then we can check it by going to –

Goto DDMS ==> File explorer ==> data ==> data ==>package name ==>database name

databasesqlite

Now quickly start with designing part in Android Application –

<?xml version="1.0" encoding="UTF-8"?>

<AbsoluteLayout 
    android:layout_height="fill_parent" 
    android:layout_width="fill_parent"
     android:stretchColumns="0" 
     android:id="@+id/myLayout"
      xmlns:android="http://schemas.android.com/apk/res/android"
      android:background="#eb543b">

<TextView
     android:layout_height="wrap_content"
      android:layout_width="wrap_content"
       android:layout_y="10dp"
        android:layout_x="110dp"
         android:text="@string/title"/>

<TextView
     android:layout_height="wrap_content"
      android:layout_width="wrap_content"
       android:layout_y="50dp" 
       android:layout_x="30dp" 
       android:text="@string/roll_no"/>

<EditText android:layout_height="40dp" 
    android:layout_width="150dp" 
    android:id="@+id/editRollno" 
    android:layout_y="50dp" 
    android:layout_x="150dp" 
    android:inputType="number"/>

<TextView android:layout_height="wrap_content" 
    android:layout_width="wrap_content" 
    android:layout_y="100dp" 
    android:layout_x="30dp" 
    android:text="@string/name"/>

<EditText android:layout_height="40dp" 
    android:layout_width="150dp" 
    android:id="@+id/editName"
     android:layout_y="100dp"
      android:layout_x="150dp"
       android:inputType="text"/>

<TextView android:layout_height="wrap_content"
     android:layout_width="wrap_content"
      android:layout_y="150dp" 
      android:layout_x="30dp"
       android:text="@string/marks"/>

<EditText android:layout_height="40dp"
     android:layout_width="150dp" 
     android:id="@+id/editMarks"
      android:layout_y="150dp"
       android:layout_x="150dp"
        android:inputType="number"/>

<Button android:layout_height="40dp"
     android:layout_width="100dp"
      android:id="@+id/btnAdd" 
      android:layout_y="200dp"
       android:layout_x="30dp" 
       android:text="@string/add"/>

<Button android:layout_height="40dp"
     android:layout_width="100dp" 
     android:id="@+id/btnDelete" 
     android:layout_y="200dp" 
     android:layout_x="150dp"
      android:text="@string/delete"/>
<Button android:layout_height="40dp" 
    android:layout_width="100dp" 
    android:id="@+id/btnModify" 
    android:layout_y="250dp"
     android:layout_x="30dp" 
     android:text="@string/modify"/>

<Button android:layout_height="40dp" 
    android:layout_width="100dp"
     android:id="@+id/btnView" 
     android:layout_y="250dp" 
     android:layout_x="150dp" 
     android:text="@string/view"/>

<Button android:layout_height="40dp" 
    android:layout_width="100dp" 
    android:id="@+id/btnViewAll"
     android:layout_y="300dp" 
     android:layout_x="30dp" 
     android:text="@string/view_all"/>

<Button android:layout_height="40dp"
     android:layout_width="100dp"
      android:id="@+id/btnShowInfo"
       android:layout_y="300dp" 
       android:layout_x="150dp"
        android:text="@string/show_info"/>

</AbsoluteLayout>

Now it will show errors for Strings.xml file so paste the below code in your Strings.xml.

<?xml version="1.0" encoding="UTF-8"?>

<resources>

<string name="hello">Simple Sqlite!</string>

<string name="app_name">StudentDetailsApp</string>

<string name="title">Student Records</string>

<string name="roll_no">Student Rollno: </string>

<string name="name">Student Name: </string>

<string name="marks">Student Marks: </string>

<string name="add">Insert</string>

<string name="delete">Delete</string>

<string name="modify">Update</string>

<string name="view">View according to Rollno.</string>

<string name="view_all">View All Details</string>

<string name="show_info">About developer</string>

</resources>

Now your MainActivity.java file in your Android Application in which you will do CRUD operations .

Note – Cursor (Database) is control structure that enables traversal over a Record in the database.We can point to any row to Records in the database with its object.

Builder is a class for showing a alert to the user that something is happened. It has two String type parameters.

MainActivity.java is your java file and I put all the CRUD within this file-

package androidarena.simplesqlite;

import android.app.Activity;
import android.app.AlertDialog.Builder;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class MainActivity extends Activity implements OnClickListener
{
	EditText editstu_Rollno,stu_editName,stu_editMarks;
	Button btnAdd,btnDelete,btnUpdate,btnView,btnViewAll,btnShowInfo;
	SQLiteDatabase db;

    @Override
    public void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        editstu_Rollno=(EditText)findViewById(R.id.editRollno);
        stu_editName=(EditText)findViewById(R.id.editName);
        stu_editMarks=(EditText)findViewById(R.id.editMarks);
        btnAdd=(Button)findViewById(R.id.btnAdd);
        btnDelete=(Button)findViewById(R.id.btnDelete);
        btnUpdate=(Button)findViewById(R.id.btnModify);
        btnView=(Button)findViewById(R.id.btnView);
        btnViewAll=(Button)findViewById(R.id.btnViewAll);
        btnShowInfo=(Button)findViewById(R.id.btnShowInfo);
        //set onclick listener on button
        btnAdd.setOnClickListener(this);
        btnDelete.setOnClickListener(this);
        btnUpdate.setOnClickListener(this);
        btnView.setOnClickListener(this);
        btnViewAll.setOnClickListener(this);
        btnShowInfo.setOnClickListener(this);
        
        //creating database name StudentRecordsDB
        db=openOrCreateDatabase("StudentRecordsDB", Context.MODE_PRIVATE, null);
		db.execSQL("CREATE TABLE IF NOT EXISTS student(rollno VARCHAR,name VARCHAR,marks VARCHAR);");
    }
    public void onClick(View view)
    {
    	if(view==btnAdd)
    	{
    		//checking for blank fields
    		if(editstu_Rollno.getText().toString().trim().length()==0||
    				stu_editName.getText().toString().trim().length()==0||
    						stu_editMarks.getText().toString().trim().length()==0)
    		{
    			alert("Error", "Please enter all fields");
    			return;
    		}
    		// inserting values to DB.
    		db.execSQL("INSERT INTO student VALUES('"+editstu_Rollno.getText()+"','"+stu_editName.getText()+
    				   "','"+stu_editMarks.getText()+"');");
    		alert("Success", "Record added");
    		clearText();
    	}
    	if(view==btnDelete)
    	{
    		if(editstu_Rollno.getText().toString().trim().length()==0)
    		{
    			alert("Error", "Please enter Rollno");
    			return;
    		}
    		
    		//calling cursor object
    		Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editstu_Rollno.getText()+"'", null);
    		if(c.moveToFirst())
    		{
    			// delete data from table
    			db.execSQL("DELETE FROM student WHERE rollno='"+editstu_Rollno.getText()+"'");
    			alert("Success", "Record Deleted");
    		}
    		else
    		{
    			alert("Error", "Invalid Rollno");
    		}
    		clearText();
    	}
    	if(view==btnUpdate)
    	{
    		if(editstu_Rollno.getText().toString().trim().length()==0)
    		{
    			alert("Error", "Please enter Rollno");
    			return;
    		}
    		
    		Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editstu_Rollno.getText()+"'", null);
    		if(c.moveToFirst())
    		{
    			//update table
    			db.execSQL("UPDATE student SET name='"+stu_editName.getText()+"',marks='"+stu_editMarks.getText()+
    					"' WHERE rollno='"+editstu_Rollno.getText()+"'");
    			alert("Success", "Record Modified");
    		}
    		else
    		{
    			alert("Error", "Invalid Rollno");
    		}
    		clearText();
    	}
    	if(view==btnView)
    	{
    		if(editstu_Rollno.getText().toString().trim().length()==0)
    		{
    			alert("Error", "Please enter Rollno");
    			return;
    		}
    		
    		Cursor c=db.rawQuery("SELECT * FROM student WHERE rollno='"+editstu_Rollno.getText()+"'", null);
    		
    		if(c.moveToFirst())
    		{
    			//view with the help of cursor object 
    			stu_editName.setText(c.getString(1));
    			stu_editMarks.setText(c.getString(2));
    		}
    		else
    		{
    			alert("Error", "Invalid Rollno");
    			clearText();
    		}
    	}
    	if(view==btnViewAll)
    	{
    		Cursor c=db.rawQuery("SELECT * FROM student", null);
    		if(c.getCount()==0)
    		{
    			alert("Error", "No records found");
    			return;
    		}
    		StringBuffer buffer=new StringBuffer();
    		while(c.moveToNext())
    		{
    			//view all 
    			buffer.append("Rollno: "+c.getString(0)+"\n");
    			buffer.append("Name: "+c.getString(1)+"\n");
    			buffer.append("Marks: "+c.getString(2)+"\n\n");
    		}
    		alert("Student Details", buffer.toString());
    	}
    	if(view==btnShowInfo)
    	{
			alert("Simple Sqlite in Android", "Developed By AndroidArena.co.in");
    	}
    }
    public void alert(String title,String message)
    {
    	Builder builder=new Builder(this);
    	builder.setCancelable(true);
    	builder.setTitle(title);
    	builder.setMessage(message);
    	builder.show();
	}
    public void clearText()
    {
    	editstu_Rollno.setText("");
    	stu_editName.setText("");
    	stu_editMarks.setText("");
    	editstu_Rollno.requestFocus();
    }
}

In the above code I have made two methods for showing messages on Alertbox and other is ClearText for clearing text after performing Records.

In this my column names are –

Rollno

Name

Marks

With VARCHAR datatype – You can set PRIMARY KEY later on.

In this I have just create Database and create Table and then perform simple if else condition to perform SQLITE DATABASE CRUD.This is simple and minor Project based on Local Database connectivity for Student Management System in Android.

Now you can Run your project  So Practice Hard and learn as much you can  :p

OutPut –

sqlite_androidarena

sqlite_insert

sqlite_update

sqlite_view

Please share your view and ask me anything . :p