SpringBoot and JPA CRUD demo

This tutorial completes a simple Demo based on SpringBoot+Spring Data JPA+MatrixOne+Intellij IDEA , and realizes the basic CRUD function.

Before you start

A brief introduction about these softwares concerned:

  • Spring Data JPA: JPA is a specification that defines an API for object-relational mappings and for managing persistent objects. Spring Data JPA is an object–relational mapping tool for the Java programming language. It provides a framework for mapping an object-oriented domain model to a relational database.
  • Intellij IDEA: IntelliJ IDEA is a popular IDE for Java developers. It has a lot of plugins that can enhance our efficiency.
  • Maven: Maven is a powerful management tool in Java that can automatically download and import Jar file according to the configuration in the pom.xml file. This feature reduces the conflicts between different versions of Jar files.
  • Spring: Spring is one of the most popular frameworks in Java and more and more enterprise is using the Spring framework to build their project. Spring Boot is built on top of the conventional spring framework. So, it provides all the features of spring and is yet easier to use than spring.
  • Postman: Postman is an application used for API testing. It is an HTTP client that tests HTTP requests, utilizing a graphical user interface, through which we obtain different types of responses that need to be subsequently validated.

Set up environment

1. Install and Launch MatrixOne

Follow the installation instruction and launch MatrixOne. Create a database test by MySQL client.

  1. mysql> create database test;

2. Create a new Spring Boot Project Using IntelliJ IDEA

Choose Spring Initializer, and name the project as you want.

image-20221027094625081

Choose Spring Web, JDBC API, Spring Data JPA, and MySQL Driver as dependencies for this project.

image-20221027101504418

Click Create, the project will be created. The dependencies are listed in the pom.xml file. Usually you don’t need to modify anything.

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  4. <modelVersion>4.0.0</modelVersion>
  5. <groupId>com.example</groupId>
  6. <artifactId>jpademo</artifactId>
  7. <version>0.0.1-SNAPSHOT</version>
  8. <name>jpademo</name>
  9. <description>jpademo</description>
  10. <properties>
  11. <java.version>1.8</java.version>
  12. <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  13. <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  14. <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
  15. </properties>
  16. <dependencies>
  17. <dependency>
  18. <groupId>org.springframework.boot</groupId>
  19. <artifactId>spring-boot-starter-data-jpa</artifactId>
  20. </dependency>
  21. <dependency>
  22. <groupId>org.springframework.boot</groupId>
  23. <artifactId>spring-boot-starter-jdbc</artifactId>
  24. </dependency>
  25. <dependency>
  26. <groupId>org.springframework.boot</groupId>
  27. <artifactId>spring-boot-starter-web</artifactId>
  28. </dependency>
  29. <dependency>
  30. <groupId>mysql</groupId>
  31. <artifactId>mysql-connector-java</artifactId>
  32. <scope>runtime</scope>
  33. </dependency>
  34. <dependency>
  35. <groupId>org.springframework.boot</groupId>
  36. <artifactId>spring-boot-starter-test</artifactId>
  37. <scope>test</scope>
  38. <exclusions>
  39. <exclusion>
  40. <groupId>org.junit.vintage</groupId>
  41. <artifactId>junit-vintage-engine</artifactId>
  42. </exclusion>
  43. </exclusions>
  44. </dependency>
  45. </dependencies>
  46. <dependencyManagement>
  47. <dependencies>
  48. <dependency>
  49. <groupId>org.springframework.boot</groupId>
  50. <artifactId>spring-boot-dependencies</artifactId>
  51. <version>${spring-boot.version}</version>
  52. <type>pom</type>
  53. <scope>import</scope>
  54. </dependency>
  55. </dependencies>
  56. </dependencyManagement>
  57. <build>
  58. <plugins>
  59. <plugin>
  60. <groupId>org.apache.maven.plugins</groupId>
  61. <artifactId>maven-compiler-plugin</artifactId>
  62. <version>3.8.1</version>
  63. <configuration>
  64. <source>1.8</source>
  65. <target>1.8</target>
  66. <encoding>UTF-8</encoding>
  67. </configuration>
  68. </plugin>
  69. <plugin>
  70. <groupId>org.springframework.boot</groupId>
  71. <artifactId>spring-boot-maven-plugin</artifactId>
  72. <version>2.3.7.RELEASE</version>
  73. <configuration>
  74. <mainClass>com.example.jpademo.JpademoApplication</mainClass>
  75. </configuration>
  76. <executions>
  77. <execution>
  78. <id>repackage</id>
  79. <goals>
  80. <goal>repackage</goal>
  81. </goals>
  82. </execution>
  83. </executions>
  84. </plugin>
  85. </plugins>
  86. </build>
  87. </project>

