This example will tell you how to use spring boot data JPA to implement insert, update, delete and select database table operation on MySQL database table. With spring boot data JPA the database table operation command has been wrapped to a method, you just need to create a java interface that extends basic spring boot data JPA Repository interface ( for example CrudRepository), then you only need to define database table operation method( such as findBy<column name>, deleteBy<column name>, etc) in the custom Repository interface, and the method name must follow special naming rules. You do not need to write SQL commands in the Repository interface.
1. Create MySQL Database Table.
- Create a MySQL database with the name dev2qa_example. The database’s default collation should be utf8 – utf8_bin.
CREATE SCHEMA `dev2qa_example` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;
- Create a table user_account in the above dev2qa_example database with the below SQL statement. The id column should be AI ( automate increment ), otherwise an error Spring Boot JPA Table ‘dbname.hibernate_sequence’ Doesn’t Exist will be thrown.
CREATE TABLE `dev2qa_example`.`user_account` ( `id` INT NOT NULL AUTO_INCREMENT, `user_name` VARCHAR(100) NULL, `password` VARCHAR(100) NULL, `email` VARCHAR(100) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
2. Create Spring Boot Project.
- Launch spring tool suite, click File —> New —> Spring Starter Project menu item to open below New Spring Starter Project wizard. Input related project info like below. And click the Next button.
- Add JPA, MySQL, and Web libraries in the dependencies wizard. And click the Finish button to complete the project initialization.
3. Spring Boot JPA CRUD Example Project Files.
Below are the source files in this project. We will introduce them one by one.
C:\WORKSPACE\WORK\DEV2QA.COM-EXAMPLE-CODE\SPRINGBOOT\SPRINGBOOTCRUDMYSQL
│ pom.xml
└───src
├───main
│ ├───java
│ │ └───com
│ │ └───dev2qa
│ │ └───example
│ │ │ SpringBootCrudMySqlApplication.java
│ │ │
│ │ ├───controller
│ │ │ UserAccountController.java
│ │ │
│ │ ├───entity
│ │ │ UserAccount.java
│ │ │
│ │ └───repository
│ │ UserAccountRepository.java
│ │
│ └───resources
│ application.properties
│
└───test
└───java
└───com
└───dev2qa
SpringBootCrudMySqlApplicationTests.java
3.1 SpringBootCrudMySqlApplication.java
This is the spring boot example starting java class. It will be loaded and run first in the spring boot application.
package com.dev2qa.example;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
//@SpringBootApplication
@Configuration
@ComponentScan(basePackages = { "com.dev2qa.example" })
@EnableAutoConfiguration
public class SpringBootCrudMySqlApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBootCrudMySqlApplication.class, args);
}
}
3.2 UserAccountController.java
This is the spring MVC controller java class which will map user request url to processing method.
package com.dev2qa.example.controller;
import java.util.List;
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.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import com.dev2qa.example.entity.UserAccount;
import com.dev2qa.example.repository.UserAccountRepository;
@Controller
@RequestMapping(path = "/userAccount")
public class UserAccountController {
@Autowired
UserAccountRepository userAccountRepository;
/*
* Mapping url exmaple:
* http://localhost:8080/userAccount/add?userName=Jerry&password=888888&email=
* [email protected]
* http://localhost:8080/userAccount/add?userName=Richard&password=888888&email=
* [email protected]
*/
@GetMapping(path = "/add")
@ResponseBody
public String addUser(@RequestParam String userName, @RequestParam String password, @RequestParam String email) {
UserAccount userAccount = new UserAccount();
userAccount.setUsername(userName);
userAccount.setPassword(password);
userAccount.setEmail(email);
userAccountRepository.save(userAccount);
String ret = "User account has been added, user name = " + userName + ", password = " + password + ", email = "
+ email;
return ret;
}
/*
* Mapping url exmaple: http://localhost:8080/userAccount/findAll
*/
@GetMapping(path = "/findAll")
@ResponseBody
public String findAllUser() {
StringBuffer retBuf = new StringBuffer();
List<UserAccount> userAccountList = (List<UserAccount>) userAccountRepository.findAll();
if (userAccountList != null) {
for (UserAccount userAccount : userAccountList) {
retBuf.append("user name = ");
retBuf.append(userAccount.getUsername());
retBuf.append(", password = ");
retBuf.append(userAccount.getPassword());
retBuf.append(", email = ");
retBuf.append(userAccount.getEmail());
retBuf.append("\r\n");
}
}
if (retBuf.length() == 0) {
retBuf.append("No record find.");
} else {
retBuf.insert(0, "<pre>");
retBuf.append("</pre>");
}
return retBuf.toString();
}
/*
* Mapping url exmaple:
* http://localhost:8080/userAccount/findByName?userName=Jerry
*/
@GetMapping(path = "/findByName")
@ResponseBody
public String findByName(@RequestParam String userName) {
StringBuffer retBuf = new StringBuffer();
List<UserAccount> userAccountList = (List<UserAccount>) userAccountRepository.findByUsername(userName);
if (userAccountList != null) {
for (UserAccount userAccount : userAccountList) {
retBuf.append("user name = ");
retBuf.append(userAccount.getUsername());
retBuf.append(", password = ");
retBuf.append(userAccount.getPassword());
retBuf.append(", email = ");
retBuf.append(userAccount.getEmail());
retBuf.append("\r\n");
}
}
if (retBuf.length() == 0) {
retBuf.append("No record find.");
}
return retBuf.toString();
}
/*
* Mapping url exmaple:
* http://localhost:8080/userAccount/findByNameAndPassword?userName=Jerry&
* password=888888
*/
@GetMapping(path = "/findByNameAndPassword")
@ResponseBody
public String findByNameAndPassword(@RequestParam String userName, @RequestParam String password) {
StringBuffer retBuf = new StringBuffer();
List<UserAccount> userAccountList = (List<UserAccount>) userAccountRepository
.findByUsernameAndPassword(userName, password);
if (userAccountList != null) {
for (UserAccount userAccount : userAccountList) {
retBuf.append("user name = ");
retBuf.append(userAccount.getUsername());
retBuf.append(", password = ");
retBuf.append(userAccount.getPassword());
retBuf.append(", email = ");
retBuf.append(userAccount.getEmail());
retBuf.append("<br/>");
}
}
if (retBuf.length() == 0) {
retBuf.append("No record find.");
}
return retBuf.toString();
}
/*
* Mapping url exmaple:
* http://localhost:8080/userAccount/updateUser?userName=Jerry&password=hello&
* [email protected]
*/
@GetMapping(path = "/updateUser")
@ResponseBody
public String updateUser(@RequestParam String userName, @RequestParam String password, @RequestParam String email) {
StringBuffer retBuf = new StringBuffer();
List<UserAccount> userAccountList = userAccountRepository.findByUsername(userName);
if (userAccountList != null) {
for (UserAccount userAccount : userAccountList) {
userAccount.setUsername(userName);
userAccount.setPassword(password);
userAccount.setEmail(email);
userAccountRepository.save(userAccount);
}
}
retBuf.append("User data update successfully.");
return retBuf.toString();
}
/*
* Mapping url exmaple:
* http://localhost:8080/userAccount/deleteByUserName?userName=Richard
*/
@GetMapping(path = "/deleteByUserName")
@ResponseBody
public String deleteByUserName(@RequestParam String userName) {
StringBuffer retBuf = new StringBuffer();
userAccountRepository.deleteByUsername(userName);
retBuf.append("User data has been deleted successfully.");
return retBuf.toString();
}
/*
* Mapping url exmaple:
* http://localhost:8080/userAccount/deleteByUserNameAndPassword?userName=
* Richard&password=888888
*/
@GetMapping(path = "/deleteByUserNameAndPassword")
@ResponseBody
public String deleteByUserNameAndPassword(@RequestParam String userName, @RequestParam String password) {
StringBuffer retBuf = new StringBuffer();
userAccountRepository.deleteByUsernameAndPassword(userName, password);
retBuf.append("User data has been deleted successfully.");
return retBuf.toString();
}
}
3.3 UserAccount.java
This is the entity java class that will be mapped to MySQL table user_account. Please note the id generate strategy must be GenerationType.IDENTITY, if you use Generation.AUTO and the MySQL table id column is set to auto-increment, then an error will be thrown.
package com.dev2qa.example.entity;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
/* Map this entity class to user_account table. */
@Entity(name = "user_account")
public class UserAccount {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@javax.persistence.Column(name = "user_name")
private String username;
private String password;
private String email;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
3.4 UserAccountRepository.java
This is the custom spring boot data JPA repository interface that extends CrudRepository. You just need to define related methods then the spring framework will automatically run the related SQL command to implement the method. This makes coding more quickly.
package com.dev2qa.example.repository;
import java.util.List;
import org.springframework.data.repository.CrudRepository;
import org.springframework.transaction.annotation.Transactional;
import com.dev2qa.example.entity.UserAccount;
public interface UserAccountRepository extends CrudRepository<UserAccount, Long> {
/*
* Get user list by user name. Please note the format should be
* findBy<column_name>.
*/
List<UserAccount> findByUsername(String username);
/*
* Get user list by user name and password. Please note the format should be
* findBy<column_name_1>And<column_name_2>.
*/
List<UserAccount> findByUsernameAndPassword(String username, String password);
@Transactional
void deleteByUsernameAndPassword(String username, String password);
@Transactional
void deleteByUsername(String username);
}
3.5 application.properties
This is the resource file that contains MySQL JDBC data source connection data used by the example.
# MySQL jdbc connection url. spring.datasource.url=jdbc:mysql://localhost:3306/dev2qa_example # MySQL jdbc driver class name. spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver # MySQL database username and password spring.datasource.username=root spring.datasource.password=root
3.6 pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>SpringBootCRUDMySQL</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>SpringBootCRUDMySQL</name>
<description>Spring boot access mysql with crud operation.</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3.7 Run The Example.
- Right-click the project name.
- Click Run As —> Spring Boot App menu item from the popup menu list.
- After the application startup, input the mapping url for the related UserAccountController java class method in a web browser to see the result.
4. Question & Answer.
4.1 Spring boot methods findAll, findById, deleteById all return empty results.
- I want to use Spring boot + MySQL to implement a REST application that will perform CRUD actions to manipulate a MySQL table. But I find when I execute the findAll() method, it returns an empty list, this is not what I expect. When I execute the findById() method, it returns the error message java.util.NoSuchElementException: No value present. And when I perform a delete action by the spring boot method deleteById(), it also tells me that No class org.dev2qa.entity.Article entity with id 10 exists! It seems my database table is empty but it is not. In what case can these errors occur?
- My custom repository class extends the JpaRepository class, and its findAll() method also returns an empty list. My database is the MySql database also. When I add one record in the MySQL database, the findAll() method return [{}], and when I add two records in the MySQL database, the findAll() method return [{},{}]. The list element number is correct but the element data is empty this is not correct. Can anyone give me some help? Thanks a lot.
- If your entity class’s properties are not public this error may occur. You should first declare the entity class properties with the @Column annotation and the properties declaration can be private, then add getters and setters method to those properties and make the getters and setters method public. After this, the JpaRepository can create an entity object and populate the object’s properties with the data read back from the MySQL database. And your findAll() method will not return an empty list at all.
Reference
- How To Install MySQL On Ubuntu
- Resolve Spring Boot JPA Table ‘dbname.hibernate_sequence’ Doesn’t Exist Error
Why this tutorial doesn’t need to run on server . Could you explain me ? Thank you.
Well spring boot have embedded Tomcat server, which works for it.
hi, i m unable to insert data in the database. I can extract it (i inserted data via mysql terminal) but not able to insert data via code. I am using STS with gradle.
Did you find the Solution for it?
Good example very easy to understand…but provider front end pages with mapping…
nice , understood
Thank you so much for your example. I have scoured the web for a Spring Boot + CRUD + MySQL example that would actually compile and yours works!! Finally, a tutorial that works. It is written clearly and concisely. I hope you come out with more articles.
Missing hibernate properties ::
# MySQL jdbc connection url.
spring.datasource.url=jdbc:mysql://localhost:3306/dev2qa_example
# MySQL jdbc driver class name.
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
# MySQL database username and password
spring.datasource.username=root
spring.datasource.password=kloud@123
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
How can i add html as a GUI to this CRUD
where is DAO class