Android Database to Array

Question

I'm entirely new to Android Java, especially database linkage. So far I've got this, which all seems to work, I just now need to get the database values from the database to an array.

package com.example.sleepertrain5;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;


public class DataBaseHelper extends SQLiteOpenHelper{
private static String DB_PATH = "/sleepertrain5/assets";
private static String DB_NAME="info2.sqlite";
private SQLiteDatabase myDatabase;
private final Context myContext;

public DataBaseHelper(Context context){
super(context, DB_NAME, null, 1);
this.myContext=context;

}

public void createDataBase() throws IOException{
boolean dbExist = checkDataBase();

if(dbExist){
    //nothing needs done
}else{
    this.getReadableDatabase();

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

    }
}
private boolean checkDataBase(){
SQLiteDatabase checkDB = null;

try{
    String myPath = DB_PATH + DB_NAME;
    checkDB = SQLiteDatabase.openDatabase(myPath, null,         SQLiteDatabase.OPEN_READONLY);
}catch(SQLiteException e){
    //no databases they don't exist
}
    if (checkDB != null){
        checkDB.close();
    }
    return checkDB != null ? true : false;
}

private void copyDataBase() throws IOException{
InputStream myInput = myContext.getAssets().open(DB_NAME);
String outFileName = DB_PATH +DB_NAME;
OutputStream myOutput = new FileOutputStream(outFileName);

byte[] buffer = new byte[1024];
        int length;
while ((length=myInput.read(buffer))>0){
    myOutput.write(buffer,0,length);
}

myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException{
//Open database
String myPath = DB_PATH + DB_NAME;
myDatabase = SQLiteDatabase.openDatabase(myPath,  null, SQLiteDatabase.OPEN_READONLY);

}

public synchronized void close(){
if(myDatabase != null)
    myDatabase.close();
super.close();
}

@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub

}
}

How would I go about reading from this into an array? I'm struggling to make sense of this at the moment, so any help would be fantastic.

EDIT: The plan is to read the data, which is coordinates and names, into the array, which I can then use later to draw markers onto a GoogleMap. GoogleMap is all set up and I think I know what I'm doing from there, but this is the part I fall down on. The array would have to be multidimensional.

Answer 1

Android uses SQLite database and SQLite query syntax for database accessing.

For querying the database directly through the SQLiteDatabase db variable you can do:

String table = CONTACT_TABLE;
String columns = {CONTACT_COLUMN_NAME};
String selection = CONTACT_COLUMN_NAME + "=" + MY_NAME;
String[] selectionArgs = {"wangyif2"};
Cursor c = db.query(table, columns, selection, selectionArgs, null, null, null, null);

This will return you a Cursor object, which you can understand as an iterator that contains all the result matching your query. You can then step through the cursor like you would with any array that is converted to an iterator.

c.moveToFirst();
while (!c.isAfterLast()) 
{
    arr[i]  = cur.getString(0);
    i++;
    c.moveToNext();
}

Answer 2

Ok, so the easiest way to work with SQLite in my opinion is using this three-class approach. I've read through a few tutorials and neither really did the trick for me....

So, here we go.

Table definition

    package com.test.sqlite;

import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class ContactTable
{
//key identifiers / column names
public static final String      KEY_ROWID       = "_id";
public static final String      KEY_NAME        = "name";
public static final String      KEY_URI         = "uri";
public static final String      TABLE_NAME      = "contacts";

//useful stuff
public static final String[]    TABLE_COLUMNS   = { KEY_ROWID, KEY_NAME, KEY_URI };     //public makes it more useful
private static final String[]   TABLE_COLTYPES  = { "integer primary key autoincrement", "text not null", "text not null" };

// Database creation SQL statement in lazy-pretty version
private static final String     TABLE_CREATE    = "create table " + TABLE_NAME + "("
                                                        + TABLE_COLUMNS[0] + " " + TABLE_COLTYPES[0] + ","
                                                        + TABLE_COLUMNS[1] + " " + TABLE_COLTYPES[1] + "," 
                                                        + TABLE_COLUMNS[2] + " " + TABLE_COLTYPES[2] + ");";

private static final String     LOGTAG          = "ContactTable";


public static void onCreate(SQLiteDatabase database)
{
    database.execSQL(TABLE_CREATE);
}

public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion)
{
    Log.w(LOGTAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(database);
}

public static void scratch(SQLiteDatabase database)
{
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    database.execSQL(TABLE_CREATE);
}
}

Now that we have set that up, we need Database Helper class, to ease the use of it.

The helper class

package com.test.sqlite;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class ContactDBHelper extends SQLiteOpenHelper
{
// 'main' package name
private static final String PACKAGE_NAME        = "com.test.demo";      

private static final String DATABASE_PATH       = "/data/data/" + PACKAGE_NAME + "/databases/";
private static final String DATABASE_NAME       = "contactdata";
private static final int    DATABASE_VERSION    = 1;

private Context             myContext;

public ContactDBHelper(Context context)
{
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    myContext = context;
}

// Method is called during creation of the database
@Override
public void onCreate(SQLiteDatabase database)
{
    ContactTable.onCreate(database);
}

// Method is called during an upgrade of the database,
// e.g. if you increase the database version
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion)
{
    ContactTable.onUpgrade(database, oldVersion, newVersion);
}

