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 the files.
Thank you for reading this article query SQLite database with sorting With URL http://x-tutorials.blogspot.com/2011/06/query-sqlite-database-with-sorting.html. Also a time to read the other articles.
0 comments:
Write your comment for this article query SQLite database with sorting above!