How to connect to database from a REST API?

Web applications store data in a database.

Doing so , data is stored permanently somewhere so that you can process it later.

Even if your application server goes down , you still have the data in your database.

Spring makes it easy to connect to any database.

You require very minimal code to do this.

In the previous post we saw how to create a REST API and used in memory data structure (array list)

In this post we will carry on from there and replace the temporary data store (array list) with a permanent data store (MySQL)

You need to do the following steps for the same:

1. Set up the database and get the database URL and credentials
2. Add required dependencies in the spring boot project
3. Configure database details in application.properties file
4. Create the entity class
5. Create a CRUD Repository in the spring boot project
6. Inject the CRUD repository where you want to do database operations
7. Invoke CRUD operations on the the CRUD repository

STEP 1: Set up the database

We will not go deep into how to set up a database here.

Let us assume that you have installed MySQL database in your local, created a schema and you have the URL and credentials with you.

STEP 2: Add required dependencies in the spring boot project

We need two dependencies for doing database operations.

One is spring data jpa library and another is the driver library for MySQL.

The driver library will be different for each database but the spring data library will remain the same.

Here are the two dependencies:

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>


<dependency>

<groupId>com.mysql</groupId>

<artifactId>mysql-connector-j</artifactId>

<scope>runtime</scope>

</dependency>

Notice that the mysql connector library is a runtime dependency so it is not required for compiling the project.

STEP 3: Configure database details in application.properties file

Configure the database details in the application.properties file.

Here is an example configuration:

spring.jpa.hibernate.ddl-auto=update

spring.datasource.url=jdbc:mysql://localhost:3306/store

spring.datasource.username=username

spring.datasource.password=password

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.jpa.hibernate.ddl-auto

This property informs Spring whether to create the tables / update the tables when the application starts.

In the above example I have included “update” which means existing database will be updated if there is any change in table or new tables or created.

spring.datasource.url

This is the URL of your database , if you have installed MySQL in your local it will be jdbc:mysql://localhost:3306 followed by your schema name (In the above example schema name is store)

spring.datasource.username

This is the username of your database

spring.datasource.password

This is the password of your database

spring.datasource.driver-class-name

This is the database driver class name , it is specific to each database , in the above example it is given for MySQL

STEP 4: Create an entity class

Spring boot uses hibernate in the background.

Hibernate is a ORM (Object Relational Mapping) library.

So to represent a table in the database you create a corresponding Java class which is called an entity.

Each attribute in the entity class will correspond to the specific column in the table represented by the entity class.

In our case , we had created a model object to represent a Product in the last post.

We will just make some changes to it.

First,

We will annotate the class with @Entity annotation.

This would mean that Product class represents “product” table in the database.

You don’t have to specify the table name.

Spring automatically uses the model class name as the table name except that the first letter will be converted to small letter (Product -> product)

@Entity
public class Product{
}

You can use @Table annotation in addition to @Entity annotation on top of the entity class if you want to specify a different table name.

Second,

You should specify which is the primary key in the table.

This can be denoted by @Id annotation.

We will choose the product id attribute as the primary key.

We will also add one more annotation @GeneratedValue to indicate how the id is generated.

@Id

@GeneratedValue(strategy=GenerationType.AUTO)

private int id;

As you notice , we specify a strategy in @GeneratedValue annotation.

It tells hibernate which strategy to use to generate the primary key.

In the above case it is mentioned as AUTO which means the database itself will choose what strategy to follow.

In my case , MySQL database server created a new table product_seq with the latest value of the id in a column named nextval.

That is the minimal configuration you need to do for the entity class.

New columns are created for the other attributes in the entity class.

The name of the columns match with the name of the attributes in the entity class.

If you want to specify a different name you can use @Column annotation.

Here is the updated model class to represent the entity :

@Entity
public class Product {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

	private String name;

	private int quantity;

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
	        return name;
	}

	public void setName(String name) {
  	       this.name = name;
	}

	public int getQuantity() {
		return quantity;
	}

	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}
}

Very minimal change to represent a table in the database!

This can get complicated when you want complex logic like if you want to represent parent-child relationship , want to write complex queries etc.

For this example though , we will keep it minimal.

You can always build on the basic knowledge to keep learning complex topics on top of it.

STEP 5: Create a CRUD repository

