Spring data jpa join query with multiple parameters

Question

Hello I am trying to pull a record from MySql db using spring jpa.

Entity 1

@Entity @Table(name = "sku_warehouse") @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE) @Document(indexName = "skuwarehouse") public class SkuWarehouse extends AbstractAuditingEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "avail_qty")
    private Integer availQty;

    @Column(name = "effective_date")
    private ZonedDateTime effectiveDate;

    @Column(name = "expiration_date")
    private ZonedDateTime expirationDate;

    @ManyToOne
    private SkuMaster skuMaster;

    @ManyToOne
    private Country country;

    @ManyToOne
    private Warehouse warehouse;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Integer getAvailQty() {
        return availQty;
    }

    public SkuWarehouse availQty(Integer availQty) {
        this.availQty = availQty;
        return this;
    }

    public void setAvailQty(Integer availQty) {
        this.availQty = availQty;
    }

    public ZonedDateTime getEffectiveDate() {
        return effectiveDate;
    }

    public SkuWarehouse effectiveDate(ZonedDateTime effectiveDate) {
        this.effectiveDate = effectiveDate;
        return this;
    }

    public void setEffectiveDate(ZonedDateTime effectiveDate) {
        this.effectiveDate = effectiveDate;
    }

    public ZonedDateTime getExpirationDate() {
        return expirationDate;
    }

    public SkuWarehouse expirationDate(ZonedDateTime expirationDate) {
        this.expirationDate = expirationDate;
        return this;
    }

    public void setExpirationDate(ZonedDateTime expirationDate) {
        this.expirationDate = expirationDate;
    }

    public SkuMaster getSkuMaster() {
        return skuMaster;
    }

    public SkuWarehouse skuMaster(SkuMaster skuMaster) {
        this.skuMaster = skuMaster;
        return this;
    }

    public void setSkuMaster(SkuMaster skuMaster) {
        this.skuMaster = skuMaster;
    }

    public Country getCountry() {
        return country;
    }

    public SkuWarehouse country(Country country) {
        this.country = country;
        return this;
    }

    public void setCountry(Country country) {
        this.country = country;
    }

    public Warehouse getWarehouse() {
        return warehouse;
    }

    public SkuWarehouse warehouse(Warehouse warehouse) {
        this.warehouse = warehouse;
        return this;
    }

    public void setWarehouse(Warehouse warehouse) {
        this.warehouse = warehouse;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        SkuWarehouse skuWarehouse = (SkuWarehouse) o;
        if (skuWarehouse.id == null || id == null) {
            return false;
        }
        return Objects.equals(id, skuWarehouse.id);
    }

    @Override
    public int hashCode() {
        return Objects.hashCode(id);
    }

    @Override
    public String toString() {
        return "SkuWarehouse{" +
            "id=" + id +
            ", availQty='" + availQty + "'" +
            ", effectiveDate='" + effectiveDate + "'" +
            ", expirationDate='" + expirationDate + "'" +
            '}';
    } }

Entity 2 :

