Adding a MySQL Database to our Spring Rest Api

Our previous tutorial we went through creating a API using a rest controller and the spring framework. Now we are going to add a database to our API so we will have a real API that can do CRUD operations to a database. The database we use is a MySQL database.

Lets get started. First we add some additional dependencies to our pom.xml file.

    <dependency&gt;
         <groupId&gt;org.springframework.boot</groupId&gt;
         <artifactId&gt;spring-boot-starter-data-jpa</artifactId&gt;
     </dependency&gt;
     
     <dependency&gt;
           <groupId&gt;mysql</groupId&gt;
           <artifactId&gt;mysql-connector-java</artifactId&gt;
    </dependency&gt;

Above we added the spring boot starter data jpa and the mysql connector for java dependencies. We save our pom.xml file and these dependencies load in.

Next we add some properties to our application. We create a application.properties file in the src/main/resources folder. We add the following to it.

spring.jpa.hibernate.ddl-auto=create
spring.datasource.url=jdbc:mysql://localhost:3306/wf_tutorials
spring.datasource.username=root
spring.datasource.password=

The first property spring.jpa.hibernate.ddl-auto=create we later changed the value to update. Why? Because it was creating our table every time we restarted the app. So I would have to look into that feature some more. The username and password properties are self explanatory.

Next we make some updates to our model.

@Entity
public class Event {

	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private Long id;
}

Most of our model properties remain the same. We add the @Entity annotation to define the class as a database model. We also set the primary key using @Id annotation and we set the auto increment feature using @GeneratedValue and we set the strategy to AUTO. It should be noted that we are using javax persistance library. So our file has the below imports. The reason I point this out is because there are other options besides these.

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

Next we create a repository interface. This interface we can use to interact with our model. It is a bridge between the model and the Controller. This is a web design pattern that you can research more on.

package com.boot.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.boot.model.Event;

public interface EventRepository extends JpaRepository<Event, Long&gt; {

}

You will notice that we have no code in the EventRepository class interface. It uses all the functions from the class it extends which is JpaRepository.

Lets go to the controller.

We add our repository at the beginning of the controller and autowire it in using the @Autowired annotation.

public class EventController {

	@Autowired
	private EventRepository eventrepo;

}

Our functions in the event controller has changed as well. To get all our events we do

@RequestMapping(value="events", method= RequestMethod.GET)
	public List<Event&gt; list(){
		return (List<Event&gt;) eventrepo.findAll();
	}

Above we using the findAll() function from our repository to get all the events.

To save an event we do.

@RequestMapping(value="events", method=RequestMethod.POST)
	public Event create(@RequestBody Event event) {
		return eventrepo.saveAndFlush(event);
	}

The eventrepo.saveAndFlush() allows us to save a new event from POST data we get from the API.

To update a record we do

@RequestMapping(value="events/{id}",method=RequestMethod.PUT)
	public Event update(@PathVariable Long id, @RequestBody Event event) {
		Event anotherEvent = eventrepo.getOne(id);
		anotherEvent.setName(event.getName());
		anotherEvent.setAuthor(event.getAuthor());
		anotherEvent.setDetails(event.getDetails());
		anotherEvent.setEventDate(event.getEventDate());
		anotherEvent.setEventTime(event.getEventTime());
		anotherEvent.setVenue(event.getVenue());
		return eventrepo.saveAndFlush(anotherEvent);
	}

We take the event from the API and set the values to the event we are updating. So we have to get the event we are updating first and then we update the records. We also call the eventrepo.saveAndFlush() to save the updated event.

To delete an event we do

@RequestMapping(value = "events/{id}", method = RequestMethod.DELETE)
	public String delete(@PathVariable Long id) {
		eventrepo.delete(id);
		return "Removed Event " + String.valueOf(id);
	}

We just call the eventrepo.delete() function with the id we get from the API.

So we have our event API completed. It connects to a database and does CRUD functions. Pretty awesome right?