Exploring Tools and Techniques for Direct SQL Storage on Android

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?

Start with the basics at google


SQLLiteHelper
SQLLiteDatabase
Cursor
SQLiteQueryBuilder

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.

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.

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.

is there CoreData for Android?

Search for: is there CoreData for Android?

Persistence libraries (OR Mapping) for Android

Search for: Persistence libraries (OR Mapping) for Android


Ormlite
GreenDAO
NexusData (Some early port attempt for Coredata)

Has anyone ported CoreData to Android?

Search for: Has anyone ported CoreData to Android?

Home page for NexusData on github

This talks about what features of coredata are available and what are not. 


Installing the database
Running Install scripts
Versioning the database
Migrating data
Local vs server side storage

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!

Here is a pretty good brief to the point article on how to use SQLite directlly

You can also use my sample code for a book provider to understand this

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.


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


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


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.

Next up for reading is the SQLiteDatabase class to see what operations it has


Transactions
executing SQL
Various tuning parameters
Versions
Locking
run queries
Paging
Constraint control

A key method for DDL is execSQL

oops! doesn't support scripts but only 1 sql statement.

Android SQLiteDatabase running DDL scripts

Search for: Android SQLiteDatabase running DDL scripts

It turns out this is a good question to ask!

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.

Free Database design tools in the cloud like vertabelo

Search for: Free Database design tools in the cloud like vertabelo

Not rocket science but here is an approach to running multiple scripts

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.

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!

Running DDL scripts through Android execSQL()

Search for: Running DDL scripts through Android execSQL()

Here is some started code for a SQLParser to parse SQL statements: www.michenux.net

java code to parse SQL statements from a script file

Search for: java code to parse SQL statements from a script file

At some point I will need to get to ANTLR 3

Android ANTLR SQLite DDL

Search for: Android ANTLR SQLite DDL

Android ANTLR

Search for: Android ANTLR

Here is some discussion on java parsers for SQL statements

Here is another link on the same subject

you may be able to use this code as is.

SQL ScriptRunner Java

Search for: SQL ScriptRunner Java

http://sqlscriptrunner.codeplex.com/

SQLScriptRunner.java

Search for: SQLScriptRunner.java

Use this code perhaps as a starting point

ScriptRunner at GitHub

Java SQLScriptRunner GitHub

Search for: Java SQLScriptRunner GitHub

Android ScriptRunner

Search for: Android ScriptRunner

Android Beryl Library

Android Beryl Library

Search for: Android Beryl Library

What on earth is OpenHub.net??

Search for: What on earth is OpenHub.net??

is Android Beryl project still active?

Search for: is Android Beryl project still active?

Search android devlopers group

Android SQLite UI explorer

Search for: Android SQLite UI explorer

Android SQLite developer console

Search for: Android SQLite developer console

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

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!

declaring auto increment for SQLite database


CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  b INTEGER
);

Or

CREATE TABLE t1(
  a INTEGER PRIMARY KEY AUTOINCREMENT,
  b INTEGER
);

SQLite DDL Code samples

Search for: SQLite DDL Code samples

Here is SQLite syntax reference

Use sqlfiddle.com

Good information on how what is and how to use sqlfiddle.com

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.

what does android sqlite insert statement return rowid

Search for: what does android sqlite insert statement return rowid

Here is a clarification on this from SOF posts

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.

difference between sqllite replace and insert

Search for: difference between sqllite replace and insert

Here is the documentation on the conflict clause

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" 


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


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

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

One can possibly employ Dynamic proxies to deal with transactional aspect

Android and dynamic proxies

Search for: Android and dynamic proxies

A dynamic proxy for doing transactions in Android

Search for: A dynamic proxy for doing transactions in Android

sqlite transactions in Android

Search for: sqlite transactions in Android

Here is some research on dynamic proxies

short of using an ORM tool here is a reasonable work around to use SQLite properly.

  1. Use a stateless service layer to work with your persistence
  2. Make sure your service layer is all interfaces and domain objects
  3. Front the stateless service layer with a Transactional Dynamic Proxy
  4. Use thread locals to pass the database context for service implementations
  5. Do not do any transactions from your implementations

Here is some early source code to show you how this works


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


/**
 * 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;
   }
}


/**
 * 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


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


public class ReadDatabaseContext extends DatabaseContext
{
	public ReadDatabaseContext(SQLiteDatabase db)
	{
		super(db);
	}
}


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


//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


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


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


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

but they should be obvious.

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

I have tested it in bits and pieces. I may load a sample project once I am done with it.

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. 


/**
 * 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

Ormlite GreenDAO NexusData

Search for: Ormlite GreenDAO NexusData

Article: 5 of the ORMs for Android


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

Comparing ORMLite and GreenDAO

SQLite Empty row support

Search for: SQLite Empty row support

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.