I am trying to update multiple table in spring jpa. Consumer table update is happening perfectl but provider table is not able to update. Instead of it , its trying for insert which causes violation of primary key which is expected because it is trying for insert instead of update. Although my code flow for both the table is same but there is issue only with producer. This is my Consumer entity
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;
}
This is my Consumer repository
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);
}
This is my Provider entity
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;
}
This is my Provider Repository
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);*/
}
Here is the logs
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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
CodePudding user response：
As the service layer isn't given based on the question i would like to answer,considering providerRepository object is present
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
As ID of Object is present in DB instead of inserting new it will merge by calling save method Refer:How do I update an entity using spring-data-jpa?
CodePudding user response：
@Modifying annotation is used to enhance the @Query annotation so that we can execute not only SELECT queries, also insert, update, delete, and even DDL queries.
For more detail go through this link.
You can also use @Modifying and @Query to write your custom JPA query to update the Provider table.
@Modifying @Query("your custom JPQL update query")
What you can also do is get the previous provider entity/data you want to update and do the changes in the same data and again save it.