public void scratch(SQLiteDatabase database)
{
    ContactTable.scratch(database);
}
/**
 * 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.
        File dirFile = new File(DATABASE_PATH);
        if (!dirFile.exists())
        {
            dirFile.mkdir();
        }

        this.getReadableDatabase();

        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 doesn't
 */
private boolean checkDataBase()
{
    SQLiteDatabase checkDB = null;
    try
    {
        String myPath = DATABASE_PATH + DATABASE_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
    } catch (SQLiteException e)
    {
        // database does't 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(DATABASE_NAME);

    // Path to the just created empty db
    String outFileName = DATABASE_PATH + DATABASE_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[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
    String myPath = DB_PATH + DB_NAME;
    myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

}
*/
}

And finally, the adapter, which totally does what you want.

DatabaseAdapter

package com.test.sqlite;

import java.util.ArrayList;

import com.test.demo.Contact;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import static com.test.sqlite.ContactTable.*;   //contains database fields

public class ContactDBAdapter
{

private Context         context;
private SQLiteDatabase  db;
private ContactDBHelper dbHelper;

public ContactDBAdapter(Context context)
{
    this.context = context;
}

public synchronized ContactDBAdapter open() throws SQLException
{
    dbHelper = new ContactDBHelper(context);
    db = dbHelper.getWritableDatabase();
    return this;
}

public synchronized void close()
{
    dbHelper.close();
}

/**
 * Create a new Contact entry. If the entry is successfully created return the new
 * rowId for that note, otherwise return a -1 to indicate failure.
 */
public long createRow(Contact contact)
{
    ContentValues values = createContentValue(contact);
    return db.insert(TABLE_NAME, null, values);
}

/**
 * Update a row / entry
 */
public boolean updateRow(long rowIndex, Contact contact)
{
    ContentValues values = createContentValue(contact);

    return db.update(TABLE_NAME, values, KEY_ROWID + "=" + rowIndex, null) > 0;
}

/**
 * Deletes a row
 */
public boolean deleteRow(long rowIndex)
{
    return db.delete(TABLE_NAME, KEY_ROWID + "=" + rowIndex, null) > 0;
}

public void deleteAllRows()
{
    for(int i = 0; i < fetchAllEntries().getCount(); i++)
        deleteRow(i);
}

/**
 * Return a Cursor over the list of all Contacts in the database
 * 
 * @return Cursor over all contacts
 */
public Cursor fetchAllEntries()
{
    return db.query(TABLE_NAME, TABLE_COLUMNS, null, null, null, null, null);
}

/**
 * Return a Cursor positioned at the defined Contact
 */
public Cursor fetchEntry(long rowIndex) throws SQLException
{
    Cursor mCursor = db.query(true, TABLE_NAME, TABLE_COLUMNS, KEY_ROWID + "=" + rowIndex, null, null, null, null, null);
    if (mCursor != null)
    {
        mCursor.moveToFirst();
    }
    return mCursor;
}

/**
 * Fetch all entries and rebuild them as Contact objects in an ArrayList. 
 * If no results are found, an empty list is returned.
 * 
 * @return ArrayList of Contacts 
 */
public ArrayList<Contact> fetchAllContacts()
{
    ArrayList<Contact> res = new ArrayList<Contact>();

    Cursor resultSet = fetchAllEntries();

    if (resultSet.moveToFirst() != false)
        for(int i = 0; i < resultSet.getCount(); i++)
        {
            String name = resultSet.getString(resultSet.getColumnIndex(KEY_NAME));
            String URI = resultSet.getString(resultSet.getColumnIndex(KEY_URI));

            Contact c = new Contact(name, URI);

            res.add(c);
            if(resultSet.moveToNext() == false)
                break;
        }
    resultSet.close();
    return res;
}

public synchronized void reflectWith(ArrayList<Contact> contacts)
{
    //      deleteAllRows();
    dbHelper.scratch(db);
    contacts.trimToSize();
    //empty contact
    Contact empty = new Contact();
    empty.empty();

    for(Contact c : contacts)
    {
        if(!c.getName().equals(empty.getName()))
            createRow(c);   //if not empty, add it
    }
}

private ContentValues createContentValue(Contact contact)
{
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, contact.getName());
    values.put(KEY_URI, contact.getURI());
    return values;
}

}

this is how it's used:

public void onCreate(Bundle savedInstanceState)
{
    super.onCreate(savedInstanceState);
    dbAdapter = new ContactDBAdapter(getApplicationContext());
    dbAdapter.open();


    setContentView(R.layout.main);

    // list stuff
    contacts = new ArrayList<Contact>();
    contacts = dbAdapter.fetchAllContacts();

    //empty placeholders
    if (contacts.size() < 5) for (int i = 0; i < 5 - contacts.size(); i++)
    {
        Contact c = new Contact();
        c.empty();
        contacts.add(c);
    }
    //      contacts.addAll(dbAdapter.fetchAllContacts());
...
}

If you have questions, do ask.