AutocompleteTextview items from SQLite database

AutoCompleteTextView is yet another basic controls in Android. The main purpose is to provide a suggestion list while you type on the textbox (as in the image below). This time instead of listing a regular ArrayList, I will show how to populate the list from a offline database table  through SQLite.

Screenshot_1488288146

Starts by creating a new project, create a BASIC PROJECT. –> the tutorial is here http://blog.kerul.net/2016/12/creating-new-android-studio-project.html .

readmorebutton

The interface layout as in content_main.xml .

<?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:id="@+id/content_main"
    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="net.kerul.stationlist.MainActivity"
    tools:showIn="@layout/activity_main">

    <AutoCompleteTextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/fromstation"
        android:layout_alignParentTop="true"
        android:layout_alignParentRight="true"
        android:layout_alignParentEnd="true"
        android:layout_toEndOf="@+id/textView" />

    <TextView
        android:text="FROM"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:id="@+id/textView" />

    <TextView
        android:text="TO"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/fromstation"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true"
        android:layout_marginTop="11dp"
        android:id="@+id/textView2" />

    <AutoCompleteTextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/fromstation"
        android:layout_alignParentRight="true"
        android:layout_alignParentEnd="true"
        android:id="@+id/tostation"
        android:layout_alignLeft="@+id/fromstation"
        android:layout_alignStart="@+id/fromstation" />
</RelativeLayout>

 

The database helper class as in MyDB.java

package net.kerul.stationlist;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class MyDB extends SQLiteOpenHelper {
    final protected static String DATABASE_NAME="KTM";//define DB name
    public MyDB(Context context) {
        super(context, DATABASE_NAME, null,1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {//1st time install auto create DB

        //here is the database definition with some data to insert

        //CREATE TABLE entry for table Stations
        db.execSQL("CREATE TABLE Stations (ID TEXT, Name TEXT, PRIMARY KEY(ID));");
        //CREATE TABLE entry for table Train_run
        db.execSQL("CREATE TABLE Train_run (ID	INTEGER, from_station_ID TEXT, to_station_ID TEXT, ticket_price REAL,PRIMARY KEY(ID));");

        //data entry (Table Stations)
        db.execSQL("INSERT INTO Stations VALUES ('KA 02','Kuala Lumpur')");
        db.execSQL("INSERT INTO Stations VALUES ('KA 03','Bank Negara')");
        db.execSQL("INSERT INTO Stations VALUES ('KA 04','Putra')");
        db.execSQL("INSERT INTO Stations VALUES ('KA 05','Segambut');");
        db.execSQL("INSERT INTO Stations VALUES ('KA 06','Kepong');");
        db.execSQL("INSERT INTO Stations VALUES ('KA 07','Kepong Sentral');");
        db.execSQL("INSERT INTO Stations VALUES ('KA 08','Sungai Buloh');");
        db.execSQL("INSERT INTO Stations VALUES ('KA 09','Kuang');");
        db.execSQL("INSERT INTO Stations VALUES ('KA 10','Rawang');");
        db.execSQL("INSERT INTO Stations VALUES ('KA 11','Serendah')");
        db.execSQL("INSERT INTO Stations VALUES ('KA 12','Batang Kali');");
        db.execSQL("INSERT INTO Stations VALUES ('KA 13','Rasa');");
        db.execSQL("INSERT INTO Stations VALUES ('KA 14','K. Kubu Bharu');");
        db.execSQL("INSERT INTO Stations VALUES ('KA 15','Tg. Malim');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 01','Midvalley');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 02','Seputeh');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 03','Salak Selatan');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 04','Bandar Tasik Selatan');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 05','Serdang');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 06','Kajang');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 07','UKM');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 08','Bangi');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 09','Batang Benar');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 10','Nilai');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 11','Labu');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 12','Tiroi');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 13','Seremban');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 14','Senawang');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 15','Sg Gadut');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 16','Rembau')");
        db.execSQL("INSERT INTO Stations VALUES ('KB 17','Tampin');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 18','Btg Melaka');");
        db.execSQL("INSERT INTO Stations VALUES ('KB 19','Gemas');");
        db.execSQL("INSERT INTO Stations VALUES ('KC 01','Sentul');");
        db.execSQL("INSERT INTO Stations VALUES ('KC 02','Batu Kentonmen');");
        db.execSQL("INSERT INTO Stations VALUES ('KC 03','Kampung Batu')");
        db.execSQL("INSERT INTO Stations VALUES ('KA 01','KL Sentral');");
        db.execSQL("INSERT INTO Stations VALUES ('KC 04','Taman Wahyu');");
        db.execSQL("INSERT INTO Stations VALUES ('KC 05','Batu Caves');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 01','Angkasapuri');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 02','Pantai Dalam');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 03','Petaling');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 04','Jalan Templer');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 05','Kg. Dato Harun');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 06','Seri Setia');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 07','Setia Jaya');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 08','Subang Jaya');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 09','Batu Tiga');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 10','Shah Alam');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 11','Padang Jawa');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 12','Bukit Badak');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 13','Klang');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 14','Teluk Pulai');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 15','Teluk Gadong');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 16','Kg. Raja Uda');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 17','Jalan Kastam');");
        db.execSQL("INSERT INTO Stations VALUES ('KD 18','Pelabuhan Klang');");

        //data entry (Table Train_run)
        db.execSQL("INSERT INTO Train_run VALUES (1,'KD 18','KD 18',0.0);");
        db.execSQL("INSERT INTO Train_run VALUES (2,'KD 18','KD 17',1.5);");
        db.execSQL("INSERT INTO Train_run VALUES (3,'KD 18','KD 16',1.9);");
        db.execSQL("INSERT INTO Train_run VALUES (4,'KD 18','KD 15',1.9);");
        db.execSQL("INSERT INTO Train_run VALUES (5,'KD 18','KD 14',1.9);");
        db.execSQL("INSERT INTO Train_run VALUES (6,'KD 18','KD 13',2.3);");
        db.execSQL("INSERT INTO Train_run VALUES (7,'KD 18','KD 12',2.3);");
        db.execSQL("INSERT INTO Train_run VALUES (8,'KD 18','KD 11',2.8);");
        db.execSQL("INSERT INTO Train_run VALUES (9,'KD 18','KD 10',3.3);");
        db.execSQL("INSERT INTO Train_run VALUES (10,'KD 18','KD 9',3.9);");
        db.execSQL("INSERT INTO Train_run VALUES (11,'KD 18','KD 8',4.3);");
        db.execSQL("INSERT INTO Train_run VALUES (12,'KD 18','KD 7',4.7);");
        db.execSQL("INSERT INTO Train_run VALUES (13,'KD 18','KD 6',4.8);");
        db.execSQL("INSERT INTO Train_run VALUES (14,'KD 18','KD 5',4.9);");
        db.execSQL("INSERT INTO Train_run VALUES (15,'KD 18','KD 4',5.3);");
        db.execSQL("INSERT INTO Train_run VALUES (16,'KD 18','KD 3',5.4);");
        db.execSQL("INSERT INTO Train_run VALUES (17,'KD 18','KD 2',5.6);");
        db.execSQL("INSERT INTO Train_run VALUES (18,'KD 18','KD 1',5.9);");
        db.execSQL("INSERT INTO Train_run VALUES (19,'KD 18','KA 01',6.4);");
        db.execSQL("INSERT INTO Train_run VALUES (20,'KD 18','KA 02',6.5);");
        db.execSQL("INSERT INTO Train_run VALUES (21,'KD 18','KA 03',6.7);");
        db.execSQL("INSERT INTO Train_run VALUES (22,'KD 18','KA 04',6.9);");
        db.execSQL("INSERT INTO Train_run VALUES (23,'KD 18','KC 01',7.1);");
        db.execSQL("INSERT INTO Train_run VALUES (24,'KD 18','KC 02',7.1);");
        db.execSQL("INSERT INTO Train_run VALUES (25,'KD 18','KC 03',7.2);");
        db.execSQL("INSERT INTO Train_run VALUES (26,'KD 18','KC 04',7.4);");
        db.execSQL("INSERT INTO Train_run VALUES (27,'KD 18','KC 05',7.7);");
        db.execSQL("INSERT INTO Train_run VALUES (28,'KD 18','KA 15',15.1);");
        db.execSQL("INSERT INTO Train_run VALUES (29,'KD 18','KA 14',12.7);");
        db.execSQL("INSERT INTO Train_run VALUES (30,'KD 18','KA 13',11.9);");
        db.execSQL("INSERT INTO Train_run VALUES (31,'KD 18','KA 12',11.5);");
        db.execSQL("INSERT INTO Train_run VALUES (32,'KD 17','KD 18',1.1);");

    }//onCreate


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion){//renew DB if DB upgraded to new version
            //do something if database is updated
            db.execSQL("DROP TABLE IF EXISTS Stations");
            db.execSQL("DROP TABLE IF EXISTS Train_run");
            onCreate(db);
        }
    }//onUpgrade
}//end MyDB database helper

