Delete ONE SPECIFIC table of a database - leave the rest intact

Question

Hi, I have a database where I store two different kinds of data. One table is for favorite routes, the other stores the retrieved routes from a server.

I can retrieve the routes etc just fine. But after retrieving the first Route, pressing back or HOME, and then retrieving another route, the routes table is filled with all the old routes plus the new ones.

So my question: how do I delete ONLY the routes table and not the whole database because I don't want to delete the added favorites....?!

I found the following function in the android docs:

public int delete (String table, String whereClause, String[] whereArgs)

and I tried to implement it, but I must pass a SQLiteDataBase as an argument. But how?

I implemented:

public void deleteTableRoutes(SQLiteDataBase db){
  db.delete("routes", null, null);
}

But I want to call this function from a different class where I have no reference to the database.. so what do I have to pass as an argument? Or how do I get a reference to my database?

I build my database upon the code example of the NotePadExample from the dev docs.

How to solve this problem? thanks

This is an extract of my class from where I want to call deleteTableRoutes(...)

package mjb.project.AVV;

/**
 * Parse the request and fill the ListView with the results
 * @author MJB
 * 
 */

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import net.htmlparser.jericho.Element;
import net.htmlparser.jericho.Source;
import android.app.ListActivity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.widget.SimpleCursorAdapter;

public class ParseHTML extends ListActivity {

    private String start_from;
    private String destination;
    private int hour;
    private int min;
    private int day;
    private int month;
    private Source source;
    private List<Element> tempList;
    private List<Element> routes;
    private String startT   = "";
    private String arrivalT = "";
    private String duration = "";
    private String line     = "";
    private boolean first=true; 
    private List<ResultElement> finalResult;
    private List<Element> results;
    private DbAdapter mDbHelper;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.parsehtml_list);

        mDbHelper = new DbAdapter(this);   
                mDbHelper.open();

        // Used to store the final results + data
        finalResult = new ArrayList<ResultElement>();

        // Get applicationsContext
        Context context = getApplicationContext();

        // Receive extras from Input.class
        ...

        try {
            // Set up the URL with the above parameters
            source = new Source(new URL("...."));   

            // Get the requested routes
            getResultElements();    
            // Get the data from the Elements
            getResultData();    
            // Print Result's data
            printRes();     

            // Delete old entries                        
            deleteTableRoutes(context);

// ERROR The method deleteRoutesTable(SQLiteDatabase) in the type DbAdapter is not applicable for the arguments (Context) .... of course, but how to get the reference to my db??

            // Insert data to database
            insertRoutesToDb(); 
            // Fill data into the ListView
            fillData();

        } catch (MalformedURLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            mDbHelper.close();
            mDbHelper=null;
        }
    }   
}

This is my DbAdapter

