How to use @Where annotation in Hibernate

February 13, 2023

The @Where annotation in Hibernate is used to apply conditional filters on a class, method, or field while retrieving data from a database. This annotation is used to specify an additional where clause that will be used to retrieve data. We are going to see how to use hibernate @Where annotation with field, class, and method.

The @Where annotation can only be used with collections type fields and cannot be used with single entity fields.

At the end of the tutorial, we will test this example using postman.

Request data to test example.

{
	"bookName": "Premchand's best stories",
        "deleted":false,
	"storyList": [{
			"storyName": "Stories of two oxes",
                        "completed":true
		},
		{
			"storyName": "idgah",
                        "completed":true
		},
		{
			"storyName": "Poosh Ki Rat",
                        "completed":true
		}
	]
}

Note – The @Where annotation only affects SELECT statements and does not modify the actual data in the database.

Consider we have entities called Book.java and Story.java. Both entities are in a one-to-many relationship (i.e one book can have multiple stories).

Let’s see how to use Hibernate @Where annotation with a field.

@Entity
public class Book {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int bookId;

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

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

	@OneToMany(cascade = CascadeType.ALL, fetch= FetchType.LAZY)
	@JoinColumn(name = "book_id",referencedColumnName="bookId")
        @Where(clause = "completed= true")
	private List<Story> storyList = new ArrayList<>();
        //getter & setter
}

Story.java

@Entity
public class Story {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int storyId;

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

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

        //getter & setter
}

In this example, the @Where annotation is applied to the storyList field, which is a collection of Story entities associated with a Book. The clause attribute of the @Where annotation specifies the condition completed= true, which means that only Story entities with a completed flag set to true will be retrieved.

Table of Contents

Using Hibernate @Where annotation with class

@Entity
@Where(clause = "deleted = false")
public class Book {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int bookId;

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

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

	@OneToMany(cascade = CascadeType.ALL, fetch= FetchType.LAZY)
	@JoinColumn(name = "book_id",referencedColumnName="bookId")
	private List<Story> storyList = new ArrayList<>();
        //getter & setter
}

In this example, the @Where annotation is used to filter out the records where the deleted field is false. That means every time when we query for the book, Hibernate will add WHERE deleted = false to the SQL query, and only non-deleted books will be returned.

Using Hibernate @Where annotation with the method

@Entity
public class Book {

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int bookId;

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

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

	@OneToMany(cascade = CascadeType.ALL, fetch= FetchType.LAZY)
	@JoinColumn(name = "book_id",referencedColumnName="bookId")
	private List<Story> storyList = new ArrayList<>();

         @Where(clause = "deleted = false")
         public Boolean isNotDeleted() {
             return !deleted;
         }
        //getter & setter
}

In this example, the @Where annotation is used with the isNotDeleted method to filter out the records where the deleted field is false. This means that every time you query for employees, Hibernate will add WHERE deleted = false to the SQL query, and only non-deleted employees will be returned.

The @Where annotation applied to a method works similarly to the one applied to a field. The only difference is that the condition is determined by the return value of the method, rather than the value of the field directly.

Let’s see an example that will show us How to use @Where annotation in Hibernate.

Add maven dependency in pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>hibernateonetomanyexample</groupId>
    <artifactId>hibernateonetomanyexample</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
    </parent>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.batch</groupId>
            <artifactId>spring-batch-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-batch</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>

</project>

Book.java

package com.javatute.entity;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;

import com.fasterxml.jackson.annotation.JsonManagedReference;
import org.hibernate.annotations.Where;

@Entity
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int bookId;

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

    @OneToMany(cascade = CascadeType.ALL, fetch= FetchType.LAZY)
    @JoinColumn(name = "book_id",referencedColumnName="bookId")
    @Where(clause = "completed= false")
    private List<Story> storyList = new ArrayList<>();

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

    public int getBookId() {
        return bookId;
    }

    public void setBookId(int bookId) {
        this.bookId = bookId;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public List<Story> getStoryList() {
        return storyList;
    }

    public void setStoryList(List<Story> storyList) {
        this.storyList = storyList;
    }

    public boolean isDeleted() {
        return deleted;
    }

    public void setDeleted(boolean deleted) {
        this.deleted = deleted;
    }
}

