Accessing data with MySQL

This guide walks you through the process of creating a Spring application connected to a MySQL Database (as opposed to an in-memory, embedded database, which most of the other guides and many sample applications use). It uses Spring Data JPA to access the database, but this is only one of many possible choices (for example, you could use plain Spring JDBC).

What You Will Build

You will create a MySQL database, build a Spring application, and connect it to the newly created database.

Java 17 or later

Gradle 7.5+ or Maven 3.5+

You can also import the code straight into your IDE:

Spring Tool Suite (STS)

IntelliJ IDEA

VSCode

Like most Spring Getting Started guides , you can start from scratch and complete each step or you can bypass basic setup steps that are already familiar to you. Either way, you end up with working code.

To start from scratch , move on to Starting with Spring Initializr .

To skip the basics , do the following:

Download and unzip the source repository for this guide, or clone it using Git : git clone https://github.com/spring-guides/gs-accessing-data-mysql.git

cd into gs-accessing-data-mysql/initial

Jump ahead to Create the Database .

When you finish , you can check your results against the code in gs-accessing-data-mysql/complete .

You can use this pre-initialized project and click Generate to download a ZIP file. This project is configured to fit the examples in this tutorial.

To manually initialize the project:

Navigate to https://start.spring.io . This service pulls in all the dependencies you need for an application and does most of the setup for you.

Choose either Gradle or Maven and the language you want to use. This guide assumes that you chose Java.

Click Dependencies and select Spring Web , Spring Data JPA , and MySQL Driver .

Click Generate .

Download the resulting ZIP file, which is an archive of a web application that is configured with your choices.

Open a terminal (command prompt in Microsoft Windows) and open a MySQL client as a user who can create new users.

For example, on a Linux system, use the following command;

mysql> create database db_example; -- Creates the new database
mysql> create user 'springuser'@'%' identified by 'ThePassword'; -- Creates the user
mysql> grant all on db_example.* to 'springuser'@'%'; -- Gives all privileges to the new user on the newly created database

Spring Boot gives you defaults on all things. For example, the default database is H2 . Consequently, when you want to use any other database, you must define the connection attributes in the application.properties file.

Create a resource file called src/main/resources/application.properties , as the following listing shows:

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/db_example
spring.datasource.username=springuser
spring.datasource.password=ThePassword
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.jpa.show-sql: true

none : The default for MySQL . No change is made to the database structure.

update : Hibernate changes the database according to the given entity structures.

create : Creates the database every time but does not drop it on close.

create-drop : Creates the database and drops it when SessionFactory closes.

You must begin with either create or update , because you do not yet have the database structure. After the first run, you can switch it to update or none , according to program requirements. Use update when you want to make some change to the database structure.

The default for H2 and other embedded databases is create-drop . For other databases, such as MySQL , the default is none .

It is a good security practice to, after your database is in a production state, set this to none , revoke all privileges from the MySQL user connected to the Spring application, and give the MySQL user only SELECT , UPDATE , INSERT , and DELETE . You can read more about this at the end of this guide. import jakarta.persistence.Entity; import jakarta.persistence.GeneratedValue; import jakarta.persistence.GenerationType; import jakarta.persistence.Id; @Entity // This tells Hibernate to make a table out of this class public class User { @GeneratedValue(strategy=GenerationType.AUTO) private Integer id; private String name; private String email; public Integer getId() { return id; public void setId(Integer id) { this.id = id; public String getName() { return name; public void setName(String name) { this.name = name; public String getEmail() { return email; public void setEmail(String email) { this.email = email;

package com.example.accessingdatamysql;
import org.springframework.data.repository.CrudRepository;
import com.example.accessingdatamysql.User;
// This will be AUTO IMPLEMENTED by Spring into a Bean called userRepository
// CRUD refers Create, Read, Update, Delete
public interface UserRepository extends CrudRepository<User, Integer> {
      
package com.example.accessingdatamysql;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller // This means that this class is a Controller
@RequestMapping(path="/demo") // This means URL's start with /demo (after Application path)
public class MainController {
  @Autowired // This means to get the bean called userRepository
         // Which is auto-generated by Spring, we will use it to handle the data
  private UserRepository userRepository;
  @PostMapping(path="/add") // Map ONLY POST Requests
  public @ResponseBody String addNewUser (@RequestParam String name
      , @RequestParam String email) {
    // @ResponseBody means the returned String is the response, not a view name
    // @RequestParam means it is a parameter from the GET or POST request
    User n = new User();
    n.setName(name);
    n.setEmail(email);
    userRepository.save(n);
    return "Saved";
  @GetMapping(path="/all")
  public @ResponseBody Iterable<User> getAllUsers() {
    // This returns a JSON or XML with the users
    return userRepository.findAll();
      
package com.example.accessingdatamysql;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class AccessingDataMysqlApplication {
  public static void main(String[] args) {
    SpringApplication.run(AccessingDataMysqlApplication.class, args);
    
@Configuration: Tags the class as a source of bean definitions for the application context.
    
@EnableAutoConfiguration: Tells Spring Boot to start adding beans based on classpath settings, other beans, and various property settings. For example, if spring-webmvc is on the classpath, this annotation flags the application as a web application and activates key behaviors, such as setting up a DispatcherServlet.
    
@ComponentScan: Tells Spring to look for other components, configurations, and services in the com/example package, letting it find the controllers.
   
The main() method uses Spring Boot’s SpringApplication.run() method to launch an application. Did you notice that there was not a single line of XML? There is no web.xml file, either. This web application is 100% pure Java and you did not have to deal with configuring any plumbing or infrastructure.
   
Build an executable JAR
    
You can run the application from the command line with Gradle or Maven. You can also build a single executable JAR file that contains all the necessary dependencies, classes, and resources and run that. Building an executable jar makes it easy to ship, version, and deploy the service as an application throughout the development lifecycle, across different environments, and so forth.
    
If you use Gradle, you can run the application by using ./gradlew bootRun. Alternatively, you can build the JAR file by using ./gradlew build and then run the JAR file, as follows:
   
Now that the application is running, you can test it by using curl or some similar tool. You have two HTTP endpoints that you can test:
   
GET localhost:8080/demo/all: Gets all data. POST localhost:8080/demo/add: Adds one user to the data.
   
The following curl command adds a user:
   
When you are on a production environment, you may be exposed to SQL injection attacks. A hacker may inject DROP TABLE or any other destructive SQL commands. So, as a security practice, you should make some changes to your database before you expose the application to your users.
   
The following command revokes all the privileges from the user associated with the Spring application:
   
Removing all privileges and granting some privileges gives your Spring application the privileges necessary to make changes to only the data of the database and not the structure (schema).
   
When you want to make changes to the database:
    
Regrant permissions.
    
Change the spring.jpa.hibernate.ddl-auto to update.
    
Re-run your applications.
   
Then repeat the two commands shown here to make your application safe for production use again. Better still, use a dedicated migration tool, such as Flyway or Liquibase.