Before we start coding
TL;DR
- Make a database
- Connect database to our project
- Use GET calls to read from SQL database with Spring Boot
- Source code on Github
- 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
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
);
INSERT INTO my_table (name, email) VALUES ("random_name", "random@random.com");
INSERT INTO my_table (name, email) VALUES ("random_name1", "random1@random.com");
INSERT INTO my_table (name, email) VALUES ("random_name2", "random2@random.com");
Starting with Spring Boot
Connect your database to the Spring Boot app by changing
application.properties
The port is default, make sure it's the same. You can check the port number when you start the Spring Boot app.
The username and password pertain to your MySQL database.
spring.datasource.url=jdbc:mysql:
spring.datasource.username=root
spring.datasource.password=password
And the dependencies are:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
You can use Spring Initializr as well to set up your project, it's highly recommended.
Postman
In order to make calls to the server I'm going to be using Postman
Although there are many alternatives.
Architecture and code
The "folders" are called packages, once you create them: inside you can make classes, interfaces, etc.
Entity
UserData class makes it possible to interact with the database
package com.example.sqldbexample.entity;
import lombok.Getter;
import javax.persistence.*;
@Getter
@Entity
@Table(name = "my_table")
public class UserData {
@Id
@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")
Repository
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.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface UserDataRepository extends JpaRepository<UserData, Long> {
}
Service
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;
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;
@Service
public class UserDataServiceImpl implements UserDataService{
@Autowired
UserDataRepository userDataRepository;
@Override
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.
API
You can read my previous article if you are unfamiliar with the Controller
@RestController
@RequestMapping("api/v1")
public class Controller {
@Autowired
private UserDataService userDataService;
@GetMapping("/getdata")
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:
