How should I structure database access methods in my Android app?

Question

I am developing an Android application that has a database having 14-15 tables. I want to access these tables obviously for querying, inserting, deleting etc.

Should I make a single helper that would open the database and then create a single adapter that would access it and query it as I plan to? Or make a helper for each table?

I am confused about this point. The first option seems more realistic to me because I wouldn't need to open the database multiple times.

Answer 1

Create a class that extends from SQLiteOpenHelper and make method for each one. One for insert, one for delete, one for update and one from retrieving ResultSet. Here is an example:

public class Db extends SQLiteOpenHelper {
    static final int    version=1;
    static final String dbName="phoneBook";
    static final String dbtable="data";
    Context cont;

    public Db(Context context) 
    {
        super(context, dbName, null, version);
        cont = context;
    }

    public void onCreate(SQLiteDatabase db) 
    {
        try {
            String sql = "CREATE TABLE "+dbtable+" (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone TEXT)";
            db.execSQL(sql);
        } catch (SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
    }

    public void addContact(String name, String number) 
    {
        try 
        {
            SQLiteDatabase db = getWritableDatabase();
            String sql = "INSERT INTO "+dbtable+" VALUES(NULL,'"+name+"', '"+number+"')";
            db.execSQL(sql);
            db.close();
        } catch (SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
    }
    public void updateContact(int sid, String name, String number) {
        try {
            SQLiteDatabase db = getWritableDatabase();
            String sql = "UPDATE "+dbtable+" SET name = '"+name+"', phone = '"+number+"' WHERE id = "+sid;
            db.execSQL(sql);
            db.close();
        } catch (SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
    }
    public ArrayList<String> getContactByPhone(String number) {
        try {
            SQLiteDatabase db=this.getReadableDatabase();
            ArrayList<String> al = new ArrayList<String>();
            Cursor cur = db.rawQuery("SELECT * from "+dbtable+" WHERE phone like '%"+number+"%'",null);
            while(cur.moveToNext())
                al.add(cur.getInt(0)+","+cur.getString(1)+","+cur.getString(2));
            db.close();
            return al;
        } catch(SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
        return null;
    }

    public ArrayList<String> getContactByName(String sname) {
        try {
            SQLiteDatabase db=this.getReadableDatabase();
            ArrayList<String> al = new ArrayList<String>();
            Cursor cur = db.rawQuery("SELECT * from "+dbtable+" WHERE name like '%"+sname+"%'",null);
            while(cur.moveToNext())
                al.add(cur.getInt(0)+","+cur.getString(1)+","+cur.getString(2));
            db.close();
            return al;
        } catch(SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
        return null;
    }

    public ArrayList<String> getAllContact() {
        try {
            SQLiteDatabase db=this.getReadableDatabase();
            ArrayList<String> al = new ArrayList<String>();
            Cursor cur = db.rawQuery("SELECT * from "+dbtable,null);
            while(cur.moveToNext())
                al.add(cur.getInt(0)+","+cur.getString(1)+","+cur.getString(2));
            db.close();
            return al;
        } catch(SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
        return null;
    }

    public void deleteContact(int sid) {
        try {
            SQLiteDatabase db = getWritableDatabase();
            String sql = "DELETE FROM "+dbtable+" WHERE id = "+sid;
            db.execSQL(sql);
            db.close();
        } catch (SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
    }

    public void deleteData() {
        try {
            SQLiteDatabase db = getWritableDatabase();
            db.execSQL("DELETE FROM "+dbtable);
            db.close();
        } catch (SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
    }

    public void showAll() {
        try {
            SQLiteDatabase db = getReadableDatabase();
            Cursor cur = db.rawQuery("SELECT * from "+dbtable, null);
            while(cur.moveToNext())
                Toast.makeText(cont, cur.getString(1)+" "+cur.getString(2), Toast.LENGTH_LONG).show();
            db.close();
        } catch (SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
    }

    public int getCount() {
        int total = 0;
        try {
            SQLiteDatabase db = getReadableDatabase();
            Cursor curs = db.rawQuery("SELECT * FROM "+dbtable+" WHERE 1",null);
            total = curs.getCount();            
            db.close();
        } catch (SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
        return total;
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            db.execSQL("DROP TABLE IF EXISTS "+dbtable);
            onCreate(db);
        } catch (SQLException e) {
            Toast.makeText(cont, e.toString(), Toast.LENGTH_LONG).show();
        }
    }

}

`