Spring Data JPA--自定义SQL--方法/教程/实例

最新推荐文章于 2025-07-01 17:22:03 发布

IT利刃出鞘

最新推荐文章于 2025-07-01 17:22:03 发布

阅读量2.4k

点赞数 4

CC 4.0 BY-SA版权

文章标签： hibernate 数据库 sql

本文链接：https://blog.csdn.net/feiying0canglang/article/details/120637879

本文详细介绍了如何在Spring Data JPA中使用@Query注解进行自定义SQL查询，包括JPQL和原生SQL的应用，以及如何处理部分字段查询、子类映射和事务管理。涵盖了删除、修改、查询操作的实战示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原文网址：Spring Data JPA--自定义SQL--方法/教程/实例_IT利刃出鞘的博客-CSDN博客

简介

说明

JPA在自定义SQL时，增删改查全都是使用@Query注解，只是里边语句是INSERT、DELETE、UPDATE、SELECT。

删除和修改需要加上@Modifying。也可以根据需要添加 @Transactional 对事物的支持，超时的设置等。

JPQL

JPQL（Java Persistence Query Language）是一种和SQL非常类似的中间性和对象化查询语言，它最终会被编译成针对不同底层数据库的SQL语言，从而屏蔽不同数据库的差异。

JPQL语言通过Query接口封装执行，Query 接口封装了执行数据库查询的相关方法。调用 EntityManager 的 Query、NamedQuery 及 NativeQuery 方法可以获得查询对象，进而可调用Query接口的相关方法来执行查询操作。

JPQL是面向对象进行查询的语言，可以通过自定义的JPQL完成删改查操作。JPQL不支持使用INSERT。对于UPDATE或DELETE操作，必须使用注解 @Modifying 进行修饰。

公共代码

配置文件及依赖

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:
    # 打印sql
    show-sql: true
    # 关闭属性的延时加载
    open-in-view: false
#    hibernate:
#      # 是否开启自动更新数据库表结构。生产中不要开启。
#      #   有以下几个值供选择。常用：update、none
#      #   create：每次加载hibernate时都删除上次生成的表，再根据你的model类生成新表。即使两次没有改变也这样执行，可能导致表数据丢失。
#      #   create-drop：每次加载hibernate时，先删除已存在的表结构再重新生成，sessionFactory一关闭，表就自动删除。
#      #   update：第一次加载hibernate时根据model类自动建立表结构，以后加载hibernate时根据model类自动更新表结构。
#      #           即使表结构变了但表中的行仍然存在不会删除以前的行。
#      #           部署到服务器后，表结构是不会被马上建立起来的，是要等应用第一次运行起来后才会。
#      #   validate：每次加载hibernate时，验证创建数据库表结构，只会和数据库中的表进行比较，不会创建新表，但是会插入新值。
#      #   none：关闭自动更新
#      ddl-auto: none
#
#      # hibernate5及之后的命名策略配置。
#      naming:
#        # 负责模型对象层次的处理，将对象模型处理为逻辑名称
#        #   有以下5个值供选择：
#        #     ImplicitNamingStrategyJpaCompliantImpl（默认值）后四者均继承自它。
#        #     ImplicitNamingStrategyComponentPathImpl
#        #     ImplicitNamingStrategyLegacyHbmImpl
#        #     ImplicitNamingStrategyLegacyJpaImpl
#        #     SpringImplicitNamingStrategy
#        implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
#
#        # 映射成真实的数据名称的处理，将逻辑名称处理为物理名称。
#        #   有以下2个值供选择：
#        #     PhysicalNamingStrategyStandardImpl：直接映射，若有@Column则以其为准。等同于之前的DefaultNamingStrategy
#        #     SpringPhysicalNamingStrategy（默认值）：字段为小写，当有大写字母的时候会转换为分隔符号“_”。等同于之前的ImprovedNamingStrategy
#        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
#
#        # hibernate5之前的命名策略配置。
#        #   有以下2个值供选择：
#        #     DefaultNamingStrategy（默认值）：直接映射，若有@Column则以其为准。
#        #     ImprovedNamingStrategy：字段为小写，当有大写字母的时候会转换为分隔符号“_”。
#        # 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>

表结构及数据

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);

其他

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;
}

实例：原生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 = "自定义SQL")
@RestController
@RequestMapping("/customSql")
public class CustomSQLController {
    @Autowired
    private UserRepository userRepository;

    @ApiOperation("原生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);
    }

}

执行结果

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

实例：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 = "自定义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);
    }
}

执行结果

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

实例：查询部分字段

其他网址

SpringBoot使用JPA如何查询部分字段_静幽水-CSDN博客_jpa 查询部分字段
 spring data jpa查询部分字段、多余附加字段 - 追极 - 博客园

说明

单个字段：可以使用原生SQL或者JHQL

多个字段：只能使用JHQL

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> {
    // ------------ 原生SQL查询部分字段 -------------------
    @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("SELECT u.userName FROM User u WHERE u.id = :id")
    List<String> queryUserNameById(@Param("id") Long id);

    // 查多个字段
    @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 = "自定义SQL")
@RestController
@RequestMapping("/customSql")
public class CustomSQLController {
    @Autowired
    private UserRepository userRepository;

    @ApiOperation("查部分字段")
    @PostMapping("part")
    public void part() {
        // 原生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;
}

执行结果

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)]

实例：读出到实体类的子类

说明

只能通过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> {
    // ----------- 原生SQL读出到实体类的子类 ----------------------

    // 查询时报错：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读出到实体类的子类 ----------------------

    @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（添加全字段构造函数）

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（继承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 = "自定义SQL")
@RestController
@RequestMapping("/customSql")
public class CustomSQLController {
    @Autowired
    private UserRepository userRepository;

    @ApiOperation("实体类的子类")
    @PostMapping("subClass")
    public void subClass() throws JsonProcessingException {
        // 原生SQL
        // 查询时报错：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));
    }
}

执行结果

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}]

实例：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 = "自定义SQL")
@RestController
@RequestMapping("/customSql")
public class CustomSQLController {
    @PersistenceContext
    private EntityManager entityManager;

    @ApiOperation("查部分字段")
    @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;
}