Monday, June 13, 2011

query SQLite database with sorting

Work on last exercise, "Edit row in SQLite database using SQLiteDatabase.update()". Modify to have option to quary order by default, adn to quary order by KEY_CONTENT1.

query SQLite database with sorting

Modify main.xml to add two key, sortbyDefault and sortby1.
<?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"
    >
<TextView  
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:text="@string/hello"
    />
<TextView  
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:text="Enter content of column 1"
    />
<EditText
    android:id="@+id/content1"
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    />
<TextView  
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:text="Enter content of column 2"
    />
<EditText
    android:id="@+id/content2"
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    />
<Button
    android:id="@+id/add"
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:text="Add"
    />
<Button
    android:id="@+id/deleteall"
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:text="Delete All"
    />
<LinearLayout
    android:orientation="horizontal"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content">
<Button
    android:id="@+id/sortbyDefault"
    android:layout_width="wrap_content" 
    android:layout_height="wrap_content" 
    android:layout_weight="1"
    android:text="Sort By default"
    />
<Button
    android:id="@+id/sortby1"
    android:layout_width="wrap_content" 
    android:layout_height="wrap_content" 
    android:layout_weight="1"
    android:text="Sort By Content 1"
    />
</LinearLayout>
<ListView
 android:id="@+id/contentlist"
 android:layout_width="fill_parent"
 android:layout_height="fill_parent"/>
</LinearLayout>


Modify SQLiteAdapter.java to add method queueAll_SortBy_CONTENT1(), simple call sqLiteDatabase.query(MYDATABASE_TABLE, columns, null, null, null, null, KEY_CONTENT1) indirectly.
package com.exercise.AndroidSQLite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class SQLiteAdapter {

 public static final String MYDATABASE_NAME = "MY_DATABASE";
 public static final String MYDATABASE_TABLE = "MY_TABLE";
 public static final int MYDATABASE_VERSION = 1;
 public static final String KEY_ID = "_id";
 public static final String KEY_CONTENT1 = "Content1";
 public static final String KEY_CONTENT2 = "Content2";

 //create table MY_DATABASE (ID integer primary key, Content text not null);
 private static final String SCRIPT_CREATE_DATABASE =
  "create table " + MYDATABASE_TABLE + " ("
  + KEY_ID + " integer primary key autoincrement, "
  + KEY_CONTENT1 + " text not null, "
  + KEY_CONTENT2 + " text not null);";
 
 private SQLiteHelper sqLiteHelper;
 private SQLiteDatabase sqLiteDatabase;

 private Context context;
 
 public SQLiteAdapter(Context c){
  context = c;
 }
 
 public SQLiteAdapter openToRead() throws android.database.SQLException {
  sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
  sqLiteDatabase = sqLiteHelper.getReadableDatabase();
  return this; 
 }
 
 public SQLiteAdapter openToWrite() throws android.database.SQLException {
  sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
  sqLiteDatabase = sqLiteHelper.getWritableDatabase();
  return this; 
 }
 
 public void close(){
  sqLiteHelper.close();
 }
 
 public long insert(String content1, String content2){
  
  ContentValues contentValues = new ContentValues();
  contentValues.put(KEY_CONTENT1, content1);
  contentValues.put(KEY_CONTENT2, content2);
  return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues);
 }
 
 public int deleteAll(){
  return sqLiteDatabase.delete(MYDATABASE_TABLE, null, null);
 }
 
 public void delete_byID(int id){
  sqLiteDatabase.delete(MYDATABASE_TABLE, KEY_ID+"="+id, null);
 }
 
 public void update_byID(int id, String v1, String v2){
  ContentValues values = new ContentValues();
  values.put(KEY_CONTENT1, v1);
  values.put(KEY_CONTENT2, v2);
  sqLiteDatabase.update(MYDATABASE_TABLE, values, KEY_ID+"="+id, null);
 }
 
 public Cursor queueAll(){
  String[] columns = new String[]{KEY_ID, KEY_CONTENT1, KEY_CONTENT2};
  Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, 
    null, null, null, null, null);
  
  return cursor;
 }
 
 public Cursor queueAll_SortBy_CONTENT1(){
  String[] columns = new String[]{KEY_ID, KEY_CONTENT1, KEY_CONTENT2};
  Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, 
    null, null, null, null, KEY_CONTENT1);
  
  return cursor;
 }
 
 public Cursor queueAll_SortBy_CONTENT2(){
  String[] columns = new String[]{KEY_ID, KEY_CONTENT1, KEY_CONTENT2};
  Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns, 
    null, null, null, null, KEY_CONTENT2);
  
  return cursor;
 }
 
 public class SQLiteHelper extends SQLiteOpenHelper {

  public SQLiteHelper(Context context, String name,
    CursorFactory factory, int version) {
   super(context, name, factory, version);
  }

  @Override
  public void onCreate(SQLiteDatabase db) {
   // TODO Auto-generated method stub
   db.execSQL(SCRIPT_CREATE_DATABASE);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   // TODO Auto-generated method stub

  }

 }
 
}


