而不是更新查询，在Spring jpa中调用插入查询

Question

我试图在spring jpa中更新多个表。正在更新使用者表，但提供者表无法更新。相反，它尝试插入导致违反主键，这是预期的，因为它尝试插入而不是更新。虽然我对这两个表的代码流是相同的，但只有生产者有问题。这是我的消费者实体

import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;

@Data
@Entity
@Table(name = "consumer_table")
@IdClass(ConsumerId.class)
public class Consumer implements Serializable {
    @Id
    @Column(name = "consumer_cmdb")
    private String consumerCMDB;
    @Id
    @Column(name = "api_name")
    private String apiName;

    @Column(name = "consumer_app_vp")
    private String consumerAppVp;
    @Column(name = "consumer_app_director")
    private String consumerAppDirector;
    @Column(name = "consumer_app_manager")
    private String consumerAppManager;
    @Column(name = "consumer_tps")
    private String consumerTps;
    @Column(name = "consumer_hosted_on")
    private String consumerHost;
    @Column(name = "consumer_system_type")
    private String consumerSystemType;
    @Column(name = "carbon_namespace_ownership")
    private String consumerCarbonNamespaceOwnership;
    @Column(name = "hosted_vm_ownership")
    private String consumerHostedVmOwnership;
    @Column(name = "consumer_status")
    private String consumerStatus;
    @Column(name = "comments")
    private String ConsumerComments;
    @Column(name = "consumer_auth_type")
    private String ConsumerAuthType;

}

这是我的消费者存储库

import java.util.List;

@Repository
public interface ConsumerRepository extends JpaRepository<Consumer, ConsumerId> {

    List<Consumer> findByConsumerCMDB(String CMDB);

    @Query(nativeQuery = true,value = "select count(*) from consumer_table where  api_name IS NOT NULL AND consumer_cmdb=:cmdbId")
    Integer getTotalApisOwned(@Param("cmdbId") String cmdbId);

    List<Consumer> findByApiName(String apiName);

    Consumer findByApiNameAndConsumerCMDB(String apiName,String consumerCMDB);



}

这是我的提供商实体

import lombok.Data;

import javax.persistence.*;
import java.io.Serializable;

@Data
@Entity
@Table(name = "provider_table")
@IdClass(Provider.class)
public class Provider implements Serializable {
    @Id
    @Column(name = "api_name")
    private String apiName;
    @Id
    @Column(name = "provider_cmdb")
    private String providerCMDB;

    @Column(name = "provider_app_vp")
    private String providerAppVp;
    @Column(name = "provider_app_director")
    private String providerAppDirector;
    @Column(name = "upstream_service_hosted_on")
    private String upstreamServiceHost;
    @Column(name = "upstream_service_url")
    private String upstreamServiceUrl;
    @Column(name = "upstream_vm_ownership")
    private String upstreamVmOwnership;
    @Column(name = "upstream_service_type")
    private String upstreamServiceType;
    @Column(name = "carbon_namespace_ownership")
    private String providerCarbonNamespaceOwnership;
    @Column(name = "hosted_vm_ownership")
    private String providerHostedVmOwnership;
    @Column(name = "provider_status")
    private String providerStatus;
    @Column(name = "comments")
    private String providerComments;
}

这是我的提供商存储库

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ProviderRepository extends JpaRepository<Provider, ProviderId> {

    public List<Provider> findByApiName(String apiName);

    public Provider findByUpstreamServiceUrl(String upstreamUrl);

    public Provider findByApiNameAndProviderCMDB(String apiName,String providerCMDB);

  /*  @Query()
    public void updateProviderByCMDBAndApiName(Provider provider, String providerCMDB, String apiName);*/

}

这是日志

