SQLite With GridView in Android

In this article you will learn how to use SQLite with GridView in Android programming.

Today in this article I will show you how to use SQLite a built in database in android with GridView. I am using this reference class SQLiteOpenHelper on android official site. Lets start.


Note: Now you can read it on my blog.I have updated this article on my personal blog here.

Prerequisites:

  • Basic knowledge of Android
  • Familiar with Eclipse IDE
  • SQL statements

Introduction

In introduction I would like to introduce the basic three things which we are going to use in this article. These are SQLite, SQLiteOpenHelper and third one GridView.

SQLite

SQLite is an open source database. SQLite supports standard relational database features like SQL syntax statements. This is built in andoird so you only have to define the SQL statements for creating and updating the database. Then the database is automatically managed for you by the Android platform.

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

Why SQLiteOpenHelper?

SQLiteOpenHelper provides utilities to simplify the tasks of creating and initializing the database if it's not already created and converting the contents of the database when your application is upgrading and the database schema changes.

GridView

Android GridView shows items in two-dimensional scrolling grid (rows & columns) and the grid items are not necessarily predetermined but they automatically inserted to the layout using a ListAdapter.

An adapter actually bridges between UI components and the data source that fill data into UI Component. Adapter can be used to supply the data to like spinner, list view, grid view etc.

student record app
Figure 1

SQLiteOpenHelper
Figure

Lets start it…!

Step 1

Create a new project by going to File ⇒ New Android Project and fill required details. I named my main activity as MainActivity.java file (you can name it according to yours).

Step 2

Create a new XML layout under layout folder and name it as Main.xml (Right Click) layout ⇒ New ⇒ Android XML File. Put the below code into your Main.xml file.

Main.xml (Layout File)

  1. <?xml version="1.0" encoding="utf-8"?>  
  2.    <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  3.    android:layout_width="match_parent"  
  4.    android:layout_height="match_parent" >  
  5.    <Button  
  6.       android:id="@+id/DisplayButton"  
  7.       android:layout_width="wrap_content"  
  8.       android:layout_height="wrap_content"  
  9.       android:layout_alignBaseline="@+id/InsertButton"  
  10.       android:layout_alignBottom="@+id/InsertButton"  
  11.       android:layout_marginLeft="44dp"  
  12.       android:layout_toRightOf="@+id/InsertButton"  
  13.       android:text="Display" />  
  14.    <TextView  
  15.       android:id="@+id/textView1"  
  16.       android:layout_width="wrap_content"  
  17.       android:layout_height="wrap_content"  
  18.       android:layout_alignParentLeft="true"  
  19.       android:layout_alignParentTop="true"  
  20.       android:text="Student Name"  
  21.       android:textAppearance="?android:attr/textAppearanceMedium" />  
  22.    <EditText  
  23.       android:id="@+id/NameEditText"  
  24.       android:layout_width="wrap_content"  
  25.       android:layout_height="wrap_content"  
  26.       android:layout_alignParentLeft="true"  
  27.       android:layout_alignParentRight="true"  
  28.       android:layout_below="@+id/textView1"  
  29.       android:layout_marginTop="18dp"  
  30.       android:ems="10" />  
  31.    <TextView  
  32.       android:id="@+id/textView2"  
  33.       android:layout_width="wrap_content"  
  34.       android:layout_height="wrap_content"  
  35.       android:layout_alignParentLeft="true"  
  36.       android:layout_below="@+id/NameEditText"  
  37.       android:text="Roll No"  
  38.       android:textAppearance="?android:attr/textAppearanceMedium" />  
  39.    <EditText  
  40.       android:id="@+id/RollEditText"  
  41.       android:layout_width="wrap_content"  
  42.       android:layout_height="wrap_content"  
  43.       android:layout_alignParentLeft="true"  
  44.       android:layout_alignParentRight="true"  
  45.       android:layout_below="@+id/textView2"  
  46.       android:layout_marginTop="24dp"  
  47.       android:ems="10" />  
  48.    <TextView  
  49.       android:id="@+id/textView3"  
  50.       android:layout_width="wrap_content"  
  51.       android:layout_height="wrap_content"  
  52.       android:layout_alignParentLeft="true"  
  53.       android:layout_below="@+id/RollEditText"  
  54.       android:text="Major Course"  
  55.       android:textAppearance="?android:attr/textAppearanceMedium" />  
  56.    <EditText  
  57.       android:id="@+id/CourseEditText"  
  58.       android:layout_width="wrap_content"  
  59.       android:layout_height="wrap_content"  
  60.       android:layout_alignParentLeft="true"  
  61.       android:layout_alignParentRight="true"  
  62.       android:layout_below="@+id/textView3"  
  63.       android:layout_marginTop="19dp"  
  64.       android:ems="10" >  
  65.    <requestFocus />  
  66. </EditText>  
  67. <Button  
  68.    android:id="@+id/InsertButton"  
  69.    android:layout_width="wrap_content"  
  70.    android:layout_height="wrap_content"  
  71.    android:layout_alignParentBottom="true"  
  72.    android:layout_marginBottom="58dp"  
  73.    android:layout_toRightOf="@+id/textView2"  
  74.    android:text="Insert" />  
  75. </RelativeLayout>  
