ListView with Sqlite Data and ListItem Listener

ListView with Sqlite Data and ListItem Listener

Expected Output

In my example, You would need 2 layout

main.xml
 

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
  android:orientation="vertical"
  android:layout_width="fill_parent"
  android:layout_height="fill_parent">
  
 <ListView android:layout_width="fill_parent" 
   android:layout_height="fill_parent" 
   android:id="@+id/mainListView">
 </ListView>
 
 
         <TextView
            android:id="@+id/tvList"
            android:layout_width="fill_parent"
            android:layout_height="wrap_content"
            android:layout_marginTop="20dp"
     />
 
</LinearLayout>

simplerow.xml

<TextView xmlns:android="http://schemas.android.com/apk/res/android"
 android:id="@+id/rowTextView" 
 android:layout_width="fill_parent" 
 android:layout_height="wrap_content"
 android:padding="10dp"
 android:textSize="16sp" >
</TextView>

strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="hello">SimpleListViewActivity!</string>
    <string name="app_name">SimpleListView with MySqlite data</string>
</resources>

SimpleListViewActivity.java


create a method call queryTable  returns an ArrayList

 ArrayList<String> queryTable(String sql)

package com.windrealm.android;

import java.util.ArrayList;
import java.util.Arrays;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.text.Html;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SimpleListViewActivity extends Activity {
  
  private ListView mainListView ;
  private ArrayAdapter<String> listAdapter ;
  SQLiteDatabase db ;
  boolean noData= true;
 
   
  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    
 //Create Database and Table 
 
 String sql="create table if not exists FavoritePlaces (recId integer PRIMARY KEY autoincrement, PlaceName text, latitude text, longitude text, mapURL text)";
 String result = createDatabase(sql,"FavPlaces.db");  
 
    updateTable("delete from FavoritePlaces");
 
 //Check if there is existing data in the sqlite db
 
   checkData();
   if (noData)     //if there is no data then add
 {
 addData("TP");
 addData("Tampines");
 addData("Pasir Ris");
 addData("Pungol");
 addData("Seng Kang");
 addData("MDAD");
 addData("DMSD");
 addData("ASP");
 addData("WWW");
 addData("XXX");
 addData("YYY");
 addData("YY11YY");
 addData("YY22YY");
 addData("YY33YY");
 addData("YY44YY");
 addData("YY55YY");
 }
    
        // Find the ListView resource. 
    mainListView = (ListView) findViewById( R.id.mainListView );
    
    ArrayList<String> placeList = new ArrayList<String>();
 
    placeList = queryTable("select placeName from FavoritePlaces " );
    // Create ArrayAdapter using the placeList.
    listAdapter = new ArrayAdapter<String>(this, R.layout.simplerow, placeList);
    
    // Set the ArrayAdapter as the ListView's adapter.
    mainListView.setAdapter( listAdapter );  
    
    // Set an item click listener for ListView
    mainListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
  

  @Override
  public void onItemClick(AdapterView<?> parent, View view, int position,
    long id) {
   // TODO Auto-generated method stub
            // Get the selected item text from ListView
    String selectedItem = (String) parent.getItemAtPosition(position);

            // Display the selected item text on Toast
   
       
    Toast.makeText(getApplicationContext(), "Your favorite : " + selectedItem,Toast.LENGTH_LONG).show();
  }
    });
    
    
  }
  
  void checkData()
  {
   queryTable("select placeName from FavoritePlaces " );
     
  }
  
  
  void addData(String pName)
  {
   
   String sql = "insert into FavoritePlaces (PlaceName,latitude,longitude,mapURL) values('"+pName+"','"+123+"','"+456+"','"+"www.tp.edu.sg"+"')";
   
    updateTable(sql);
    
   
  }
  

  
  String createDatabase(String sql,String dbName)
 {
   //Create SQLite database below
  try{
   
   System.out.println(sql); //Print to console for debugging
   
   db = SQLiteDatabase.openOrCreateDatabase("sdcard/"+dbName,null);
   db.beginTransaction();
   db.execSQL(sql);
   db.setTransactionSuccessful();
   db.endTransaction();
  }
  catch (Exception e) {
   Toast.makeText(this,e.getMessage(),Toast.LENGTH_LONG).show();
     
   System.out.println(e.toString());
   return ("error open DB");
  }
  
  return "DB"+dbName+" Created...";

   
 } //end of createDatabase method
 
 
  //SQL statements for Insert, Delete , Update
 String updateTable(String sql)
 {
  try{

   System.out.println(sql);
   db.beginTransaction();
   db.execSQL(sql);
   db.setTransactionSuccessful();
   db.endTransaction();
   
  }catch (Exception e) { System.out.println(e.toString());
   return ("Error updating DB");
  }  
  return ("DB updated");  
 }
  
 
 ArrayList<String> queryTable(String sql)

{

  //queryTable("Select placeName from FavoritePlaces order by recId desc");
  
   ArrayList<String> placeNameList = new ArrayList<String>();
  
  try{ 
   System.out.println(sql);
  
     Cursor cursor = db.rawQuery(sql,null);
   while (cursor.moveToNext()) {
       
    noData =false;  //there is data found
    
    String placeName = cursor.getString(cursor.getColumnIndex("PlaceName"));
     
    placeNameList.add(placeName);  //Add to ArrayList 
             
   }
       
   
    
  }
  catch (Exception e) {
   System.out.println(e.toString());
   return  placeNameList;
  }
  
  if (noData) 
  { 
    
   return null; 
  }
  else
  return placeNameList ;

   
 }
 
 
 
 
 
 
 
// SQL query statement or Select statement
 String queryTable(String sql, TextView displayResults)
 {
  boolean noData= true;
  

  String htmlText ="<b><pre>Name  Latitude  Longitude  map Link </pre></b><br>";
     
  try{ 
   System.out.println(sql);
   displayResults.setText("");  //clear text from display
  
   Cursor cursor = db.rawQuery(sql,null);
   while (cursor.moveToNext()) {
       
    
    noData =false;  //there is data found
    
    String placeName = cursor.getString(cursor.getColumnIndex("PlaceName"));
    String latitude = cursor.getString(cursor.getColumnIndex("latitude"));
    String longitude = cursor.getString(cursor.getColumnIndex("longitude")); 
    
    String mapUrl = "    <a href='http://maps.google.com/maps?q="+latitude+","+longitude+"'> Maps </a>    ";
    htmlText +=placeName+" "+latitude+" "+longitude +" "+mapUrl+"<br>";
         
        
   }
       
   
   System.out.println(htmlText);
   displayResults.append(Html.fromHtml( htmlText));
  }
  catch (Exception e) {
   System.out.println(e.toString());
   return ("Error Retrieving DB");
  }
  
  if (noData) 
  { 
    
   return("Not found"); 
  }
  else
  return ("Search Results");

   
 }
}

DOWNLOAD