CODEWITHSUNDEEP
springjpakotlinspring-data-jpa
Heidi E
Heidi E

Reputation: 361

Kotlin JPA query inner join using two types

I am new to Kotlin and JPA. I have an inner join query that gets data from two tables(Postgres). The query works fine. However, since I now have two types (the two tables), using either one only returns all the fields from one of the tables. In order to return all fields, I changed the type to List. However, when I do that, my oject that is returned has no fields, only the raw data. How can I change my code so my json response contains both the name of the fields, as well as the data.

Sorry if my question isn't clear, I'm very new to Kotlin.

UPDATED CODE my repository code

package com.sg.xxx.XXXTTT.report.repository
import com.sg.xxx.XXXTTT.report.model.Report
import com.sg.xxx.XXXTTT.report.model.ReportWithBatches
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import org.springframework.stereotype.Repository
import java.time.LocalDate


@Repository
interface IReportRepository : JpaRepository<Report, Long> {

    fun findAllByCreationDate(date: LocalDate): List<Report>

    fun findByReportName(name: String): Report?

    fun findByAdlsFullPath(name: String): Report?

    @Query("SELECT new com.sg.xxx.xxxttt.report.model.ReportWithBatches(r.adlsFullPath, r.sentToXXX, r.contentLength, r.creationDate, r.remoteFileNameOnFTA, b.dataPath , b.version, b.source, r.numberOfRecords) FROM Report r INNER JOIN BatchInfo b ON r.reportUuid = b.reportUuid WHERE r.creationDate = ?1")
    fun findAllByCreationDateJoinBatches(date: LocalDate): List<ReportWithBatches>
}

my controller code

    @GetMapping(value = ["/linkBatches/{today}"])
    fun findAllByCreationDateJoinBatches(@PathVariable("today") @DateTimeFormat(pattern = "yyyyMMdd") date: LocalDate): List<ReportWithBatches> {
        return eligibleService.findAllByCreationDateJoinBatches(date)
    }

my DTO

package com.sg.xxx.xxxttt.report.model
import java.time.LocalDate

open class ReportWithBatches(
        var adlsFullPath: String?,
        var sentToXXX: Boolean?,
        var contentLength: Long?,
        var creationDate: LocalDate,
        var remoteFileNameOnFTA: String?,
        var dataPath: String?,
        var version: Int?,
        var source: String?,
        var numberOfRecords: Long?
)

my function in the service

fun findAllByCreationDateJoinBatches(date: LocalDate): List<ReportWithBatches> {
        return reportRepository.findAllByCreationDateJoinBatches(date)
    }
        }

Upvotes: 3

Views: 2092

Answers (1)

Alexey Soshin
Alexey Soshin

Reputation: 17721

As was correctly stated in the comments, the return type of your query is List<Array<Any?>>, not List<Any>.

Create a data class that would serve as your DTO and map results to it:

data class ReportWithBatchInfo(val azureFileName : String, /* more field here */)

fun findAllByCreationDateJoinBatches(date: LocalDate): List<ReportWithBatchInfo> {
    return reportRepository.findAllByCreationDateJoinBatches(date).map {
        ReportWithBatchInfo(it[0] as String, /* more mappings here */)
    }
}

Upvotes: 3

Related Questions