Download Code

Android Database Tutorial

Android platform provides different ways to store user data like sqlite,web server database,cloud database etc.

Android SQLite Database Tutorial

In android sqlite database tutorial you will learn how to store user data using Android SQLite Database with Android SQLite Database operations.You will use ListView,TextView,EditText,Toast,Table Layout components.

  • Insert
  • Display
  • Update
  • Delete

Let me give you some basic information about SQLite and Android SQLite Database.

SQLite

  • SQLite is an in-process library
  • implements a self-contained, server less, zero-configuration, transactional SQL database engine.
  • The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.

For more detail visit SQLite official website.

Android SQLite Database

Exposes methods to manage a SQLitedatabase.

Android SQLite Database has methods to create, delete, execute SQL commands, and perform other common database management tasks.

SQLiteOpenHelper

A helper class to manage database creation and version management.

You create a subclass implementing onCreate(SQLite Database), onUpgrade(SQLiteDatabase, int, int) and optionally onOpen(SQLiteDatabase), and this class takes care of opening the database if it exists, creating it if it does not, and upgrading it as necessary. Transactions are used to make sure the database is always in a sensible state.

Let’s start create new android SQLite Database tutorial application project.Create layouts in android sqlite database tutorial with some components.

activity_main.xml

android sqlite database,sqlite,sqlite database,how to create sqlite database in android,sqlite android tutorial,sqlite database tutorial android,android sqlite database tutorial,

insert.xml

android sqlite database,sqlite,sqlite database,how to create sqlite database in android,sqlite android tutorial,sqlite database tutorial android,android sqlite database tutorial,

complete_data.xml

android sqlite database,sqlite,sqlite database,how to create sqlite database in android,sqlite android tutorial,sqlite database tutorial android,android sqlite database tutorial,

update.xml

android sqlite database,sqlite,sqlite database,how to create sqlite database in android,sqlite android tutorial,sqlite database tutorial android,android sqlite database tutorial,

delete.xml

android sqlite database,sqlite,sqlite database,how to create sqlite database in android,sqlite android tutorial,sqlite database tutorial android

slip_display.xml

android sqlite database,sqlite,sqlite database,how to create sqlite database in android,sqlite android tutorial,sqlite database tutorial android,android sqlite database tutorial,

You are done with layouts.Now first SQLite Helper class.

SQLiteHelper.java

package developer.codecsqlitedatabase;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteHelper extends SQLiteOpenHelper{
         // Android SQLite Database name
	private static final String DATABASE_NAME="EmployeeManagement.db";
	private static final String DATABASE_TABLE_NAME="Employees";
	public static final String EMPLOYEE_ID="id";
	public static final String EMPLOYEE_NAME="name";
	public static final String EMPLOYEE_EMAIL="email";
	public static final String EMPLOYEE_PHONE="phone";
	public static final String EMPLOYEE_DESIGNATION="designation";
	
	//constructor to create database
	
	public SQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, 1);
		// TODO Auto-generated constructor stub
	}

	// method to create table
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(
				"create table Employees " +
				"(id integer primary key, name text,email text,phone text,designation text)"
				);
	}

	// on upgrading the database
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS Employees");
	     onCreate(db);
	}

	// insert data
	
	public boolean insertEmployee (String name, String email, String phone, String designation)
	   {
	      SQLiteDatabase db = this.getWritableDatabase();
	      ContentValues contentValues = new ContentValues();
	      contentValues.put("name", name);
	      contentValues.put("phone", phone);
	      contentValues.put("email", email);	
	      contentValues.put("designation", designation);
	      
	      db.insert("Employees", null, contentValues);
	      return true;
	   }
	
	// get complete data/info
	
	public ArrayList getAllEmployeesData()
	   {
	      ArrayList array_list = new ArrayList();
	      
	      //hp = new HashMap();
	      SQLiteDatabase db = this.getReadableDatabase();
	      Cursor res =  db.rawQuery( "select * from Employees", null );
	      res.moveToFirst();
	      
	      while(res.isAfterLast() == false){
	         array_list.add(res.getString(res.getColumnIndex(EMPLOYEE_NAME)));
	         res.moveToNext();
	      }
	   return array_list;
	   }
	// get single entry
	public Cursor getSingleEntry(int id){
	      SQLiteDatabase db = this.getReadableDatabase();
	      Cursor res =  db.rawQuery( "select * from Employees where id="+id+"", null );
	      return res;
	   }
	// delete entry
	public Integer deleteEmployee (Integer id)
	   {
	      SQLiteDatabase db = this.getWritableDatabase();
	      return db.delete("Employees", 
	      "id = ? ", 
	      new String[] { Integer.toString(id) });
	   }
	// update entry
	public boolean updateEmployeeInfo (Integer id, String name, String email, String phone, String designation)
	   {
	      SQLiteDatabase db = this.getWritableDatabase();
	      ContentValues contentValues = new ContentValues();
	      contentValues.put("name", name);
	      contentValues.put("email", phone);
	      contentValues.put("phone", email);
	      contentValues.put("designation", designation);
	      
	      db.update("Employees", contentValues, "id = ? ", new String[] { Integer.toString(id) } );
	      return true;
	   }
	
	
}