3. Modify the application.properties file

Under resources folder, the MatrixOne connection and Hibernate need to be configured in application.properties file.

  1. # Application Name
  2. spring.application.name=jpademo
  3. # Database driver
  4. spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  5. # Data Source name
  6. spring.datasource.name=defaultDataSource
  7. # Database connection url, modify to MatrixOne address and port, with parameters
  8. spring.datasource.url=jdbc:mysql://127.0.0.1:6001/test?characterSetResults=UTF-8&continueBatchOnError=false&useServerPrepStmts=true&alwaysSendSetIsolation=false&useLocalSessionState=true&zeroDateTimeBehavior=CONVERT_TO_NULL&failoverReadOnly=false&serverTimezone=Asia/Shanghai&socketTimeout=30000
  9. # Database username and password
  10. spring.datasource.username=root
  11. spring.datasource.password=111
  12. # Web application port
  13. server.port=8080
  14. # Hibernate configurations
  15. spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect
  16. spring.jpa.properties.hibernate.id.new_generator_mappings = false
  17. spring.jpa.properties.hibernate.format_sql = true
  18. spring.jpa.hibernate.ddl-auto = validate

4. Create table and insert some data in MatrixOne

Connect to MatrixOne with MySQL client and execute the following SQL statements. You can save these SQL statements in a book.sql under /resource/database/.

  1. mysql> USE test;
  2. mysql> CREATE TABLE IF NOT EXISTS `book` (
  3. `id` int(11) NOT NULL AUTO_INCREMENT,
  4. `author` varchar(255) DEFAULT NULL,
  5. `category` varchar(255) DEFAULT NULL,
  6. `name` varchar(255) DEFAULT NULL,
  7. `pages` int(11) DEFAULT NULL,
  8. `price` int(11) DEFAULT NULL,
  9. `publication` varchar(255) DEFAULT NULL,
  10. PRIMARY KEY (`id`)
  11. );
  12. mysql> INSERT INTO `book` (`id`, `author`, `category`, `name`, `pages`, `price`, `publication`) VALUES
  13. (1, 'Antoine de Saint-Exupery', 'Fantancy', 'The Little Prince', 100, 50, 'Amazon'),
  14. (2, 'J. K. Rowling', 'Fantancy', 'Harry Potter and the Sorcerer''s Stone', 1000, 200, 'Amazon'),
  15. (3, 'Lewis Carroll', 'Fantancy', 'Alice''s Adventures in Wonderland', 1500, 240, 'Amazon');

Write Code

After setting up the environment, we write code to implement a simple CRUD application. After finishing coding, you’ll have a project structure as below. You can create these packages and java class in advance. We will code the Create, Update, Insert, Delete, Select operations for this demo application.

image-20221027105233860

1. BookStoreController.java

  1. package com.example.jpademo.controller;
  2. import com.example.jpademo.entity.Book;
  3. import com.example.jpademo.services.IBookStoreService;
  4. import org.springframework.beans.factory.annotation.Autowired;
  5. import org.springframework.http.HttpStatus;
  6. import org.springframework.http.ResponseEntity;
  7. import org.springframework.stereotype.Controller;
  8. import org.springframework.web.bind.annotation.*;
  9. import java.util.List;
  10. @Controller
  11. @RequestMapping("bookservice")
  12. public class BookStoreController {
  13. @Autowired
  14. private IBookStoreService service;
  15. @GetMapping("books")
  16. public ResponseEntity<List<Book>> getBooks(){
  17. List<Book> books = service.getBooks();
  18. return new ResponseEntity<List<Book>>(books, HttpStatus.OK);
  19. }
  20. @GetMapping("books/{id}")
  21. public ResponseEntity<Book> getBook(@PathVariable("id") Integer id){
  22. Book book = service.getBook(id);
  23. return new ResponseEntity<Book>(book, HttpStatus.OK);
  24. }
  25. @PostMapping("books")
  26. public ResponseEntity<Book> createBook(@RequestBody Book book){
  27. Book b = service.createBook(book);
  28. return new ResponseEntity<Book>(b, HttpStatus.OK);
  29. }
  30. @PutMapping("books/{id}")
  31. public ResponseEntity<Book> updateBook(@PathVariable("id") int id, @RequestBody Book book){
  32. Book b = service.updateBook(id, book);
  33. return new ResponseEntity<Book>(b, HttpStatus.OK);
  34. }
  35. @DeleteMapping("books/{id}")
  36. public ResponseEntity<String> deleteBook(@PathVariable("id") int id){
  37. boolean isDeleted = service.deleteBook(id);
  38. if(isDeleted){
  39. String responseContent = "Book has been deleted successfully";
  40. return new ResponseEntity<String>(responseContent,HttpStatus.OK);
  41. }
  42. String error = "Error while deleting book from database";
  43. return new ResponseEntity<String>(error,HttpStatus.INTERNAL_SERVER_ERROR);
  44. }
  45. }

