Spring Data JPA Query Method - IN Clause

← Back to All Spring Data JPA Tutorials

In this tutorial, we will learn how to write a query method for the IN Clause condition in the Spring Data JPA repository.

Spring Data JPA query methods are the most powerful methods, we can create query methods to select the records from the database without writing SQL queries. Behind the scenes, Spring Data JPA will create SQL queries based on the query method and execute the query for us.

Let's follow the Spring Data JPA naming convention to write a query method for the IN clause for the Product entity class.

We need to create a method starting with the prefix findBy followed by the field name and then In suffix - findBy[FieldName][In]

Example: Consider the Product entity class and if we want to retrieve products with In clause then here is the Spring Data JPA query method:

	List<Product> findByNameIn(List<String> names);

Let's create a complete example to understand end-to-end.

Maven Dependencies

Add the following maven dependencies to your Spring Boot project:

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
			<scope>runtime</scope>
		</dependency>

Product Entity

Let's first create a Product entity class and add the following content to it:

package net.javaguides.springdatajpacourse.entity;

import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.util.Date;

@Entity
@Table(name="products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "sku")
    private String sku;

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

    @Column(name = "description")
    private String description;

    @Column(name = "price")
    private BigDecimal price;

    @Column(name = "image_url")
    private String imageUrl;

    @Column(name = "active")
    private boolean active;

    @Column(name = "date_created")
    @CreationTimestamp
    private Date dateCreated;

    @Column(name = "last_updated")
    @UpdateTimestamp
    private Date lastUpdated;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getSku() {
        return sku;
    }

    public void setSku(String sku) {
        this.sku = sku;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    public String getImageUrl() {
        return imageUrl;
    }

    public void setImageUrl(String imageUrl) {
        this.imageUrl = imageUrl;
    }

    public boolean isActive() {
        return active;
    }

    public void setActive(boolean active) {
        this.active = active;
    }

    public Date getDateCreated() {
        return dateCreated;
    }

    public void setDateCreated(Date dateCreated) {
        this.dateCreated = dateCreated;
    }

    public Date getLastUpdated() {
        return lastUpdated;
    }

    public void setLastUpdated(Date lastUpdated) {
        this.lastUpdated = lastUpdated;
    }

    @Override
    public String toString() {
        return "Product{" +
                "id=" + id +
                ", sku='" + sku + '\'' +
                ", name='" + name + '\'' +
                ", description='" + description + '\'' +
                ", price=" + price +
                ", imageUrl='" + imageUrl + '\'' +
                ", active=" + active +
                ", dateCreated=" + dateCreated +
                ", lastUpdated=" + lastUpdated +
                '}';
    }
}

ProductRepository

Let's create ProductRepository which extends JpaRepository and add the following code to it:

import java.util.List;

import net.javaguides.springdatajpacourse.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {
	/**
	 * Return the products based on SQL in clause condition
	 * @param names
	 * @return
	 */
	List<Product> findByNameIn(List<String> names);
}

Configure MySQL and Hibernate Properties

Let's use the MySQL database to store and retrieve the data in this example and we gonna use Hibernate properties to create and drop tables.

Open the application.properties file and add the following configuration to it:

spring.datasource.url=jdbc:mysql://localhost:3306/ecommerce?useSSL=false
spring.datasource.username=root
spring.datasource.password=Mysql@123

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect

spring.jpa.hibernate.ddl-auto = create-drop

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Make sure that you will create ecommerce database before running the Spring boot application.

Also, change the MySQL username and password as per your MySQL installation on your machine.

Testing Spring Data JPA Query Method - IN Clause

In order to test the query method with IN clause that we have created, we gonna use CommandLineRunner.run() method to execute the testing code while the Spring boot application startup: 

import net.javaguides.springdatajpacourse.entity.Product;
import net.javaguides.springdatajpacourse.repository.ProductRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

@SpringBootApplication
public class SpringDataJpaCourseApplication implements CommandLineRunner{

	public static void main(String[] args) throws Exception {
		SpringApplication.run(SpringDataJpaCourseApplication.class, args);
	}

	@Autowired
	private ProductRepository productRepository;

	@Override
	public void run(String... args) throws Exception {
        Product product = new Product();
        product.setName("product 1");
        product.setDescription("product 1 desc");
        product.setPrice(new BigDecimal(100));
        product.setDateCreated(new Date());
        product.setLastUpdated(new Date());
        product.setSku("product 1 sku");
        product.setActive(true);
        product.setImageUrl("product1.png");

        // save product
        productRepository.save(product);

        Product product2 = new Product();
        product2.setName("product 2");
        product2.setDescription("product 2 desc");
        product2.setPrice(new BigDecimal(200));
        product2.setDateCreated(new Date());
        product2.setLastUpdated(new Date());
        product2.setSku("product 2 sku");
        product2.setActive(true);
        product2.setImageUrl("product2.png");

        // save product 2
        productRepository.save(product2);

        // products based on in condition
        List<Product> productsIn = productRepository.findByNameIn(List.of("product 1","product 2"));
        productsIn.forEach((p) ->
                System.out.println(p));
	}
}

Once Spring boot application execution completes, you can able to see Spring Data JPA (uses Hibernate as JPA provider) generated SQL statements in a console:

Hibernate: 
    insert 
    into
        products
        (active, date_created, description, image_url, last_updated, name, price, sku) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: 
    insert 
    into
        products
        (active, date_created, description, image_url, last_updated, name, price, sku) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: 
    select
        product0_.id as id1_0_,
        product0_.active as active2_0_,
        product0_.date_created as date_cre3_0_,
        product0_.description as descript4_0_,
        product0_.image_url as image_ur5_0_,
        product0_.last_updated as last_upd6_0_,
        product0_.name as name7_0_,
        product0_.price as price8_0_,
        product0_.sku as sku9_0_ 
    from
        products product0_ 
    where
        product0_.name in (
            ? , ?
        )

Note that the below SQL query with In clause:

    select
        product0_.id as id1_0_,
        product0_.active as active2_0_,
        product0_.date_created as date_cre3_0_,
        product0_.description as descript4_0_,
        product0_.image_url as image_ur5_0_,
        product0_.last_updated as last_upd6_0_,
        product0_.name as name7_0_,
        product0_.price as price8_0_,
        product0_.sku as sku9_0_ 
    from
        products product0_ 
    where
        product0_.name in (
            ? , ?
        )

Related Spring Data JPA Query Method Tutorials

• Spring Data JPA Query Method - Multiple Fields
• Spring Data JPA Query Method - Distinct
• Spring Data JPA Query Method - Greater Than
• Spring Data JPA Query Method - Less Than
• Spring Data JPA Query Method - Between
• Spring Data JPA Query Method - Like
• Spring Data JPA Query Method - IN Clause

← Back to All Spring Data JPA Tutorials