Spring Boot 2.x –SQL Database (Spring Data JPA)

I am Xiaoxian, a non-slash youth who focuses on big data and distributed technology. I love Coding, reading, photography and life more!

Source code repository: github.com/zhshuixian/…

The previous section focused on integrating Spring Boot Log4j2 with Slf4j to print and export logs to files. In application development, it is inevitable to deal with database. In Java ecology, the commonly used open source persistence layer framework has MyBatis, Hibernate and so on. Example projects for Spring Boot 2.X will use MyBatis or MyBatis-Plus (MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping). MyBatis avoids almost all JDBC code and manually setting parameters and fetching result sets).

JPA connects to a SQL database and reads and writes Data from it.

• JPA dependency introduction
• JPA link MySQL
• JPA entities @Entity and @ID
• JPA writes, updates, deletes, and queries data
• JPA multiple record writing, query, paging query
• JPA custom SQL queries

MySQL is used here. If you want to use other databases such as PostgreSQL, just change the corresponding dependencies and specify the Driver package. You need to have MySQL or other SQL databases installed in advance.

Refer to the article under Linux install MySQL 8: blog.csdn.net/u010974701/…

After installation, run the following command:

create database spring;
Copy the code

1. What is Spring Data JPA

Java Persistence API JPA(Java Persistence API), introduced from JDK 5, is the standard Java specification of ORM. JPA is mainly designed to simplify the development of Java persistence layer applications. It integrates ORM frameworks such as Hibernate, TopLink and JDO, and does not provide concrete implementation.

Some of JPA’s advantages and features: Standardization: Interfaces/classes provided by the standard JPA specification can be migrated to other JPA frameworks with very little code modification. Easy to use: Use annotations to define mappings between Java classes and relational databases without XML configuration. Easy migration: Changing databases and changing JPA frameworks requires almost no code changes. Advanced features: JDBC-comparable query capabilities; You can use object-oriented thinking to manipulate the database; Support container-level transactions such as large data sets, transactions, and concurrency;

JPA main technologies: ORM: using annotations or XML to describe the mapping between objects and data tables API: standard JPA interfaces, classes. JPQL: Object-oriented query language that avoids tight coupling between programs and concrete SQL.

Spring Data JPA is a subset of Spring Data and uses Hibernate as the underlying ORM by default. IO /projects/sp… Here’s the introduction:

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 powerful, making JPA configuration more flexible; Extract EntityManager creation and destruction, transaction management and other codes for unified management; Implemented some EJB features, such as container injection support. Spring Data JPA takes this one step further and simplifies the business code by simply declaring the interfaces to the persistence layer. The framework does the rest for you, using canonical method names that determine what Data manipulation logic you need to implement.

Read: according to the method name automatically generated SQL rules can reference smile brother’s blog: ityouknow.com/springboot/…

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

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

2. Configuration of Spring Data JPA

How Spring Data JPA introduces dependencies and links to SQL Data.

2.1 dependency introduction

Create a project in IDEA called 02-sqL-spring-data-jPA and check the following dependencies. If the dependencies download slowly, you can change the domestic mirror source:

Gradle depends on 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'
    }
}
Copy the code

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>
Copy the code

2.2. Connect to SQL database

Edit/SRC/main/resources/application. The properties file, write the following content:

You only need to change the database URL, username, password, and JDBC Driver
MySQL 8 needs to specify serverTimezone to connect to MySQLspring.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 configuration of Hibernate
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
Whether to display SQL execution statements in Log
spring.jpa.show-sql=true
# hibernate.ddl-auto configures operations on database tables
# create and create-drop: Drop the Entity table and recreate it each time
# update: Update the data table structure according to the Entity without deleting the data table
# None: The default value, which does nothing, is recommended in practice
spring.jpa.hibernate.ddl-auto=none
Copy the code

In spring.jpa.hibernate.dcl-auto, the create mode can be used to automatically generate the corresponding data table from the @Entity annotation class, but it is not recommended to use this mode in actual development, otherwise re-running the project is a pao library (LU).

# Create some logs for the 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
Copy the code

3. Start using Spring Data JPA

The project is divided into the following three layers according to functions:

API Interface layer: Provides RESTful API interfaces for system external interaction. Interface service layer: The main logical part of an application. It is not recommended to write application logic on the API interface layer. Data persistence layer: Write corresponding Repository interface to implement interaction with MySQL database.

3.1 Data table structure and @Entity Entity class

