I have a prototype application which searches from database for Address records based on keywords. Keywords are entered on the GUI by the user. The number of keywords is unknown beforehand. Dynamic query is therefore used. The corresponding entity model is shown in my other blog. The equivalent SQL statement looks something like this:
select a.*
from Address as a, AddressField as af, Address_AddressField as aaf
where a.ADDRESSID=aaf.ADDRESSID
and aaf.ADDRESSFIELDID=af.ADDRESSFIELDID
and (upper(af.VALUE) like 'BOMBAY'
or upper(af.VALUE) like 'MUMBAI'
or ... /* more parameters */ )
(Note that in a production system, it is better to have an additional column in the database table to store the uppper-cased values. That way, they can be indexed to improve query performance.)
Here, I will show how various ORM frameworks handle this type of dynamic query.
iBATIS SQL Map is a mapping framework between SQL statements inputs/outputs and the object model. Therefore, SQL statements are used in SQL Map directly. The SQL Map query is quite straight forward and uses the same SQL statement:
select a.*
from Address as a, AddressField as af, Address_AddressField as aaf
where a.ADDRESSID=aaf.ADDRESSID
and aaf.ADDRESSFIELDID=af.ADDRESSFIELDID
upper(VALUE) like #[]#
iBATIS has this <iterate> tag to denote that for every element in the input parameter (which is a collection, in this case a 'list') format it into the SQL statement using its various constructs - prepend, open, close, conjunction, etc.
The corresponding Java code calling this query:
public class SQLMapAddressDao extends SqlMapDaoTemplate
implements IAddressDao {
...
private Address[] getByFieldValues(List<AddressField> afs)
throws DataAccessException {
try {
List list=queryForList("Address.getByFieldValues", afs);
if(list==null || list.size()==0)
return null;
else
return (Address[]) list.toArray(new Address[list.size()]);
} catch (Exception e) {
throw new DataAccessException(e);
}
}
...
}
iBATIS also supports .NET so I can execute the same query in .NET. The following C# code does the same thing as the Java snippet above.
public Address[] GetByFieldValues(IList afs) {
IList list = ExecuteQueryForList("Address.getByFieldValues", afs)
if (list == null || list.Count == 0)
return null;
else {
return (Address[])
((ArrayList)list).ToArray(typeof(Address));
}
}
Every ORM framework has its own object query language (OQL). Hibernate uses HQL. Programmatically, building the HQL statement is simply building a string. The HQL statement can programatically built as such:
public Address[] getByFieldValues(AddressField[] afs)
throws DataAccessException{
Address[] result=null;
if(afs==null afs.length==0)
return null;
// this query does not work with mySQL 4.0.x.
String select="select addr"
+ " from com.laws.Address.Address as addr,"
+ " com.laws.Address.AddressField as af";
StringBuffer where=new StringBuffer(" where (upper(af.value) like :value_0");
for(int i=1; i<afs.length; i++) {
where.append(" or upper(af.value) like :value_"+i);
}
where.append(") and addr in elements(af.addresses)");
String query=select+where.toString();
try {
Session sess = HibernateSessionFactory.currentSession();
Query q = sess.createQuery(query);
for(int i=0; i<afs.length; i++) {
q.setParameter("value_"+i, %"+afs[i].getValue().toUpperCase()+"%");
}
List list = q.list();
if (list != null && list.size() > 0) {
result = (Address[]) list.toArray(new Address[list.size()]);
}
HibernateSessionFactory.closeSession();
} catch (HibernateException he) {
HibernateSessionFactory.handleException(he);
}
return result;
}
JPA has been included as part of EJB 3.0 specification to replace the old EJB Container Managed Persistence (CMP) approach. In JPA the same query in JPQL is programmatically built:
public Address[] getByFieldValues(AddressField[] afs)
throws DataAccessException {
if(afs==null afs.length==0)
return null;
String select="select a"
+ " from Address addr,"
+ " AddressField af, in(af.addresses) a";
StringBuffer where=new StringBuffer(" where (upper(af.value) like :value_0");
for(int i=1; i<afs.length; i++) {
where.append(" or upper(af.value) like :value_"+i);
}
where.append(") ");
String queryString=select+where.toString();
Query query=EntityManagerHelper.createQuery(queryString);
// the JPA parameter index is 1-based.
for(int i=0; i<afs.length; i++){
query.setParameter(i+1, afs[i].getValue());
}
List list=query.getResultList();
if(list==null list.size()==0)
return null;
else
return (Address[]) list.toArray(new Address[list.size()]);
}
The code is almost identical to Hibernate except for the JPQL vs. HQL syntax differences. This is not surprising considering JPA shares its roots with Hibernate.
EF 1.0 Beta 3 does not support dynamic query (using LINQ to SQL). To have string-based dynamic query, I had to download a sample from MSDN, extract out the Dynamics.cs file from the zip and place it into my project. Then I have the System.Linq.Dynamic namespace available to me.
public Address.Domain.Address[] GetByFieldValues(Address.Domain.AddressField[] afs) {
if (afs == null afs.Length == 0)
return null;
var aQuery= addressContext.AddressField.Where("1=0").Select(a=>a);
foreach (Address.Domain.AddressField af in afs) {
if (af.GetValue().Length > 0) {
var aQuery2=addressContext.AddressField
.Where("value.ToUpper().Contains(@0)", af.GetValue().ToUpper())
.Select(a => a);
aQuery=aQuery.Union(aQuery2);
}
}
Dictionary<EntityKey, AddressModel.Address> dict=new Dictionary<EntityKey,AddressModel.Address>();
foreach (AddressModel.AddressField afEntity in aQuery) {
afEntity.Address.Load();
afEntity.AddressFieldTypeReference.Load();
foreach (AddressModel.Address aEntity in afEntity.Address) {
if(!dict.ContainsKey(aEntity.EntityKey))
dict.Add(aEntity.EntityKey, aEntity);
}
}
return AddressEFHelper.AddressArrayEntityToDomain(dict.Values.ToArray());
}
This implementation is very clumsy compared to other previous frameworks.
- I could not build a single query string. I think it's due to the escaped double-quotes (\") in the string - somehow, it gives me error at runtime
- I could not get the Address entities from the first iteration (which gives me AddressFields); I had to get the Addresses from the selected AddressFields in a second iteration (nested foreach loops)
Personally, I prefer iBATIS the best because it gives me the flexibility of writing my own SQL statements. The SQL statements are reviewed by the DBA and provide input for the DBA for database optimisation (as we all know that
the database is usually the weakest link in terms of overall system performance).
mania
I then tried Google's SyntaxHilighter. It does not try to guess the code language. Instead the user specifies the language using the class attribute of the <pre> tag. It produces far more superior results with line numbers and alternate line background colors. A sample output is shown below.
