Android SQLite MultiTable Database Development

Question

I would like to create a multi table database with all the RUD (minus the C) in a databasehandler class, but have all the tables created and updated in a helper class.

Here is what i've been using for a single table:

package com.cc.folfapptest;

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

public class ConfigDatabaseHandler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "AndroidSQLLiteDB";

    // Config table name
    private static final String TABLE_CONFIG = "Config";

    // Config Table Columns names
    private static final String KEY_ID = "id"; //
    private static final String KEY_VERSION = "Version";
    private static final String KEY_PRO_KEY = "ProKey";
    private static final String KEY_SERVERID = "ServerID";
    private static final String KEY_SERVERID_ALT = "ServerID_Alt";
    private static final String KEY_CURRENT_BAG_ID = "CurrentBagID";
    private static final String KEY_PLAYER_ID = "PlayerID"; 
    private static final String KEY_PLAYER_SERVER_ID = "PlayerServerID"; 

    String _currentBagID;
    String _playerID;
    String _playerServerID;

    public ConfigDatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_CONFIG_TABLE = "CREATE TABLE " + TABLE_CONFIG + "("
                + KEY_ID + " INTEGER PRIMARY KEY," 
                + KEY_VERSION + " TEXT," 
                + KEY_PRO_KEY + " TEXT," 
                + KEY_SERVERID + " TEXT," 
                + KEY_SERVERID_ALT + " TEXT," 
                + KEY_CURRENT_BAG_ID + " TEXT," 
                + KEY_PLAYER_ID + " TEXT," 
                + KEY_PLAYER_SERVER_ID + " TEXT" 
                + ")";
        db.execSQL(CREATE_CONFIG_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONFIG);

        // Create tables again
        onCreate(db);
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new config
    int addConfig(Config config) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_VERSION, config.getVersion()); // Version
        values.put(KEY_PRO_KEY, config.getProKey()); // ProKey
        values.put(KEY_SERVERID, config.getServerID()); 
        values.put(KEY_SERVERID_ALT, config.getServerID());
        values.put(KEY_CURRENT_BAG_ID, config.getCurrentBagID()); 
        values.put(KEY_PLAYER_ID, config.getPlayerID()); 
        values.put(KEY_PLAYER_SERVER_ID, config.getPlayerServerID());

        // Inserting Row
        long myReturnlong = db.insert(TABLE_CONFIG, null, values);
        int myReturnInt = (int)myReturnlong;
        //db.close(); // Closing database connection
        return myReturnInt;
    }

    // Getting Config
    Config getConfig(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_CONFIG, new String[] { KEY_ID, KEY_VERSION, KEY_PRO_KEY, KEY_SERVERID, KEY_SERVERID_ALT, KEY_CURRENT_BAG_ID, KEY_PLAYER_ID, KEY_PLAYER_SERVER_ID}, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Config config = new Config();
        config.setID(Integer.parseInt(cursor.getString(0)));
        config.setVersion(cursor.getString(1));
        config.setProKey(cursor.getString(2));
        config.setServerID(cursor.getString(3));
        config.setServerID_Alt(cursor.getString(4));
        config.setCurrentBagID(cursor.getString(5));
        config.setPlayerID(cursor.getString(6));
        config.setPlayerServerID(cursor.getString(7));
        // return player
        return config;
    }

    // Updating Config
    public int updateConfig(Config config) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_VERSION, config.getVersion()); // Version
        values.put(KEY_PRO_KEY, config.getProKey()); // ProKey
        values.put(KEY_SERVERID, config.getServerID()); // SERVERID
        values.put(KEY_SERVERID_ALT, config.getServerID_Alt()); // SERVERID
        values.put(KEY_CURRENT_BAG_ID, config.getCurrentBagID()); 
        values.put(KEY_PLAYER_ID, config.getPlayerID()); 
        values.put(KEY_PLAYER_SERVER_ID, config.getPlayerServerID());

        // updating row
        return db.update(TABLE_CONFIG, values, KEY_ID + " = ?",
                new String[] { String.valueOf(config.getID()) });
    }

    // Truncate Config Table
    public void truncateConfigTable() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CONFIG, null, null);
    }
}

I was looking at the code for a way around this, and I think if i just don't override the onCreate() onUpgrade() in the above like class functionality and to just handle all the creates and changes to tables like (not exactly, but like):

public class DataBaseHelper extends SQLiteOpenHelper {
public DataBaseHelper(Context context, String name,CursorFactory factory, int version) 
{
           super(context, name, factory, version);
}
// Called when no database exists in disk and the helper class needs
// to create a new one.
@Override
public void onCreate(SQLiteDatabase _db) 
{
        _db.execSQL(DatabaseFunc.DATABASE_CREATE);
        _db.execSQL(DatabaseFunc.DATABASE_CREATE2);

}
// Called when there is a database version mismatch meaning that the version
// of the database on disk needs to be upgraded to the current version.
@Override
public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) 
{
        // Log the version upgrade.
        Log.w("TaskDBAdapter", "Upgrading from version " +_oldVersion + " to " +_newVersion + ", which will destroy all old data");

        // Upgrade the existing database to conform to the new version. Multiple
        // previous versions can be handled by comparing _oldVersion and _newVersion
        // values.
        // The simplest case is to drop the old table and create a new one.
        _db.execSQL("DROP TABLE IF EXISTS " + "LOGIN");
        _db.execSQL("DROP TABLE IF EXISTS " + "SMSREG");
        // Create a new one.
        onCreate(_db);
}

}

