Build a SpringBoot+Spring Data JPA CRUD demo with MatrixOne

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 0.6. 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>
  11.     <properties>
  12.         <java.version>1.8</java.version>
  13.         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  14.         <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  15.         <spring-boot.version>2.3.7.RELEASE</spring-boot.version>
  16.     </properties>
  18.     <dependencies>
  19.         <dependency>
  20.             <groupId>org.springframework.boot</groupId>
  21.             <artifactId>spring-boot-starter-data-jpa</artifactId>
  22.         </dependency>
  23.         <dependency>
  24.             <groupId>org.springframework.boot</groupId>
  25.             <artifactId>spring-boot-starter-jdbc</artifactId>
  26.         </dependency>
  27.         <dependency>
  28.             <groupId>org.springframework.boot</groupId>
  29.             <artifactId>spring-boot-starter-web</artifactId>
  30.         </dependency>
  32.         <dependency>
  33.             <groupId>mysql</groupId>
  34.             <artifactId>mysql-connector-java</artifactId>
  35.             <scope>runtime</scope>
  36.         </dependency>
  37.         <dependency>
  38.             <groupId>org.springframework.boot</groupId>
  39.             <artifactId>spring-boot-starter-test</artifactId>
  40.             <scope>test</scope>
  41.             <exclusions>
  42.                 <exclusion>
  43.                     <groupId>org.junit.vintage</groupId>
  44.                     <artifactId>junit-vintage-engine</artifactId>
  45.                 </exclusion>
  46.             </exclusions>
  47.         </dependency>
  48.     </dependencies>
  50.     <dependencyManagement>
  51.         <dependencies>
  52.             <dependency>
  53.                 <groupId>org.springframework.boot</groupId>
  54.                 <artifactId>spring-boot-dependencies</artifactId>
  55.                 <version>${spring-boot.version}</version>
  56.                 <type>pom</type>
  57.                 <scope>import</scope>
  58.             </dependency>
  59.         </dependencies>
  60.     </dependencyManagement>
  62.     <build>
  63.         <plugins>
  64.             <plugin>
  65.                 <groupId>org.apache.maven.plugins</groupId>
  66.                 <artifactId>maven-compiler-plugin</artifactId>
  67.                 <version>3.8.1</version>
  68.                 <configuration>
  69.                     <source>1.8</source>
  70.                     <target>1.8</target>
  71.                     <encoding>UTF-8</encoding>
  72.                 </configuration>
  73.             </plugin>
  74.             <plugin>
  75.                 <groupId>org.springframework.boot</groupId>
  76.                 <artifactId>spring-boot-maven-plugin</artifactId>
  77.                 <version>2.3.7.RELEASE</version>
  78.                 <configuration>
  79.                     <mainClass>com.example.jpademo.JpademoApplication</mainClass>
  80.                 </configuration>
  81.                 <executions>
  82.                     <execution>
  83.                         <id>repackage</id>
  84.                         <goals>
  85.                             <goal>repackage</goal>
  86.                         </goals>
  87.                     </execution>
  88.                 </executions>
  89.             </plugin>
  90.         </plugins>
  91.     </build>
  93. </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
  8. # Database connection url, modify to MatrixOne address and port, with parameters
  9. 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
  10. # Database username and password
  11. spring.datasource.username=dump
  12. spring.datasource.password=111
  13. # Web application port
  14. server.port=8080
  16. # Hibernate configurations
  17. spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLDialect
  18. spring.jpa.properties.hibernate.id.new_generator_mappings = false
  19. spring.jpa.properties.hibernate.format_sql = true
  20. 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;
  3. import com.example.jpademo.entity.Book;
  4. import com.example.jpademo.services.IBookStoreService;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.http.HttpStatus;
  7. import org.springframework.http.ResponseEntity;
  8. import org.springframework.stereotype.Controller;
  9. import org.springframework.web.bind.annotation.*;
  11. import java.util.List;
  13. @Controller
  14. @RequestMapping("bookservice")
  15. public class BookStoreController {
  17.     @Autowired
  18.     private IBookStoreService service;
  20.     @GetMapping("books")
  21.     public ResponseEntity<List<Book>> getBooks(){
  23.         List<Book> books = service.getBooks();
  24.         return new ResponseEntity<List<Book>>(books, HttpStatus.OK);
  26.     }
  28.     @GetMapping("books/{id}")
  29.     public ResponseEntity<Book> getBook(@PathVariable("id") Integer id){
  30.         Book book = service.getBook(id);
  31.         return new ResponseEntity<Book>(book, HttpStatus.OK);
  32.     }
  34.     @PostMapping("books")
  35.     public ResponseEntity<Book> createBook(@RequestBody Book book){
  36.         Book b = service.createBook(book);
  37.         return new ResponseEntity<Book>(b, HttpStatus.OK);
  39.     }
  41.     @PutMapping("books/{id}")
  42.     public ResponseEntity<Book> updateBook(@PathVariable("id") int id, @RequestBody Book book){
  44.         Book b = service.updateBook(id, book);
  45.         return new ResponseEntity<Book>(b, HttpStatus.OK);
  46.     }
  48.     @DeleteMapping("books/{id}")
  49.     public ResponseEntity<String> deleteBook(@PathVariable("id") int id){
  50.         boolean isDeleted = service.deleteBook(id);
  51.         if(isDeleted){
  52.             String responseContent = "Book has been deleted successfully";
  53.             return new ResponseEntity<String>(responseContent,HttpStatus.OK);
  54.         }
  55.         String error = "Error while deleting book from database";
  56.         return new ResponseEntity<String>(error,HttpStatus.INTERNAL_SERVER_ERROR);
  57.     }
  59. }