Step 3

Create a new XML layout under layout folder and name it as Gridview.xml (Right Click) layout ⇒ New ⇒Android XML File. Put the below code into your Gridview.xml file.

Gridview.xml File
  1. <?xml version="1.0" encoding="utf-8"?>  
  2.    <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"  
  3.    android:layout_width="match_parent"  
  4.    android:layout_height="match_parent" >  
  5.    <TextView  
  6.       android:id="@+id/textView1"  
  7.       android:layout_width="wrap_content"  
  8.       android:layout_height="wrap_content"  
  9.       android:layout_alignParentLeft="true"  
  10.       android:layout_alignParentTop="true"  
  11.       android:text="Name"  
  12.       android:textAppearance="?android:attr/textAppearanceLarge" />  
  13.    <TextView  
  14.    android:id="@+id/textView2"  
  15.    android:layout_width="wrap_content"  
  16.    android:layout_height="wrap_content"  
  17.    android:layout_alignParentTop="true"  
  18.    android:layout_centerHorizontal="true"  
  19.    android:text="Roll"  
  20.    android:textAppearance="?android:attr/textAppearanceLarge" />  
  21.    <TextView  
  22.       android:id="@+id/textView3"  
  23.       android:layout_width="wrap_content"  
  24.       android:layout_height="wrap_content"  
  25.       android:layout_alignParentRight="true"  
  26.       android:layout_alignParentTop="true"  
  27.       android:text="Course"  
  28.       android:textAppearance="?android:attr/textAppearanceLarge" />  
  29.    <GridView  
  30.       android:id="@+id/gridView1"  
  31.       android:layout_width="match_parent"  
  32.       android:layout_height="wrap_content"  
  33.       android:layout_alignParentLeft="true"  
  34.       android:layout_below="@+id/textView1"  
  35.       android:fastScrollAlwaysVisible="true"  
  36.       android:fastScrollEnabled="true"  
  37.       android:numColumns="3" >  
  38.    </GridView>  
  39. </RelativeLayout>  
Step 4

Here put the below code into your MainActivity.java file which you have created in step1.

MainActivity.java File

  1. public class MainActivity extends Activity {  
  2.    //Data members  
  3.    private EditText nameEditText;  
  4.    private EditText rollEditText;  
  5.    private EditText courseEditText;  
  6.    private Button insertButton;  
  7.    private Button displayButton;  
  8.    //object of DatabaseHandler class  
  9.    DatabaseHandler handler;  
  10.    @Override  
  11.    protected void onCreate(Bundle savedInstanceState) {  
  12.       super.onCreate(savedInstanceState);  
  13.       setContentView(R.layout.main);  
  14.       nameEditText=(EditText) findViewById(R.id.NameEditText);  
  15.       rollEditText=(EditText) findViewById(R.id.RollEditText);  
  16.       courseEditText=(EditText) findViewById(R.id.CourseEditText);  
  17.       insertButton=(Button) findViewById(R.id.InsertButton);  
  18.       displayButton=(Button) findViewById(R.id.DisplayButton);  
  19.       insertButton.setOnClickListener(new View.OnClickListener() {  
  20.          @Override  
  21.          public void onClick(View v) {  
  22.             String name=nameEditText.getText().toString();  
  23.             String roll=rollEditText.getText().toString();  
  24.             String course=courseEditText.getText().toString();  
  25.             handler=new DatabaseHandler(getBaseContext());//getting the context object  
  26.             handler.open();  
  27.             long id=handler.InsertData(name, roll, course);  
  28.             Toast.makeText(getBaseContext(), "Your data in inserted",Toast.LENGTH_LONG).show();  
  29.             nameEditText.setText("");  
  30.             rollEditText.setText("");  
  31.             courseEditText.setText("");  
  32.             handler.close();  
  33.          }  
  34.       });  
  35.       displayButton.setOnClickListener(new View.OnClickListener() {  
  36.          @Override  
  37.          public void onClick(View v) {  
  38.             Intent i=new Intent(MainActivity.this,GridViewActivity.class);  
  39.             startActivity(i);//start gridview activity to show the records.  
  40.          }  
  41.       });  
  42.    }  
  43. }  
