The previous section mainly introduces Spring Boot integrating Log4j2 and Slf4j to print and output logs to files. In the application development, it is inevitable to deal with the database. In the Java ecosystem, the commonly used open-source persistence layer frameworks are MyBatis, Hibernate, etc. to illustrate, the example project of Spring Boot 2.X will mainly use MyBatis or MyBatis plus (MyBatis is an excellent persistence layer framework, which supports customization SQL, stored procedures, and advanced mapping. MyBatis avoids almost all JDBC code and manual setting of parameters and getting result sets).

This section will take the user information table as an example to implement Spring Data JPA to connect SQL database and read and write data. It is mainly divided into the following parts:

• Dependency introduction of JPA
• JPA link MySQL
• JPA Entity @ Entity @Id
• JPA write, update, delete, query data
• JPA multi record write, query, paging query
• JPA custom SQL query

create database spring;

1. What is Spring Data JPA

JPA(Java Persistence API), whose Chinese name is java persistence API, was introduced from JDK 5 and is the standard Java specification of orm. JPA is mainly to simplify the development of Java persistence layer applications, integrate ORM frameworks such as Hibernate, TopLink, JDO, etc., and does not provide specific implementation.

Some advantages of JPA: Standardization: the interface / class provided by the standard JPA specification can be migrated to other JPA frameworks without modifying the code. Easy to use: use annotations to define the mapping between Java classes and relational databases without XML configuration. Easy to migrate: changing database and JPA framework hardly need to change code. Advanced features: comparable to the query ability of JDBC; can use object-oriented thinking to operate the database; support container level transactions such as big data sets, transactions and concurrency;

Main technologies of JPA: ORM: use annotations or XML to describe the mapping between objects and data tables API: Standard JPA interface and class. JPQL: an object-oriented query language to avoid close coupling between programs and specific SQL.

Spring Data JPA is a subset of Spring Data. Hibernate is used as the underlying ORM by default. Official website documents https://spring.io/projects/spring-data-jpa Here's how:

Spring Data JPA, part of the larger Spring Data family, makes it easy to easily implement JPA based repositories. This module deals with enhanced support for JPA based data access layers. It makes it easier to build Spring-powered applications that use data access technologies.

Spring's support for JPA is very strong, which makes the configuration of JPA more flexible; the creation and destruction of EntityManager, transaction management and other codes are extracted for unified management; some EJB functions are implemented, such as container injection support. Spring Data JPA further simplifies the business code. We just need to declare the interface of the persistence layer, and the rest will be handed to the framework to help you complete. It uses the standard method name to determine what kind of data operation logic you need to implement according to the standard method name.

With Spring Data JPA, you just need to write the Repository interface and name your methods according to the specification.

• Hibernate open source ORM (object / relationship mapping) framework.
• Spring Data JPA uses Hibernate as the underlying ORM by default, which is a subset of Spring Data.

2. Configuration of Spring Data JPA

How Spring Data JPA introduces dependency and link SQL data.

2.1 dependency introduction

Create a new project 02 SQL spring data JPA in IDEA. Check the following dependencies. If the related dependencies are downloaded slowly, you can replace the domestic image source:

Gradle dependency configuration

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    compileOnly 'org.projectlombok:lombok'
    runtimeOnly 'mysql:mysql-connector-java'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}

Maven dependency configuration

<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.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
      <exclusions>
        <exclusion>
          <groupId>org.junit.vintage</groupId>
          <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
  </dependencies>

2.2. Connect to SQL database

Edit the / src/main/resources/application.properties file and write the following:

# Database URL, user name, password, JDBC Driver to change the database only need to change these information
# MySQL 8 needs to specify serverTimezone to connect successfully
spring.datasource.url=jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.datasource.password=xiaoxian
spring.datasource.username=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# Some configurations of Hibernate
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
# Whether to display SQL execution statement in Log
spring.jpa.show-sql=true
# hibernate.ddl-auto configuration operation on database tables
# Create and create drop: delete and recreate the data table corresponding to Entity every time
# Update: update the data table structure according to the Entity, and the data table will not be deleted
# none: the default value. No operation is required. This is recommended in practice
spring.jpa.hibernate.ddl-auto=none

In particular, spring.jpa.hibernate.ddl-auto uses the create mode to facilitate automatic @Entity The annotated class automatically generates the corresponding data table, but it is not recommended in actual development. Otherwise, re running the project is a delete (pao) library (lu).

# Some logs of create mode
Hibernate: drop table if exists sys_user
Hibernate: create table sys_user (user_id bigint not null, user_address varchar(128), user_age integer, username varchar(16), primary key (user_id)) engine=InnoDB

