How to Integrate MySQL with Springboot Java?

Photo by Clément Hélardot on Unsplash

Here is how we can integrate MySQL in a Springboot Java Project.

MySQL is the most used relational database and learning how to integrate it with spring-boot is very necessary in any Software development firm.

I am using an example of a simple spring-boot application with Maven as a dependency manager.

Add the Dependencies

We need to add the dependency for Spring JPA (for all the methods required for interacting with the database) and MySQL driver dependency.

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

Add Connection Details

We will use application.yml to add the MySQL connection details. Make sure MySQL is up and running. In my case, MySQL is running on my local, so here is what we need to add.

spring:  
  datasource:  
    url: jdbc:mysql://localhost:3306/mydb  
    username: root  
    password: saquib786  
    driver-class-name: com.mysql.cj.jdbc.Driver  
  
  jpa:  
    hibernate:  
      ddl-auto: update  
      naming.physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl  
    show-sql: true

naming.physical-strategy is used to make sure that MySQL driver does not change the Capital letters in field names to underscore. For example, if I have a field name userEmail, it will try to look for user_email, which leads to conflict.

Create Entity Class

Class annotated with @Entity acts analogous to Table in MySQL. So the fields that you add here, will be columns in the concerned table.

@Entity  
public class UserInfo {  
  
    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private int userId;  
  
    private String userName;  
    private String userEmail;  
    private String userPassword;
 //Getter and Setters
}

Add the JPA Repository

We need to create an Interface that extends JPARepository to get all the methods provided by JPA out of the box.

public interface UserRepository extends JpaRepository<UserInfo, Integer> {  
  
    UserInfo findByUserEmail(String userEmail);  
}

Create a Service or Controller

We now need to call the methods, to interact with the database. We can simply create a controller and @Autowired the repository service to interact with the database.

@Autowired  
private UserRepository userRepository;  
  
@RequestMapping(value = "/add", method = RequestMethod.POST)  
public String addUser(@RequestBody UserInfo userInfo) {  
    try {  
        userRepository.save(userInfo);  
        return "User added successfully";  
    }catch(Exception e) {  
        return "Exception occured while saving" + e.toString();  
    }  
}  
  
@RequestMapping(value = "/getById", method = RequestMethod.GET)  
public UserInfo getUserById(@RequestParam Integer id){  
    Optional<UserInfo> userInfo = userRepository.findById(id);  
    if(userInfo.isPresent()) {  
        return userInfo.get();  
    }else {  
        return null;  
    }  
}

This is it for the basics. There are many methods and frameworks that we can use for more complex tasks.

I hope this helps you. If it did, clap me a thousand times (joking, a few tens would do), leave me a comment (it will encourage me a lot), click the follow button and subscribe to get email notifications.

Until Next Time

Saquib Aftab