How can i extract a DTO from multiple tables in a micronaut application in Kotlin?

Viewed 708

I am trying to extract into a list of DTO from multiple tables using a Native query but the documentation is just not helping. Here are the two entity classes :


@Entity
@Table(name="addresses")
data class Addresses (

        @Id
        @Column(name="address_id")
        val address_id:Long=0,

        @Column(name="address_name")
        val address_name:String="",

        @OneToOne
        @JoinColumn(name = "emp_id")
        private var pos: Employee?
)

import com.fasterxml.jackson.annotation.JsonManagedReference
import javax.persistence.*

@Entity
@Table(name = "employee")
data class Employee (

        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name = "emp_id")
        val emp_id:Long=0,

        @Column(name = "emp_name")
        val emp_name:String="",

        @OneToOne(mappedBy = "pos",  cascade = [CascadeType.ALL],
                orphanRemoval = true, fetch = FetchType.EAGER)
        private var posAddress: Addresses,

)

I want to use this CustomDTO to extract the result set

@Introspected
class CustomDTO (
        val emp_id:Long?,

        val emp_name:String?,

        val address_name:String?
)

So i will have to use a native query in order to do that :

@Repository
interface EmployeeRepository : JpaRepository<Employee, Long> {
    @Join(value="addresses", type = Join.Type.FETCH)
    @Query("select e.emp_id,emp_name,a.address_name from employee e join addresses a on a.emp_id=e.emp_id")
    fun getResult():List<CustomDTO>
}

But when i try to run it , i get an error : Property address_name is not present in entity: com.example.domain.Employee

Any ideas how i can resolve this ?

1 Answers

For anyone struggling :

@Query("select new com.packagename.DeliverySlotDto(ts.active, st.name) from TemplateSlot ts \n" +
        " inner join DeliveryServiceType st on st.id=ts.serviceTypeId where ts.posId = :posId ")
fun findServiceTypeAndStatusDto(posId:Long):List<DeliverySlotDto>
Related