My first assumption of SQLite in Android was, there is a lot of code to do very little. Once you get started though its quite easy.
I’m going to step through how to set up the database connection initially and insert and delete some data from it.
CLICK HERE FOR COMPLETE PROJECT
Within the complete project I have provided examples of how to use the methods which are created bellow.
SETTING UP DATABASE CLASS
1. Create a database class and make it extend SQLiteOpenHelper
2. Create a variable for the database version, something like this:
private static final int DATABASE_VERSION = 1;
3. Give you database a name:
private static final String DATABASE_NAME = “imAnExample”;
4. Create your tables names. You can have as many as you like. Within this example I am only going to have one table.
private static final String TABLE_VEGETABLES = “vegetables”;
5. Now create your columns for the tables:
private static final String KEY_ID = “id”;
private static final String KEY_VEG_TYPE = “type”;
6. Create the constructor for the class it should look something like:
public DatabaseConnection(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
7. Creating the tables in the onCreate method. This is where we will be setting up the columns to use and and key etc. It should look something like:
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase)
{
String CREATE_VEGETABLES_TABLE = "CREATE TABLE " + TABLE_VEGETABLES + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_VEG_TYPE + " TEXT" + ")";
sqLiteDatabase.execSQL(CREATE_VEGETABLES_TABLE);
}
8. If the database is upgraded and you don’t want to keep any of the existing content add in a method similar to this:
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSQL("DROP TABLE IF EXISTS " + TABLE_VEGETABLES);
onCreate(db);
}
Before I continue with this class. I am going to first make a class to retain the data before it is inserted into the database. This is just simply a class of setter and getter methods. e.g. for this example there should be one method for getting/setting the id and one for getting/setting the vegetable name. This class can be found within the complete example.
ADDING/REMOVING/RETURNING ALL FROM THE DATABASE
9. Adding a new vegetable to the database. In this example I have not specified an id, by not doing this the database will take care of it.
// Adding new vegetable to the table
void addVegetable(GettingVegetableData vegetableData)
{
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_VEG_TYPE, vegetableData.getVegetableName()); // vegetable Name
// Inserting Row
db.insert(TABLE_VEGETABLES, null, values);
db.close(); // Closing database connection
}
10. Returning all of the vegetables in a list from the database.
// Getting All vegetables
public List gettingVegetableDataList()
{
List listOfVegetables = new ArrayList();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_VEGETABLES;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst())
{
do
{
GettingVegetableData vegetableData = new GettingVegetableData();
vegetableData.setId(Integer.parseInt(cursor.getString(0)));
vegetableData.setVegetableName(cursor.getString(1));
// Adding vegetableData to list
listOfVegetables.add(vegetableData);
}
while (cursor.moveToNext());
}
// return contact list
return listOfVegetables;
}
11. There is two possible ways in order to delete a vegetable from the database. By its ID or its name. I have shown the two ways bellow:
// Deleting single vegetable by id
public void deleteVegetableByID(int id)
{
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_VEGETABLES, KEY_ID + "= " + id, null);
db.close();
}
// Deleting single vegetable by name
public void deleteVegetableByName(String name)
{
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_VEGETABLES, KEY_VEG_TYPE + "= " + name, null);
db.close();
}
ProgramThat; 