Delete row in SQLite database

Delete row in SQLite database

android.database.sqlite.SQLiteDatabase class provide a convenience method for deleting rows in the database.



Modify SQLiteAdapter.java, implement a method delete_byID(int id) to delete individual row using id.

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 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 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 implement a custom AlertDialog to handle the ui, call delete_byID(int id) to delete row.

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;

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

  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?");
        
          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));
        
          TextView dialogC1_id = new TextView(AndroidSQLite.this);
          LayoutParams dialogC1_idLayoutParams
           = new LayoutParams(LayoutParams.WRAP_CONTENT, LayoutParams.WRAP_CONTENT);
          dialogC1_id.setLayoutParams(dialogC1_idLayoutParams);
          dialogC1_id.setText(item_content1);
        
          TextView dialogC2_id = new TextView(AndroidSQLite.this);
          LayoutParams dialogC2_idLayoutParams
           = new LayoutParams(LayoutParams.WRAP_CONTENT, 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("OK", new DialogInterface.OnClickListener() {
              // do something when the button is clicked
              public void onClick(DialogInterface arg0, int arg1) {
               mySQLiteAdapter.delete_byID(item_id);
         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();
  }

}

All other files, main.xml and row.xml, follow the last exercise "Retrieve individual items in cursor".