2. BooStoreDAO.java

  1. package com.example.jpademo.dao;
  2. import com.example.jpademo.entity.Book;
  3. import org.springframework.stereotype.Repository;
  4. import org.springframework.transaction.annotation.Transactional;
  5. import javax.persistence.EntityManager;
  6. import javax.persistence.PersistenceContext;
  7. import javax.persistence.Query;
  8. import java.util.List;
  9. @Transactional
  10. @Repository
  11. public class BookStoreDAO implements IBookStoreDAO {
  12. @PersistenceContext
  13. private EntityManager entityManager;
  14. /**
  15. * This method is responsible to get all books available in database and return it as List<Book>
  16. */
  17. @SuppressWarnings("unchecked")
  18. @Override
  19. public List<Book> getBooks() {
  20. String hql = "FROM Book as atcl ORDER BY atcl.id";
  21. return (List<Book>) entityManager.createQuery(hql).getResultList();
  22. }
  23. /**
  24. * This method is responsible to get a particular Book detail by given book id
  25. */
  26. @Override
  27. public Book getBook(int bookId) {
  28. return entityManager.find(Book.class, bookId);
  29. }
  30. /**
  31. * This method is responsible to create new book in database
  32. */
  33. @Override
  34. public Book createBook(Book book) {
  35. entityManager.persist(book);
  36. Book b = getLastInsertedBook();
  37. return b;
  38. }
  39. /**
  40. * This method is responsible to update book detail in database
  41. */
  42. @Override
  43. public Book updateBook(int bookId, Book book) {
  44. //First We are taking Book detail from database by given book id and
  45. // then updating detail with provided book object
  46. Book bookFromDB = getBook(bookId);
  47. bookFromDB.setName(book.getName());
  48. bookFromDB.setAuthor(book.getAuthor());
  49. bookFromDB.setCategory(book.getCategory());
  50. bookFromDB.setPublication(book.getPublication());
  51. bookFromDB.setPages(book.getPages());
  52. bookFromDB.setPrice(book.getPrice());
  53. entityManager.flush();
  54. //again i am taking updated result of book and returning the book object
  55. Book updatedBook = getBook(bookId);
  56. return updatedBook;
  57. }
  58. /**
  59. * This method is responsible for deleting a particular(which id will be passed that record)
  60. * record from the database
  61. */
  62. @Override
  63. public boolean deleteBook(int bookId) {
  64. Book book = getBook(bookId);
  65. entityManager.remove(book);
  66. //we are checking here that whether entityManager contains earlier deleted book or not
  67. // if contains then book is not deleted from DB that's why returning false;
  68. boolean status = entityManager.contains(book);
  69. if(status){
  70. return false;
  71. }
  72. return true;
  73. }
  74. /**
  75. * This method will get the latest inserted record from the database and return the object of Book class
  76. * @return book
  77. */
  78. private Book getLastInsertedBook(){
  79. String hql = "from Book order by id DESC";
  80. Query query = entityManager.createQuery(hql);
  81. query.setMaxResults(1);
  82. Book book = (Book)query.getSingleResult();
  83. return book;
  84. }
  85. }

