CODEWITHSUNDEEP
androidsqlite
James
James

Reputation: 474

SQLite multiple tables

What I'm looking to do is create a sqlite db in android that has multiple tables (I know how to do this). What I would like to do is have one class (Activity) that creates each table, opens and closes the db. Then for each table create a class for it to use for my insert, delete and select statements. I'm confused how to do this as I can't seem to find any examples.

What I currently have is a class for each table that creates the table, opens, inserts and deletes but once my app creates one table the app won't create the other. Could someone please advise me if what I want to do is A. possible and B. good practice.

Or should I just put all my SQL into one class?

Upvotes: 1

Views: 3760

Answers (3)

yedidyak
yedidyak

Reputation: 1984

I just had and fixed the same problem. All the tables must be created simultaneously, yet through separate SQL statements. What I did was to keep my separate SQLOpenHelper classes, and made a new 'Creator' class with one method that executed all the CREATE statements which were static Strings in the Helper classes. Then each helper class called that method in its onCreate().

Upvotes: 3

Jose L Ugia
Jose L Ugia

Reputation: 6250

There are some approaches that may result in good practice. Vogella's blog was always a good reference to me. Have a look to this approach. It's made out of a class which controls the creation and update of the database and another one as a DAO to make the necessary request for a certain type of objects.

Personally I've always used a combination of both, together within a Singleton class, which was perfect for maintaining and controlling the whole lifecycle of the instance (in my case SQLiteDatabase) which allows you to make the tasks you need to perform without opening, closing or losing reference to your database object. The same class is inheriting from SQLiteOpenHelper to manage the creation and update of the database.

A general structure could look like this:

package your.package;

import java.io.File;

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

public class DBManager extends SQLiteOpenHelper {

private static DBManager sInstance;

private static SQLiteDatabase db;

private final String LOG_TAG = this.getClass().getSimpleName();

private static final String DB_NAME = "DBName";
private static final int DB_VERSION = 1;

public static synchronized DBManager getInstance(Context _c) {
if (sInstance == null) {
    sInstance = new DBManager(_c);
}
return sInstance;
}

private DBManager(Context _c) {
super(_c, DB_NAME, null, DB_VERSION);
}

public void deleteTable(String table) {
if (db == null || !db.isOpen()) {
    db = getWritableDatabase();
}

db.execSQL("DROP TABLE " + table);
db.close();
}

public void reset() {

File dbFile = new File(getReadableDatabase().getPath());
dbFile.delete();

sInstance = null;
db.close();
}

public static void closeDB() {
if (sInstance != null && db != null && db.isOpen()) {
    db.close();
}
}

public long insertContent(String table, ContentValues cv) {
if (db == null || !db.isOpen()) {
    db = getWritableDatabase();
}

return db.insert(table, null, cv);
}

public void updateContent(String table, ContentValues cv, String whereClause, String[] whereArgs) {
if (db == null || !db.isOpen()) {
    db = getWritableDatabase();
}

db.update(table, cv, whereClause, whereArgs);
}

@Override
public void onCreate(SQLiteDatabase db) {

if (db == null || !db.isOpen()) {
    db = getWritableDatabase();
}

db.execSQL("your query");
}

@Override
public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
Log.i(LOG_TAG, "onUpgrade old: " + oldVersion + " new: " + newVersion);
}

}

Upvotes: 0

FabiF
FabiF

Reputation: 2796

I'am not sure to understand exactly your question but I suggest you to look for ContentProvider. It's the best way to separate the database creation and requests.

Secondly you seem have trouble to create the database. I suppose you use class that extends from SQLiteOpenHelper. You can separate tables in different class but you have to make only one call to the onCreate of the SQLiteOpenHelper extended class. However you can call in this method other static methods from different class for each table. It's a proper way to make easier the database maintenance. If I understood your problem I can provide to you some samples.

Upvotes: 4

Related Questions