CODEWITHSUNDEEP
javamysqlsqlspring-bootspring-data-jpa
Nick
Nick

Reputation: 480

combine joined columns of a table with one column of another table into JAVA object

I want to combine column of different tables in one entity (object) but im getting a column null even it's not null. I have two entities called Operation and CanceledOperation:

Operation:

@Entity
@Table(name = "OPERATIONS")
public class Operation{

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", unique = true, nullable = false)
private Long id;

@Column(name = "Date")
private Timestamp date;

@Transient
private String message;
    
// Other attributes and getters & setters
}

CanceledOperation:

@Entity
@Table(name = "CANCELED_OPERATIONS")
public class CanceledOperation{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Long id;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "OPERATION_ID", nullable = false)
    private Operation operation;

    @Column(name = "RAINSON")
    private String raison;
//getters & setters
}

I'm trying to join the operation and canceledIperation in order to display the raison for the canceled operation and null non canceled.

this is my native query in my repository:

@query(value = "SELECT op.* , co.raison as raison FROM operations op LEFT JOIN canceled_operations co ON op.ID  = co.OPERATION_ID", countQuery = "SELECT count(*) FROM operations op LEFT JOIN canceled_operations co ON op.ID  = co.OPERATION_ID")
    Page<Operation> getAllOperations(Pageable pageable);

and this is how i call my repository:

final Page<Operation> operations= operationRepository.getAllOperations(pageable);

I tried many solution namely using the Object[] instead of Operation and I have also added an attribute called raison in the operation entity with @transient annotation, but even that I still get the raison is null. can you please give me a hint to solve this issue. thank in advance.

Upvotes: 1

Views: 579

Answers (2)

Nick
Nick

Reputation: 480

Well I found an easy way to do it, instead of doing a complicated query, I have used findAll() of each table then I affect the raison attribute from CanceledOperation to the message attribute of Operation using stream and map the two received lists from repositories.

Upvotes: 1

user13744362
user13744362

Reputation: 21

In the CanceledOperation entity, can you give referencedColumnName = "ID" and try?

@OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "OPERATION_ID",referencedColumnName = "ID", nullable = false)
    private Operation operation;

Upvotes: 0

Related Questions