2022-07-13 21:13:25.442 DEBUG [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] org.hibernate.SQL                        : select consumer0_.api_name as api_name1_1_0_, consumer0_.consumer_cmdb as consumer2_1_0_, consumer0_.consumer_auth_type as consumer3_1_0_, consumer0_.comments as comments4_1_0_, consumer0_.consumer_app_director as consumer5_1_0_, consumer0_.consumer_app_manager as consumer6_1_0_, consumer0_.consumer_app_vp as consumer7_1_0_, consumer0_.carbon_namespace_ownership as carbon_n8_1_0_, consumer0_.consumer_hosted_on as consumer9_1_0_, consumer0_.hosted_vm_ownership as hosted_10_1_0_, consumer0_.consumer_status as consume11_1_0_, consumer0_.consumer_system_type as consume12_1_0_, consumer0_.consumer_tps as consume13_1_0_ from consumer_table consumer0_ where consumer0_.api_name=? and consumer0_.consumer_cmdb=?
Hibernate: select consumer0_.api_name as api_name1_1_0_, consumer0_.consumer_cmdb as consumer2_1_0_, consumer0_.consumer_auth_type as consumer3_1_0_, consumer0_.comments as comments4_1_0_, consumer0_.consumer_app_director as consumer5_1_0_, consumer0_.consumer_app_manager as consumer6_1_0_, consumer0_.consumer_app_vp as consumer7_1_0_, consumer0_.carbon_namespace_ownership as carbon_n8_1_0_, consumer0_.consumer_hosted_on as consumer9_1_0_, consumer0_.hosted_vm_ownership as hosted_10_1_0_, consumer0_.consumer_status as consume11_1_0_, consumer0_.consumer_system_type as consume12_1_0_, consumer0_.consumer_tps as consume13_1_0_ from consumer_table consumer0_ where consumer0_.api_name=? and consumer0_.consumer_cmdb=?
2022-07-13 21:13:25.444 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [a1]
2022-07-13 21:13:25.445 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [cm1]
2022-07-13 21:13:25.811 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([api_name1_1_0_] : [VARCHAR]) - [a1]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([consumer2_1_0_] : [VARCHAR]) - [cm1]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([consumer3_1_0_] : [VARCHAR]) - [cat4]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([comments4_1_0_] : [VARCHAR]) - [cm4]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([consumer5_1_0_] : [VARCHAR]) - [cd4]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([consumer6_1_0_] : [VARCHAR]) - [capm4]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([consumer7_1_0_] : [VARCHAR]) - [cap4]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([carbon_n8_1_0_] : [VARCHAR]) - []
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([consumer9_1_0_] : [VARCHAR]) - [ch4]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([hosted_10_1_0_] : [VARCHAR]) - [chvm4]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([consume11_1_0_] : [VARCHAR]) - [cs4]
2022-07-13 21:13:25.812 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([consume12_1_0_] : [VARCHAR]) - [cst4]
2022-07-13 21:13:25.813 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([consume13_1_0_] : [VARCHAR]) - [null]
2022-07-13 21:13:25.818 DEBUG [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] org.hibernate.SQL                        : update consumer_table set consumer_auth_type=?, comments=?, consumer_app_director=?, consumer_app_manager=?, consumer_app_vp=?, carbon_namespace_ownership=?, consumer_hosted_on=?, hosted_vm_ownership=?, consumer_status=?, consumer_system_type=?, consumer_tps=? where api_name=? and consumer_cmdb=?
Hibernate: update consumer_table set consumer_auth_type=?, comments=?, consumer_app_director=?, consumer_app_manager=?, consumer_app_vp=?, carbon_namespace_ownership=?, consumer_hosted_on=?, hosted_vm_ownership=?, consumer_status=?, consumer_system_type=?, consumer_tps=? where api_name=? and consumer_cmdb=?
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [cat4]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [cm5]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARCHAR] - [cd4]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARCHAR] - [capm4]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [5] as [VARCHAR] - [cap4]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [6] as [VARCHAR] - []
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [7] as [VARCHAR] - [ch4]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [8] as [VARCHAR] - [chvm4]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [9] as [VARCHAR] - [cs4]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [10] as [VARCHAR] - [cst4]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [11] as [VARCHAR] - [null]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [12] as [VARCHAR] - [a1]
2022-07-13 21:13:25.819 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [13] as [VARCHAR] - [cm1]
2022-07-13 21:13:26.533 DEBUG [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] org.hibernate.SQL                        : select provider0_.provider_cmdb as provider1_2_0_, provider0_.api_name as api_name2_2_0_, provider0_.provider_app_director as provider3_2_0_, provider0_.provider_app_vp as provider4_2_0_, provider0_.carbon_namespace_ownership as carbon_n5_2_0_, provider0_.comments as comments6_2_0_, provider0_.hosted_vm_ownership as hosted_v7_2_0_, provider0_.provider_status as provider8_2_0_, provider0_.upstream_service_hosted_on as upstream9_2_0_, provider0_.upstream_service_type as upstrea10_2_0_, provider0_.upstream_service_url as upstrea11_2_0_, provider0_.upstream_vm_ownership as upstrea12_2_0_ from provider_table provider0_ where provider0_.provider_cmdb=? and provider0_.api_name=? and provider0_.provider_app_director=? and provider0_.provider_app_vp=? and provider0_.carbon_namespace_ownership=? and provider0_.comments=? and provider0_.hosted_vm_ownership=? and provider0_.provider_status=? and provider0_.upstream_service_hosted_on=? and provider0_.upstream_service_type=? and provider0_.upstream_service_url=? and provider0_.upstream_vm_ownership=?
Hibernate: select provider0_.provider_cmdb as provider1_2_0_, provider0_.api_name as api_name2_2_0_, provider0_.provider_app_director as provider3_2_0_, provider0_.provider_app_vp as provider4_2_0_, provider0_.carbon_namespace_ownership as carbon_n5_2_0_, provider0_.comments as comments6_2_0_, provider0_.hosted_vm_ownership as hosted_v7_2_0_, provider0_.provider_status as provider8_2_0_, provider0_.upstream_service_hosted_on as upstream9_2_0_, provider0_.upstream_service_type as upstrea10_2_0_, provider0_.upstream_service_url as upstrea11_2_0_, provider0_.upstream_vm_ownership as upstrea12_2_0_ from provider_table provider0_ where provider0_.provider_cmdb=? and provider0_.api_name=? and provider0_.provider_app_director=? and provider0_.provider_app_vp=? and provider0_.carbon_namespace_ownership=? and provider0_.comments=? and provider0_.hosted_vm_ownership=? and provider0_.provider_status=? and provider0_.upstream_service_hosted_on=? and provider0_.upstream_service_type=? and provider0_.upstream_service_url=? and provider0_.upstream_vm_ownership=?
2022-07-13 21:13:26.534 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [pcm1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [a1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARCHAR] - [p1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARCHAR] - [p1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [5] as [VARCHAR] - [p1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [6] as [VARCHAR] - [pc5]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [7] as [VARCHAR] - [pw1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [8] as [VARCHAR] - [ps1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [9] as [VARCHAR] - [uh1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [10] as [VARCHAR] - [us1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [11] as [VARCHAR] - [us1]
2022-07-13 21:13:26.535 TRACE [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [12] as [VARCHAR] - [uo1]
2022-07-13 21:13:26.900 DEBUG [,ae80adb706f0a62d,ae80adb706f0a62d] 53825 --- [nio-8431-exec-3] org.hibernate.SQL                        : insert into provider_table (provider_cmdb, api_name, provider_app_director, provider_app_vp, carbon_namespace_ownership, comments, hosted_vm_ownership, provider_status, upstream_service_hosted_on, upstream_service_type, upstream_service_url, upstream_vm_ownership) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into provider_table (provider_cmdb, api_name, provider_app_director, provider_app_vp, carbon_namespace_ownership, comments, hosted_vm_ownership, provider_status, upstream_service_hosted_on, upstream_service_type, upstream_service_url, upstream_vm_ownership) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Answer 1

我有问题。实际上，在Provider类中，我用错误的类进行了注释。我已经做了更改，现在效果很好：）

这是最新的提供者实体类

@Data
@Entity
@Table(name = "provider_table")
@IdClass(ProviderId.class)
public class Provider implements Serializable {
    @Id
    @Column(name = "api_name")
    private String apiName;
    @Id
    @Column(name = "provider_cmdb")
    private String providerCMDB;

    @Column(name = "provider_app_vp")
    private String providerAppVp;
    @Column(name = "provider_app_director")
    private String providerAppDirector;
    @Column(name = "upstream_service_hosted_on")
    private String upstreamServiceHost;
    @Column(name = "upstream_service_url")
    private String upstreamServiceUrl;
    @Column(name = "upstream_vm_ownership")
    private String upstreamVmOwnership;
    @Column(name = "upstream_service_type")
    private String upstreamServiceType;
    @Column(name = "carbon_namespace_ownership")
    private String providerCarbonNamespaceOwnership;
    @Column(name = "hosted_vm_ownership")
    private String providerHostedVmOwnership;
    @Column(name = "provider_status")
    private String providerStatus;
    @Column(name = "comments")
    private String providerComments;
}

Answer 2

@修改注释用于增强@Query注释，这样我们不仅可以执行SELECT查询，还可以执行insert、update、delete甚至DDL查询。

有关更多详细信息，请访问此链接。

>

@修改@Query（“您的自定义JPQL更新查询”）

您还可以做的是获取您要更新的先前提供商实体/数据，并对相同数据进行更改并再次保存它。

Answer 3

由于服务层不是基于我想回答的问题给出的，因此考虑到存在providerRepository对象

Provider provider=providerRepository.findByUpstreamServiceUrl("xyz"); //Assuming using this method

provider.setXYZ(..);///set the values needed to be updated

providerRepository.save(provider); // As JPA works on state of an object this would update the object instead in inserting in DB

由于对象的ID存在于数据库中，而不是插入新的，它将通过调用save方法进行合并。请参阅：如何使用spring数据jpa更新实体？