SQLite With GridView in Android

Introduction

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. Let's 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 the 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 a built-in android 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 a 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. The adapter can be used to supply the data to like spinner, list view, grid view, etc.

Figure 1

Figure 2

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)

<?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" >
<Button
android:id="@+id/DisplayButton"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/InsertButton"
android:layout_alignBottom="@+id/InsertButton"
android:layout_marginLeft="44dp"
android:layout_toRightOf="@+id/InsertButton"
android:text="Display" />
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentTop="true"
android:text="Student Name"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/NameEditText"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentRight="true"
android:layout_below="@+id/textView1"
android:layout_marginTop="18dp"
android:ems="10" />
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_below="@+id/NameEditText"
android:text="Roll No"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/RollEditText"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentRight="true"
android:layout_below="@+id/textView2"
android:layout_marginTop="24dp"
android:ems="10" />
<TextView
android:id="@+id/textView3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_below="@+id/RollEditText"
android:text="Major Course"
android:textAppearance="?android:attr/textAppearanceMedium" />
<EditText
android:id="@+id/CourseEditText"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentRight="true"
android:layout_below="@+id/textView3"
android:layout_marginTop="19dp"
android:ems="10" >
<requestFocus />
</EditText>
<Button
android:id="@+id/InsertButton"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentBottom="true"
android:layout_marginBottom="58dp"
android:layout_toRightOf="@+id/textView2"
android:text="Insert" />
</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

<?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" >
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_alignParentTop="true"
android:text="Name"
android:textAppearance="?android:attr/textAppearanceLarge" />
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentTop="true"
android:layout_centerHorizontal="true"
android:text="Roll"
android:textAppearance="?android:attr/textAppearanceLarge" />
<TextView
android:id="@+id/textView3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentRight="true"
android:layout_alignParentTop="true"
android:text="Course"
android:textAppearance="?android:attr/textAppearanceLarge" />
<GridView
android:id="@+id/gridView1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentLeft="true"
android:layout_below="@+id/textView1"
android:fastScrollAlwaysVisible="true"
android:fastScrollEnabled="true"
android:numColumns="3" >
</GridView>
</RelativeLayout>

Step 4

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

MainActivity.java File

public class MainActivity extends Activity {
//Data members
private EditText nameEditText;
private EditText rollEditText;
private EditText courseEditText;
private Button insertButton;
private Button displayButton;
//object of DatabaseHandler class
DatabaseHandler handler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
nameEditText=(EditText) findViewById(R.id.NameEditText);
rollEditText=(EditText) findViewById(R.id.RollEditText);
courseEditText=(EditText) findViewById(R.id.CourseEditText);
insertButton=(Button) findViewById(R.id.InsertButton);
displayButton=(Button) findViewById(R.id.DisplayButton);
insertButton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String name=nameEditText.getText().toString();
String roll=rollEditText.getText().toString();
String course=courseEditText.getText().toString();
handler=new DatabaseHandler(getBaseContext());//getting the context object
handler.open();
long id=handler.InsertData(name, roll, course);
Toast.makeText(getBaseContext(), "Your data in inserted",Toast.LENGTH_LONG).show();
nameEditText.setText("");
rollEditText.setText("");
courseEditText.setText("");
handler.close();
}
});
displayButton.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
Intent i=new Intent(MainActivity.this,GridViewActivity.class);
startActivity(i);//start gridview activity to show the records.
}
});
}
}

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

public class GridViewActivity extends Activity
{
private GridView gridView;
private ArrayList<String> list;
private ArrayAdapter<String> adapter;
DatabaseHandler handler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.gridview);
//GridView
gridView=(GridView) findViewById(R.id.gridView1);
//ArrayList
list=new ArrayList<String>();
adapter=new ArrayAdapter<String>(getApplicationContext(),android.R.layout.simple_spinner_item,list);
String name, roll, course;
name="";
roll="";
course="";
handler=new DatabaseHandler(getBaseContext());//getting the context object
handler.open();
try
{
//for holding retrieve data from query and store in the form of rows
Cursor c=handler.DisplayData();
//Move the cursor to the first row.
if(c.moveToFirst())
{
do
{
name=c.getString(c.getColumnIndex("name"));
roll=c.getString(c.getColumnIndex("roll"));
course=c.getString(c.getColumnIndex("course"));
//add in to array list
list.add(name);
list.add(roll);
list.add(course);
gridView.setAdapter(adapter);
}while(c.moveToNext());//Move the cursor to the next row.
}
else
{
Toast.makeText(getApplicationContext(), "No data found", Toast.LENGTH_LONG).show();
}
}catch(Exception e)
{
Toast.makeText(getApplicationContext(), "No data found"+e.getMessage(), Toast.LENGTH_LONG).show();
}
handler.close();
//Toast.makeText(getBaseContext(),"Name: "+name+"Roll No: "+roll+"Course: "+course , Toast.LENGTH_LONG).show();
}
}

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

public class DatabaseHandler
{
//Variables
public static final String NAME="name";
public static final String ROLL="roll";
public static final String COURSE="course";
public static final String TABLE_NAME="studenttable";
public static final String DATABASE_NAME="studentdb";
public static final int DATABASE_VERSION=1;
//Table query
public static final String TABLE_CREATE="create table studenttable(name text not null, roll text not null, course text not null);";
DataBaseHelper dbhelper;
Context context;
SQLiteDatabase db;
public DatabaseHandler(Context ctx)
{
this.context=ctx;
dbhelper=new DataBaseHelper(context);
}
private static class DataBaseHelper extends SQLiteOpenHelper
{
//Create a helper object to create, open, and/or manage a database.
public DataBaseHelper(Context ctx)
{
super(ctx,DATABASE_NAME,null,DATABASE_VERSION);
}
@Override
//Called when the database is created for the first time.
public void onCreate(SQLiteDatabase db)
{
db.execSQL(TABLE_CREATE);//Here create a table
}
@Override
//Called when the database needs to be upgraded.
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXIST studenttable");
onCreate(db);
}
}
public DatabaseHandler open()
{
//Create and/or open a database that will be used for reading and writing.
db=dbhelper.getWritableDatabase();
return this;
}
public void close()
{
//Close any open database object.
dbhelper.close();
}
//Insert record in the database
public long InsertData(String name, String roll,String course)
{
//This class is used to store a set of values
ContentValues content=new ContentValues();
content.put(NAME, name);
content.put(ROLL, roll);
content.put(COURSE, course);//Adds a value to the set.
return db.insertOrThrow(TABLE_NAME,null, content);
}
//Display record from the database
public Cursor DisplayData()
{
//Select query
return db.rawQuery("SELECT * FROM studenttable", null);
//return db.query(TABLE_NAME, new String[]{NAME, ROLL,COURSE}, null, null, null, null, null);
}
}

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

<?xml version="1.0" encoding="utf-8"?>
<manifest
xmlns:android="http://schemas.android.com/apk/res/android"
package="com.example.sqlitepractice"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk
android:minSdkVersion="8"
android:targetSdkVersion="19" />
<application
android:allowBackup="true"
android:icon="@drawable/ic_launcher"
android:label="@string/app_name"
>
<activity
android:name="com.example.sqlitepractice.MainActivity"
android:label="Student Record App" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<activity
android:name=".GridViewActivity"
android:label="Student Record"
></activity>
</application>
</manifest>

Now It's complete, build and run your project.

“Cheers, Enjoy Coding”.