Hi

I'm wondering if the following is possible...

I have sql queries, that contain fields out of several tables. Not all columns from the tables are used. Therefore, the SELECT fields do not make up an existing Hibernate generated database Entity object, instead I want to be able to automatically populate a custom class, for example the following:

Class Customer
{
BigDecimal oid;
BigDecimal Ldc_oid;
BigDecimal addr_oid
....
}

This instances of Customer object will be used to populate a table on a jsp page later.

So, is it possible for hibernate to create an resultset based Customer class upon executing sqlquery ( below ) - that isn't a Hibernate generated entity object.

resultset = query.list()

( So, resultset is a list of Customer objects )

I could create a database View that represents the selected query so Hibernate will generate a Entity class based on this, but this isn't ideal.

Thanks.

HQL's "select new" functionality will do what you need, if all the tables you're reading from are mapped:SQL quereis might also support select new, though there's nothing about that in the ref docs, and I've never tried it.

Thanks,

I don't suppose you can use native SQL?

I need to use oracle's NVL command

I don't think SQL queries support the "new" syntax, but I know I've seen references to nvl in the dialect files. Does HQL support nvl? There's a line in OracleDialect:That makes me think that you can use nvl() in HQL.

When native sql is used, the result set is a list of object arrays. Is there anyway to wrap all this up into an object that will refer to each element in the array for easier access. I don't want to iterate through the entire resultset copying each row into java object.

object[1] doesn't mean a lot - I'd like to access getCity instead, for example.

Does Hiberate offer an easy method for doing this?

Thanks

Did nvl in HQL not work? If it does, that's bound to be the best solution.

To use native SQL queries for this, given that they (probably) don't support the new syntax, you have to build the object from scratch. Hibernate gives you a list of Object[]s per result set row, and there's no way for hibernate to do anything with those objects. So no, all you're going to get out of the returned list is a pile of scalars without names.

First, try using new(), maybe it does work with SQL queries. After that, I'd recommend writing a data access logic method that is specific to the query, is aware of what's in Object[1] etc., and builds the correct objects from those arrays.

I have an sql query that I don't think HQL has the functionality to replicate, thus I'm wondering about an alternative.

I tried the new command -

SELECT { new SearchResults ( field1, field2 ) } from
...

But I got
Non supported SQL92 token at position: 13: new
[2006-03-07 16:41:39,706,ERROR, lang.Class] org.hibernate.exception.GenericJDBCException: could not execute query

As I understand things, you use { } curly brackets to disinguish between HQL and native SQL.

No, I don't think {} means anything. You use createSQLQuery for SQL queries, and createQuery for HQL queries. There is no createHQLSQLQuery method, or anything like that.

Obviously SQL queries don't support the new syntax then. Bummer.

I'm curious to know what you're trying to do that you think HQL can't handle. There are things that it can't do, but not too many. If this really is one of those cases, then you will have to use the List<Object[]> that your query is returning, there's no way around it.

Since in hibernate 3.0 ( I'm using MyEclise ), you have to specify types for each selected field, for example:

query.addScalar("OID" , Hibernate.LONG );
query.addScalar("LDC_OID" , Hibernate.LONG );
query.addScalar("ADDR_OID" , Hibernate.LONG );

I'm wondering that it can't be that difficult to populate a given object, if the constructor matches the definition given in the addScalar list.

But if you're using createSQLQuery then the functionality isn't there. You have to do it yourself. Doesn't matter that it would be easy for hibernate to do it: if noone has put the functionality in, then you can't use that functionality.

Can I re-ask a couple of questions:

1) Have you tried using nvl() in an HQL query? Is it supported? That's probably the way to go, if it's possible.

2) What query are you writing that HQL cannot handle? Why are you using SQL queries, which do not handle the "new" syntax, when you could be using HQL, which does?

Here is the native sql:

SELECT
DISTINCT ElCustomer.OID,
ElCustomer.LDC_OID,
ElCustomer.ADDR_OID ,
ElCustomer.LDC_CNSMR_ACNT_NUM ,
ElCustomer.FULL_NAME ,
ElCustomer.NAME_VALIDATOR,
ElCustomer.ADDR_VALIDATOR ,
ElCustomer.BILLING_CYCLE,
ElAddr.MINISTRY_OID ,
ElAddr.STREET_ADD ,
ElAddr.UNIT_NUM ,
ElAddr.CITY_OID,
ElAddr.PROVINCE,
ElAddr.POST_CODE,
ElCity.NAME cityName,
ElMinistry.NAME ministryName
FROM
EL_ADDR ElAddr,
EL_CITY ElCity,
EL_CUSTOMER ElCustomer,
EL_MINISTRY ElMinistry,
EL_DAILY_AGG_DATA,
EL_SEARCH_ENTITY searchCity,
EL_SEARCH_ENTITY customerSearch,
EL_SEARCH_ENTITY searchMinistry
WHERE
customerSearch.TYPE = 'C' AND
EL_DAILY_AGG_DATA.SEARCH_ENTITY_OID = customerSearch.OID AND
EL_DAILY_AGG_DATA.SRVC_DAY BETWEEN :startDate AND
:endDate AND
ElCustomer.OID = customerSearch.VALUE AND
searchCity.TYPE ='I' AND
elCity.OID = searchCity.VALUE AND
elCustomer.ADDR_OID = elAddr.OID AND
elAddr.CITY_OID = elCity.OID AND
searchMinistry.TYPE = 'M' AND
elMinistry.OID = searchMinistry.value AND
elAddr.MINISTRY_OID = elMinistry.OID AND
searchMinistry.value = NVL ( :ministryOid , searchMinistry.value ) AND
UPPER( ElAddr.STREET_ADD ) LIKE '%' || NVL ( :street ,UPPER( ElAddr.STREET_ADD ) ) || '%' AND
searchCity.value = NVL ( :city , searchCity.value ) AND
ElAddr.POST_CODE LIKE NVL ( CONCAT(:postcode1,:postcode2) , ElAddr.POST_CODE ) AND
ElCustomer.LDC_CNSMR_ACNT_NUM = NVL ( :consumerAccountNumber , ElCustomer.LDC_CNSMR_ACNT_NUM )


I tried converting this to HQL , but Hiberate disliked the aliased self joins to EL_SEARCH_ENTITY.