Story.java

package com.javatute.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Story {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int storyId;

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

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

    public int getStoryId() {
        return storyId;
    }

    public void setStoryId(int storyId) {
        this.storyId = storyId;
    }

    public String getStoryName() {
        return storyName;
    }

    public void setStoryName(String storyName) {
        this.storyName = storyName;
    }

    public boolean isCompleted() {
        return completed;
    }

    public void setCompleted(boolean completed) {
        this.completed = completed;
    }
}

BookRepository.java

package com.javatute.repository;

import java.io.Serializable;

import com.javatute.entity.Book;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;


@Repository
public interface BookRepository extends CrudRepository<Book,Serializable> {
    Book findByBookId(int bookId);
}

BookService.java

package com.javatute.service;

import com.javatute.entity.Book;
import org.springframework.stereotype.Component;



@Component
public interface BookService {
    public Book saveBook(Book book);
    public Book findByBookId(int bookId);
}

BookServiceImpl.java

package com.javatute.serviceimpl;

import java.util.ArrayList;
import java.util.List;

import com.javatute.entity.Book;
import com.javatute.repository.BookRepository;
import com.javatute.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;



@Service("bookServiceImpl")
public class BookServiceImpl implements BookService {

    @Autowired
    private BookRepository bookRepository;

    public Book saveBook(Book book) {
        book = bookRepository.save(book);
        return book;
    }

    public Book findByBookId(int bookId) {
        Book book = bookRepository.findByBookId(bookId);
        return book;
    }
}

BookController.java

package com.javatute.controller;

import com.javatute.entity.Book;
import com.javatute.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping(value = "/book")
public class BookController {

    @Autowired
    private BookService bookService;

    @RequestMapping(value = "/savebook", method = RequestMethod.POST)
    @ResponseBody
    public Book saveBook(@RequestBody Book book) {
        Book bookResponse = bookService.saveBook(book);
        return bookResponse;
    }

    @RequestMapping(value = "/{bookId}", method = RequestMethod.GET)
    @ResponseBody
    public Book getBookDetails(@PathVariable int bookId) {
        Book bookResponse = bookService.findByBookId(bookId);
        return bookResponse;
    }

}

Let’s define configuration file

JpaConfig.java

package com.javatute.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@EnableJpaRepositories(basePackages = "com.javatute.repository")
public class JpaConfig {

}

SpringMain.java

package com.javatute.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import java.net.URI;

@SpringBootApplication
@ComponentScan(basePackages = "com.javatute.*")
@EntityScan("com.javatute.*")
public class SpringMain {
    public static void main(String[] args) throws Exception{
        SpringApplication.run(SpringMain.class, args);
    }
}

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/springbootcrudexample
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect =  org.hibernate.dialect.MySQL8Dialect
server.port = 9092

Let’s test the example using postman

First, create a book with a few stories.

Request data

{
	"bookName": "Premchand's best stories",
    "deleted":false,
	"storyList": [{
			"storyName": "Stories of two oxes",
            "completed":true
		},
		{
			"storyName": "idgah",
            "completed":true
		},
		{
			"storyName": "Poosh Ki Rat",
            "completed":true
		}
	]
}

Response data

{
    "bookId": 5,
    "bookName": "Premchand's best stories",
    "storyList": [
        {
            "storyId": 6,
            "storyName": "Stories of two oxes",
            "completed": true
        },
        {
            "storyId": 7,
            "storyName": "idgah",
            "completed": true
        },
        {
            "storyId": 8,
            "storyName": "Poosh Ki Rat",
            "completed": true
        }
    ],
    "deleted": false
}

How to use @Where annotation in Hibernate

Now perform the get operation. We should not able to fetch the story since we have used @Where(clause = "completed= false")and we don’t have any records of this condition.

That’s all about How to use @Where annotation in Hibernate.

See Hibernate @Where annotation docs.

Using Hibernate @Where annotation with class

Using Hibernate @Where annotation with the method

Let’s see an example that will show us How to use @Where annotation in Hibernate.

RELATED ARTICLESMORE FROM AUTHOR

Com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 byte

What is Hibernate

Hibernate one to one unidirectional with MySQL and Spring Boot

RELATED ARTICLES MORE FROM AUTHOR