Android: Databases: Handling databases

Create SQLite database in app

public class DatabaseHelper extends SQLiteOpenHelper {


 private static final String DATABASE_NAME = "DATABASE_NAME";

 private static final int SCHEMA_VERSION = 1;


 ...


 private DatabaseHelper() {


  super(ReportTvApplication.getContext(), DATABASE_NAME, null, SCHEMA_VERSION);

 }

}

Pre-existing SQLite database file from the "assets" folder.

1. Preparing the SQLite database file.

Open your database and add a new table called "android_metadata":

CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');

INSERT INTO "android_metadata" VALUES ('en_US');

Then, it is necessary to rename the primary id field of your tables to "_id" (or add a primary key field called "_id") so Android will know where to bind the id field of your tables.

2. Copying, opening and accessing your database in your Android application.

Now just put your database file in the "assets" folder of your project and create a Database Helper class by extending the SQLiteOpenHelper class from the "android.database.sqlite" package.

Make your DataBaseHelper class look like this:

public class DatabaseHelper extends SQLiteOpenHelper {


    private static final String DATABASE_NAME = "database.sql";
    private SQLiteDatabase myDataBase;
    private final Context myContext;


    public DatabaseHelper(Context context) {

        super(context, DATABASE_NAME, null, 1);

        this.myContext = context;
    }


    public void createDataBase() throws IOException {

        boolean dbExist = databaseExists();

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

        } else {

            this.getReadableDatabase();

            try {
                copyDataBase();

            } catch (IOException e) {
                throw new Error("Error copying database");
            }
        }
    }


    private boolean databaseExists() {

        SQLiteDatabase checkDB = null;

        try {
            final String myPath = getDatabasePath();

            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

        } catch (SQLiteException e) {
            // database does't exist yet.
        }

        if (checkDB != null) {
            checkDB.close();
        }

        return checkDB != null ? true : false;
    }

    private String getDatabasePath() {

        final String myPath = myContext.getDatabasePath(DatabaseHelper.DATABASE_NAME)
                .getAbsolutePath();

        return myPath;
    }

    private void copyDataBase() throws IOException {

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

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

        // transfer bytes from the inputfile to the outputfile
        byte[] buffer = new byte[1024];

        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

        myDataBase = SQLiteDatabase.openDatabase(getDatabasePath(), 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 it'd be easy

       // to you to create adapters for your views.


}

Example query with distinct

public List<String> getSimulations() {


  String[] projection = new String[] { SIMULATION_ID };


// The first boolean corresponds to distinct

  Cursor cursor = getReadableDatabase().query(true, TABLE, projection, null, null, null, null, null, null);


  return getSimulationsFromCursor(cursor);


 }


private List<String> getSimulationsFromCursor(Cursor cursor) {


  List<String> simulations = new ArrayList<String>();


  // This is the safe way to check if the cursor has content

  if (cursor == null || !cursor.moveToFirst()) {

   return null;

  }


  // For whatever reason cursor.isLast() wasn't working for me, I could go past the last

  for (int i = 0; i < cursor.getCount(); i++, cursor.moveToNext()) {


  // The index belongs to the index of the field in the projection string

   simulations .add(cursor.getString(0));

  }


  return scenarios;

 }