Spring Data JPA IsNull Example Using Spring Boot

In this post, we will see Spring Data JPA IsNull Example using Spring Boot and Oracle. We will see how to use IsNull keyword to define Query Methods(Query creation from method names).

Let’s say we have an entity called Student.java.

package com.netsurfingzone.entity;

@Entity
public class Student {

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

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

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

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

}

In the database, we have some records as below.

Spring Data JPA IsNull Example

Below Query method will return all record which name is null.

public List<Student> findByNameIsNull();

Generated Query.

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where student0_.name is null

Below Query method will return all record which name is john or null.

public List<Student> findByNameOrNameIsNull(String name);

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where student0_.name=? or student0_.name is null


Below Query method will return all record which name is john or null and the university is rgtu.

public List<Student> findByNameAndUniversityOrNameIsNull(String name, String university);

Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_number3_0_, student0_.university as university4_0_ from student student0_ where student0_.name=? and student0_.university=? or student0_.name is null

Let’s see an example of Spring Data JPA IsNull Example.

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ click on next.  Fill all details(GroupId – springdataisnull, ArtifactId – springdataisnull and name – springdataisnull) and click on finish. Keep packaging as the jar.

Modify pom.xml

<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>springdataisnull</groupId>
	<artifactId>springdataisnull</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springdataisnull</name>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>

		</dependency>

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

		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.3</version>
		</dependency>

	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
		<plugins>

			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.1</version>
				<configuration>
					<fork>true</fork>
					<executable>C:\Program Files\Java\jdk1.8.0_131\bin\javac.exe</executable>
				</configuration>
			</plugin>


		</plugins>
	</build>
</project>

Note – In pom.xml we have defined javac.exe path in configuration tag. You need to change accordingly i.e where you have installed JDK.

If you see any error for oracle dependency then follow these steps.

Directory structure –

Spring Data JPA delete() vs deleteInBatch()

Student.java

package com.netsurfingzone.entity;

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

@Entity
public class Student {

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

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

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

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

	public int getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

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

	public String getRollNumber() {
		return rollNumber;
	}

	public void setRollNumber(String rollNumber) {
		this.rollNumber = rollNumber;
	}

	public String getUniversity() {
		return university;
	}

	public void setUniversity(String university) {
		this.university = university;
	}

}

StudentController.java

package com.netsurfingzone.controller;

import java.util.List;

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;

import com.netsurfingzone.entity.Student;
import com.netsurfingzone.service.StudentService;

@RestController
@RequestMapping(value = "/student")
public class StudentController {

	@Autowired
	private StudentService studentService;

	@RequestMapping(value = "/saveall", method = RequestMethod.POST)
	@ResponseBody
	public List<Student> saveAllStudents(@RequestBody List<Student> studentList) {
		List<Student> studentResponse = (List<Student>) studentService.saveAllStudent(studentList);
		return studentResponse;
	}
	
	@RequestMapping(value = "/findbynameisnull", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findByNameIsNull() {
		List<Student> studentResponse = (List<Student>) studentService.findByNameIsNull();
		return studentResponse;
	}
	
	@RequestMapping(value = "/findbynameanduniversityornameisnull/{name}/{university}", method = RequestMethod.GET)
	@ResponseBody
	public List<Student> findByNameAndUniversityOrNameIsNull(@PathVariable String name, @PathVariable String university) {
		List<Student> studentResponse = (List<Student>) studentService.findByNameAndUniversityOrNameIsNull(name, university);
		return studentResponse;
	}

}

Note – See more details about @Controller and RestController here.

StudentRepository.java – interface


package com.netsurfingzone.repository;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.netsurfingzone.entity.Student;

@Repository
public interface StudentRepository extends CrudRepository<Student, Serializable> {

	public List<Student> findByNameIsNull();
	
	public List<Student> findByNameOrNameIsNull(String name);
	
	public List<Student> findByNameAndUniversityOrNameIsNull(String name, String university);
	
	
}

StudentService.java – interface

package com.netsurfingzone.service;

import java.util.List;

import org.springframework.stereotype.Component;

import com.netsurfingzone.entity.Student;

@Component
public interface StudentService {
	public List<Student> saveAllStudent(List<Student> studentList);

