How to create a JPA query with LEFT OUTER JOIN
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.
jpql
JPA: JOIN in JPQL
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
IntelliJ IDEA highlights @Entity class names with “Cannot resolve symbol” in JPQL
Seems like you have not selected the default JPA provider in facet configuration. Depending upon which provider you are using, pick one from the list. Available options are EclipseLink, Hibernate, OpenJPA, TopLink
JPQL Like Case Insensitive
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 and Exists query
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); }
How do I do a “deep” fetch join in JPQL?
The JPA spec does not allow aliasing a fetch join, but some JPA providers do. EclipseLink does as of 2.4. EclipseLink also allow nested join fetch using the dot notation (i.e. “JOIN FETCH a.bs.c”), and supports a query hint “eclipselink.join-fetch” that allows nested joins (you can specify multiple hints of the same hint name). In … Read more
Setting a parameter as a list for an IN expression
Your JPQL is invalid, remove the brackets List<String> logins = em.createQuery(“SELECT a.accountManager.loginName ” + “FROM Account a ” + “WHERE a.id IN :ids”) .setParameter(“ids”,Arrays.asList(new Long(1000100), new Long(1000110))) .getResultList();
JPA Native Query select and cast object
You might want to try one of the following ways: Using the method createNativeQuery(sqlString, resultClass) Native queries can also be defined dynamically using the EntityManager.createNativeQuery() API. String sql = “SELECT USER.* FROM USER_ AS USER WHERE ID = ?”; Query query = em.createNativeQuery(sql, User.class); query.setParameter(1, id); User user = (User) query.getSingleResult(); Using the annotation @NamedNativeQuery … Read more
Doing an “IN” query with Hibernate
The syntax of your JPQL query is incorrect. Either use (with a positional parameter): List<Long> ids = Arrays.asList(380L, 382L, 386L); Query query = em.createQuery(“FROM TrackedItem item WHERE item.id IN (?1)”); query.setParameterList(1, ids) List<TrackedItem> items = query.getResultList(); Or (with a named parameter): List<Long> ids = Arrays.asList(380L, 382L, 386L); Query query = em.createQuery(“FROM TrackedItem item WHERE item.id … Read more
IN-clause in HQL or Java Persistence Query Language
Are you using Hibernate’s Query object, or JPA? For JPA, it should work fine: String jpql = “from A where name in (:names)”; Query q = em.createQuery(jpql); q.setParameter(“names”, l); For Hibernate’s, you’ll need to use the setParameterList: String hql = “from A where name in (:names)”; Query q = s.createQuery(hql); q.setParameterList(“names”, l);