This research on using SQLite on Android will address the following: a) How do I create a database? How do I populate it? b) How do I read from the database? c) How do I update and delete from it? d) How do I do transactions, if allowed at all? e) Are there utilities similar to JPA or Hiberante or CoreData? f) How do I run DDL scripts? g) How do I migrate data? h) How do I create a service layer that abstracts transactions much like session beans in java?
satya - 8/22/2014 11:41:03 AM
Key Classes
SQLLiteHelper
SQLLiteDatabase
Cursor
SQLiteQueryBuilder
satya - 8/22/2014 11:44:05 AM
See here how I have used SQLLite for content providers
See here how I have used SQLLite for content providers
You will find here how to explore on device databases, use sqllite sql, and many of the sql needs dicussed here in the context of a content provider. Your needs may be simple enough that you don't need a content provider. And hence this page to explore just direct SQL access internal to an application.
satya - 8/22/2014 11:49:07 AM
Should I use direct SQL or encapsulate in a content provider
Having a content provider will allow you to participate in external applications. Your data can be shared in a meaningful secure way with other applications. Allows you to provide activities to manipulate, create, or delete this data as part of the data design.
Writing a content provider is probably 20% more work. I am tempted to recommend an approach where you provide 2 things to your data a) a service layer to access the data internally b) a content provider wrapper for external applications. I don't think the development overhead would be too much.
Or start with a service layer and the database directly underneath it and consider the content provider once you understand the data better and see how best to expose it.
satya - 8/22/2014 11:50:16 AM
How to use SQLite on Android?
How to use SQLite on Android?
Search for: How to use SQLite on Android?
Let's see what is covered on the web on this subject.
satya - 8/22/2014 11:51:34 AM
is there CoreData for Android?
is there CoreData for Android?
satya - 8/22/2014 11:52:08 AM
Persistence libraries (OR Mapping) for Android
Persistence libraries (OR Mapping) for Android
satya - 8/22/2014 1:21:56 PM
Some suggested ORM tools
Ormlite
GreenDAO
NexusData (Some early port attempt for Coredata)
satya - 8/22/2014 1:22:08 PM
Has anyone ported CoreData to Android?
Has anyone ported CoreData to Android?
satya - 8/22/2014 1:27:23 PM
Home page for NexusData on github
Home page for NexusData on github
This talks about what features of coredata are available and what are not.
satya - 8/22/2014 2:11:51 PM
More things to consider
Installing the database
Running Install scripts
Versioning the database
Migrating data
Local vs server side storage
satya - 8/22/2014 2:48:42 PM
How to use SQLiteOpenHelper effectively?
How to use SQLiteOpenHelper effectively?
Search for: How to use SQLiteOpenHelper effectively?
It is not hard to conceive after some examination of this class,what this class is for. However it is a good question to ask and and make sure the thought process is right!
satya - 8/22/2014 2:53:18 PM
Here is a pretty good brief to the point article on how to use SQLite directlly
Here is a pretty good brief to the point article on how to use SQLite directlly
satya - 8/22/2014 2:55:01 PM
You can also use my sample code for a book provider to understand this
You can also use my sample code for a book provider to understand this
satya - 8/22/2014 3:29:45 PM
Some Key Aspects of SQLiteOpenHelper
This is a class that you instantiate to get access to your database file.
This class encapsulates the idea that when you install an app, the database may already be there so you need to upgrade and migrate the database to the new structure.
Or this is a fresh install so there is no database there so you need to create for the first time and create all the tables and load any initial data.
This class provides callbacks for each of these cases and it is up to you what you want to do in that call back method.
Once you instantiate this class, you can ask it to give you a real reference to the SQliteDatabase object. It is at this time that wrapper class may chose to run the checks and see if this is an old database or a new one etc.
The same helper class is used in the content providers as well and serves the same basic purpose.
satya - 8/22/2014 3:32:42 PM
Conceptual idea
MyDBHelper extends SQLiteHelper {
Constructor(name, version,..)
getDatabase(...)//check it and give me a valid database
onCreate(){ //if it is a fresh database }
onUpgrade() { //needs to create and migrate data as the db exists }
onDowngrare() { //similar }
}
satya - 8/23/2014 9:18:06 AM
If you didn't have this class you would have done it this way
//During the install of your app
See if the app is already installed
if so, what is the version
if not installed
go ahead and create the new database structure
if installed previously
is the structure right for the new version
if not, alter the tables or create the tables
migrate the data
//Installation complete
//move on with the rest of the app
//And hope that this takes not too long!
satya - 8/23/2014 9:19:20 AM
The class above mostly eliminates
if/else conditions
needing to save the version in a particular place etc.
There may be a few more things as preparing the database
in a particular way if desired for reads or writes etc.
satya - 8/23/2014 9:21:25 AM
Next up for reading is the SQLiteDatabase class to see what operations it has
Next up for reading is the SQLiteDatabase class to see what operations it has
satya - 8/23/2014 9:25:13 AM
Key methods here include
Transactions
executing SQL
Various tuning parameters
Versions
Locking
run queries
Paging
Constraint control
satya - 8/23/2014 9:26:17 AM
A key method for DDL is execSQL
A key method for DDL is execSQL
oops! doesn't support scripts but only 1 sql statement.
satya - 8/23/2014 9:28:00 AM
Android SQLiteDatabase running DDL scripts
Android SQLiteDatabase running DDL scripts
Search for: Android SQLiteDatabase running DDL scripts
It turns out this is a good question to ask!
satya - 8/23/2014 9:37:09 AM
An interesting idea: Vertabelo.com
An interesting idea: Vertabelo.com
To create data models and generate SQL online. I suppose like ERWin in the cloud. Appears to be half-heartedly free in a very limited way. May be an option for small companies.
satya - 8/23/2014 9:42:29 AM
Free Database design tools in the cloud like vertabelo
Free Database design tools in the cloud like vertabelo
Search for: Free Database design tools in the cloud like vertabelo
satya - 8/23/2014 9:52:13 AM
Not rocket science but here is an approach to running multiple scripts
Not rocket science but here is an approach to running multiple scripts
satya - 8/23/2014 9:56:46 AM
THis is required because execSQL() only runs 1 sql statement
To over come this you parse the file with multiple statements yourself and run execSQL() repeatedly.
If you are doing this you may want to use the assets folder and not the raw folder as the later allows multiple copies of it for multiple configurations. Unless you want this go with assets.
Also pay attention to how big this script is as all of this runs under a transactional boundary. Not sure if needed to break up the work into multiple transactional units. But again if it is that much work, is it even a mobile app to begin with!!
I wonder if there are utility or a library that someone wrote that makes this work reusable.
satya - 8/23/2014 10:04:43 AM
Also keep in mind that migrations are tricky a little
You have to consider various versions the database may be in such as 1, 2, 3, or 4.
You have to have appropriate actions or scripts to go from one to the other.
You also have to have to accordingly copy the rows and migrate them to the appropriate version.
Hence this work must be carefully planned.
Very likely in all cases you want to write scripts so that this works well. As data gets larger, this could get very tricky
I also wonder if you should employ a server side option where you send the whole darn file to a server and have it translate it to the right version and bring the whole file back!
satya - 8/23/2014 10:18:44 AM
Running DDL scripts through Android execSQL()
Running DDL scripts through Android execSQL()
satya - 8/23/2014 10:19:15 AM
Here is some started code for a SQLParser to parse SQL statements: www.michenux.net
Here is some started code for a SQLParser to parse SQL statements: www.michenux.net
satya - 8/23/2014 10:23:24 AM
java code to parse SQL statements from a script file
java code to parse SQL statements from a script file
Search for: java code to parse SQL statements from a script file
satya - 8/23/2014 10:26:59 AM
At some point I will need to get to ANTLR 3
satya - 8/23/2014 10:27:20 AM
Android ANTLR SQLite DDL
Android ANTLR SQLite DDL
satya - 8/23/2014 10:37:07 AM
Here is some discussion on java parsers for SQL statements
satya - 8/23/2014 10:37:46 AM
Here is another link on the same subject
Here is another link on the same subject
you may be able to use this code as is.
satya - 8/23/2014 10:39:17 AM
SQL ScriptRunner Java
SQL ScriptRunner Java
satya - 8/23/2014 10:41:03 AM
http://sqlscriptrunner.codeplex.com/
satya - 8/23/2014 10:42:02 AM
SQLScriptRunner.java
SQLScriptRunner.java
satya - 8/23/2014 10:42:14 AM
Use this code perhaps as a starting point
satya - 8/23/2014 10:44:43 AM
Java SQLScriptRunner GitHub
Java SQLScriptRunner GitHub
satya - 8/23/2014 10:53:23 AM
Android SQLScriptRunner
Android ScriptRunner
satya - 8/23/2014 10:53:48 AM
Android Beryl Library
Android Beryl Library
satya - 8/23/2014 10:57:44 AM
What on earth is OpenHub.net??
What on earth is OpenHub.net??
satya - 8/23/2014 11:01:11 AM
is Android Beryl project still active?
is Android Beryl project still active?
satya - 8/27/2014 3:23:08 PM
Android SQLite UI explorer
Android SQLite UI explorer
satya - 8/27/2014 3:23:19 PM
Android SQLite developer console
Android SQLite developer console
satya - 8/27/2014 3:25:02 PM
is there a GUI tool to connect to SQLite through adb shell
is there a GUI tool to connect to SQLite through adb shell
Search for: is there a GUI tool to connect to SQLite through adb shell
satya - 8/27/2014 3:33:29 PM
I was hoping by now someone might have developed a GUI tool to access SQLite
for mobile devices from the host OS. Looks like there is one on the device itself in google play. Other than that one has to use sqlite3 command line tool as it had been from the beginning. Or just like before copy the database file and inspect it on the OS using GUI tools. Both are awkward solutions for a flowing experience.
Ought to be painful to debug issues. Wonder if it is possible to wrap the remote shell in a quick and dirty GUI!
satya - 8/27/2014 3:44:39 PM
declaring auto increment for SQLite database
satya - 8/27/2014 3:45:59 PM
You can do one of these
CREATE TABLE t1(
a INTEGER PRIMARY KEY,
b INTEGER
);
Or
CREATE TABLE t1(
a INTEGER PRIMARY KEY AUTOINCREMENT,
b INTEGER
);
satya - 8/27/2014 3:46:50 PM
SQLite DDL Code samples
SQLite DDL Code samples
satya - 8/27/2014 3:53:35 PM
Good information on how what is and how to use sqlfiddle.com
Good information on how what is and how to use sqlfiddle.com
satya - 8/28/2014 10:09:48 AM
Understand the insert statement: The Null Column Hack
Understand the insert statement
The second argument is a column name that can be null which can then allow an empty row to be inserted. Not sure why one would do that. Hence passing a null value for this column name is what I need as I have no intention of adding a row that has all NULL values.
optional; may be null. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided values is empty, no column names are known and an empty row can't be inserted. If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty.
satya - 8/28/2014 10:36:21 AM
what does android sqlite insert statement return rowid
what does android sqlite insert statement return rowid
Search for: what does android sqlite insert statement return rowid
satya - 8/28/2014 10:38:01 AM
Here is a clarification on this from SOF posts
satya - 8/28/2014 10:39:38 AM
Summary of that discussion
The row id in sqlite is either the integer primary key field, or, in its absence, a 64-bit undeclared column named ROWID. So when you insert either NULL or no value in an auto-increment field, the generated value is returned.
satya - 8/28/2014 2:59:50 PM
difference between sqllite replace and insert
difference between sqllite replace and insert
satya - 8/28/2014 3:02:01 PM
Here is the documentation on the conflict clause
satya - 8/28/2014 3:07:32 PM
Apparently this is what happens...
The replace in the android sqlite api is basically an insert with a "replace" conflict guideline.
This guideline looks to see if a unique constraint is violated. Say u are trying to insert an article whose name is already there. Right thing to do is to disallow it. But if you wish you can delete the old record and insert the new one. The newly inserted one will have a new rowid and a new possibly primary key.
So keep in mind it is not an "update"
satya - 8/28/2014 3:58:57 PM
Here is how you can convert an object into a key/value pair for Android SQLite operations
private ContentValues getBookAsContentValues(Book book)
{
ContentValues cv = new ContentValues();
cv.put(BookSQLLiteMetaData.NAME, book.getName());
cv.put(BookSQLLiteMetaData.ISBN, book.getIsbn());
cv.put(BookSQLLiteMetaData.AUTHOR, book.getAuthr());
return cv;
}
satya - 8/28/2014 3:59:29 PM
Here is how you use that to update a book in the database using db.update
public void updateBook(Book book)
{
if (book.getId() < 0) {
throw new SQLException("Book id is less than 0");
}
SQLiteDatabase db = getWriteDb();
ContentValues bcv = this.getBookAsContentValues(book);
String whereClause = String.format("%1 = %2",BookSQLLiteMetaData.ID_COLNAME,book.getId());
//Or
String whereClause2 = "? = ?";
String[] whereArgs = new String[2];
whereArgs[0] = BookSQLLiteMetaData.ID_COLNAME;
whereArgs[1] = Integer.toString(book.getId());
db.update(BookSQLLiteMetaData.TABLE_NAME, bcv, whereClause, null);
}
satya - 8/29/2014 9:39:18 AM
You can do this for exception messages
int count = db.update(BookSQLLiteMetaData.TABLE_NAME, bcv, whereClause, null);
if (count == 0) {
throw new SQLException(
String.format("Failed ot update book for book id:%s",book.getId()));
}
satya - 8/29/2014 12:48:51 PM
Best way to deal with transactions is not to deal with them at all...
Just like in stored procedures you want the container to deal with transactions. EJBs are another example of this. But out of the box it is the programmers responsiblity in Android SQLite..
satya - 8/29/2014 12:49:11 PM
One can possibly employ Dynamic proxies to deal with transactional aspect
One can possibly employ Dynamic proxies to deal with transactional aspect
satya - 8/29/2014 12:49:19 PM
Android and dynamic proxies
Android and dynamic proxies
satya - 8/29/2014 12:51:40 PM
A dynamic proxy for doing transactions in Android
A dynamic proxy for doing transactions in Android
Search for: A dynamic proxy for doing transactions in Android
satya - 8/29/2014 12:58:14 PM
sqlite transactions in Android
sqlite transactions in Android
satya - 8/30/2014 10:19:27 AM
Here is some research on dynamic proxies
satya - 9/3/2014 10:01:12 AM
Here is the final result using SQLite correctly in Android
short of using an ORM tool here is a reasonable work around to use SQLite properly.
satya - 9/3/2014 10:03:19 AM
The approach involves the following
satya - 9/3/2014 10:03:38 AM
Here is some early source code to show you how this works
Here is some early source code to show you how this works
satya - 9/3/2014 10:04:25 AM
Start with a client that knows how to work with BOOK objects
public class SQLitePersistenceTester extends BaseTester
{
private static String tag = "SQLitePersistenceTester";
SQLitePersistenceTester(Context ctx, IReportBack target) {
super(ctx, target,tag);
}
//Add a book whose id is one larger than the books
//in the database
public void addBook()
{
Book book = Book.getAMockBook();
int bookid = Services.PersistenceServices.bookps.saveBook(book);
reportString(String.format("Inserted a book %s whose generated id now is %s"
,book.getName()
,bookid));
}
//Delete the last book
public void removeBook()
{
int i = getCount();
Services.PersistenceServices.bookps.deleteBook(i);
}
//write the list of books so far to the screen
public void showBooks()
{
List<Book> bookList =
Services.PersistenceServices.bookps.getAllBooks();
reportString(String.format("Number of books:", bookList.size()));
for(Book b: bookList) {
reportString(String.format("id:%s name:%s author:%s isbn:%s"
,b.getId()
,b.getName()
,b.getAuthr()
,b.getIsbn()));
}
}
//COunt the number of books in the database
private int getCount()
{
List<Book> bookList =
Services.PersistenceServices.bookps.getAllBooks();
return bookList.size();
}
}
satya - 9/3/2014 10:05:10 AM
Here is the Services class
/**
* Allow a name space for clients to discover various services
* Usage:
* Services.persistenceServices.bookps.addBook(); etc.
* Dynamic proxy will take care of transactions.
* Dynamic proxy will take care of mock data.
* Dynamic Proxy will allow more than one interface
* to apply the above aspects.
*/
public class Services
{
public static class PersistenceServices {
////se this pointer during initialization
public static IBookPS bookps = null;
}
public static void init() {}
private static Object mainProxy;
static
{
//set up bookps
ClassLoader cl = IBookPS.class.getClassLoader();
//Add more interfaces as available
Class[] variousServiceInterfaces = new Class[] { IBookPS.class };
//Create a big object that can proxy all the related interfaces
//for which similar common aspects are applied
//In this cases it is android sqllite transactions
mainProxy =
Proxy.newProxyInstance(cl,
variousServiceInterfaces,
new DBServicesProxyHandler());
//Preset the namespace for easy discovery
PersistenceServices.bookps = (IBookPS)mainProxy;
}
}
satya - 9/3/2014 10:06:02 AM
Here is the Dynamic Proxy Handler that deals with transactions
/**
* DBServicesProxyHandler
* A class to externalize SQLite Transactions
* It is a dynamic proxy.
* @See Services.java to see how a reference to this is used
*
* It is capable of hosting multiple interfaces
*
* Each interface may represent persistence aspects of a
* particular entity.
*
* Or the interface can be a composite interface dealing with
* multiple entities.
*
* It also uses ThreadLocals to pass the DatabaseContext
* @See DatabaseContext
*
* DatabaseContext provides the SQLiteDatabase reference to
* the implementation classes.
*
* Related classes
* ****************
* Services.java : Client access to interfaces
* IBookPS: Client interface to deal with persisting a Book
* BookPSSQLite: SQLite Implementation of IBookPS
*
* DBServicesProxyHandler: This class that is a dynamic proxu
* DatabaseContext: Holds a db reference for BookPSSQlite implementation
* DirectAccessBookDBHelper: Android DBHelper to construct the database
*
*/
public class DBServicesProxyHandler implements InvocationHandler
{
private BookPSSQLite bookServiceImpl = new BookPSSQLite();
private static String tag = "DBServicesProxyHandler";
DBServicesProxyHandler(){
}
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
logMethodSignature(method);
String mname = method.getName();
if (mname.startsWith("get"))
{
return this.invokeForReads(method, args);
}
else
{
return this.invokeForWrites(method, args);
}
}
private void logMethodSignature(Method method)
{
String interfaceName = method.getDeclaringClass().getName();
String mname = method.getName();
Log.d(tag,String.format("%s : %s", interfaceName, mname));
}
private Object callDelegatedMethod(Method method, Object[] args)
throws Throwable
{
return method.invoke(bookServiceImpl, args);
}
private Object invokeForReads(Method method, Object[] args) throws Throwable
{
try
{
DatabaseContext.setReadableDatabaseContext();
return callDelegatedMethod(method, args);
}
finally
{
DatabaseContext.reset();
}
}
private Object invokeForWrites(Method method, Object[] args) throws Throwable
{
try
{
DatabaseContext.setWritableDatabaseContext();
DatabaseContext.beginTransaction();
Object rtnObject = callDelegatedMethod(method, args);
DatabaseContext.setTransactionSuccessful();
return rtnObject;
}
finally
{
try {
DatabaseContext.endTransaction();
}
finally {
DatabaseContext.reset();
}
}
}
}//eof-class
satya - 9/3/2014 10:06:36 AM
Here is the abstracted DatabaseContext
public class DatabaseContext {
public enum ReadWriteType
{
Read, Write;
}
protected SQLiteDatabase db = null;
public DatabaseContext(SQLiteDatabase db)
{
this.db = db;
}
public static ReadDatabaseContext createReadDatabaseContext()
{
return dbfactory.createReadableDatabase();
}
public static WriteDatabaseContext createWriteDatabaseContext()
{
return dbfactory.createWritableDatabase();
}
private static ThreadLocal<DatabaseContext> tl_DatabaseContext
= new ThreadLocal<DatabaseContext>();
public static void setWritableDatabaseContext()
{
DatabaseContext dc = createWriteDatabaseContext();
tl_DatabaseContext.set(dc);
}
public static void setReadableDatabaseContext()
{
DatabaseContext dc = createReadDatabaseContext();
tl_DatabaseContext.set(dc);
}
public static DatabaseContext getCurrentDatabaseContext()
{
return (DatabaseContext)tl_DatabaseContext.get();
}
public static SQLiteDatabase getDb()
{
return getCurrentDatabaseContext().db;
}
public static void reset()
{
//you have to call this at the end of the thread
//Makes sure you do this in the final
tl_DatabaseContext.set(null);
}
public static void beginTransaction(){
getCurrentDatabaseContext().internalBeginTransaction();
}
public static void setTransactionSuccessful(){
getCurrentDatabaseContext().internalSetTransactionSuccessful();
}
public static void endTransaction() {
getCurrentDatabaseContext().internalEndTransaction();
}
//protected functions
//These are implemented by read and write database differently
//Write database will have implementations
//Read database will not implement them
protected void internalBeginTransaction(){}
protected void internalSetTransactionSuccessful(){}
protected void internalEndTransaction() {}
//The following code is used initially to
//setup a factory
public static interface IFactory
{
ReadDatabaseContext createReadableDatabase();
WriteDatabaseContext createWritableDatabase();
}
private static IFactory dbfactory = null;
/**
* Used only once during the setup of the database.
* Called by the SQLiteHelper derived class during the
* database setup.
* @param factory
*/
public static void initialize(DatabaseContext.IFactory factory)
{
DatabaseContext.dbfactory = factory;
}
}//eof-class
satya - 9/3/2014 10:06:53 AM
ReadDatabaseContext
public class ReadDatabaseContext extends DatabaseContext
{
public ReadDatabaseContext(SQLiteDatabase db)
{
super(db);
}
}
satya - 9/3/2014 10:07:14 AM
WriteDatabaseContext
public class WriteDatabaseContext extends DatabaseContext
{
public WriteDatabaseContext(SQLiteDatabase db)
{
super(db);
} //protected functions
@Override
protected void internalBeginTransaction(){
db.beginTransaction();
}
@Override
protected void internalSetTransactionSuccessful(){
db.setTransactionSuccessful();
}
@Override
protected void internalEndTransaction() {
db.endTransaction();
}
}
satya - 9/3/2014 10:07:53 AM
Here is implementation of IBookPS
//Book related services
public class BookPSSQLite extends ASQLitePS
implements IBookPS
{
private static String tag = "BookPSSQLite";
public int saveBook(Book book)
{
//get the database
//id does not exist in the book object
if (book.getId() == -1)
{
//id of the book doesn't exist
//so create it
return (int)createBook(book);
}
//id exists in book object
updateBook(book);
return book.getId();
}
public void deleteBook(int bookid){
SQLiteDatabase db = getWriteDb();
String tname = BookSQLLiteMetaData.TABLE_NAME;
String whereClause =
String.format("%s = %s;",
BookSQLLiteMetaData.ID_COLNAME,
bookid);
String[] whereClauseargs = null;
db.delete(tname,whereClause, whereClauseargs);
};
private long createBook(Book book)
{
//book doesn't exist
//create it
SQLiteDatabase db = getWriteDb();
ContentValues bcv = this.getBookAsContentValues(book);
//I don't need ot insert an empty row
//usually any nullable column name goes here
//if I want to insert an empty row
String nullColumnNameHack = null;
//Construct values from the Book object
//SQLException is a runtime exception
long rowId = db.insertOrThrow(BookSQLLiteMetaData.TABLE_NAME, nullColumnNameHack, bcv);
return rowId;
}
public void updateBook(Book book)
{
if (book.getId() < 0) {
throw new SQLException("Book id is less than 0");
}
SQLiteDatabase db = getWriteDb();
ContentValues bcv = this.getBookAsContentValues(book);
String whereClause = String.format("%1 = %2",BookSQLLiteMetaData.ID_COLNAME,book.getId());
//Or
String whereClause2 = "? = ?";
String[] whereArgs = new String[2];
whereArgs[0] = BookSQLLiteMetaData.ID_COLNAME;
whereArgs[1] = Integer.toString(book.getId());
int count = db.update(BookSQLLiteMetaData.TABLE_NAME, bcv, whereClause, null);
if (count == 0) {
throw new SQLException(
String.format("Failed ot update book for book id:%s",book.getId()));
}
}
private ContentValues getBookAsContentValues(Book book)
{
ContentValues cv = new ContentValues();
cv.put(BookSQLLiteMetaData.NAME, book.getName());
cv.put(BookSQLLiteMetaData.ISBN, book.getIsbn());
cv.put(BookSQLLiteMetaData.AUTHOR, book.getAuthr());
return cv;
}
public List<Book> getAllBooks()
{
SQLiteDatabase db = getReadDb();
String tname = BookSQLLiteMetaData.TABLE_NAME;
String[] colnames = BookSQLLiteMetaData.s_self.getColumnNames();
//Selection
String selection = null; //all rows. Usually a where clause. exclude where part
String[] selectionArgs = null; //use ?s if you need it
String groupBy = null; //sql group by clause: exclude group by part
String having = null; //similar
String orderby = null;
String limitClause = null; //max number of rows
//db.query(tname, colnames)
Cursor c = db.query(tname,colnames,selection,selectionArgs,groupBy,having,orderby,limitClause);
//This may not be the optimal way to read data through a list
//Directly pass the cursor back if your intent is to read these one row at a time
List<Book> bookList = new ArrayList<Book>();
for(c.moveToFirst();c.isAfterLast();c.moveToNext())
{
Book b = new Book();
BookSQLLiteMetaData.s_self.fillFields(c,b);
bookList.add(b);
}
return bookList;
}
public Book getBook(int bookid) {
SQLiteDatabase db = getReadDb();
String tname = BookSQLLiteMetaData.TABLE_NAME;
String[] colnames = BookSQLLiteMetaData.s_self.getColumnNames();
//Selection
String selection =
String.format("%s = %s",
BookSQLLiteMetaData.ID_COLNAME,
bookid);
//all rows. Usually a where clause. exclude where part
String[] selectionArgs = null; //use ?s if you need it
String groupBy = null; //sql group by clause: exclude group by part
String having = null; //similar
String orderby = null;
String limitClause = null; //max number of rows
//db.query(tname, colnames)
Cursor c = db.query(tname,colnames,selection,selectionArgs,groupBy,having,orderby,limitClause);
try
{
if (c.isAfterLast())
{
Log.d(tag,"No rows for id" + bookid);
return null;
}
Book b = new Book();
BookSQLLiteMetaData.s_self.fillFields(c, b);
return b;
}
finally {
c.close();
}
}
}//eof-class
satya - 9/3/2014 10:08:35 AM
ASQLitePS
public abstract class ASQLitePS
{
/**
* Give me the current database on the thread
* it can be read or write database.
* Transactions are automatically handled
* @return SQLiteDatabase
*/
protected SQLiteDatabase getDb()
{
return DatabaseContext.getDb();
}
/**
* Use these if you need to.
* Use getDb() instead as that returns DB on the current thread
* @return SQLiteDatabase
*/
protected SQLiteDatabase getWriteDb()
{
return getDb();
}
/**
* Use these if you need to.
* Use getDb() instead as that returns DB on the current thread
* @return SQLiteDatabase
*/
protected SQLiteDatabase getReadDb()
{
return getDb();
}
}
satya - 9/3/2014 10:09:04 AM
Domain Object: Book
public class Book extends BaseEntity
{
//Key data fields
//*************************************
private String name;
private String authr;
private String isbn;
//*************************************
public Book(String ownedAccount, String createdBy, Date createdOn,
String lastUpdatedBy, Date lastUpdatedOn, String name,
String authr, String isbn) {
super(ownedAccount, createdBy, createdOn, lastUpdatedBy, lastUpdatedOn,-1);
this.name = name;
this.authr = authr;
this.isbn = isbn;
}
//To help with persistence
public Book() {
}
//Generated methods
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthr() {
return authr;
}
public void setAuthr(String authr) {
this.authr = authr;
}
public String getIsbn() {
return isbn;
}
public void setIsbn(String isbn) {
this.isbn = isbn;
}
public static Book getAMockBook()
{
String ownedAccount = "Account1";
String createdBy = "satya";
Date createdOn = Calendar.getInstance().getTime();
String lastUpdatedBy = "satya";
Date lastUpdatedOn = Calendar.getInstance().getTime();
//See how many books I have and increment it by one
List<Book> books = Services.PersistenceServices.bookps.getAllBooks();
int i = books.size();
String name = String.format("Book %s",i);
String authr = "satya";
String isbn = "isbn-12344";
return new Book(ownedAccount,createdBy,createdOn,
lastUpdatedBy,lastUpdatedOn,
name,authr,isbn);
}
}
satya - 9/3/2014 10:09:25 AM
A Base domain object
public class BaseEntity {
private int id; //database identifier
private String ownedAccount = null; //Multi-tenant if needed
private String createdBy;
private Date createdOn;
private String lastUpdatedBy;
private Date lastUpdatedOn;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOwnedAccount() {
return ownedAccount;
}
public BaseEntity(String ownedAccount, String createdBy, Date createdOn,
String lastUpdatedBy, Date lastUpdatedOn, int id) {
super();
this.ownedAccount = ownedAccount;
this.createdBy = createdBy;
this.createdOn = createdOn;
this.lastUpdatedBy = lastUpdatedBy;
this.lastUpdatedOn = lastUpdatedOn;
this.id = id;
}
//For persitence
public BaseEntity(){}
public void setOwnedAccount(String ownedAccount) {
this.ownedAccount = ownedAccount;
}
public String getCreatedBy() {
return createdBy;
}
public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}
public Date getCreatedOn() {
return createdOn;
}
public void setCreatedOn(Date createdOn) {
this.createdOn = createdOn;
}
public String getLastUpdatedBy() {
return lastUpdatedBy;
}
public void setLastUpdatedBy(String lastUpdatedBy) {
this.lastUpdatedBy = lastUpdatedBy;
}
public Date getLastUpdatedOn() {
return lastUpdatedOn;
}
public void setLastUpdatedOn(Date lastUpdatedOn) {
this.lastUpdatedOn = lastUpdatedOn;
}
}
satya - 9/3/2014 10:10:34 AM
There are a few more classes that I haven't shown here
but they should be obvious.
satya - 9/3/2014 10:12:19 AM
Bottom line dealing with SQL Storage
I have feeling you WILL need an ORM library to deal with SQLite and Android environment effectively.
Even with an ORM environment you may want to use dynamic proxies if you want to craft a service layer
satya - 9/3/2014 10:15:41 AM
Caution: The code I have presented here is not fully tested...
I have tested it in bits and pieces. I may load a sample project once I am done with it.
satya - 9/3/2014 10:51:13 AM
Already a correction! You may want to examine in the proxy if a transaction is in progress
If so you want to ignore begining a new transaction and allow it to be part of the previous one! at least for most general cases.
You can also invoke your sibling method directly, you don't need to go through the service layer if you are part of the same database.
satya - 9/3/2014 11:01:48 AM
Here is the updated proxy
/**
* DBServicesProxyHandler
* A class to externalize SQLite Transactions
* It is a dynamic proxy.
* @See Services.java to see how a reference to this is used
*
* It is capable of hosting multiple interfaces
*
* Each interface may represent persistence aspects of a
* particular entity.
*
* Or the interface can be a composite interface dealing with
* multiple entities.
*
* It also uses ThreadLocals to pass the DatabaseContext
* @See DatabaseContext
*
* DatabaseContext provides the SQLiteDatabase reference to
* the implementation classes.
*
* Related classes
* ****************
* Services.java : Client access to interfaces
* IBookPS: Client interface to deal with persisting a Book
* BookPSSQLite: SQLite Implementation of IBookPS
*
* DBServicesProxyHandler: This class that is a dynamic proxu
* DatabaseContext: Holds a db reference for BookPSSQlite implementation
* DirectAccessBookDBHelper: Android DBHelper to construct the database
*
*/
public class DBServicesProxyHandler implements InvocationHandler
{
private BookPSSQLite bookServiceImpl = new BookPSSQLite();
private static String tag = "DBServicesProxyHandler";
DBServicesProxyHandler(){
}
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
logMethodSignature(method);
String mname = method.getName();
if (mname.startsWith("get"))
{
return this.invokeForReads(method, args);
}
else
{
return this.invokeForWrites(method, args);
}
}
private void logMethodSignature(Method method)
{
String interfaceName = method.getDeclaringClass().getName();
String mname = method.getName();
Log.d(tag,String.format("%s : %s", interfaceName, mname));
}
private Object callDelegatedMethod(Method method, Object[] args)
throws Throwable
{
return method.invoke(bookServiceImpl, args);
}
private Object invokeForReads(Method method, Object[] args) throws Throwable
{
if (DatabaseContext.isItAlreadyInsideATransaction() == true)
{
//It is already bound
return invokeForReadsWithoutATransactionalWrap(method, args);
}
else
{
//A new transaction
return invokeForReadsWithATransactionalWrap(method, args);
}
}
private Object invokeForReadsWithATransactionalWrap(Method method, Object[] args) throws Throwable
{
try
{
DatabaseContext.setReadableDatabaseContext();
return callDelegatedMethod(method, args);
}
finally
{
DatabaseContext.reset();
}
}
private Object invokeForReadsWithoutATransactionalWrap(Method method, Object[] args) throws Throwable
{
return callDelegatedMethod(method, args);
}
private Object invokeForWrites(Method method, Object[] args) throws Throwable
{
if (DatabaseContext.isItAlreadyInsideATransaction() == true)
{
//It is already bound
return invokeForWritesWithoutATransactionalWrap(method, args);
}
else
{
//A new transaction
return invokeForWritesWithATransactionalWrap(method, args);
}
}
private Object invokeForWritesWithATransactionalWrap(Method method, Object[] args) throws Throwable
{
try
{
DatabaseContext.setWritableDatabaseContext();
DatabaseContext.beginTransaction();
Object rtnObject = callDelegatedMethod(method, args);
DatabaseContext.setTransactionSuccessful();
return rtnObject;
}
finally
{
try {
DatabaseContext.endTransaction();
}
finally {
DatabaseContext.reset();
}
}
}
private Object invokeForWritesWithoutATransactionalWrap(Method method, Object[] args) throws Throwable
{
return callDelegatedMethod(method, args);
}
}//eof-class
satya - 10/27/2014, 1:25:18 PM
Ormlite GreenDAO NexusData
Ormlite GreenDAO NexusData
satya - 10/27/2014, 1:27:23 PM
Article: 5 of the ORMs for Android
satya - 10/27/2014, 1:33:26 PM
The list is
GreenDAO: Appears to be a leader in speed
OrmLite: Java annotations based. A bit slow
SugarORM: Built for Android, uses inheritance to persistence methods
ActiveAndroid: Another inheritance based approach
satya - 1/31/2015, 11:07:51 AM
SQLite Empty row support
SQLite Empty row support
satya - 1/31/2015, 11:13:28 AM
I get it now the null column name
The SQL language insert statement needs at least one column name in its syntax even if every column allows a NULL value!!! So a nullable column name is required to issue an empty row insert.