	public List<Student> findByNameIsNull();
	
	public List<Student> findByNameOrNameIsNull(String name);
	
	public List<Student> findByNameAndUniversityOrNameIsNull(String name, String university);

}

Note – See here more about @Component, @Controller, @Service and @Repository annotations here.

StudentServiceImpl.java

package com.netsurfingzone.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.netsurfingzone.entity.Student;
import com.netsurfingzone.repository.StudentRepository;
import com.netsurfingzone.service.StudentService;

@Service("studentServiceImpl")
public class StudentServiceImpl implements StudentService {

	@Autowired
	private StudentRepository studentRepository;

	@Transactional
	public List<Student> saveAllStudent(List<Student> studentList) {
		List<Student> response = (List<Student>) studentRepository.saveAll(studentList);
		return response;
	}

	@Transactional
	public List<Student> findByNameIsNull() {
		List<Student> response = (List<Student>) studentRepository.findByNameIsNull();
		return response;
	}
	
	@Transactional
	public List<Student> findByNameOrNameIsNull(String name) {
		List<Student> response = (List<Student>) studentRepository.findByNameOrNameIsNull(name);
		return response;
	}
	
	
	@Transactional
	public List<Student> findByNameAndUniversityOrNameIsNull(String name, String university) {
		List<Student> response = (List<Student>) studentRepository.findByNameAndUniversityOrNameIsNull(name, university);
		return response;
	}
	
	
	

}

SpringMain.java


package com.netsurfingzone.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;

@SpringBootApplication
@ComponentScan(basePackages = "com.*")
@EntityScan("com.netsurfingzone.entity")
public class SpringMain {
	public static void main(String[] args) {

		SpringApplication.run(SpringMain.class, args);
	}

}

Note – See more details about @ComponentScan here.

JpaConfig.java

package com.netsurfingzone.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

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

}

Note – See more details about @Configuration annotations here.

application.properties

# Connection url for the database
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.username=SYSTEM
spring.datasource.password=oracle2
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
# Show or not log for each sql query
spring.jpa.show-sql = true
 
 
spring.jpa.hibernate.ddl-auto =create
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle10gDialect
 
server.port = 9091

Let’s run the SpringMain class(run as java application).

Perform saveall operation first using below REST API.

http://localhost:9091/student/saveall

Request Data.

[
    {
        "name": "john",
        "rollNumber": "0126CS01",
        "university":"rgtu"
    },
    {
        "name": null,
        "rollNumber": "0126CS02",
        "university":"rgtu"
    },
    {
        "name": "aric",
        "rollNumber": "0126CS03",
        "university":"rgtu"
    },
    {
        "name": null,
        "rollNumber": "0126CS04",
        "university":"rgtu"
    }
]

Response data –

[
    {
        "id": 1,
        "name": "john",
        "rollNumber": "0126CS01",
        "university": "rgtu"
    },
    {
        "id": 2,
        "name": null,
        "rollNumber": "0126CS02",
        "university": "rgtu"
    },
    {
        "id": 3,
        "name": "aric",
        "rollNumber": "0126CS03",
        "university": "rgtu"
    },
    {
        "id": 4,
        "name": null,
        "rollNumber": "0126CS04",
        "university": "rgtu"
    }
]

Test the below URLs.

http://localhost:9091/student/findbynameisnull

localhost:9091/student/findbynameornameisnull/john

localhost:9091/student/findbynameanduniversityornameisnull/{name}/{rgtu}

That’s all about Spring Data JPA IsNull Example Using Spring Boot and Oracle.

You may like.

Other Spring Data JPA and Hibernate tutorials.

Spring Data JPA Docs.

Summary – We have seen about Spring Data JPA IsNull Example. We Covered below Query Methods.

public List<Student> findByNameIsNull();
public List<Student> findByNameOrNameIsNull(String name);
public List<Student> findByNameAndUniversityOrNameIsNull(String name, String university);