Database Operations with ORM Lite16 May
In the first blog post, we discovered about ORMLite as a formidable ORM for use in android applications. Here we will delve deeper into how to perform various database operations using ORMLite
Query Operation in ORM Lite
The DAOs provide basic methods to query for an object that matches an id (queryForId) and query for all objects (queryForAll), iterating through all of the objects in a table . However, for more custom queries, there is the queryBuilder() method which returns a QueryBuilder object for the DAO with which you can construct custom queries to return a sub-set of your tables.
Query Builder is something that assists in building SQL query (SELECT) statements for a particular table in a particular database.
1.Query for all
Returns the list of all records in the table we have in build function queryForAll();
// get our dao RuntimeExceptionDao<DemoORMLite, Integer> DemoORMLiteDao = getHelper().getDemoORMLiteDao (); // query for all of the data objects in the database List<SimpleData> list = simpleDao.queryForAll();
2. Query for id
Returns the record corresponding to given id we have in build function queryForId(id);
TEntity entity = <strong>this</strong>.dao.queryForId(id);
3. Query for particular field name
Here we use QueryBuilder to query for field “lastname” and return the list of records that have last_name =”lastname”
Public List<DemoORMLite> RetrieveByLastName(String lastname)throws SQLException {
QueryBuilder<TEntity,Integer> queryBuilder = dao.queryBuilder();
List list;
queryBuilder.where().eq("last_name", lastname);
list = queryBuilder.query();
return list;
}
Deleting a record in ormlite
DeleteBuilder assists in building sql DELETE statements for a particular table in a database.
Sample Code that deletes elements from table by argument
DatabaseHelper helper = OpenHelperManager.getHelper(App.getContext(), DatabaseHelper.class);</pre>
//get helper
Dao dao = helper.getDao(YOUR_CLASS.class);
//get your Dao
DeleteBuilder<CanteenLog, Integer> deleteBuilder = dao.deleteBuilder();
// CanteenLog here is table name
deleteBuilder.where().eq("FIELD_NAME", arg);
deleteBuilder.delete();
Order by in ormlite
Syntax: orderBy(String columnName, boolean ascending)
Add “ORDER BY” clause to the SQL query statement to order the results by the specified column name. Use the ascending boolean to get a ascending or descending order. This can be called multiple times to group by multiple columns.
QueryBuilder<Visit, Integer> qb = getHelper().getyourdaoname().queryBuilder();
qb.where().eq("Field_name", fieldname);
qb.orderBy("order_according_to_this_field", false);
Using Max in Ormlite
long max = fooDao.queryRawValue(
"select max(modified) from foo where userid = ?", id);
// now perform a second query to get the max row
Foo foo = fooDao.queryBuilder().where().eq("modified", max).queryForFirst();
Set default value to column in ORMLite
@DatabaseField(defaultValue = "unknownName", canBeNull = true)
Here we explored various simple ORMLite operations. If you have used ORMLite in more advanced way, Please feel free to drop a comment below !!
), its anyways a good idea to hire a developer having experience with one or all of them.
