Stack Overflow
Stack Overflow

Questions tagged [jpql]

The Java Persistence Query Language (JPQL) is a platform-independent object-oriented query language defined as part of the Java Persistence API specification.

The Java Persistence query language (JPQL) is used to define searches against persistent entities independent of the mechanism used to store those entities. As such, JPQL is "portable", and not constrained to any particular data store. The Java Persistence query language is an extension of the Enterprise JavaBeans query language (EJB QL), adding operations such as bulk deletes and updates, join operations, aggregates, projections and subqueries.

3498 questions
157
votes
4 answers

Adding IN clause List to a JPA Query

I have built a NamedQuery that looks like this: @NamedQuery(name = "EventLog.viewDatesInclude", query = "SELECT el FROM EventLog el WHERE el.timeMark >= :dateFrom AND " + "el.timeMark <= :dateTo AND " + "el.name IN…
AlanObject
  • 9,613
  • 19
  • 86
  • 142
133
votes
3 answers

JPQL IN clause: Java-Arrays (or Lists, Sets...)?

I would like to load all objects that have a textual tag set to any of a small but arbitrary number of values from our database. The logical way to go about this in SQL would be to build an "IN" clause. JPQL allows for IN, but it seems to require me…
Bernd Haug
  • 2,157
  • 3
  • 17
  • 21
123
votes
10 answers

What is the LIMIT clause alternative in JPQL?

I'm working with PostgreSQL query implementing in JPQL. This is a sample native psql query which works fine, SELECT * FROM students ORDER BY id DESC LIMIT 1; The same query in JPQL doesnt work, @Query("SELECT s FROM Students s ORDER BY s.id DESC…
Madhu
  • 2,643
  • 6
  • 19
  • 34
110
votes
2 answers

Problems with making a query when using Enum in entity

I have the following in a Question entity: @NamedQuery(name = "Question.allApproved", query = "SELECT q FROM Question q WHERE q.status = 'APPROVED'") and @Enumerated(EnumType.STRING) private Status status; // usual accessors I am getting this…
LuckyLuke
  • 47,771
  • 85
  • 270
  • 434
105
votes
9 answers

Parameter in like clause JPQL

I am trying to write a JPQL query with a like clause: LIKE '%:code%' I would like to have code=4 and find 455 554 646 ... I cannot pass :code = '%value%' namedQuery.setParameter("%" + this.value + "%"); because in another place I need :value not…
Manuele Piastra
95
votes
9 answers

Spring Data JPA and Exists query

I'm using Spring Data JPA (with Hibernate as my JPA provider) and want to define an exists method with a HQL query attached: public interface MyEntityRepository extends CrudRepository { @Query("select count(e) from MyEntity e…
Stefan Haberl
  • 9,812
  • 7
  • 72
  • 81
86
votes
5 answers

IN-clause in HQL or Java Persistence Query Language

I have the following parametrised JPA, or Hibernate, query: SELECT entity FROM Entity entity WHERE name IN (?) I want to pass the parameter as an ArrayList, is this possible? Hibernate current tells me, that java.lang.ClassCastException:…
Daniel
  • 27,718
  • 20
  • 89
  • 133
78
votes
7 answers

Spring Data optional parameter in query method

I want to write some query methods in repository layer. This method must ignore null parameters. For example: List findByBarAndGoo(Bar barParam, @optional Goo gooParam); This method must be return Foo by this condition: bar == barParam && goo…
mohammad_1m2
  • 1,571
  • 5
  • 19
  • 38
75
votes
16 answers

org.hibernate.NonUniqueResultException: query did not return a unique result: 2?

I have below code in my DAO: String sql = "SELECT COUNT(*) FROM CustomerData " + "WHERE custId = :custId AND deptId = :deptId"; Query query = session.createQuery(sql); query.setParameter("custId", custId); query.setParameter("deptId",…
user3198603
  • 5,528
  • 13
  • 65
  • 125
73
votes
3 answers

JPQL Create new Object In Select Statement - avoid or embrace?

I've learnt recently that it is possible to create new Objects in JPQL statements as follows: select new Family(mother, mate, offspr) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr Is this something…
mgamer
  • 13,580
  • 25
  • 87
  • 145
72
votes
2 answers

What to use: JPQL or Criteria API?

My Java application is using JPA for object persistence. The business domain is very simple (just three classes are persistent, with 3-5 properties in each). Queries are simple as well. The question is which approach I should use: JPQL or Criteria…
yegor256
  • 102,010
  • 123
  • 446
  • 597
67
votes
6 answers

JPQL Like Case Insensitive

I want to search data in User table by name case insensitive. @Repository public interface UserRepository extends JpaRepository { @Query("select u from User u where lower(u.name) like %lower(?1)%") public List…
windupurnomo
  • 1,121
  • 1
  • 11
  • 22
59
votes
1 answer

How To Define a JPA Repository Query with a Join

I would like to make a Join query using Jpa repository with annotation @Query. I have two tables: table user with iduser,user_name and: table area with idarea, area_name and iduser The native query is: SELECT u.user_name FROM user as…
Shinigami
  • 1,015
  • 2
  • 15
  • 23
58
votes
2 answers

What's the difference between the IN and MEMBER OF JPQL operators?

What's the difference between the IN and MEMBER OF JPQL operators?
Julio Faerman
  • 13,228
  • 9
  • 57
  • 75
58
votes
3 answers

Limit number of results in JPQL

How it is possible to limit the number of results retrieved from a database? select e from Entity e /* I need only 10 results for instance */
ryskajakub
  • 6,351
  • 8
  • 45
  • 75
1
2 3
…
99 100