Hi,
I wonder if any of you could help me get a start on this problem. I would like to use a query that contains MySQL functions in the where clause (see below), through Hibernate. If I could just get a working strategy for the general problem, I'm sure I can work out the details myself.
I'm using the native SQL interface and I've tried a couple of things. The objects that I intend to retrieve are 3rd generation derived classes, and (unfortunately) fairly complex. After a couple of attempts at generation the full query that would retrieve the complete object, I've tried the query below, which attempts to retrieve just the primary key for the desired objects (which I would fetch by key, later). I"ve tried both the version below, and another version that attempted to retrieve the PK as a Long. I've tried using {p.*} (which does not appear to be supported for derived classes), and even putting many of the things in the where clause in appropriate {}s.
... but I'm beginning to get involved in problem that I'm not clear will help, even if I get them solved. Surely someone else is using functions in their queries, and retreiving derived classes. How, in general, did you do it? Surely I can just get that PK?
Thanks!
Blake Meike
Hibernate version: 2.1
Mapping documents:
The object I'm attempting to retrieve are:
Program is joinedsubclass of AggregateResource is a joined subclass of Resource
Code between sessionFactory.openSession() and session.close():
Code:
Query query
= getHibernateTemplate().getSessionFactory().openSession()
.createSQLQuery(QUERY_CONFLICTING_MYSQL, "p", Program.class);
query.setParameter(0, dev.getId());
query.setParameter(1, start);
query.setParameter(2, stop);
query.setParameter(3, start);
query.setParameter(4, stop);
//XXX: MySQL JDBC driver throws an error looking
// for column 'archiveN2_53_0_' in query.list()
List conflicts = new ArrayList();
for (Iterator i = query.list().iterator(); i.hasNext(); ) {
conflicts.add(findProgram((Long) i.next()));
}
return conflicts;
....
private static final String QUERY_CONFLICTING_MYSQL
= "select p.id as {p.id}"
+ " from Program p inner join Resource r"
+ " where (p.id = r.id)"
+ " and (p.programtype = " + Program.TYPE_LIVE + ")"
+ " and (r.originDeviceId = ?)"
+ " and not (p.broadcastStart is null)"
+ " and not (p.durationSeconds is null)"
+ " and (p.durationSeconds > 0)"
+ " and ((? between p.broadcastStart and DATE_ADD(p.broadcastStart, INTERVAL p.durationSeconds SECOND))"
+ " or (? between p.broadcastStart and DATE_ADD(p.broadcastStart, INTERVAL p.durationSeconds SECOND))"
+ " or ((? < p.broadcastStart) and (? > DATE_ADD(p.broadcastStart, INTERVAL p.durationSeconds SECOND))))";
Full stack trace of any exception that occurs:
various....
Name and version of the database you are using:
MySQL 4.0.20
The generated SQL (show_sql=true):
Quote:
select p.id from Program p inner join Resource r
where (p.id = r.id) and (p.programtype = 2)
and (r.originDeviceId = ?)
and not (p.broadcastStart is null)
and not (p.durationSeconds is null)
and (p.durationSeconds > 0)
and ((? between p.broadcastStart
and DATE_ADD(p.broadcastStart, INTERVAL p.durationSeconds SECOND))
or (? between p.broadcastStart
and DATE_ADD(p.broadcastStart, INTERVAL p.durationSeconds SECOND))
or ((? < p.broadcastStart)
and (? > DATE_ADD(p.broadcastStart, INTERVAL p.durationSeconds SECOND))))
Login
FAQ
Search