3. IBookStoreDAO.java

  1. package com.example.jpademo.dao;
  2. import com.example.jpademo.entity.Book;
  3. import java.util.List;
  4. public interface IBookStoreDAO {
  5. List<Book> getBooks();
  6. Book getBook(int bookId);
  7. Book createBook(Book book);
  8. Book updateBook(int bookId,Book book);
  9. boolean deleteBook(int bookId);
  10. }

4. Book.java

  1. package com.example.jpademo.entity;
  2. import javax.persistence.*;
  3. import java.io.Serializable;
  4. @Entity
  5. @Table(name="book")
  6. public class Book implements Serializable {
  7. private static final long serialVersionUID = 1L;
  8. @Id
  9. @GeneratedValue(strategy= GenerationType.AUTO)
  10. @Column(name="id")
  11. private int id;
  12. @Column(name="name")
  13. private String name;
  14. @Column(name="author")
  15. private String author;
  16. @Column(name="publication")
  17. private String publication;
  18. @Column(name="category")
  19. private String category;
  20. @Column(name="pages")
  21. private int pages;
  22. @Column(name="price")
  23. private int price;
  24. public int getId() {
  25. return id;
  26. }
  27. public void setId(int id) {
  28. this.id = id;
  29. }
  30. public String getName() {
  31. return name;
  32. }
  33. public void setName(String name) {
  34. this.name = name;
  35. }
  36. public String getAuthor() {
  37. return author;
  38. }
  39. public void setAuthor(String author) {
  40. this.author = author;
  41. }
  42. public String getPublication() {
  43. return publication;
  44. }
  45. public void setPublication(String publication) {
  46. this.publication = publication;
  47. }
  48. public String getCategory() {
  49. return category;
  50. }
  51. public void setCategory(String category) {
  52. this.category = category;
  53. }
  54. public int getPages() {
  55. return pages;
  56. }
  57. public void setPages(int pages) {
  58. this.pages = pages;
  59. }
  60. public int getPrice() {
  61. return price;
  62. }
  63. public void setPrice(int price) {
  64. this.price = price;
  65. }
  66. }

5. BookStoreService.java

  1. package com.example.jpademo.services;
  2. import com.example.jpademo.dao.IBookStoreDAO;
  3. import com.example.jpademo.entity.Book;
  4. import org.springframework.beans.factory.annotation.Autowired;
  5. import org.springframework.stereotype.Service;
  6. import java.util.List;
  7. @Service
  8. public class BookStoreService implements IBookStoreService {
  9. @Autowired
  10. private IBookStoreDAO dao;
  11. @Override
  12. public List<Book> getBooks() {
  13. return dao.getBooks();
  14. }
  15. @Override
  16. public Book createBook(Book book) {
  17. return dao.createBook(book);
  18. }
  19. @Override
  20. public Book updateBook(int bookId, Book book) {
  21. return dao.updateBook(bookId, book);
  22. }
  23. @Override
  24. public Book getBook(int bookId) {
  25. return dao.getBook(bookId);
  26. }
  27. @Override
  28. public boolean deleteBook(int bookId) {
  29. return dao.deleteBook(bookId);
  30. }
  31. }

6. IBookStoreService.java

  1. package com.example.jpademo.services;
  2. import com.example.jpademo.entity.Book;
  3. import java.util.List;
  4. public interface IBookStoreService {
  5. List<Book> getBooks();
  6. Book createBook(Book book);
  7. Book updateBook(int bookId, Book book);
  8. Book getBook(int bookId);
  9. boolean deleteBook(int bookId);
  10. }

7. JpademoApplication

  1. package com.example.jpademo;
  2. import org.springframework.boot.SpringApplication;
  3. import org.springframework.boot.autoconfigure.SpringBootApplication;
  4. @SpringBootApplication
  5. public class JpademoApplication {
  6. public static void main(String[] args) {
  7. SpringApplication.run(JpademoApplication.class, args);
  8. }
  9. }

Test

Build and test this project.

image-20221027110133726