@Entity
@Table(name = "sku_master")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@Document(indexName = "skumaster")
public class SkuMaster extends AbstractAuditingEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "ordering_item")
    private String orderingItem;

    @NotNull
    @Column(name = "sku_number", nullable = false)
    private String skuNumber;

    @Column(name = "sku_description")
    private String skuDescription;

    @Column(name = "brand_name")
    private String brandName;

    @Column(name = "item_status")
    private String itemStatus;

    @Column(name = "item_weight")
    private Float itemWeight;

    @Column(name = "item_tax_cd")
    private String itemTaxCd;

    @Enumerated(EnumType.STRING)
    @Column(name = "vendor_ship_cd")
    private Flag vendorShipCd;

    @Column(name = "ltu_quantity")
    private String ltuQuantity;

    @ManyToMany
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    @JoinTable(name = "sku_master_country",
               joinColumns = @JoinColumn(name="sku_masters_id", referencedColumnName="id"),
               inverseJoinColumns = @JoinColumn(name="countries_id", referencedColumnName="id"))
    private Set<Country> countries = new HashSet<>();

    @ManyToMany
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
    @JoinTable(name = "sku_master_warehouse",
               joinColumns = @JoinColumn(name="sku_masters_id", referencedColumnName="id"),
               inverseJoinColumns = @JoinColumn(name="warehouses_id", referencedColumnName="id"))
    private Set<Warehouse> warehouses = new HashSet<>();

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getOrderingItem() {
        return orderingItem;
    }

    public SkuMaster orderingItem(String orderingItem) {
        this.orderingItem = orderingItem;
        return this;
    }

    public void setOrderingItem(String orderingItem) {
        this.orderingItem = orderingItem;
    }

    public String getSkuNumber() {
        return skuNumber;
    }

    public SkuMaster skuNumber(String skuNumber) {
        this.skuNumber = skuNumber;
        return this;
    }

    public void setSkuNumber(String skuNumber) {
        this.skuNumber = skuNumber;
    }

    public String getSkuDescription() {
        return skuDescription;
    }

    public SkuMaster skuDescription(String skuDescription) {
        this.skuDescription = skuDescription;
        return this;
    }

    public void setSkuDescription(String skuDescription) {
        this.skuDescription = skuDescription;
    }

    public String getBrandName() {
        return brandName;
    }

    public SkuMaster brandName(String brandName) {
        this.brandName = brandName;
        return this;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getItemStatus() {
        return itemStatus;
    }

    public SkuMaster itemStatus(String itemStatus) {
        this.itemStatus = itemStatus;
        return this;
    }

    public void setItemStatus(String itemStatus) {
        this.itemStatus = itemStatus;
    }

    public Float getItemWeight() {
        return itemWeight;
    }

    public SkuMaster itemWeight(Float itemWeight) {
        this.itemWeight = itemWeight;
        return this;
    }

    public void setItemWeight(Float itemWeight) {
        this.itemWeight = itemWeight;
    }

    public String getItemTaxCd() {
        return itemTaxCd;
    }

    public SkuMaster itemTaxCd(String itemTaxCd) {
        this.itemTaxCd = itemTaxCd;
        return this;
    }

    public void setItemTaxCd(String itemTaxCd) {
        this.itemTaxCd = itemTaxCd;
    }

    public Flag getVendorShipCd() {
        return vendorShipCd;
    }

    public SkuMaster vendorShipCd(Flag vendorShipCd) {
        this.vendorShipCd = vendorShipCd;
        return this;
    }

    public void setVendorShipCd(Flag vendorShipCd) {
        this.vendorShipCd = vendorShipCd;
    }

    public String getLtuQuantity() {
        return ltuQuantity;
    }

    public SkuMaster ltuQuantity(String ltuQuantity) {
        this.ltuQuantity = ltuQuantity;
        return this;
    }

    public void setLtuQuantity(String ltuQuantity) {
        this.ltuQuantity = ltuQuantity;
    }

    public Set<Country> getCountries() {
        return countries;
    }

    public SkuMaster countries(Set<Country> countries) {
        this.countries = countries;
        return this;
    }

    public SkuMaster addCountry(Country country) {
        this.countries.add(country);
        return this;
    }

    public SkuMaster removeCountry(Country country) {
        this.countries.remove(country);
        return this;
    }

    public void setCountries(Set<Country> countries) {
        this.countries = countries;
    }

    public Set<Warehouse> getWarehouses() {
        return warehouses;
    }

    public SkuMaster warehouses(Set<Warehouse> warehouses) {
        this.warehouses = warehouses;
        return this;
    }

    public SkuMaster addWarehouse(Warehouse warehouse) {
        this.warehouses.add(warehouse);
        return this;
    }

    public SkuMaster removeWarehouse(Warehouse warehouse) {
        this.warehouses.remove(warehouse);
        return this;
    }

    public void setWarehouses(Set<Warehouse> warehouses) {
        this.warehouses = warehouses;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        SkuMaster skuMaster = (SkuMaster) o;
        if (skuMaster.id == null || id == null) {
            return false;
        }
        return Objects.equals(id, skuMaster.id);
    }

    @Override
    public int hashCode() {
        return Objects.hashCode(id);
    }

    @Override
    public String toString() {
        return "SkuMaster{" +
            "id=" + id +
            ", orderingItem='" + orderingItem + "'" +
            ", skuNumber='" + skuNumber + "'" +
            ", skuDescription='" + skuDescription + "'" +
            ", brandName='" + brandName + "'" +
            ", itemStatus='" + itemStatus + "'" +
            ", itemWeight='" + itemWeight + "'" +
            ", itemTaxCd='" + itemTaxCd + "'" +
            ", vendorShipCd='" + vendorShipCd + "'" +
            ", ltuQuantity='" + ltuQuantity + "'" +
            '}';
    }
}

