CODEWITHSUNDEEP
androiddatabaseeclipsesqlite
Daniel o Keeffe
Daniel o Keeffe

Reputation: 578

Delete contents from SQLite database

I am trying to delete all the tables from an SQLite database. The problem is I can't seem to do it via the SQLite command and can't find the files on my computer. I have the following code to insert data into the database. 

package com.example.mycoursetimetable;


import java.util.ArrayList;
import java.util.HashMap;

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

public class DBTools extends SQLiteOpenHelper {

public DBTools(Context applicationContext){

    super(applicationContext, "module.db", null, 1);

}

@Override
public void onCreate(SQLiteDatabase database) {

    String query = "CREATE TABLE modules ( moduleId INTEGER PRIMARY KEY,ModuleCode TEXT PRIMARY KEY, ModuleName TEXT,ModuleType TEXT, DayOfWeek TEXT, StartTime TEXT, EndTime TEXT,Location TEXT,AdditionalInfo Text)";

    database.execSQL(query);

}

@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {

    String query = "DROP TABLE IF EXISTS modules";

    database.execSQL(query);
    onCreate(database);

}

public void insertModule(HashMap<String, String> queryValues){

    SQLiteDatabase database = this.getWritableDatabase();

    ContentValues values = new ContentValues();

    values.put("ModuleCode", queryValues.get("ModuleCode"));
    values.put("ModuleName", queryValues.get("ModuleName"));
    values.put("ModuleType", queryValues.get("ModuleType"));
    values.put("DayOfWeek", queryValues.get("DayOfWeek"));
    values.put("StartTime", queryValues.get("StartTime"));
    values.put("EndTime", queryValues.get("EndTime"));
    values.put("Location", queryValues.get("Location"));
    values.put("AdditionalInfo", queryValues.get("AdditionalInfo"));

    database.insert("modules", null, values);

    database.close();

}

public int updateModule(HashMap<String, String> queryValues){

    SQLiteDatabase database = this.getWritableDatabase();

    ContentValues values = new ContentValues();


    values.put("ModuleCode", queryValues.get("ModuleCode"));
    values.put("ModuleName", queryValues.get("ModuleName"));
    values.put("ModuleType", queryValues.get("ModuleType"));
    values.put("DayOfWeek", queryValues.get("DayOfWeek"));
    values.put("StartTime", queryValues.get("StartTime"));
    values.put("EndTime", queryValues.get("EndTime"));
    values.put("Location", queryValues.get("Location"));
    values.put("AdditionalInfo", queryValues.get("AdditionalInfo"));

    return database.update("modules", values, 
            "moduleId" + " = ?", new String[]  {queryValues.get("moduleId") });

}

public void deleteModule(String id){

    SQLiteDatabase database = this.getWritableDatabase();

    String deleteQuery = "DELETE FROM modules WHERE moduleId='" + id + "'";

    database.execSQL(deleteQuery);

}

public ArrayList<HashMap<String, String>> getAllModules(){

    ArrayList<HashMap<String, String>> moduleArrayList = new  ArrayList<HashMap<String, String>>();

    String selectQuery = "SELECT * FROM modules ORDER BY ModuleCode";

    SQLiteDatabase database = this.getWritableDatabase();

    Cursor cursor = database.rawQuery(selectQuery, null);

    if(cursor.moveToFirst()){

        do{

            HashMap<String, String> moduleMap = new HashMap<String,  String>();

            moduleMap.put("moduleId", cursor.getString(0));
            moduleMap.put("ModuleCode", cursor.getString(1));
            moduleMap.put("ModuleName", cursor.getString(2));
            moduleMap.put("ModuleType", cursor.getString(3));
            moduleMap.put("DayOfWeek", cursor.getString(4));
            moduleMap.put("StartTime", cursor.getString(5));
            moduleMap.put("EndTime", cursor.getString(6));
            moduleMap.put("Location", cursor.getString(7));
    //      moduleMap.put("AdditionalInfo", cursor.getString(8));


            moduleArrayList.add(moduleMap);

        } while(cursor.moveToNext());

    }

    return moduleArrayList;

}

public HashMap<String, String> getModuleInfo(String id){

    HashMap<String, String> moduleMap = new HashMap<String, String>();

    SQLiteDatabase database = this.getReadableDatabase();

    String selectQuery = "SELECT * FROM modules WHERE moduleId='" + id + "'";

    Cursor cursor = database.rawQuery(selectQuery, null);

    if(cursor.moveToFirst()){

        do{

            moduleMap.put("moduleId", cursor.getString(0));
            moduleMap.put("ModuleCode", cursor.getString(1));
            moduleMap.put("ModuleName", cursor.getString(2));
            moduleMap.put("ModuleType", cursor.getString(3));
            moduleMap.put("DayOfWeek", cursor.getString(4));
            moduleMap.put("StartTime", cursor.getString(5));
            moduleMap.put("EndTime", cursor.getString(6));
            moduleMap.put("Location", cursor.getString(7));
        //  moduleMap.put("AdditionalInfo", cursor.getString(8));


        } while(cursor.moveToNext());

    }

    return moduleMap;

}

}

From the SQLite shell, I have tried many times in various ways to select the tables in my database. It keeps saying no such table, but the data is there in my application on startup. Does any one know how I could go about getting rid of these tables??

TIA

Upvotes: 0

Views: 674

Answers (1)

jboi
jboi

Reputation: 11892

If you really want to delete the whole database content including its structure (tables), than the easiest way is to drop the database and recreate it.

Drop with context.deleteDatabase(name) and recreate just by opening it with the Db open helper.

The deleteDatabase is very useful, e.g. when testing the create database code.

Dropping and recreating the database ensures that no internal structure or anything is left. It's a clean new start.

Upvotes: 1

Related Questions