Parameter in Like Clause Jpql

how to write jpql query with like operator

Assuming that you have the following Employee entity:

@Entity
public class Employee
{
   @Id
   @Column(name = "emp_id")
   Long id;

   @Column(name = "emp_name")
   String name;

   @ManyToOne
   @JoinColumn(name = "dept_id")
   Department department;
}

@Entity
public class Department
{
   @Id
   @Column(name = "dept_id")
   Long id;

   @Column(name = "dept_name")
   String name;
}

you can write the following jpql:

@Query("select e from Employee e left join e.department d where upper(d.name) like '%' || upper(:deptName ) || '%'")
List<Employee> searchAllEmployees(@Param("deptName")String deptName);

See also this section of hibernate documentation.

How to set a parameter for JPQL like query?

update

Query q = em.createQuery("select o from Item o WHERE o.itemName like ':def%'");

to

Query q = em.createQuery("select o from Item o WHERE o.itemName like :def");

Spring JPA @Query with LIKE

Try to use the following approach (it works for me):

@Query("SELECT u.username FROM User u WHERE u.username LIKE CONCAT('%',:username,'%')")
List<String> findUsersWithPartOfName(@Param("username") String username);

Notice: The table name in JPQL must start with a capital letter.

Hibernate JPA select with parameter and multiple LIKE

Because you can't use the named parameter to add sql. You're passing the whole set of like elements as a value, so you need to build up the JPQL the same way you do in the first instance.

I've added some sample code that I absolutely did not test to get you moving in the right direction.

@GetMapping("/getNombre/{nom}")
private List<PersonaNegativa> getByNombre(@PathVariable String nom) {
    DoubleMetaphone dm = new DoubleMetaphone();
    dm.setMaxCodeLen(5);

    int parameterCount = 0;
    StringBuilder clause = new StringBuilder();
    List<String> parameters = new ArrayList<>();
    for (String string : nom.split(" ")) {
        clause.append((clause.length() == 0) ? " p.NombreCompletoFonetico like ?" : " or p.NombreCompletoFonetico like ?");
        parameters.add("%" + dm.doubleMetaphone(string));

    }

    String jpaql = "select p from PersonaNegativa p where " + clause.toString();

    List<PersonaNegativa> listneg = new ArrayList<PersonaNegativa>();
    try {
        TypedQuery<PersonaNegativa> query = entityManager
                .createQuery(jpaql,
                        PersonaNegativa.class);
        for (int i = 0; i < parameters.size(); i++) {
            query.setParameter(i, parameters.get(i))
        }
        return query.getResultList();

    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

like clause in JPA native sql query

I think that you want CONCAT():

and name LIKE CONCAT('%', ?2, '%')

how to pass value to LIKE statement in jpa native query

Made it work by prepending and appending % in search string and passing it as below

@Query(value = "select u.* from users where u.name like ?1",nativeQuery = true)
List<User> findAllByNameContaining(String searchString);

Calling it by

List<Users> result = userRepository.findAllByNameContaining("%".concat(searchString).concat("%"));

How to use quote inside LIKE jpql query?

I found the solution!
And... it is pretty simple. It is needed just to double the quote symbol like ''

And the JPQL query will looks like:

SELECT t FROM Author t WHERE t.fullName LIKE '%Tes O''Ha%' 
    OR t.firstName  LIKE '%Tes%' 
    OR t.firstName  LIKE '%O''Ha%' 
    OR t.secondName LIKE '%Tes%' 
    OR t.secondName LIKE '%O''Ha%'

That's it!

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

I'm not sure for JPA 1.0 but you can pass a Collection in JPA 2.0:

String qlString = "select item from Item item where item.name IN :names"; 
Query q = em.createQuery(qlString, Item.class);

List<String> names = Arrays.asList("foo", "bar");

q.setParameter("names", names);
List<Item> actual = q.getResultList();

assertNotNull(actual);
assertEquals(2, actual.size());

Tested with EclipseLInk. With Hibernate 3.5.1, you'll need to surround the parameter with parenthesis:

String qlString = "select item from Item item where item.name IN (:names)";

But this is a bug, the JPQL query in the previous sample is valid JPQL. See HHH-5126.

---

Related Topics