from: android-sqllite-multiple-tables will something like that work? I'd rather not have to combine 8 tables functionality into a single file. This would also allow me to quickly convert this and begin using it as a multi table setup.

I found a LOT of single table SQL Lite examples, single table examples just suck because when in the 'wild' would you use a database for a single table? sheesh.

ramaral · Accepted Answer · 2013-10-28 21:18:09Z

2

Create a Singleton Database class:

public class Database extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "yourDatabaseName";

    //Declare a String for each Table name
    public static final String TABLE_NAME1 = "tableName1";
    public static final String TABLE_NAME2 = "tableName2";

    //Declare a SQL string for create each table
    private static final String CREATE_TABLE1 =
            "CREATE TABLE if not exists " + TABLE_NAME1 ..............";

    private static final String CREATE_TABLE2 =
            "CREATE TABLE if not exists " + TABLE_NAME2 .................";

    private static Database Singleton = null;
    private Context context;
    private static SQLiteDatabase Db;

    private Database(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);

        this.context = context;
    }

    public static synchronized Database getInstance(Context c){
        if(Singleton == null) {
            Singleton = new Database(c.getApplicationContext());
            Db = Singleton.getWritableDatabase();
        }
        return Singleton;
    }

    public SQLiteDatabase getDatabase() {

        return Db;
    }

     public synchronized void close() {

         if (Singleton != null && Db.isOpen()) Db.close();
     }


    @Override
    protected void finalize() throws Throwable  {

        try{
            close();
        }
        finally{
            super.finalize();
        }

    @Override
    public void onCreate(SQLiteDatabase db) {

        //Create Tables
        db.execSQL(CREATE_TABLE1);
        db.execSQL(CREATE_TABLE2);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }
}

Create base class for Tables. This class has common methods to handle tables

abstract class DatabaseTable {

    private SQLiteDatabase Db;
    private String TableName;

    public DatabaseTable(SQLiteDatabase db,String tableName) {

        this.TableName = tableName;
        this.Db = db;

    }

    public Cursor fetchAll() {

        Cursor cursor = Db.query(TableName, null, null, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    public Cursor fetchAllOrderBy(String OrderField) {

        Cursor cursor = Db.query(TableName, null, null, null, null, null, OrderField);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    public Cursor fetchById(long id) {

        Cursor cursor = Db.query(TableName, null, "_id = " + id, null, null, null, null);
        if (cursor != null) {
            cursor.moveToFirst();
        }
        return cursor;
    }

    public boolean deleteById(long id) {

        return Db.delete(TableName,"_id = " + id, null) > 0;
    }

    public boolean deleteAll() {

        return Db.delete(TableName,null, null) > 0;
    }

    public int getNumberRows() {

        return (int) DatabaseUtils.queryNumEntries(Db, TableName);
    }

}

Now you should define classes for each table you want handle:

public class TbTable1 extends DatabaseTable{

    //Declare string for each field name
    public static final String FIELD_ID = "_id";
    public static final String FIELD_FIELDNAME1 = "fieldName1";
    public static final String FIELD_FIELDNAME2 = "fieldName2";
    ..........
    ..........

    private SQLiteDatabase Db;

    //Get the table name from Database class
    private static final String TableName = Database.TABLE_NAME1;


    public TbTable1(SQLiteDatabase db) {

        super(db,TableName);
        this.Db = db;
    }

    // Below define all the methods you need to access this table

    public long insertRecord(String value1, String value2) {

        ContentValues initialValues = new ContentValues();
        initialValues.put(FIELD_FIELDNAME2, value1);
        initialValues.put(FIELD_FIELDNAME2, value2);
        return Db.insert(TableName, null, initialValues);
    }

    ..............
    ..............
}

When you want do anything with a table:

SQLiteDatabase Db = Database.getInstance(context).getDatabase();  
TbTable1 table1 = new TbTable1(Db);  
table1.insertRecord("Value1","Value2");
Cursor cursor = table1.fetchAll();

// do something with data

cursor.close();

Hope this help!

answered Oct 28, 2013 at 21:18

ramaral

6,1894 gold badges36 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

shaddow Over a year ago

EXCELLENT! exactly what i'm looking for. You ought to consider writing an article and posting it somewhere. I KNOW it'd get viewed. I've been looking for something like this for days... I found only 2 examples online dealing with SQLLite and multitables that weren't just trivial, but attempted to show a way around.... You went far beyond that. This is useable and coherently written as an example. Please put it up somewhere, it is really an excellent example of how it SHOULD be done. Thanks!

shaddow Over a year ago

my top level DBHandler class is very similar to what you have... it's the table breakdown that is completely exceptional and something i had real problems finding a good example in dealing with multi tables. I just wish most of my quesions got as good an answer as this. If i could i'd give you max points!

shaddow Over a year ago

if you don't do an article I'm going to. ;) Is just too important for people to see a correct way of doing this vs. what is out there for consumption.

ramaral Over a year ago

No problems, since that you make reference to this answer

shaddow Over a year ago

of course, always props and pointers to the problem solvers :)

|

Collectives™ on Stack Overflow

Android SQLite MultiTable Database Development

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related