Questions tagged [hql]

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
1

votes
1

answer
19

Views

How to create query in HQL for one to many mapping

I have two entity as below public class Author{ @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = 'author') @Fetch(FetchMode.SUBSELECT) private Set books; } public class Book{ @ManyToOne @JoinColumn(name = 'AUTHOR_ID_REF', nullable = false) private Author author; private Stri...
Mujahid
1

votes
0

answer
146

Views

HQL Hibernate INNER JOIN SUBQuery

I have some experience in SQL, but my knowledge in HQL is limited Here is my SQL query select * from (select * from readings join (select max(signal) as signal,sender as mac ,receiver as rc from readings where created_datetime between '2018-02-05 10:25:52' AND '2018-04-05 10:25:52' group by...
Koushik Paul
1

votes
2

answer
150

Views

Translating HQL Query to executable SQL Query with list parameter

I am writing a util function to get the total record count based on any HQL that I get passed in without loading all data. The passed in HQL might be pretty complex with lots of selects, joins, where conditions, groupings and sortings. For that I want to wrap the query with a SELECT COUNT(*) FROM ()...
PMcL
1

votes
0

answer
39

Views

hive error with partition by

Here is my create table CREATE TABLE parquetpoc.employee USING PARQUET LOCATION '/mnt/adlsQA/parquetPOC/output/employee' PARTITIONED BY (`snapshot_year_month` string,`snapshot_day` string) In the PARTITIONED BY I have tried, quotes, single quotes, double quotes, no quotes, and this thing `. My folde...
test acc
1

votes
0

answer
32

Views

How to properly create a Spring JPA query using HQL?

If I run this query on my db it works fine: UPDATE Products SET in_Stock = (quantity_in_stock is not null and quantity_In_Stock > 0) WHERE id = 1 and deleted_At is NULL; But I'm using Spring JPA and trying to create this same query like this: @Modifying @Query('UPDATE Product p SET p.inStock = (p.qu...
Igor
1

votes
1

answer
53

Views

I want to aggregate the data depending on the updated_on timestamp in jpql but invalid keyword INTERVAL

I want to aggregate the data depending on the updated_on timestamp which should take aggregation over 2 hour interval in JPQL.The query works fine in postgres pgadmin. I have used the query: @Query('select DATE_TRUNC('day',srisk.updatedOn)+cast(DATE_PART('hour',srisk.updated_on) as Integer)/2*INTERV...
Swati Shinde
1

votes
2

answer
126

Views

Mapping projection form hibernate HQL query into object

I am returning only a few columns from a table in DB: List l = (List) session.createQuery('Select p.one, p.two FROM MyClass p WHERE p.id IN :id') .setParameter('userId', id) .list(); However, the query returns List of arrays, e.g l.get(0) // [0] is object representing p.one in query, [1] p.two Is th...
Darlyn
1

votes
1

answer
47

Views

Fetch relation of fetched relation in hibernate HQL query

I have entity Customer, Car: @Entity public class Customer{ List cars; ... } @Entity public class Car{ List previousOwners; ... } Basicly Customer can have multiple cars rented, and Cars remember previous owners. Both entities have more relations, and i am omiting them. Now want to retrieve all car...
Johnyb
1

votes
2

answer
61

Views

Hive Case Resulting Duplicate Row

I have a table containing contact numbers and another reference table containing a 'length' variable and a number column. What I need is to find the prefix name where the prefix of the number matches the one in the reference table, but it should be the one that goes with the longest prefix matching...
Gin
1

votes
0

answer
316

Views

Getting “error”: “<expression>,<operator>, GROUP, HAVING or ORDER expected got '('”

IntelliJ IDEA Ultimate is showing me an error: ,, GROUP, HAVING or ORDER expected got '(' right under a braket inside a HQL statement which apperently is not a problem for compilation or runtime. @Query('SELECT offer FROM OfferEntity offer ' + ' JOIN offer.placeOwnership AS owner ' + ' JOIN owne...
displayname
1

votes
1

answer
32

Views

Cannot query entities using JOIN

I am trying to get my head around HQL and run it inside the Persistence window of IntelliJ IDEA. Here's the thing: I can run simple queries like these: hql> SELECT offer FROM OfferEntit offer; hql> SELECT offer FROM OfferEntit offer WHERE offer.id = 1L; but if I add something like a JOIN: hql> SELE...
displayname
1

votes
0

answer
16

Views

HQL query select one set of columns vs select other columns when a particular column is null

What I want to accomplish is the following. Please let me know how I can combine these two cases into one HQL conditional query. If myID is not null then use this query @Query('select item from Item where a = ?1 and b=?2 and c=?3 and myID=?4) If If myID is null then use this query @Query('select it...
spring notes
1

votes
1

answer
44

Views

Inheritance in Hibernate. Inserting example

I want to make connection between Product class and Laptop Product class @Entity @Table(name = 'products') @Inheritance(strategy = InheritanceType.JOINED) public class Product { @Id @Column(unique = true, nullable = false) @GeneratedValue(strategy = GenerationType.AUTO) private int id; @Column(nulla...
Michael Andrushenko
1

votes
2

answer
43

Views

Hibernate HQL Syntax error in exist clause

I have this hql query . Query query = session.createQuery( 'select inv from Invoice inv ' + 'where 1=1 ' + 'and inv.edited = false ' + 'and businessSubscription.customer.user.id = :userId ' + 'and inv.customerInvoice is null ' + 'and inv.parentInvoice is null order by inv.id desc ' + 'and exists...
mah454

View additional questions