MainActivity.java

package developer.codecsqlitedatabase;

import android.support.v7.app.ActionBarActivity;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;


public class MainActivity extends Activity implements OnClickListener{

	
	Button iBtn,vBtn,uBtn,dBtn,tBtn;
	
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        initialize();
    }
    
    public void initialize(){
    	iBtn = (Button) findViewById(R.id.insertBtn);
    	vBtn = (Button) findViewById(R.id.viewBtn);
    	uBtn = (Button) findViewById(R.id.updateBtn);
    	dBtn = (Button) findViewById(R.id.deleteBtn);
    	tBtn = (Button) findViewById(R.id.tableBtn);
    	iBtn.setOnClickListener(this);
    	vBtn.setOnClickListener(this);
    	uBtn.setOnClickListener(this);
    	dBtn.setOnClickListener(this);
    	tBtn.setOnClickListener(this);
    }

	@Override
	public void onClick(View v) {
		// TODO Auto-generated method stub
		switch(v.getId()){
		case R.id.insertBtn:
			Intent i = new Intent(getApplicationContext(),insertEmployee.class);
			startActivity(i);
			break;
		case R.id.viewBtn:
			Intent vw = new Intent(getApplicationContext(),showCompleteData.class);
			startActivity(vw);
			break;
		case R.id.updateBtn:
			Intent up = new Intent(getApplicationContext(),updateEmployee.class);
			startActivity(up);
			break;
		case R.id.deleteBtn:
			Intent d = new Intent(getApplicationContext(),deleteEmployee.class);
			startActivity(d);
			finish();
			break;
		case R.id.tableBtn:
			Intent t =  new Intent(getApplicationContext(),slipEmployeeView.class);
			startActivity(t);
			break;
		}
	}

   
}

insertEmployee.java

package developer.codecsqlitedatabase;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class insertEmployee extends Activity implements OnClickListener{

	EditText name,email,phone,designation;
	Button submitBtn;
	private SQLiteHelper dbHelper;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		setContentView(R.layout.insert);
		initialize();
		dbHelper = new SQLiteHelper(this);
	}
	public void initialize(){
		name = (EditText) findViewById(R.id.empName);
		email = (EditText) findViewById(R.id.empEmail);
		phone = (EditText) findViewById(R.id.empPhone);
		designation = (EditText) findViewById(R.id.empDesignation);
		submitBtn = (Button) findViewById(R.id.insertEmpBtn);
		submitBtn.setOnClickListener(this);
	}
	@Override
	public void onClick(View v) {
		// TODO Auto-generated method stub
		switch(v.getId()){
		case R.id.insertEmpBtn:
			dbHelper.insertEmployee(name.getText().toString(), email.getText().toString(), phone.getText().toString(), designation.getText().toString());
				Toast.makeText(getApplicationContext(), "Insertion Successful", Toast.LENGTH_SHORT).show();
			
			break;
		}
	}

}

showCompleteData.java

package developer.codecsqlitedatabase;