package mjb.project.AVV;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbAdapter {

    public static final String KEY_FROM  = "title";
    public static final String KEY_TO    = "body";
    public static final String KEY_ROWID = "_id";

    public static final String KEY_START    = "start";
    public static final String KEY_ARRIVAL  = "arrival";
    public static final String KEY_LINE     = "line";
    public static final String KEY_DURATION = "duration";

    private static final String DATABASE_NAME           = "data";
    private static final String DATABASE_NOTESTABLE     = "notes";
    private static final String DATABASE_ROUTESTABLE    = "routes";

    private static final String TAG = "DbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE_NOTES =
            "create table notes (_id integer primary key autoincrement, "
                    + "title text not null, body text not null)";

    private static final String DATABASE_CREATE_ROUTES =
        "create table routes (_id integer primary key autoincrement, "
                + "start , arrival , " 
                + "line , duration );";

    private static final int DATABASE_VERSION = 3;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE_NOTES);
            Log.d(TAG, "created notes table");

            db.execSQL(DATABASE_CREATE_ROUTES);
            Log.d(TAG, "created routes table");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS notes");
            db.execSQL("DROP TABLE IF EXISTS routes");
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public DbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public DbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }


    /**
     * Create a new note using the title and body provided. If the note is
     * successfully created return the new rowId for that note, otherwise return
     * a -1 to indicate failure.
     * 
     * @param title the title of the note
     * @param body the body of the note
     * @return rowId or -1 if failed
     */
    public long createNote(String title, String body) {

        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_FROM, title);
        initialValues.put(KEY_TO, body);

        return mDb.insert(DATABASE_NOTESTABLE, null, initialValues);
    }

    /**
     * Create a new route using the title and body provided. If the route is
     * successfully created return the new rowId for that route, otherwise return
     * a -1 to indicate failure.
     * 
     * @param start the start time of the route
     * @param arrival the arrival time of the route
     * @param line the line number of the route
     * @param duration the routes duration 
     * @return rowId or -1 if failed
     */

    public long createRoute(String start, String arrival, String line, String duration){

        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_START, start);
        initialValues.put(KEY_ARRIVAL, arrival);
        initialValues.put(KEY_LINE, line);
        initialValues.put(KEY_DURATION, duration);

        return mDb.insert(DATABASE_ROUTESTABLE, null, initialValues);
    }

    /**
     * Delete the note with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteNote(long rowId) {
        return mDb.delete(DATABASE_NOTESTABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all notes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllNotes() {
        return mDb.query(DATABASE_NOTESTABLE, new String[] {KEY_ROWID, KEY_FROM,
                KEY_TO}, null, null, null, null, null);
    }

    /**
     * Return a Cursor over the list of all routes in the database
     * 
     * @return Cursor over all routes
     */
    public Cursor fetchAllRoutes() {
        return mDb.query(DATABASE_ROUTESTABLE, new String[] {KEY_ROWID, KEY_START,
                KEY_ARRIVAL, KEY_LINE, KEY_DURATION}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the note that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchNote(long rowId) throws SQLException {
        Cursor mCursor =
                mDb.query(true, DATABASE_NOTESTABLE, new String[] {KEY_ROWID,
                        KEY_FROM, KEY_TO}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    /**
     * Return a Cursor positioned at the route that matches the given rowId
     * 
     * @param rowId id of route to retrieve
     * @return Cursor positioned to matching route
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchRoute(long rowId) throws SQLException {
        Cursor mCursor =
                mDb.query(true, DATABASE_ROUTESTABLE, new String[] {KEY_ROWID,
                        KEY_START, KEY_ARRIVAL, KEY_LINE, KEY_DURATION}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }

    /**
     * Update the note using the details provided. The note to be updated is
     * specified using the rowId, and it is altered to use the title and body
     * values passed in
     * 
     * @param rowId id of note to update
     * @param title value to set note title to
     * @param body value to set note body to
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateNote(long rowId, String title, String body) {
        ContentValues args = new ContentValues();
        args.put(KEY_FROM, title);
        args.put(KEY_TO, body);
        return mDb.update(DATABASE_NOTESTABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }

    public void deleteRoutesTable(SQLiteDatabase db){
        db.delete("routes", null, null);
    }
}

Answer 1

You can access the database with your DbAdapter class. Constructing this requires your Context. That is probably the appropriate place to put the deleteTableRoutes method. In fact, you already have a similar deleteNote method (though that deletes a single row). Note that you don't need to pass it a parameter, because DbAdapter has a mDb instance field.

Answer 2

in my DbAdapter class:

public void deleteRoutesTable(){
    mDb.delete("routes", null, null);
}

from where I call it:

public void onCreate(){
.......
try {
            mDbHelper.deleteRoutesTable();
        } catch (NullPointerException e) {
            // TODO Auto-generated catch block
            Log.d(TAG_AVV, "ERROR!");
            e.printStackTrace();
        }

I solved it. I just placed the try/catch call in the wrong method. When onResume() is called, there is no table to delete. Now I put it into my onCreate() method and it works just fine!

thanks to all helpers!

Answer 3

In reference to your most recent post (which, by the way, really should have been an edit to your question and not an 'answer"): Using mDbHelper to access your database is the way things should work. However, the reason you're getting a null pointer is probably that you don't have the right constructor in your DbAdapter.

If you aren't familiar with the term, the constructor is this part of your code:

public DbAdapter(Context ctx) {
    this.mCtx = ctx;
}

It's missing the line where you instantiate your DatabaseHelper class. You need to add just one line:

mDbHelper = new DatabaseHelper(ctx);