Do I use SQLite when I want a back end database with no output?

Question

I know it sounds stupid. I'm stupid so ill keep it brief.

I'm developing an android app for a project. I need a backend database which contains 40 records of latitude and longitude map coordinates. I DONT need a data entry form in the app. All i want to do is have the data stored and then to access it when needed.

e.g. Im storing coordinates from google maps and I want to use the longitude and latitude coordinates in my database to put a marker on the map.

All tutorials I have looked at for SQLiteDatabases have data entry forms with CRUD. I don't need these features and it makes me think im starting in the wrong place. Is SQLite even what I should be looking into?

The data will be stored on my local machine and not a server.

I imagine this is a very basic thing to be cleared up.

If you want a server side database, then you are in principle free to choose any database you wish (SQL Server, SQLite, MySQL, etc.). Any reason you are thinking of using SQLite? — Tim Biegeleisen, Nov 28 '17 at 01:21
@TimBiegeleisen Im developing the app in Android Studio, I did some looking around and there seemed to be alot of SQLite stuff in Android Studio forum posts. If theres a better alternative you would suggest then im open ears. Thanks for the reply :) — , Nov 28 '17 at 01:42
So here's the deal: If your app can hit any endpoint, a probably good assumption, then you can create whatever web service you want, which in turn can use any database you want. SQLite in the context of Android usually means an embedded database in the phone itself. — Tim Biegeleisen, Nov 28 '17 at 01:43
@TimBiegeleisen, I'm not going to host the app anywhere, Its going to be completely local on my machine and ran on an emulator or to a device plugged into my computer. Currently I have my data saved in an Excel document and I have imported it into a SQLite database. — , Nov 28 '17 at 01:52
I have the same setup right now at home. You can use any database which your computer would support. — Tim Biegeleisen, Nov 28 '17 at 01:59
@TimBiegeleisen Ah awesome! This has cleared things up loads, thanks! — , Nov 28 '17 at 02:02
Wait...I should mention that I have a Java web app on my computer which actually hits the database. But you may use any technology you wish. — Tim Biegeleisen, Nov 28 '17 at 02:04

SHartley · Answer 1 · 2017-12-01T08:05:31.127

If this is a single app project for yourself and there's not much data, then you can simply use the SharedPreferences system to store things.

protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  settings = getSharedPreferences(getPackageName(), MODE_PRIVATE);
  SharedPreferences.Editor editor = settings.edit();
  editor.putString("lat0",38.162206);
  editor.putString("lat1",19.178379);
  editor.putString("lat2",-14.400629);
  editor.putString("long0",-102.128906);
  editor.putString("long1",9.316406);
  editor.putString("long2",-61.699219);
  editor.apply();
  // ...
  //maybe read back with
  double long1 = Double.valueof(settings.getString("long0",null));

If you need to store store a large set, then using sqlite is the easiest way to go, since it comes with the APIs. You can manually code and save the data for recall later. Something like this might work:

  protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    ArrayList<Double> markerLatitude = new ArrayList<>();
    ArrayList<Double> markerLongitude = new ArrayList<>();
    File databaseFile = getDatabasePath("coordinates.db");
    //create table for 1st time if not existing
    SQLiteDatabase db = openOrCreateDatabase(databaseFile.toString(), Context.MODE_PRIVATE, null);
    db.execSQL("CREATE TABLE IF NOT EXISTS \"coordinates\" (latitude real, longitude real)");
    Cursor result = db.rawQuery("SELECT count(*) FROM \"coordinates\"", null);
    //if nothing there...
    if (!(result.moveToFirst() && result.getInt(0) == 0)) {
      //populate table
      db.execSQL("INSERT INTO coordinates (latitude, longitude) VALUES(38.162206,-102.128906)");
      db.execSQL("INSERT INTO coordinates (latitude, longitude) VALUES(19.178379,9.316406)");
      db.execSQL("INSERT INTO coordinates (latitude, longitude) VALUES(-14.400629,-61.699219)");
      // ...
    }
    // read values
    else {
      result = db.rawQuery("SELECT latitude, longitude FROM \"coordinates\"", null);
      result.moveToFirst();
      do {
        //populate some lists or do whatever with the retrieval
        markerLatitude.add(result.getDouble(result.getColumnIndex("latitude")));
        markerLongitude.add(result.getDouble(result.getColumnIndex("longitude")));
      }
      while (result.moveToNext());
    }
    cursor.close();

If this is a distributed app, or you need to frequently edit your data, you need to create an assets folder (Android Studio: app->src->main right-click new->folder->assets) and place your data file in there (json, sqlite, ect) and read it back later. You can use a desktop program such as DB browser for sqlite to edit your sqlite database. See Where do I place the assets folder. See Saving Data Using SQLite. There's also the Room Persistence Library.

the second option is what im looking to do. The source you provided for using your own sqlite database is from 2009, I've already looked at that and it doesn't work. Surely it shouldn't be this complicated to just have a database file already created, and just refer to parts of that database in the code. — , Nov 28 '17 at 16:43
If you only have 40 unchanging coordinates, just bake in some double arrays i.e. double lat[]={38.162206,19.178379,-14.400629}; — SHartley, Dec 01 '17 at 08:23

Himanshu · Answer 2 · 2017-12-01T08:49:35.660

I ran into a similar problem when I needed to store read only tabular data for my app. Using SQLite from Java was neither fast nor small.

I chose Arrays of Strings and those took me far:-

final static String[][] values = {
            {"00%", "00%", "00%", "00%"},
            {"00%", "30%", "40%", "50%"},
            {"00%", "30%", "40%", "50%"},
            {"00%", "45%", "55%", "65%"},
}

Until I wanted to do some data querying and filtering. While SQL and SQLite are great tools for that, their Java examples scared me away. I also found Java 8 streams complicated, but they might work for you.

Then I migrated one file to Kotlin:-

val values = listOf(listOf("00%", "00%", "00%", "00%"),
                    listOf("00%", "30%", "40%", "50%"),
                    listOf("00%", "30%", "40%", "50%"),
                    listOf("00%", "45%", "55%", "65%"))

And that has worked nicely so far:-

values.filter({it -> it[1] == "30%"}) // get rows where 2nd col == 30%

More here

score 0 · Answer 3 · answered Dec 01 '17 at 09:05

As other answers suggest there is not one solution. It heavily depends on your needs. If your data is not changing (coordinates) you can save it in strings file in res directory or even you can have a java array that holds the data for you. If you need to change the data you can use sqlite or any other db, sharedPreferences or saving it in a file. working with databases is at first hard but you can manage it much easier than other forms.

Do I use SQLite when I want a back end database with no output?

3 Answers3