I have the following query: However, this throws a syntax error on the query when I put a case statement inside an aggregate. I can get SUM to work fine alone, and case statements to work fine alone. I’m wondering whether JPQL can support cases in aggregates or if I’m forced to use a native query.…
Tag: jpql
SQL code does not work when converted to JPQL code (ORA-00904 error code)
Thank you very much for looking at this post :). I have the following problem: I have a piece of SQL code that runs perfectly on the Oracle database. The problem comes when I try to run as JPQL code. I get the following error in the Eclipse IDE: Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904:…
Spring Data JPA – Custom Native Query-Methods list prints java.lang.Object
I have created a native custom query method: And calling EntityManager with: But all I get is: when I print out the list. Number of objects is correct so I assume something with casting is not correct. I do get a warning in my IDE: Why is this happening and how do I get the correct list? Answer You can
Need Help Converting a SQL Query to JPQL
So far, Spring’s JPA repositories have sufficed for me, but now I need a custom query. I have struggled with this for a day trying to learn more about SQL, joins, etc and came up with a native MySQL …
Hibernate Null Attribute Value using Camel-Case
Using Hibernate 5.3, Spring Boot 2.1, MySQL, that runs in Windows 10 OS. What I know I have verified on my phpmyadmin the case of the attributes. And as long the case were the same as the attributes of my Entity class you don’t have to explicitly define the column name in that Entity. And using @Column(…
paging and sorting with Jpa returns only around 10% records from db
When I try to retrieve data from JpaRepository with Pageable request it only return 10% from all records. as example I had 68 records in MySQL database and pageble request only return first 10 records. when i try to get 2nd resultset it returns empty page content object. but if I request 100 records in one re…
My custom order by in criteria query/jpql
Is it possible to do my custom order by using criteria query or jpql in Java? In my table, I have a column – number type(for now, this column includes numbers such as 1, 2, 3, 5, 10). I would like to sort result in this order 2, 5, 1, 10, 3. How can I do it? Answer You have
JPA distinct inner join with Condition on joined table
I have two tables below: I want to get the college object with list of students of a specific department. The SQL query is below: So far I have tried the below JPA query but it is returning multiple College objects. How to return a single college object with list of students with filtered department? NOTE: I …
How should I pass values into JPQL query?
I’m working on test, that will check if object had been saved into database. Test will save object with unique values and after that I want to fetch this object from database and check if it is not …
Substring in select statement in JPQL
I’m trying to select a substring of column, i.e. select substring(description, 1, 200) from category where id=1 Is it possible to have a substring function within a select statement in JPQL/JPA 2? If yes, how? If no, are there any alternatives? Thanks. Answer There is a scalar expression for this: SUBST…