ORMLite Tutorial for Android

ORMLITE – Light Weight Object Relational Mapping

OVERVIEW

ORMLite provides a lightweight Object Relation Mapping between Java classes and SQL databases ORMLite supports JDBC connections to MySQL, Postgres, H2, SQLite, Derby, HSQLDB, Microsoft SQL Server. ORMLite also supports native database calls on Android OS.

Using ORMLite with Android

1. Downloading ORMLITE

To get started with ORMLite, We need to download the ORMLite jar files.These can be downloaded from ORMLite release page

Once we download ORMLite we need to add external library to our android project. Just Drop the jar file into your project's libs/ subdirectory.

we only need the ormlite-android-4.14.jar, not the ormlite-core or any other packages.

2. Getting Started

To get started with Ormlite we will need to create our own database helper class which should extend the OrmLiteSqliteOpenHelper class. This class creates and upgrades the database when the application is installed and also provide the DAO(Data Access Object) classes used by other classes. The helper class must implement the methods

onCreate(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource)

onUpgrade(SQLiteDatabase database, ConnectionSource connectionSource, int oldVersion, int newVersion)

onCreate creates the database when app is first installed while onUpgrade handles the upgrading of the database tables when we upgrade our app to a new version.

The helper should be kept open across all activities in the app with the same SQLite database connection reused by all threads. If we open multiple connections to the same database, stale data and unexpected results may occur. It is recommended to use the OpenHelperManager to monitor the usage of the helper - it will create it on the first access, track each time a part of our code is using it, and then it will close the last time the helper is released.

Once we define our database helper and are managing it correctly, We will use it in our Activity classes. An easy way to use the OpenHelperManager is to extend OrmLiteBaseActivity for each of your activity classes - there is also OrmLiteBaseListActivity, OrmLiteBaseService, and OrmLiteBaseTabActivity. These classes provide a helper protected field and a getHelper() method to access the database helper whenever it is needed and will automatically create the helper in the onCreate() method and release it in the onDestroy() method.

Here is a sample DatabaseHelper

1:  import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;  
2:  import com.j256.ormlite.dao.Dao;  
3:  import com.j256.ormlite.dao.RuntimeExceptionDao;  
4:  import com.j256.ormlite.support.ConnectionSource;  
5:  import com.j256.ormlite.table.TableUtils;  
6:    
7:  /**  
8:  * Database helper class used to manage the creation and upgrading of your  
9:  * database. This class also usually provides the DAOs used by the other  
10:  * classes.  
11:  */  
12:  public class DatabaseHelper extends OrmLiteSqliteOpenHelper {  
13:    
14:  // name of the database file for your application -- change to something  
15:  // appropriate for your app  
16:  private static final String DATABASE_NAME = "Enbake";  
17:  // any time you make changes to your database, you may have to increase the  
18:  // database version  
19:  private static final int DATABASE_VERSION = 1;  
20:    
21:  // the DAO object we use to access the any table  
22:  private Dao<DemoORMLite, Integer> DemoORMLiteDao = null;  
23:  private RuntimeExceptionDao<DemoORMLite, Integer> DemoORMLiteRuntimeDao = null;  
24:    
25:  public DatabaseHelper(Context context) {  
26:  super(context, DATABASE_NAME, null, DATABASE_VERSION);  
27:  }  
28:    
29:  /**  
30:  * This is called when the database is first created. Usually you should  
31:  * call createTable statements here to create the tables that will store  
32:  * your data.  
33:  */  
34:  @Override  
35:  public void onCreate(SQLiteDatabase db, ConnectionSource connectionSource) {  
36:  try {  
37:  Log.i(DatabaseHelper.class.getName(), "onCreate");  
38:  TableUtils.createTable(connectionSource, DemoORMLite.class);  
39:  } catch (SQLException e) {  
40:  Log.e(DatabaseHelper.class.getName(), "Can't create database", e);  
41:  throw new RuntimeException(e);  
42:  }  
43:    
44:  // here we try inserting data in the on-create as a test  
45:  RuntimeExceptionDao<DemoORMLite, Integer> dao = getDemoORMLiteDao();  
46:  String name = "Enbake"  
47:  // create some entries in the onCreate  
48:  long date = System.currentTimeMillis();  
49:  DemoORMLite demo = new DemoORMLite(name,date);  
50:  dao.create(demo);  
51:  Log.i(DatabaseHelper.class.getName(), "created new entries in onCreate: ");  
52:  }  
53:    
54:  /**  
55:  * This is called when the application is upgraded and it has a higher  
56:  * version number. This allows you to adjust the various data to match the  
57:  * new version number.  
58:  */  
59:  @Override  
60:  public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource,  
61:  int oldVersion, int newVersion) {  
62:  try {  
63:  Log.i(DatabaseHelper.class.getName(), "onUpgrade");  
64:  TableUtils.dropTable(connectionSource, DemoORMLite.class, true);  
65:  // after we drop the old databases, we create the new ones  
66:  onCreate(db, connectionSource);  
67:  } catch (SQLException e) {  
68:  Log.e(DatabaseHelper.class.getName(), "Can't drop databases", e);  
69:  throw new RuntimeException(e);  
70:  }  
71:  }  
72:    
73:  /**  
74:  * Returns the Database Access Object (DAO) for our SimpleData class. It  
75:  * will create it or just give the cached value.  
76:  */  
77:  public Dao<DemoORMLite, Integer> getDao() throws SQLException {  
78:  if (DemoORMLiteDao == null) {  
79:  DemoORMLiteDao = getDao(DemoORMLite.class);  
80:  }  
81:  return DemoORMLiteDao;  
82:  }  
83:    
84:  /**  
85:  * Close the database connections and clear any cached DAOs.  
86:  */  
87:  @Override  
88:  public void close() {  
89:  super.close();  
90:  DemoORMLiteRuntimeDao = null;  
91:  }  
92:  }  
93:    

