Spring Data JPA is a powerful tool for building Spring-based applications that use different data access technologies, relational and non-relational databases, map-reduce frameworks, and cloud-based data services.

In an earlier article, I talked about using Spring Data JPA with the H2 database in a Spring Boot application for storing and accessing data from the in-memory store.

In this article, you'll learn how to use MySQL with Spring Data JPA in a Spring Boot application for data persistence. We'll be building a simple note-taking application that uses Spring Data JPA to store and retrieve data in a relational database — MySQL.

Dependencies

You only need spring-data-starter-data-jpa and mysql-connector-java dependencies to use MySQL database with Spring Data JPA.

If you are using Gradle, add the following dependencies to your build.gradle file:

implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
runtimeOnly 'mysql:mysql-connector-java'

For a Maven project, include the following dependencies to the pom.xml file:

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

If you want to create a new Spring Boot project from scratch, just use Spring Initializr web tool to bootstrap a new application with the above dependencies.

Configure MySQL Database

By default, Spring Boot automatically configures the Hibernate as a JPA provider and also tries to configure the data source object based on the dependencies available in the classpath and properties declared in the application.properties or application.yml file.

The classpath dependencies work well for in-memory databases like H2. But for MySQL, we need to explicitly define the database connection credentials in a properties file.

Just open the application.properties file and add the following properties into it:

# MySQL connection properties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=mypass
spring.datasource.url=jdbc:mysql://localhost:3306/testdb?createDatabaseIfNotExist=true&useSSL=false

# Log JPA queries
# Comment this in production
spring.jpa.show-sql=true

# Drop and create new tables (create, create-drop, validate, update)
# Only for testing purpose - comment this in production
spring.jpa.hibernate.ddl-auto=create

# Hibernate SQL dialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

Spring Boot will read the above properties and will auto-configure a DataSource bean for you.

Alternatively, you can define the DataSource bean in a @Configuration annotated Java class like below:

@Bean
public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();

    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUsername("root");
    dataSource.setPassword("mypass");
    dataSource.setUrl("jdbc:mysql://localhost:3306/testdb?createDatabaseIfNotExist=true&useSSL=false");

    return dataSource;
}

The above configuration will automatically create a MySQL database named testdb. Make sure to change the username and password properties to match your installation.

Create an Entity

The next step is to create the Note entity for storing notes. This class has the following properties:

  • id — Unique primary key with auto-increment enabled.
  • title — The title of the Note entity.
  • content — Note's body. Must not be null.
  • created — The timestamp at which the Note object was created.

Now, let's add the Note entity with the following content:

Note.java

package com.attacomsian.jpa.domains;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "notes")
public class Note {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private String content;
    private Date created;

    public Note() {
    }

    public Note(String title, String content, Date created) {
        this.title = title;
        this.content = content;
        this.created = created;
    }

    // getters and setters, equals(), toString() .... (omitted for brevity)
}

All Spring Data JPA models must be annotation with the @Entity annotation to indicate that they are JPA entities.

This time, we also annotated the Note class with the @Table annotation to specify the name of the database table that this entity will be mapped to.

The id attribute is annotated with both @Id and @GeneratedValue annotations. The former indicates that it is a primary key of the entity. The later defines the primary key generation strategy. In the above case, we have declared the primary key as an AUTO INCREMENT field.

The other three properties, title, content and created, are left unannotated. This means they are mapped to database columns that share the same names as the properties themselves.

Create a Repository

Let us create the NoteRepository interface to store and access data from MySQL database:

NoteRepository.java

package com.attacomsian.jpa.repositories;

import com.attacomsian.jpa.domains.Note;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface NoteRepository extends CrudRepository<Note, Long> {

    List<Note> findByTitleContaining(String title);

}

NoteRepository extends the CrudRepository interface. The type of entity and ID that it needs, Note and Long, are specified in the generic parameters on CrudRepository.

We also defined an additional method findByTitleContaining() to get all notes that contain a specific keyword in their title value.

That's it. You can now use generic CRUD methods like save(), delete(), count(), findById(), and findAll() on NoteRepository.

The best thing is you don't need to provide an implementation for these methods. Spring Data JPA will automatically create an implementation when you run the application. That's what makes Spring Data JPA so powerful too to use for data access layers.

Create an Application Class

The next step is to create the main application class that will act as an entry point for our Spring Boot console application:

Application.java

