Android SQLite TableView - INSERT,SELECT,SHOW

This is an android tableview tutorial with SQLite as our database. A tableview renders data into tables: columns and rows. In this tutorial we see how to take advantage of this and use it alongside SQLite database. SQLite is a local database inbuilt into android devices and persist our data. We shall explore how to INSERT,SELECT and SHOW data to and from the database. The source is provide and its self explanatory and we have video for more explanations.

Screenshot

  • Here's the screenshot of the project.
Android TableView SQLite Example [caption id="" align="alignnone" width="494"]Android SQlite TableView Example Android SQlite TableView Example[/caption]

 TableView Landscape Mode

[caption id="" align="alignnone" width="739"]Android tableView SQLite Landscape Android tableView SQLite Landscape[/caption]

Project Structure

Here's the project structure:

Common Questions this example explores

  • Android SQLite Example.
  • Android TableView Example.
  • How to render data into table in android.
  • Table with headers and columns/rows in adroid.
  • How to insert into seqlite and retrieve data.

Tools Used

This example was written with the following tools:
  • Windows 8
  • AndroidStudio IDE
  • Genymotion Emulator
  • Language : Java
  • Topic : TableView, Table Adapter, SQLite CRUD

Libaries Used

  • We use codecrafters.tableview.TableView library.

Source Code

Lets jump directly to the source code. [su_tabs]

Build.Gradle

  • Normally in android projects, there are two build.gradle files. One is the app level build.gradle, the other is project level build.gradle. The app level belongs inside the app folder and its where we normally add our dependencies and specify the compile and target sdks.
  • Also Add dependencies for AppCompat and Design support libraries.
  • Our MainActivity shall derive from AppCompatActivity while we shall also use Floating action button from design support libraries.
  • We also add our tableview here.
apply plugin: 'com.android.application'

android {
    compileSdkVersion 24
    buildToolsVersion "25.0.1"

    defaultConfig {
        applicationId "com.tutorials.hp.sqlitetableview"
        minSdkVersion 15
        targetSdkVersion 24
        versionCode 1
        versionName "1.0"
    }
    buildTypes {
        release {
            minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android.txt'), 'proguard-rules.pro'
        }
    }
}

dependencies {
    compile fileTree(dir: 'libs', include: ['*.jar'])
    testCompile 'junit:junit:4.12'
    compile 'com.android.support:appcompat-v7:24.2.1'
    compile 'com.android.support:design:24.2.1'
    compile 'com.android.support:cardview-v7:24.2.1'
    compile 'de.codecrafters.tableview:tableview:2.2.0'

}
 

MainActivity.java

  • Launcher activity.
  • ActivityMain.xml inflated as the contentview for this activity.
  • We initialize views and widgets inside this activity.
  • We use TableView as our adapterview and SimpleTableAdapter as our adapter.
package com.tutorials.hp.sqlitetableview;

import android.app.Dialog;
import android.graphics.Color;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import com.tutorials.hp.sqlitetableview.mDB.DBAdapter;
import com.tutorials.hp.sqlitetableview.mModel.Spacecraft;

import de.codecrafters.tableview.TableView;
import de.codecrafters.tableview.listeners.TableDataClickListener;
import de.codecrafters.tableview.toolkit.SimpleTableDataAdapter;
import de.codecrafters.tableview.toolkit.SimpleTableHeaderAdapter;

public class MainActivity extends AppCompatActivity {