Method that pull the record

@Query("select sw from SkuWarehouse sw left join fetch Warehouse w where sw.warehouse = w.warehouseId AND w.warehouseId=:warehouseId AND sw.skuMaster=:skuMasterId")
SkuWarehouse findOneWithEagerRelationships(@Param("warehouseId") String warehouseId, @Param("skuMasterId") Long skuMasterId);

Actually SQL query that works

select * from sku_warehouse sw 
join warehouse w  on (sw.warehouse_id = w.id)
where  w.warehouse_id='024'
 and sw.sku_master_id='1072'

Error Log

Caused by: org.hibernate.property.access.spi.PropertyAccessException: Error accessing field [private java.lang.Long com.company.wmapis.domain.SkuMaster.id] by reflection for persistent property [com.company.wmapis.domain.SkuMaster#id] : 1454
    at org.hibernate.property.access.spi.GetterFieldImpl.get(GetterFieldImpl.java:71)
    at org.hibernate.tuple.entity.AbstractEntityTuplizer.getIdentifier(AbstractEntityTuplizer.java:224)
    at org.hibernate.persister.entity.AbstractEntityPersister.getIdentifier(AbstractEntityPersister.java:4647)
    at org.hibernate.persister.entity.AbstractEntityPersister.isTransient(AbstractEntityPersister.java:4358)
    at org.hibernate.engine.internal.ForeignKeys.isTransient(ForeignKeys.java:226)
    at org.hibernate.engine.internal.ForeignKeys.getEntityIdentifierIfNotUnsaved(ForeignKeys.java:276)
    at org.hibernate.type.EntityType.getIdentifier(EntityType.java:462)
    at org.hibernate.type.ManyToOneType.nullSafeSet(ManyToOneType.java:161)
    at org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:53)
    at org.hibernate.loader.hql.QueryLoader.bindParameterValues(QueryLoader.java:628)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1956)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1909)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
    at org.hibernate.loader.Loader.doQuery(Loader.java:932)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
    at org.hibernate.loader.Loader.doList(Loader.java:2615)
    at org.hibernate.loader.Loader.doList(Loader.java:2598)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
    at org.hibernate.loader.Loader.list(Loader.java:2425)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1459)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1426)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398)
    at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1444)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:210)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:82)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 167 common frames omitted
Caused by: java.lang.IllegalArgumentException: Can not set java.lang.Long field com.company.wmapis.domain.SkuMaster.id to java.lang.Long
    at sun.reflect.UnsafeFieldAccessorImpl.throwSetIllegalArgumentException(UnsafeFieldAccessorImpl.java:167)
    at sun.reflect.UnsafeFieldAccessorImpl.throwSetIllegalArgumentException(UnsafeFieldAccessorImpl.java:171)
    at sun.reflect.UnsafeFieldAccessorImpl.ensureObj(UnsafeFieldAccessorImpl.java:58)
    at sun.reflect.UnsafeObjectFieldAccessorImpl.get(UnsafeObjectFieldAccessorImpl.java:36)
    at java.lang.reflect.Field.get(Field.java:393)
    at org.hibernate.property.access.spi.GetterFieldImpl.get(GetterFieldImpl.java:67)
    ... 206 common frames omitted

What is wrong in the Jpa query? I even tried passing the SkuMaster Object instead of Long but then I get MySql query exception.

Id is the field in the entity class defined as @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; — sw-enthusiast
– sw-enthusiast, Commented May 5, 2017 at 17:38
The stack trace says the query has been parsed and it is attempting to get the a field name id value from the Class SkuMaster (as a Long). private java.lang.Long com.company.wmapis.domain.SkuMaster.id Do you have a public Long getId() method in SkuMaster? — K.Nicholas
– K.Nicholas, Commented May 5, 2017 at 17:58

sw-enthusiast · Accepted Answer · 2017-05-05 20:47:09Z

5

It works after I changed my Jpa query and method as below

@Query(value="SELECT * FROM sku_warehouse sw JOIN warehouse w ON (sw.warehouse_id = w.Id) WHERE w.warehouse_id=?1 AND sw.sku_master_id=?2", nativeQuery = true)
SkuWarehouse findOneWithEagerRelationships(String warehouseId, Long skuMasterId);

answered May 5, 2017 at 20:47

sw-enthusiast

2091 gold badge4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spring data jpa join query with multiple parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related