In the first part, we covered some very basic things we can do with Spring Data repositories. In this part, we will learn how to make more complex queries. By that I mean how to find data by entity field or make a count. You will be amazed how easy it is with Spring Data.
Entities
We will use Post entity from our previous part, update it and add an entity called User.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
package models; import java.util.Date; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.ManyToOne; import javax.persistence.Table; @Entity @Table(name = "posts") public class Post { @Id @GeneratedValue(strategy = GenerationType.AUTO) public Long id; @Column(nullable = false) public String title; @Column(nullable = false, unique = true) public String url; @Column(nullable = false, name = "created_at") public Date created_at; @Column(name = "is_active") public boolean isActive; @ManyToOne(optional = false, fetch = FetchType.LAZY) public User user; } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
package models; import java.util.Date; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.OneToMany; import javax.persistence.Table; @Entity @Table(name = "users") public class User { @Id @GeneratedValue(strategy = GenerationType.AUTO) public Long id; @Column(nullable = false) public String username; @OneToMany(mappedBy = "user", fetch = FetchType.LAZY) public Set<Post> posts; } |
As you can see, we have defined relation between our entities. Every user can have multiple posts and each post has only 1 user.
Tasks
Let’s imagine we have a situation where we want to create a simple blogging system. We have to be able to:
1. find all active posts
2. find a post by an url
3. find all posts by a user
4. count all active posts
1. Find all active posts
We will use the PostRepository we have defined in our first part. By simply adding a method to the repository, it will generate the right code and map everything to a SQL. Because CrudRepository already has few basic methods prebuilt, we don’t need to add a method to find all posts. Instead, we can use findAll method.
But to find all active posts we have to define our custom method. Actually, it’s very simple.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
package repositories; import java.util.Set; import org.springframework.data.repository.CrudRepository; import org.springframework.stereotype.Repository; import models.Post; @Repository public interface PostRepository extends CrudRepository<Post, Long> { public Set<Post> findByIsActiveTrue(); } |
This is it. This is the whole magic. One short line of code. But how it actually works? Spring Data will build the query based on method name and method return type. It will split the method name. In our case into find, by, isactive, true. First part defines to make a select query, the second indicates we want to filter, the third field name and fourth the value of the field. But be careful, defining field value after specifying the field name it will only work for booleans. For other field types, you need to pass the value as method argument. One great this is also that we can add multiple fields.
2. Find post by an url
Continuing the though from the previous section, we can have method build from different fields. For example, let’s load a post by it’s url. We have to update our repository.
|
1 2 3 4 5 6 |
@Repository public interface PostRepository extends CrudRepository<Post, Long> { public Set<Post> findByIsActiveTrue(); public Post findByUrl(String url); } |
Again, if we look at the method name, we will see that we are finding a post by it’s url. Because the url is a String, we have to pass the value as a method argument. Because return type is a Post, it will return one post. In case if the query returns multiple rows, then the exception will be thrown. Be careful that when you expect only 1 record, that you query by some unique field.
But actually, our blog system has to return a post by url and be active. We could have a code where we load the post by url and then check if isActive or not. Instead, we can do this in one query.
|
1 2 3 4 5 6 |
@Repository public interface PostRepository extends CrudRepository<Post, Long> { public Set<Post> findByIsActiveTrue(); public Post findByUrlAndIsActiveTrue(String url); } |
We are now querying the database by 2 field: url and isActive. When we use different fields in a method name, all of them are joined by AND. We cannot use OR. For that, we have to use some other approach (we will explain it in another tutorial).
3. Find all posts by a user
Every user has a username. Our task is to find all posts by a user or more specific, find all posts by a username. Writing a method name is actually the same, we just need to include the relation name. Again, we update our PostRepository.
|
1 2 3 4 5 6 7 |
@Repository public interface PostRepository extends CrudRepository<Post, Long> { public Set<Post> findByIsActiveTrue(); public Post findByUrlAndIsActiveTrue(String url); public Set<Post> findByUserUsername(String username); } |
Method name has to include the relation name. When we defined it in the entity, we have to use the same name in the method. If we change it in the entity, we also have to change the method name. It may look complicated and no really robust to changes, but there is no other way for Spring Data to know how to correctly build the query. Of course, as we mentioned it few times already, in the next part we will learn how we can actually use custom queries to help out Spring Data with building the native SQL query.
Once we define the relation in the method name, everything else is the same. We again filter by field name, we can or use multiple fields. But remember, for each relation field, we have to prepend the name with the relation name.
4. Count all active posts
For the last task, we have to count all active posts. CrudRepository already has a method called count(), but it will count all posts. We could use findByIsActiveTrue() method to find all posts and get a populated Set
Don’t do that. Sure, it works and it might even work in the production for a small number of posts, but in case of a larger dataset, it’s not a good practice. We have to fetch all the records, populate Set
Instead, we will use count which maps to SQL count. It’s much much faster and consumes much less resources. Before, we were finding records, so we prepended every method name with find. If we want to count, we have to do what? You are right, prepend every method name with count. Let’s for the last time update our PostRepository.
|
1 2 3 4 5 6 7 8 |
@Repository public interface PostRepository extends CrudRepository<Post, Long> { public Set<Post> findByIsActiveTrue(); public Post findByUrlAndIsActiveTrue(String url); public Set<Post> findByUserUsername(String username); public Long countByIsActiveTrue(); } |
There are few differences compared to other method names. First is return type. It has to be a Long, so it will bind a row count to it (Integer can be too small). As we mentioned before, we start method with count and then define the field filters. It’s that simple.
Further reading
You can read all about how to correctly build the method name using the official docs. It has everything explained really nicely and it also demonstrates some additional keywords. I strongly recommend it.
Part 3 – What more will we learn?
In the next part, we will how we can make even more complex queries by using @Query annotation. @Query annotation enables us to write HQL, which is very similar to SQL but has a compile time checking. Another thing we will learn is how to extend Repository and use PersistanceManager to build super complex queries. We will create custom methods and insert them into repositories. It’s a really cool and advanced feature, so stay tuned.
