How to Connect MySQL Database to your Spring Boot Application

Spring Boot is a popular framework for building microservices and web applications. One of its key features is its ability to easily connect to a variety of databases. In this blog post, we’ll walk through how to connect your Spring Boot application to a MySQL database. We will use Spring Data JPA to perform basic CRUD operations, and we’ll also take a look at how to handle transactions.



Setting Up MySQL

Before we get started, we need to have a MySQL database set up. If you don’t have one already, you can download and install MySQL from the official website. Once you have MySQL installed, log in to the MySQL command-line client using the following command:

mysql -u root -p

Next, create a database for your Spring Boot application by running the following command:

CREATE DATABASE spring_boot_db;


Adding Dependencies to Your Project

To connect your Spring Boot application to a MySQL database, you’ll need to add the following dependencies to your project:

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

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.21</version>
</dependency>


Configuring the Connection to MySQL

To configure the connection to your MySQL database, you’ll need to add the following properties to your application.properties file:

spring.datasource.url=jdbc:mysql://localhost:3306/spring_boot_db
spring.datasource.username=root
spring.datasource.password=password
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

Here, we’re specifying the URL of the database, the username, and the password. The show-sql property is set to true so that we can see the SQL statements generated by JPA in the logs. The ddl-auto property is set to update, which means that the database schema will be automatically updated if the model changes.



Creating the Entity Class

Now that we have the basic setup in place, let’s create an entity class that represents the data we’ll be storing in our database. For this example, we’ll create a simple User entity with three fields: id, username, and email.

@Entity
public class User {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  
  private String username;
  
  private String email;
  
  // Getters and setters
}

Note that the id field is annotated with @Id to indicate that it’s the primary key of the table. The @GeneratedValue annotation is used to specify that the value of the id field should be generated automatically by the database.



Creating the Repository

Next, we’ll create a repository to handle the CRUD operations for the User entity. We’ll extend the JpaRepository interface, which provides a convenient way to interact with the database.

public interface UserRepository extends JpaRepository<User, Long> {
}


Creating the Service

Now that we have the repository in place, we’ll create a service that uses it to perform CRUD operations on the User entity.

@Service
public class UserService {

  private final UserRepository userRepository;

  @Autowired
  public UserService(UserRepository userRepository) {
    this.userRepository = userRepository;
  }

  public User createUser(User user) {
    return userRepository.save(user);
  }

  public User findUserById(Long id) {
    return userRepository.findById(id).orElse(null);
  }

  public List<User> findAllUsers() {
    return userRepository.findAll();
  }

  public User updateUser(User user) {
    return userRepository.save(user);
  }

  public void deleteUser(Long id) {
    userRepository.deleteById(id);
  }
}

Here, we’re using the UserRepository to perform basic CRUD operations on the User entity. The save method is used for both creating and updating a user, and the findById method is used to retrieve a user by its id.



Handling Transactions

In a real-world application, you’ll often need to perform multiple database operations in a single transaction. For example, you may want to insert a new user and update another user in a single transaction. To handle transactions in Spring Boot, you can simply annotate your service method with @Transactional.

@Transactional
public User createAndUpdateUser(User userToCreate, User userToUpdate) {
  User createdUser = userRepository.save(userToCreate);
  userToUpdate.setUsername(userToUpdate.getUsername() + "_updated");
  User updatedUser = userRepository.save(userToUpdate);
  return createdUser;
}

In this example, if either the insert or the update operation fails, the entire transaction will be rolled back and the database will remain unchanged.



Conclusion

In this blog post, we’ve covered how to connect a Spring Boot application to a MySQL database and perform basic CRUD operations. We’ve also looked at how to handle transactions in Spring Boot. With these basics in place, you should be able to build a fully functional database-backed application using Spring Boot.



Leave a Reply