How to make APIs with Spring Boot: read from a MySQL database

How to make APIs with Spring Boot: read from a MySQL database


4 min read

Before we start coding


  1. Make a database
  2. Connect database to our project
  3. Use GET calls to read from SQL database with Spring Boot
  4. Source code on Github
  5. Architecture:


For any questions you can ask me on Twitter @ tekbog

Set up the database (and make sure it's running)

In my case I'm gonna use MySQL Workbench to create a database called "mydb" and then add a table called "my_table"


Once that's done run this query to put some data in your database:

USE mydb;

CREATE TABLE my_table 
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL

INSERT INTO my_table (name, email) VALUES ("random_name", "");
INSERT INTO my_table (name, email) VALUES ("random_name1", "");
INSERT INTO my_table (name, email) VALUES ("random_name2", "");

Starting with Spring Boot

Connect your database to the Spring Boot app by changing


The port is default, make sure it's the same. You can check the port number when you start the Spring Boot app.

image.png The username and password pertain to your MySQL database.


And the dependencies are:






You can use Spring Initializr as well to set up your project, it's highly recommended.


In order to make calls to the server I'm going to be using Postman Although there are many alternatives.

Architecture and code

image.png The "folders" are called packages, once you create them: inside you can make classes, interfaces, etc.


UserData class makes it possible to interact with the database

package com.example.sqldbexample.entity;

import lombok.Getter;

import javax.persistence.*;

//lombok getter so we don't have to write getters
//The entity interacts with the database
@Table(name = "my_table")
public class UserData {
    //an id is always needed to index the data
    //the annotation GeneratedValue allows the data to be generated automatically
    //and we add the name of the columns where the data goes
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", nullable = false)
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "email")
    private String email;

To begin we import Lombok so we don't have to write the getters for the class. Afterwards we define the class as Entity and map the Java objects to the database we are accessing. The most confused annotation could be GeneratedValue: we always need an id and in this case we made our id to be generated automatically. Spring annotations have the following syntax:

@AnnotationName(parameter="parameter value")


The Repository interface is a DAO (data access object) bean that simplifies our interaction with SQL thanks to JPA, the interface takes 2 parameters: the type of object and the type of id, after which it will allow you to use its extended methods:


In our case we want to interact with the entity object we have created called UserData and the id is Long type.

Note that Repository annotation also indicates that this is a type of bean we can use anywhere in our project.

package com.example.sqldbexample.repository;

import com.example.sqldbexample.entity.UserData;
import org.springframework.stereotype.Repository;

//repository to interact with the entity
//we use the JpaRepository because of all its methods
public interface UserDataRepository extends JpaRepository<UserData, Long> {


UserDataService interface is gonna allow us to use the repository we have just created to get the data we want

package com.example.sqldbexample.service;

import com.example.sqldbexample.entity.UserData;

import java.util.List;

//interface to handle the entity UserData
public interface UserDataService {
    List<UserData> getUserData();

Now the implementation is as follows:

package com.example.sqldbexample.service;

import com.example.sqldbexample.entity.UserData;
import com.example.sqldbexample.repository.UserDataRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

public class UserDataServiceImpl implements UserDataService{

    //get the repository bean
    UserDataRepository userDataRepository;

    //we use the repository bean to get access to all the methods
    //like findAll
    public List<UserData> getUserData() {
        return userDataRepository.findAll();

Thanks to the JPA repository we get access to all its methods without having to implement any of them, it just works with our MySQL database. So in order to get all the users from the table we use .findAll() method.


You can read my previous article if you are unfamiliar with the Controller

//@RestController annotation will make this class the controller
//meaning that it's listening to htttp://localhost:8080 calls
//and replying with whatever endpoints you have made
public class Controller {

    private UserDataService userDataService;

    public List<UserData> getData(){
        return userDataService.getUserData();

The main difference is the fact that we add RequestMapping so everything goes to api/v1 path after localhost:port/. Putting the version of your API is good practice.

To sum it up we use the UserDataService bean (service), the Autowired annotation allows us to get the bean through Inversion of Control (Spring magic) and use that interface's implementation without calling it ourselves.

Test that everything works

Start the server, make sure your database is running and then using Postman call localhost:8080/api/v1/getdata

you should get this back: image.png