5,063 questions
0
votes
2
answers
113
views
How to pick the latest record with GROUP BY userId
In my application I want to find the latest duty of each user from 'StaffDuty' table using hibernate query (i.e. HQL). Below is my query.
query = session.createQuery("FROM StaffDuty where deptId....
- 2,073
0
votes
1
answer
26
views
Is there a HQL condition where one object of a set is in a many-to-many relationship in 2025?
I have a Task with a @ManyToMany referenced roles, which specify roles a user must have at least one of, to perform the task. The HQL search should therefore find all tasks that have at least one of ...
- 10.2k
0
votes
0
answers
14
views
HQL query returns NULL after session.save(object) and session.flush()
I am creating an enablePackage API code flow in JAVA. I am using the Spring MVC project.
I am facing an issue related to HQL query returning null.
The code logic is as follows:
Foo.class
List<...
3
votes
1
answer
231
views
Silently ignoring "Could not locate named parameter" for param in subquery and pagination
I'm running a JPA non-native query, which returns a custom object.
@Query("""
SELECT
new path.to.CustomObject(
t,
(select id from users where name = :param1)
...
- 1,574
0
votes
1
answer
219
views
Hibernate 6.x - How to register a PostgreSQL function returning a table for use in JPQL?
i'm trying to register a PostgreSQL custom function in Hibernate 6.x so that I can use it directly in JPQL, without relying on native queries.
Scenario
I have a PostgreSQL SQL function that returns a ...
0
votes
0
answers
45
views
NHibernate MismatchedTokenException
I have an HQL query that, every time I run it, throws Antlr.Runtime.MismatchedTokenException: 'A recognition error occurred.'. So far as I can tell, this is because I'm trying to use an entity ...
- 874
1
vote
1
answer
38
views
HQL: cast to SIGNED is not known symbol
I need to convert this query to HQL syntax:
SELECT * FROM my_table
GROUP BY `name`
HAVING MIN(ABS(CAST(202000 AS SIGNED) - CAST(step_id AS SIGNED))) = ABS(CAST(202000 AS
SIGNED) - CAST(step_id AS ...
- 1,098
0
votes
1
answer
170
views
HQL Hibernate 6 ORM Query class type on delete statement
I am upgrading an spring boot app (spring-boot-starter-parent) from 2.6.1 to 3.4.1. part of that upgrade is related to the upgrade of hibernate 5 to 6 and specifically hibernate core 6.6.4.Final with ...
- 2,310
0
votes
0
answers
39
views
How to determine if there is an intersection between two HQL wherestatement by code
I have two queries targeting the same table, and I want to determine if the filtering ranges of these two queries intersect, that is, whether the two queries will filter the same data.Even if the ...
3
votes
1
answer
57
views
HQL generate wrong cast
Having a cast like this:
SELECT
CAST(a * 100000000 as big_integer)
FROM
Amount
WHERE
id = :id
Generate this SQL:
prepareStatement(
"select cast(amount0_.\"a\"*100000000 ...
- 2,163
0
votes
0
answers
63
views
HQL ORDER BY query with adding days to date column
I use spring boot, and I want to add specific days to date column in mysql database on hql query order by clause. But which results error when I tried. I need to get results in the order of adding ...
- 2,073
0
votes
0
answers
30
views
Select entity and ids of its surrounding entities
This is my query
It is meant to return the selectedResponse and its next and previous ids. As you can see, the Where clause currently constrains the results. I tried a nested select and CTE, both ...
2
votes
1
answer
33
views
How to get a single entry per non-unique column value?
I have tables in DB and matching entities. I have a table 'products'. Each entry in 'products' has unique 'id' column and 'code', which is not unique, multiple different 'products' may have the same '...
- 123
0
votes
0
answers
30
views
Using a Constructor in HQL gives MappingException when using it in repository interface but works fine when using it in service class [duplicate]
According to chapter Using a Constructor in HQL in https://www.baeldung.com/hibernate-query-to-custom-class I should be able to use my DTO constructor in @Query select statement.
However when I try to ...
- 367
0
votes
1
answer
99
views
How to check a property in any of multiple column when column value not null
Below are some of the tables in my product app where I need to execute a case statement as per the data values.
class Payment implements Serializable {
@Id
@GeneratedValue(strategy = ...
- 2,073
1
vote
1
answer
111
views
How to find sum of multiple columns in where clause
In my application I have an accounting table with columns amount, paidAmount, fine. Here I need to find accounts with balance zero by adding these 3 column and matching it with zero.
Below is my hql ...
- 2,073
0
votes
1
answer
693
views
Visual Studio Code - use SQL formatter to format HQL files
I would like a good formatter for HQL files. I found one extension dedicated for Hive & Spark but I don't like the way it formats my queries. I prefer the way the SQL Formatter works. Is there a ...
- 79
0
votes
0
answers
41
views
I can't query media by id (pk) only for name and alternative titles
I am trying to write a query to filter media by category, id, name and alternative titles. When I pass category and name or alternative title the query work normally, but when I pass only category and ...
- 133
1
vote
1
answer
89
views
NHibernate - Problem with creating a custom Linq extension
I have such query in NH Linq:
var query = _session
.Query<Revision>()
.Join(
_session.Query<Document>(),
rev => rev.Document,
doc => doc,
(rev,...
- 121
0
votes
1
answer
127
views
Bitwise operation in HQL
I am using Hibernate 6, and I have created a custom dialect.
Below is my CustomPostgreSQLDialect.java:
public class CustomPostgreSQLDialect extends Dialect {
@Override
public void ...
- 1
0
votes
1
answer
42
views
Replace keyword in hql query and slowness
After adding replace operation to my hql query it became slower. How can I make it faster?
Products are transferred to my database with cronjob. My goal is to make a special change only to the name ...
0
votes
1
answer
81
views
Gini coefficient in hive by group
I have a table with three columns group, person, score I would like to calculate the Gini coefficient for score per group. So that I can measure the inequality of persons based on score What is the ...
- 495
0
votes
1
answer
37
views
SQL query for selecting a single role in PostgeSQL query with Pageable object
I have this Oracle query implemented as HQL and Spring Boot query which I want to migrate to PostgreSQL
@Query("select profileId from HrDepartment where profileId = :id and profileId is not ...
- 938
1
vote
1
answer
1k
views
Hibernate 6 casts query parameters
Hi there got the following repository:
@Repository
public interface FooRepository extends JpaRepository<Foo, Long>, JpaSpecificationExecutor<Foo> {
@Query(
"""
...
- 2,017
0
votes
1
answer
71
views
Migrate Oracle SQL query with ROWNUM into PostgreSQL HQL query
I have the following Oracle SQL query:
SELECT user FROM global_users user WHERE user.status = 'ACTIVE'
AND user.description IS NOT NULL AND user.updatedGoodsDate BETWEEN '2024-03-10 20:09:53' AND '...
- 938
0
votes
0
answers
18
views
HQL unexpected end of subtree
I need to write a hql query to get all objects where a field is contains in a list of Integer.
But I have an error: unextected end of subtree
this is the request:
@Query(
value = "SELECT ...
0
votes
0
answers
326
views
How to read and execute hql file(hive query) and create pyspark dataframe
I have a .hql file. I need to read and execute the query to create a dataframe from the query result.
I have the below code
def read_and_exec_hql(hql_file_path):
with open(hql_file_path, 'r') as f:...
- 434
2
votes
0
answers
671
views
CTE usage with Spring Data JPA 3.2 + Hibernate 6.4 Does not work
I am using current latest version of Spring Data Jpa. As per the release notes and PRs Spring Data JPA should support CTE / Recursive CTE . Following are the links for confirmation :
https://github....
- 605
0
votes
1
answer
282
views
Updating from hibernate 5.5.x to Hibernate 6.1.7 generated the incorrect discriminator query
My model is as follows:
@Entity
@Table(name = "PARTY")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "CLASS", discriminatorType = ...
- 149
5
votes
1
answer
3k
views
Query execution errors after upgrading spring boot 3.2.0 to 3.2.1 - ORA-00933 - command not properly ended
I have a Java Spring application with lots of queries, which so far have worked until spring boot 3.2.0.
Recently, after simply changing to spring boot 3.2.1, I started getting this kind of error:
org....
- 161
3
votes
1
answer
2k
views
Query execution errors after upgrading spring boot 3.2.0 to 3.2.1
i have a Java Spring application with lots of queries, which so far have worked until spring boot 3.2.0.
Recently, after simply changing to spring boot 3.2.1, I started getting this kind of error:
org....
- 161
0
votes
2
answers
42
views
Extracting multiple result with child tables in PostgreSQL and HQL
In the following scenario, there are 3 tables in a PostgreSQL database (tables and values are simplified for clarity). I am trying to get the following values:
sum of the amounts for records (101, ...
- 12.2k
1
vote
1
answer
52
views
SQL statement to log unupdated element from IN clause
I am updating a table with SQL IN clause with multiple parameters. I would like to know the parameter which could not update. Is there any way I get that info from SQL update statement?
I have a PUT ...
- 343
0
votes
1
answer
45
views
Getting date with max sales for a month
I have a table in Hadoop that I can count the number of sales for a specific month
select count(*) from sales where sold_date between '2023-08-01' and '2023-08-31'
How can I update this query to get ...
- 4,529
1
vote
0
answers
33
views
Why hibernate doesn't use my own join with entityGraph?
I want hibernate to use my own join (left outer join t.route Operations routeOperation) in its internal query. And now it turns out that in order for me to remove the n+1 problem, I need to add ...
- 33
0
votes
1
answer
94
views
I have some questions about HQL syntax
guys. I'm a novice learning Hibernate.Recently,I learned some konwledge about Hibernate query language. When learned FROM systnx,I want to konw 'SELECT A FROM A' is same with 'FROM A'?
I ask for GTP,...
- 3
0
votes
1
answer
44
views
Improve the performance of this windowing-heavy query
I have the below code in HQL. It's drawing data from a table with >2.5bn rows and around 334 columns. It takes a while to run so I'm looking for any potential performance boosts where possible. ...
- 105
0
votes
0
answers
44
views
EntityManager is null if not created Manually
I work on a maven project which is deployed as a runnable jar on a server.
Unfortunately my EntityManager stays null if I not assign it manually.
Persistence.xml
<persistence-unit name="...
- 139
0
votes
1
answer
39
views
JdbcSQLSyntaxErrorException function DATE_ADD not found
I tried to use the MySQL function DATE_ADD in a nativeQuery
UPDATE my_table SET my_date = DATE_ADD(my_date, INTERVAL 1 HOUR)
but I got the error:
JdbcSQLSyntaxErrorException function DATE_ADD not ...
- 2,470
0
votes
1
answer
57
views
Hive query to find a month for each year in which maximum number of quantities were sold
I have a data like below, from which i am trying to find out which month for each year has max quantity ordered
if the data is not showing clearly i have added a snip of it
QUANTITYORDERED QTR_ID ...
- 21
0
votes
1
answer
86
views
Round robin with record limitation
I have to build a hive SQL query for the following requirement
I have an customer table. I need to divide the total table record by 6 (i.e suppose table contain 600 records each month 100 record upto ...
0
votes
1
answer
2k
views
JPA/Hibernate - Using Postgre's "ANY" Function in HQL
Context: Spring Boot Starter JPA Version 3.0.2, Hibernate Core 6.2.7.Final
Consider the following SQL tables and corresponding Hibernate entities:
CREATE TABLE person (
id INT PRIMARY KEY,
name ...
- 429
0
votes
1
answer
1k
views
Returning list of strings in JPA @Query using projection
I have an entity Dog and then an entity of Event that the dogs participate in. I want to extract a list(string) of each Event that has a corresponding DogId. I am using JPA projection because I need a ...
- 3
0
votes
1
answer
581
views
Combine two queries into one
In the first query, I need to have the count for each type regardless of whether that’s read or not (unread can be true or false). In the second query, I need the count for all types only for when ...
- 669
0
votes
0
answers
169
views
JPA/Hibernate: how to re-use JPQL query
I have a somewhat complicated @NamedQuery that queries the following entity:
@Entity
class Employee {
@Id
String id;
LocalDate dateOfBirth;
String companyId;
}
It includes several joins and ...
- 997
2
votes
1
answer
190
views
generate_series function in Hibernate
I have a column total in the table Inv and I want to extract total amount per month in hibernate using generate_series:
@Query(value = "SELECT _month," +
"sum(inv.total) AS ...
- 582
0
votes
1
answer
129
views
How to write HQL using LEFT OUTER JOINs on multiple hasMany associations that doesn't expect all JOINs to be non-empty
In my Grails application I had defined a domain object called Order (mapped to a work_order table since order is a reserved word in MySQL) which has a one-to-many relationship with another domain ...
- 236
0
votes
0
answers
266
views
how to fetch 100k data with in a second in spring boot in this query
it takes too much time to fetch data.
after half an hour session out msg.
I want 100k data to fetch through ajax.
data increases day by day. after 1 year data across 400k then can not fetch data.
...
- 11
0
votes
1
answer
44
views
Prevent extra queries after performing join fetch when referencedColumn not matching the joinColumn
I have these entities where some values of ref_b may not match any row in table b.
class A {
@Id
@Column(name = id)
Long id;
@Column(name = "column_b")
private String ref_b;
...
- 1
0
votes
2
answers
35
views
How to know if a variable had a different mapping in a different time set
My question focuses on finding out if a variable was mapped differently in a previous time period.
I want to see if last month the place names could have switched (Example: Kansas was coming in for ...