3. Start using Spring Data JPA

The project is divided into three layers according to its functions:

< img SRC = "https://gitee.com/ylooq/image-repository/raw/master/image2020/20200301161620. PNG" ALT = "project structure" style = "zoom: 67%;" / >

API interface layer: provides a RESTful API interface, which is an interface for external interaction of the system. Interface service layer: the main logic part of the application. It is not recommended to write application logic in API interface layer. Data persistence layer: write the corresponding Repository interface to realize the interaction with MySQL database.

3.1 data sheet structure and @Entity Entity class

Here, a user information table is used as an example, with the structure as follows:

SQL: if the mode is create or update, it will be generated automatically when the code is running

-- MySQL Database, other databases may need to be modified by themselves
create table sys_user
(
	user_id bigint auto_increment,
	username varchar(18) not null,
	nickname varchar(36) not null,
	user_age tinyint null,
	user_sex varchar(2) null,
	constraint sys_user_pk
		primary key (user_id)
);

@Entity Entity class: create a new package with the name of entity. Create a new SysUser class under entity:

@Entity
@Getter
@Setter
@Table(name = "sys_user",schema = "spring")
public class SysUser {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long  userId;

    @Column(length = 18,unique = true,nullable = false,name = "username",updatable = true)
    @NotEmpty(message = "User name cannot be empty")
    @Pattern(regexp = "^[a-zA-Z0-9]{3,16}$", message = "User name needs 3 to 16 digits in English,number")
    private String  username;

    @Column(length = 18,nullable = false)
    @NotEmpty(message = "User nickname cannot be empty")
    private String  nickname;

    @Range(min=0, max=100,message = "Age needs to be between 0 and 100")
    private Integer userAge;

    // User sex will automatically map to the field name of user \
    @Column(length = 2)
    private String  userSex;
}

Code resolution:

@Entity: indicates that this is an entity class, which is used to annotate ORM mapping classes in JPA.

@Table(name = "sys_user", schema = "spring"): indicates the table name corresponding to the ORM mapping class, which is the class name by default. For example, when sys user is mapped to sys_user, Java hump naming is mapped to SQL, it is separated by underscore, and the field name is the same rule. Schema specifies the database, which is specified in the database connection configuration by default.

@Id: primary key annotation.

@Generated value (strategy = generationtype. Identity): Specifies the primary key generation policy.

• IDENTITY, auto increase primary key, automatically generated by the database
• SEQUENCE: to produce the primary key according to the SEQUENCE of the database
• TABLE, specify a data TABLE to save the primary key
• AUTO, automatically controlled by program, default

@Column (length = 18, unique = true, nullable = false, name = "", updatable = true): Specifies the length, uniqueness, nullability, field name of the field, and whether the field can be updated. The default is non unique constraint and nullable value. The field name is mapped according to the name rule by default, and updatable is true by default.

@NotEmpty(message = ''): cannot be empty, message means a prompt message when it is null or the character length is 0.

@Pattern: regular expression, for example, you can use to verify whether the user name and password meet the specification.

@Range: Specifies the maximum and minimum values, for example, the maximum score is 100.

3.2. Write JpaRepository interface

Create a new repository package and a new SysUserRepository interface. You don't need to write other code, so you can basically CURD the database:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import org.xian.boot.entity.SysUser;

@Repository
public interface SysUserRepository extends JpaRepository<SysUser, Long> {
// Jparepository < sysuser, long >, the first parameter specifies the Entity class, and the second specifies the primary key type
}

3.3 add, query, update and delete

Objective: to realize the RESTful API interface for adding, deleting, modifying and querying the database.

MyResponse: general message return class. Whether the operation of adding, deleting and modifying is successful and the class of information return:

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class MyResponse implements Serializable {
    private static final long serialVersionUID = -2L;
    private String status;
    private String message;
}

3.3.1 add a piece of data

Implement an API interface for new user data. Create a new service package and a new SysUserService class:

package org.xian.boot.service;
import org.springframework.stereotype.Service;
import org.xian.boot.MyResponse;
import org.xian.boot.entity.SysUser;
import org.xian.boot.repository.SysUserRepository;
import javax.annotation.Resource;

@Service
public class SysUserService {
    @Resource
    private SysUserRepository sysUserRepository;

    /**
     * Save a record
     * @param sysUser User information
     * @return Save results
     */
    public MyResponse save(SysUser sysUser) {
        try {
            sysUserRepository.save(sysUser);
            return new MyResponse("success", "New success");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage());
        }
    }
}

Code resolution:

@Service: define a Bean, and the annotated class will be automatically registered to the Spring container.

