Spring Data JPA / CrudRepository / findBy Methods

We have already developed our entities and the most basic repositories in the previous posts. You can have a look here if you did not read the previous chapters. Now is the time to move on and develop our basic application a bit further and make it one step more advanced. In this post I want to reveal some functionalities of CrudReposiory interface to you.

What we will learn after reading this post is how to write basic methods to select the data from a table using CRUD Repository without writing sql code like where clauses, count, like etc.

Filling up the tables

First I do not like handling ~10 records in the database. Instead I want to fill tables using a faker library. After just googling “java faker”, I found a library and added it to my pom.xml file. It will give us some fake data to fill our tables.

...
		<dependency>
			<groupId>com.github.javafaker</groupId>
			<artifactId>javafaker</artifactId>
			<version>1.0.2</version>
		</dependency>
...

I changed my ApplicationListener.java file to use the faker library, added 1K people and 100 cities.

package com.enginaar.spring.data.listener;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.event.EventListener;
import org.springframework.stereotype.Component;

import com.enginaar.spring.data.domain.City;
import com.enginaar.spring.data.domain.Person;
import com.enginaar.spring.data.repository.CityRepository;
import com.enginaar.spring.data.repository.FlightRepository;
import com.enginaar.spring.data.repository.PersonRepository;
import com.github.javafaker.Faker;

@Component
public class ApplicationListener {

	@Autowired
	private PersonRepository persons;
	@Autowired
	private CityRepository cities;
	@Autowired
	private FlightRepository flights;
	Faker faker = new Faker();

	@EventListener(ApplicationReadyEvent.class)
	public void doSomethingAfterStartup() {
		initPeople();
		initCities();
	}

	private void initCities() {
		for (int i = 0; i < 50; i++) {
			City a = new City();
			a.setName(faker.address().city());
			cities.save(a);
		}
	}

	private void initPeople() {
		for (int i = 0; i < 1_000; i++) {
			Person p = new Person();
			p.setName(faker.name().firstName());
			p.setLastName(faker.name().lastName());

			persons.save(p);
		}
	}
}

When you run the application it will take a while to insert all data into the tables.

Well, I think we are ready to proceed.

You will remember that we have a couple of repository interfaces per table. If we want to select some data within a table we are expected to code the functionality in corresponding repository. For example, after my application was up, I checked Person table manually if I had any duplicate records. Let’s have a look at our queries and see the results.

select count(*), c.name from person c 
group by c.name 
having count(c.name)> 1

When I run this query I see 104 different duplicate records with the same name names. Please keep in mind that I am only evaluating name field. Assume that we do not want to have any duplicated names in the table. What are the different approaches to have this functionality.

• We can simply add a unique index on the name column of person table
• Before inserting the record, we can check if we have a records with the same name.
• Before inserting it, we can count all records with a query. If we records, we can skip it.

I want to implement this functionality using queries manually.

In the past, we were developing data access layer either using sql queries or implementing these interfaces using hibernate. Today Spring Data JPA provides us CrudRepository and takes care various types of queries without writing sql code backing hibernate on our behalf.

Developing queries

When we would like to develop a query using a field name simply we can follow some rules and let Spring handle the implementation. For example in the following code snippet you can see the PersonRepository has two methods

• List<Person> findByName(String name)
• List<Person> findByLastname(String surname)

package com.enginaar.spring.data.repository;

import java.util.List;

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

import com.enginaar.spring.data.domain.Person;

@Repository
public interface PersonRepository extends CrudRepository<Person, Long> {
	List<Person> findByName(String name);
	List<Person> findByLastName(String lastname);
}

Althought we do not have any implementation of this PersonRepository interface Spring realizes it as a Repository looking at the the @Repository annotation and CrudRepository as ancestor class. It creates the implementation of this interface on the fly while the application is starting.

Let’s use this method in the code.

package com.enginaar.spring.data.listener;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.event.EventListener;
import org.springframework.stereotype.Component;

import com.enginaar.spring.data.domain.City;
import com.enginaar.spring.data.domain.Person;
import com.enginaar.spring.data.repository.CityRepository;
import com.enginaar.spring.data.repository.FlightRepository;
import com.enginaar.spring.data.repository.PersonRepository;
import com.github.javafaker.Faker;

@Component
public class ApplicationListener {

	@Autowired
	private PersonRepository persons;
	@Autowired
	private CityRepository cities;
	@Autowired
	private FlightRepository flights;
	Faker faker = new Faker();

	@EventListener(ApplicationReadyEvent.class)
	public void doSomethingAfterStartup() {
		initPeople();
		initCities();

	}

	private void initCities() {
		for (int i = 0; i < 50; i++) {
			City a = new City();
			a.setName(faker.address().city());
			cities.save(a);
		}

	}

	private void initPeople() {
		for (int i = 0; i < 1000; i++) {
			String name = faker.name().firstName();
			List<Person> prsList = persons.findByName(name);
			if(prsList.size() > 0)
				continue;
			Person p = new Person();
			p.setName(name);
			p.setLastName(faker.name().lastName());

			persons.save(p);
		}
	}
}

In initPeople method, we inserted 1000 people into the table with our listener class. While inserting the data, we are checking if a record with the same name existed in the table or not. If the record is present, we skip inserting the record, otherwise we go for inserting a new one.

We could implement the same functionality by counting the records as I mentioned earlier. I added a new method to the repository named countByName in the following section.

package com.enginaar.spring.data.repository;

import java.util.List;

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

import com.enginaar.spring.data.domain.Person;

@Repository
public interface PersonRepository extends CrudRepository<Person, Long> {
	List<Person> findByName(String name);
	List<Person> findByLastName(String lastname);
	
	int countByName(String name);
}

private void initPeople() {
		for (int i = 0; i < 1000; i++) {
			String name = faker.name().firstName();
			//List<Person> prsList = persons.findByName(name);
			//if(prsList.size() > 0)
			if(persons.countByName(name) > 0)
				continue;
			Person p = new Person();
			p.setName(name);
			p.setLastName(faker.name().lastName());

			persons.save(p);
		}

select person0_.id as id1_2_, person0_.last_name as last_nam2_2_, person0_.name as name3_2_ from person person0_ where person0_.name=? -- findByName
select count(person0_.id) as col_0_0_ from person person0_ where person0_.name=? -- countByName

If we use findBy prefix with a column name (from entity class), Spring generates a query to select the records. You can see the generated sql query by Spring in previous code snippet in the first line commented with “findByName”.

If we use countBy prefix with a column name (from entity class), Spring does generate query again to calculate the count. You can see it in line 2 commented with “countByName”.

There are bunch of different rules to make our work easier. You can have a look here to get more information about CrudRepository and provided rules.

You can find some samples in following snippet.

...
  List<Person> findByNameAndLastName(String name, String lastname);
  List<Person> findByNameOrLastName(String name, String lastname);
  List<Person> findByNameStartingWith(String name);
..

For further information, you can have a look in the last section of this page.

Next steps

CrudRepository is very useful when we are talking about basic queries. When the topic come to a bit complicated queries like joins we have more advanced approaches which I am planning to write another post about it. Until that time, keep following.

Hope to see you later.

References

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation

https://docs.spring.io/spring-data/data-commons/docs/1.6.1.RELEASE/reference/html/repositories.html