askIT

Populate listview from SQLite database (Android SQLite)

media-floppy.png Download Android project file

We can populate a listview in Android with data from a SQLite table. In this example we have a table that consists of continens in the world. We want to print this table as a list.

table.png
Figure: Table continens.

populate_listview_from_sqlite_database.png
Figure: ListView in Android.

You need to add the following files:


res\layout\continents_list.xml

This is the design for the list view for Android.

<?xml version="1.0" encoding="utf-8"?>
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:fadingEdgeLength="0dp"
    android:fillViewport="true"
    android:overScrollMode="never"
    android:scrollbars="none" >

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >

        <ListView
            android:id="@+id/listViewContinents"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:dividerHeight="1dp"
            android:padding="10dp" >
        </ListView>

    </RelativeLayout>
</ScrollView>

res\layout\continents_list_items.xml

This layout file decides how each list item should look.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="match_parent" >

    <TextView
        android:id="@+id/textViewListName"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="List item"
        android:textAppearance="?android:attr/textAppearanceLarge"
        android:layout_marginTop="10dp"
        android:layout_marginBottom="10dp"
        android:layout_marginLeft="10dp" />
    <TextView
        android:id="@+id/textViewListNumber"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="10dp"
        android:layout_marginRight="10dp"
        android:text="Number"
        android:textAppearance="?android:attr/textAppearanceLarge"
        android:layout_alignParentEnd="true"
        android:layout_alignBottom="@+id/textViewListName" />
</RelativeLayout>

java\com.nettport.globe\DBAdapter.java