    EditText nameEditText,propellantEditTxt,destEditTxt;
    Button saveBtn;
    TableView<String[]>  tb;
    TableHelper tableHelper;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);

        //TABLEVIEW
         tableHelper=new TableHelper(this);
        tb = (TableView<String[]>) findViewById(R.id.tableView);
        tb.setColumnCount(3);
        tb.setHeaderBackgroundColor(Color.parseColor("#2ecc71"));
        tb.setHeaderAdapter(new SimpleTableHeaderAdapter(this,tableHelper.getSpaceProbeHeaders()));
        tb.setDataAdapter(new SimpleTableDataAdapter(this, tableHelper.getSpaceProbes()));

        //TABLE CLICK
        tb.addDataClickListener(new TableDataClickListener() {
            @Override
            public void onDataClicked(int rowIndex, Object clickedData) {
                Toast.makeText(MainActivity.this, ((String[])clickedData)[1], Toast.LENGTH_SHORT).show();
            }
        });


        FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                displayDialog();
            }
        });
    }


    /*
    DISPLAY INPUT DIALOG
    SAVE
     */
    private void displayDialog()
    {
        Dialog d=new Dialog(this);
        d.setTitle("SQLITE DATA");
        d.setContentView(R.layout.dialog_layout);

        //INITIALIZE VIEWS
        nameEditText= (EditText) d.findViewById(R.id.nameEditTxt);
        propellantEditTxt= (EditText) d.findViewById(R.id.propEditTxt);
        destEditTxt= (EditText) d.findViewById(R.id.destEditTxt);

        saveBtn= (Button) d.findViewById(R.id.saveBtn);


        //SAVE
        saveBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                Spacecraft s = new Spacecraft();
                s.setName(nameEditText.getText().toString());
                s.setPropellant(propellantEditTxt.getText().toString());
                s.setDestination(destEditTxt.getText().toString());


                if (new DBAdapter(MainActivity.this).saveSpacecraft(s)) {
                    nameEditText.setText("");
                    propellantEditTxt.setText("");
                    destEditTxt.setText("");

                    tb.setDataAdapter(new SimpleTableDataAdapter(MainActivity.this, tableHelper.getSpaceProbes()));

                } else {
                    Toast.makeText(MainActivity.this, "Not Saved", Toast.LENGTH_SHORT).show();
                }
            }
        });

        //SHOW DIALOG
        d.show();


    }

}
 
[su_tab title="Spacecraft"]
  • Our data object class.
  • Represents a single spacecraft object.
package com.tutorials.hp.sqlitetableview.mModel;

/**
 *   9/27/2016 for ProgrammingWizards Channel and http://www.camposha.info.
 */
public class Spacecraft {
    private String name,propellant,destination;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPropellant() {
        return propellant;
    }

    public void setPropellant(String propellant) {
        this.propellant = propellant;
    }

    public String getDestination() {
        return destination;
    }

    public void setDestination(String destination) {
        this.destination = destination;
    }
}
 
[su_tab title="Constants"]
  • We hold our SQLite constants right here in this class like table name,database name,rows etc.
package com.tutorials.hp.sqlitetableview.mDB;

/**
 *   9/27/2016 for ProgrammingWizards Channel and http://www.camposha.info.
 */
public class Constants {
    /*
  COLUMNS
   */
    static final String ROW_ID="id";
    static final String NAME="name";
    static final String PROPELLANT="propellant";
    static final String DESTINATION="destination";


    /*
    DB PROPERTIES
     */
    static final String DB_NAME="tv_DB";
    static final String TB_NAME="tv_TB";
    static final int DB_VERSION=1;

    /*
    TABLE CREATION STATEMENT
     */
    static final String CREATE_TB="CREATE TABLE tv_TB(id INTEGER PRIMARY KEY AUTOINCREMENT,"
            + "name TEXT NOT NULL,propellant TEXT NOT NULL,destination TEXT NOT NULL);";


    /*
    TABLE DELETION STMT
     */
    static final String DROP_TB="DROP TABLE IF EXISTS "+TB_NAME;

}
 
[su_tab title="DBHelper"]
  • Our database helper class.
  • Ectends SQliteOpenHelper.
  • We create database table and upgrade it here.
package com.tutorials.hp.sqlitetableview.mDB;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 *   9/27/2016 for ProgrammingWizards Channel and http://www.camposha.info.
 */
public class DBHelper extends SQLiteOpenHelper {
    public DBHelper(Context context) {
        super(context, Constants.DB_NAME, null, Constants.DB_VERSION);
    }
    /*
    CREATE TABLE
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        try
        {
            db.execSQL(Constants.CREATE_TB);
        }catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
    /*
    UPGRADE TABLE
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
        try {
            db.execSQL(Constants.DROP_TB);
            db.execSQL(Constants.CREATE_TB);

        }catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}
 
[su_tab title="DBAdapter"]
  • Our database adapter class.
  • Here's where we insert,select and return an arraylist of our data.
package com.tutorials.hp.sqlitetableview.mDB;

import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.widget.Space;


import com.tutorials.hp.sqlitetableview.mModel.Spacecraft;

import java.util.ArrayList;

/**
 *   9/27/2016 for ProgrammingWizards Channel and http://www.camposha.info.
 */
public class DBAdapter {