Modify AndroidSQLite.java to add Button and Button.OnClickListener() of buttonSortDefault and buttonSort1.
package com.exercise.AndroidSQLite;

import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.ViewGroup.LayoutParams;
import android.widget.AdapterView;
import android.widget.Button;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;

public class AndroidSQLite extends Activity {
 
 EditText inputContent1, inputContent2;
 Button buttonAdd, buttonDeleteAll;
 Button buttonSortDefault, buttonSort1;
 
 private SQLiteAdapter mySQLiteAdapter;
 ListView listContent;
 
 SimpleCursorAdapter cursorAdapter;
 Cursor cursor;
 
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        inputContent1 = (EditText)findViewById(R.id.content1);
        inputContent2 = (EditText)findViewById(R.id.content2);
        buttonAdd = (Button)findViewById(R.id.add);
        buttonDeleteAll = (Button)findViewById(R.id.deleteall);
        
        listContent = (ListView)findViewById(R.id.contentlist);

        mySQLiteAdapter = new SQLiteAdapter(this);
        mySQLiteAdapter.openToWrite();

        cursor = mySQLiteAdapter.queueAll();
        String[] from = new String[]{SQLiteAdapter.KEY_ID, SQLiteAdapter.KEY_CONTENT1, SQLiteAdapter.KEY_CONTENT2};
        int[] to = new int[]{R.id.id, R.id.text1, R.id.text2};
        cursorAdapter =
         new SimpleCursorAdapter(this, R.layout.row, cursor, from, to);
        listContent.setAdapter(cursorAdapter);
        listContent.setOnItemClickListener(listContentOnItemClickListener);
        
        buttonAdd.setOnClickListener(buttonAddOnClickListener);
        buttonDeleteAll.setOnClickListener(buttonDeleteAllOnClickListener);
        
        buttonSortDefault = (Button)findViewById(R.id.sortbyDefault);
        buttonSort1 =  (Button)findViewById(R.id.sortby1);
        
        buttonSortDefault.setOnClickListener(new Button.OnClickListener(){

   @Override
   public void onClick(View arg0) {
    // TODO Auto-generated method stub
    cursor = mySQLiteAdapter.queueAll();
    String[] from = new String[]{SQLiteAdapter.KEY_ID, SQLiteAdapter.KEY_CONTENT1, SQLiteAdapter.KEY_CONTENT2};
          int[] to = new int[]{R.id.id, R.id.text1, R.id.text2};
          cursorAdapter =
           new SimpleCursorAdapter(AndroidSQLite.this, R.layout.row, cursor, from, to);
          listContent.setAdapter(cursorAdapter);
    updateList();
   }});
        
