Spring Boot Best Practices (v) Spring Data JPA Operation MySQL 8

First, Spring Data JPA Introduction

The JPA (Java Persistence API) Java Persistence API is the standard specification for Java persistence, and Hibernate is a technical implementation of the persistence specification, and Spring Data JPA is a framework encapsulated on Hibernate.

Development environment

• Spring Boot 2.0.4
• Spring Data JPA 2.0.4
• MySQL 8.0.12
• JDK 8
• Idea 2018.2
• Windows 10

Ii. Integration Step 2.1 configuration dependencies

Add Spring Data JPA and MySQL Connector, configure the Pom.xml file with the following code:

<dependency>    <groupId>org.springframework.boot</groupId>    <artifactId>spring-boot-starter-data-jpa</artifactId>    <version>2.0.4.RELEASE</version></dependency><dependency>    <groupId>mysql</groupId>    <artifactId>mysql-connector-java</artifactId>    <version>8.0.12</version></dependency>

More JPA Versions: HTTP://MVNREPOSITORY.COM/ARTIFACT/ORG.SPRINGFRAMEWORK.BOOT/SPRING-BOOT-STARTER-DATA-JPA

More MySQL versions: Http://mvnrepository.com/artifact/mysql/mysql-connector-java

2.2 Application.properties setting up configuration files

## 数据源配置spring.datasource.url=jdbc:mysql://172.16.10.79:3306/mytestdb?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=truespring.datasource.username=rootspring.datasource.password=123456spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.jpa.hibernate.ddl-auto=updatespring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialectspring.jpa.show-sql=true

• Hbm2ddl.auto: Auto Create | update | Validate database table structure
• Dialect: Setting the database engine to InnoDB
• SHOW-SQL: Print SQL statements for easy debugging

Hbm2ddl.auto has four properties:

• Create: Each time you load hibernate, the last generated table will be deleted, and then the new table will be generated based on your model class, even if there are no changes two times to do so, which is an important reason for the loss of database table data. [Delete-Create-action]

• Create-drop: The table is generated according to the model class each time hibernate is loaded, but the table is automatically deleted when Sessionfactory is closed. [Delete-Create-action-then delete]

• Update: The most commonly used property, the first time you load hibernate based on the model class will automatically establish the structure of the table (provided that the database is first established), and later when loading hibernate according to the model class automatically update the table structure, even if the table structure has changed, but the table rows still exist, The previous row is not deleted. It is important to note that when deployed to a server, the table structure is not immediately established, it is to wait for the application to run for the first time. [No table-Create-action | There is a table-update does not have an attribute column-action]

• Validate: Each time hibernate is loaded, validation creates a database table structure that is only compared to tables in the database and does not create new tables, but inserts new values. [Start validation table structure, validation not successful, project start failed]

2.3 Adding entity classes (entities)

