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