        buttonSort1.setOnClickListener(new Button.OnClickListener(){

   @Override
   public void onClick(View arg0) {
    // TODO Auto-generated method stub
    cursor = mySQLiteAdapter.queueAll_SortBy_CONTENT1();
    String[] from = new String[]{SQLiteAdapter.KEY_ID, SQLiteAdapter.KEY_CONTENT1, SQLiteAdapter.KEY_CONTENT2};
          int[] to = new int[]{R.id.id, R.id.text1, R.id.text2};
          cursorAdapter =
           new SimpleCursorAdapter(AndroidSQLite.this, R.layout.row, cursor, from, to);
          listContent.setAdapter(cursorAdapter);
    updateList();
   }});
        
        
    }
    
    Button.OnClickListener buttonAddOnClickListener
    = new Button.OnClickListener(){

  @Override
  public void onClick(View arg0) {
   // TODO Auto-generated method stub
   String data1 = inputContent1.getText().toString();
   String data2 = inputContent2.getText().toString();
   mySQLiteAdapter.insert(data1, data2);
   updateList();
  }
     
    };
    
    Button.OnClickListener buttonDeleteAllOnClickListener
    = new Button.OnClickListener(){

  @Override
  public void onClick(View arg0) {
   // TODO Auto-generated method stub
   mySQLiteAdapter.deleteAll();
   updateList();
  }
     
    };
    
    private ListView.OnItemClickListener listContentOnItemClickListener
    = new ListView.OnItemClickListener(){

  @Override
  public void onItemClick(AdapterView<?> parent, View view, int position,
    long id) {
   // TODO Auto-generated method stub
   
   Cursor cursor = (Cursor) parent.getItemAtPosition(position);
   final int item_id = cursor.getInt(cursor.getColumnIndex(SQLiteAdapter.KEY_ID));
            String item_content1 = cursor.getString(cursor.getColumnIndex(SQLiteAdapter.KEY_CONTENT1));
            String item_content2 = cursor.getString(cursor.getColumnIndex(SQLiteAdapter.KEY_CONTENT2));
            
            AlertDialog.Builder myDialog 
            = new AlertDialog.Builder(AndroidSQLite.this);
            
            myDialog.setTitle("Delete/Edit?");
            
            TextView dialogTxt_id = new TextView(AndroidSQLite.this);
            LayoutParams dialogTxt_idLayoutParams 
             = new LayoutParams(LayoutParams.WRAP_CONTENT, LayoutParams.WRAP_CONTENT);
            dialogTxt_id.setLayoutParams(dialogTxt_idLayoutParams);
            dialogTxt_id.setText("#" + String.valueOf(item_id));
            
            final EditText dialogC1_id = new EditText(AndroidSQLite.this);
            LayoutParams dialogC1_idLayoutParams 
             = new LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT);
            dialogC1_id.setLayoutParams(dialogC1_idLayoutParams);
            dialogC1_id.setText(item_content1);
            
            final EditText dialogC2_id = new EditText(AndroidSQLite.this);
            LayoutParams dialogC2_idLayoutParams 
             = new LayoutParams(LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT);
            dialogC2_id.setLayoutParams(dialogC2_idLayoutParams);
            dialogC2_id.setText(item_content2);
            
            LinearLayout layout = new LinearLayout(AndroidSQLite.this);
            layout.setOrientation(LinearLayout.VERTICAL);
            layout.addView(dialogTxt_id);
            layout.addView(dialogC1_id);
            layout.addView(dialogC2_id);
            myDialog.setView(layout);
            
            myDialog.setPositiveButton("Delete", new DialogInterface.OnClickListener() {
                // do something when the button is clicked
                public void onClick(DialogInterface arg0, int arg1) {
                 mySQLiteAdapter.delete_byID(item_id);
           updateList();
                 }
                });
            
            myDialog.setNeutralButton("Update", new DialogInterface.OnClickListener() {
                // do something when the button is clicked
                public void onClick(DialogInterface arg0, int arg1) {
                 String value1 = dialogC1_id.getText().toString();
                 String value2 = dialogC2_id.getText().toString();
                 mySQLiteAdapter.update_byID(item_id, value1, value2);
           updateList();
                 }
                });
            
            myDialog.setNegativeButton("Cancel", new DialogInterface.OnClickListener() {
                // do something when the button is clicked
                public void onClick(DialogInterface arg0, int arg1) {
         
                 }
                });
            
            myDialog.show();
            
            
  }};

 @Override
 protected void onDestroy() {
  // TODO Auto-generated method stub
  super.onDestroy();
  mySQLiteAdapter.close();
 }



 private void updateList(){
  cursor.requery();
    }

}


download filesDownload the files.

7 comments:

Nam said...

thanks about your blog. I learning very much about here. Only here ... thanks

souliyo said...

Good tutorial can you make some example like this with search box and show the result. like a dictionary. i need a simple way like. search the word from db, show the list and go the result page by clicking the word.

do you have any source like this

sathish said...

Hi,

Thanks for posting more useful android content for learning.

I have followed the same as you had done in the above blog for SQLite data base creation and querying the data in order.
But i am unable to get the data in order even after using ORDER BY also. can you please let me know any other things i need to maintain to get the ordered data from the tables.
It is very helpful for me if you provide some examples on this topic.

Thanks in andvance.

Sathish.

Jonas said...

Thankyou very much for turorial, it's really help me :)

Rajesh said...

thank u so much(**,)

Imran majeed said...

Greeeeeeetaaaaaat tutorials.....
help me a lot....
thanks a lot......

Anonymous said...

Great job, been on hundreds of sites looking for an example like this, thanks. Black Mountain