Spring Data JPA Specification Criteria Query generally used for search functionality. In this example we will use how to use collection or list of input parameters into CriteriaQuery to fetch data from database.

Suppose you have a web page where you give your users options to search based on various parameters.

You have given input fields on the search page with dropdown or input box and users search by giving input according to their choice or requirements. It is possible that users try to search by giving only one input field’s value or providing no input value at all and click on search or submit button.

So in this situation we do not know which field(s) we need to build a query for fetching data from database table.

Therefore Criteria query comes to rescue here. Spring Data JPA Specification Criteria Query is somehow a little different from Hibernate Criteria Query API.

Here in this Spring Data JPA Specification Criteria Query with IN Clause example, we will see how to provide search results based on users input parameter.

We will not only fetch data based on a single value but we will also fetch data when users provide a collection or list of input values.

Let’s say your search page has a multi-select dropdown and obviously users will be able to select multiple values from dropdown. So in this case you have to use IN clause to fetch data from database.

Therefore we will see how to pass a list or collection of input parameters in IN clause using Spring Data JPA Specification Criteria Query IN Clause.

Recommended Reading:

Hibernate Criteria API Example

JPA Criteria API Example

Prerequisites

Knowledge of Spring Data JPA
Knowledge of Java

Softwares

JDK 1.8
Eclipse
Gradle
Spring Boot Dependencies

Let’s move on to the example Spring Data JPA Specification Criteria Query with IN Clause…

Setting up gradle project in Eclipse

Create a gradle based Spring Boot project in Eclipse. Once project gets successfully created in Eclipse, delete generated classes from source and test directories and update the build.gradle script with the below file.

buildscript {
	ext {
	   springBootVersion = '1.5.9.RELEASE'
    }
    repositories {
		mavenLocal()
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

apply plugin: 'java'
apply plugin: 'org.springframework.boot'

sourceCompatibility = 1.8
targetCompatibility = 1.8

repositories {
    mavenLocal()
    mavenCentral()
}

dependencies {
	compile('org.springframework.boot:spring-boot-starter-web')
	compile("org.springframework.boot:spring-boot-starter-data-jpa")
	runtime("com.oracle.jdbc:ojdbc7:12.1.0.2")
}

After updating the build file, try to build the blank project.

If you face any issue related to main class then you can create the class with main method in order to make the build successful.

Define datasource and change the default port

Define the datasource in application.properties file under classpath directory src/main/resources.

Please make sure to change the database credentials and connection details in the application.properties file.

We do not want to start our server on default port, so we changing the port as 9999 on which our embedded tomcat server will be started up.

#datasource
spring.datasource.driverClassName=oracle.jdbc.driver.OracleDriver
spring.datasource.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
spring.datasource.url=jdbc:Oracle:thin:@//<host>:<port>/<service name>
spring.datasource.username=<username>
spring.datasource.password=<password>

#server port
server.port=9999

#disable schema generation from Hibernate
spring.jpa.hibernate.ddl-auto=none

Creating Spring configuration class

Create below Spring configuration class in order to create Spring DataSource and EntityManagerFactory beans.

Notice we have also let Spring know where our Entity classes are.

@Configuration
@EnableJpaRepositories(basePackages = "com.jeejava.repository")
public class DatabaseConfig {

	@Autowired
	private Environment environment;
	
	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource ds = new DriverManagerDataSource();
		ds.setDriverClassName(environment.getRequiredProperty("spring.datasource.driverClassName"));
		ds.setUrl(environment.getRequiredProperty("spring.datasource.url"));
		ds.setUsername(environment.getRequiredProperty("spring.datasource.username"));
		ds.setPassword(environment.getRequiredProperty("spring.datasource.password"));
		return ds;
	}
	
	@Bean
	public EntityManagerFactory entityManagerFactory(DataSource dataSource) {
		HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
		vendorAdapter.setDatabase(Database.ORACLE);
		LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
										
		factory.setJpaVendorAdapter(vendorAdapter);
		factory.setPackagesToScan("com.jeejava.entity");
		factory.setDataSource(dataSource);
		factory.afterPropertiesSet();
		return factory.getObject();
	}
}

Entity classes

Let’s say we have below entity classes for corresponding tables in Oracle database.

We have basically case, country and region tables in database. Let’s say a case is file for a country that belongs to a region.

I have removed all the getters and setters from the below entity classes. We will use these entity classes to fetch results based on search criteria.

@Entity
@Table(name = "CASE")
public class Case implements Serializable {

    private static final long serialVersionUID = 1L;
	
    @Id
    @Column(name = "ID")
    private Integer id;
                
	@ManyToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "REGION_ID", nullable = false)
    private Region region;
	
    @ManyToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "COUNTRY_ID", nullable = false)
    private Country country;
                
	//getters and setters
                
}

@Entity
@Table(name = "REGION")
public class Region implements Serializable {

	private static final long serialVersionUID = 1L;
	
	@Id
	@Column(name = "ID")
	private Integer id;
	
	@Column(name = "NAME")
	private String name;
	
