How do you execute a SQLQuery?

Lasek · **Joined:** Fri Apr 29, 2005 10:57 pm **Posts:** 41

Hibernate version: 3

I'm trying to execute an SQLQuery:

Code:

SQLQuery q = HibernateUtil.getSession().createSQLQuery(
         "select * from NBWA_business as business WHERE TO_DAYS(expirationDate) > TO_DAYS(:date)"
      );
      q.setDate("date",getDate());
      q.addEntity("business",Business.class);
      System.out.println( q.list() );

I have no problems using a Business class in normal Hibernate context, but this is the first time I tried SQLQuery (because there's a very large database and Hibernate query language doesn't have any date functions), and I get the following problem:

Code:

Hibernate: select * from NBWA_business as business WHERE TO_DAYS(expirationDate) > TO_DAYS(?)
Jul 16, 2005 3:33:30 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: S0022
Jul 16, 2005 3:33:30 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Column 'ID0_' not found.
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not execute query
   at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
   at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:1596)
   at org.hibernate.loader.Loader.list(Loader.java:1577)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
   at org.ablogic.NBWA.core.NewBusinessesApplication.start(NewBusinessesApplication.java:57)
   at org.ablogic.NBWA.Main.main(Main.java:61)
Caused by: java.sql.SQLException: Column 'ID0_' not found.
   at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:910)
   at com.mysql.jdbc.ResultSet.getLong(ResultSet.java:2553)
   at com.mchange.v2.c3p0.impl.NewProxyResultSet.getLong(NewProxyResultSet.java:2754)
   at org.hibernate.type.LongType.get(LongType.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:759)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
   at org.hibernate.loader.Loader.doQuery(Loader.java:412)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
   at org.hibernate.loader.Loader.doList(Loader.java:1593)
   ... 6 more

Business is a simple object with a Long ID.

Any ideas on what the problem is or where can I find some info on how to exectue SQL Queries?

Thanks!

heed · **Joined:** Thu Jul 14, 2005 6:03 pm **Posts:** 2

You may want to try refreshing your schema if it has changed at all recently. I often see a similar exception when I modify a configuration file and then test without exporting the schema to the database.

Lasek · **Joined:** Fri Apr 29, 2005 10:57 pm **Posts:** 41

Nope tried that, the schema is the same, just to be safe I re-created the table - still the same error :(((

BUt is the way I am trying to use SQLQuery correct?

heed · **Joined:** Thu Jul 14, 2005 6:03 pm **Posts:** 2

Have you tried
select business.* from NBWA_business as business WHERE TO_DAYS(business.expirationDate) > TO_DAYS(?)

Also, see it the query select * from NBWA_business as business works...

Lasek · **Joined:** Fri Apr 29, 2005 10:57 pm **Posts:** 41

Nope same problem... even when I delete all the WHERE stuff - same exect exception...

What's wierd is why is it trying to search for ID0_ ??? Of course there's no such column, there an ID column, and my .hbm.xml file says

Code:

        <id name="ID" column="ID" type="java.lang.Long">
            <generator class="native"/>
        </id>
        

:(((((

Any ideas?

max · **Posted:** Mon Jul 18, 2005 4:52 am

read the docs for how to execute sql queries.

p.s. your query will work as is in H3.1, but not in 3.0.x

Lasek · **Joined:** Fri Apr 29, 2005 10:57 pm **Posts:** 41

YAY I solved the problem... However, a few notes:

1) Here's a good document on native SQL queries:

http://www.hibernate.org/hib_docs/v3/re ... rysql.html

2) Please please PLEASE highlight "braces" because I used parethesis instead and couldn't figure out why Hibernate wasnt removing them...

3) Setting <property name="hibernate.show_sql">false</property> to true doesn't show statements (I'm using System.out)

4) Your docs are either outdated or overdated because the methods specified there (createSQLQuery with 3 params) don't actually exist in 3.0.5.

But thanks a lot!

Oh BTW a quick explanation on how I fixed it - simply put {} around all aliases.

mark_in_gr · **Joined:** Mon Sep 05, 2005 4:48 pm **Posts:** 31

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]

max · **Posted:** Tue Sep 06, 2005 1:02 am

yes you need to list all columns - how else can hibernate build the objects ?

It is not ALL columns from ALL tables - it is all columns that is relevant for your class and the subclasses.

mark_in_gr · **Joined:** Mon Sep 05, 2005 4:48 pm **Posts:** 31

But I have selected all columns that are relevant to my query. Your response is very vague(as is the documentation, which I find lackluster at best!!!!!). What exactly do you mean all columns relevant to my classes and subclasses?

Let's say that I have a join with two tables, and each table has a many-to-one relationship w/another class/table, then that table has a many-to-one, and so on . . . are we expected to include ALL columns from ALL tables? This makes no sense and I don't expect that ANY ORM API would enforce this type of rigidness!

If you could explain yourself more thoroughly or provide an example, it would be helpful.

emmanuel · **Posted:** Wed Sep 07, 2005 4:22 am

mark_in_gr wrote:

Your response is very vague(as is the documentation, which I find lackluster at best!!!!!).
[...]
This makes no sense and I don't expect that ANY ORM API would enforce this type of rigidness!

You understand that denigrate the code and doc author is not the best way to get help, don't you?

Have a look at the unit test suite available with the distribution, there are unit tests for every feature of hibernate and we spend time to make it user friendly.

max · **Posted:** Wed Sep 07, 2005 6:29 am

as emmanuel said - look in the examples/junittests.

And please rethink what you are saying - if you ask hibernate to return full objects then we need all the full columns. And of course we only need ids for assocations if your assocations are lazy!

mark_in_gr · **Joined:** Mon Sep 05, 2005 4:48 pm **Posts:** 31

Well, you have to admit, the documentation is rather lacking. I was not denagrading it, I was just stating a fact. I don't have time to sift through examples for days, so I was forced to implement the solution in a technology I am more familiar with, that being straight JDBC calls.

It is working now using JDBC. Thank you anyway for the response.

b_chin · **Joined:** Fri Apr 04, 2008 2:12 pm **Posts:** 11

max wrote:

as emmanuel said - look in the examples/junittests.

Where are these examples you speak of? I am having similar problems. I have Hibernate v3.2.5, and I cannot find them anywhere.

max · **Posted:** Sun Apr 13, 2008 5:17 am

in the docs and unit test.

Use search in the docs and use Find references in your favorite IDE to find code in the unittests that uses createSQLQuery