createSqlQuery problems

[ultron]

I'm trying to do a very simple native SQL query using Spring/Hibernate like this:

        List subscriptions = getSession().createSQLQuery("select {s}.ID as {s.id} from product {s}")
            .addEntity("s", Product.class)
            .list();

The above code produces the following exception:

2007-06-23 17:24:46,789 INFO [org.hibernate.type.IntegerType] - could not read column value from result set: clazz_0_; Column 'clazz_0_' not found.
2007-06-23 17:24:46,789 INFO [org.hibernate.type.IntegerType] - could not read column value from result set: clazz_0_; Column 'clazz_0_' not found.
2007-06-23 17:24:46,794 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: S0022
2007-06-23 17:24:46,794 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: S0022
2007-06-23 17:24:46,794 ERROR [org.hibernate.util.JDBCExceptionReporter] - Column 'clazz_0_' not found.
2007-06-23 17:24:46,794 ERROR [org.hibernate.util.JDBCExceptionReporter]  Column 'clazz_0_' not found.

The following HQL query works fine though:

getHibernateTemplate().find("from Product");

Can anyone with some more experience with this please point me in the right direction, as I'm out of ideas on this one. I

[dr_pompeii]

List subscriptions = getSession().createSQLQuery("select {s}.ID as {s.id} from product {s}")

see this in your query :
from product the "p"

try in the same sentence with:
from Product the "P"

regards

[ultron]

see this in your query :
from product the "p"

try in the same sentence with:
from Product the "P"

regards

Thanks for the input, though this part is actually correct. The database table is named 'product' while the corresponding Hibernate class is called 'Product'.

My latest attempt is this query:

        List products = getSession().createSQLQuery("select {p}.ID from product as {p}")
            .addEntity("p", Product.class)
            .list();

Which produces the following error output:

2007-06-24 19:06:50,150 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: S0022
2007-06-24 19:06:50,150 ERROR [org.hibernate.util.JDBCExceptionReporter] - Column 'ID9_0_' not found.

Can anyone please let me know how I can turn on the logging so that I can see the actual SQL Hibernate produces? Or is there anything else that is obviously wrong with my query?

I really have no clue what is going on and what kind of mangled SQL Hibernate is generating at this point. (The column ID9_0_ obviously doesn't exist anywhere.) But again, HQL works flawlessly, whereas SQL doesn't. The unfortunate part is that I'm going to have to use native SQL for some of the queries, so I have to get this working somehow...

[dr_pompeii]

The database table is named 'product' while the corresponding Hibernate class is called 'Product'.

yes i know, but when you work with Hibernate you should use the name class no the table name, check too the name properties , if in your class you has a variable call "name" but in the table in the db it has other name like 'myname'

so in your query you should work with "name" instead of "myname"

Can anyone please let me know how I can turn on the logging so that I can see the actual SQL Hibernate produces? Or is there anything else that is obviously wrong with my query?

for log4j
add this

#Hibernate Framework
log4j.logger.org.hibernate=INFO
log4j.logger.org.hibernate.tool.hbm2ddl=INFO

now too add in your context for hibernate in

<property name="hibernateProperties">
...
		<!-- Echo all executed SQL to stdout -->
		<prop key="hibernate.show_sql" >true</prop>		
...

see this link
http://www.hibernate.org/hib_docs/v3...rysql-creating

i see in the examples that they does something like this

sess.createSQLQuery("SELECT * FROM CATS")
 .addScalar("ID", Hibernate.LONG)

and you dont

i never work with sql query direcly with Hibernate

but the link should be useful

regards

[karldmoore]

The reason for the weird looking names is purely because Hibernate has generated them for you. Have you tried something simple like this? It would be useful to see the HBM the code you are running and the log output.

List products = getSession().createSQLQuery("select * from product"
            .addEntity(Product.class)
            .list();

[ultron]

The reason for the weird looking names is purely because Hibernate has generated them for you.

After some more testing, it has become clear that the query works if every quantity is explicitly queried for:

        List subscriptions = getSession().createSQLQuery("select "
                + "  p.ID,"
                + "  p.ProductTypeID,"
...
                + "  s.PeriodicPrice,"
...
                + " from"
                + "  subscription s" 
                + " inner join"
                + "  product p "
                + "   on s.ID = p.ID")
        .addEntity("s", Subscription.class)
        .list();

Hibernate generates the query:

select   p.ID,  p.ProductTypeID,  ...,  s.PeriodicPrice,  ... from  subscription s inner join  product p    on s.ID = p.ID

which works. The query Hibernate executes appears to be unchanged from the one I input in this case.

However, if I use asterisks to 'select all' in a slightly simpler query, like this:

        List subscriptions = getSession().createSQLQuery("select "
                + "  {s.*} "
                + " from"
                + "  subscription " 
                + " inner join"
                + "  product p "
                + "   on s.ID = p.ID")
        .addEntity("s", Subscription.class)
        .list();

i get the following errors:

Hibernate: select   s.ID as ID9_0_, s_1_.Description as Descript2_9_0_, s_1_.DoSendAboInfo as DoSendAb3_9_0_, s_1_.IsDeleted as IsDeleted9_0_, s_1_.ItemID as ItemID9_0_, s_1_.OurItemNr as OurItemNr9_0_, s_1_.Price as Price9_0_, s_1_.Title as Title9_0_, s_1_.ProductTypeID as Product10_9_0_, s_1_.VendorID as VendorID9_0_, s.IsDefaultSub as IsDefaul2_67_0_, s.IsVisible as IsVisible67_0_, s.PeriodicPrice as Periodic4_67_0_, s.PortingText as PortingT5_67_0_, s.SortWeight as SortWeight67_0_, s.SubscriptionURL as Subscrip7_67_0_, s.ContractPeriodID as Contract8_67_0_, s.ID as ID67_0_  from  subscription s inner join  product p    on s.ID = p.ID
2007-06-26 11:54:21,891 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1054, SQLState: 42S22
2007-06-26 11:54:21,891 ERROR [org.hibernate.util.JDBCExceptionReporter] - Unknown column 's_1_.Description' in 'field list'

Basically, it appears that if Hibernate is doing any mapping whatsoever the SQL gets mangled beyond recognition, with Hibernate creating aliases such as s_1_ out of thin air without even defining them. I've tried a number of different combinations using various combinations of addEntity and addJoin and including {p.*} in the selection, but the query always fails.

Is this an indication that there are annotations missing from my .java class files? Before I make this post even longer by including those files, do I need to specify explicit annotation mapping rules in those files for a native SQL 'select all' to work as expected? Are there any other obvious problems with what I'm doing? I'd like to mention again though, that HQL queries work just fine, which is why I'm surprised to see so many problems with native SQL.