Android SQLite example

When you create an application that uses Android phones' storage to store databases, SQLite is your best choice. It is a built-in database system of Android. In this post, I will give you an example of using SQLite database in an application. In this example, you learn how to create a database, a table, insert data into the table, and read the data from the table to display in a ListView.

Now to have a workable example application on using SQLite database in Android, you need to create a new Android project. Then modify the activity_main.xml file to include a ListView as shown below.

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
   xmlns:tools="http://schemas.android.com/tools"
   android:layout_width="match_parent"
   android:layout_height="match_parent"
   android:paddingBottom="@dimen/activity_vertical_margin"
   android:paddingLeft="@dimen/activity_horizontal_margin"
   android:paddingRight="@dimen/activity_horizontal_margin"
   android:paddingTop="@dimen/activity_vertical_margin"
   tools:context=".MainActivity" >

  <ListView
android:id="@+id/pro_lsit"
android:layout_width="fill_parent"
android:layout_height="fill_parent"

/>
  
  

</RelativeLayout>




In this SQLite example, the ListView displays names and unit prices of products. So it must be customized to achieve this goal. In this ListView customization, you will need two files: list_item.xml and CustoAdapter.java file. The list_item.xml file represents row template of the ListView and the CustomAdapter will be used to feed data to the ListView.

list_item.xml

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

<RelativeLayout  
   xmlns:android="http://schemas.android.com/apk/res/android"
   android:layout_width="match_parent"
   android:layout_height="match_parent"
   android:padding="5dp" >
  
<TextView
     android:id="@+id/pro_name"
     android:layout_width="wrap_content"
     android:layout_height="match_parent"
     android:layout_marginRight="10dp"
     android:textSize="20sp"
       />

<TextView
     android:id="@+id/pro_uprice"
     android:layout_width="wrap_content"
     android:layout_height="match_parent"
android:textSize="20sp"
android:layout_alignParentRight="true"
      />

  
</RelativeLayout>


CustomAdapter.java

  package com.example.androidproject;  
import java.util.ArrayList;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.TextView;

public class CustomAdapter extends ArrayAdapter<String> {

int groupid;
ArrayList<String> records;
Context context;

public CustomAdapter(Context context, int vg, int id, ArrayList<String> records){
super(context,vg, id, records);
this.context=context;
groupid=vg;
this.records=records;

}

public View getView(int position, View convertView, ViewGroup parent) {

     LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
     View itemView = inflater.inflate(groupid, parent, false);
     String[] row_items=records.get(position).split("__");
     TextView textName= (TextView) itemView.findViewById(R.id.pro_name);
     textName.setText(row_items[1]);
     TextView textPrice= (TextView) itemView.findViewById(R.id.pro_uprice);
     textPrice.setText(row_items[2]+"$");
     return itemView;
}
}


Now create two additional java files: SQLiteDB and DBSchema. The SQLiteDB.java file helps you to create a database and a table. In this example, the database and table to be created are products.db and tblproducts. They will be created automatically for you when you create an object of the SQLiteDB class. The DBSchema.java file contains a class called DBSchema that defines the names of table and its columns.

SQLiteDB.java

  package com.example.androidproject;  
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class SQLiteDB extends SQLiteOpenHelper{
public static final int DATABASE_VERSION= 1;
public static final String DATABASE_NAME= "products.db";
private String createtbl="CREATE TABLE "+DBSchema.TABLE_NAME+" ("+DBSchema.COL_NAME_KEY+" INT PRIMARY KEY, "+
DBSchema.COL_NAME_PNAME+" TEXT, "+DBSchema.COL_NAME_UPRICE+" REAL)";
private static final String deletetbl="DROP TABLE IF EXISTS " + DBSchema.TABLE_NAME;
public SQLiteDB(Context context){
//create database if it does not exist
super(context,DATABASE_NAME,null,DATABASE_VERSION);
}

public void onCreate(SQLiteDatabase db){
//create table
db.execSQL(createtbl);
}

public void onUpgrade(SQLiteDatabase db,int olv,int newv){
db.execSQL(deletetbl);
onCreate(db);
}
}

DBSchema.java

  package com.example.androidproject;  
public class DBSchema {
public DBSchema(){}
public static final String TABLE_NAME="tblproducts";
public static final String COL_NAME_KEY="pid";
public static final String COL_NAME_PNAME="pname";
public static final String COL_NAME_UPRICE="unitprice";

}


Open the MainActivity.java file. In the onCreate() method, you will write code to create a reference to the ListView, create an object of CustomAdapter, set the adapter object to the ListView, and create an object of the SQLiteDB. Then add two methods: insertSampleData() and readSampleData() to the MainActivity class. The insertSampleData() will be invoked to insert some example data. The readSampleData() reads the sample data from the tblproducts table to display in the ListView.

MainActivity.java

  package com.example.androidproject;
import java.util.ArrayList;
import android.os.Bundle;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.widget.ListView;

public class MainActivity extends Activity{
ListView listview;
CustomAdapter adapter;
ArrayList<String> records;
SQLiteDB dbhelper;
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
listview=(ListView)findViewById(R.id.pro_lsit);
records=new ArrayList<String>();
adapter=new CustomAdapter(this,R.layout.list_item,R.id.pro_name,records);
listview.setAdapter(adapter);
//create SQLiteDB instance resulting the creation of products.db database and tblproducts table
dbhelper=new SQLiteDB(this);

}

public void onStart(){
  super.onStart();
  insertSampleData();
  readSampleData();
}

public void insertSampleData(){
//get a writable database
SQLiteDatabase database=dbhelper.getWritableDatabase();
//constructors key/value data
ContentValues values=new ContentValues();
//first row data
values.put(DBSchema.COL_NAME_KEY,1);
values.put(DBSchema.COL_NAME_PNAME,"Samsung Galaxy Note3");
values.put(DBSchema.COL_NAME_UPRICE,577.0);
//insert the first row to database
database.insertOrThrow(DBSchema.TABLE_NAME,null, values);
//second row data
values.put(DBSchema.COL_NAME_KEY,2);
values.put(DBSchema.COL_NAME_PNAME,"Samsung Galaxy S5");
values.put(DBSchema.COL_NAME_UPRICE,570.0);
//insert the second row to database
database.insertOrThrow(DBSchema.TABLE_NAME,null, values);
//third row data
values.put(DBSchema.COL_NAME_KEY,3);
values.put(DBSchema.COL_NAME_PNAME,"iPad Air");
values.put(DBSchema.COL_NAME_UPRICE,578.0);
//insert the third row to database
database.insertOrThrow(DBSchema.TABLE_NAME,null, values);

}

public void readSampleData(){
//get a readable database
SQLiteDatabase database=dbhelper.getReadableDatabase();
String sql="SELECT * FROM "+DBSchema.TABLE_NAME;
//create Cursor object to read data from database
   Cursor c=database.rawQuery(sql,null);
   String pname;
   float uprice;
   Integer id;
   if(c.getCount()>0)
   while(c.moveToNext()){
   del_id=c.getInt(c.getColumnIndex(DBSchema.COL_NAME_KEY));
   pname=c.getString(c.getColumnIndex(DBSchema.COL_NAME_PNAME));
   uprice=c.getFloat(c.getColumnIndex(DBSchema.COL_NAME_UPRICE));
   String item=id+"__"+pname+"__"+uprice;
   records.add(item);
     
   }
   //notify listview of dataset changed
   adapter.notifyDataSetChanged();
}

}


Android-SQLite-example

Posted by: Dara | post date: 07-28-2014 | Subject: Android Apps Development

write programming tip



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.