hql

Hibernate; HQL; why does the delete query not work, but select does?

I want to delete certain records from a table. These records have some child-records in other tables. In order to be able to delete the main records, I have to delete the child records first. Here is the example of the HQL used: delete from ItineraryBooking ib where ib.booking.user.id = :paramId Basically, this should remove all It...

Hibernate Query that Orders an Attribute based on a nested path.

I have two Objects, Entries and Samples. Each entry has an associated set of Samples, and each Sample has a vote associated with it. I need to query the database for all Entries, but for each Entry I need the associated set of Samples sorted according to their vote attribute: public Class Entry{ Set<Sample> samples; } public Clas...

Convert SQL-query with subselect in select section to HQL

Hello. I have trouble with converting native SQL query to HQL. Query is something like follows: select count(*) , sum(select count(*) from employee e where e.company_id=c.id)) from company c where c.id = someID First returned value is count of companies, second - amount of employees for specified company. I.e. I have to get this tw...

Correct sql/hql query (aggregate in where clause)

I want to do query as below. Query is wrong but describes my intentions. SELECT name, dateTime, data FROM Record WHERE dateTime = MAX(dateTime) Update: Ok. The query describes intentions not quite good. My bad. I want to select latest record for each person. ...

List of list of named parameters in EJBQL/HQL

I'm trying to execute a long 'INSERT ON DUPLICATE KEY UPDATE' with up to a few thousand rows in a MySQL+JBoss+Hibernate application. It looks something like: INSERT INTO Table (field1, field2, field3) VALUES (value1_1, value2_1, value3_1), (value1_2, value2_2, value3_2), (value1_3, value2_3, value3_3), ... ON DUPLICATE KEY UPDATE ... ...

how does the new keyword in hql work?

I found this example in jboss's documentation. select new Family(mother, mate, offspr) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr Where does the Family class come from. Do I need to import somewhere, or to use its fully qualified class name? ...

HQL query throws ClassCastException when using count()devided by double value

I have a query looks like this @NamedQuery(name="WorkingDayLog.getExpectedWorkingHours",query="select ((count(o.id)-:approvedVacs)*(:shiftDuration/60.0)*:factor) from WorkingDayLog o where o.workingDayDate between :appraisalStart and :appraisalEnd")` it compiles fine but when executed it throws the following exception ClassCastExce...

Get children count via HQL

Hi, I have a one-to-many mapping between a parent entity and child entities. Now I need to find the number of children associated with each parent for a list of parents. I am trying to do this with HQL but I am not sure how I can get the list of parents in there. Also, I don't know how I can return the entity itself and not just its ID....

Hibernate Query with conditions in a complex db structure

I have a complex database that's looking like this: product *1 <-> n* inventory *n <-> 1* inventoryUser *1 <-> n* user Now I would like to query e.g. all products where user.firstname = 'peter' in hql. ...

Hibernate HQL: Get count of results without actually returning them

I want to get the count of the results of a dynamically-generated HQL query, without actually getting the list of results. Say that the query I have is something like: select Company company LEFT OUTER JOIN FETCH products product I read in the Hibernate documentation that: You can count the number of query results without returnin...

Nhibernate: distinct results in second level Collection

I have an object model like this: class EntityA { ... IList<EntityB> BList; ... } class EntityB { ... IList<EntityC> CList; } I have to fetch all the colelctions (Blist in EntityA and CList in EntityB), because if they all will be needed to make some operations, if i don't eager...

Creating a default query for a column in a table (SQL)???

Hey I have a column in one of my tables which is suppose to be the total sum for from the rows of a number of tables. Is there a way i can have a default query which runs on the total sum column so that every time a row is added to the other table an update is made in the total sum column. Thanks ...

Executing multiple updates in a single NHibernate HQL statement

I would like to write the following SQL in HQL so it executes as a single statement: update child_thingy c set c.parent_thingy_id = null where c.common_thingy_id = @common_thingy_id delete from parent_thingy p where p.common_thingy_id = @common_thingy_id I've translated the SQL to HQL as follows: update ChildThingy c set c.ParentThi...

Apply native SQL where clause to Nhibernate query for entity

I have this problem. I have a module (module 1) that use Nhibernate to manage entity persistence; this module interacs with an other module (module 2). The "module 2" allows to generate dynamically native SQL where clause. Now I would use it to manage filter operation in "module 1". Which is the bast way to do it? Is possible get the...

Hibernate/HQL - how to get all duplicates returned from database?

I've got a table containing some duplicates (defined as some specific columns contain the same values). What's the best way to get all those rose back? I need all the duplictes, so group by in combination with having count() > 1* is not the way I'd like to go. So if my table contains the following data 1 - foo - bar - something 2 - foo...

NHibernate Joining Data in HQL

I have the following two tables mapped in NHibernate: VehicleCheck ID (int) VehicleReg (varchar) DriverName (varchar) TS (datetime) VehicleCheckItem ID (int) VehicleCheckID (int) CheckType (int) Passed (bit) What I want to do is get the most recent vehicleCheck for each vehicleReg and a count of vehicle checks failed for each,...

How to query: Select games in categories A, B and C

I'd like to know how to effectively run a query such as: select game from Game game inner join game.Categories cat where cat.Name in ('A', 'B') This gives me games with categories A or B. But I want games that the the A category and the B category. This is to be used in HQL (NHibernate Query Language), but I'd like also to know how t...

using HQL Console in Intellij IDEA 9x: "Session is not created"

I'm trying to use the HQL Console feature of Intellij IDEA for interactively debugging HQL. We use JPA with Hibernate as the provider. I've configured the JPA Facet for the module as per this, as well as a DataSource. The HQL console seems to know about my entities, as it can auto-complete them in the console. But whenever I try a q...

Hibernate HQL with interfaces

According to this section of the Hibernate documentation I should be able to query any java class in HQL http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-polymorphism Unfortunately when I run this query... "from Transaction trans where trans.envelopeId=:envelopeId" I get the message "Transaction is n...

Query syntax error when bulk-deleting in Hibernate.

I try to executer bulk delete using Hibernate HQL query, following reference manunal but I get a query syntax error. This line final Query qry = getSession(false).createQuery(" delete from NewCalendarDay"); causes exception: org.hibernate.QueryException: query must begin with SELECT or FROM: delete [ delete from pl.com.bms.avaro.s...