Hibernate with native SQL issue

[Ankur Si]

Hi Ranchers,
I've got an problem in hibernate with SQL, my code is as below -

 
Query query = session.createSQLQuery("select event.* from person right outer join event on event.event_id=person.person_id"); 
 
 List insurance = query.list();
System.out.println(" SIZE IS >>>> " + insurance.size());                  
for (Iterator it = insurance.iterator(); it.hasNext() :wink:    
{
EventMulti evnt1 = (EventMulti )it.next();

   System.out.println("??????? Title :" +evnt1.equals(EventMulti.class));
}
 
   

It gives me following error -

  
Hibernate: select event.* from person right outer join event on event.event_id=person.person_id
 SIZE IS >>>> 22
[Ljava.lang.Object;
 
log4j:WARN Please initialize the log4j system properly.
java.lang.ClassCastException: [Ljava.lang.Object;
  

could you please help me out in this regard.

Thanks in advance

[David Madouros]

Ankur,

When you use 'select' in either an HQL or native SQL query, Hibernate does not return the object in your 'from' clause. Just as when using non-Hibernate SQL, 'select event.* ...' returns all the columns from the event table. The List you get back from your query is a list of Object[] (object arrays). Each element in the list is an array of Objects and each element in the array represents one column in the event table.

For example, if I have a table defined as:

mysql> describe parent;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| uid       | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| last_name | varchar(30) | YES  | UNI | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

that contains the following data:

mysql> select * from parent;
+-----+-----------+
| uid | last_name |
+-----+-----------+
|   3 | Isidorio  |
|   2 | Johnston  |
|   1 | Madouros  |
+-----+-----------+

and I use a Hibernate SQL query as follows:

select parent.* from Parent parent order by parent.uid

I get a List of Object[]. The first element in the List represents the first row in my result set:

Object[0] is of type BigInteger and has a value of 1
Object[1] is of type String and has a value of "Madouros"

The second element in the List represents the second row in my result set:
Object[0] is of type BigInteger and has a value of 2
Object[1] is of type String and has a value of "Johnston"

etc.

Not seeing your tables, I cannot say exactly what you get back. But, this should give you the idea.

[Saikiran Madhavan]

hi David,

I too face the same issue as you told, i am using sql-query in my hbm and instead of getting my specified Mailbox Object, i am getting collection of Object.

Can you tell me how to write the same peice of SQL in HQL and to retrieve my specified Object

my hbm file is as below:-

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.optum.secureMessaging.model">
   <class name="Mailbox">
  
  <id name="mailboxId" type="java.lang.Long">
            <column name="mailbox_id" />
            <generator class="native"></generator>
        </id>
        
        <!-- entry for Administrator Account id -->
        
        <property name="adminaccountId" type="java.lang.Long">
            <column name="administrator_account_id"  />
        </property>
        
        <property name="name" type="java.lang.String">
            <column name="name" length="255" />
        </property>
        
        <property name="description" type="java.lang.String">
            <column name="description" length="255" />
        </property>
            <property name="notificationEmail" type="java.lang.String">
            <column name="notification_email_address" length="255" />
        </property> 
        
  <!-- SET OF ACCOUNTS -->
        <set name="accounts" table="account_mailbox" lazy="false" inverse="true" cascade="all" sort="natural" >
<key column="mailbox_id"/>
<many-to-many class="Account" column="account_id" />
</set>

<!-- SET OF FOLDERS -->
         <set name="folders" cascade="all" inverse="true" lazy="false" sort="natural">
<key column="mailbox_id" />
<one-to-many class="Folder" />
</set>
        
  </class>
  
  <query name="Mailbox.Name">FROM Mailbox WHERE name=?</query>
  
  <query name="Mailbox.MailId">FROM Mailbox WHERE mailbox_id=?</query>
  
    
  
   <!-- This query will return the count of the number of accounts with
the specified address. However, it returns a list in which the first
element in the list is an Long object representing the count -->
<query name="Mailbox.CountByName"><![CDATA[
      select count(*) FROM Mailbox WHERE name=?)]]>
   </query>
   
   <query name="Mailbox.AdminAccountId"> FROM Mailbox WHERE administrator_Account_id=?</query>
   
   <query name="Mailbox.">From Mailbox where administrator_Account_id='4234'</query>
      
  
  <sql-query name="Mailbox.MatchMailboxesToMessage">
    <return-scalar column="name" type="string"/>
  <![CDATA[
    select M.name
    from Mailbox M,
    Folder F,
    Folder_Message FM
    where M.mailbox_id = F.mailbox_id
    and F.folder_id = FM.folder_id
    and FM.message_id = ?
    ]]>
</sql-query>

<!--   [B]<sql-query name="Mailbox.MatchMailboxesToAccount">    
  <![CDATA[    
    SELECT *
FROM mailbox
WHERE mailbox.administrator_account_id=? 
AND mailbox.mailbox_id NOT IN (
                SELECT mailbox.mailbox_id
FROM account, account_mailbox, mailbox 
WHERE account.address=?
 AND account.account_id=account_mailbox.account_id
 AND account_mailbox.mailbox_id = mailbox.mailbox_id
AND mailbox.administrator_account_id=?)

   ]]>[/B]</sql-query> -->    
</hibernate-mapping>

the high-lighted query is the problematic one.
The associated Account.hbm is also given below

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.optum.secureMessaging.model">
  <class name="Account">
  
  <id name="accountId" type="java.lang.Long">
            <column name="account_id" />
            <generator class="native"></generator>
        </id>
        
        <property name="address" type="java.lang.String">
            <column name="address" length="255" />
        </property>
        
        <property name="active" column="active" type="yes_no" />
        
        <!-- SET OF MAILBOXES         -->
        <set name="mailboxes" table="account_mailbox" cascade="all" sort="natural">
<key column="account_id"/>
<many-to-many class="Mailbox" column="mailbox_id" />
</set> 
 
        
  </class>
  
  <!-- Select all accounts matching a criteria address -->
  <query name="Account.Address">FROM Account WHERE address=?</query>
  
  <!-- Count the number of accounts with the same address as the criteria address -->
  
  <query name="Account.Active">FROM Account WHERE active=?</query>
  
  <!-- This query will return the count of the number of accounts with
the specified address. However, it returns a list in which the first
element in the list is an Long object representing the count -->
<query name="Account.CountByAddress"><![CDATA[
      select count(*) FROM Account WHERE address=?)]]>
   </query>
   
   <!-- This query will return the count of the number of accounts with
the specified address that currently have the specified value for "active". 
However, it returns a list in which the first element in the list is an Long 
object representing the count -->
<query name="Account.CountByActiveAddress"><![CDATA[
      select count(*) FROM Account WHERE address=? and active=?)]]>
   </query>
   
   <query name="Account.">FROM Account</query>
   
   <sql-query name="Account.Mailbox"><![CDATA[
      select address from Account where account_id in 
      (select account_id from account_mailbox where mailbox_id =?)]]></sql-query>
   
   <!-- Match an account and message id. Uses account address and mailbox id -->