The database adapter will create the table needed, and contains methods for insert and select query into the SQLite database.

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class DBAdapter {

    /* 01 Variables ---------------------------------------- */
    private static final String databaseName = "globe";
    private static final int databaseVersion = 6;

    /* 02 Database variables ------------------------------- */
    private final Context context;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;


    /* 03 Class DbAdapter ---------------------------------- */
    public DBAdapter(Context ctx){
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }

    /* 04 DatabaseHelper ------------------------------------ */
    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context){
            super(context, databaseName, null, databaseVersion);
        }

        @Override
        public void onCreate(SQLiteDatabase db){
            try{
                // Create table goal
                db.execSQL("CREATE TABLE IF NOT EXISTS continens (" +
                        " _id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        " c_id INTEGER, " +
                        " c_name VARCHAR);");
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {


            // ! All tables that are going to be dropped need to be listed here
            db.execSQL("DROP TABLE IF EXISTS continens");
            onCreate(db);

            String TAG = "Tag";
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");

        } // end public void onUpgrade
    } // DatabaseHelper


    /* 05 Open database --------------------------------------------------------- */
    public DBAdapter open() throws SQLException {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    /* 06 Close database --------------------------------------------------------- */
    public void close() {
        DBHelper.close();
    }

    /* 07 Quote smart ------------------------------------------------------------ */
    public String quoteSmart(String value){
        // Is numeric?
        boolean isNumeric = false;
        try {
            double myDouble = Double.parseDouble(value);
            isNumeric = true;
        }
        catch(NumberFormatException nfe) {
            System.out.println("Could not parse " + nfe);
        }
        if(isNumeric == false){
            // Escapes special characters in a string for use in an SQL statement
            if (value != null && value.length() > 0) {
                value = value.replace("\", "\\");
                value = value.replace("'", "\'");
                value = value.replace("", "\0");
                value = value.replace("
", "\n");
                value = value.replace("
", "\r");
                value = value.replace("\"", "\\"");
                value = value.replace("\x1a", "\Z");
            }
        }

        value = "'" + value + "'";

        return value;
    }
    public double quoteSmart(double value) {
        return value;
    }
    public int quoteSmart(int value) {
        return value;
    }

    /* 08 Insert data ------------------------------------------------------------ */
    public void insert(String table, String fields, String values){

        try {
            db.execSQL("INSERT INTO " + table +  "(" + fields + ") VALUES (" + values + ")");
        }
        catch(SQLiteException e){
            System.out.println("Insert error: " + e.toString());
        }
    }

    /* 09 Count ------------------------------------------------------------------ */
    public int count(String table)
    {
        try {
            Cursor mCount = db.rawQuery("SELECT COUNT(*) FROM " + table + "", null);
            mCount.moveToFirst();
            int count = mCount.getInt(0);
            mCount.close();
            return count;
        }
        catch(SQLiteException e){
            return -1;
        }

    }

    /* 10 Select ----------------------------------------------------------------- */
    // Select
    public Cursor select(String table, String[] fields) throws SQLException
    {
        /* Select example:
                Cursor allCategories;
        String fields[] = new String[] {
                "category_id",
                "category_name",
                "category_parent_id"
        };
        allCategories = db.selectAllWhere("categories", fields);
         */
        Cursor mCursor = db.query(table, fields, null, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    // Select All where (String)
    public Cursor select(String table, String[] fields, String whereClause, String whereCondition) throws SQLException
    {
        /* Select example:
                Cursor allCategories;
        String fields[] = new String[] {
                "category_id",
                "category_name",
                "category_parent_id"
        };
        allCategories = db.selectAllWhere("categories", fields, "category_parent_id", "0");
         */
        Cursor mCursor = db.query(table, fields, whereClause + "=" + whereCondition, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    // Select All where (Long)
    public Cursor select(String table, String[] fields, String whereClause, long whereCondition) throws SQLException {
        /* Select example:
        long row = 3;
        String fields[] = new String[] {
                "food_id",
                "food_name",
                "food_manufactor_name"
        };
        allCategories = db.selectAllWhere("categories", fields, "category_parent_id", row);
        displayRecordFromNotes(c);
         */

        Cursor mCursor = db.query(table, fields, whereClause + "=" + whereCondition, null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    /* 11 Update ----------------------------------------------------------------- */
    public boolean update(String table, String primaryKey, long rowId, String field, String value) {
        /* Update example:
        long id = 1;
        String value = "xxt@doesthiswork.com";
        String valueSQL = db.quoteSmart(value);
        db.update("users", "user_id", id, "user_email", valueSQL);
         */

        // Remove first and last value of value
        value = value.substring(1, value.length()-1); // removes \' after running quote smart

        ContentValues args = new ContentValues();
        args.put(field, value);
        return db.update(table, args, primaryKey + "=" + rowId, null) > 0;
    }
    public boolean update(String table, String primaryKey, long rowId, String field, double value) {
        ContentValues args = new ContentValues();
        args.put(field, value);
        return db.update(table, args, primaryKey + "=" + rowId, null) > 0;
    }
    public boolean update(String table, String primaryKey, long rowId, String field, int value) {
        ContentValues args = new ContentValues();
        args.put(field, value);
        return db.update(table, args, primaryKey + "=" + rowId, null) > 0;
    }

}

java\com.nettport.globe\ContinentsCursorAdapter.java

The cursor adapter takes the cursor from the database and places it into the xml layout file.

import android.content.Context;
import android.database.Cursor;
import android.support.v4.widget.CursorAdapter;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;

/**
* Created by bruker on 20.06.2017.
*/


public class ContinentsCursorAdapter extends CursorAdapter {
        public ContinentsCursorAdapter(Context context, Cursor cursor) {
                super(context, cursor, 0);
        }

        // The newView method is used to inflate a new view and return it,
        // you dont bind any data to the view at this point.
        @Override
        public View newView(Context context, Cursor cursor, ViewGroup parent) {
                return LayoutInflater.from(context).inflate(R.layout.continents_list_items, parent, false);
        }

        // The bindView method is used to bind all data to a given view
        // such as setting the text on a TextView.
        @Override
        public void bindView(View view, Context context, Cursor cursor) {
                // Find fields to populate in inflated template
                TextView textViewListName = (TextView) view.findViewById(R.id.textViewListName);
                TextView textViewListNumber = (TextView) view.findViewById(R.id.textViewListNumber);

                // Extract properties from cursor
                int getID = cursor.getInt(cursor.getColumnIndexOrThrow("_id"));
                String getName = cursor.getString(cursor.getColumnIndexOrThrow("c_name"));

                // Populate fields with extracted properties
                textViewListName.setText(getName);
                textViewListNumber.setText(String.valueOf(getID));
        }
}

java\com.nettport.globe\Continents.java

Continents file takes data from the database and inserts it into the adapter.

import android.content.Context;
import android.database.Cursor;
import android.os.Bundle;
import android.support.v4.widget.CursorAdapter;
import android.support.v7.app.AppCompatActivity;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

/**
* Created by bruker on 20.06.2017.
*/


public class Continents extends AppCompatActivity {

        @Override
        protected void onCreate(Bundle savedInstanceState) {
                super.onCreate(savedInstanceState);
                setContentView(R.layout.continents_list);



                /* Database */
                DBAdapter db = new DBAdapter(this);
                db.open();

                // Cursor
                String fields[] = new String[] {
                                "_id",
                                "c_name"
                };

                Cursor continensCursor = db.select("continens", fields);
                /*
                int continensCount = continensCursor.getCount();
                for(int x=0;x<continensCount;x++) {
                        display(continensCursor);
                        continensCursor.moveToNext();
                }
                */


                // Find ListView to populate
                ListView lvItems = (ListView) findViewById(R.id.listViewContinents);


                // Setup cursor adapter using cursor from last step
                ContinentsCursorAdapter continentsAdapter = new ContinentsCursorAdapter(this, continensCursor);

                // Attach cursor adapter to the ListView
                lvItems.setAdapter(continentsAdapter); // uses ContinensCursorAdapter



                db.close();



        }

        // Print notes table content
        private void display(Cursor inpCursor) {
                Toast.makeText(this,
                                "Id: " + inpCursor.getString(0) + "\n" +
                                                "Name: " + inpCursor.getString(1),
                                Toast.LENGTH_LONG).show();
        } // end private void displayRecordFromNotes


}

java\com.nettport.globe\MainActivity.java

MainActivity checks if there are any data in the datbase table. If not it creates some. After it moves the activity to continents.

import android.content.Intent;
import android.database.sqlite.SQLiteException;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.Toast;

import com.facebook.stetho.Stetho;
import com.facebook.stetho.okhttp3.StethoInterceptor;

import okhttp3.OkHttpClient;

public class MainActivity extends AppCompatActivity {

        @Override
        protected void onCreate(Bundle savedInstanceState) {
                super.onCreate(savedInstanceState);
                setContentView(R.layout.activity_main);


                /* Stetho */
                Stetho.initializeWithDefaults(this);

                new OkHttpClient.Builder()
                                .addNetworkInterceptor(new StethoInterceptor())
                                .build();

                /* Insert to database */
                dbInsertSetup();

                /* Go to continents */
                Intent i = new Intent(MainActivity.this, Continents.class);
                startActivity(i);

        }

        /*- Db insert setup -----------------------------------------------------------------------*/
        private void dbInsertSetup(){

                /* Database */
                DBAdapter db = new DBAdapter(this);
                db.open();

                /* Setup for continents */
                // Count rows in continents
                int numberRows = db.count("continens");

                if(numberRows < 1){
                        // Run setup
                        Toast.makeText(this, "Loading setup...", Toast.LENGTH_LONG).show();


                        db.insert("continens", "_id, c_name", "NULL, 'Asia'");
                        db.insert("continens", "_id, c_name", "NULL, 'Africa'");
                        db.insert("continens", "_id, c_name", "NULL, 'Antarctica'");
                        db.insert("continens", "_id, c_name", "NULL, 'Australia'");
                        db.insert("continens", "_id, c_name", "NULL, 'Europe'");
                        db.insert("continens", "_id, c_name", "NULL, 'North America'");
                        db.insert("continens", "_id, c_name", "NULL, 'South America'");
                }

                db.close();
        }

}

build.gradle

To be able to use stetho to inspect tables you need to add dependencies.

dependencies {
        compile 'com.facebook.stetho:stetho:1.2.0'
        compile 'com.facebook.stetho:stetho-okhttp3:1.4.1'
}

Add comment

avatar_blank_60.png

Comment:

Name:

Email:

Comments

No comments yet. You can be the first one to comment. Just write your comment in the form and click on the submit button.