import java.util.ArrayList;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class showCompleteData extends Activity{

	ListView list;
	TextView dName,dEmail,dPhone,dDesignation;
	String name,email,phone,designation;
	SQLiteHelper dbHelper;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		setContentView(R.layout.complete_data);
		initialize();
		dbHelper = new SQLiteHelper(this);
		ArrayList dataList = dbHelper.getAllEmployeesData();
		ArrayAdapter employAdapter = new ArrayAdapter(this,android.R.layout.simple_list_item_1,dataList);
		list.setAdapter(employAdapter);
		list.setOnItemClickListener(new OnItemClickListener() {

			@Override
			public void onItemClick(AdapterView<?> parent, View view,
					int position, long id) {
				// TODO Auto-generated method stub
				int click_Employee = position+1;
				Cursor c = dbHelper.getSingleEntry(click_Employee);
				c.moveToFirst();
				name = c.getString(c.getColumnIndex(dbHelper.EMPLOYEE_NAME));
				email = c.getString(c.getColumnIndex(dbHelper.EMPLOYEE_EMAIL));
				phone = c.getString(c.getColumnIndex(dbHelper.EMPLOYEE_PHONE));
				designation = c.getString(c.getColumnIndex(dbHelper.EMPLOYEE_DESIGNATION));
				dName.setText(name);
				dEmail.setText(email);
				dPhone.setText(phone);
				dDesignation.setText(designation);
			}
		});
	}
	public void initialize(){
		list = (ListView) findViewById(R.id.listView1);
		dName = (TextView) findViewById(R.id.eName);
		dEmail = (TextView) findViewById(R.id.eEmail);
		dPhone = (TextView) findViewById(R.id.ePhone);
		dDesignation = (TextView) findViewById(R.id.eDesignation);
	}

}

updateEmployee.java

package developer.codecsqlitedatabase;

import java.util.ArrayList;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.AdapterView.OnItemClickListener;

public class updateEmployee extends Activity{
	ListView list;
	EditText dName,dEmail,dPhone,dDesignation;
	String name,email,phone,designation;
	SQLiteHelper dbHelper;
	int click_Employee;
	Button upBtn;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		setContentView(R.layout.update);
		initialize();
		dbHelper = new SQLiteHelper(this);
		ArrayList dataList = dbHelper.getAllEmployeesData();
		ArrayAdapter employAdapter = new ArrayAdapter(this,android.R.layout.simple_list_item_1,dataList);
		list.setAdapter(employAdapter);
		list.setOnItemClickListener(new OnItemClickListener() {

			@Override
			public void onItemClick(AdapterView<?> parent, View view,
					int position, long id) {
				// TODO Auto-generated method stub
				click_Employee = position+1;
				Cursor c = dbHelper.getSingleEntry(click_Employee);
				c.moveToFirst();
				name = c.getString(c.getColumnIndex(dbHelper.EMPLOYEE_NAME));
				email = c.getString(c.getColumnIndex(dbHelper.EMPLOYEE_EMAIL));
				phone = c.getString(c.getColumnIndex(dbHelper.EMPLOYEE_PHONE));
				designation = c.getString(c.getColumnIndex(dbHelper.EMPLOYEE_DESIGNATION));
				dName.setText(name);
				dEmail.setText(email);
				dPhone.setText(phone);
				dDesignation.setText(designation);
			}
		});
		
		upBtn.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				dbHelper.updateEmployeeInfo(click_Employee,dName.getText().toString(),dEmail.getText().toString(),dPhone.getText().toString(),dDesignation.getText().toString());
				Toast.makeText(getApplicationContext(), "Updated Successfully", Toast.LENGTH_SHORT).show();
			}
		});
		
	}
	public void initialize(){
		list = (ListView) findViewById(R.id.listView1);
		dName = (EditText) findViewById(R.id.upName);
		dEmail = (EditText) findViewById(R.id.upEmail);
		dPhone = (EditText) findViewById(R.id.upPhone);
		dDesignation = (EditText) findViewById(R.id.upDesignation);
		upBtn = (Button) findViewById(R.id.updateEmploy);
	}

}

deleteEmployee.java

package developer.codecsqlitedatabase;

import java.util.ArrayList;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.AdapterView.OnItemClickListener;

public class deleteEmployee extends Activity{
	ListView list;
	SQLiteHelper dbHelper;
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		setContentView(R.layout.delete);
		initialize();
		dbHelper = new SQLiteHelper(this);
		ArrayList dataList = dbHelper.getAllEmployeesData();
		ArrayAdapter employAdapter = new ArrayAdapter(this,android.R.layout.simple_list_item_1,dataList);
		list.setAdapter(employAdapter);
		list.setOnItemClickListener(new OnItemClickListener() {

			@Override
			public void onItemClick(AdapterView<?> parent, View view,
					int position, long id) {
				// TODO Auto-generated method stub
				int click_Employee = position+1;
				dbHelper.deleteEmployee(click_Employee);
				Toast.makeText(getApplicationContext(), "Employee Deleted Successfully", Toast.LENGTH_SHORT).show();
				Intent n = new Intent(getApplicationContext(),MainActivity.class);
				startActivity(n);
				finish();
			}
		});
	}
	public void initialize(){
		list = (ListView) findViewById(R.id.listView1);
		
	}

}

