使用 SpringBoot 和 Spring Data JPA 构建一个 CRUD 示例

本篇文档将指导你如何使用 SpringBoot、Spring Data JPA 和 Intellij IDEA 构建一个简单的应用程序，并实现 CRUD（创建、读取、更新、删除）功能。

开始之前

本篇教程涉及到的软件介绍如下：

  • Spring Data JPA：JPA（Java Persistence API，Java 持久层 API）是一种规范，是 JDK 5.0 注解或 XML 描述对象与关系表的映射关系，并将运行期的实体对象持久化到数据库中。Spring Data JPA 是一个 Java 对象映射关系的解决方案的 ORM（Object-Relational Mapping）框架，是一个将面向对象的域模型映射到关系数据库的开源框架。

  • Intellij IDEA：IntelliJ IDEA 是一种商业化销售的 Java 集成开发环境（Integrated Development Environment，IDE）工具软件。它所拥有诸多插件，可以提高我们的工作效率。

  • Maven：Maven 是 Java 中功能强大的项目管理工具，可以根据 pom.xml 文件中的配置自动下载和导入 Jar 文件。这个特性减少了不同版本 Jar 文件之间的冲突。

  • Spring：Spring 是 Java 中最流行的框架之一，越来越多的企业使用 Spring 框架来构建他们的项目。Spring Boot 构建在传统的 Spring 框架之上。因此，它提供了 Spring 的所有特性，而且比 Spring 更易用。

  • Postman: Postman 是一个用于 API 测试的应用程序。它是一个 HTTP 客户端，利用图形用户界面测试 HTTP 请求，以获得需要进行验证的不同类型的响应。

配置环境

1. 安装构建 MatrixOne

按照步骤介绍完成安装单机版 MatrixOne，在 MySQL 客户端新建一个命名为 test 数据库。

  1. mysql> create database test;

2. 使用 IntelliJ IDEA 创建一个新的 Spring Boot 项目

选择 Spring Initializer，按需命名项目名称。

image-20221027094625081

选择如下依赖项：

  • Spring Web
  • JDBC API
  • Spring Data JPA
  • MySQL Driver

image-20221027101504418

点击 Create 创建项目。依赖项列在 pom.xml 文件中。通常你无需修改任何东西。

  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. 修改 application.properties 文件

进入到 resources 文件目录下，配置 application.properties 文件，完成 MatrixOne 连接。

  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. 在 MatrixOne 中新建表并插入数据

使用 MySQL 客户端连接到 MatrixOne 并执行以下 SQL 语句。你可以将这些 SQL 语句保存在 /resource/database/ 目录下的 book.sql 中。

  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');

编写代码

完成环境配置后，我们编写代码来实现一个简单的 CRUD 应用程序。

在完成编写编码后，你将拥有一个如下所示的项目结构。你可以预先创建这些包和 java 类。

我们将为这个演示应用程序编写创建、更新、插入、删除和选择操作。

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. }

测试

构建并启动这个项目。

image-20221027110133726

当出现下面的消息时，表示应用程序已经正常启动，你可以使用 Postman 调用 REST 端口。

  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. 获取 Book 列表，使用 GET 请求调用以下端口

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

image-20221027112426189

2. 创建一个新 Book，使用 POST 请求调用以下端口

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

将 Header 中的内容类型设置为 application/json，将 Request Body 设置为原始 JSON 有效负载。

  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. 如需获取特定 Book，使用 GET 请求调用以下端口

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

image-20221027115844378

4. 在数据库中升级 Book，使用 PUT 请求调用以下端口

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

set content type as in header as application/json

set request body as raw with JSON payload

  • 将 Header 中的内容类型设置为 application/json。

  • 将 Request Body 设置为原始 JSON 有效负载

  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. 如需从数据库中删除特定的 Book，使用 DELETE 请求调用以下端口

  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)