[Contrib: Evaluation System] AARON: Using SQL in eval DAO

Aaron Zeckoski azeckoski at unicon.net
Thu Aug 11 10:55:05 PDT 2011


In that case, it is just a matter of putting in a setter on the dao
impl for dbType or whatever you want to call it and assigning it to
the value of that bean.

One thing to note on this, I will need to wrap that in an option so it
can be disabled because we need this code to be able to work in OAE
and a hack like this makes that impossible. Let me know when you have
it in there and I will wrap it in an off by default option. Anyone who
wants to use it will need to enable it with a setting of some kind.

-AZ


On Thu, Aug 11, 2011 at 1:47 PM, Jim Eng <jimeng at umich.edu> wrote:
> I think I will have to use the hack, because I have tried various ways of writing it as a join and that led to HQL or JDBC exceptions.  The SQL syntax for this is very different in different databases, and hibernate (or at least hibernate 3.2) doesn't seem to support them well.
>
> I will do that for now, and if we can come up with a better way, I will revisit it.  I will definitely try it again when we move up to hibernate 3.5 or 3.6.
>
> Thanks, Aaron.
>
> Jim
>
>
>
> On Aug 11, 2011, at 11:48 AM, Aaron Zeckoski wrote:
>
>> Hibernate doesn't really make it possible to identify the
>> dialect/vendor as far as I can tell. My recommendation would be to
>> write your query as a join instead of a subselect since most databases
>> can efficiently compile joins but mysql is bad at compiling subselects
>> in most cases.
>>
>> You could also do it as 2 queries (though there are limits to this
>> option and it will possibly be slower than a system which can compile
>> the query effectively).
>>
>> If that won't work, we can do a hack to pull in the sakai vendor via
>> spring but we can't just add externallogic as a dependency of the dao
>> as that creates a loop in the dependencies. Pulling in the vendor is a
>> poor solution so I would only want to do that if there is no other way
>> (but I think there are other ways).
>>
>> <bean id="sakaiDatabaseType"
>> factory-bean="org.sakaiproject.db.api.SqlService"
>> factory-method="getVendor" />
>>
>> -AZ
>>
>>
>>
>>
>> On Thu, Aug 11, 2011 at 10:15 AM, Jim Eng <jimeng at umich.edu> wrote:
>>> Hi Aaron,
>>> I am working on consolidated notifications, in which a student gets one
>>> announcement or reminder a day instead of one message for each eval they can
>>> take. I have written HQL in the DAO impl to update records to indicate that
>>> an announcement has been sent.  The query works fine in Oracle but it is
>>> deadly slow in MySQL.  I have tried revising the HQL to generate a better
>>> MySQL query, but it seems that quirks in hibernate and MySQL make this
>>> unlikely.  I have written SQL that performs very well in MySQL (but only
>>> MySQL).
>>> It's easy elsewhere in sakai to write custom SQL for specific vendors.
>>>  Hibernate usually takes care of that, but it fails in this case.  If we had
>>> a way in the DAO of identifying the vendor, I could do this:
>>> if("mysql".equalsIgnoreCase(dialect)) {
>>> StringBuilder sqlBuffer = new StringBuilder();
>>> sqlBuffer.append("update EVAL_ASSIGN_USER eau, EVAL_EMAIL_PROCESSING_QUEUE
>>> epq ");
>>> if(sendingAvailableEmails) {
>>> sqlBuffer.append("set eau.AVAILABLE_EMAIL_SENT=:dateSent ");
>>> } else {
>>> sqlBuffer.append("set eau.REMINDER_EMAIL_SENT=:dateSent ");
>>> }
>>> sqlBuffer.append("where eau.id = epq.EAU_ID and epq.USER_ID=:userId and
>>> epq.EMAIL_TEMPLATE_ID=:emailTemplateId");
>>>
>>> updateQuery = session.createSQLQuery(sqlBuffer.toString());
>>>
>>> } else {
>>> StringBuilder hqlBuffer = new StringBuilder();
>>> hqlBuffer.append("update EvalAssignUser ");
>>> if(sendingAvailableEmails) {
>>> hqlBuffer.append("set availableEmailSent = :dateSent ");
>>> } else {
>>> hqlBuffer.append("set reminderEmailSent = :dateSent ");
>>> }
>>> hqlBuffer.append("where id in (select eauId from EvalEmailProcessingData
>>> where emailTemplateId = :emailTemplateId and userId = :userId)");
>>>
>>> updateQuery = session.createQuery(hqlBuffer.toString());
>>> }
>>>
>>> Unfortunately, hibernate does not surface that information that I can see..
>>>  I have tried accessing the "hibernate.dialect" property in various ways,
>>> and I've looked for anything in the hibernate API's that might identify the
>>> dialect.  Haven't found anything.
>>> So I'd like to ask this.  The database vendor is readily accessible from
>>> sakai's SqlService.  Suppose we added this method to EvalExternalLogic:
>>> public String getDatabaseVendor();
>>> What would you think about calling an external logic method in the DAO impl?
>>>  Can you see another way to do this, other than just hanging MySQL users out
>>> to dry?
>>> Thanks.
>>> Jim
>>>
>>>
>>>
>>
>>
>>
>> --
>> Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
>>
>>
>
>



-- 
Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile


More information about the evaluation mailing list