    Context c;
    SQLiteDatabase db;
    DBHelper helper;

    /*
    1. INITIALIZE DB HELPER AND PASS IT A CONTEXT

     */
    public DBAdapter(Context c) {
        this.c = c;
        helper = new DBHelper(c);
    }


    /*
    SAVE DATA TO DB
     */
    public boolean saveSpacecraft(Spacecraft spacecraft) {
        try {
            db = helper.getWritableDatabase();

            ContentValues cv = new ContentValues();
            cv.put(Constants.NAME, spacecraft.getName());
            cv.put(Constants.PROPELLANT, spacecraft.getPropellant());
            cv.put(Constants.DESTINATION, spacecraft.getDestination());


            long result = db.insert(Constants.TB_NAME, Constants.ROW_ID, cv);
            if (result > 0) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            helper.close();
        }

        return false;
    }

    /*
     1. RETRIEVE SPACECRAFTS FROM DB AND POPULATE ARRAYLIST
     2. RETURN THE LIST
     */

    public ArrayList<Spacecraft> retrieveSpacecrafts()
    {
        ArrayList<Spacecraft> spacecrafts=new ArrayList<>();

        String[] columns={Constants.ROW_ID,Constants.NAME,Constants.PROPELLANT,Constants.DESTINATION};

        try
        {
            db = helper.getWritableDatabase();
            Cursor c=db.query(Constants.TB_NAME,columns,null,null,null,null,null);

            Spacecraft s;

            if(c != null)
            {
                while (c.moveToNext())
                {
                    String s_name=c.getString(1);
                    String s_propellant=c.getString(2);
                    String s_destination=c.getString(3);


                    s=new Spacecraft();
                    s.setName(s_name);
                    s.setPropellant(s_propellant);
                    s.setDestination(s_destination);

                    spacecrafts.add(s);
                }
            }

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


        return spacecrafts;
    }

}
 
[su_tab title="TableHelper"]
  • Binds our arraylist to our tableView.
package com.tutorials.hp.sqlitetableview;

import android.content.Context;
import android.widget.Toast;

import com.tutorials.hp.sqlitetableview.mDB.DBAdapter;
import com.tutorials.hp.sqlitetableview.mModel.Spacecraft;

import java.util.ArrayList;

import de.codecrafters.tableview.TableView;
import de.codecrafters.tableview.listeners.TableDataClickListener;
import de.codecrafters.tableview.toolkit.SimpleTableDataAdapter;
import de.codecrafters.tableview.toolkit.SimpleTableHeaderAdapter;

/**
 *   12/1/2016 for ProgrammingWizards Channel and http://www.camposha.info.
 */
public class TableHelper {



    Context c;

    private String[] spaceProbeHeaders={"Name","Propellant","Destination"};
    private String[][] spaceProbes;

    public TableHelper(Context c) {
        this.c = c;
    }

    public String[] getSpaceProbeHeaders()
    {
        return spaceProbeHeaders;
    }


    public  String[][] getSpaceProbes()
    {
        ArrayList<Spacecraft> spacecrafts=new DBAdapter(c).retrieveSpacecrafts();
        Spacecraft s;

        spaceProbes= new String[spacecrafts.size()][3];

        for (int i=0;i<spacecrafts.size();i++) {

             s=spacecrafts.get(i);

            spaceProbes[i][0]=s.getName();
            spaceProbes[i][1]=s.getPropellant();
            spaceProbes[i][2]=s.getDestination();
        }

        return spaceProbes;





    }
}





 

ActivityMain.xml

  • Template layout.
  • Contains our ContentMain.xml.
  • Also defines the appbarlayout, toolbar as well as floatingaction buttton.
<?xml version="1.0" encoding="utf-8"?>
<android.support.design.widget.CoordinatorLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:fitsSystemWindows="true"
    tools:context="com.tutorials.hp.sqlitetableview.MainActivity">

