Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0.5

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="ami.server.dto.stats.JukeboxStatsReportDTO" table="calliope_log_hibernate.jukebox_stats_report">
<id name="id" column="id">
<generator class="native"/>
</id>
<property name="jukeboxId" column="jukebox_id"/>
<property name="hardDiskId" column="hard_disk_id"/>
<property name="companyId" column="company_id"/>
<property name="contractId" column="contract_id"/>
<property name="timeStart" column="time_start" type="date"/>
<property name="timeEnd" column="time_end" type="date"/>
<property name="timeReceived" column="time_received" type="date"/>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Iterator dates = session.createSQLQuery("select distinct(date(time_received)) from jukebox_stats_report").list().iterator();

Full stack trace of any exception that occurs:

Name and version of the database you are using:
MySQL 4.1.14

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I am attempting to determine the best way to implement a query where I arrive at a result set based on the use of the distinct function and the MySQL specific DATE function. I basically want to create a grouping of all distinct dates, taking into account ONLY the date portion of the MySQL datetime column, and omitting the time portion(thus the use of the DATE function in the SQL).

When I execute the query listed above, the Iterator appears to have an element based on a "hasNext()" call, however I get an ArrayIndexOutOfBoundsException when I execute the following code on the returned Iterator object:

Iterator dates = session.createSQLQuery("select distinct(date(time_received)) from jukebox_stats_report").list().iterator();

while(dates.hasNext()) {
Object[] row = (Object[]) dates.next();
Date date = (Date)row[0];

The ArrayIndexOutOfBoundsException is being thrown on the Date date = (Date)row[0] because of the invalid index. One of my questions is; do I treat the result of a distinct as a scalar, thus requiring the use of the Object Array as above? Or would a better approach be to structure the code another way, using the mapping object as a return value and obtain the single value through that object?

Iterator dates = session.createSQLQuery("select distinct(date(time_received)) from jukebox_stats_report stats").addEntity("stats", JukeboxStatsReportDTO)list().iterator();

while(dates.hasNext()) {
JukeboxStatsReportDTO jsr = (JukeboxStatsReportDTO)dates.next();
Date date = jsr.getTimeReceived();
}


I have not tried the second approach yet. What is the most efficient way to get this distinct SQL query w/the MySQL specific function to work?

Mark

The second approach I mentioned yeilds the following error:

"column id0_ not found".

I have seen this error before and posted it on the forums. Various techs have attempted to answer this question, but I am still at a loss as to how I can format this style of query.

Again, all I want to do is execute the following query, which makes use of MySQL specific functions(in this case the "date" function):

"select distinct(date(date_column) from table".

That's it. HQL will not work because of the MySQL specific functions. SQLQuery is the only option. However, when I attempt the following, I get the "column id0_ not found SQLException":

List dates = session.createSQLQuery("select distinct(date(time_received)) from jukebox_stats_report stats").addEntity("stats", JukeboxStatsReportDTO.class);

I have tried everything I can think of here and am at a complete loss as to how to execute this simple query. Is this possible based on the previously listed mappings in Hibernate 3.0.5?

Mark