Post
Ask Question

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.
 
student record app
Figure 1
 
SQLiteOpenHelper
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)
  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.       
  2.   
  3. <?xml version="1.0" encoding="utf-8"?>  
  4. <manifest  
  5.     xmlns:android="http://schemas.android.com/apk/res/android"    
  6.         package="com.example.sqlitepractice"    
  7.         android:versionCode="1"    
  8.         android:versionName="1.0" >  
  9.     <uses-sdk    
  10.         android:minSdkVersion="8"    
  11.         android:targetSdkVersion="19" />  
  12.     <application    
  13.             android:allowBackup="true"    
  14.             android:icon="@drawable/ic_launcher"    
  15.             android:label="@string/app_name"    
  16.     >  
  17.         <activity    
  18.             android:name="com.example.sqlitepractice.MainActivity"    
  19.             android:label="Student Record App" >  
  20.             <intent-filter>  
  21.                 <action android:name="android.intent.action.MAIN" />  
  22.                 <category android:name="android.intent.category.LAUNCHER" />  
  23.             </intent-filter>  
  24.         </activity>  
  25.         <activity    
  26.         
  27.             android:name=".GridViewActivity"    
  28.         
  29.             android:label="Student Record"    
  30.         
  31.         ></activity>  
  32.     </application>  
  33. </manifest>   
    Now It's complete, build and run your project.
     
    “Cheers, Enjoy Coding”.


    Similar Articles