Believe me when I say you don’t have to write any extra code to implement CRUD operations in Spring Boot!

Just create a blank interface extending CrudRepository provided by Spring and automatically all the code related to CRUD operations are injected!

Here is the repository I created:

public interface ProductRepository extends CrudRepository<Product, Integer> {




}

You need to pass the entity class name and the data type of the primary key as generic parameters to the interface as above.

Spring CrudRepository interface has inbuilt methods for each CRUD operations:

save() and saveAll() for both Create (C) and Update (U)

findAll() and findById() for Read(R)

deleteById() for Delete

To query by a particular column name you can append the attribute name representing the column next to findBy keyword , include it in the repository you created and Spring Data will automatically create the query for you !

Example : findByName(String name) method should be declared in your custom repository to find all the products by name.

Here is the updated repository :

public interface ProductRepository extends CrudRepository<Product, Integer> {

   public List<Product> findByName(String name);

}

STEP 6: Inject the CRUD repository

Inject the Crud Repository just created in the class you need to do database operations.

In our case , we will inject in the controller class itself.

In production applications your application will be more layered and you wouldn’t do database operations in a controller class.

But for this example we will do it the controller class itself.

@Autowired
private ProductRepository productRepository;

STEP 7: Invoke CRUD operations

We will migrate our REST API operations from using ArrayList datastructure to Relational database.

Here is the code with ArrayList:

@RestController
public class RestAPI {

	List<Product> products = new ArrayList<Product>();

@GetMapping("/product")
public List<Product> getAllProducts() {
       	return products;
}
@GetMapping("/product/{id}")
public Product getProduct(@PathVariable int id) {
	for (Product p : products) {
		if (p.getId() == id) {
		return p;
		}
	}
	return null;
}

@GetMapping("/api/product")
public Product getProductById(@RequestParam("id") int id) {
		for (Product p : products) {
		if (p.getId() == id) {
			return p;
			}
		}
		return null;
	}

@PostMapping(path = "/product")
public List<Product> addProduct(@RequestBody Product product) {
		product.setId(products.size() + 1);
		this.products.add(product);
		return products;
	}

@PutMapping("/product")
public List<Product> updateProduct(@RequestBody Product product) {
		this.products.forEach(p -> {
		if (p.getId() == product.getId()) {
		p.setName(product.getName());
		p.setQuantity(product.getQuantity());
			}
		});
		return products;
	}

@DeleteMapping("/product/{id}")
public List<Product> deleteProduct(@PathVariable int id) {
		Product toRemove = null;
		for (Product p : products) {
			if (p.getId() == id) {
				toRemove = p;
			}
		}
		if (toRemove != null) {
			this.products.remove(toRemove);
		}
		return products;
	}
}

We will make a few changes here.

As mentioned in earlier step we will inject ProducRepository.

Then wherever we are dealing with arraylist we will replace it with CRUD operations provided by Spring CrudRepository :

@RestController
public class RestAPI {
	@Autowired
	private ProductRepository repository;

	@GetMapping("/product")
	public Iterable<Product> getAllProducts() {
		return this.repository.findAll();
	}



	@GetMapping("/product/{id}")
	public Product getProduct(@PathVariable int id) {
		return this.repository.findById(id).get();
	}

	@GetMapping("/api/product")
	public Product getProductById(@RequestParam("id") int id)    {

		return this.repository.findById(id).get();

	}


	@PostMapping(path = "/product")
	public void addProduct(@RequestBody Product product) {
		this.repository.save(product);
	}

	@PutMapping("/product")
        public void updateProduct(@RequestBody Product product) {
		this.repository.save(product);
	}

	@DeleteMapping("/product/{id}")
	public void deleteProduct(@PathVariable int id) {
		this.repository.deleteById(id);
	}

	@GetMapping("/product/name/{name}")
	public List<Product> getProductByName(@PathVariable String name) {
		return this.repository.findByName(name);
	}
}

Notice how minimal the changes are and how clean the repository methods are.

There is one extra REST API (“/product/name/{name}”) to showcase custom repository method (“findByName”)

Now if you run the application , the REST APIs work in the same way as before except that the data is now permanent in the database and even if you restart your application it is still there!

That’s it!

Code:

vijaysrj/springrestdata (github.com)