How to create Android Database application?

Question

I am new to Databases and do not know if this would be the best way to do what I want to achieve. I want to create a custom list of restaurants around me, then search them, and sort them on ranking, title, or location.

Would I need to create a database for this? I technically could use a text file and Arrays but I feel like this is very inefficient. I would need a Node to contain the following data: Name of Establishment, Address, Phone Number, Ranking (Based of Our Ranking System).

How should I go about doing this?

Answer 1

Yes, you definitely want to use a database. If you use a database local to the phone, you need to use an SQLite database. Here is a good place to start.

If you want a database that is preloaded in the phone, put it in your assets folder. Here is an example of a database helper class with the database packaged with the phone:

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Vector;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper{

private static final String TAG = "DataBaseHelper";

//The Androids default system path of your application database.
private static String DB_PATH = "/data/data/yourpackage/databases/";

private static String DB_NAME = "DatabaseName";

public SQLiteDatabase myDataBase; 

private final Context myContext;


    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     * @param context
     */
    public DataBaseHelper(Context context) {

        super(context, DB_NAME, null, 1);
        this.myContext = context;
    }   

  /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{

        boolean dbExist = checkDataBase();

        if(dbExist){
            //do nothing - database already exist
        }else{

            //By calling this method and empty database will be created into the default system path
               //of your application so we are gonna be able to overwrite that database with our database.
            this.getWritableDatabase();

            try {

                copyDataBase();

            } catch (IOException e) {

                throw new Error("Error copying database");

            }
        }

    }

    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesnt
     */
    private boolean checkDataBase(){

        SQLiteDatabase checkDB = null;

        try{
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

        }catch(SQLiteException e){

            //database doest exist yet.

        }

        if(checkDB != null){

            checkDB.close();

        }

        return checkDB != null ? true : false;
    }

    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException{



        //Open your local db as the input stream
        InputStream myInput = myContext.getAssets().open(DB_NAME);

        Log.d(TAG, "found the database");
        // Path to the just created empty db
        String outFileName = DB_PATH + DB_NAME;


        //Open the empty db as the output stream
        OutputStream myOutput = new FileOutputStream(outFileName);


        //transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[2048];
        int length;
        while ((length = myInput.read(buffer))>0){
            myOutput.write(buffer, 0, length);
        }

        //Close the streams
        myOutput.flush();
        myOutput.close();
        myInput.close();

    }

    public void openDataBase() throws SQLException{

        //Open the database
        String myPath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    @Override
    public synchronized void close() {

            if(myDataBase != null)
                myDataBase.close();

            super.close();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

        // Add your public helper methods to access and get content from the database.
       // You could return cursors by doing "return myDataBase.query(....)" so itd be easy
       // to you to create adapters for your views.

Answer 2

What you want to do is create a Web Service, this lives in the "cloud" on the Internets somewhere. Then you have your mobile app periodically contact the web service and request restaurant data perhaps based on the users current location, so the app passes the gps coordinates to the web service and the Web Service checks its database for restaurants within X distance of that location, then the Web Service returns the list to the app in a format such as Json or XML, the App parses this info and displays it to the user.

Ideally you would also have a database on the phone that would store results on the device this reduces the amount of requests you have to pull from the server, saving on battery, data and server resources. This gets a little complicated as you have to decide what to ask for intelligently and the server has to decide what to send you.

There was a Google I/O talk on how to do this. http://www.youtube.com/watch?v=xHXn3Kg2IQE

Answer 3

A SQLite database would be an excellent way to accomplish your application goals. Based on what you said, a single table called "Restaurants" containing 5 fields: _id, Name, Address, Phone, Rank would do the trick.

The SQLite SQL documentation is located here: http://www.sqlite.org/lang.html . Some of the commands you'll need to be familiar with are CREATE TABLE, INSERT, UPDATE and SELECT. Sqlite for Android is a well-documented standard found across the web. You should have no trouble finding examples of how to accomplish your task.

Remember to close any database objects that you create else, you'll create a leak.

Good luck!