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_h
ibernate.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():

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 have searched the forums for a resolution to my issue, but have not found a thread which is close enough . . . I am trying to create an HQL statement that will allow me to select rows from a table which match a certain date value. However, I want to query ONLY on the date value, not the time. The column in the database in which I am selecting on is the "timeReceived" property in the mapping above. The associated column in the database is defined as a datetime column.

Basically, I want to create a comparable HQL statement that will produced the same results as the following standard SQL statement:

"select count(*) from table tb
where date_format(tb.time_received, '%Y-%m-%d') = current_date();"

In place of current date, I may use a formatted date object in the correct format or the MySQL current_date() function.

Is it possible to use something like the Query.setDate() method and somehow tell the HQL statment that I want to use MySQL specific date formatting functions on the property/column?

I assume there is a fairly easy solution to this as it is a very common type of query.

Below is the exact standard SQL that I want to produce by way of HQL:

select count(*)
from jukebox_stats_report
where time_received = current_date()

Seeing that the property(timeReceived) in the mapping is defined as a date type, I would assume that ONLY the date info would be included in the where clause of the HQL statement that I am using below:

Query aggrCheck = session.createQuery("select count(jsr) " +
"from JukeboxStatsReportDTO jsr where " +
"jsr.timeReceived = :selectDate");
aggrCheck.setDate("selectDate", selectDate);

But when I run this, I get a count of zero when I am sure there are rows based on running the standard SQL listed above.

I also tried the following:

Query aggrCheck = session.createQuery("select count(jsr) " +
"from JukeboxStatsReportDTO where " +
"jsr.timeReceived = current_date()");

But received the following error:

12:45:30,027 ERROR [PARSER] *** ERROR: Invalid path: 'jsr.timeReceived'
12:45:30,030 ERROR [PARSER] *** ERROR: <AST>:0:0: unexpected end of subtree
12:45:30,141 ERROR [UtilBmBean] org.hibernate.hql.ast.QuerySyntaxError: Invalid path: 'jsr.timeReceived' [select count(jsr) from ami.server.dto.stats.JukeboxStatsReportDTO where jsr.timeReceived = current_date()]

From this, I assume the query string is not being parsed correctly, although the Hibernate docs do mention you may use the "current_date()" function.

Does anyone know how I can correctly determine whether I have data in a table based on a particular date, only checking the date values? Whether I use a scalar or not is immaterial. I just want to be able to know if the rowset is empty or not.

Thanks,

Mark

I am now using a SQLQuery and it appears to be working. Part of the problem was I was pointed to the wrong datasource. I will attempt to get this to work w/HQL also.