SQLite database in separate class vs. in same clas

Question

I have an SQLite database that is in a separate class from the main class that extends Activity.

I noticed that there are two ways of setting up the database. one way is to put it inside the main Activity class, either in the class or as a nested sub class. the second way is to put it in the separate class.

the separate class looks better, however there is one disadvantage. You have to create an instance of it in the main activity class every time you want to do something. I read that instantiating objects in Android is expensive and should be avoided.

despite this, I would rather make the database as a separate class. Is the cost of instantiating objects enough that it makes putting the database in the same class a better deal?

example of separate class for SQLite database: incomplete psudo-code

public class SQLiteDB {
   private static class DbHelper extends SQLiteOpenHelper{
      // db helper methods
   }

// methods for DB, like get, set, and others
public void openDatabase(){  }
public void closeDatabse(){  }
public void insertRecord(String record){  }
}

example use in main Activity: incompete psudo-code

public class Main extends Activity{

// every time I want to use it I must instantiate an object for the database class

// many instances of SQLiteDB object created, garbage collector works hard

SQLiteDB mDatabase = new SQLiteDB();

openDatabase();

insertRecord("insert this");

closeDatabase();

}

Answer 1

I prefer the solution you gave here. The primary advantage is that you can easily access the database from any Activity (or other class) in your app. To solve the problem of creating a new instance every time you use the database, you can instead create a single instance in onCreate(), use the database all you want while the Activity is active, and finally close the database in onDestroy().

Answer 2

SQLite database in separate class vs. in same class, which is better?

This is very comprehensive question and it depends on more factors(type of application, personal requirements, how you'll deal with db etc.). Somebody can prefer to place database as inner class and someone as separated class. Problem is that many developers are trying to "stick" as much code as possible into one class and maybe they "fear" to create a little more classes. I don't know that exactly. I mentioned that only as my personal note.

But let's back to your question. What is better?

I think that approach with separeted class. You should let your Activity classes only "Activity classes" > only for creating and dealing with UI. Application appearance should be separated from application logic. If you'll follow this "rule" your code will become more clean and human-readable(if someone else will look at your code he shouldn't be completely lost). It's not a shame to have 20 purely written classes as to have all stuff sticked in one class(like a pig).

however there is one disadvantage. You have to create an instance of it in the main activity class every time you want to do something. I read that instantiating objects in Android is expensive and should be avoided.

Did you think about an usage of Singleton? This design pattern is worth to think about it. You will always have only one instance that have many benefits e.q. no waste of memory. I have only good experiences with Singleton. Therefore i recommend you to try and use it.

Example:

private static SQLiteOpenHelper instance;

public static SQLiteOpenHelper getInstance(Context mContext) {
   if (instance == null) {
      instance = new SQLiteOpenHelperImplementation(mContext);
   }
   return instance;
}

And at the end i give you a few suggestions:

• Everytime you'll work with cursors, databases etc. release / close them immediately after work is done. This can solve many exceptions related to SQLiteDatabase and Cursor

• An usage of synchronized blocks and methods is pretty good practise in the case of concurrent programming to avoid many problems

• If you have more than one table in database i suggest you create "serving" class for each table that will wrap CRUD operations and specific methods of the table

• Before Activity is destroyed, check and release all sources which are not already released.

Answer 3

This would be a matter of personal taste.

However, what I've found to be efficient and clean has been to create a class that extends SQLiteOpenHelper. In this class you will end up writing the SQL code to create your tables and writing methods as your stored procedures.

The class would look something like this:

public class DatabaseInterface extends SQLiteOpenHelper {

// Database version
private static final int DATABASE_VERSION = 1;
public DatabaseInterface(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);

}
//in your oncreate you will write the queries to create your tables
@Override
public void onCreate(SQLiteDatabase db) {

    String CREATE_NEWS = "CREATE TABLE News(id INTEGER)";

    db.execSQL(CREATE_NEWS);
}

// upgrading tables
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // drop tables if exist
    db.execSQL("DROP TABLE IF EXSIST " + NEWS);
    // recreate tables
    onCreate(db);

}

Consider we have a News obj that takes 1 param as it's constructor, your stored procedures can look something like this:

public ArrayList<News> getNews() {
    ArrayList<News> mNewes = new ArrayList<News>();
    SQLiteDatabase db = null;
    Cursor cursor = null;

    try {
        String sQry = "SELECT * FROM " + NEWS;
        db = this.getWritableDatabase();
        cursor = db.rawQuery(sQry, null);

        if (cursor.moveToFirst()) {
            do {
                mNewes.add(new News(cursor.getInt(0)));

            } while (cursor.moveToNext());
        }
    } catch (SQLiteException e) {
        Log.e("SQLite - getNewes", e.getMessage());
        return null;
    } finally {
        cursor.close();
        db.close();
    }

    return mNewes;
}

In the above method you get and open the application database preform the query on it, anticipating any sql errors and then close the database. Doing it this way assures that you never have any resources open that you don't need/aren't using.

I've used this method in two apps that are currently out in the market and it runs rather quickly with making several hundred calls to methods I created for my stored procedures