# Accessing Data with Spring Data JPA and MySQL By Atta Ur Rehman Shah (https://attacomsian.com/about). Published Oct 3, 2019, updated Sep 25, 2022. Topics: Spring Boot, Spring Data JPA, MySQL. Canonical URL: https://attacomsian.com/blog/accessing-data-spring-data-jpa-mysql > Learn how to access data using Spring Data JPA with MySQL database in a Spring Boot application. [Spring Data JPA](https://attacomsian.com/blog/getting-started-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](https://attacomsian.com/blog/spring-data-jpa-h2-database), 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 the MySQL database with Spring Data JPA. If you are using Gradle, add the following dependencies to your `build.gradle` file: ```groovy 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: ```xml org.springframework.boot spring-boot-starter-data-jpa mysql mysql-connector-java runtime ``` If you want to create a new Spring Boot project from scratch, just use [Spring Initializr](https://start.spring.io) 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](https://attacomsian.com/blog/spring-data-jpa-h2-database#configure-h2-database). 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 to it: ```properties # 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 purposes - 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 auto-configure a `DataSource` bean for you. Alternatively, you can define the `DataSource` bean in a `@Configuration` annotated Java class like below: ```java @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. It 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

```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 annotated 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 this entity will be mapped. The `id` attribute is annotated with both `@Id` and `@GeneratedValue` annotations. The former indicates that it is a primary key of the entity. The latter 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 unannotated. This means they are mapped to database columns having the same names as the properties. ## Create a Repository Let us create the `NoteRepository` interface to store and access data from the MySQL database:

NoteRepository.java

```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 { List 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 `findByTitleContaining()` method 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 handle it when you run the application. This makes Spring Data JPA a powerful tool 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](https://attacomsian.com/blog/spring-boot-console-application):

Application.java

```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 the 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. In 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: ```bash $ ./gradlew bootRun ``` For Maven, type the following command to launch the application: ```bash $ ./mvnw spring-boot:run ``` You should see the following output printed on the console: ```plaintext 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 the 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](https://github.com/attacomsian/code-examples/tree/master/spring-data-jpa/jpa-mysql) 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](https://attacomsian.com/blog/getting-started-spring-data-jpa) guide to learn more about the core features and configuration instructions for different data sources.