Database Details - Android

As with the prior article, it's necessary to create a service for nearly every business object. For simplicity, I've focused on the Location (FishingSpot) service, and have left the other service implementations as an exercise for the reader.

The concrete implementation of the LocationService interface for databases is contained in the class LocationServiceDB:

LocationServiceDB.java

  1. package com.crowleyworks.futilefishing.service;
  2.  
  3. import java.util.ArrayList;
  4.  
  5. import android.content.ContentValues;
  6. import android.database.Cursor;
  7.  
  8. import com.crowleyworks.futilefishing.Main;
  9. import com.crowleyworks.futilefishing.model.FishingSpot;
  10. import com.crowleyworks.futilefishing.model.User;
  11.  
  12.  
  13. public class LocationServiceDB extends AbstractServiceDB implements LocationService {
  14.  
  15. @Override
  16. public void create(FishingSpot l) throws ServiceException {
  17. open();
  18. ContentResponse<FishingSpot> response = new ContentResponse<FishingSpot>(0, "Success");
  19. ContentValues cv = new ContentValues();
  20. cv.putNull(COL_ID);
  21. cv.put("name", l.getName());
  22. cv.put("author", Main.getUserId());
  23. cv.put("latitude", l.getLatitude());
  24. cv.put("longitude", l.getLongitude());
  25. cv.put("isPublic", l.isPublicSpot() ? 1 : 0);
  26. cv.put("createdLocally", 1);
  27. cv.put("comments", l.getComments());
  28. long newRowId = mDb.insert("locations", null, cv);
  29. l.setId(newRowId);
  30. close();
  31. ArrayList<FishingSpot> items = new ArrayList<FishingSpot>();
  32. items.add(l);
  33. response.setItems(items);
  34. response.setAction(ContentBroker.CREATE_JSON);
  35. delegate.contentLoaded(response);
  36. }
  37.  
  38. @Override
  39. public void update(FishingSpot l) throws ServiceException {
  40. open();
  41. ContentResponse<FishingSpot> response = new ContentResponse<FishingSpot>(0, "Success");
  42. ContentValues cv = new ContentValues();
  43. cv.put("name", l.getName());
  44. cv.put("author", l.getOwner().getUserId());
  45. cv.put("latitude", l.getLatitude());
  46. cv.put("longitude", l.getLongitude());
  47. cv.put("isPublic", l.isPublicSpot() ? 1 : 0);
  48. cv.put("comments", l.getComments());
  49. String selection = COL_ID + " LIKE ?";
  50. String[] selectionArgs = {String.valueOf(l.getId())};
  51. long newRowId = mDb.update("locations", cv, selection, selectionArgs);
  52. response.setRc(newRowId);
  53. close();
  54. ArrayList<FishingSpot> items = new ArrayList<FishingSpot>();
  55. items.add(l);
  56. response.setItems(items);
  57. delegate.contentLoaded(response);
  58. }
  59.  
  60. @Override
  61. public void delete(FishingSpot l) throws ServiceException {
  62. String selection = COL_ID + " LIKE ?";
  63. String[] selectionArgs = {String.valueOf(l.getId())};
  64. mDb.delete("locations", selection, selectionArgs);
  65. delegate.contentLoaded(new ContentResponse<FishingSpot>(0, "Success"));
  66. }
  67.  
  68. @Override
  69. public void get(long id) throws ServiceException {
  70. open();
  71. ArrayList<FishingSpot> items = new ArrayList<FishingSpot>();
  72. Cursor rs = mDb.rawQuery(LOC_Q_ONE+id, null);
  73. rs.moveToFirst();
  74. if (!rs.isAfterLast()) {
  75. FishingSpot fs = parseFishingSpot(rs);
  76. items.add(fs);
  77. }
  78. rs.close();
  79. close();
  80. ContentResponse<FishingSpot> response = new ContentResponse<FishingSpot>(0, "Success");
  81. response.setItems(items);
  82. delegate.contentLoaded(response);
  83. }
  84.  
  85. @Override
  86. public void getMany(String criteria) throws ServiceException {
  87. open();
  88. ArrayList<FishingSpot> items = new ArrayList<FishingSpot>();
  89. Cursor rs = mDb.rawQuery(LOC_Q_MANY, null);
  90. rs.moveToFirst();
  91. while (!rs.isAfterLast()) {
  92. FishingSpot fs = parseFishingSpot(rs);
  93. items.add(fs);
  94. rs.moveToNext();
  95. }
  96. rs.close();
  97. close();
  98. ContentResponse<FishingSpot> response = new ContentResponse<FishingSpot>(0, "Success");
  99. response.setAction(ContentBroker.LIST);
  100. response.setItems(items);
  101. delegate.contentLoaded(response);
  102. }
  103.  
  104. private FishingSpot parseFishingSpot(Cursor cursor) {
  105. FishingSpot fs = new FishingSpot();
  106. fs.setId(cursor.getLong(cursor.getColumnIndex(COL_ID)));
  107. fs.setName(cursor.getString(cursor.getColumnIndex("name")));
  108. fs.setLatitude(cursor.getDouble(cursor.getColumnIndex("latitude")));
  109. fs.setLongitude(cursor.getDouble(cursor.getColumnIndex("longitude")));
  110. fs.setPublicSpot(cursor.getInt(cursor.getColumnIndex("isPublic"))==1);
  111. fs.setComments(cursor.getString(cursor.getColumnIndex("comments")));
  112. String authorId = cursor.getString(cursor.getColumnIndex("author"));
  113. User u = new User();
  114. u.setUserId(authorId);
  115. fs.setOwner(u);
  116. return fs;
  117. }
  118.  
  119. private ContentBrokerDelegate<FishingSpot> delegate;
  120. @Override
  121. public void setDelegate(ContentBrokerDelegate<FishingSpot> delegate) {
  122. this.delegate = delegate;
  123. }
  124.  
  125. }

Comments:

  • Lines 15 - 36: Create a new FishingSpot in the database. I'll provide additional detail for this method, but many of the same principals apply to all methods in this class.
  • Lines 19 - 27: Unlike iOS (and JDBC), values for each column in a table are specified in a ContentValues object. Since the method signature is overloaded, nearly all calls (String, long, int) are identical. The one item of special note is the means by which the primary key column is set to NULL.
  • Line 28 - 35: Insert the data into the database. The return value should be the new primary key. Set this value in the FishingSpot object, and notify the delegate of the results.
  • Lines 38 - 58: Update an existing FishingSpot in the database. This code is very similar to the create() method.
  • Lines 60 - 66: Delete an existing FishingSpot from the database. This code is very similar to the create() method. (Note: For production grade systems, it's necessary to preserve referential integrity; if a FishingSpot is referenced in a Catch then the user should not be able to delete the FishingSpot.)
  • Lines 68 - 83: Retrieve an existing FishingSpot from the database. Lines 73 and 74 are critical: It's important to first move to the first row, and then determine if the cursor is after the last. (Translation: Determine if there are any rows at all.) If a row exists, parse out the retrieved data.
  • Lines 85 - 102: Retrieve all FishingSpot objects from the database. Note that this method iterates over the results as long as the isAfterLast() method returns false.
  • Lines 104 - 117: Given a row of data, parse out the contents of a FishingSpot.
  • Lines 119 - 123: It's possible to make all DB calls synchronously. However, to remain consistent with the web implementation, it's advantageous to notify a delegate when an operation has completed.

This completes the implementation of the concrete database class. With this in place, it's time to update the main code and the ServiceFactory to handle toggling between online and offline mode.