Here, a user information table is used as an example, structured as follows:

The field name	The field type	note
user_id	bigint	Primary key, increment
username	varchar(18)	User name, not null unique
nickname	varchar(36)	User nickname, non-empty
user_age	tinyint	The user’s age
user_sex	varchar(2)	User’s gender

SQL: In create or UPDATE mode, it is automatically generated when the code is running

MySQL database, other databases may need to modify
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)
);
Copy the code

Entity class: Create a package with the name 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 dec} {$", message = "User name must be 3 to 16 digits in English, numerals")
    private String  username;

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

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

    // userSex is automatically mapped to the field name of user_sex
    @Column(length = 2)
    private String  userSex;
}
Copy the code

Code parsing:

@Entity: Indicates that this is an Entity class and is used in JPA to annotate ORM mapping classes.

@table (name = “sys_user”, schema = “spring”) : Specify the Table name of the ORM mapping class. The default is the class name. For example, SysUser is mapped to sys_user, and Java camel name is mapped to SQL, separated by underscores (_). The same rule applies to field names. Schema Specifies the database, which is specified in the database connection configuration by default.

@id: primary key annotation.

@GeneratedValue(Strategy = GenerationType.identity) : Specifies the primary key generation strategy.

• IDENTITY, auto-increment primary key, automatically generated by the database
• SEQUENCE produces primary keys based on the SEQUENCE of the database
• TABLE, which specifies a TABLE to hold the primary key
• AUTO: automatically controlled by the program. Default value

@column (length = 18,unique = true, Nullable = false,name = “”, updatable = true) : Specifies the length of the Column, whether it is unique, whether it can be null, the Column name, and whether it can be updated. The default constraint is not unique and can be null; the field name is mapped according to the name rule by default; updateable defaults to true.

@notempty (message = “”) : cannot be empty. Message indicates null or a message with a character length of 0.

@pattern: regular expressions, for example, that you can use to verify that a user name or password conforms to the specification.

@range: specifies maximum and minimum values, such as a maximum score of 100.

3.2. Write the JpaRepository interface

Create a new repository package, create a new SysUserRepository interface, and you don’t need to write any other code to CURD your 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
      
        with the first argument specifying the Entity class and the second specifying the primary key type
      ,>
}
Copy the code

3.3. Add, query, update and delete

Objective to achieve the RESTful API interface for adding, deleting, changing and checking databases.

MyResponse: general message return class, add, delete, modify the success of the operation and information return class:

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

3.3.1 Add a piece of data

Implement a new user data API interface. Create a service package and create a 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 *@paramSysUser User information *@returnSave the result */
    public MyResponse save(SysUser sysUser) {
        try {
            sysUserRepository.save(sysUser);
            return new MyResponse("success"."New success");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage()); }}}Copy the code

Code parsing:

@service: Defines a Bean. The annotated class is automatically registered with the Spring container.

@Resource: Automatic assembly of beans as opposed to the @autowired annotation.

In SysUserRepository, the save() method is inherited from CrudRepository.

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);
}
Copy the code

Creating RESTful apis, creating controller packages, and creating SysUserController classes:

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

    @PostMapping(value = "/save")
    public MyResponse save(@RequestBody SysUser sysUser) {
        returnsysUserService.save(sysUser); }}Copy the code

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

{
	"username":"xiaoxian"."nickname":"Little First brother"."userAge":17."userSex":"Male"
}
Copy the code

3.3.2. Query a data

SysUserRepository (); SysUserRepository ();

    /** * Query user information by user name **@paramUsername username *@returnUser information */
    SysUser findByUsername(String username);
Copy the code

SysUserService feature:

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

SysUserController feature:

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

Rerun, use Postname visit http://localhost:8080/api/user/find

3.3.3 Update user data

SysUserService implements an interface to change user information based on the user name.

    public MyResponse update(SysUser sysUser) {
        // In 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 username username, and then use the save method to update
            SysUser oldSysUser = sysUserRepository.findByUsername(sysUser.getUsername());
            sysUser.setUserId(oldSysUser.getUserId());
            sysUserRepository.save(sysUser);
            return new MyResponse("success"."Update successful");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage()); }}Copy the code

SysUserController feature:

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

Rerun, use Postname visit http://localhost:8080/api/user/update.

3.3.4 Delete a piece of data