<!--
   
   <sql-query name="Account.MatchMessageToAccount">
<return alias="id" class="Long"/>
select Msg.message_id from Mailbox M, Account_Mailbox AM, Account A, Folder F,
    Folder_Message FM, Message Msg where A.address=? and AM.account_id=A.account_id 
    and M.mailbox_id=AM.mailbox_id and F.mailbox_id=M.mailbox_id and FM.folder_id=F.folder_id 
    and Msg.message_id=FM.message_id and Msg.message_id=?
    
</sql-query>
  -->
   
   
   <sql-query name="Account.MatchMessageToAccount">
select count(Msg.message_id) from Mailbox M, Account_Mailbox AM, Account A, Folder F,
    Folder_Message FM, Message Msg where A.address=? and AM.account_id=A.account_id 
    and M.mailbox_id=AM.mailbox_id and F.mailbox_id=M.mailbox_id and FM.folder_id=F.folder_id 
    and Msg.message_id=FM.message_id and Msg.message_id=?
    
</sql-query>
  
</hibernate-mapping>

thanks in advance
saikiran

[Mark Spritzler]

Saikiran,

Please don't duplicate post. You have already created a thread on this topic. Which is right here

http://www.coderanch.com/t/217667/ORM/java/SQL-Query-not-returning-specified

And answers both questions here, so I don't want to have to duplicate my answers to two different threads.

Good Luck

Mark