SQLite multiple tables

Question

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?

It says it can't find the table, I understand what I'm doing wrong I'm more looking for advice/best practice? Many thanks. — James, Aug 16 '12 at 21:34

score 4 · Answer 1 · answered Aug 16 '12 at 21:37

4

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.

answered Aug 16 '12 at 21:37

FabiF

2,796
2
18
27

Yea that's the problem I'm having I only call the on create method once which is the problem. How could I call it from within another class, so I could check that the table exists and if not create it? – James Aug 16 '12 at 21:43
Why you can't create all the tables when you create the database? – FabiF Aug 16 '12 at 21:47
Each class creates each table. So depending on which class gets called first depends on which table gets created. An example I would like would be for one class to create all the tables and handle the opening and closing and then for every table have a class that references the class of the table create to do inserts, selects, deletes and updates. – James Aug 16 '12 at 21:58
can you provide the examples plz – Shane Jan 10 '13 at 07:37

score 3 · Answer 2 · answered Sep 10 '12 at 10:13

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().

score 0 · Answer 3 · answered Aug 16 '12 at 21:51

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);
}

}

SQLite multiple tables

3 Answers3