With @Query annotation we can use advance LIKE expressions. This allows to embed wildcard like % within the query while using query parameters. Let's understand that with example.
Example
Entity
@Entity
public class Employee {
private @Id
@GeneratedValue
Long id;
private String name;
private String dept;
private int salary;
.............
}
Normal JPQL LIKE expression
Let's declare a query with @Query having normal JPQL LIKE operator. The query will find employees with name containing a certain part. While using query parameter, JPQL does not allow to use % wildcard in the query, instead it has to be embedded within parameter value:
public interface EmployeeRepository extends CrudRepository<Employee, Long> {
@Query("SELECT e FROM Employee e WHERE e.name LIKE ?1")
public List<Employee> findByName(String name);
.............
}
Let's see how we will use above method:
repo.findByName("%ana%")
Using @Query advance LIKE expression
Above query works fine but we have to use wildcards within the parameter value. Spring @Query allows to use wildcards within the query itself while passing the parameter value without them. Let's see how to do that:
public interface EmployeeRepository extends CrudRepository<Employee, Long> {
.............
@Query("SELECT e FROM Employee e WHERE e.name LIKE %?1%")
public List<Employee> findByName2(String name);
.............
}
Calling above method:
repo.findByName2("ana");
Using query method with 'Containing' keyword
If our query is not too complex then we can use query method without any JPQL. We need to use 'Containing' keyword which is equivalent to 'LIKE' expression.
public interface EmployeeRepository extends CrudRepository<Employee, Long> {
.............
public List<Employee> findByNameContaining(String name);
}
Calling above method:
repo.findByNameContaining("ana");
Complete Example
The repository
public interface EmployeeRepository extends CrudRepository<Employee, Long> {
@Query("SELECT e FROM Employee e WHERE e.name LIKE ?1")
public List<Employee> findByName(String name);
@Query("SELECT e FROM Employee e WHERE e.name LIKE %?1%")
public List<Employee> findByName2(String name);
public List<Employee> findByNameContaining(String name);
}
The example client
@Component
public class ExampleClient {
@Autowired
private EmployeeRepository repo;
public void run() {
List<Employee> employees = createEmployees();
repo.saveAll(employees);
System.out.println(" -- finding all employees --");
Iterable<Employee> all = repo.findAll();
all.forEach(System.out::println);
System.out.println(" -- finding by name containing %ana% --");
List<Employee> list = repo.findByName("%ana%");
list.forEach(System.out::println);
System.out.println(" -- finding by name containing ana --");
list = repo.findByName2("ana");
list.forEach(System.out::println);
System.out.println(" -- finding via query method containing ana --");
list = repo.findByNameContaining("ana");
list.forEach(System.out::println);
}
private List<Employee> createEmployees() {
return Arrays.asList(
Employee.create("Diana", "Admin", 2000),
Employee.create("Mike", "Sale", 1000),
Employee.create("Rose", "IT", 4000),
Employee.create("Sara", "Admin", 3500),
Employee.create("Tanaka", "Sale", 3000),
Employee.create("Charlie", "IT", 2500)
);
}
}
Main class
public class ExampleMain {
public static void main(String[] args) {
AnnotationConfigApplicationContext context =
new AnnotationConfigApplicationContext(AppConfig.class);
ExampleClient exampleClient = context.getBean(ExampleClient.class);
exampleClient.run();
EntityManagerFactory emf = context.getBean(EntityManagerFactory.class);
emf.close();
}
} -- finding all employees -- Employee{id=1, name='Diana', dept='Admin', salary=2000} Employee{id=2, name='Mike', dept='Sale', salary=1000} Employee{id=3, name='Rose', dept='IT', salary=4000} Employee{id=4, name='Sara', dept='Admin', salary=3500} Employee{id=5, name='Tanaka', dept='Sale', salary=3000} Employee{id=6, name='Charlie', dept='IT', salary=2500} -- finding by name containing %ana% -- Employee{id=1, name='Diana', dept='Admin', salary=2000} Employee{id=5, name='Tanaka', dept='Sale', salary=3000} -- finding by name containing ana -- Employee{id=1, name='Diana', dept='Admin', salary=2000} Employee{id=5, name='Tanaka', dept='Sale', salary=3000} -- finding via query method containing ana -- Employee{id=1, name='Diana', dept='Admin', salary=2000} Employee{id=5, name='Tanaka', dept='Sale', salary=3000}
Example ProjectDependencies and Technologies Used: - spring-data-jpa 2.0.7.RELEASE: Spring Data module for JPA repositories.
Uses org.springframework:spring-context version 5.0.6.RELEASE - hibernate-core 5.3.1.Final: Hibernate's core ORM functionality.
Implements javax.persistence:javax.persistence-api version 2.2 - h2 1.4.197: H2 Database Engine.
- JDK 1.8
- Maven 3.3.9
|
|