Spring Data JPA operates MySQL 8

Introduction to Spring Data JPA

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

The development environment

Spring Boot 2.0.4
Spring Data JPA 2.0.4
MySQL 8.0.12
JDK 8
The IDEA of 2018.2
Windows 10

Second, integration steps

2.1 Configuring Dependencies

Add Spring Data JPA and MySQL Connector and configure the pom.xml file as follows:

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

More JPA version: mvnrepository.com/artifact/or…

More Mysql version: mvnrepository.com/artifact/my…

2.2 Application.properties Setting the configuration file

# # data source configuration spring. The datasource. Url = JDBC: mysql: / / 172.16.10.79:3306 / mytestdb? serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.jpa.hibernate.ddl-auto=update spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect spring.jpa.show-sql=trueCopy the code

Hbm2ddl. Auto: automatically create | | update database table structure
Dialect: Set the database engine to InnoDB
Show-sql: displays SQL statements for debugging

Hbm2ddl.auto has four attributes:

Create: Each time Hibernate is loaded, the last generated table is deleted, and then the new table is generated again based on your Model class, even if there are no changes twice. This is an important cause of database table data loss. [Delete – Create – Operation]
Create-drop: The table is generated from the Model class every time Hibernate is loaded, but the table is dropped automatically when sessionFactory is closed. [Delete – Create – Operation – Delete again]
Update: When Hibernate is loaded for the first time, the table structure will be automatically established based on the Model class (provided the database is established first). When Hibernate is loaded, the table structure will be automatically updated based on the Model class. Even if the table structure is changed, the rows in the table will still exist, and the previous rows will not be deleted. Note that when deployed to the server, the table structure is not established immediately, but only after the application is first run. [no table – creation – | operation are table – update not attribute column – operations]
Validate: Each time Hibernate is loaded, verify that the database table structure is created, and only the tables in the database are compared. No new tables are created, but new values are inserted. [Start verification table structure, verification failed, project startup failed]

2.3 Adding an Entity Class

@Entity
public 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(a){}
    public User(String name, int age, String pwd) {
        this.name = name;
        this.age = age;
        this.pwd = pwd;
    }
	/ /... Ignore the set and get methods
}
Copy the code

@GeneratedValue Automatically generates an ID
@column Sets the Column properties (name=” database Column name “)
@TRANSIENT does not map to the database

2.4 Creating a Repository interface to build business methods

public interface UserRepository extends JpaRepository<User.Long> {
    public User findByName(String name);
}
Copy the code

Inheriting JpaRepository inherits:

Repository.save(user); // Insert or save
Repository.saveFlush(user); // Save and refresh
Repository.exists(1) // Check whether the primary key exists
Repository.findOne(1); // Primary key query single
Repository.delete(1); // Delete the primary key
Repository.findByUsername(“stone”); // Query a single item
Repository.findAll(pageable); // A sorted and paginated list of queries
Repository.saveState(1, 0); // Update a single field

These methods can operate on a table without writing a single line of code, but you can extend your own methods by adding methods to UserRepository.

2.5 Adding and Querying databases

@Controller
@RequestMapping("/")
public class UserController {

    @Autowired
    private UserRepository userRepository;

    @RequestMapping("/")
    public ModelAndView index(a) {
        userRepository.save(new User("Wang".18."123456"));
        ModelAndView modelAndView = new ModelAndView("/index");
        modelAndView.addObject("dataSize", userRepository.findAll().size());
        returnmodelAndView; }}Copy the code

Now that the Spring Data JPA integration is complete, start debugging and see how it works.

Three, advanced use

This section will cover the following points of advanced use:

Transaction implementation
Automatically generate SQL based on name
Custom Sql statement query

3.1 Transaction Implementation

3.1.1 Spring transaction implementation steps

To implement a transaction, there are only two steps:

Set InnoDB as the database engine in application.properties:

spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

Step 2. Identify the transaction @Transactional ona method or class

Sample code:

@Transactional
public void saveGroup(){
    userRepository.save(user);
    userRepository.save(user2);
}
Copy the code

If an error occurs, the transaction is rolled back.

3.1.2 Causes of transaction invalidity

3.1.2.1 Confirming the database Engine

Configure the database engine as InnoDB in application.properties:

spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

3.1.2.2 The engine for viewing tables must be InnoDB

By command:

show table status from mytestdb;

Alter table engine:

alter table table_name engine=innodb;

3.1.2.3 Note the introduction of the @Transactional namespace

@ Transactional annotation from org. Springframework. Transaction. The annotation package, rather than javax.mail. Transaction.

3.2 Automatically generate SQL by name

JPA supports methods to automatically generate Sql queries based on simple keywords, such as a combination of name and age, with the following code:

public User findByNameAndAge(String name,int age);

Use keyword “And”, or query time range:

public User findByStartDateBetween(Long startDate);

Use the keyword Between.

More internally supported keywords are listed in 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 like ? 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 %)
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ? 1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ? 1
In	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)

IO /spring-data…

3.3 Customizing Sql Statement Queries

Spring Boot JPA also has good support for writing your own SQL, simply by adding @Query(SQL).

Sample code:

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

Note that @pipelineannotations must be added to perform modifications and deletions so that ORM knows to perform writes, and @Transactional (Transactional) annotations must be added to update/ Delete Query to perform properly.

Common mistakes

When using Spring Data JPA, you may encounter some of the following errors.

1.No default constructor for entity

The Entity class does not have a default constructor for empty arguments.

2. Java.sqlexception: Access denied for user ”@’172.17.0.1′ (using password: NO)

MySQL > select * from ‘MySQL8’; MySQL > select * from ‘MySQL8’;

spring.datasource.username=root
spring.datasource.password=123456
The following is to configure the old database driver configuration
#spring.datasource.data-username=root
#spring.datasource.data-password=123456
Copy the code

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

The spring.datasource. Driver-class-name configuration for MySQL 8 needs to be changed to com.mysql.cj.jdbc.driver instead of com.mysql.jdbc.driver. The correct configuration is as follows:

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