@Resource: relative to @ Autowired annotation, Bean is automatically assembled.

In the SysUserRepository interface above, the save() method inherits from the CrudRepository. Some source codes are as follows:

package org.springframework.data.repository;
@NoRepositoryBean
public interface CrudRepository<T, ID> extends Repository<T, ID> {
	/**
	 * Saves a given entity. Use the returned instance for further operations as the save operation might have changed the
	 * entity instance completely.
	 *
	 * @param entity must not be {@literal null}.
	 * @return the saved entity; will never be {@literal null}.
	 * @throws IllegalArgumentException in case the given {@literal entity} is {@literal null}.
	 */
	<S extends T> S save(S entity);
}

Create a new RESTful API interface, a new controller package, and a new SysUserController class:

@RestController
@RequestMapping(value = "/api/user")
public class SysUserController {
    @Resource
    private SysUserService sysUserService;

    @PostMapping(value = "/save")
    public MyResponse save(@RequestBody SysUser sysUser) {
        return sysUserService.save(sysUser);
    }
}

Run the project, and submit the following data to the http://localhost:8080/api/user/save, change different input values, verify @The role of annotations in the Entity class. Change some values and write more data to the database.

{
	"username":"xiaoxian",
	"nickname":"Brother Xiao Xian brother",
	"userAge":17,
	"userSex":"male"
}

3.3.2 querying a piece of data

Function, query user information according to user name username, add in SysUserRepository class:

    /**
     * Query user information according to user name
     *
     * @param username User name
     * @return User information
     */
    SysUser findByUsername(String username);

New for SysUserService:

    public SysUser find(String username) {
        return sysUserRepository.findByUsername(username);
    }

New SysUserController:

    @PostMapping(value = "/find")
    public SysUser find(@RequestBody String username) {
        return sysUserService.find(username);
    }

Rerun, access with Postname http://localhost:8080/api/user/find

3.3.3 updating user data

Objective: to implement an interface to change user information according to user name. SysUserService adds:

    public MyResponse update(SysUser sysUser) {
        // In the actual development, the corresponding business logic needs to be written according to the specific business. Here is just an example
        try {
            // You need to query the primary key according to the user name username, and then use the save method to update it
            SysUser oldSysUser = sysUserRepository.findByUsername(sysUser.getUsername());
            sysUser.setUserId(oldSysUser.getUserId());
            sysUserRepository.save(sysUser);
            return new MyResponse("success", "Update success");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage());
        }
    }

New SysUserController:

    @PostMapping(value = "/update")
    public MyResponse update(@RequestBody SysUser sysUser){
        return sysUserService.update(sysUser);
    }

Rerun, access with Postname http://localhost:8080/api/user/update.

3.3.4 delete a piece of data

Objective: to implement an API interface to delete user information. SysUserService added:

    public MyResponse delete (String username){
        try {
            SysUser oldSysUser = sysUserRepository.findByUsername(username);
            sysUserRepository.delete(oldSysUser);
            return new MyResponse("success", "Delete successful");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage());
        }
    }

New SysUserController:

    @PostMapping(value = "/delete")
    public MyResponse delete(@RequestBody String username){
        return sysUserService.delete(username);
    }

Rerun, access with Postname http://localhost:8080/api/user/delete

3.4 write multiple records

The writing of multiple records is similar to that of a single record. New for SysUserService:

    public MyResponse saveAll(List<SysUser> sysUserList) {
        try {
            sysUserRepository.saveAll(sysUserList);
            return new MyResponse("success", "New success");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage());
        }
    }

New SysUserController:

    @PostMapping(value = "/saveAll")
    public MyResponse saveAll(@RequestBody List<SysUser> sysUserList) {
        return sysUserService.saveAll(sysUserList);
    }

Rerun, access with Postname http://localhost:8080/api/user/saveAll , change the incoming data, and view the returned results.

3.5 browse all records

Implement an API interface to query all records. SysUserService adds:

    public List<SysUser> list(){
        return sysUserRepository.findAll();
    }

New SysUserController:

    @GetMapping(value = "list")
    public List<SysUser> list(){
        return sysUserService.list();
    }

Rerun, use postname get to access http://localhost:8080/api/user/list , you can see all the data in the returned database table.

3.6 paging

In section 3.5, all data is queried using this method, which is very inconvenient for tables with large amount of data. Here, an API interface for paging browsing will be implemented:

New for SysUserService:

    public Page<SysUser> page(Integer page, Integer size) {
        // Sort by userId, Sort.Direction.ASC/DESC ascending / descending
        Pageable pageable = PageRequest.of(page, size, Sort.Direction.ASC, "userId");
        return sysUserRepository.findAll(pageable);
    }