	@OneToMany(cascade = CascadeType.MERGE, mappedBy = "region")
	private Set<Case> cases;
	
	//getters and setters
}

@Entity
@Table(name = "COUNTRY")
public class Country implements Serializable {

	private static final long serialVersionUID = 1L;
                
	@Id
	@Column(name = "ID")
	private Integer id;
	
	@Column(name = "NAME")
	private String name;
	
	@OneToMany(cascade = CascadeType.MERGE, mappedBy = "country")
        private Set<Case> cases;
                
	//getters and setters
}

Now let’s say we want to search cases based on country or region or regions or both country and region(s).

Therefore we need to create a POJO class that we will use as input parameters for fetching data from database tables.

In the below class we have two attributes – country and list of region ids. We will pass single id value to the country attribute and one or more id values to the regionIds.

public class CaseSearchCriteria implements Serializable {

    private static final long serialVersionUID = 1L;
    private String country;
	
    private List<Integer> regionIds;
	
    public String getCountry() {
        return country;
    }
	
    public void setCountry(String country) {
        this.country = country;
    }
	
    public List<Integer> getRegionIds() {
        return regionIds;
    }
	
    public void setRegionIds(List<Integer> regionIds) {
        this.regionIds = regionIds;
    }
}

Creating Repositories

You need to create below Spring Data JPA Repositories in order to fetch data from database tables.

We will be using Spring Data JPA Specification Criteria Query to fectch data and we want to search cases based on country or regions or both. So we have applied JpaSpecificationExecutor on Case Repository.

public interface CaseRepo extends JpaRepository<Case, Integer>, JpaSpecificationExecutor<Case> { }

Below two repositories are option for our example but may be required for other purpose.

public interface RegionRepo extends JpaRepository<Region, Integer> { }
public interface CountryRepo extends JpaRepository<Country, Integer> { }

Creating Spring Service class

Create below Spring Service class in order to perform the business logic or here to fetch the from database tables.

The important part here is CaseSearchSpecificaton class that does the required thing for us.

Notice how we are building Predicates using CriteriaQuery API to build our query for fetching data.

We first check whether there is any input parameter passed for fetching data otherwise we won’t fetch if no criteria is built from the input parameter(s).

The values in double quatotaion for building CriteriaQuery are entity attributes based on which we want to fetch data from database

For regionIds we are building CriteriaQuery IN clause because we may have multiple region ids, whereas for country we are building CriteriaQuery equal clause because we will have only single country id.

Now we build this search Specification and pass it to repository findAll method to fetch data.

Ideally you should not return entity class object as a return type from Spring Service class method but for example purpose I have returned here. You should always wrap to DTO or Value Object and return that object to Controller class.

@Service
public class SearchCaseService {

    @Autowired
    private CaseRepo caseRepo;
	
    @Transactional
    public List<Case> searchCases(final CasSearchCriteria searchCriteria) {
        List<Case> cases = caseRepo.findAll(CaseSearchSpecificaton.findByCriteria(searchCriteria));
        return cases;
    }
	
    private static class CaseSearchSpecificaton {
        private static Specification<Case> findByCriteria(final CasSearchCriteria searchCriteria) {
            return new Specification<Case>() {
                @Override
                public Predicate toPredicate(Root<Case> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                    List<Predicate> predicates = new ArrayList<>();
                    if (searchCriteria.getRegionIds() != null && !searchCriteria.getRegionIds().isEmpty()) {
                        Join<Case, Region> regions = root.join("region");
                        predicates.add(regions.in(searchCriteria.getRegionIds()));
                    }
                    if (null != searchCriteria.getCountry()) {
                        Join<Case, Country> country = root.join("country");
                        predicates.add(cb.equal(country.get("id"), searchCriteria.getCountry()));
                    }
                    return cb.and(predicates.toArray(new Predicate[predicates.size()]));
                }
            };
        }
    }
}

Creating Spring REST Controller class

Create below Spring REST Controller class in order to fetch data from service layer and make available to other client APIs. Here is the below POST method that takes CaseSearchCriteria object as a parameter in the request body.

Now when you pass values for country or regions, then you will get the expected data (if available for respective country or regions) from database.

@RestController
public class CaseSearchController {

    @Autowired
    private SearchCaseService searchCaseService;
	
    @PostMapping("/search/cases")
    public ResponseEntity<List<Case>> searchCases(@RequestBody CaseSearchCriteria caseSearchCriteria) {
        return new ResponseEntity<List<Case>>(searchCaseService.searchCases(caseSearchCriteria), HttpStatus.OK);
    }
}

Testing the application

Now we are done with coding…

Let’s test out application. Use similar type of request body for POST request with the REST end-point http://localhost:9999/search/cases.

{
	"country":1,
	"regionIds":[3,4]
}

Note the above request body parameters are in JSON format. Once you hit the request you should get desired data.

You may also want to pass only country or regionIds to fetch the data.

Recommended Reading:

Hibernate Criteria API Example

JPA Criteria API Example

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

Your email address will not be published. Required fields are marked *