preface

The first time I used Spring JPA, I felt like it was a godsend. I didn’t need to write any database access code to get a basic CURD function out. Let’s use an example to illustrate the basic operations that JPA uses.

Create a new project and add dependencies

Create an empty SpringBoot project in Intellij IDEA. For details, see the first Encounter in SpringBoot. Based on the requirements of this sample, we will add the following three dependencies

<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> The < version > 6.0.6 < / version > < / dependency >Copy the code

Preparing the Database Environment

For this project, we created a new springBoot_JPA database and authorized the SpringBoot user

create database springboot_jpa;

grant all privileges on springboot_jpa.* to 'springboot'@The '%' identified by 'springboot';

flush privileges;
Copy the code

Project configuration

# Common data source configurationspring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver Spring. The datasource. Url = JDBC: mysql: / / 10.110.2.56:3306 / springboot_jpa? charset=utf8mb4&useSSL=false
spring.datasource.username=springboot
spring.datasource.password=springboot
# Hikari Data source specific configuration
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
JPA related configuration
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create
Copy the code

The data source configuration is the same as in the previous article using JDBC Templet in SpringBoot. The next few configurations need to be explained

  1. Spring.jpa. show-sql=true Displays executed SQL statements in logs.
  2. The spring.jpa.Hibernate. Dddl-auto =create configuration specifies that tables corresponding to entity classes are deleted and created at program startup. This parameter is dangerous because it will delete the corresponding table and rebuild it. So never use it in a build environment. Only once in the test environment, when the database structure is initially initialized.
  3. Spring. Jpa. Database – platform = org. Hibernate. The dialect. MySQL5InnoDBDialect. In SrpingBoot 2.0, When Hibernate creates tables, the default database storage engine is MyISAM (InnoDB). This parameter is used to switch the default storage engine to InnoDB when creating tables.

Create the first data entity class

The database entity class is a POJO Bean object. Let’s create a UserDO database entity. The source code for the database entity is as follows

package com.yanggaochao.springboot.learn.springbootjpalearn.security.domain.dao; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; /** * user Entity class ** @author * @since 2018-03-12 */ @entity @table (name ="AUTH_USER")
public class UserDO {
    @Id
    private Long id;
    @Column(length = 32)
    private String name;
    @Column(length = 32)
    private String account;
    @Column(length = 64)
    private String pwd;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd; }}Copy the code

Among them:

  1. @Entity is a mandatory annotation that declares that this class corresponds to a database table.
  2. @table (name = “AUTH_USER”) is an optional annotation. Specifies the table information corresponding to the database entity. Table name, index information, and so on. The table name for this entity class is declared AUTH_USER. If not specified, the table name is the same as the entity name.
  3. The @ID annotation declares the attribute that uniquely identifies the entity.
  4. @column (Length = 32) Specifies the definition of the table field that declares the attributes of the entity. The default entity corresponds to one field of the table for each attribute. By default, the field names are the same as the property names (but not necessarily equal). The type of the field is automatically inferred from the entity attribute type. Here we declare the length of the character field. If you do not, the system takes 255 as the length of the field

If all the above configurations are correct, run the project at this time, and we can see the following contents in the log:

Hibernate: drop table if exists auth_user
Hibernate: create table auth_user (id bigint not null, account varchar(32), name varchar(32), pwd varchar(64), primary key (id)) engine=InnoDB
Copy the code

The system built the data sheet for us automatically. View tables and table structures in the database

The above process is similar to what we used to do with Hibernate, from the declaration of database entities to the automatic creation of tables. Now let’s enter the world of Spring Data JPA and see what it has to offer

Implement a persistence layer service

In the world of Spring Data JPA, implementing a persistence layer of services is a very simple matter. For example, if we want to implement a persistence layer service for adding, deleting, modifying, and querying the UserDO entity object, we only need to declare an interface. The interface inheritance org. Springframework. Data. The repository. The repository < T, ID > interfaces, or his son. Here in order to function fully, we inherited the org. Springframework. Data. Jpa. Repository. JpaRepository < T, ID > interface. Where T is the database entity class and ID is the primary key of the database entity class. Then simply add an @repository annotation to the interface.