And the MainActivity.java is below. Bear in mind that the description of what the coding do are available in the comment.

package net.kerul.stationlist;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.view.View;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.ArrayAdapter;
import android.widget.AutoCompleteTextView;

import java.util.ArrayList;

public class MainActivity extends AppCompatActivity {
    private AutoCompleteTextView fromstation, tostation;
    private MyDB mydb;
    private SQLiteDatabase db;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);

        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();
            }
        });

        //UI the AutoCompleteTextView
        fromstation = (AutoCompleteTextView)findViewById(R.id.fromstation);
        tostation = (AutoCompleteTextView)findViewById(R.id.tostation);

        //create the ArrayList from database
        mydb = new MyDB(this);
        db = mydb.getWritableDatabase();//connect to MyDB
        //
        final String [] mydata;
        ArrayList<String> array = new ArrayList<>();
        //Inside the method you've read the cursor, loop through it and add those item to array
        String sql="SELECT * FROM Stations";
        //execute SQL
        Cursor cr = db.rawQuery(sql, null);
        cr.moveToFirst();//cursor pointing to first row
        mydata = new String[cr.getCount()];//create array string based on numbers of row
        int i=0;
        do  {
            mydata[i] = cr.getString(1);//insert new stations to the array list
            //Log.i("ArrayList",mydata[i]);
            i++;
        }while(cr.moveToNext());
        //Finally Set the adapter to AutoCompleteTextView like this,
        ArrayAdapter<String> adapter = new ArrayAdapter<String>(this,
                        android.R.layout.simple_dropdown_item_1line, mydata);
        //populate the list to the AutoCompleteTextView controls
        fromstation.setAdapter(adapter);
        tostation.setAdapter(adapter);

    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.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);
    }
}

Popular Posts