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.
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!