Jpa- Joining Two Tables in Non-Entity Class

JPA- Joining two tables in non-entity class

@SqlResultSetMapping can be placed at any entity class (don't annotate POJOs - it won't work). Mapping to POJO class with @ConstructorResult was added in version 2.1 of JPA. POJO used with the mapping has to have correct constructor.

All columns corresponding to arguments of the intended constructor must be specified using the columns element of the ConstructorResult annotation in the same order as that of the argument list of the constructor.

Please consult following example with query usage and work out your case accordingly.

@Entity
public class Address {
    @Id int id;  
    String street;
}


@SqlResultSetMapping(name="PersonDTOMapping",
    classes = {
     @ConstructorResult(targetClass = PersonDTO.class,
       columns = {@ColumnResult(name="name"), @ColumnResult(name="street")}
     )}
)
@Entity
public class Person {
    @Id int id;
    String name;
    Address address;  
}  

public class PersonDTO {
    String name;
    String street;
    public PersonDTO(String name, String street) {
        this.name = name;
        this.street = street;
    }
}

// usage
Query query = em.createNativeQuery(
    "SELECT p.name AS name, a.street AS street FROM Person p, Address a WHERE p.address_id=a.id",
    "PersonDTOMapping");
List<PersonDTO> result = query.getResultList();

Please note that aliases (AS name and AS street) has to match the names in @ColumnResults.
The example was tested against Ecliselink 2.5.1.

Joining 2 tables with non-key columns in JPA, Hibernate

You need to change your mappedBy to merchant

Foo

@Entity
@Table(name = "skl_transactions")
public class Foo implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Basic(optional = false)
    protected Long id;

    @Column(name = "column_a")
    private String senderAddress;

    -----

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="column_b", referencedColumnName = "column_a", insertable=false, updatable=false)
    private Bar merchant;

    // getters and setters
}

Bar

@Entity
@Table(name = "merchant_config")
public class Bar implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Basic(optional = false)
    protected Long id;

    @Column(name = "column_b")
    private String merchantAddress;

    -----

    @OneToMany(mappedBy = "merchant", fetch = FetchType.LAZY)
    private List<Foo> transactions = new ArrayList<>();

    // getters and setters
}

If above have any issue try changing name and referencedColumnName the other way round

in Foo
@ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="column_a", referencedColumnName = "column_b", insertable=false, updatable=false)
    private Bar merchant;

in Bar
@OneToMany(mappedBy = "merchant", fetch = FetchType.LAZY)
    private List<Foo> transactions = new ArrayList<>();

Joing two tables in JPA repository

So you are querying native queries, so you need to pass nativeQuery = true to query argument. Also, you need to add @Repository annotation to TransictionRepository interface. That is nothing but your Dao layer.

package com.overflow.overflow.service;

import java.util.List;

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

import com.overflow.overflow.models.Transictions;

@Repository
public interface TransictionRepository extends JpaRepository<Transictions, Long> {
    @Query(nativeQuery = true,
            value = "SELECT transiction.user_id, transiction.quantity,transiction.instrument_name, transiction.Price,instrument.LTP"
            + "FROM instrument"
            + "INNER JOIN transiction"
            + "ON instrument.instrument=transiction.instrument_name")
    public List<Object[]> getTransictionsAndInstruments();
}

---

Related Topics