package com.attacomsian.jpa;

import com.attacomsian.jpa.domains.Note;
import com.attacomsian.jpa.repositories.NoteRepository;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import java.util.Date;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Bean
    public CommandLineRunner noteDemo(NoteRepository noteRepository) {
        return (args) -> {

            // create notes
            noteRepository.save(new Note("Welcome to Spring Boot", "Basic Introduction", new Date()));
            noteRepository.save(new Note("Learn Spring Data JPA", "Java Persistence Layer", new Date()));
            noteRepository.save(new Note("Learn Spring Security", "Build Secure Web Apps", new Date()));

            // fetch all notes
            System.out.println("Notes found with findAll():");
            System.out.println("---------------------------");
            for (Note note : noteRepository.findAll()) {
                System.out.println(note.toString());
            }
            System.out.println();

            // fetch note by id
            Note note = noteRepository.findById(1L).get();
            System.out.println("Note found with findById(1L):");
            System.out.println("-----------------------------");
            System.out.println(note.toString());
            System.out.println();

            // fetch all notes that contain keyword `learn`
            System.out.println("Notes that contain keyword 'learn':");
            System.out.println("-----------------------------------");
            for (Note n : noteRepository.findByTitleContaining("learn")) {
                System.out.println(n.toString());
            }
            System.out.println();

            // update note title
            Note noteUpdate = noteRepository.findById(2L).get();
            noteUpdate.setTitle("Understanding Spring Data JPA");
            noteRepository.save(noteUpdate);
            System.out.println("Update note title:");
            System.out.println("------------------");
            System.out.println(noteUpdate.toString());
            System.out.println();

            // total notes in DB
            System.out.println("Total notes in DB:");
            System.out.println("------------------");
            System.out.println(noteRepository.count());
            System.out.println();

            // delete all notes
            noteRepository.deleteAll();
        };
    }
}

The above code is very much self-explanatory. The Application class is annotated with @SpringBootApplication to enable the auto configurations. The main() method calls Spring Boot’s SpringApplication.run() method to start the application.

We are auto wiring NoteRepository to the noteDemo() method. Inside this method, we first save a few Note objects through the save() method. Next, we call the fetchAll() method to get all Note objects from the MySQL database.

Later, we get a single Note object from the database by its ID using findById(). Finally, we call the findByTitleContaining() method to fetch all notes which contain the keyword learn in their titles.

Run the Application

Let us run the application to see the output. If you are using Gradle, execute the following command to start the application:

$ ./gradlew bootRun

For Maven, type the following command to launch the application:

$ ./mvnw spring-boot:run

You should see the following output printed on the console:

Notes found with findAll():
---------------------------
Note{id=1, title='Welcome to Spring Boot', content='Basic Introduction', created=2019-10-03 13:48:52.0}
Note{id=2, title='Learn Spring Data JPA', content='Java Persistence Layer', created=2019-10-03 13:48:52.0}
Note{id=3, title='Learn Spring Security', content='Build Secure Web Apps', created=2019-10-03 13:48:52.0}

Note found with findById(1L):
-----------------------------
Note{id=1, title='Welcome to Spring Boot', content='Basic Introduction', created=2019-10-03 13:48:52.0}

Notes that contain keyword 'learn':
-----------------------------------
Note{id=2, title='Learn Spring Data JPA', content='Java Persistence Layer', created=2019-10-03 13:48:52.0}
Note{id=3, title='Learn Spring Security', content='Build Secure Web Apps', created=2019-10-03 13:48:52.0}

Update note title:
------------------
Note{id=2, title='Understanding Spring Data JPA', content='Java Persistence Layer', created=2019-10-03 13:48:52.0}

Total notes in DB:
------------------
3

Source code: Download the complete source code from GitHub available under MIT license.

Conclusion

Congratulations! You have successfully created a simple Spring Boot application that uses Spring Data JPA with MySQL database to store, access, delete and update data. In short, it performs all CRUD operations.

Check out the getting started with Spring Data JPA guide to learn more about the core features and configuration instructions for different data sources.

✌️ Like this article? Follow @attacomsian on Twitter. You can also follow me on LinkedIn and DEV. Buy me a coffee (cost $3)

Need help to start a new Spring Boot or MEAN stack project? I am available for contract work. Hire me to accomplish your business goals with engineering and design. Let’s talk about your project: hi@attacomsian.com.