Step 5

Create a new Class by right clicking on (Right Click) src ⇒ package folder ⇒ New ⇒ Class and name your class as GridViewActivity.java. In this activity all the records from the database will fetch and show in the GridView.Put the below code into your GridViewActivity.java file.

GridViewActivity.java File
  1. public class GridViewActivity extends Activity  
  2. {  
  3.    private GridView gridView;  
  4.    private ArrayList<String> list;  
  5.    private ArrayAdapter<String> adapter;  
  6.    DatabaseHandler handler;  
  7.    @Override  
  8.    protected void onCreate(Bundle savedInstanceState) {  
  9.    super.onCreate(savedInstanceState);  
  10.    setContentView(R.layout.gridview);  
  11.    //GridView  
  12.    gridView=(GridView) findViewById(R.id.gridView1);  
  13.    //ArrayList  
  14.    list=new ArrayList<String>();  
  15.    adapter=new ArrayAdapter<String>(getApplicationContext(),android.R.layout.simple_spinner_item,list);  
  16.    String name, roll, course;  
  17.    name="";  
  18.    roll="";  
  19.    course="";  
  20.    handler=new DatabaseHandler(getBaseContext());//getting the context object  
  21.    handler.open();  
  22.    try  
  23.    {  
  24.       //for holding retrieve data from query and store in the form of rows  
  25.       Cursor c=handler.DisplayData();  
  26.       //Move the cursor to the first row.  
  27.       if(c.moveToFirst())  
  28.       {  
  29.          do  
  30.          {  
  31.             name=c.getString(c.getColumnIndex("name"));  
  32.             roll=c.getString(c.getColumnIndex("roll"));  
  33.             course=c.getString(c.getColumnIndex("course"));  
  34.             //add in to array list  
  35.             list.add(name);  
  36.             list.add(roll);  
  37.             list.add(course);  
  38.             gridView.setAdapter(adapter);  
  39.          }while(c.moveToNext());//Move the cursor to the next row.  
  40.       }  
  41.       else  
  42.       {  
  43.          Toast.makeText(getApplicationContext(), "No data found", Toast.LENGTH_LONG).show();  
  44.       }  
  45.       }catch(Exception e)  
  46.       {  
  47.          Toast.makeText(getApplicationContext(), "No data found"+e.getMessage(), Toast.LENGTH_LONG).show();  
  48.       }  
  49.       handler.close();  
  50.       //Toast.makeText(getBaseContext(),"Name: "+name+"Roll No: "+roll+"Course: "+course , Toast.LENGTH_LONG).show();  
  51.    }  
  52. }  
Step 6

Create a new Class by right clicking on (Right Click) src ⇒ package folder ⇒ New ⇒ Class and name your class as DataBaseHandler.java. This class will handle the SQLite Database all the required information will hold this class like DB name,version and column names. Put the below code into your DataBaseHandler.java.

