LIKE queries in SQL allow users to search for specified patterns in the database table columns, enabling flexible and dynamic querying capabilities. The operator is often used with wildcard characters i.e. % which matches any sequence of characters and _ which matches a single character. Let us delve into understanding how to write a Spring Data JPA LIKE query providing a simple example.
1. Introduction
Spring Data JPA provides a powerful abstraction over the data access layer, allowing developers to interact with the database using repository interfaces.
- Using
JdbcTemplate - Using custom query methods in
JpaRepository
2. Setting up a database on Docker
Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.
-- Remember to change the password -- docker run -d -p 5432:5432 -e POSTGRES_PASSWORD= --name postgres postgres -- command to stop the Postgres docker container -- docker stop postgres -- command to remove the Postgres docker container -- docker rm postgres
Remember to enter the password of your choice. If everything goes well the postgresql database server will be up and running on a port number – 5432 and you can connect with the Dbeaver GUI tool to connect to the server.
2.1 Setting up pre-requisite data
To proceed further with the tutorial we will set up the required mock data in the postgresql.
drop database mydatabase;
create database mydatabase;
drop table employees;
create table employees (id serial primary key, name varchar(100), department varchar(100));
select * from employees;
insert into employees (name, department) values ('John Doe', 'Engineering');
insert into employees (name, department) values ('Jane Doe', 'Marketing');
insert into employees (name, department) values ('Jim Beam', 'Sales');
insert into employees (name, department) values ('Jake Blues', 'Engineering');
create table product(id serial primary key, name varchar(100), category varchar(100));
select * from product;
insert into product (name, category) values ('Tablet1', 'Electronics'), ('Tablet1', 'Electronics'), ('Smartphone', 'Electronics'), ('Desk', 'Furniture');
3. Code Example
3.1 Dependencies
Add the following dependencies to your pom.xml file or if you have created a spring project from start.spring.io this won’t be necessary as the file will be automatically populated with the dependencies information.
<!--for jdbc--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId> </dependency> <!--for jpa--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <!--database--> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> </dependency>
3.2 Configure application and database properties
Add the following properties to the application.properties file present in the resources folder.
# application name spring.application.name=springlikequery # database properties spring.datasource.url=jdbc:postgresql://localhost:5432/mydatabase spring.datasource.username=postgres spring.datasource.password=somepostgrespassword # application properties server.port=9090 spring.main.banner-mode=off spring.main.log-startup-info=false
3.3 Creating the Model Classes
Create an Employee model class to interact with the JdbcTemplate class.
public class Employee {
private Long id;
private String name;
private String department;
// toString, Getters, and Setters methods
}
Create a Product entity class to internal with the JpaRepository interface.
@Entity
@Table
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
private String category;
// toString, Getters, and Setters methods
}
3.4 Creating the Data Interaction Layer
Create the employee repository class to interact with the employee entity for fetching the data from the table using the JdbcTemplate. Do note JdbcClient is a wrapper over the JdbcTemplate but the template provides better control and easier readability to understand the changes and the logic. Hence we are using the template over the client.
@Repository
public class EmployeeRepository {
private final RowMapper<Employee> rowMapper = (rs, rowNum) -> {
Employee employee = new Employee();
employee.setId(rs.getLong("id"));
employee.setName(rs.getString("name"));
employee.setDepartment(rs.getString("department"));
return employee;
};
@Autowired
private JdbcTemplate jdbcTemplate;
public List<Employee> findByNameLike(String namePattern) {
String sql = "SELECT * FROM employees WHERE name LIKE ?";
return jdbcTemplate.query(sql, new Object[]{"%" + namePattern + "%"}, rowMapper);
}
}
The given code defines a method findByNameLike that interacts with the database to fetch information about employees. The method has an sql query string that selects all columns from the employees table where the search key matches a specified pattern using the SQL LIKE operator. The method uses jdbcTemplate to execute the SQL query and map the result set rows to the employee dto object.
Similarly, create the product repository interface to interact with the product entity for fetching the data from the table using the JpaRepository interface.
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
@Query("SELECT p FROM Product p WHERE p.name like %:name%")
List<Product> findByNameLike(@Param("name") String name);
}
The given code defines a Spring data interface that extends JpaRepository<Product, Long> providing crud operations ability. Within this interface, a custom query method findByNameLike annotated with the @Query annotation is defined to select products from the Product entity where the product name matches a given using the SQL LIKE operator.
3.5 Create the Main file
Create a Spring boot application and query custom methods.
- The file defines a Spring Boot application named
SpringlikequeryApplication. It implements theCommandLineRunnerinterface to execute additional code after the application context has been loaded. - The main method is the entry point of the application, invoking
SpringApplication.runwith the application’s class and the command-line arguments. - The
runmethod is overridden to call two private methods,jdbcTemplateandjpaRepository, which perform database operations. - The
@Autowiredannotation is used to inject instances ofEmployeeRepositoryandProductRepositoryinto the application. These repositories are used to interact with the database.
@SpringBootApplication
public class SpringlikequeryApplication implements CommandLineRunner {
// Main method
public static void main(String[] args) {
SpringApplication.run(SpringlikequeryApplication.class, args);
}
@Override
public void run(String... args) throws Exception {
jdbcTemplate();
jpaRepository();
}
@Autowired
EmployeeRepository empRepo;
private void jdbcTemplate() {
System.out.println(empRepo.findByNameLike("Jane"));
System.out.println(empRepo.findByNameLike("Doe"));
}
@Autowired
ProductRepository productRepository;
private void jpaRepository() {
System.out.println(productRepository.findByNameLike("Tablet"));
System.out.println(productRepository.findByNameLike("Desk"));
}
}
The jdbcTemplate method prints the results of querying the database for employees whose names contain “Jane” and “Doe” using the findByNameLike method of EmployeeRepository.
[Employee{id=2, name='Jane Doe', department='Marketing'}]
[Employee{id=1, name='John Doe', department='Engineering'}, Employee{id=2, name='Jane Doe', department='Marketing'}]
Similarly, the jpaRepository method prints the results of querying the database for products whose names contain “Tablet” and “Desk” using the findByNameLike method of ProductRepository. The expected output is:
[Product{id=1, name='Tablet1', category='Electronics'}, Product{id=2, name='Tablet1', category='Electronics'}]
[Product{id=4, name='Desk', category='Furniture'}]
3.6 Run the application
Run your Spring Boot application and the application will be started on a port number specified in the application properties file. As soon as the application is started the command runner interface will trigger the run() method and the output will be printed on the IDE console.
4. Conclusion
The application demonstrates the use of Spring data repositories to perform pattern-based searches on database entities. The CommandLineRunner helps to run specific query examples upon application startup, showcasing how to use both JdbcTemplate for custom queries and Spring Data JPA repositories for more abstracted database interactions. These approaches highlight the flexibility and power of Spring Boot in managing dao layers, allowing developers to execute complex queries with minimal boilerplate code.
5. Download the source code
In this tutorial, we demonstrated how to write LIKE queries using JdbcTemplate and custom query methods in JpaRepository each providing a flexible and powerful way to perform pattern matching in SQL queries.
You can download the full source code of this example here: Spring Data ‘LIKE’ Query with JdbcClient or JpaRepository