slipEmployeeView.java

package developer.codecsqlitedatabase;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.graphics.Color;
import android.os.Bundle;
import android.view.Gravity;
import android.view.Window;
import android.view.WindowManager;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;

public class slipEmployeeView extends Activity {
	TextView data;
	SQLiteHelper dbHelper;
	SQLiteDatabase db;
	

	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		requestWindowFeature(Window.FEATURE_NO_TITLE);
		getWindow().setFlags(WindowManager.LayoutParams.FLAG_FULLSCREEN,WindowManager.LayoutParams.FLAG_FULLSCREEN);
		setContentView(R.layout.slip_display);

		dbHelper = new SQLiteHelper(this);


		// Reference to TableLayout
		TableLayout tableLayout=(TableLayout)findViewById(R.id.tablelayout);
		// Add header row
		TableRow rowHeader = new TableRow(this);
		rowHeader.setBackgroundColor(Color.parseColor("#c0c0c0"));
		rowHeader.setLayoutParams(new TableLayout.LayoutParams(TableLayout.LayoutParams.MATCH_PARENT,
				TableLayout.LayoutParams.WRAP_CONTENT));
		String[] headerText={"Name","Email","Phone","Designation"};
		for(String c:headerText) {
			TextView tv = new TextView(this);
			tv.setLayoutParams(new TableRow.LayoutParams(TableRow.LayoutParams.WRAP_CONTENT,
					TableRow.LayoutParams.WRAP_CONTENT));
			tv.setGravity(Gravity.CENTER);
			tv.setTextSize(18);
			tv.setPadding(5, 5, 5, 5);
			tv.setText(c);
			rowHeader.addView(tv);
		}
		tableLayout.addView(rowHeader);

		// Get data from sqlite database and add them to the table
		// Open the database for reading
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		// Start the transaction.
		db.beginTransaction();

		try
		{
			String selectQuery = "SELECT * FROM Employees";
			Cursor cursor = db.rawQuery(selectQuery,null);
			if(cursor.getCount() >0)
			{
				while (cursor.moveToNext()) {
					// Read columns data
					String outlet_id= cursor.getString(cursor.getColumnIndex("id"));
					String outlet_name= cursor.getString(cursor.getColumnIndex("name"));
					String outlet_email= cursor.getString(cursor.getColumnIndex("email"));
					String outlet_phone = cursor.getString(cursor.getColumnIndex("phone"));
					String outlet_designation = cursor.getString(cursor.getColumnIndex("designation"));

					// dara rows
					TableRow row = new TableRow(this);
					row.setLayoutParams(new TableLayout.LayoutParams(TableLayout.LayoutParams.MATCH_PARENT,
							TableLayout.LayoutParams.WRAP_CONTENT));
					
					String[] colText={outlet_name+"",outlet_email,outlet_phone,outlet_designation};
					for(String text:colText) {
						TextView tv = new TextView(this);
						tv.setLayoutParams(new TableRow.LayoutParams(TableRow.LayoutParams.WRAP_CONTENT,
								TableRow.LayoutParams.WRAP_CONTENT));
						tv.setGravity(Gravity.CENTER);
						tv.setTextSize(16);
						tv.setPadding(5, 5, 5, 5);
						tv.setText(text);
						row.addView(tv);
					}
					tableLayout.addView(row);

				}


			}
			
			db.setTransactionSuccessful();

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

		}
		finally
		{
			db.endTransaction();
			// End the transaction.
			db.close();
			// Close database
		}
	}
}

Now declare activities in android sqlite database tutorial file Android Manifest.xml.

android sqlite database,sqlite,sqlite database,how to create sqlite database in android,sqlite android tutorial,sqlite database tutorial android

Now run your android sqlite database tutorial application project.

android sqlite database,sqlite,sqlite database,how to create sqlite database in android,sqlite android tutorial,sqlite database tutorial android,android sqlite database tutorial,

android sqlite database,sqlite,sqlite database,how to create sqlite database in android,sqlite android tutorial,sqlite database tutorial android,android sqlite database tutorial,

that’s it for this android sqlite database tutorial.

For more queries regarding this tutorial either you can comment here or go to Stack Over Flow website and create a question there.

Thanks.