package com.yanggaochao.springboot.learn.springbootjpalearn.security.dao; import com.yanggaochao.springboot.learn.springbootjpalearn.security.domain.dao.UserDO; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.stereotype.Repository; /** * user service data interface class ** @author Yang Chao * @since 2018-03-12 */ @repository package com.yanggaochao.springboot.learn.springbootjpalearn.security.dao; import com.yanggaochao.springboot.learn.springbootjpalearn.security.domain.dao.UserDO; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.stereotype.Repository; /** * @author Yang Chao * @since 2018-03-12 */ @repository public interface UserDao extends JpaRepository<UserDO, Long> { }Copy the code

Not a single line of code. So for the entity class UserDO, we already have the following functionality

For example, we use the following code to save some user entities to the database.

package com.yanggaochao.springboot.learn.springbootjpalearn;

import com.yanggaochao.springboot.learn.springbootjpalearn.security.dao.UserDao;
import com.yanggaochao.springboot.learn.springbootjpalearn.security.domain.dao.UserDO;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Optional;

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserDOTest {

    @Autowired
    private UserDao userDao;

    @Before
    public void before() {
        UserDO userDO = new UserDO();
        userDO.setId(1L);
        userDO.setName("The wind is clear");
        userDO.setAccount("fengqy");
        userDO.setPwd("123456");
        userDao.save(userDO);
        userDO = new UserDO();
        userDO.setId(3L);
        userDO.setName("Oriental invincibility");
        userDO.setAccount("bubai");
        userDO.setPwd("123456");
        userDao.save(userDO);
        userDO.setId(5L);
        userDO.setName("Ask the heavens.");
        userDO.setAccount("wentian");
        userDO.setPwd("123456");
        userDao.save(userDO);
    }
    @Test
    public void testAdd() {
        UserDO userDO = new UserDO();
        userDO.setId(2L);
        userDO.setName("Let it go.");
        userDO.setAccount("renwox");
        userDO.setPwd("123456");
        userDao.save(userDO);
        userDO = new UserDO();
        userDO.setId(4L);
        userDO.setName("Linghu Chong");
        userDO.setAccount("linghuc");
        userDO.setPwd("123456");
        userDao.save(userDO);
    }

    @After
    public void after() { userDao.deleteById(1L); userDao.deleteById(3L); userDao.deleteById(5L); }}Copy the code

This one uses Junit to execute the test case. The @before annotation executes the prepared code Before the test case. Insert three user information here. When this test is complete, you can look at the database and see that there are 5 records in the database:

We can also verify the correctness of the lookup object function by identification, the query function for all data, and even the query function for sorting and paging through test cases

    @Test
    public void testLocate() {
        Optional<UserDO> userDOOptional = userDao.findById(1L);
        if (userDOOptional.isPresent()) {
            UserDO userDO = userDOOptional.get();
            System.out.println("name = " + userDO.getName());
            System.out.println("account = " + userDO.getAccount());
        }
    }

    @Test
    public void testFindAll() {
        List<UserDO> userDOList = userDao.findAll(new Sort(Sort.Direction.DESC,"account"));
        for (UserDO userDO : userDOList) {
            System.out.println("name = " + userDO.getName());
            System.out.println("account = "+ userDO.getAccount()); }}Copy the code

As you can see, all we did was add database configuration information to the SpingBoot project, declare a UserDO database entity object, and declare a persistence layer interface. Change interface inherits from the org. Springframework. Data. Jpa. Repository. JpaRepository < T, ID > interface. Then, the system automatically has a rich increase, delete, modify, query function. The query capability even has sorting and paging capabilities.

This is the power of JPA. In addition to these interfaces, users will have other needs, and JPA will meet your needs as well.

To expand the query

From the screenshot above “UserDao query entity deletion”, we can see that the query function is not satisfactory and many of the query functions we want are not yet available. But don’t worry. JPA has a very convenient and elegant way to do this

Query by attribute

If we want to query based on a property of an entity we can declare the interface in the UserDao interface. For example, if we want to query based on the entity’s account attribute (which we might use when logging in). We are com. Yanggaochao. Springboot. Learn. Springbootjpalearn. Security. Dao. Add a UserDao interface declaration is ok

  UserDO findByAccount(String account);
Copy the code

Then add a test case

@Test
    public void testFindByAccount() {
        UserDO userDO = userDao.findByAccount("wentian");
        if(userDO ! = null) { System.out.println("name = " + userDO.getName());
            System.out.println("account = "+ userDO.getAccount()); }}Copy the code

After running, it will be printed in the log

Name = Account = wentianCopy the code

This is a very powerful way to support not only a single attribute, but also combinations of attributes. For example, if we want to find the interface where the account and password both meet the query criteria. So we declare it in the UserDao interface

