Native sql iBatis style

oneway · **Joined:** Sun Sep 14, 2003 10:54 am **Posts:** 36

Hi,

Sometimes it may be necessary to execute custom SQL that returns, e.g., one value or a List of values (e.g. a list of Strings or list of int values, etc). iBatis makes it very easy to execute this type of queries (see code below), without a need for any special mappings, etc.

What is the best way to do this in HB? Thanks

Code:

<select id="getInventoryQuantity" resultClass="int" parameterClass="string">

    select QTY as value from INVENTORY where ITEMID = #value#
  
</select>

christian · **Posted:** Fri Jul 09, 2004 2:12 pm

Hibernate is a "full" ORM tool, iBatis is a "light" ORM tool. Both have their use and place. In Hibernate, you would do myInventory.getQuantity().

oneway · **Joined:** Sun Sep 14, 2003 10:54 am **Posts:** 36

Yes, the example I used was not a very good one. This one is better:

Code:

<select id="getUniqueUsernameList" resultClass="string" parameterClass="account">
    select distinct USERNAME as value from SIGNON
</select>

The idea is that I do not want to drag back the whole SIGNON table (it could be fairly large and *wide*), I just want the list of unique user names that signed on.

Thanks

christian · **Posted:** Fri Jul 09, 2004 2:26 pm

In Hibernate, use an HQL query.

oneway · **Joined:** Sun Sep 14, 2003 10:54 am **Posts:** 36

Quote:

In Hibernate, use an HQL query.

But then I would still be returning the list of Signon class instances, would I not?

If not, then it appears I'd have to make up a new class something like:

Code:

public class SignonUserName {
private String userName;
...
}

and then use it in the query:

Code:

<sql-query name="hbUniqueUserList">
    <return alias="sigonUserName" class="SignonUserName"/>
    select distinct {Signon}.USERNAME as value from SIGNON {Signon}
</sql-query>

Is that what you had in mind? Seems like a lot of work just to run a query.

Also, the SQL query must only use the classes that have been mapped to tables, correct?

Thanks

christian · **Posted:** Fri Jul 09, 2004 3:01 pm

No, use projection. Please read the HQL documentation.

oneway · **Joined:** Sun Sep 14, 2003 10:54 am **Posts:** 36

christian wrote:

No, use projection. Please read the HQL documentation.

The point I'm trying to make is that the result of native SQL query can only be a class that has been mapped (or a list of the mapped class instances). It's impossible for the result of native SQL to return, say, an int/Integer or a list of Dates, etc.

Thanks

christian · **Posted:** Fri Jul 09, 2004 8:17 pm

There is already a JIRA issue about "returning scalar types" from createSqlQuery(). Aside from the fact that "scalar type" is not really correct, this is where you have to put your vote.