SQL-Query not returning specified Object

[Saikiran Madhavan]

Hi all,

I am using Hibernate 3.1 with SQL Server 2005,the problem i am facing is this.I have written a SQL-Query within my hbm file for returning a specified Object, that query is a complicated sub-query involving 3 tables.

first of all i am new to Hiberbate so i dont know how to convert the same query into HQL.

My SQL query is as below:-

  
<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=?)

   ]]>
</sql-query>

The hbm files are as 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="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>
 
</hibernate-mapping>

Account.hbm

 
<?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>

can anyone help me out why my query is not returning Mailbox object(s)and how to write the query in HQL format ?

thanks in advance
saikiran

[Mark Spritzler]

Just quickly looking at the sql-query, in the mapping you don't tell Hibernate what kind of object it is supposed to return, so how is Hibernate going to guess? So Hibernate will be returning a list of Object arrays. so List<Object[]>.

Here are some ways to get Hibernate to return an List of your objects rather than a list of Objects arrays

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);

And in your case to tell Hibernate what types of Objects you want you can do something like this from the Hibernate documentation

  
<sql-query name="persons">
    <return alias="person" class="eg.Person"/>
    SELECT person.NAME AS {person.name},
           person.AGE AS {person.age},
           person.SEX AS {person.sex}
    FROM PERSON person
    WHERE person.NAME LIKE :namePattern
</sql-query>
  

[Saikiran Madhavan]

Hi Mark,

I am sorry for posting the question multiple times.That question has got similar problem like that of mine, thats why i posted it there also.

Thank you for your reply and i will try this piece of code in my program.

Saikiran

[Saikiran Madhavan]

Hi,

i got the desired object by adding the return tag...Thank you for your valuable advice.


thanks all

saikiran