Xml based Android ORM
This orm allows developer to create simple database using xml files.
Xml file based on 4 main tags. They are below
tables We define all tables in this section
initialize Some tables should be initialize when they are created. if you want to initialize your tables, you must use this section. This section only runs after tables are created.
queries Developers must define their all queries in this section. Every Query must have unique name.
upgradesDay after day, If we need to upgrade the our database, Main Section tag easyORM must have version attribute. When this
attribute is increased, this section operations will be performed under onUpgrade method which is provided by SqLite.
Let's look at each section closer
following xml overviewl shows the general usage:
<easyORM version="1">
<tables>
section1
</tables>
<initialize>
section2
</initialize>
<queries>
section3
</queries>
<upgrades>
section4
</upgrades>
</easyORM> tables tag should include the table tags. this section allows us to define our all tables.
<tables>
<table name="informations">
<column name="ID" type="integer"/>
<column name="address" size="250" type="varchar"/>
<column name="phone" nullable="false" size="15" type="varchar"/>
</table>
<table name="users">
<column name="ID" autoIncrement="true" primary="true" type="integer"/>
<column name="name" size="20" type="varchar" unique="true"/>
<column name="lastName" size="20" type="varchar"/>
<column name="number" type="integer"/>
<column name="infoID" reference="informations:ID" type="integer"/>
</table>
</tables>Above defination creates 2 table which are informations and users.
information table has ID,address and phone columns
users table has ID,name,lastName,number and infoID columns. ID is primary key. infoID is foreign key and it refers
to information table' s ID'filed.
initialize section runs only when tables are created. If we initialize some fields/tables, it is good to use this part.
There is 2 type of usage. First one is rawquery. Second one is insert
rawQuery allows us to define sql queries.
insert allows us to define sql queries using xml schema.
<initialize>
<rawquery query="insert into informations(ID,address,phone) values(1,'First Address','00905343332211')"/>
<insert into="users">
<column name="name" value="lionell"/>
<column name="lastName" value="messi"/>
<column name="number" value="10"/>
<column name="infoID" value="1"/>
</insert>
</initialize> queries this section should have query tags. query tags allow us to define select/insert/update/delete
operation methods.
query must have type and name attribute. type attibute points the query type which can be select/insert/update/delete.
note : getInformation getInformation2 getInformation3 and getInformation4 return the same results.
<query name="getInformation" columns="*" type="select">
<table name="informations"/>
<where name="ID"/>
</query>
<query name="getInformation2" type="select">
<column name="address" />
<column name="phone" />
<column name="ID" />
<table name="informations"/>
<where name="ID"/>
</query>
<query name="getInformation3" columns="address" type="select">
<column name="phone" />
<column name="ID" />
<table name="informations"/>
<where name="ID"/>
</query>
<query name="getInformation4" columns="ID,address,phone" type="select">
<table name="informations"/>
<where name="ID"/>
</query>
<query name="getInformation3" columns="address" type="select">
<column name="phone" />
<table name="informations"/>
<where name="ID"/>
</query>
<query name="addUser" dataSync="true" type="insert">
<table name="users"/>
<column name="name" type="varchar" value="?"/>
<column name="lastName" value="?"/>
<column name="number" type="integer" value="?"/>
<column name="infoID" type="integer" value="?"/>
</query>
<query name="deleteUserByID" dataSync="true" type="delete">
<table name="users"/>
<where name="ID" value="?"/>
</query>
<query name="upduteUserByID" dataSync="true" type="update">
<table name="users"/>
<column name="name" value="?"/>
<column name="lastName" value="?"/>
<column name="number" value="?"/>
<where name="ID" value="?"/>
</query>inner query defination is usable.
where tag can have select tag.
For examle :
<query name="deleteUserByPhone" type="delete">
<table name="users"/>
<where name="infoID" value="?">
<select columns="ID">
<table name="informations"/>
<where name="phone" value="?"/>
</select>
</where>
</query>
<query name="upduteUserByPhone" type="update">
<table name="users"/>
<column name="name" value="?"/>
<column name="lastName" value="?"/>
<where name="infoID" value="?">
<select columns="ID">
<table name="informations"/>
<where name="phone" value="?"/>
</select>
</where>
</query> upgrades section can have upgrade tags. all upgrade tags must have version attribute. Because,
the upgrade tags only run its version is between db oldVersion and dbNewVersion. Upgrade tag can have table creation or
rawQuery
let's look at the usage:
<upgrade version="2">
<table name="locations">
<column name="ID" type="integer"/>
<column name="latitude" size="250" type="float"/>
<column name="longitude" size="250" type="float"/>
<column name="userID" reference="users:ID" type="integer"/>
</table>
<rawquery query="insert into locations(ID,longitude,userID) values(1,55.55,66.66,123456)"/>
</upgrade>We use the tag to create new table. table tag must have a unique name .
table tag should have column defination and column name points the table filed name. So it must be unique.
tableName:itsField. Default values is falseA query must have a unique name attribute and type attribute.
type should be select/insert/delete/update. distinct is optional default is false. usage is distinct="true"
Also it should have columns attribute. This attribute is used for select queries. if it is left empty, means that *
if we select some fileds, columns should be used like select="name,lastname,number"
query tag can have table,column,where,orderBy,groupBys,havings tags.
query tags can have dataSync attribute. it is set to true,a data sync event is distributed to all listeners after this query is executed
table tags can be used.All tables will be joined where tags, where conditions will be processed as and operation. We can set process to and/or. Default value is and .if we want to define <= condition, where tag should have less="true" and equals="true".
if we want to define < condition, where tag should have less="true" and equals="false".
if we want to define != condition, where tag should have equals="false".
<where name="name" value="?" process="or" />
<where name="lastName" value="?" />
Above xml code snippet output is where name=? or lastName=?
To be continued...
Mixed Example and its sql view
<query name="getUser" columns="name,surname" type="select">
<column name="ID" alias="SYUm" sum="true"/>
<column name="ID" alias="total" avg="true"/>
<table name="users"/>
<where name="ID" less="true" value="?"/>
<orderby name="name" describe="desc"/>
<orderby name="surname"/>
<groupbys>
<groupby name="name"/>
<groupby name="surname"/>
<groupby name="ID" sum="true"/>
<groupby name="ID" avg="true"/>
</groupbys>
<havings>
<having name="ID" avg="true" equals="true" less="true" process="or" value="?"/>
<having name="ID" equals="true" greater="true" process="or" sum="true" value="?"/>
</havings>
</query>Above example' s sql code is :
getUser
select name,surname, SUM(ID) as SYUm, AVG(ID) as total
from users
Where ID <= ?
GROUP BY name , surname , SUM(ID) , AVG(ID)
HAVING AVG(ID) <= ? or SUM(ID) >= ?
ORDER BY name desc , surname ascWe need to create a simple java class to map defined table. Class' field name must be the same with defined table column names.
All field must have getter/setter methods. The class must have default constructor.
Java class should be below for above table users
public class User {
private Integer ID;
private String name, lastName;
private Integer number, infoID;
public Integer getID() {
return ID;
}
public void setID(Integer ID) {
this.ID = ID;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Integer getNumber() {
return number;
}
public void setNumber(Integer number) {
this.number = number;
}
public Integer getInfoID() {
return infoID;
}
public void setInfoID(Integer infoID) {
this.infoID = infoID;
}
}To create DataBase,
public class MainActivity extends Activity{
@Override
protected void onCreate(Bundle savedInstanceState){
:
:
EasyORM easyORM = EasyOrmFactory.getOrCreateDataBase(this, "dbName");
try {
EasyOrmFactory.verboseAllQueries(true);
EasyOrmFactory.setExactMatch(false);
easyORM.registerXMLSchema("db.xml");
} catch (Exception e) {
e.printStackTrace();
}
}
}Xml Defination is,
<easyORM version="1">
<tables>
<!--
__________________________
| informations |
|__________________________|
|ID | address | phone |
|___|___________|__________|
|___|___________|__________|
|___|___________|__________|
-->
<table name="informations">
<column name="ID" type="integer"/>
<column name="address" size="250" type="varchar"/>
<column name="phone" nullable="false" size="15" type="varchar"/>
</table>
<!--
___________________________________________
| users |
|___________________________________________|
|ID | name | lastName | number | infoID |
|___|_______|_____________|________|________|
|___|_______|_____________|________|________|
|___|_______|_____________|________|________|
-->
<table name="users">
<column name="ID" autoIncrement="true" primary="true" type="integer"/>
<column name="name" size="20" type="varchar" unique="true"/>
<column name="lastName" size="20" type="varchar"/>
<column name="number" type="integer"/>
<column name="infoID" reference="informations:ID" type="integer"/>
</table>
</tables>
<!--this section runs only table is created-->
<initialize>
<rawquery query="insert into informations(ID,address,phone) values(1,'First Address','00905343332211')"/>
<insert into="users">
<column name="name" value="lionell"/>
<column name="lastName" value="messi"/>
<column name="number" value="10"/>
<column name="infoID" value="1"/>
</insert>
</initialize>
<queries>
<!--select operation-->
<query name="getInformation" type="select">
<table name="informations"/>
<where name="ID"/>
</query>
<query name="getAllUser" type="select">
<table name="users"/>
</query>
<query name="getUserByConstValue" distinct="true" type="select">
<table name="users"/>
<where name="lastName" value="messi"/>
</query>
<query name="getUserByLastName" distinct="true" type="select">
<table name="users"/>
<where name="lastName" value="?"/>
</query>
<query name="getUserLike" columns="*" type="select">
<table name="users"/>
<where name="lastName" like="true" type="varchar" value="%?%"/>
</query>
<query name="getUserAndInformations" columns="*" type="select">
<table name="users"/>
<table name="informations"/>
<where name="users.infoID" process="and" static="false" value="informations.ID"/>
<where name="users.ID" value="?"/>
</query>
<!--insert operaiton-->
<query name="addUser" dataSync="true" type="insert">
<table name="users"/>
<column name="name" type="varchar" value="?"/>
<column name="lastName" value="?"/>
<column name="number" type="integer" value="?"/>
<column name="infoID" type="integer" value="?"/>
</query>
<query name="addInformations" type="insert">
<table name="informations"/>
<column name="ID" type="integer" value="?"/>
<column name="address" type="double" value="?"/>
<column name="phone" type="double" value="?"/>
</query>
<!--delete operation-->
<query name="deleteUserByID" dataSync="true" type="delete">
<table name="users"/>
<where name="ID" value="?"/>
</query>
<query name="deleteInformationByID" dataSync="true" type="delete">
<table name="informations"/>
<where name="ID" value="?"/>
</query>
<query name="deleteUserByPhone" dataSync="true" type="delete">
<table name="users"/>
<where name="infoID" value="?">
<select columns="ID">
<table name="informations"/>
<where name="phone" value="?"/>
</select>
</where>
</query>
<!--update operations-->
<query name="upduteUserByID" dataSync="true" type="update">
<table name="users"/>
<column name="name" value="?"/>
<column name="lastName" value="?"/>
<column name="number" value="?"/>
<where name="ID" value="?"/>
</query>
<query name="upduteInformationByID" dataSync="true" type="update">
<table name="informations"/>
<column name="address" value="?"/>
<column name="phone" value="?"/>
<where name="ID" value="?"/>
</query>
<query name="upduteUserByPhone" type="update">
<table name="users"/>
<column name="name" value="?"/>
<column name="lastName" value="?"/>
<where name="infoID" value="?">
<select columns="ID">
<table name="informations"/>
<where name="phone" value="?"/>
</select>
</where>
</query>
<rawquery name="select1" query="select * from users where ID=?" />
</queries>
<upgrades>
<!--this ection will be executed when the version is set to 2. We have to define following table in tables tag when db version is setted to 2-->
<upgrade version="2">
<table name="locations">
<column name="ID" type="integer"/>
<column name="latitude" size="250" type="float"/>
<column name="longitude" size="250" type="float"/>
<column name="userID" reference="users:ID" type="integer"/>
</table>
</upgrade>
</upgrades>
</easyORM>To register DataSyncListener
EasyOrmFactory.registerDataSync(this);
To register CreateTable and Upgrade table events
easyORM.registerCreateTableListener(this);
To use insert, update , select, delete queries:
easyORM.getEasyExecute().insert("queryName", dbObject);Execution performer interface methods are
void delete(String queryName, Object[] params) throws QueryNotFoundException, QueryExecutionException;
void deleteObject(String queryName, Object object) throws QueryNotFoundException, QueryExecutionException, FieldNotFoundException;
boolean insert(String queryName, Object u) throws QueryNotFoundException, FieldNotFoundException, QueryExecutionException;
<T> List<T> select(String queryName, final Class<T> input) throws QueryNotFoundException, QueryExecutionException;
<T> List<T> select(String queryName, Object[] parameters, final Class<T> input) throws QueryNotFoundException, QueryExecutionException;
void delete(String queryName) throws QueryNotFoundException, QueryExecutionException;
void update(String queryName, Object[] params) throws QueryNotFoundException, QueryExecutionException;
void updateObject(String queryName, Object object) throws QueryNotFoundException, QueryExecutionException, FieldNotFoundException;
void executeRawQuery(String queryName, Object[] params) throws QueryNotFoundException, QueryExecutionException;
Easy ORM supports only 1 log level. To enable all logs, you should use following code. All logs will be dumped with EasyORM prefix.
EasyOrmFactory.verboseAllQueries(true);database.xml file must be under Assets folder. To create Assets folder, File->New->Folder->Assets Folder. This selection create Assets folder next to(the same level) res folder.
compile 'com.github.okayatalay:easyOrm:1.0.0' line should be added to dependencies scope.
dependencies {
:
:
compile 'com.github.okayatalay:easyOrm:1.0.8'
}
maven { url 'https://jitpack.io' } should be added to into project built.gradle file under allprojects -> repositories
allprojects {
repositories {
:....
:....
maven { url 'https://jitpack.io' }
}
}