UserDO findByAccountAndPwd(String account, String pwd);
Copy the code

For example, if we want to query the list of users whose ID is greater than a certain condition, we can declare the following interface

List<UserDO> findAllByIdGreaterThan(Long id);
Copy the code

This statement structure can be illustrated by the following table

Custom query

If that is not enough. Then we can pass through the import org. Springframework. Data. Jpa. Repository. The Query annotation to solve this problem. For example, if we want to query a list of all users whose names are equal to some two names, declare the following interface

@Query("SELECT O FROM UserDO O WHERE O.name = :name1 OR O.name = :name2 ")
List<UserDO> findTwoName(@Param("name1") String name1, @Param("name2") String name2);
Copy the code

Here we use the SYNTAX of PQL to define a query. Two of the parameter names are determined by the payment following: in the statement

If you are used to writing SQL statements to complete queries, you can also do this in the following way

@Query(nativeQuery = true, value = "SELECT * FROM AUTH_USER WHERE name = :name1 OR name = :name2 ")
List<UserDO> findSQL(@Param("name1") String name1, @Param("name2") String name2);
Copy the code

Add a nativeQuery = true attribute to the @Query annotation to write the Query as a native SQL statement.

Combined the primary key

From the org. Springframework. Data. Jpa. Repository. JpaRepository < T, ID > interface definition, data entity’s primary key is a single object, What if the primary key of a database table is a combination of two or more fields?

Let’s expand on the previous scenario. Suppose we have a Role object with two attributes: an ID and a name, which correspond to the AUTH_ROLE table, and a Role user relationship object, RoleUser, which corresponds to the Role and user, There are two properties roleId,userId corresponds to the AUTH_ROLE_USER table. So we need to declare a RoleDO object as follows

package com.yanggaochao.springboot.learn.springbootjpalearn.security.domain.dao; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; /** * @author * @since 2018-03-12 */ @entity @table (name ="AUTH_ROLE")
public class RoleDO {
    @Id
    private Long id;
    @Column(length = 32)
    private String name;
    @Column(length = 64)
    private String note;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) { this.note = note; }}Copy the code

To implement the Java.io.Serializable interface, create a new primary key class with the same attributes as the fields of the database entity’s primary key

package com.yanggaochao.springboot.learn.springbootjpalearn.security.domain.dao; import java.io.Serializable; Public class RoleUserId implements Serializable {private Long; public class RoleUserId implements Serializable {private Long roleId; private Long userId; }Copy the code

Similarly, we declare a RoleUserDO object as follows

package com.yanggaochao.springboot.learn.springbootjpalearn.security.domain.dao; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.IdClass; import javax.persistence.Table; import java.io.Serializable; / * * * role relationship between user Entity class * * @ author Yang Gaochao * @ since the 2018-03-12 * / @ Entity @ IdClass RoleUserId. Class @ Table (name ="AUTH_ROLE_USER")
public class RoleUserDO  {
    @Id
    private Long roleId;
    @Id
    private Long userId;

    public Long getRoleId() {
        return roleId;
    }

    public void setRoleId(Long roleId) {
        this.roleId = roleId;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) { this.userId = userId; }}Copy the code

Since the data entity class has the same properties as the data entity primary key class, we can delete the data entity primary key class and declare the data entity primary key class as our own. Of course, you will implement the Java.io.Serializable interface yourself.

Thus, if we want to query the list of all users under a role, we can declare the following interface

@Query("SELECT U FROM UserDO U ,RoleUserDO RU WHERE U.id = RU.userId AND RU.roleId = :roleId")
List<UserDO> findUsersByRole(@Param("roleId") Long roleId);
Copy the code

Of course, in this case, we will see that the AUTH_ROLE and AUTH_ROLE_USER tables are created automatically. Their table structure is shown below

Note that in the auth_roLE_USER table, the attribute name userId is converted to user_ID and the attribute name roleId is converted to role_ID.

If we were to implement the above function as SQL statements, we would change the interface declaration to the following form.

@Query("SELECT U.* FROM AUTH_USER U ,AUTH_ROLE_USER RU WHERE U.id = RU.user_id AND RU.role_id = :roleId")
List<UserDO> findUsersByRole(@Param("roleId") Long roleId);
Copy the code

Afterword.

This sample basically covers the details of using JPA. We can see that. Using JPA to complete the function of adding, deleting, modifying and querying relational databases is very convenient and fast. All the code has been uploaded to Github’s repository, Springboot-Jpa-Learn

The original was published in Brief Book