DataBaseHandler.java File
  1. public class DatabaseHandler  
  2. {  
  3.    //Variables  
  4.    public static final String NAME="name";  
  5.    public static final String ROLL="roll";  
  6.    public static final String COURSE="course";  
  7.    public static final String TABLE_NAME="studenttable";  
  8.    public static final String DATABASE_NAME="studentdb";  
  9.    public static final int DATABASE_VERSION=1;  
  10.    //Table query  
  11.    public static final String TABLE_CREATE="create table studenttable(name text not null, roll text not null, course text not null);";  
  12.    DataBaseHelper dbhelper;  
  13.    Context context;  
  14.    SQLiteDatabase db;  
  15.    public DatabaseHandler(Context ctx)  
  16.    {  
  17.       this.context=ctx;  
  18.       dbhelper=new DataBaseHelper(context);  
  19.    }  
  20.    private static class DataBaseHelper extends SQLiteOpenHelper  
  21.    {  
  22.       //Create a helper object to create, open, and/or manage a database.  
  23.       public DataBaseHelper(Context ctx)  
  24.       {  
  25.          super(ctx,DATABASE_NAME,null,DATABASE_VERSION);  
  26.       }  
  27.       @Override  
  28.       //Called when the database is created for the first time.  
  29.       public void onCreate(SQLiteDatabase db)  
  30.       {  
  31.          db.execSQL(TABLE_CREATE);//Here create a table  
  32.       }  
  33.       @Override  
  34.       //Called when the database needs to be upgraded.  
  35.       public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
  36.          db.execSQL("DROP TABLE IF EXIST studenttable");  
  37.          onCreate(db);  
  38.       }  
  39.    }  
  40.    public DatabaseHandler open()  
  41.    {  
  42.       //Create and/or open a database that will be used for reading and writing.  
  43.       db=dbhelper.getWritableDatabase();  
  44.       return this;  
  45.    }  
  46.    public void close()  
  47.    {  
  48.       //Close any open database object.  
  49.       dbhelper.close();  
  50.    }  
  51.    //Insert record in the database  
  52.    public long InsertData(String name, String roll,String course)  
  53.    {  
  54.       //This class is used to store a set of values  
  55.       ContentValues content=new ContentValues();  
  56.       content.put(NAME, name);  
  57.       content.put(ROLL, roll);  
  58.       content.put(COURSE, course);//Adds a value to the set.  
  59.       return db.insertOrThrow(TABLE_NAME,null, content);  
  60.    }  
  61.    //Display record from the database  
  62.    public Cursor DisplayData()  
  63.    {  
  64.       //Select query  
  65.       return db.rawQuery("SELECT * FROM studenttable"null);  
  66.       //return db.query(TABLE_NAME, new String[]{NAME, ROLL,COURSE}, null, null, null, null, null);  
  67.    }  
  68. }  
Step 7

Open your AndroidManifest.xml and add entries of newly added activity into your AndroidManifest.xml. Simple put the below code into your AndroidManifest.xml.

AndroidManifest.xml File
  1. <?xml version="1.0" encoding="utf-8"?>  
  2.   
  3. <manifest xmlns:android="http://schemas.android.com/apk/res/android"  
  4.   
  5.     package="com.example.sqlitepractice"  
  6.   
  7.     android:versionCode="1"  
  8.   
  9.     android:versionName="1.0" >  
  10.   
  11.     <uses-sdk  
  12.   
  13.     android:minSdkVersion="8"  
  14.   
  15.     android:targetSdkVersion="19" />  
  16.   
  17.     <application  
  18.   
  19.         android:allowBackup="true"  
  20.   
  21.         android:icon="@drawable/ic_launcher"  
  22.   
  23.         android:label="@string/app_name"  
  24.   
  25.     >  
  26.   
  27.     <activity  
  28.   
  29.         android:name="com.example.sqlitepractice.MainActivity"  
  30.   
  31.         android:label="Student Record App" >  
  32.   
  33.         <intent-filter>  
  34.   
  35.             <action android:name="android.intent.action.MAIN" />  
  36.   
  37.             <category android:name="android.intent.category.LAUNCHER" />  
  38.   
  39.         </intent-filter>  
  40.   
  41.     </activity>  
  42.   
  43.     <activity  
  44.   
  45.         android:name=".GridViewActivity"  
  46.   
  47.         android:label="Student Record"  
  48.   
  49.     ></activity>  
  50.   
  51.     </application>  
  52.   
  53. </manifest>  
Now Its complete,build and run your project.

“Cheers, Enjoy Coding”.