askIT

Android Listview from SQLite in fragment

media-floppy.png Download Android project file

listview_from_sqlite_in_fragment.png
Figure: List of countries from a SQLite database

res\layout\fragment_yourfragment.xml

<FrameLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context="com.nettport.dietbystram.dietbystram.CategoriesFragment">

    <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/listViewEurope"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:dividerHeight="1dp"
                android:padding="10dp" >
            </ListView>

        </RelativeLayout>

    </ScrollView>

</FrameLayout>

java\...Fragment.class

At the top create a variable for the cursor.


public class ...Fragment extends Fragment {
        /*- Class Variables ---------------------------------------------------------------*/
        private Cursor listCursor;

Create a method for on Activity Created


        /*- on Activity Created --------------------------------------------------------- */
        @Override
        public void onActivityCreated(Bundle savedInstanceState) {
                super.onActivityCreated(savedInstanceState);

                // Populate the list of categories
                populateList();


        } // onActivityCreated

Create a method for populate List


        /*- populate List -------------------------------------------------------------- */
        public void populateList(){

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

                // Get categories
                String fields[] = new String[] {
                                "_id",
                                "c_name"
                };
                // listCursor = db.select("countries", fields); // Gives all countries
                listCursor = db.select("countries", fields, "c_continent", "'Europe'"); // Only Europe

                // Createa a array
                ArrayList<String> values = new ArrayList<String>();

                // Convert categories to string
                int categoriesCount = listCursor.getCount();
                for(int x=0;x < categoriesCount;x++){

                        values.add(listCursor.getString(listCursor.getColumnIndex("c_name")));

                        listCursor.moveToNext();
                }

                // Close cursor
                // categoriesCursor.close();

                // Create adapter
                ArrayAdapter<String> adapter = new ArrayAdapter<String>(getActivity(),
                        android.R.layout.simple_list_item_1, values);

                // Set Adapter
                ListView lv = (ListView)getActivity().findViewById(R.id.listViewEurope);
                lv.setAdapter(adapter);

                // OnClick
                lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {

                        @Override
                        public void onItemClick(AdapterView arg0, View arg1, int arg2, long arg3) {
                                listItemClicked(arg2);
                        }
                });

                // Close db
                db.close();
        } // populateList

Create a method for listItemClicked

        /*- List item clicked ------------------------------------------------------------ */
        public void listItemClicked(int listItemIDClicked){

                // Move cursor to ID clicked
                listCursor.moveToPosition(listItemIDClicked);

                // Get ID and name from cursor
                String id = listCursor.getString(0);
                String name = listCursor.getString(1);

        // Do something
                Toast.makeText(getActivity(), "ID: " + id + "\nName: " + name, Toast.LENGTH_SHORT).show();

        } // listItemClicked

java\DBAdapter.class

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;

import java.util.ArrayList;

public class DBAdapter {

    /* 01 Variables ---------------------------------------- */
    private static final String databaseName = "countries";
    private static final int databaseVersion = 3;

    /* 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 countries (" +
                        " _id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        " c_name VARCHAR, " +
                        " c_continent 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 countries");
            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
    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.select("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
    public Cursor select(String table, String[] fields, String whereClause, String whereCondition, String orderBy, String OrderMethod) throws SQLException
    {
                /* Select example:
                                Cursor allCategories;
                String fields[] = new String[] {
                                "category_id",
                                "category_name",
                                "category_parent_id"
                };
                allCategories = db.select("categories", fields, "category_parent_id", "0");
                */
        Cursor mCursor = db.query(table, fields, whereClause + "=" + whereCondition, null, null, null, orderBy + " " + OrderMethod, 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\MainActivity.class

import android.net.Uri;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v4.app.Fragment;
import android.support.v4.app.FragmentManager;
import android.view.View;
import android.support.design.widget.NavigationView;
import android.support.v4.view.GravityCompat;
import android.support.v4.widget.DrawerLayout;
import android.support.v7.app.ActionBarDrawerToggle;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.Toast;

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

import okhttp3.OkHttpClient;

public class MainActivity extends AppCompatActivity
        implements NavigationView.OnNavigationItemSelectedListener,
        Europe.OnFragmentInteractionListener{

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

        /* Inialize fragmet */
        Fragment fragment = null;
        Class fragmentClass = null;
        fragmentClass = Europe.class;
        try {
            fragment = (Fragment) fragmentClass.newInstance();
        } catch (Exception e) {
            e.printStackTrace();
        }

        FragmentManager fragmentManager = getSupportFragmentManager();
        fragmentManager.beginTransaction().replace(R.id.flContent, fragment).commit();



        /* Email icon */
        FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
        fab.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                Snackbar.make(view, "Replace with your own action", Snackbar.LENGTH_LONG)
                        .setAction("Action", null).show();
            }
        });

        /* Drawer */
        DrawerLayout drawer = (DrawerLayout) findViewById(R.id.drawer_layout);
        ActionBarDrawerToggle toggle = new ActionBarDrawerToggle(
                this, drawer, toolbar, R.string.navigation_drawer_open, R.string.navigation_drawer_close);
        drawer.setDrawerListener(toggle);
        toggle.syncState();

        NavigationView navigationView = (NavigationView) findViewById(R.id.nav_view);
        navigationView.setNavigationItemSelectedListener(this);


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

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

        /* Insert to database */
        dbInsertSetup();



    }


    /*- 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("countries");

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


            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Norway', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Sweden', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Denmark', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Iceland', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Germany', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Nederland', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Brazil', 'South America'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Austria', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Belgium', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Estonia', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Cyprus', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Czech Republic', 'Europe'");
            db.insert("countries", "_id, c_name, c_continent", "NULL, 'Croatia', 'Europe'");
        }

        db.close();
    }



    @Override
    public void onBackPressed() {
        DrawerLayout drawer = (DrawerLayout) findViewById(R.id.drawer_layout);
        if (drawer.isDrawerOpen(GravityCompat.START)) {
            drawer.closeDrawer(GravityCompat.START);
        } else {
            super.onBackPressed();
        }
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();

        //noinspection SimplifiableIfStatement
        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }

    @SuppressWarnings("StatementWithEmptyBody")
    @Override
    public boolean onNavigationItemSelected(MenuItem item) {
        // Handle navigation view item clicks here.
        int id = item.getItemId();

        if (id == R.id.nav_camera) {
            // Handle the camera action
        } else if (id == R.id.nav_gallery) {

        } else if (id == R.id.nav_slideshow) {

        } else if (id == R.id.nav_manage) {

        } else if (id == R.id.nav_share) {

        } else if (id == R.id.nav_send) {

        }

        DrawerLayout drawer = (DrawerLayout) findViewById(R.id.drawer_layout);
        drawer.closeDrawer(GravityCompat.START);
        return true;
    }

    @Override
    public void onFragmentInteraction(Uri uri) {
    }
}

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.