    <android.support.design.widget.AppBarLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:theme="@style/AppTheme.AppBarOverlay">

        <android.support.v7.widget.Toolbar
            android:id="@+id/toolbar"
            android:layout_width="match_parent"
            android:layout_height="?attr/actionBarSize"
            android:background="?attr/colorPrimary"
            app:popupTheme="@style/AppTheme.PopupOverlay" />

    </android.support.design.widget.AppBarLayout>

    <include layout="@layout/content_main" />

    <android.support.design.widget.FloatingActionButton
        android:id="@+id/fab"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_gravity="bottom|end"
        android:layout_margin="@dimen/fab_margin"
        android:src="@android:drawable/ic_dialog_email" />

</android.support.design.widget.CoordinatorLayout>
 

ContentMain.xml

  • Content Layout.
  • Defines the views and widgets to be displayed inside the MainActivity.
  • In this case its a simple tableview.
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    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"
    app:layout_behavior="@string/appbar_scrolling_view_behavior"
    tools:context="com.tutorials.hp.sqlitetableview.MainActivity"
    tools:showIn="@layout/activity_main">

    <de.codecrafters.tableview.TableView
        android:id="@+id/tableView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        />
</RelativeLayout>
 
[su_tab title="dialog_layout.xml"]
  • This will be inflated into our nice material input dialog for inserting data to sqlite.
<?xml version="1.0" encoding="utf-8"?>

    <android.support.v7.widget.CardView xmlns:android="http://schemas.android.com/apk/res/android"
        android:orientation="horizontal" android:layout_width="500dp"
        xmlns:card_view="http://schemas.android.com/apk/res-auto"
        android:layout_margin="1dp"
        card_view:cardCornerRadius="10dp"
        card_view:cardElevation="5dp"

        android:layout_height="match_parent">



    <LinearLayout
            android:layout_width="match_parent"
            android:orientation="vertical"
            android:layout_height="match_parent">

        <!--INPUT VIEWS-->
        <android.support.design.widget.TextInputLayout
            android:id="@+id/nameLayout"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <EditText
                android:id="@+id/nameEditTxt"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:singleLine="true"
                android:hint= "Name" />
        </android.support.design.widget.TextInputLayout>

        <android.support.design.widget.TextInputLayout
            android:id="@+id/propLayout"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <EditText
                android:id="@+id/propEditTxt"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:singleLine="true"
                android:hint= "Propellant" />
        </android.support.design.widget.TextInputLayout>

        <android.support.design.widget.TextInputLayout
            android:id="@+id/destLayout"
            android:layout_width="match_parent"
            android:layout_height="wrap_content">

            <EditText
                android:id="@+id/destEditTxt"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:singleLine="true"
                android:hint= "Destination" />
        </android.support.design.widget.TextInputLayout>

        <!--BUTTON-->
        <Button android:id="@+id/saveBtn"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:text="Save"
            android:clickable="true"
            android:background="@color/colorAccent"
            android:layout_marginTop="40dp"/>


</LinearLayout>

    </android.support.v7.widget.CardView>

 
[su_tab title="Video/Preview"]
  • Video version of this tutorial. Coming soon...
https://www.youtube.com/watch?v=5W5Er2FCXrQ&lc=z235gbcoarqbzpgpf04t1aokgrztfehzt1na2rpjnvuurk0h00410

Download

  • Download the Project below:
Download

How To Run

  1. Download the project above.
  2. You'll get a zipped file,extract it.
  3. Open the Android Studio.
  4. Now close, already open project.
  5. From the Menu bar click on File >New> Import Project.
  6. Now Choose a Destination Folder, from where you want to import project.
  7. Choose an Android Project.
  8. Now Click on “OK“.
  9. Done, your done importing the project,now edit it.

Conclusion.

This was a simple android draggabletreeview example. How to bind data using simpletreeadapter, add treenodes and nest them both programmatically and at runtime.

More

YouTube

  • Visit our channel for more examples like these.

Facebook

Oclemy,Cheers.