there are a few things to notice when we use ORMLite

First: We just annotate our class as a table and its members as fields and we' re almost done with creating a table

The second thing to notice is that ORMLite handles all of the basic data types without any explicit work on your part (integers, strings, floats, dates, and more).

1.3 Creating Table with columns

1:        public class DemoORMLite {  
2:        
3:        
4:      /** Class name will be tablename  
5:      */  
6:      @DatabaseField(generatedId = true, canBeNull = false)  
7:      int _id;  
8:      @DatabaseField(canBeNull = true)  
9:      String first_name;  
10:      @DatabaseField(canBeNull = true)  
11:      String last_name;  
12:      @DatabaseField(canBeNull = true)  
13:      Date created;  
14:      DemoORMLite() {  
15:        
16:      }  
17:        
18:      public DemoORMLite(String name,long date) {  
19:      this.first_name = name;  
20:      this.last_name = "lastname";  
21:      this.created = new Date(date);  
22:        
23:      }  
24:        
25:      @Override  
26:      public String toString() {  
27:      StringBuilder sb = new StringBuilder();  
28:      sb.append(_id);  
29:      sb.append(", ").append(first_name);  
30:      sb.append(", ").append(last_name);  
31:      SimpleDateFormat dateFormatter = new SimpleDateFormat(  
32:      "MM/dd/yyyy HH:mm:ss.S");  
33:      sb.append(", ").append(dateFormatter.format(created));  
34:        
35:      return sb.toString();  
36:      }  
37:      }  
38:      
39:    }
40:    

4. Deleting record from ORMLite

Assists in building sql DELETE statements for a particular table in a particular database.

Sample Code

1:  DatabaseHelper helper = OpenHelperManager.getHelper(App.getContext(), DatabaseHelper.class);  
2:    
3:   //get helper   
4:   Dao dao = helper.getDao(YOUR_CLASS.class);  
5:   //get your Dao  
6:   DeleteBuilder<CanteenLog, Integer> deleteBuilder = dao.deleteBuilder();  
7:   deleteBuilder.where().eq("FIELD_NAME", arg);  
8:   deleteBuilder.delete();  
9:    

deletes elements from table in field by arg

5. Query in ORMLite

1. Query for all

returns the list of all records in the table we have inbuild function queryForAll();

1:  // get our dao  
2:  RuntimeExceptionDao<DemoORMLite, Integer> DemoORMLiteDao = getHelper().getDemoORMLiteDao ();  
3:  // query for all of the data objects in the database  
4:  List<SimpleData> list = simpleDao.queryForAll();  

2. Query for id

returns the record corresponding to given id we have inbuild function queryForId(id);

Sample code

1:  TEntity entity = this.dao.queryForId(id);  

1.5.3 Query for particular field name

here we query for field “lastname” and it returns list of records that have last_name =”lastname”

1:  public List<DemoORMLite> RetrieveByLastName(String lastname) throws SQLException {  
2:  QueryBuilder<TEntity, Integer> queryBuilder = dao.queryBuilder();  
3:  List<TEntity> list;  
4:  queryBuilder.where().eq("last_name", lastname);  
5:  list = queryBuilder.query();  
6:  return list;  
7:  }