Lasek:
I am having a similar problem. I am attempting to run a fairly complex query using a "createSQLQuery" call to a MySQL 4.0 database using Hibernate 3.0.5 and JDK 1.4.2. I have searched the forum for 3 days, but every suggestion I have found and tried does not fix the problem.
Here is the code:
Code:
String hql ="select play.company_id as {play.companyId}, play.contract_id as {play.contractId}, play.jukebox_id as {play.jukeboxId}, play.song_id as {play.songId},
date_format(play.time_selected, '%Y%m%d') as playDate,
sum(if (play.type_code = '100' and company.company_type = 'OPERATOR', play.free_credits + play.paid_credits, 0) )
as purchased_credits . . . from play_detail_event as play, companies as company, jukebox_stats_report as stats where play.company_id = company.id and play.jukebox_stats_report_id = stats.id and date_format(stats.time_received, '%d %M %Y') = :aggrDate
group by play.company_id, play.contract_id, play.jukebox_id, play.song_id, playDate";
SQLQuery playAggregatesQry = s.createSQLQuery(hql);
playAggregatesQry.addEntity("play", PlayDetailEventDTO.class);
playAggregatesQry.addEntity("company", CompanyDTO.class);
playAggregatesQry.addEntity("stats", JukeboxStatsReportDTO.class);
playAggregatesQry.addScalar("purchased_credits", Hibernate.INTEGER);
playAggregatesQry.addScalar("cleared_credits_total", Hibernate.INTEGER);
playAggregatesQry.addScalar("paid_std_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("paid_mod_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("paid_msf_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("total_paid_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("ifp_std_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("ifp_mod_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("ifp_msf_selections",
Hibernate.INTEGER);
playAggregatesQry.addScalar("total_ifp_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("total_patron_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("popularity_paid_plays", Hibernate.INTEGER);
playAggregatesQry.addScalar("royalty_paid_plays", Hibernate.INTEGER);
playAggregatesQry.addScalar("demo_plays", Hibernate.INTEGER);
playAggregatesQry.addScalar("ifp_plays", Hibernate.INTEGER);
playAggregatesQry.addScalar("plays_errors", Hibernate.INTEGER);
playAggregatesQry.addScalar("plays_other", Hibernate.INTEGER);
playAggregatesQry.addScalar("plays_rejected", Hibernate.INTEGER);
playAggregatesQry.addScalar("nifp_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("nifp_plays", Hibernate.INTEGER);
playAggregatesQry.addScalar("autoplay_selections", Hibernate.INTEGER);
playAggregatesQry.addScalar("autoplay_plays", Hibernate.INTEGER);
playAggregatesQry.addScalar("bad_media_errors", Hibernate.INTEGER);
playAggregatesQry.addScalar("faded_autoplays", Hibernate.INTEGER);
playAggregatesQry.addScalar("cleared_autoplays", Hibernate.INTEGER);
playAggregatesQry.addScalar("rejected_autoplays", Hibernate.INTEGER);
playAggregatesQry.addScalar("mod_cost", Hibernate.INTEGER);
playAggregatesQry.addScalar("mod_charge", Hibernate.INTEGER);
playAggregatesQry.setDate("aggrDate", date);
List playAggregates = playAggregatesQry.list();
The exception I keep getting is:
java.sql.SQLException: Column 'id0_' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2278)
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1289)
at org.jboss.resource.adapter.jdbc.WrappedResultSet.getInt(WrappedResultSet.java:328)
at org.hibernate.type.IntegerType.get(IntegerType.java:26)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:693)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:282)
at org.hibernate.loader.Loader.doQuery(Loader.java:389)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:208)
at org.hibernate.loader.Loader.doList(Loader.java:1522)
at org.hibernate.loader.Loader.list(Loader.java:1505)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1343)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)
at ami.server.datamart.billing.BDMAggregationSingleton.findPlayDetailEvents(BDMAggregationSingleton.java:949)
at ami.server.datamart.billing.BDMAggregationSingleton.compDailySongJukeAgg(BDMAggregationSingleton.java:254)
Ok, the questions I have are:
There is a mention in the Hibernate docs in the Native Query section(17.2) as follows:
Note: if you list each property explicitly, you must include all properties of the class and its subclasses!So, what does this mean exactly? Do I really have to include EVERY column from every table(even if the table is a many-to-one type foreign key?) This seems very illogical to me!!!
I have to use SQLQuery because of the use of the "if" conditions and DAT_FORMAT functions specific to MySQL.
What am I doing wrong? Also, the setDate bind for the date column does not work on other queries where I have selected all columns from the table. But when I hard code a sample date string based on the DATE_FORMAT I am using in the sql, it returns the proper result set.
If I can't get this to work, I will be forced to use standard JDBC calls. I would prefer to use Hibernate if I could seeing that I have all the code written for it. Is this really a bug in 3.0.5 and fixed in 3.1. If so, when will 3.1 become production grade?[/b][/code]