Reputation: 81
SQLite DB with multiple tables
I keep seeing examples of how to use DBHelper with multiple tables, but they all have the subject data and queries defined in the DBHelper class rather than in each data class. That seems wrong, like it's not preserving separation of concerns. I might be misunderstanding how DBHelper is used. Wouldn't it be better for DBHelper to just have the foundation parts of the database, like Create, and put the individual database functions like "Insert" in each subject class, like Book.insert, Author.insert, etc. and then those can use DBHelper. It just seems wrong to combine DB functions for books and authors in DBHelper. Does anyone have an example of how this would be coded, or maybe an explanation of why it isn't necessary? Thanks in advance!
Upvotes: 0
Views: 502
Answers (1)
Reputation: 56938
Does anyone have an example of how this would be coded, or maybe an explanation of why it isn't necessary?
As you have seen examples, then as they work, it is clearly not necessary.
- In fact when it boils down to it a DBHelper isn't necessary as it's a convenience for checking if a database exists, if not creating the database and calling onCreate and then opening a connection to the database if it did or did not exist.
Combining everything in the one class that extends SQliteOpenHelper (the DBHelper) has the slight advantage that the SQLiteDatabase can be retrieved using this.getWritableDatabase(), setting a variable, in the constructor, declared at the class level can further simplify matters; you can then simply refer to to variable.
Another reason that can be made for combining is that as SQLite is a relational database, then relatiinships can make separation overly complex/confusing. Where you do put code that deals with a relationship between tableA and tableB, and then where do you put code for the relationship between tableA and tableC and tableB and tableC and what about tableA, tableB, tableC code ......... and then what if you had generic code or utility code.
If separate classes are used then a DBHelper instance or an SQLite database instance would need to be passed.
Using seprate classes comes into play when there are multiple developers each can work on smaller components of the whole.
As such for answering questions on SO combining has a distinct advantage.
Splitting - Example
DBHelper.java
/**
* DBHelper
*/
@SuppressWarnings("WeakerAccess")
class DBHelper extends SQLiteOpenHelper {
/**
* Consrtuctor
*
* @param context activity context
* @param name database name
* @param factory cursorfactory
* @param version database version
*/
DBHelper(Context context, @SuppressWarnings("SameParameterValue") String name, @SuppressWarnings("SameParameterValue") SQLiteDatabase.CursorFactory factory, @SuppressWarnings("SameParameterValue") int version) {
super(context, name, factory, version);
}
/**
* Instantiates a new Db helper.
*
* @param context the context
*/
DBHelper(Context context) {
super(context, DBConstants.DATABASE_NAME, null, 1);
}
private static DBHelper instance;
/**
* Gets helper.
*
* @param context the context
* @return the helper
*/
static synchronized DBHelper getHelper(Context context) {
if(instance == null) {
instance = new DBHelper(context);
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
expand(db, false);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldversion, int newversion) {
}
public static void reopen(Context context) {
instance = new DBHelper(context);
}
}
However, the DBHelper itself is retrieved/constructed/instanntiated via DBDAO.java :-
public class DBDAO {
/**
* The Db.
*/
private SQLiteDatabase db;
private DBHelper dbhelper;
@SuppressWarnings("unused")
private Context mContext;
public static final String THISCLASS = DBDAO.class.getSimpleName();
private static final String LOGTAG = "SW_DBDAO";
/**
* DBAO Constructor
*
* @param context Context of the invoking method
*/
public DBDAO(Context context) {
this.mContext = context;
dbhelper = DBHelper.getHelper(context);
db = dbhelper.getWritableDatabase();
}
public SQLiteDatabase getDb() {
return db;
}
/**
* Gets table row count.
*
* @param tablename table to inspect
* @return number of rows
*/
public int getTableRowCount(String tablename) {
Cursor csr = db.query(tablename,null,null,null,null,null,null);
int rv = csr.getCount();
csr.close();
return rv;
}
/**
* getTableRows - generic get rows from a table
*
* @param table Table name
* @param joinclause Joing clause, if blank skipped
* @param filter Filter clause less WHERE, if blank skipped
* @param order Order clause less ORDER BY keywords, skipped if blank
* @return Cursor with extracted rows, if any.
*/
public Cursor getTableRows(String table, @SuppressWarnings("SameParameterValue") String joinclause, String filter, String order) {
String sql = " SELECT * FROM " + table;
if (joinclause.length() > 0 ) {
sql = sql + joinclause;
}
if (filter.length() > 0 ) {
sql = sql + " WHERE " + filter;
}
if (order.length() > 0) {
sql = sql + " ORDER BY " + order;
}
sql = sql + " ;";
return db.query(table + joinclause,null,filter,null,null,null,order);
}
}
One of the tables is Aisle the methods are in
class DBAisleMethods {
private Context context;
private DBDAO dbdao;
private static SQLiteDatabase db;
private static long lastaisleadded;
private static boolean lastaisleaddok = false;
private static boolean lastaisleupdateok = false;
private DBShopMethods dbshopmethods;
public static final String THISCLASS = DBAisleMethods.class.getSimpleName();
private final String[] dummyrowcolumns = new String[] {
DBAislesTableConstants.AISLES_ID_COL,
DBAislesTableConstants.AISLES_SHOPREF_COL,
DBAislesTableConstants.AISLES_ORDER_COL,
DBAislesTableConstants.AISLES_NAME_COL
};
private final MatrixCursor dummyrow = new MatrixCursor(dummyrowcolumns);
/**
* Instantiates a new Db aisle methods.
*
* @param ctxt the ctxt
*/
DBAisleMethods(Context ctxt) {
context = ctxt;
dbdao = new DBDAO(context);
db = dbdao.getDb();
dbshopmethods = new DBShopMethods(context);
}
/**************************************************************************
* getAisleCount - get the number of aisles
*
* @return number of Aisles
*/
int getAisleCount() {
return DBCommonMethods.getTableRowCount(db,
DBAislesTableConstants.AISLES_TABLE
);
}
/**************************************************************************
* getLastAisleAdded - return the is of the last Aisle added
*
* @return id of the Aisle that was last added
*/
@SuppressWarnings("unused")
long getLastAisleAdded() {
return lastaisleadded;
}
/**************************************************************************
* ifAisleAdded - returns status of the last Aisle insert
*
* @return true if the last Aisle insert added the Aisle, else false
*/
boolean ifAisleAdded() {
return lastaisleaddok;
}
/**************************************************************************
* insertAisle - add a new Aisle
*
* @param aislename name of the aisle
* @param aislorder order of the aisle (within shop)
* @param shopref reference to the parent shop
*/
void insertAisle(String aislename, int aislorder, long shopref) {
long addedid;
if (dbshopmethods.doesShopExist(shopref)) {
ContentValues cv = new ContentValues();
cv.put(DBAislesTableConstants.AISLES_NAME_COL, aislename);
cv.put(DBAislesTableConstants.AISLES_ORDER_COL, aislorder);
cv.put(DBAislesTableConstants.AISLES_SHOPREF_COL, shopref);
addedid = db.insert(DBAislesTableConstants.AISLES_TABLE,
null,
cv);
if (addedid > -1) {
lastaisleadded = addedid;
lastaisleaddok = true;
}
} else {
lastaisleaddok = false;
}
}
/**************************************************************************
* @param aisleid ID of the Aisle to modify
* @param aisleorder new Aisle Order value (0 skips)
* @param aislename new Aisle Name value (blank skips)
*/
void modifyAisle(long aisleid, int aisleorder, String aislename) {
int updatecount = 0;
ContentValues cv = new ContentValues();
if (aisleorder > 0) {
cv.put(DBAislesTableConstants.AISLES_ORDER_COL, aisleorder);
updatecount++;
}
if (aislename.length() > 0) {
cv.put(DBAislesTableConstants.AISLES_NAME_COL, aislename);
updatecount++;
}
if (updatecount < 1) {
return;
}
String[] whereargs = {Long.toString(aisleid)};
String whereclause = DBAislesTableConstants.AISLES_ID_COL + " = ?";
lastaisleupdateok = db.update(DBAislesTableConstants.AISLES_TABLE, cv, whereclause, whereargs) > 0;
}
/**************************************************************************
* deleteAisle - Delete an Aisle and any children the aisle has
* children could be :- productusage rows
* shoplist rows
* rule rows
*
* @param aisleid id of the aisle to be deleted
* @param intransaction true if already in a transaction
*/
void deleteAisle(long aisleid, boolean intransaction) {
@SuppressWarnings("unused") String sql;
@SuppressWarnings("UnusedAssignment") int pudeletes = 0;
@SuppressWarnings({"UnusedAssignment", "unused"}) int sldeletes = 0;
@SuppressWarnings({"UnusedAssignment", "unused"}) int rdeletes = 0;
@SuppressWarnings({"UnusedAssignment", "unused"}) int adelete = 0;
if (doesAisleExist(aisleid)) {
if (!intransaction) {
db.beginTransaction();
}
// Set whereargs string array to the aisleid as a string
String[] whereargs = {Long.toString(aisleid)};
// Delete ProductUsage rows that have Aisle as a parent
pudeletes = db.delete(
DBProductusageTableConstants.PRODUCTUSAGE_TABLE,
DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL +
" = ?",
whereargs
);
// Delete Shopping List rows with Aisle as a parent
//noinspection UnusedAssignment
sldeletes = db.delete(
DBShopListTableConstants.SHOPLIST_TABLE,
DBShopListTableConstants.SHOPLIST_AISLEREF_COL +
" = ?",
whereargs
);
// Delete Rule rows with Aisle as a parent
//noinspection UnusedAssignment
rdeletes = db.delete(
DBRulesTableConstants.RULES_TABLE,
DBRulesTableConstants.RULES_AISLEREF_COL +
" = ?"
,
whereargs);
// Finally Delete the Aisle itself
//noinspection UnusedAssignment
adelete = db.delete(
DBAislesTableConstants.AISLES_TABLE,
DBAislesTableConstants.AISLES_ID_COL + " = ?",
whereargs);
if (!intransaction) {
db.setTransactionSuccessful();
db.endTransaction();
}
}
}
}
As you can see from the above relationships come into play; an Aisle is related to Shop as a child, an Aisle may be a parent to a Product which may be a Parent to a ProductUsage which may be a Parent to a ShoppingList.
- This is also a shortened version with other methods removed for the sake of brevity.
An example of usage of the above, in an activity, could be (amongst other code) :-
dbshopmethods = new DBShopMethods(this);
dbaislemethods = new DBAisleMethods(this);
slcsr = dbshopmethods.getShops("", shopsorderby);
selectshoplistadapter = new AdapterShopList(this,
slcsr,
CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER,
getIntent(),
true, false, false);
selectshoplist.setAdapter(selectshoplistadapter);
setSelectShopListener();
alcsr = dbaislemethods.getAisles(shopfilter,orderby,false);
aislelistadapter = new AdapterAisleList(
this,
alcsr,
CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER,
getIntent(),
false, false, false
);
aiselist.setAdapter(aislelistadapter);
aislesadapterset = true;
aiselist.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView,
View view,
int position,
long id) {
listItemClick(view, position, id);
}
});
aiselist.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
@Override
public boolean onItemLongClick(AdapterView<?> adapterView, View view,
int position,
long id) {
listItemLongClick(view, position,id);
return true;
}
});
Upvotes: 1
Related Questions
- Multiple database tables in SQLite
- SQLite multiple tables
- Multiple Tables In SQlite android
- Multi-table database SQLite android
- multiple table sqlite database
- Android - relational database SQLite with multiple tables
- Multiple tables in one SQLite database
- Multiple SQLite Tables
- Multi-table database in Android app
- More than one table in SQLite database in android?