Supporting Classes - Android

Configuration

For Android, there's no need to add additional libraries to the project; SQLite support is natively included. It may be necessary, however, to update application.xml to provide permission for writing to the file system:

  1. <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>

AbstractServiceDB

This class contains general-purpose functionality that's common to all DB services.

AbstractServiceDB.java

  1. package com.crowleyworks.futilefishing.service;
  2.  
  3. import com.crowleyworks.futilefishing.Main;
  4.  
  5. import android.content.Context;
  6. import android.database.sqlite.SQLiteDatabase;
  7. import android.database.sqlite.SQLiteOpenHelper;
  8. import android.database.sqlite.SQLiteDatabase.CursorFactory;
  9.  
  10. public abstract class AbstractServiceDB {
  11.  
  12. public static final String COL_ID = "id";
  13. public static final String LOC_Q_MANY =
  14. "select id, name, author, latitude, longitude, isPublic, comments from locations order by name";
  15. public static final String LOC_Q_ONE =
  16. "select id, name, author, latitude, longitude, isPublic, comments from locations order by name where id=";
  17.  
  18. public static final String BAIT_Q_MANY = "select id, name, author, descr, image from bait order by name";
  19. public static final String BAIT_Q_ONE = "select id, name, author, descr, image from bait order by name where id=";
  20.  
  21. public static final String FISH_Q_MANY = "select id, name, author, descr, restrictions from fish order by name";
  22.  
  23. public static final String GET_LAST_KEY = "select last_insert_rowid();";
  24.  
  25. private class DbManagementHelper extends SQLiteOpenHelper {
  26. private final String DB_CREATE_USERS =
  27. "create table users (userid text primary key, username text)";
  28. private final String DB_CREATE_LOCATIONS =
  29. "create table locations (id integer primary key, name text, author text, latitude real, longitude real,"
  30. +" isPublic integer, comments text, createdLocally integer)";
  31. private final String DB_CREATE_BAIT =
  32. "create table bait (id integer primary key, name text, "
  33. +"author text, descr text, createdLocally integer, image blob)";
  34. private final String DB_CREATE_FISH =
  35. "create table fish (id integer primary key, name text, author"
  36. +" text, descr text, createdLocally integer, restrictions text)";
  37. private final String DB_CREATE_CATCHES =
  38. "create table catches (id integer primary key, author text, catchdate integer, locationblurred integer, "
  39. +" locationid integer, fishid integer, customlat real, customlng real, length real, weight real, "
  40. +" comments text, createdLocally integer, imagepath text)";
  41.  
  42. public DbManagementHelper(Context context, String name, CursorFactory factory, int version) {
  43. super(context, name, factory, version);
  44. }
  45.  
  46. @Override
  47. public void onCreate(SQLiteDatabase db) {
  48. db.execSQL(DB_CREATE_USERS);
  49. db.execSQL(DB_CREATE_LOCATIONS);
  50. db.execSQL(DB_CREATE_BAIT);
  51. db.execSQL(DB_CREATE_FISH);
  52. db.execSQL(DB_CREATE_CATCHES);
  53. }
  54.  
  55. @Override
  56. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  57. db.execSQL("drop table if exists catches");
  58. db.execSQL("drop table if exists locations");
  59. db.execSQL("drop table if exists bait");
  60. db.execSQL("drop table if exists fish");
  61. db.execSQL("drop table if exists users");
  62. onCreate(db);
  63. }
  64. }
  65.  
  66. private DbManagementHelper mHelper;
  67. protected SQLiteDatabase mDb;
  68.  
  69. /*
  70. * Make the initial connection to the database
  71. */
  72. public void open() {
  73. mHelper = new DbManagementHelper(Main.getMainActivity(), "futilefishing.db", null, 1);
  74. mDb = mHelper.getWritableDatabase();
  75. }
  76.  
  77. /*
  78. * Close the connection
  79. */
  80. public void close() {
  81. mDb.close();
  82. mHelper.close();
  83. }
  84. }

Comments:

  • Lines 12 - 23: Some generic constants that will be used in the concrete subclasses.
  • Lines 25 - 64: Some necessary structural code to set up a database (and the associated tables) when the app is run for the first time. As with iOS, it's also possible to include a pre-populated database. (Note: The upgrade capability is missing from iOS.)
  • Lines 69 - 83: Basic boilerplate code for opening (and closing) a connection to the database.

The LocationService Interface

There are no change to the LocationService interface, but I'm displaying it below for parity. Also, the next section will provide a concrete implementation of the interface, so it's convenient to have the interface close by.

  1. package com.crowleyworks.futilefishing.service;
  2.  
  3. import com.crowleyworks.futilefishing.model.FishingSpot;
  4.  
  5. public interface LocationService {
  6.  
  7. public void create(FishingSpot l) throws ServiceException;
  8. public void update(FishingSpot l) throws ServiceException;
  9. public void delete(FishingSpot l) throws ServiceException;
  10. public void get(long id) throws ServiceException;
  11. public void getMany(String criteria) throws ServiceException;
  12.  
  13. public void setDelegate(ContentBrokerDelegate<FishingSpot> delegate);
  14.  
  15. }

Now that the superclass and the interface have been described, it's time to move on to the database implementation details.