Why use SQLite in Android?
- SQLite is an Open Source Database which is embedded into Android. So, free to use :)
- SQLite has dynamic memory allocation feature and requires only little memory at runtime.
- When optimized for size, the whole SQLite library with everything enabled is [footprint | less than 350KiB in size].
- SQLite is available on every Android device.
- Zero-Configuration, using an SQLite database in Android does not require any database setup.
- Serverless.
- SQLite supports several RDBMS features for example, SQL syntax, transactions and prepared statements.
- Stable Cross-Platform Database File, so data can be used for multiplatform Apps/Games.
- Single Database File.
- Manifest typing.
- Variable-length records.
What is SQLiteOpenHelper?
- To create and upgrade a database in your Android application you usually subclass SQLiteOpenHelper. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.
- In this class you need to override the onCreate() and onUpgrade() methods.
- onCreate() is called by the framework, if the database does not exists.
- onUpgrade() is called, if the database version is increased in your application code. This method allows you to update the database schema.
- Both methods receive an SQLiteDatabase object as parameter which represents the database.
- SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to an SQLiteDatabase object; either in read or write mode.
- The database tables should use the identifier _id for the primary key of the table. Several Android functions rely on this standard.
What is SQLiteDatabase?
- SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.
- More specifically SQLiteDatabase provides the insert(), update() and delete() methods.
- In addition it provides the execSQL() method, which allows to execute an SQL statement directly.
- The object ContentValues allows to define key/values. The "key" represents the table column identifier and the "value" represents the content for the table record in this column. ContentValues can be used for inserts and updates of database entries.
- Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class .
- rawQuery() directly accepts an SQL select statement as input.
- query() provides a structured interface for specifying the SQL query.
- SQLiteQueryBuilder is a convenience class that helps to build SQL queries.
Working Example
MyDatabaseOpenHelper.java
public class MyDatabaseOpenHelper extends SQLiteOpenHelper {
public MyDatabaseOpenHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
String createQuery =
"CREATE TABLE tb_my_table ("
+ " _id integer primary key autoincrement,"
+ " insert_id integer,"
+ " name text,"
+ " full_name text,"
+ " num_roll text,"
+ " num_phone text);";
db.execSQL(createQuery);
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(MyDatabaseOpenHelper.class.getName(), "Upgrading database from version"
+ oldVersion + " to " + newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS tb_my_table");
onCreate(db);
}
}
MyDatabase.java
public class MyDatabase {
private static final String TAG = "DatabaseConnector";
private static final String DB_NAME = "MyDBName";
private SQLiteDatabase database;
private MyDatabaseOpenHelper dbOpenHelper;
public MyDatabase(Context context) {
dbOpenHelper = new MyDatabaseOpenHelper(context, DB_NAME, null, 1);
}
public void openDatabaseConnection() throws SQLException
{
//open database in reading/writing mode
database = dbOpenHelper.getWritableDatabase();
}
public void closeDatabaseConnection() throws SQLException
{
if (database != null)
database.close();
}
public void insertImageListToDatabase(String tableName, String insert_id, String name, String full_name, String num_roll, String num_phone)
{
try {
ContentValues newCon = new ContentValues();
newCon.put("insert_id", image_id);
newCon.put("name", path);
newCon.put("full_name", path_o);
newCon.put("num_roll", numView);
newCon.put("numDone", num_phone);
openDatabaseConnection();
database.insert(tableName, null, newCon);
closeDatabaseConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
public String[][] getImageListFromDB(String tableName){
String insert_id = null;
String name = null;
String full_name = null;
String num_roll = null;
String num_phone = null;
int counter = 0;
int rowCount = 0;
try {
openDatabaseConnection();
Cursor cursorRowCount = database.rawQuery("select count(*) from "+tableName, null);
cursorRowCount.moveToFirst();
rowCount= cursorRowCount.getInt(0);
Log.i(TAG,"Value of rowCount = "+rowCount);
cursorRowCount.close();
} catch (Exception e) {
e.printStackTrace();
}
String[][] result = new String[rowCount][5];
try {
Cursor cursorGetAllData = database.rawQuery("select * from "+tableName, null);
if( cursorGetAllData != null) {
if (cursorGetAllData.moveToFirst()){
do {
insert_id = cursorGetAllData.getString(cursorGetAllData.getColumnIndex("insert_id"));
path = cursorGetAllData.getString(cursorGetAllData.getColumnIndex("path"));
full_name = cursorGetAllData.getString(cursorGetAllData.getColumnIndex("path_o"));
num_roll = cursorGetAllData.getString(cursorGetAllData.getColumnIndex("num_roll"));
num_phone = cursorGetAllData.getString(cursorGetAllData.getColumnIndex("num_phone"));
result[counter][0] = image_id;
result[counter][1] = name;
result[counter][2] = path_o;
result[counter][3] = num_roll;
result[counter][4] = num_phone;
counter++;
}while (cursorGetAllData.moveToNext());
}
}
cursorGetAllData.close();
closeDatabaseConnection();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
public void deleteTable(String tableName){
try {
openDatabaseConnection();
database.execSQL("DROP TABLE IF EXISTS "+tableName);
closeDatabaseConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
public void clearTable(String tableName){
try {
openDatabaseConnection();
// database.execSQL("TRUNCATE TABLE "+tableName);
// SQLite has no TRUNCATE command
database.execSQL("DROP TABLE IF EXISTS "+tableName);
String createQuery =
"CREATE TABLE tb_my_table ("
+ " _id integer primary key autoincrement,"
+ " insert_id integer,"
+ " name text,"
+ " full_name text,"
+ " num_roll text,"
+ " num_phone text);";
database.execSQL(createQuery);
closeDatabaseConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
}
No comments:
Post a Comment