When you see the following example messages, the application is well launched; we can call REST endpoints using POSTMAN.

  1. 2022-10-27 11:16:16.793 INFO 93488 --- [ main] com.example.jpademo.JpademoApplication : Starting JpademoApplication on username-macbookpro.local with PID 93488 (/Users/username/IdeaProjects/jpademo/target/classes started by username in /Users/username/IdeaProjects/jpademo)
  2. 2022-10-27 11:16:16.796 INFO 93488 --- [ main] com.example.jpademo.JpademoApplication : No active profile set, falling back to default profiles: default
  3. 2022-10-27 11:16:18.022 INFO 93488 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
  4. 2022-10-27 11:16:18.093 INFO 93488 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 50ms. Found 0 JPA repository interfaces.
  5. 2022-10-27 11:16:18.806 INFO 93488 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
  6. 2022-10-27 11:16:18.814 INFO 93488 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
  7. 2022-10-27 11:16:18.814 INFO 93488 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.41]
  8. 2022-10-27 11:16:18.886 INFO 93488 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
  9. 2022-10-27 11:16:18.886 INFO 93488 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2005 ms
  10. 2022-10-27 11:16:19.068 INFO 93488 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default]
  11. 2022-10-27 11:16:19.119 INFO 93488 --- [ main] org.hibernate.Version : HHH000412: Hibernate ORM core version 5.4.25.Final
  12. 2022-10-27 11:16:19.202 INFO 93488 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.1.2.Final}
  13. 2022-10-27 11:16:19.282 INFO 93488 --- [ main] com.zaxxer.hikari.HikariDataSource : defaultDataSource - Starting...
  14. 2022-10-27 11:16:20.025 INFO 93488 --- [ main] com.zaxxer.hikari.HikariDataSource : defaultDataSource - Start completed.
  15. 2022-10-27 11:16:20.035 INFO 93488 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
  16. 2022-10-27 11:16:21.929 INFO 93488 --- [ main] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
  17. 2022-10-27 11:16:21.937 INFO 93488 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
  18. 2022-10-27 11:16:22.073 WARN 93488 --- [ main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
  19. 2022-10-27 11:16:22.221 INFO 93488 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
  20. 2022-10-27 11:16:22.415 INFO 93488 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''
  21. 2022-10-27 11:16:22.430 INFO 93488 --- [ main] com.example.jpademo.JpademoApplication : Started JpademoApplication in 6.079 seconds (JVM running for 8.765)
  22. 2022-10-27 11:16:40.180 INFO 93488 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
  23. 2022-10-27 11:16:40.183 INFO 93488 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
  24. 2022-10-27 11:16:40.249 INFO 93488 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 66 ms

1. To get list of books call following endpoint with GET Request

  1. http://localhost:8080/bookservice/books

image-20221027112426189

2. To Create New Book use following url with POST Request

  1. http://localhost:8080/bookservice/books

Set content type as in header as application/json, set request body as raw with JSON payload

  1. {
  2. "name": "The Lion, the Witch and the Wardrobe",
  3. "author": "C. S. Lewis",
  4. "publication": "Amazon",
  5. "category": "Fantancy",
  6. "pages": 123,
  7. "price": 10
  8. }

image-20221027115733788

3. To get a particular book, use following url with GET request type in postman

  1. http://localhost:8080/bookservice/books/<id>

image-20221027115844378

4. To update Book in database, use following url with PUT request type in postman

  1. http://localhost:8080/bookservice/books/<id>
  • Set content type as in header as application/json

  • Set request body as raw with JSON payload

  1. {
  2. "name": "Black Beauty",
  3. "author": "Anna Sewell",
  4. "publication": "Amazon",
  5. "category": "Fantancy",
  6. "pages": 134,
  7. "price": 12
  8. }

image-20221027120144112

5. To delete a particular Book from database, use following url with DELETE request type in postman

  1. http://localhost:8080/bookservice/books/<id>

image-20221027120306830

  1. mysql> select * from book;
  2. +----+--------------------------+----------+----------------------------------+-------+-------+-------------+
  3. | id | author | category | name | pages | price | publication |
  4. +----+--------------------------+----------+----------------------------------+-------+-------+-------------+
  5. | 1 | Antoine de Saint-Exupery | Fantancy | The Little Prince | 100 | 50 | Amazon |
  6. | 2 | Anna Sewell | Fantancy | Black Beauty | 134 | 12 | Amazon |
  7. | 3 | Lewis Carroll | Fantancy | Alice's Adventures in Wonderland | 1500 | 240 | Amazon |
  8. +----+--------------------------+----------+----------------------------------+-------+-------+-------------+
  9. 3 rows in set (0.02 sec)