SysUserService implements an API to delete user information.

    public MyResponse delete (String username){
        try {
            SysUser oldSysUser = sysUserRepository.findByUsername(username);
            sysUserRepository.delete(oldSysUser);
            return new MyResponse("success"."Deleted successfully");
        } catch (Exception e) {
            return new MyResponse("error", e.getMessage()); }}Copy the code

SysUserController feature:

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

Rerun, use Postname visit http://localhost:8080/api/user/delete

3.4 Write multiple records

Writing to multiple records is similar to writing to a single record. SysUserService feature:

    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()); }}Copy the code

SysUserController feature:

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

Rerun, use Postname visit http://localhost:8080/api/user/saveAll to change the incoming data, view the results returned.

3.5. Browse All Records

SysUserService implements an API interface to query all records.

    public List<SysUser> list(a){
        return sysUserRepository.findAll();
    }
Copy the code

SysUserController feature:

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

Rerun, use Postname GET access to http://localhost:8080/api/user/list, you can see returns all data in the database table.

3.6. Paging browsing

In section 3.5, this method is used to query all data, which is very inconvenient for a large amount of data. Here we will implement a paging browsing API interface:

SysUserService feature:

    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);
    }
Copy the code

SysUserController feature:

    @PostMapping(value = "page")
    public Page<SysUser> page(@RequestParam(defaultValue = "0") Integer page, @RequestParam(defaultValue = "3") Integer size) {
        // page is numbered from 0
        // Browse the first page by default, each page size is 3
        return sysUserService.page(page, size);
    }
Copy the code

Rerun, use Postname access to http://localhost:8080/api/user/page? Page =1&size=2, you can see that the following data is returned:

{// content result set "content": [{"userId": 12, "username": "zhang", "nickname": "zhang", "userAge": 23, "userSex": "Male"}, {" userId ": 16," username ":" daxian ", "nickname" : "big brother" first, "userAge" : 19, "userSex" : "male"}], "pageable" : "Sort ": {"sorted": true, "unsorted": false, "empty": false}, "offset": 2, "pageSize": 1, // pageNumber: 1, // pageNumber" paged": true, "unpaged": false}, "totalElements": 5, // False, // If last page "totalPages": 3, // total page "size": 2, // data set size per page "number": 1, // current page "sort": {"sorted": True, "unsorted": false, "empty": false}, "numberOfElements": 2, // Number of contents "first": false, // whether the first page "empty": False // content Whether the content is empty}Copy the code

3.6. Customize query SQL

For simple queries, you can simply write method names based on the rules by extending the rules you read about, without having to write your own additional SQL. In JPA, you can customize SQL Query statements with the @Query annotation. Here we demonstrate fuzzy query of user information based on Nickname:

New SysUserRepository class:

    FindByNicknameLike = findByNicknameLike = findByNicknameLike = findByNicknameLike@paramNickname user *@paramPageable pages *@returnUser 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 the user nickname is equivalent to searchByNickname@paramNickname user *@paramPageable pages *@returnUser information */
    Page<SysUser> findByNicknameLike(@Param("nickname") String nickname, Pageable pageable);

Copy the code

SysUserService feature:

    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);
    }
Copy the code

SysUserController feature:

    @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);
    }
Copy the code

Rerun, use Postname access to http://localhost:8080/api/user/search? Nickname =0& page=0&size=5

4. Summary of this chapter

This chapter mainly introduces Spring Data JPA and how to use it, demonstrating how to add, delete, modify and update the database. For multi-table query like JPA, multi-data source support is not extended one by one due to the length. Interested readers can delve further through reference documentation, extended reading, and search engines.

In JPA, there are two ways to do multi-table queries:

One is the cascading query of JPA, where @onetomany, @ManyToOne and other annotations specify multiple table association rules in the @Entity Entity class.

The other is to create a new class that accepts the returned result set and then customize the Query SQL via @Query.

Further reading: ityouknow.com/springboot/…

Reference link: www.ibm.com/developerwo…

Docs. Spring. IO/spring – the data…

In the next chapter, the practical Spring Boot will integrate MyBatis or MyBatis-Plus, and the subsequent practical demonstration projects will also be based on one of the two frameworks.

• MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all of the JDBC code and manual setting of parameters and fetching result sets.
• MyBatis-Plus (MP for short) is a MyBatis enhancement tool, on the basis of MyBatis only do enhancement do not change, to simplify the development and improve efficiency.

Personally, I prefer MyBatis-Plus, but MyBatis should be widely used in the actual development. If you have any good suggestions, you can click on the comments below to let Xiao Xian know.