@Entitypublic class User implements Serializable {    @Id    @GeneratedValue    private Long id;    @Column(name = "name", nullable = false)    private String name;    @Column(nullable = false)    private int age;    @Column(nullable = false)    private String pwd;    public User(){}    public User(String name, int age, String pwd) {        this.name = name;        this.age = age;        this.pwd = pwd;    }    //...忽略set、get方法}

• @GeneratedValue Auto-Generate ID
• @Column Set column properties (name= "database column name")
• @Transient does not map to a database

2.4 Creating a Repository interface building a business method

public interface UserRepository extends JpaRepository<User,Long> {    public User findByName(String name);}

After inheriting the jparepository, it inherits:

• Repository.save (user); Insert or Save
• Repository.saveflush (user); Save and Refresh
• Repository.exists (1)//primary key query is present
• Repository.findone (1); Primary KEY Query Single
• Repository.delete (1); Primary KEY Delete
• Repository.findbyusername ("stone"); Query a single
• Repository.findall (pageable); List of queries with sorting and paging
• Repository.savestate (1, 0); Update a single field

These methods, you can do the operation of a table without writing a line of code, of course, you can also extend some of their own methods, just need to add a method in the userrepository.

2.5 Adding, querying the database

@Controller@RequestMapping("/")public class UserController {    @Autowired    private UserRepository userRepository;    @RequestMapping("/")    public ModelAndView index() {        userRepository.save(new User("老王",18,"123456"));        ModelAndView modelAndView = new ModelAndView("/index");        modelAndView.addObject("dataSize", userRepository.findAll().size());        return modelAndView;    }}

So far, the integrated Spring Data JPA has all been done, start debugging, and see how it works.

Third, advanced use

The knowledge points that will be covered by advanced use in this section are as follows:

• Transaction implementation
• Automatically generate SQL by name
• Custom SQL statement Query

3.1 Transaction Implementation 3.1.1 Spring transaction Implementation steps

To implement a transaction, you need only two steps:

Step one, in the Application.properties configuration database engine for InnoDB:

Spring.jpa.database-platform=org.hibernate.dialect.mysql5innodbdialect

Step two, identify the transaction on the method or class @transactional

Example code:

@Transactionalpublic void saveGroup(){    userRepository.save(user);    userRepository.save(user2);}

If an error occurs, the transaction is rolled back.

3.1.2 The reason that the transaction does not take effect 3.1.2.1 confirm the database engine

In the Application.properties configuration database engine for InnoDB:

Spring.jpa.database-platform=org.hibernate.dialect.mysql5innodbdialect

3.1.2.2 the engine of the view table must be InnoDB

by command:

Show table status from Mytestdb;

To modify the table's engine:

ALTER TABLE table_name ENGINE=INNODB;

3.1.2.3 Note Introduction of the @transactional namespace

@Transactional annotations come from org.springframework.transaction.annotation packages, not javax.transaction.

3.2 Automatically generate SQL by name

JPA supports the automatic generation of SQL queries based on simple keywords, such as queries based on the combination of name and age, with the following code:

Public User Findbynameandage (String name,int age);

Use the keyword "and", or query the time interval:

Public User Findbystartdatebetween (Long startdate);

Use the keyword "between".

More internal support keywords, such as the following table:

Keyword	Sample	JPQL Snippet
and	Findbylastnameandfirstname	... where x.lastname =? 1 and x.firstname =? 2
Or	Findbylastnameorfirstname	... where x.lastname =? 1 or x.firstname =? 2
Is,equals	Findbyfirstname,findbyfirstnameis	... where x.firstname =? 1
Between	Findbystartdatebetween	... where x.startdate between? 1 and? 2
LessThan	Findbyagelessthan	.. where X.age <? 1
Lessthanequal	Findbyagelessthanequal	... where x.age <=? 1
GreaterThan	Findbyagegreaterthan	.. where X.age >? 1
Greaterthanequal	Findbyagegreaterthanequal	... where x.age >=? 1
After	Findbystartdateafter	.. where X.startdate >? 1
Before	Findbystartdatebefore	.. where X.startdate <? 1
IsNull	Findbyageisnull	... where x.age is null
Isnotnull,notnull	Findbyage (IS) notnull	... where x.age not null
Like	Findbyfirstnamelike	... where x.firstname like? 1
Notlike	Findbyfirstnamenotlike	... where x.firstname not? 1
Startingwith	Findbyfirstnamestartingwith	... where x.firstname like? 1 (parameter bound with appended%)
Endingwith	Findbyfirstnameendingwith	... where x.firstname like? 1 (parameter bound with prepended%)
Containing	Findbyfirstnamecontaining	... where x.firstname like? 1 (parameter bound wrapped in%)
By	Findbyageorderbylastnamedesc	... where x.age =? 1 ORDER BY X.lastname DESC
Not	Findbylastnamenot	.. where X.lastname <>? 1
Inch	Findbyagein (Collection Ages)	... where x.age in? 1
Notin	Findbyagenotin (Collection Ages)	... where x.age not in? 1
True	Findbyactivetrue ()	... where x.active = True
False	Findbyactivefalse ()	... where x.active = False
IgnoreCase	Findbyfirstnameignorecase	... where UPPER (x.firstame) = UPPER (? 1)

Official document: Docs.spring.io/spring-data/jpa/docs/2.0.9.release/reference/html/#jpa. Repositories

3.3 Custom SQL statement queries

For the user to write Sql,spring Boot JPA is also very good support, only need to add @query (SQL).

Example code:

@Transactional@Modifying@Query("update User set name=?1 where id=?2")public int modifyName(String name,Long id);

Note: @modifying annotations must be added when performing the modification and deletion, and the ORM knows that to perform the write operation, update/delete query must also need to add @transactional (transaction) for normal operation.

Iv. Common Mistakes

In the use of Spring Data JPA, you may encounter some of the following errors.

1.No default constructor for entity

Entity class entities do not have a default constructor for empty parameters, and can be resolved by adding them.

2.java.sql.sqlexception:access denied for user ' @ ' 172.17.0.1 ' (using Password:no)

Startup Project error, username and password configuration key error, MySQL8 username and password configuration and before the same, MySQL 8 correct user name password configuration is as follows:

spring.datasource.username=rootspring.datasource.password=123456# 以下为配置老数据库驱动配置#spring.datasource.data-username=root#spring.datasource.data-password=123456

3.Caused by:java.lang.IllegalStateException:Cannot Load Driver class:com.mysql.jdbc.Driver

The Spring.datasource.driver-class-name configuration of MySQL 8 needs to be changed to "Com.mysql.cj.jdbc.Driver" instead of "Com.mysql.jdbc.Driver", which is correctly configured as follows:

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver