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.

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.

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.

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.

3. All the methods in endpoint

In the previous post, we defined a single method for an endpoint. In a typical web application, each endpoint will have multiple methods. To build an example of a complete endpoint, we need to have a persistence layer that can support these endpoints. To enable MySQL support for spring framework, we need to update pom.xml file with following dependencies.


Now we need to define data sources for MySQL. The first step is to create a database and user in the MySQL database. To do that log in to MySQL using the following command.

$ mysql -u root

Now run following set of commands to create a database named tutorial with username tutorial and password tutorial123.

Now we can initialize a data source in spring framework by putting properties in the application.properties file.

As we can see in the properties, the value ddl-auto is set to update. This will cause the database schema to be updated based on the definition of entity objects. This is a good practice while developing the system but normally should not be used while in production.
Let's say we want to define a simple endpoint that supports an entity called User. We define the entity in Java as below.

As we can see, the key to an entity class is the annotation @Entity.  You can also add an optional annotation @Table which will allow you control over the name of the table that gets created within the database. By default, if you omit the @Table annotation, the table name would be automatically generated.
The next step is to generate a Repository interface for accessing the entity from the database. We typically create a subclass of CrudRepository.

An empty repository interface provides sufficient functionality for this example. We will see how to add more functionality to repository class in a  different post. The Javadoc of CrudRepository provides details of all the methods that are readily available without any addition to the interface.
Let's run the server to see whether the server can connect to the database. As we run the server, we check what happened to our database.

$ mysql -u tutorial -ptutorial123 tutorial
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.12 Homebrew

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+--------------------+
| Tables_in_tutorial |
+--------------------+
| hibernate_sequence |
| user               |
+--------------------+
2 rows in set (0.01 sec)

mysql> desc user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | bigint(20)   | NO   | PRI | NULL    |       |
| email    | varchar(255) | YES  |     | NULL    |       |
| fullname | varchar(255) | YES  |     | NULL    |       |
| password | varchar(255) | YES  |     | NULL    |       |
| username | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> 

The database has the user table created mapping our entity class to a table. Now we have confirmed that the server is able to connect to our database server. Now we go back to the old endpoint class that we created in the previous post and create a similar endpoint class called UserEndpoint. In this class, we will define all the methods required for managing users.

$ curl -X POST \
>   http://localhost:8080/user \
>   -H 'Content-Type: application/json' \
>   -H 'Postman-Token: d0298724-86d2-49da-a0c0-067ed9e18e1e' \
>   -H 'cache-control: no-cache' \
>   -d '{
> "fullname":"John Doe",
> "username":"jdoe",
> "password":"JohnDoe123",
> "email":"johndoe@example.com"
> }'
{"id":1,"fullname":"John Doe","username":"jdoe","password":"JohnDoe123","email":"johndoe@example.com"}

We can also verify the record in the database.

$ mysql -u tutorial -ptutorial123 tutorial -e "select * from user"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------------------+----------+------------+----------+
| id | email               | fullname | password   | username |
+----+---------------------+----------+------------+----------+
|  1 | johndoe@example.com | John Doe | JohnDoe123 | jdoe     |
+----+---------------------+----------+------------+----------+

Now we can perform other operations on User record.

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

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

Now we can run the database query again and see that the record has vanished.

mysql> select * from user;
Empty set (0.00 sec)

Now we create the user again by running the POST command. Since we are using database autoincrement, the id of the user will be 2 now.
Now we change the email of the user using PATCH command.

curl -X PATCH   http://localhost:8080/user/2   -H 'Content-Type: application/json'   -H 'Postman-Token: d0298724-86d2-49da-a0c0-067ed9e18e1e'   -H 'cache-control: no-cache'   -d '{
"email":"jdoe@example.com"
}'
{"id":2,"fullname":"John Doe","username":"jdoe","password":"JohnDoe123","email":"jdoe@example.com"}

Here we have it. All the endpoints are functioning properly backed by MySQL as the database.