SQLite Android
Sqlite is a open source database which is embedded in to android . Sqlite support relation database features like sql syntax ,transactions prepared statements but it need a little amount of memory approx 250 kbytes at run time.
Data Types:
TEXT equals to String in Java
INTEGER equals to Long in Java
REAL equals to Double in Java
SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa.
SQLite in android:
Accessing the database will involves accessing the filesystem. Which will be slow. Therefore we need to use the AsyncTask for database operations.
Note : All the database transaction should be done in the ASyncTask class
if you create a database through an application the database will be stored in the file system in the
/DATA/data/App_Name/databases/dbfile_name
here DATA represents the return path of Environment.getDataDirectory()
Packages:
android.database contains all the database specific classes.
android.database.sqlite conatins the SQL related classes.
SQLiteOpenHelper():
To create or upgrade the database in android application we us usually use subclass the SQLiteOpenHelper .In the constructor of your subclass yu need to call the super with the database name and the database version.
In this class you need to override 2 methods..
1.OnCreate() :Called by the framework when there is no database exists
2.OnUpgrade() : Called when the database version is increased.
Both methods receives the SQLiteDatabase objects as the parameters.
SQLIteOpenHelper provides two methods getReadableDatabase() and getWritableDatabase() to get access to SQLiteDatabase either in read or write mode.
The database tables should use the identifier _id for the primary key of the table
It is best practice to create separate class per table . Create static methods oncreate() and onUpgrade().
These method can be called by the corresponding methods of the SQLiteOpenHelper.
SQLiteDatabase:
this is the base class for working with the sql statements for querying to insert ,delete update the data in the database tables.
In addition it provides the execSQL() method to execute the sql statements.
The object ConetentValues allows to define the key/value. The key represents the column identifier and the value represents the content for the column.
Queries can be created via rawQuery() and query() or SQLiteBuilder class.
1.RawQuery() directly accepts the Sql select statements as input.
2.Query() provides a structured interface for specifying the SQL query.
RawQuery():
Cursor cusrsor=getReadableDatabase.rawQuery("select * from emp where _id=?",new String[]{ id });
Cursor cursor=getReadableDatabase().rawQuery("select * from emp where _id=?",new String[] { id } );
2. Query():
Cursor cursor=database.query(DATABASE_TABLE,new String[] {EMPID,EMPNAME,EMPSAL}(column that shoud be fetched),
where EMPID=?(where clause) ,new String[]{ "1000" } ,null(groupby),null(having),null(orderby) );a
Cursor :
A query returns Cursor object. Cursor represents the result of a query and basically points to on row the query result.
Methods:
getCount() get the number of elements in the query result.
MoveToFirst() and MoveToNext() to move between the rows
isAfterLast() to check if the end of the query result has been reached.
Cursor provides typed get*() methods, e.g. getLong(columnIndex), getString(columnIndex) to access the column data for the current position of the result. The "columnIndex" is the number of the column you are accessing.
A full database app example :
super(context, DB_NAME, null, DB_VERSION);
}
EMPLOYEE.EMP_ID.name()+ " INTEGER PRIMARY KEY AUTOINCREMENT, " +
EMPLOYEE.EMP_NAME.name()+" VARCHAR(60) NOT NULL, "+
EMPLOYEE.EMP_NO.name()+" VARCHAR(30) NOT NULL, "+
EMPLOYEE.EMP_SAL.name()+" VARCHAR(60) NOT NULL " +
");";
public void onCreate(SQLiteDatabase db) {
String CREATE_EMPLOYEE_TABLE="CREATE TABLE IF NOT EXISTS "+EMPLOYEE.class.getSimpleName()+"("+
EMPLOYEE.EMP_ID.name()+ " INTEGER PRIMARY KEY AUTOINCREMENT, +
EMPLOYEE.EMP_NAME.name()+" VARCHAR(60) NOT NULL, "+
EMPLOYEE.EMP_NO.name()+" VARCHAR(30) NOT NULL, "+
EMPLOYEE.EMP_SAL.name()+" VARCHAR(60) NOT NULL " +
");";
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// when the database version has been changes this method will be called
//drop the current table and create the new table if there are any new cols added to the table.
db.execSQL("DROP TABLE IF EXISTS "+EMPLOYEE.class.getSimpleName());
onCreate(db);
}
That's it about creating the database and creating the table in database.
Now it time for add the content to the database table and querying the database table to fetch the data that we need..
Create a manager class which manages all the database calls like addition ,adaptation and deletion .
final public class EmployeeManager {
private static final String TAG = EmployeeManager.class.getSimpleName();
}
Create a DatabaseHandler class which will be done the basic operation of opening the database in red or write modes and calling the manager class for the appropriate actions.
final public class DatabaseHandler {
private final static String TAG=DatabaseHandler.class.getSimpleName();
}
make the classes singleton to made the class instantiate only once..and with no public constructor.
Code for DatabaseHandler:
private static DatabaseHandler databaseHandler;
private DatabaseHandler(Context context) {
this.context=context;
}
public static DatabaseHandler getInstace(Context context){
if(databaseHandler==null)
return new DatabaseHandler(context);
return databaseHandler;
}
Code for EmployeeManager:
private static EmployeeManager manager;
Context context;
private EmployeeManager(Context context) {
this.context = context;
}
public static EmployeeManager getInstance(Context context) {
if (manager == null)
return new EmployeeManager(context);
return manager;
}
Add the methods for adding ,deleting updating the employee and the methods as follows
Code for DatabaseHandler:
Adding Employee:
public long addEmployee(EmployeePL employeePL) {
Log.d(TAG, "addEmployee(): "+employeePL);
long id=0;
SQLiteOpenHelper databaseHelper;
SQLiteDatabase db;
try{
databaseHelper=new DatabaseHelper(context);
db=databaseHelper.getWritableDatabase();
id=EmployeeManager.getInstance(context).addEmployee(db, employeePL);
}catch (Exception e) {
Toast.makeText(context, e.getMessage(),Toast.LENGTH_LONG).show();
}
return id;
}
Updating Employee:
public long updateEmployee(EmployeePL employeePL) {
Log.d(TAG, "updateEmployee(): "+employeePL);
long id=0;
SQLiteOpenHelper databaseHelper;
SQLiteDatabase db;
try{
databaseHelper=new DatabaseHelper(context);
db=databaseHelper.getWritableDatabase();
id=EmployeeManager.getInstance(context).updateEmployee(db, employeePL);
}catch (Exception e) {
Toast.makeText(context, e.getMessage(),Toast.LENGTH_LONG).show();
}
return id;
}
Deleting Employee:
public void deleteEmployee(int empID) {
Log.d(TAG, "deleteEmployee(): "+empID);
SQLiteOpenHelper databaseHelper;
SQLiteDatabase db;
try{
databaseHelper=new DatabaseHelper(context);
db=databaseHelper.getWritableDatabase();
EmployeeManager.getInstance(context).deleteEmployee(db,empID);
}catch (Exception e) {
Toast.makeText(context, e.getMessage(),Toast.LENGTH_LONG).show();
}
}
Get Employee :
public EmployeePL getEmployee(int empId) {
Log.d(TAG, "getEmployee(): "+empId);
EmployeePL employeePL = null;
SQLiteOpenHelper databaseHelper;
SQLiteDatabase db;
try{
databaseHelper=new DatabaseHelper(context);
db=databaseHelper.getReadableDatabase();
employeePL=EmployeeManager.getInstance(context).getEmployee(db,empId);
}catch (Exception e) {
Toast.makeText(context, e.getMessage(),Toast.LENGTH_LONG).show();
}
return employeePL;
}
Code for EmployeeManager:
implementatition of the method in the above code snippet...
EmployeeManager.getInstance(context).addEmployee(db, employeePL);
EmployeeManager.getInstance(context).updateEmployee(db, employeePL);
EmployeeManager.getInstance(context).deleteEmployee(db, empId);
EmployeeManager.getInstance(context).getEmployee(db, empId);
Adding Employee:
public long addEmployee(SQLiteDatabase database, EmployeePL employeePL)
throws Exception {
long id;
try {
ContentValues contentValues = new ContentValues();
contentValues.put(DatabaseHelper.EMPLOYEE.EMP_NAME.name(),
employeePL.getEmpName());
contentValues.put(DatabaseHelper.EMPLOYEE.EMP_SAL.name(),
employeePL.getEmpSal());
contentValues.put(DatabaseHelper.EMPLOYEE.EMP_NO.name(), employeePL
.getEmpNo());
id = database.insert(DatabaseHelper.EMPLOYEE.class.getSimpleName(),
null, contentValues);
Log.d(TAG, "Adding the employee is done.." + id);
} catch (Exception e) {
throw e;
}
return id;
}
Updating Employee:
public long updateEmployee(SQLiteDatabase database, EmployeePL employeePL)
throws Exception {
long id;
try {
ContentValues contentValues = new ContentValues();
contentValues.put(DatabaseHelper.EMPLOYEE.EMP_NAME.name(),
employeePL.getEmpName());
contentValues.put(DatabaseHelper.EMPLOYEE.EMP_SAL.name(),
employeePL.getEmpSal());
contentValues.put(DatabaseHelper.EMPLOYEE.EMP_NO.name(), employeePL
.getEmpNo());
id=database.update(DatabaseHelper.EMPLOYEE.class.getSimpleName()
, contentValues,DatabaseHelper.EMPLOYEE.EMP_ID.name()+"=?",new String[]{String.valueOf(employeePL.getEmpId())});
Log.d(TAG, "updating the employee is done..");
} catch (Exception e) {
throw e;
}
return id;
}
Deleting Employee:
public void deleteEmployee(SQLiteDatabase database, int empID)
throws Exception {
Log.d(TAG, "deleteEmployee: " + empID);
try {
database.delete(DatabaseHelper.EMPLOYEE.class.getSimpleName(),
DatabaseHelper.EMPLOYEE.EMP_ID.name() + "=?",
new String[] { String.valueOf(empID) });
} catch (Exception e) {
throw e;
}
}
Get Employee :
public EmployeePL getEmployee(SQLiteDatabase database, int empId)
throws Exception {
EmployeePL employeePL = null;
try {
Cursor cursor = database.query(DatabaseHelper.EMPLOYEE.class
.getSimpleName(), new String[] {
DatabaseHelper.EMPLOYEE.EMP_ID.name(),
DatabaseHelper.EMPLOYEE.EMP_NAME.name(),
DatabaseHelper.EMPLOYEE.EMP_NO.name(),
DatabaseHelper.EMPLOYEE.EMP_SAL.name() },
DatabaseHelper.EMPLOYEE.EMP_ID.name() + "=?",
new String[] { String.valueOf(empId) }, null, null, null);
if (cursor != null && cursor.getCount() != 0) {
cursor.moveToFirst();
employeePL = new EmployeePL();
employeePL.setEmpId(cursor.getInt(0));
employeePL.setEmpName(cursor.getString(1));
employeePL.setEmpNo(cursor.getString(2));
employeePL.setEmpSal(cursor.getString(3));
}
} catch (Exception e) {
throw e;
}
return employeePL; }
Calling the database method :
DatabaseHandler.getInstace(this).addEmployee(employeePL);//form employee object
DatabaseHandler.getInstace(this).deleteEmployee(employeePL2.getEmpId());
DatabaseHandler.getInstace(this).getEmployee(new Long(id).intValue());
you can check the database operations using the command line....the topic on how to access the database from the command line is follows...
Shall access to db using command line..
The following snippet shows how to access the database and query the database using the command line.
First navigate to the android sdk floder installed on your system and navigate to tools folder then follows the steps .
Note: I have used employee.db as the sample database in my application and EMPLOYEE is the database table in the employee.db.
This snippet is just to show how to use the commands in adb to work with sqlite database.
 |
| Adb Shell Command line access to the database. |