New SysUserController:

    @PostMapping(value = "page")
    public Page<SysUser> page(@RequestParam(defaultValue = "0") Integer page, @RequestParam(defaultValue = "3") Integer size) {
        // page numbered from 0
        // The default is to browse the first page, and the size of each page is 3
        return sysUserService.page(page, size);
    }

Rerun, use Postname mode to access http://localhost:8080/api/user/page?page=1&size=2 , you can see the data returned as follows:

{	// content result set
    "content": [
        {
            "userId": 12,
            "username": "zhang",
            "nickname": "Zhang Xiao Xian",
            "userAge": 23,
            "userSex": "male"
        },
        {
            "userId": 16,
            "username": "daxian",
            "nickname": "Big brother brother",
            "userAge": 19,
            "userSex": "male"
        }
    ],
    "pageable": {
        // Sorting information
        "sort": {
            "sorted": true,
            "unsorted": false,
            "empty": false
        },
        "offset": 2,
        "pageSize": 2, // Dataset size per page
        "pageNumber": 1, // The number of pages
        "paged": true,
        "unpaged": false
    },
    "totalElements": 5, // Total data volume
    "last": false, // Last page or not
    "totalPages": 3, // PageCount
    "size": 2, // Dataset size per page
    "number": 1, // Current page number
    "sort": {
        "sorted": true,
        "unsorted": false,
        "empty": false
    },
    "numberOfElements": 2, // Quantity of content
    "first": false, // First page or not
    "empty": false // Whether content content is empty
}

3.6 user defined query SQL

For a simple Query, you can read the rules introduced by extension, and write the method name directly according to the rules without additional SQL. In JPA, you can customize SQL Query statements through @ Query annotation. Here we will show how to Query user information according to the Nickname of the user:

New in SysUserRepository class:

    /**
     * Querying user information based on user nickname is equivalent to findByNicknameLike
     *
     * @param nickname User nickname
     * @param pageable paging
     * @return User information
     */
    @Query("SELECT sysUser  from SysUser sysUser where sysUser.nickname like %:nickname%")
    Page<SysUser> searchByNickname(@Param("nickname") String nickname, Pageable pageable);

    /**
     * Querying user information based on user nickname is equivalent to searchByNickname
     *
     * @param nickname User nickname
     * @param pageable paging
     * @return User information
     */
    Page<SysUser> findByNicknameLike(@Param("nickname") String nickname, Pageable pageable);

New for SysUserService:

    public Page<SysUser> searchByNickname(String nickname, Integer page, Integer size) {
        // Sort by userId
        Pageable pageable = PageRequest.of(page, size, Sort.Direction.ASC, "userId");
        return sysUserRepository.searchByNickname(nickname,pageable);
    }

New SysUserController:

    @PostMapping(value = "search")
    public Page<SysUser> search(@RequestParam String nickname, @RequestParam(defaultValue = "0") Integer page, @RequestParam(defaultValue = "3") Integer size) {
        return sysUserService.searchByNickname(nickname, page, size);
    }

Rerun, use Postname mode to access http://localhost:8080/api/user/search?nickname = Rui & page = 0 & size = 5 , you can see the data returned as follows:

4. Summary of this chapter

This chapter mainly introduces Spring Data JPA and how to use it. It demonstrates how JPA can add, delete, modify and update the database. For multi table queries like JPA, multi data sources are not supported one by one due to the length. Interested readers can further understand it through reference documents, extended reading and search engine.

In JPA, there are two ways to query multiple tables:

One is JPA's cascading query. Annotations such as @ onetomony, @ ManyToOne specify multiple table association rules in the @ Entity entity class;

The other is to create a new class to receive the returned result set, and then Query SQL through @ Query;

Extended reading: http://ityouknow.com/springboot/2016/08/20/spring-boot-jpa.html

Reference link: https://www.ibm.com/developerworks/cn/opensource/os-cn-spring-jpa/index.html

https://docs.spring.io/spring-data/jpa/docs/2.2.5.RELEASE/reference/html/

In the next chapter, how to integrate the Spring Boot into MyBatis or MyBatis plus will be the focus of the follow-up demonstration projects.

• MyBatis is an excellent persistence framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all JDBC code and manual setting of parameters and getting result sets.
• MyBatis plus (MP for short) is an enhancement tool of MyBatis. Based on MyBatis, only enhancements are made and no changes are made to simplify development and improve efficiency.

Individuals prefer MyBatis plus, but it should be widely used in actual development. If you have any good suggestions, you can click the message below to tell Xiaoxian.