2. BooStoreDAO.java

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

3. IBookStoreDAO.java

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

4. Book.java

  1. package com.example.jpademo.entity;
  3. import javax.persistence.*;
  4. import java.io.Serializable;
  6. @Entity
  7. @Table(name="book")
  8. public class Book implements Serializable {
  10.     private static final long serialVersionUID = 1L;
  11.     @Id
  12.     @GeneratedValue(strategy= GenerationType.AUTO)
  13.     @Column(name="id")
  14.     private int id;
  16.     @Column(name="name")
  17.     private String name;
  19.     @Column(name="author")
  20.     private String author;
  22.     @Column(name="publication")
  23.     private String publication;
  25.     @Column(name="category")
  26.     private String category;
  28.     @Column(name="pages")
  29.     private int pages;
  31.     @Column(name="price")
  32.     private int price;
  34.     public int getId() {
  35.         return id;
  36.     }
  38.     public void setId(int id) {
  39.         this.id = id;
  40.     }
  42.     public String getName() {
  43.         return name;
  44.     }
  46.     public void setName(String name) {
  47.         this.name = name;
  48.     }
  50.     public String getAuthor() {
  51.         return author;
  52.     }
  54.     public void setAuthor(String author) {
  55.         this.author = author;
  56.     }
  58.     public String getPublication() {
  59.         return publication;
  60.     }
  62.     public void setPublication(String publication) {
  63.         this.publication = publication;
  64.     }
  66.     public String getCategory() {
  67.         return category;
  68.     }
  70.     public void setCategory(String category) {
  71.         this.category = category;
  72.     }
  74.     public int getPages() {
  75.         return pages;
  76.     }
  78.     public void setPages(int pages) {
  79.         this.pages = pages;
  80.     }
  82.     public int getPrice() {
  83.         return price;
  84.     }
  86.     public void setPrice(int price) {
  87.         this.price = price;
  88.     }
  90. }

5. BookStoreService.java

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

6. IBookStoreService.java

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

7. JpademoApplication

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

Test

Build and test this project.

image-20221027110133726

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

  1. 2022-10-27 11:16:16.793  INFO 93488 --- [           main] com.example.jpademo.JpademoApplication   : Starting JpademoApplication on nandeng-macbookpro.local with PID 93488 (/Users/nandeng/IdeaProjects/jpademo/target/classes started by nandeng in /Users/nandeng/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)