Original website: JPA--SpringBoot -- custom SQL -- Integration / method / how / how / instance / example / actual combat / tutorial -- spring data JPA_ CSDN blog
brief introduction
explain
When JPA customizes SQL, all additions, deletions and queries are annotated with @ Query, except that the statements inside are INSERT, DELETE, UPDATE and SELECT.
@ Modifying is required for deletion and modification. You can also add @ Transactional support for things and timeout settings as needed.
JPQL
JPQL (Java Persistence Query Language) is an intermediate and object-oriented query language very similar to SQL. It will eventually be compiled into SQL language for different underlying databases, so as to shield the differences between different databases.
JPQL language encapsulates the execution through the Query interface, which encapsulates the related methods of executing database queries. Call the Query, NamedQuery and NativeQuery methods of EntityManager to obtain the Query object, and then call the relevant methods of the Query interface to perform the Query operation.
JPQL is an object-oriented query language. You can DELETE, modify and query through custom JPQL. JPQL does not support INSERT. For UPDATE or DELETE operations, the annotation @ Modifying must be used for modification.
Public code
Configuration files and dependencies
application.yml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/jpa?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
username: root
password: 222333
jpa:
# Print sql
show-sql: true
# Turn off deferred loading of properties
open-in-view: false
# hibernate:
# # Whether to turn on automatic updating of database table structure. Do not open during production.
# # There are several values to choose from. Common: update, none
# # create: delete the last generated table every time hibernate is loaded, and then generate a new table according to your model class. Even if there is no change twice, it may cause table data loss.
# # Create drop: each time hibernate is loaded, the existing table structure is deleted and then regenerated. As soon as sessionFactory is closed, the table is automatically deleted.
# # update: when hibernate is loaded for the first time, the table structure is automatically established according to the model class. When hibernate is loaded later, the table structure is automatically updated according to the model class.
# # Even if the table structure changes, the rows in the table still exist and the previous rows will not be deleted.
# # After deployment to the server, the table structure will not be established immediately. It will not be established until the application runs for the first time.
# # validate: every time hibernate is loaded, the database table structure will be verified and created. It will only be compared with the tables in the database. No new table will be created, but new values will be inserted.
# # none: turn off automatic updates
# ddl-auto: none
#
# # Naming policy configuration for hibernate 5 and later.
# naming:
# # It is responsible for processing the model object hierarchy and processing the object model into logical names
# # There are five values to choose from:
# # ImplicitNamingStrategyJpaCompliantImpl (the default) inherits the last four.
# # ImplicitNamingStrategyComponentPathImpl
# # ImplicitNamingStrategyLegacyHbmImpl
# # ImplicitNamingStrategyLegacyJpaImpl
# # SpringImplicitNamingStrategy
# implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
#
# # The processing of mapping to real data names, and processing logical names into physical names.
# # There are 2 values to choose from:
# # PhysicalNamingStrategyStandardImpl: direct mapping. If there is @ Column, it shall prevail. Equivalent to the previous DefaultNamingStrategy
# # Spring physical naming strategy (default): the field is lowercase. When there are uppercase letters, it will be converted to the separator "". Equivalent to the previous improved naming strategy
# physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
#
# # Naming policy configuration before hibernate 5.
# # There are 2 values to choose from:
# # DefaultNamingStrategy (default): direct mapping. If there is @ Column, it shall prevail.
# # Improved naming strategy: the field is lowercase. When there are uppercase letters, it will be converted to the separator "".
# # naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo_jpa</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo_jpa</name>
<description>demo_jpa</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<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.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.hibernate.javax.persistence</groupId>
<artifactId>hibernate-jpa-2.1-api</artifactId>
<version>1.0.2.Final</version>
</dependency>
<dependency>
<groupId>com.github.xiaoymin</groupId>
<artifactId>knife4j-spring-boot-starter</artifactId>
<version>3.0.2</version>
</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>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
Table structure and data
t_user.sql
DROP TABLE IF EXISTS t_user;
CREATE TABLE `t_user`
(
`id` bigint(0) NOT NULL AUTO_INCREMENT,
`user_name` varchar(32) NULL DEFAULT NULL,
`age` int(10) NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = InnoDB;
INSERT INTO `t_user`(user_name, age) values ('Tony1', 21);
INSERT INTO `t_user`(user_name, age) values ('Tony2', 22);
INSERT INTO `t_user`(user_name, age) values ('Tony3', 23);
INSERT INTO `t_user`(user_name, age) values ('Tony3', 24);
other
entity
package com.example.demo.user.entity;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
@Data
@NoArgsConstructor
@Entity
@Table(name = "t_user")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String userName;
private Integer age;
}Instance: native SQL
Repository
package com.example.demo.user.repository;
import com.example.demo.user.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM t_user WHERE user_name = :userName", nativeQuery = true)
List<User> fetchByUserName(@Param("userName") String userName);
@Query(value = "SELECT * FROM t_user WHERE age = ?1", nativeQuery = true)
List<User> fetchByAge(Integer age);
@Query(value = "SELECT * FROM t_user WHERE user_name LIKE %:userName%", nativeQuery = true)
List<User> fetchByUserNameLike(@Param("userName") String userName);
@Transactional
@Modifying
@Query(value = "UPDATE t_user SET user_name = :userName WHERE id = :id", nativeQuery = true)
int updateById(@Param("id") Long id, @Param("userName")String userName);
}
Controller
package com.example.demo.user.controller;
import com.example.demo.user.entity.User;
import com.example.demo.user.repository.UserRepository;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@Api(tags = "custom SQL")
@RestController
@RequestMapping("/customSql")
public class CustomSQLController {
@Autowired
private UserRepository userRepository;
@ApiOperation("Primordial SQL")
@PostMapping("origin")
public void origin() {
List<User> fetchByUserName = userRepository.fetchByUserName("Tony3");
List<User> fetchByAge = userRepository.fetchByAge(23);
List<User> fetchByUserNameLike = userRepository.fetchByUserNameLike("Tony");
int updateById = userRepository.updateById(3L, "Tony4");
userRepository.updateById(3L, "Tony3");
System.out.println("----------------------------------------------------------");
System.out.println("fetchByUserName : " + fetchByUserName);
System.out.println("fetchByAge : " + fetchByAge);
System.out.println("fetchByUserNameLike : " + fetchByUserNameLike);
System.out.println("updateById : " + updateById);
}
}
results of enforcement
Hibernate: SELECT * FROM t_user WHERE user_name = ? Hibernate: SELECT * FROM t_user WHERE age = ? Hibernate: SELECT * FROM t_user WHERE user_name LIKE ? Hibernate: UPDATE t_user SET user_name = ? WHERE id = ? Hibernate: UPDATE t_user SET user_name = ? WHERE id = ? ---------------------------------------------------------- fetchByUserName : [User(id=3, userName=Tony3, age=23), User(id=4, userName=Tony3, age=24)] fetchByAge : [User(id=3, userName=Tony3, age=23)] fetchByUserNameLike : [User(id=1, userName=Tony1, age=21), User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony3, age=23), User(id=4, userName=Tony3, age=24)] updateById : 1
Example: JPQL
Repository
package com.example.demo.user.repository;
import com.example.demo.user.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Query("SELECT u FROM User u WHERE u.userName = :userName")
List<User> getByUserName(@Param("userName") String userName);
@Query("SELECT u FROM User u WHERE u.age = ?1")
List<User> getByAge(Integer age);
@Query("SELECT u FROM User u WHERE u.userName LIKE %:userName%")
List<User> getByUserNameLike(@Param("userName") String userName);
@Transactional
@Modifying
@Query("UPDATE User u SET u.userName = :userName WHERE u.id = :id")
int updateUserNameById(@Param("id") Long id, @Param("userName")String userName);
}
Controller
package com.example.demo.user.controller;
import com.example.demo.user.entity.User;
import com.example.demo.user.repository.UserRepository;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@Api(tags = "custom SQL")
@RestController
@RequestMapping("/customSql")
public class CustomSQLController {
@Autowired
private UserRepository userRepository;
@ApiOperation("JHQL")
@PostMapping("jhql")
public void jhql() {
List<User> getByUserName = userRepository.getByUserName("Tony3");
List<User> getByAge = userRepository.getByAge(23);
List<User> getByUserNameLike = userRepository.getByUserNameLike("Tony");
int updateUserNameById = userRepository.updateUserNameById(3L, "Tony4");
userRepository.updateUserNameById(3L, "Tony3");
System.out.println("----------------------------------------------------------");
System.out.println("getByUserName : " + getByUserName);
System.out.println("getByAge : " + getByAge);
System.out.println("getByUserNameLike : " + getByUserNameLike);
System.out.println("updateUserNameById : " + updateUserNameById);
}
}
results of enforcement
Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.user_name as user_nam3_0_ from t_user user0_ where user0_.user_name=? Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.user_name as user_nam3_0_ from t_user user0_ where user0_.age=? Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.user_name as user_nam3_0_ from t_user user0_ where user0_.user_name like ? Hibernate: update t_user set user_name=? where id=? Hibernate: update t_user set user_name=? where id=? ---------------------------------------------------------- getByUserName : [User(id=3, userName=Tony3, age=23), User(id=4, userName=Tony3, age=24)] getByAge : [User(id=3, userName=Tony3, age=23)] getByUserNameLike : [User(id=1, userName=Tony1, age=21), User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony3, age=23), User(id=4, userName=Tony3, age=24)] updateUserNameById : 1
Example: query some fields
Other web sites
How does SpringBoot query some fields using jpa_ Jingyoushui CSDN blog_ jpa query partial fields
spring data jpa query some fields, redundant additional fields - chasing pole - blog Park
explain
Single field: you can use native SQL or JHQL
Multiple fields: only JHQL can be used
Repository
package com.example.demo.user.repository;
import com.example.demo.user.entity.User;
import com.example.demo.user.entity.UserDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// ------------Native SQL query partial fields-------------------
@Query(value = "SELECT t_user.user_name FROM t_user WHERE id = :id", nativeQuery = true)
List<String> fetchUserNameById(@Param("id") Long id);
// No converter found capable of converting from type... To type
// @Query(value = "SELECT * FROM t_user WHERE id = :id", nativeQuery = true)
// List<UserDTO> fetchUserNameAndAgeById(@Param("id") Long id);
// ------------JHQL query partial fields-------------------
// Check only one field
@Query("SELECT u.userName FROM User u WHERE u.id = :id")
List<String> queryUserNameById(@Param("id") Long id);
// Check multiple fields
@Query("SELECT new com.example.demo.user.entity.UserDTO(u.userName, u.age)" +
" FROM User u " +
" WHERE u.id = :id")
List<UserDTO> queryUserNameAndAgeById(@Param("id") Long id);
}
Controller
package com.example.demo.user.controller;
import com.example.demo.user.entity.User;
import com.example.demo.user.entity.UserDTO;
import com.example.demo.user.repository.UserRepository;
import com.fasterxml.jackson.databind.ObjectMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@Api(tags = "custom SQL")
@RestController
@RequestMapping("/customSql")
public class CustomSQLController {
@Autowired
private UserRepository userRepository;
@ApiOperation("Check some fields")
@PostMapping("part")
public void part() {
// Native SQL
List<String> fetchUserNameById = userRepository.fetchUserNameById(3L);
// No converter found capable of converting from type... To type
// List<UserDTO> fetchUserNameAndAgeById = userRepository.fetchUserNameAndAgeById(3L);
// JHQL
List<String> queryUserNameById = userRepository.queryUserNameById(3L);
List<UserDTO> queryUserNameAndAgeById = userRepository.queryUserNameAndAgeById(3L);
System.out.println("----------------------------------------------------------");
System.out.println("fetchUserNameById : " + fetchUserNameById);
System.out.println("queryUserNameById : " + queryUserNameById);
System.out.println("queryUserNameAndAgeById : " + queryUserNameAndAgeById);
}
}
UserDTO
package com.example.demo.user.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserDTO {
private String userName;
private Integer age;
}
results of enforcement
Hibernate: SELECT t_user.user_name FROM t_user WHERE id = ? Hibernate: select user0_.user_name as col_0_0_ from t_user user0_ where user0_.id=? Hibernate: select user0_.user_name as col_0_0_, user0_.age as col_1_0_ from t_user user0_ where user0_.id=? ---------------------------------------------------------- fetchUserNameById : [Tony3] queryUserNameById : [Tony3] queryUserNameAndAgeById : [UserDTO(userName=Tony3, age=23)]
Instance: read out the subclass of the entity class
explain
Only through JHQL.
Repository
package com.example.demo.user.repository;
import com.example.demo.user.entity.User;
import com.example.demo.user.entity.UserVO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
// -----------Native SQL reads out subclasses of entity classes----------------------
// Query error: Unknown entity: com.example.demo.user.entity.UserVO
// @Query(value = "SELECT * FROM t_user WHERE id = :id", nativeQuery = true)
// List<UserVO> fetchById(@Param("id") Long id);
// -----------JHQL reads out subclasses of entity classes----------------------
@Query("SELECT new com.example.demo.user.entity.UserVO(u.id, u.userName, u.age)" +
" FROM User u " +
" WHERE u.id = :id")
List<UserVO> queryById(@Param("id") Long id);
}
User (add full field constructor)
package com.example.demo.user.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "t_user")
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String userName;
private Integer age;
}UserVO (inherit User)
package com.example.demo.user.entity;
import lombok.Data;
import lombok.EqualsAndHashCode;
@EqualsAndHashCode(callSuper = true)
@Data
public class UserVO extends User{
private String remark;
public UserVO(Long id, String userName, Integer age) {
super(id, userName, age);
}
}
Controller
package com.example.demo.user.controller;
import com.example.demo.user.entity.User;
import com.example.demo.user.entity.UserVO;
import com.example.demo.user.repository.UserRepository;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@Api(tags = "custom SQL")
@RestController
@RequestMapping("/customSql")
public class CustomSQLController {
@Autowired
private UserRepository userRepository;
@ApiOperation("Subclass of entity class")
@PostMapping("subClass")
public void subClass() throws JsonProcessingException {
// Native SQL
// Query error: Unknown entity: com.example.demo.user.entity.UserVO
// List<UserVO> fetchById = userRepository.fetchById(3L);
// JHQL
List<UserVO> queryById = userRepository.queryById(3L);
System.out.println("----------------------------------------------------------");
System.out.println("queryById : " + new ObjectMapper().writeValueAsString(queryById));
}
}
results of enforcement
Hibernate: select user0_.id as col_0_0_, user0_.user_name as col_1_0_, user0_.age as col_2_0_ from t_user user0_ where user0_.id=?
----------------------------------------------------------
queryById : [{"id":3,"userName":"Tony3","age":23,"remark":null}]Instance: EntityManager
Controller
package com.example.demo.user.controller;
import com.example.demo.user.entity.User;
import com.example.demo.user.entity.UserDTO;
import com.example.demo.user.repository.UserRepository;
import com.fasterxml.jackson.databind.ObjectMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.persistence.Query;
import javax.persistence.EntityManager;
import java.util.List;
@Api(tags = "custom SQL")
@RestController
@RequestMapping("/customSql")
public class CustomSQLController {
@PersistenceContext
private EntityManager entityManager;
@ApiOperation("Check some fields")
@PostMapping("part")
public void part() {
String sql = "SELECT new com.example.demo.user.entity.UserVO(u.id, u.userName, u.age) FROM User u WHERE u.id = :id";
Query query = entityManager.createQuery(sql);
query.setParameter("id", 2L);
List<UserVO> resultList = query.getResultList();
entityManager.close();
}
}
UserDTO
package com.example.demo.user.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserDTO {
private String userName;
private Integer age;
}
Other web sites
Spring Data JPA uses JPQL and native SQL for queries_ junbiao's blog - CSDN blog
@Usage of Query annotation (Spring Data JPA) - lost road - blog Garden
Spring Boot Jpa custom SQL_springTen blog - CSDN blog