I created an example application with a multiple selections dialog, where
you can create time schedules, edit and list them.
Click here to dowload the example and source code : http://code.google.com/p/androsql/downloads/list
This example implement a tipical many-to-many relations.
Also, this application use the Enum capabilities to print out the database content in your LogCat console.
The following sections will decribe my SQLite framework through the explanation of my multiple selections dialog example.
Contents
- The problem with the NotePad android tutorial
- The Multiple choise dialog example
- Define database with enum
- The SCHEMA
- The Tables
- The DAO
- The Debuger
The problem with the NotePad android tutorial
If you had already plays with the NotePad android tutorial, maybe you find like me that the way they do database layer is not intuitive, error prone and hard to debug.
In the NotesDbAdapter class note how the DATABASE_CREATE variable don't use the table's column name static variables. This is because if you use the static column name variable you will receive an Android error; |  |
ERROR/Database(238): Failure 1 (near "KEY_TITLE": syntax error) on 0x83478 when preparing 'KEY_TITLE create table notes (_id integer primary key autoincrement, title text not null, body text not null);
Another problem I see is, more you define tables and columns name, more your class become confusing.
Also, if you follow the notepad tutorial, you will define your tables in many class. This which leads you to many table creation problems. See the article "A SQLiteOpenHelper Is Not A SQLiteTableHelper" to know more about this topic. Those reasons convince me to create a small framework for doing SQLite on Android.
The Multiple choise dialog example
To make an example of my SQLite framework, I decided to create a muliple choice schedules application wich saves the state of multi to multi table relation. This example is based on the Android ApiDemos. The App/Dialog - Repeat alarm application. |  |
Define database with enum.
When I developed my framework it was important to me to:
- define my database with Enum.
- use the DAO pattern instead of the DatabaseAdapter pattern.
- add a debug helper functionality that can print the content of my database in the logcat.
The reasons that leaded me to choose Enum for my databases management was:
- I want to define my tables only one time.
- No need to duplicate code between process.
- Using Enum is fast.
- You have the table and column name autocompletion while you typing.
- It is more easy with Enum to automate your task like getting all table and column names. This is very useful for creating method such as print database in the logfile.
The SCHEMA
The MULTIPLE_LIST_SCHEMA is an Enum class that represent the database structure of the MulitipleChoice application. It contains all your table Enum and also create useful static variable that can help you writing SQL script. It is the SCHEMA that lets you get all table names, table columns, and column definitions. It is used to generate your create, delete and other schema related SQL script. Other Enum class like the SCHEDULES_TABLE represent a SQLite tables. That is where you define the columns and the properties of your tables. A table Enum contain is own definition. So, to create a table reference, the Schema Enum refer to the static variable TABLE_NAME, COLUMNS and COLUMNS_TYPE. |  Muliple choice dialog project view |
The Tables
Other Enum class like the SCHEDULES_TABLE represent a SQLite table. That is where you define the columns and the properties of your tables.
For example look inside the SCHEDULES_TABLE Enum class. You can see that this table have two columns, Id and Name.
To create a new entry in the table, you can do
Date stamp = new Date();
initialValues.put(SCHEDULES_TABLE.NAME.getCOLUMN_NAME(), stamp.toGMTString());
long rowId = mDb.insert(SCHEDULES_TABLE.TABLE_NAME, null, initialValues);
The static field of the table Enum define the general properties of the table, like the table name.
public static String TABLE_NAME = "SCHEDULES_TABLE";
Two other important variable are COLUMNS and COLUMNS_TYPE.
There are used to create SQL task associate to the schema like:
- create all your tables.
- delete all your tables.
- select all your tables (used by the AbstractDao.dumpDataBase method).
The DAO
I decided to take the DAO as the database pattern of my example.
For me, the adapter pattern does not necessarily have relations with a database layer.
Every java J2EE developer think about the database layer with the DAO pattern. Me too. That why I dropped the Adapter pattern.
"The Adapter pattern is used so that two unrelated interfaces can work together. The joining between them is called an Adapter. This is something like we convert interface of one class into interface expected by the client. We do that using an Adapter."
http://www.allapplabs.com/java_design_patterns/adapter_pattern.htm
"The DAO implements the access mechanism required to work with the data source. The data source could be a persistent store like an RDBMS, an external service like a B2B exchange, a repository like an LDAP database, or a business service accessed via CORBA Internet Inter-ORB Protocol (IIOP) or low-level sockets."
http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html
AbstractDao
The AbstractDao provides common functions to the application Dao.
It is important to define the SQLiteOpenHelper innerClass inside the AdstractDao to avoid the creation of many instances of the DatabaseHelper.
This DatabaseHelper is responsible to create and populate the database. It's important to share this inner class with all Dao, because, that way, we are sure that the database creation happen only one time in the application life cycle. So you have to create all the tables in the first DatabaseHelper onCreate(SQLiteDatabase db) call.
The Debuger
The dumpDataBase method prints out the content of you database in the LogCat console. This method is very convenient for debugging your application. That's it! You don't need anymore to do SQLite command line to see the content of you database. |  |
Conclusion
So, now that I have exposed my work, feel free to download it and to play with it, and even more, don't hesitate to give your comments and suggestions.
Click here to dowload the example and source code : http://code.google.com/p/androsql/downloads/list
No comments:
Post a Comment