Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using a join in a Hibernate HQL update query

i have the following hibernate mapping:

 <class name="Domain.Roomreservation, Core" table="Reservationroom">
    <id name="ID" unsaved-value="undefined">
        <generator class="native">
            <!--<param name="sequence">GLOBALSEQUENCE</param>-->
        </generator>
    </id>

    <property name="FromTime" not-null="true" index="IDX_RESRAUM_FromTime" />
    <property name="UntilTime" not-null="true" index="IDX_RESRAUM_UntilTime"/>

    <many-to-one name="Booking" column="Book_ID" index="IDX_RAUMRES_BOOK" lazy="false"
        class="Domain.Booking, Core" not-null="true" />
    </class>

And the Reservationroom table looks like:

ID         <pk>
Book_ID    <fk>
FromTime
UntilTime
....
....

My Hibernate Query looks like:

String hql = "UPDATE Roomreservation as rr set rr.FromTime= 12:15" +
                     "Inner Join Booking b ON rr.Book_ID= b.ID " +
                     "Where b.ID = 95637";
                        IQuery query = CurrentSession.CreateQuery(hql);
                        int result = query.ExecuteUpdate();
                        Debug.WriteLine("Rows affected: " + result);

But I always getting Error: NHibernate.Hql.Ast.ANTLR.QuerySyntaxException

Can someone help me how to get this to work?

like image 361
Paks Avatar asked Aug 20 '12 08:08

Paks


People also ask

Can we use join in HQL query?

Some of the commonly supported clauses in HQL are: HQL From: HQL From is same as select clause in SQL, from Employee is same as select * from Employee . We can also create alias such as from Employee emp or from Employee as emp . HQL Join : HQL supports inner join, left outer join, right outer join and full join.

How do you use join in Hibernate query?

We can apply the Joins in Hibernate by using the HQL query or native SQL query. To make a join between the two tables, the two tables must be in a logical relationship. We can achieve the relationship between two tables by applying the parent table's primary key as a child table's foreign key.

How do you write a subquery in HQL?

A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. Note that HQL subqueries can occur only in the select or where clauses.


1 Answers

You have to use subquery instead of join. Roughly as follows:

UPDATE Roomreservation as rr set rr.FromTime= 12:15
WHERE rr.Book_ID IN (
  SELECT b.id 
  FROM Booking b 
  WHERE b.id = 95637);

Additionally depending about type of FromTime it is likely that it should be presented in some other format.

like image 167
Mikko Maunu Avatar answered Sep 18 '22 15:09

Mikko Maunu