4. More on CrudRepository

In the previous post, we used the CrudRepository interface. We only used the methods already provided by the interface. In this post, we look at how we can extend the interface with custom queries. Let's go back to our UserEndpoint. In the GET method, we can currently get the details of a user given his id. This is an impractical scenario. Most users' would not know what their id is. It is an internal identifier generated by our service and it makes no sense for our users.
Let's say we want to extend the existing GET/DELETE interface to allow query by id, username, and email address. Since we intend to query the table using the username and email columns, we need some kind of index on those columns. Since these fields are expected to be unique, we put a unique constraint in the @Table annotation.

	package in.springframework.blog.tutorials;
	import javax.persistence.*;
	@Entity
	@Table(name = "user",
	uniqueConstraints =
	{
	@UniqueConstraint(name = "uq_email", columnNames = {"email"}),
	@UniqueConstraint(name = "uq_username", columnNames = {"username"})
	})
	public class User {
	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private Long id;
	private String fullname;
	private String username;
	private String password;
	private String email;
	public Long getId() {
	return id;
	}
	public void setId(Long id) {
	this.id = id;
	}
	public String getFullname() {
	return fullname;
	}
	public void setFullname(String fullname) {
	this.fullname = fullname;
	}
	public String getUsername() {
	return username;
	}
	public void setUsername(String username) {
	this.username = username;
	}
	public String getPassword() {
	return password;
	}
	public void setPassword(String password) {
	this.password = password;
	}
	public String getEmail() {
	return email;
	}
	public void setEmail(String email) {
	this.email = email;
	}
	}

view raw User.java hosted with ❤ by GitHub

When this change is deployed, the corresponding changes in database schema would be as below.

mysql> desc user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | bigint(20)   | NO   | PRI | NULL    |       |
| email    | varchar(255) | YES  | UNI | NULL    |       |
| fullname | varchar(255) | YES  |     | NULL    |       |
| password | varchar(255) | YES  |     | NULL    |       |
| username | varchar(255) | YES  | UNI | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` bigint(20) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `fullname` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email` (`email`),
  UNIQUE KEY `uq_username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

As we can see from the schema both the columns have unique constraints attached to them. The next step is to add methods in the UserRepository.java to query the user for a given username or email.

	package in.springframework.blog.tutorials;
	import org.springframework.data.jpa.repository.Query;
	import org.springframework.data.repository.CrudRepository;
	import org.springframework.data.repository.query.Param;
	import java.util.Optional;
	public interface UserRepository extends CrudRepository<User, Long> {
	@Query("SELECT u FROM User u where u.email=:email")
	Optional<User> findUserByEmail(@Param("email") String email);
	@Query("SELECT u FROM User u where u.username=:username")
	Optional<User> findUserByUsername(@Param("username") String username);
	}

view raw UserRepository.java hosted with ❤ by GitHub

Now that we have all the required changes in place, we modify the UserEndpoint to handle the changes. We first create a private method retrieveUser which will retrieve a user from repository given an id, username or email.

	private Optional<User> retrieveUser(String idOrUserNameOrEmail) {
	try {
	Long id = Long.parseLong(idOrUserNameOrEmail);
	Optional<User> optionalUser = userRepository.findById(id);
	if (optionalUser.isPresent()) {
	return optionalUser;
	}
	}
	catch(NumberFormatException e) {
	}
	Optional<User> optionalUser = userRepository.findUserByEmail(idOrUserNameOrEmail);
	if (optionalUser.isPresent()) {
	return optionalUser;
	}
	optionalUser = userRepository.findUserByUsername(idOrUserNameOrEmail);
	return optionalUser;
	}

view raw RetrieveUserMethod.java hosted with ❤ by GitHub

In this method, we take an argument of type String, we first assume it to be the id if id fails we try with email and then with the username.

$ curl -X GET http://localhost:8080/user/jdoe@example.com
{"id":6,"fullname":"John Doe","username":"jdoe","password":"JohnDoe123","email":"jdoe@example.com"}

Here is the complete UserEndpoint class after modification.

	package in.springframework.blog.tutorials;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.http.MediaType;
	import org.springframework.web.bind.annotation.*;
	import javax.persistence.EntityNotFoundException;
	import java.util.Optional;
	@RestController
	@RequestMapping("/user")
	public class UserEndpoint {
	@Autowired
	private UserRepository userRepository;
	@RequestMapping(value = "/{idOrUserNameOrEmail}", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
	public Optional<User> getUser(@PathVariable("idOrUserNameOrEmail") String idOrUserNameOrEmail) {
	return retrieveUser(idOrUserNameOrEmail);
	}
	@RequestMapping(method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
	public Optional<User> getUser(@RequestBody User user) {
	return Optional.of(userRepository.save(user));
	}
	@RequestMapping(value = "/{idOrUserNameOrEmail}", method = RequestMethod.DELETE, produces = MediaType.APPLICATION_JSON_VALUE)
	public Optional<User> deleteUser(@PathVariable("idOrUserNameOrEmail") String idOrUserNameOrEmail) {
	Optional<User> optionalUser = retrieveUser(idOrUserNameOrEmail);
	if (optionalUser.isPresent()) {
	userRepository.delete(optionalUser.get());
	return optionalUser;
	}
	throw new EntityNotFoundException(String.format("%s user not found.", idOrUserNameOrEmail));
	}
	@RequestMapping(value = "/{idOrUserNameOrEmail}",
	method = RequestMethod.PATCH,
	produces = MediaType.APPLICATION_JSON_VALUE)
	public Optional<User> patchUser(@PathVariable("idOrUserNameOrEmail") String idOrUserNameOrEmail,
	@RequestBody User user) {
	Optional<User> optionalUser = retrieveUser(idOrUserNameOrEmail);
	if (optionalUser.isPresent()) {
	User storedUser = optionalUser.get();
	if (user.getEmail() != null) {
	storedUser.setEmail(user.getEmail());
	}
	if (user.getFullname() != null) {
	storedUser.setFullname(user.getFullname());
	}
	if (user.getPassword() != null) {
	storedUser.setPassword(user.getPassword());
	}
	return Optional.of(userRepository.save(storedUser));
	}
	throw new EntityNotFoundException(String.format(String.format("User with id %s is not found", idOrUserNameOrEmail)));
	}
	private Optional<User> retrieveUser(String idOrUserNameOrEmail) {
	try {
	Long id = Long.parseLong(idOrUserNameOrEmail);
	Optional<User> optionalUser = userRepository.findById(id);
	if (optionalUser.isPresent()) {
	return optionalUser;
	}
	}
	catch(NumberFormatException e) {
	}
	Optional<User> optionalUser = userRepository.findUserByEmail(idOrUserNameOrEmail);
	if (optionalUser.isPresent()) {
	return optionalUser;
	}
	optionalUser = userRepository.findUserByUsername(idOrUserNameOrEmail);
	return optionalUser;
	}
	}

view raw UserEndpoint.java hosted with ❤ by GitHub