Blog

Spring Data JPA findBy Column Name with Example





Hey guys in this post, we discuss Data JPA finder methods by field name with example.

Overview

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

Read More:

To create finder methods in Data JPA, we need to follow a certain naming convention. To create finder methods for the entity class field name, we need to create a method starting with findBy followed by field name. Consider the following Employee entity class which has 2 fields name and location

public class Employee {
	
	private String name;
	
	private String location;
	
	//setters and getters
}

To create a finder method for the name field then we need to create a method like this –

findByName(String name)

To create a finder method for the location field then we need to create a method like this –

findByLocation(String location)

Behind the scenes, Data JPA will create a SQL query like this –

SELECT * FROM employee WHERE name="employee name";
SELECT * FROM employee WHERE location="location name";

As you can see, it’s so much easy to select the records with the help of finder methods. That’s the beauty of Data JPA.

Watch the video

Complete example

Let’s create a step-by-step spring boot project and create different finder methods for various fields.

Create database and insert sample data

Open MySQL workbench and execute the following commands

CREATE DATABASE mydb;

USE mydb;

CREATE TABLE tbl_laptops(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255) NOT NULL,
    brand VARCHAR(255) NOT NULL,
    price DOUBLE(10, 2) NOT NULL
);

INSERT INTO tbl_laptops(name, description, brand, price)
VALUES("Dell Inspiron", "dell company laptop", "Dell", 60000.00);

INSERT INTO tbl_laptops(name, description, brand, price)
VALUES("Dell XPS", "dell company laptop", "Dell", 70000.00);

INSERT INTO tbl_laptops(name, description, brand, price)
VALUES("Macbook Air", "apple company laptop", "Apple", 85000.00);

INSERT INTO tbl_laptops(name, description, brand, price)
VALUES("Macbook Pro", "apple company laptop", "Apple", 160000.00);

INSERT INTO tbl_laptops(name, description, brand, price)
VALUES("HP", "hp company laptop", "HP", 50000.00);

INSERT INTO tbl_laptops(name, description, brand, price)
VALUES("Lenovo", "lenovo company laptop", "Lenovo", 50000.00);

SELECT * FROM tbl_laptops;

We have created the table tbl_laptops which contains 5 fields. We have inserted the sample data as well.

Create spring boot project

There are many different ways to create a spring boot application, you can follow the below articles to create one –

>> Create spring boot application using Spring initializer
>> Create spring boot application in Spring tool suite [STS]
>> Create spring boot application in IntelliJ IDEA

Add maven dependencies

Open pom.xml and add the following dependencies –

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.4</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>in.bushansirgur</groupId>
	<artifactId>findbyfieldname</artifactId>
	<version>v1</version>
	<name>findbyfieldname</name>
	<description>Spring boot data jpa find by field name</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

spring-boot-starter-web dependency for building web applications using Spring MVC. It uses the tomcat as the default embedded container.




spring-boot-devtools dependency for automatic reloads or live reload of applications. spring-boot-starter-data-jpa dependency is a starter for using Spring Data JPA with Hibernate. lombok dependency is a java library that will reduce the boilerplate code that we usually write inside every entity class like setters, getters, and toString()

Create an entity class

Create Laptop.java inside the in.bushansirgur.springboot.entity package and add the following content

package in.bushansirgur.springboot.entity;

import java.math.BigDecimal;

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

import lombok.Data;

@Entity
@Data
@Table(name="tbl_laptops")
public class Laptop {
	
	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private Long id;
	
	private String name;
	
	private String description;
	
	private String brand;
	
	private BigDecimal price;
}

We have added @Data annotation which is a Lombok annotation, that will automatically create setters, getters, toString(), and equals() for us.

@Entity is a mandatory annotation that indicates that this class is a JPA entity and is mapped with a database table.

@Table annotation is an optional annotation that contains the table info like table name.




@Id annotation is a mandatory annotation that marks a field as the primary key

Create a Repository

Create an interface LaptopRepository.java inside the in.bushansirgur.springboot.repos package and add the following content

