Android Tutorial – Using SQLite Databases

We will talk about one way of managing data in Android Applications (http://d.android.com/guide/topics/data/data-storage.html).

(Note : This tutorial isnt for learning learn SQLite Syntax.)

First, We will create a helper class to manage creation of our DataBase and manage it’s Operations,

where it will be accessible only through our application Only. we will define our class that will extends SQLiteOpenHelper (http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html).

import java.util.ArrayList; //used to store our data later.

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.SQLException;

import android.database.sqlite.*;

import android.util.Log;

public class MyDBOpenHelper extends SQLiteOpenHelper{

private static final int DATABASE_VERSION = 2;

private static final String Order_TABLE_NAME = “Orders_Table”;

private static final String KEY_NAME = “order_name”;

private static final String KEY_STATUS = “status_name”;

private static final String Order_TABLE_CREATE =

“CREATE TABLE “ + Order_TABLE_NAME + ” (“ +

KEY_NAME + ” TEXT, “ +

KEY_STATUS + ” TEXT);”; //Query string to create our table.

public MyDBOpenHelper(Context context) {

super(context, Order_TABLE_NAME, null, DATABASE_VERSION);

}

@Override

public void onCreate(SQLiteDatabase db) {

db.execSQL(Order_TABLE_CREATE);

}

@Override

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

// TODO Auto-generated method stub

}

Next step we will start querying data, our resulted data will be returned in ArrayList<Order> type

(where Order is an already defined class in our application).

You can then get an instance of your SQLiteOpenHelper implementation using the constructor you’ve defined. To write to and read from the database, call getWritableDatabase() and getReadableDatabase(), respectively. These both return a SQLiteDatabase object that represents the database and provides methods for SQLite operations.

public static class OrderManager

{

private MyDBOpenHelper _db_Orders;

private static final String GET_Orders = “SELECT * FROM “+Order_TABLE_NAME ;

public OrderManager(Context context)

{

_db_Orders = new MyDBOpenHelper(context);

}

public boolean insert(String orderName, String orderStatus)

{

try

{

SQLiteDatabase sqlite = _db_Orders.getWritableDatabase();

/*

sqlite.execSQL(“INSERT INTO “+ Order_TABLE_NAME +

” (” + KEY_NAME +”, “+ KEY_STATUS + “)” +

” VALUES (‘” + orderName + “‘, ‘” + orderStatus + “‘)”);

*/

ContentValues initialValues = new ContentValues();

initialValues.put(KEY_NAME, orderName);

initialValues.put(KEY_STATUS, orderStatus);

sqlite.insert(Order_TABLE_NAME, null, initialValues);

}

catch(SQLException sqlerror)

{

Log.v(“Insert ERROR”, sqlerror.getMessage());

return false;

}

return true;

}

public ArrayList<Order> getOrders()

{

ArrayList<Order> orders = new ArrayList<Order>();

SQLiteDatabase sqliteDB = _db_Orders.getReadableDatabase();

Cursor crsr = sqliteDB.rawQuery(GET_Orders, null);

Log.v(“Select Query result”, String.valueOf(crsr.getCount()) );

crsr.moveToFirst();

for(int i=0; i < crsr.getCount(); i++)

{

orders.add(new Order(crsr.getString(0), crsr.getString(1)));

//Log.v(“DATA”, crsr.getString(0) + ” ” +crsr.getString(1));

crsr.moveToNext();

}

return orders;

}

}

References :

similar code source :

http://developer.android.com/guide/topics/data/data-storage.html#db

http://hackaday.com/2010/07/21/android-development-101-part-3introduction-to-databases/

getWritableDataBase():

http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#getWritableDatabase%28%29

getReadableDataBase():

http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#getReadableDatabase%28%29

rawquery:

http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery%28java.lang.String,%20java.lang.String[]%29

Cursor:

http://developer.android.com/reference/android/database/Cursor.html

About these ads