Questions tagged [hql]

1

votes
2

answer
72

Views

Grails GORM - return count of objects who's array property > 0

Consider the following; class Person { int id String name static hasMany = [cars : Car] } class Car { int id String brand static belongsTo = Person static hasMany = [owners: Person] } The above will result in a person_cars join table. All I'm trying to find out is if there are any entries in that ta...
dre
1

votes
0

answer
574

Views

spring boot jpa trying to model an existing database

I have a database that I'm trying to model. There are 3 tables, table cycles with a one to many to table cyclesensors and a one to many to table sensors. I'm trying to get the latest cycle data and this is the query that I found to work: Select c.id, cs.cycles, s.* from BizWareUser.Sensor s Inner...
Aaron Pettitt
1

votes
2

answer
298

Views

HQL query to complex DTO

I have an issue with mapping HQL query to complex DTO. By complex DTO I mean DTO that composites another DTOs / collection DTOs. I tried to find solution but didn't find anything that can suit my requirements. For instance there is a DTO (I omit properties for simplicity): public class Consignment {...
aime
1

votes
0

answer
143

Views

Hibernate: how to update element order in a list (order generated by @orderColumn) without fetching the list

I have entity with list of sub entities, and I want to keep the order of the sub entities so I used the annotation @OrderColumn. @OrderColumn(name = 'pos') private List children; now I wish to change position of one child in the list, the way to do that is to fetch the list of child and then change...
Brachi
1

votes
2

answer
65

Views

I can't understand HQL to SQL translation

Why my simple List products = session.createQuery('FROM Shopping').list(); gives following error: java.sql.SQLException: Unknown column 'shopping0_.product' in 'field list' Here is mapping file Shopping.hbm.xml This class contains the employee detail. The table is: +----------+-------------+------+...
superstate
1

votes
1

answer
391

Views

how to convert column's enum type to integer in hql query?

I have a entity be contain column enum Type. definition column enum type in entity in the following: @Column(name = 'gender', columnDefinition = 'number(3)', nullable = false) @Enumerated(EnumType.ORDINAL) private Gender gender; I create a hql query in following : SELECT customer.nationalCode AS nat...
O.R
1

votes
0

answer
27

Views

JPA query get last message for each interlocutor

I have table messages with id, sender_id, receiver_id, sent_time, message, how it's possible to select last message for each interlocutor if I have user_id, which can be sender and/or receiver? I've found solution for usual MySQL query, but that will not work with JPA query/CriteriaQuery. In MySQL q...
Vadym Borys
1

votes
1

answer
69

Views

Simplify the existing Hibernate Query Language (HQL)

I have a hibernate query that I need to simplify as it has many joins. And I feel like the joins are unnecessary in some cases. I was trying to understand and simplify the query to get the same result after execution. However, I am not able to do so. Here is the query: Select a from Animal a left j...
Yogesh Ghimire
0

votes
0

answer
6

Views

How to create hql query for select and group by in Hibernate

I want to crate hql Qury for return result with select and group by statement know I write Sql qury in postgreDB, and I Can return my Result but I can't return this Result with hql in my Service Class I hope you can help me , Thanks with below Sql query in postgre I can return my result select trs....
fardad
1

votes
2

answer
137

Views

HIVE Group By in Multiple Tables

Two tables - 'salaries' and 'master1' The salaries is by year, and I can group them to get the sums for each player using SELECT playerID, sum(salary) as sal FROM salaries GROUP BY playerID ORDER BY sal DESC LIMIT 10; This returns the playerID and sum of salary, but I need the names of the playe...
Zakary Krumlinde
1

votes
2

answer
43

Views

Is there a way to write this SQL in HQL?

I need to run this query in MySQL using Hibernate (HQL): SELECT t.atrib2, MY_FUNC(attrib1), MY_FUNC(attrib2), o.atrib1 FROM (SELECT t.atrib1, t.atrib2, t.atrib3,o.atrib1 FROM myTable t JOIN otherTable o ON O.ID = T.O_ID ORDER BY creationDate LIMIT 0, 100000) t; I can not use a SELECT like this: S...
Jared
1

votes
1

answer
147

Views

In HQL select registerFunction using Custom SQLdialect is not working