package in.bushansirgur.springboot.repos;

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

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import in.bushansirgur.springboot.entity.Laptop;

@Repository
public interface LaptopRepository extends JpaRepository<Laptop, Long> {
	
	List<Laptop> findByName(String name);
	
	List<Laptop> findByBrand(String name);
	
	List<Laptop> findByPrice(BigDecimal price);
}

@Repository annotates classes at the persistence layer, which will act as a database repository. We have extended this interface with JPARepository interface which will provide built-in methods to interact with the database also we can define finder methods. We have defined 3 finder methods findByName(), findByBrand() and findByPrice() which will return the List<Laptops>

Create a Rest controller

Create LaptopController.java inside the in.bushansirgur.springboot.controller package and add the following content

package in.bushansirgur.springboot.controller;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import in.bushansirgur.springboot.entity.Laptop;
import in.bushansirgur.springboot.repos.LaptopRepository;

@RestController
public class LaptopController {
	
	@Autowired
	LaptopRepository lRepo;
	
	@GetMapping("/laptops/name")
	public ResponseEntity<List<Laptop>> getLaptopsByName(@RequestParam String name) {
		return new ResponseEntity<List<Laptop>>(lRepo.findByName(name), HttpStatus.OK);
	}
	
	@GetMapping("/laptops/brand")
	public ResponseEntity<List<Laptop>> getLaptopsByBrand(@RequestParam String brand) {
		return new ResponseEntity<List<Laptop>>(lRepo.findByBrand(brand), HttpStatus.OK);
	}
	
	@GetMapping("/laptops/price")
	public ResponseEntity<List<Laptop>> getLaptopsByPrice(@RequestParam BigDecimal price) {
		return new ResponseEntity<List<Laptop>>(lRepo.findByPrice(price), HttpStatus.OK);
	}
}

We have auto wired the LaptopRepository using @Autowired annotation. We have created 3 handler methods, getLaptopsByName(), getLaptopsByBrand() and getLaptopsByPrice() which will call the repository methods findByName(), findByBrand() and findByPrice() respectively.

Run the app

Run the application using the below maven command –

mvn spring-boot:run

Open the browser and enter the following URL –

  • http://localhost:8080/laptops/name?name=dell inspiron
[
    {
        "id": 1,
        "name": "Dell Inspiron",
        "description": "dell company laptop",
        "brand": "Dell",
        "price": 60000.0
    }
]
  • http://localhost:8080/laptops/brand?brand=apple
[
    {
        "id": 3,
        "name": "Macbook Air",
        "description": "apple company laptop",
        "brand": "Apple",
        "price": 85000.0
    },
    {
        "id": 4,
        "name": "Macbook Pro",
        "description": "apple company laptop",
        "brand": "Apple",
        "price": 160000.0
    }
]
  • http://localhost:8080/laptops/price?price=50000.00
[
    {
        "id": 5,
        "name": "HP",
        "description": "hp company laptop",
        "brand": "HP",
        "price": 50000.0
    },
    {
        "id": 6,
        "name": "Lenovo",
        "description": "lenovo company laptop",
        "brand": "Lenovo",
        "price": 50000.0
    }
]

References




Post Views: 59,371

Related posts:

  1. Spring Data JPA findBy Multiple Columns with Example
  2. Spring Data JPA Contains Query with Example
  3. Spring Data JPA Greater than with Example
  4. Spring Data JPA Query Methods In with Examples
  5. Spring Data JPA Between Date Example
  6. Spring Data JPA Query Methods Like with Example
  7. Spring Data JPA Sort by Date with Example
  8. Spring Data JPA Pagination with Example
  9. Spring Data JPA Sort Multiple Columns with Examples
  10. Spring Boot Data JPA JPQL Select Query

Bushan Sirgur

Hey guys, I am Bushan Sirgur from Banglore, India. Currently, I am working as an Associate project in an IT company.

This Post Has 2 Comments

  1. Arpan Mandal April 19, 2022 Reply

    my repo object is null so there is a null pointer exception,how can i handle those

  2. Margarita February 21, 2023 Reply

    I got null pointer exception too.

Leave a Reply