One of the solutions is to create a custom GROUP_CONCAT HQL function that is translated to SQL. Idea is to create function: group_concat(name, true, ‘ # ‘, name, ‘DESC’) 1: name of the column for aggregation 2: true\false use DISTINCT or not 3: the separator for concatenation 4: column name for ORDER BY 5: sorting … Read more
I stumbled upon this issue as well. I would ultimately like to execute the following JPQL: SELECT COUNT(u) FROM ( SELECT DISTINCT u FROM User u JOIN u.roles r WHERE r.id IN (1) ) But this wasn’t possible, also not with criteria API. Research taught that this was just a design limitation in JPA. The … Read more
In SQL the solution is very simple – join the table with a subquery, which gives you the most recent meeting for each attendee: select * from Meeting ALL join ( select max(meetingDate) as newest, attendee from Meeting group by attendee ) LATEST on ALL.meetingDate = LATEST.newest AND ALL.attendee = LATEST.attendee This works, and works … Read more
IN tests is value of single valued path expression (persistent attribute of your entity) in values you provided to query (or fetched via subquery). MEMBER OF tests is value you provided to query (or defined with expression) member of values in some collection in your entity. Lets’s use following example entity: @Entity public class EntityA … Read more
No, it is not possible to have subquery in the select clause in JPQL query. JPQL supports subqueries in WHERE and HAVING clauses. It can be (at least) part of ANY, SOME, ALL, IN, EXIST expressions, and of course it can be used normal conditional expressions: SELECT a FROM A a WHERE a.val = (SELECT … Read more
I faced similar experience when using postgresql json function in native JPA query. select * from component where data ::json ->> ?1 = ?2 JPA will throw error that i have not set the named parameter :json. The solution: “select * from component where data \\:\\:json ->> ?1 = ?2”
Write this; SELECT f from Student f LEFT JOIN f.classTbls s WHERE s.ClassName=”abc” Because your Student entity has One To Many relationship with ClassTbl entity.
Join on one-to-many relation in JPQL looks as follows: select b.fname, b.lname from Users b JOIN b.groups c where c.groupName = :groupName When several properties are specified in select clause, result is returned as Object[]: Object[] temp = (Object[]) em.createNamedQuery(“…”) .setParameter(“groupName”, groupName) .getSingleResult(); String fname = (String) temp[0]; String lname = (String) temp[1]; By the … Read more
You can use the concat operator: @Query(“select u from User u where lower(u.name) like lower(concat(‘%’, ?1,’%’))”) public List<User> findByNameFree(String name); or with a named parameter: @Query(“select u from User u where lower(u.name) like lower(concat(‘%’, :nameToFind,’%’))”) public List<User> findByNameFree(@Param(“nameToFind”) String name); (Tested with Spring Boot 1.4.3)
Spring Data JPA 1.11 now supports the exists projection in repository query derivation. See documentation here. In your case the following will work: public interface MyEntityRepository extends CrudRepository<MyEntity, String> { boolean existsByFoo(String foo); }