I have following HQL Query StringBuilder selectBuilder = new StringBuilder( 'select new support.dto.SrsRequirementStatus( s.id, sm.id, sm.name, ' + 'sr.id, sr.requirement, sr.reference, ' + 'sca.code_id, sca.code_name, spa.code_id, spa.code_name, ssa.code_id, ssa.code_name, ' + 'function('cr_min_q...
Shantaram Tupe
1

votes
1

answer
225

Views

Multiple join Conditions in Hibernate HQL, while mapping the data instead of in where clause

I am trying to do something like this Native SQL : Select * from FirstTable ft right join SecondTable st on (st.ID=ft.ID and ft.fStatus !='b') where ft.fStatus!='b' In short I just don't want to map FirstTable data with SecondTable when there is something like this [ft.fStatus !='b']. But when thi...
Ankush Sankhyan
1

votes
1

answer
256

Views

HQL: group by hour(time) and sum(pcount)

I am using this query to give me sum count which is grouped by hour @Query(value = 'select sum(PC.count) as pcounts, hour(PC.time) as hourly from ProductCount as PC where PC.time >= :currentDate and PC.time < :nextDate group by hour(PC.time)') List findByCountAndGroupByTime(@Param('currentDate') Tim...
nirvair
1

votes
0

answer
56

Views

How do I count total distinct items in a one-to-many join query in Hibernate JPQL?

I'm having trouble getting the total count of the 'one' side of a one-to-many join query in hibernate HQL / JPQL. The two tables I'm attempting to join (which I don't control and cannot change) look something like this: House: PK FK: owner_id PK: town PK: state PK FK: inspection_id Car: PK FK: owner...
SnoopDougg
1

votes
2

answer
312

Views

Return another entity or DTO inside of a DTO

So basically I am writing an API that uses a DTO, but I am having trouble returning another entity inside of the DTO. Here is my DTO: public class DirectoryDTO { String personFirstName; String personLastName; Hrper hrper; public DirectoryDTO(String personFirstName, String personLastName, Hrper hrper...
Dan
1

votes
0

answer
76

Views

HQL statement works with Persistence Query, not with Hibernate @Formula

Inside a Hibernate entity, i try to retrieve a Set of entities with @Formula @Formula(value = 'SELECT entityA from EntityA entityA ' + 'INNER JOIN entityA.id listValue WHERE listValue.id ' + 'in ( select entityB.listValueId from EntityB entityB where entityB.companyId ' + 'in ( select entityC.id...
Stephan
1

votes
0

answer
62

Views

What is this double hyphen and how can it be used in HQL

While writing a mysql query we can use either # or a double hyphen (--) to comment a line. So we tried the same in hql but, this link here says that HQL does not support comments. Well when we tried to write a query in hql-editor we tried it, so the line followed was having different color like this...
Arun Sudhakaran
1

votes
1

answer
55

Views

Hibernate Transformers.aliasToBean populate primary fields

I am trying to get list of Tbcompany table using Transformers.aliasToBean with 2 primary key fields. I am using SQL SERVER and Hibernate 3.2.4. My table has 2 primary fields. Tbcompany.class public class Tbcompany { private TbcompanyId id; private String hcompanycode; public TbcompanyId getId() { re...
Dekso
1

votes
0

answer
47

Views

HQL query multiple record result from list of multiple pair values

I am having a difficult time finding a way to request a list of records, where the criteria is a list of pairs of values. Here is the general structure of my table. | ID | PROP1 | PROP2 | PROP3 | ------------------------------------- | 1 | AAA | 11 | VAL1 | --------------------...
Dave Shuck
1

votes
1

answer
22

Views

HQL SELECT multiple tables directly into class

So I'm trying to get all columns of 2 tables into my custom created class without looping into the array. But I cant make it work. Here is my classes: SampleForm: public class SampleForm { List audittrail; List auditevents; public SampleForm(List audittrail, List auditevents) { super(); this.audittr...
Dekso
1

votes
1

answer
24

Views

HQL subqueries in hibernate

I have to write hql for one of the subquery. I am getting querysyntax exception. Below is my code. public List findTempSensorObjs(String systemId, Character isLatest) { Map params = new HashMap(); List tSensorList = new ArrayList(); params.put('systemId', systemId); params.put('status', isLatest); S...
user2707232
1

votes
2

answer
271

Views

HQL : select sum group by join

In HQL I would make a query like this : I want to select the category name and the sum montantdebit by a category category{ idcategory ;namecategory} and operation {montantdebit , Category} I have tried the following: select cat.nameCategory, sum(operation.montantdebit) from Operation operation...
EL Hassane
1

votes
0

answer
84

Views

Hive Error- while copying data from one DB table to another DB table

I want to copy data from one DB table to another DB table using hive on EMR. Below is the HQL using which I'm copying data along with the date partition. insert into Target.exttbl_user_identification_details PARTITION(load_date='2018-04-23') select * from Source.exttbl_user_identification_details;...
Ganesh
1

votes
0

answer
45

Views

Hibernate multimapping - map tables to one entity

I have a spring boot application and use hibernate to load entities from PostgreSQL DB. I have a lot of tables with the same structure (columns: TIMESTAMP timestamp, DOUBLE ask, DOUBLE ask_vol, DOUBLE bid, DOUBLE bid_vol). For all this tables I use one entity Pair: @AllArgsConstructor @Data @Entity...
Rolaman
1

votes
2

answer
206

Views

Hibernate OGM with MongoDB could not resolve property

This HQL query give me error: String q4 = 'SELECT i, COUNT(ie) FROM CorsoStudi cs \n' + '\t JOIN cs.inserogati ie JOIN ie.insegn i \n' + '\t WHERE cs.nome = 'Laurea in Informatica' \n' + '\t GROUP BY i'; The error is: Exception in thread 'main' java.lang.IllegalArgumentException: org.hibern...
Emanuele Crema
1

votes
0

answer
39

Views

HQL query with 2 column Sub Join

I have a table price: | id | price | product_id | date | other_columns... | I have the following query to return the latest price row for each product_id: select p1.* from price p1 right join ( select p2.product_id, p2.id, max(p2.date) from price p2 GROUP BY p2.product_id ) p2 ON p2.id = p1.id; When...
Edd
1

votes
2

answer
225

Views

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: User is not mapped [from User]

I'm getting java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: User is not mapped [from User] error. I've gone through these question, but didn't helped me. org.hibernate.hql.internal.ast.QuerySyntaxException: table is not mapped Hibernate error - QuerySyntaxEx...
rpm shuvo
1

votes
2

answer
123

Views

How to filter table based on percentile and then random sample in HQL?

I'm trying to random sample 200 rows from a table, but first I want to filter it to pick only top 1 percent values from a variable. I'm getting the following error - Error while compiling statement: FAILED: ParseException line 3:31 cannot recognize input near 'select' 'percentile_approx' '(' in expr...
Parth Shiras
1

votes
1

answer
37

Views

Unable to resolve $proc_date from the script

I have multiple HQL's, below is the one example. located at : /home/ganesh/CopyJobs/hql/ insert into XYZ.exttbl_form_data PARTITION (load_date='$proc_date') select FORM_DATA_ID,FORM_ID,USER_ID,INTERACTIONS_ID,SUBMISSION_DATETIME,FILEDS from PQR.exttbl_form_data where load_date='$proc_date' In the m...
Ganesh
1

votes
1

answer
65

Views

Fetching Data through Hibernate without passing Primary Key

I am using a composite primary key which is defined in Entity Class as : @EmbeddedId private ParticipantPrimaryKey pKey; and the primary Key consists of Event Id and Student Id which are defined in PrimaryKey Entity Class. Now i need to fetch the participants from the table which are participating i...
Kishan Mokaria
1

votes
0

answer
21

Views

Convert SQL query to find ratio two sub queries into HQL

I am trying to convert following query to HQL: select x / total*100 from (select count( * ) as x from events e, publish_events p where e.event_id = p.event_id and e.amount 0 and e.creation_date > sysdate-2 and p.publish_status = 'xxxxxx' and p.publish_requester = 'xxxxx'), (select count(*) total fr...
Rohit Kadyan
1

votes
1

answer
176

Views

how to join two tables and get all matched record in hibernate(using entity class mapping)

I have two entities called FeeTerms.java and FeeTermDates.java I want to get all matched records from these two entities using pure HQL Look at entities: FeeTerms.java package com.rasvek.cg.entity; // Generated May 14, 2018 11:39:07 PM by Hibernate Tools 5.1.7.Final import java.util.HashSet; import...
bharath
1

votes
1

answer
310

Views

Grails Unit Test fails: String-based queries like [executeQuery] are currently not supported in this implementation of GORM

There are other questions on SO about this, but they are pretty old and none offer solutions to my issue. I'm getting the following error from a unit test: java.lang.UnsupportedOperationException: String-based queries like [executeQuery] are currently not supported in this implementation of GORM. Us...
beechovsky
1

votes
2

answer
285

Views

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token (asf is alias for a Table)

i have written hibernate query to get details from multiple tables as as follows Query query = currentSession.createQuery('select s.studentAdmissionId as studentAdmissionId, pd.rollNum as rollNum ,s.firstname as firstName , s.lastname as lastName , m.className as className,' + 'sec.sectionName...
bharath
1

votes
1

answer
29

Views

How to use inner join in hql using subqueries

I am new to HQL. I have a table like Product Table I have a query like Query query = getEntityManager().createQuery( 'Select f from Product f where f.Type=1 and f.EmpId='' + empId + '' and f.Status IN ('+allowedStatus+')'); empId=Abc and allowedStatus = (1,2) After hitting this query I am getting a...
anupama kushwah
1

votes
0

answer
55

Views

HQL, use @ in alias

I'm trying to run this query SELECT userType from Users as @type But hibernate throws a syntax error. org.hibernate.QueryException: unexpected char: '@' I tried to use quotes but it didnt work as well. Can this be done?
Fabio K
1

votes
0

answer
30

Views

table with column idEntity fails query

I have a table with a column named 'idEntity', if i run this query: select idEntity from algo.AUDITORIA ; in oracle, there's no problem and the query runs, but in SqlServer I'm getting: Incorrect syntax near the keyword 'from'. it seams that IDENTITY is a reserved word, and instead of refereng to t...
Alfredo M
1

votes
1

answer
223

Views

hql query to retrieve data that has max date value

I'm using Spring Data JPA and I need to retrieve a set of data. Entities I'm working with: NationalPart @Id @GeneratedValue(strategy=GenerationType.IDENTITY) private Long idNationalPart; private Date nationalPartStartDate; @ManyToOne @JoinColumn(name='idCategory') private Category nationalCategory;...
smallhamster
1

votes
2

answer
299

Views

How to update with inner join in HQL

How to update with inner join in HQL ? My query in SQL like this update u set u.name = t.name from User u inner join Temp t on t.id = u.id and I try in HQL like @Query(' update User u set u.name = ..?.. where u.id in (select id from Temp) ') How can I get name in Temp to set in this query? thank you...
methodplayer

View additional questions